From: | "Andrus" <kobruleht2(at)hot(dot)ee> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Best way to prevent overlapping dates |
Date: | 2007-05-25 08:22:25 |
Message-ID: | f366ef$1ut3$1@news.hub.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
I need to disable rows with overlapping dates in 8.1+
I created the following trigger procedure for this.
Is this best way ?
Will it prevent overlapping rows in all cases ?
Andrus.
create table puhkus (reanr serial primary key, palgus date, plopp date);
CREATE OR REPLACE FUNCTION puhkus_sequenced_pkey() RETURNS trigger AS $$
DECLARE
OverlappingRow INTEGER;
BEGIN
SELECT reanr
INTO OverlappingRow
FROM puhkus
WHERE puhkus.reanr<>NEW.reanr AND doverlaps( puhkus.palgus, puhkus.plopp,
NEW.palgus, NEW.plopp );
IF found THEN
RAISE EXCEPTION 'Changed row % overlaps with existing row %', NEW.reanr,
OverLappingRow ;
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER puhkus_sequenced_trigger BEFORE INSERT OR UPDATE ON puhkus
FOR EACH ROW EXECUTE PROCEDURE puhkus_sequenced_pkey();
CREATE OR REPLACE FUNCTION public.doverlaps(date,
date, date, date, out bool) IMMUTABLE AS
$_$
SELECT coalesce($1, date '0001-01-01')<=coalesce($4, date '9999-12-31') AND
coalesce($2, date '9999-12-31')>=coalesce($3, date '0001-01-01' );
$_$ language sql;
CREATE OR REPLACE FUNCTION PUBLIC.GOMONTH(DATE, INTEGER, OUT DATE) IMMUTABLE
AS
$_$
SELECT ($1 + ($2 * '1 MONTH'::INTERVAL))::DATE;
$_$ LANGUAGE SQL;
I also tried code from SNODGRASS book
"Developing Time-Oriented Database Applications" but this causes error.
create table puhkus (palgus date, plopp date);
alter table puhkus add check
(NOT EXISTS ( SELECT *
FROM puhkus AS I1
WHERE 1 < (SELECT COUNT(*)
FROM puhkus AS I2
WHERE doverlaps(i1.palgus, i1.plopp, i2.palgus, i2.plopp)
) ));
but got error
ERROR: cannot use subquery in check constraint
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Allison | 2007-05-25 10:34:52 | Re: why postgresql over other RDBMS |
Previous Message | Cyril VELTER | 2007-05-25 08:17:34 | Re: Winsock error 10035 while trying to upgrade from 8.0 to 8.2 |