Best way to prevent overlapping dates
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
On May 25, 2007, at 3:22 , Andrus wrote:
CREATE TRIGGER puhkus_sequenced_trigger BEFORE INSERT OR UPDATE ON
puhkus
FOR EACH ROW EXECUTE PROCEDURE puhkus_sequenced_pkey();
You can also use CREATE CONSTRAINT TRIGGER, which allows you to have
deferrable constraints. This is useful if statements within a
transaction may temporarily put the database in a state where it
violates the constraint: the constraint will be called at the end of
the transaction to make sure integrity is maintained.
http://www.postgresql.org/docs/8.2/interactive/sql-createconstraint.html
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
Right. As the error says, subqueries in CHECK constraints is not
implemented in PostgreSQL.
Michael Glaesemann
grzm seespotcode net
On Tue, 2007-05-29 at 10:59 -0500, Michael Glaesemann wrote:
On May 25, 2007, at 3:22 , Andrus wrote:
CREATE TRIGGER puhkus_sequenced_trigger BEFORE INSERT OR UPDATE ON
puhkus
FOR EACH ROW EXECUTE PROCEDURE puhkus_sequenced_pkey();You can also use CREATE CONSTRAINT TRIGGER, which allows you to have
deferrable constraints. This is useful if statements within a
transaction may temporarily put the database in a state where it
violates the constraint: the constraint will be called at the end of
the transaction to make sure integrity is maintained.http://www.postgresql.org/docs/8.2/interactive/sql-createconstraint.html
The docs say:
"CREATE CONSTRAINT TRIGGER is used within CREATE TABLE/ALTER TABLE and
by pg_dump to create the special triggers for referential integrity. It
is not intended for general use."
Is there harm in using constraint triggers outside of pg_dump? It seems
like deferrable constraints would be useful for many applications, but
the docs imply that it's a bad idea.
Regards,
Jeff Davis
PS: Nice to meet you at PGCon. Hope you enjoyed it as much as I did!
Jeff Davis <pgsql@j-davis.com> writes:
Is there harm in using constraint triggers outside of pg_dump? It seems
like deferrable constraints would be useful for many applications, but
the docs imply that it's a bad idea.
It's un-deprecated as of 8.3.
regards, tom lane
On May 29, 2007, at 12:41 , Jeff Davis wrote:
"CREATE CONSTRAINT TRIGGER is used within CREATE TABLE/ALTER TABLE and
by pg_dump to create the special triggers for referential
integrity. It
is not intended for general use."Is there harm in using constraint triggers outside of pg_dump? It
seems
like deferrable constraints would be useful for many applications, but
the docs imply that it's a bad idea.
It's a PostgreSQL extension, so it's nonstandard, but my
understanding is that it's safe to use and there are no current plans
to make it go away. As long as you understand what you're doing, I
don't know of any problems with using CREATE CONSTRAINT TRIGGER. I've
asked similar questions in the past, so you might want to check the
archives.
Michael Glaesemann
grzm seespotcode net
PS: Nice to meet you at PGCon. Hope you enjoyed it as much as I did!
It was good to meet you too, Jeff! Hope to see you at the next
gathering :)