CHECK constraint and trigger - Mailing list pgsql-novice

Hi,

I have one table where I want one column ("canceled_date") to be updated by a trigger function whenever the boolean
"canceled"column is set to true. The problem is that I also want to have a CHECK constraint defined on that table that
makessure no overlapping bookings exist, and the trigger works fine until I add the chk_not_overlaps contraint (see
below).

=> insert into booking(customer, resource, start_time, end_time)
values (1,1,'2006-12-01','2006-12-02');

Query returned successfully: 1 rows affected, 20 ms execution time.

=> update booking set canceled=true where id=1;

ERROR: new row for relation "booking" violates check constraint "chk_not_overlaps"
SQL state: 23514

=> alter table booking drop constraint chk_not_overlaps;

Query returned successfully: 1 rows affected, 20 ms execution time.

=> update booking set canceled=true where id=1;

Query returned successfully: 1 rows affected, 20 ms execution time.

I have tried altering the trigger event (before/after insert or update) without success. Any ideas?

Pg version is 8.2b for Windows (PostgreSQL 8.2beta2 on i686-pc-mingw32, compiled by GCC gcc.exe (GCC) 3.4.2
(mingw-special))

----------------------------------------------------------------------------------------
create table booking
(
    id bigserial,
    customer integer not null,
    resource integer not null,
    added_date timestamp not null default now(),
    canceled boolean not null default false,
    canceled_date timestamp,
    start_time timestamp not null,
    end_time timestamp not null,
    payment_recieved boolean not null default false,
    pickup_time timestamp,
    return_time timestamp,
    constraint pk_booking primary key (id)
);

create or replace function not_overlaps(bigint, timestamp, timestamp) returns boolean as
'select
    case when sum(id) > 0 then
        false
    else
        true
    end
from booking
where resource = $1
and (start_time, end_time) overlaps ($2, $3)
and canceled = false
group by resource'
language sql;

alter table booking
    add constraint chk_not_overlaps check (not_overlaps(resource, start_time, end_time));

create or replace function tf_set_canceled_date() returns trigger as
$$
declare
begin
    if NEW.canceled=true then
        NEW.canceled_date = now();
    end if;
    return NEW;
end;
$$
language plpgsql;

create trigger tgr_booking_modified
    before insert or update on booking
    for each row execute procedure tf_set_canceled_date();

----------------------------------------------------------------------------------------


Koppla av och tjäna pengar på din skicklighet på Spray Spel! http://www.spray.se/underhallning/spel/