반응형
Introduction
When creating a pocket agenda application, it's quite useful to have a mechanism inhibiting intervals overlapping (i.e. first job starting at 10:00, lasting two hours and second job starting at 11:00, lasting two hours). This article is a possible implementation method for PostgreSQL 6.5 and maybe for PostgreSQL 7.1. ;)
After two sweaty weeks worried about a trigger which did not work, I can finally and proudly explain the problem I had and how I solved it.
I am writing a web based application (Apache, PHP, PostgreSQL) for managing a music hall reservation system and here I faced a problem I'd never thought about. How to avoid two different reservations overlapping? In this case a "unique key" constraint is not enough to guarantee reservations will be correctly inserted because the key oneness" itself cannot guarantee that two reservations are not partially overlapped (you can only see this with a very aimed SELECT).
Possible solutions :
Developing extra dedicated checks in the php code.
Adding to the reservation table a trigger which inhibits the feared overlapping.
Since the problem was generally recognized, and since the extra checking structure would have been boring but necessary for safely guaranteeing the right insertion and change, I opted for the latter (b).
What's a trigger anyway?
PostgreSQL allows you to launch a procedure before or after you do an INSERT, UPDATE or DELETE transaction. This launched procedure is called a trigger. If you specify to launch it before though, it could fail and force the whole operation to fail.
What coding languages can this trigger be written with?
PostgreSQL allows you to write the trigger associated procedure in C, SQL, PL/pgSQL, or PL/TCL. I used PL/pgSQL because I thought it was the quickest way to implement the trigger, but it could also be written in the other languages (being better performing in C (Code is better ;-)).
To have a trigger equipped table in PostgreSQL you must (in order) :
Create the table
Create the procedure used by the trigger
Create the trigger
Here you have the minimum working example; more documentation can be found at www.postgresql.org in the PL/pgSQL chapter ( for pgsql 6.5 Programmer's Guide, III volume, cap. 42 ).
Creating the trigger
CREATE TABLE tritab (
idr int4,
nome text,
start int4,
len int4);
Here you created the table. idr is a unique record identifier (you cannot have two records with the same identifier). start is the starting reservation time. len is the reservation time.
CREATE FUNCTION trifun () RETURNS OPAQUE AS '
DECLARE
myrec RECORD;
BEGIN
/* se insert verifica se ci sono record vecchi che si intersecano */
/* if insert verifies if there are old intersecting records */
IF TG_OP = ''INSERT'' THEN
SELECT * INTO myrec FROM tritab WHERE
start < (NEW.start + NEW.len) AND
(start + len) > NEW.start;
IF FOUND THEN
RAISE EXCEPTION ''INSERT failed:
intersection with record % at (%,%)'',
myrec.idr, myrec.start, myrec.len;
END IF;
END IF;
/* se update come insert con in piu il constraint che l id non sia quello del record modificato */
/* if update like insert plus check on id, assuring that it's not the one modified */
IF TG_OP = ''UPDATE'' THEN
SELECT * INTO myrec FROM tritab WHERE
start < (NEW.start + NEW.len) AND
(start + len) > NEW.start AND
idr <> OLD.idr;
IF FOUND THEN
RAISE EXCEPTION ''UPDATE failed:
intersection with record % at (%,%)'',
myrec.idr, myrec.start, myrec.len;
END IF;
END IF;
RETURN NEW;
END;
' LANGUAGE 'plpgsql';
Here you created the function to be triggered. I'll promptly give you a private explanation after you have studied that chapter in the PostgreSQL manual. Some tips :
TG_OP contains "INSERT" when the trigger is called for inserting and "UPDATE" when it's called for a change. This distinction was necessary since in the change case the record had to be excluded from the "SELECT" because at procedure call time it's still not modified in the table.
NEW is a type RECORD macro which contains the new record.
OLD is a type RECORD macro which contains the old record (in cases where changes are occuring).
FOUND is a boolean macro which is TRUE if the select has found at least one record.
RAISE exits the procedure and returns a NULL value (for which the trigger makes the insert or change fail).
CREATE TRIGGER tritri BEFORE INSERT OR UPDATE on tritab
FOR EACH ROW EXECUTE PROCEDURE trifun();
This linked the function as a trigger to the table.
Now, when launching sequentially these two example insertions :
INSERT INTO tritab ( idr, nome, start, len ) values ( 1, 'one', 10, 2);
INSERT INTO tritab ( idr, nome, start, len ) values ( 2, 'two', 11, 2);
the second will fail.
Goal.
Thanks to
DaDoS e NdK di ircnet/#programmazione
Copyright © 2001 Matteo Nastasi (matteo.nastasi@milug.org),
Translated by Martino Bana (martino.bana@milug.org)
When creating a pocket agenda application, it's quite useful to have a mechanism inhibiting intervals overlapping (i.e. first job starting at 10:00, lasting two hours and second job starting at 11:00, lasting two hours). This article is a possible implementation method for PostgreSQL 6.5 and maybe for PostgreSQL 7.1. ;)
After two sweaty weeks worried about a trigger which did not work, I can finally and proudly explain the problem I had and how I solved it.
I am writing a web based application (Apache, PHP, PostgreSQL) for managing a music hall reservation system and here I faced a problem I'd never thought about. How to avoid two different reservations overlapping? In this case a "unique key" constraint is not enough to guarantee reservations will be correctly inserted because the key oneness" itself cannot guarantee that two reservations are not partially overlapped (you can only see this with a very aimed SELECT).
Possible solutions :
Developing extra dedicated checks in the php code.
Adding to the reservation table a trigger which inhibits the feared overlapping.
Since the problem was generally recognized, and since the extra checking structure would have been boring but necessary for safely guaranteeing the right insertion and change, I opted for the latter (b).
What's a trigger anyway?
PostgreSQL allows you to launch a procedure before or after you do an INSERT, UPDATE or DELETE transaction. This launched procedure is called a trigger. If you specify to launch it before though, it could fail and force the whole operation to fail.
What coding languages can this trigger be written with?
PostgreSQL allows you to write the trigger associated procedure in C, SQL, PL/pgSQL, or PL/TCL. I used PL/pgSQL because I thought it was the quickest way to implement the trigger, but it could also be written in the other languages (being better performing in C (Code is better ;-)).
To have a trigger equipped table in PostgreSQL you must (in order) :
Create the table
Create the procedure used by the trigger
Create the trigger
Here you have the minimum working example; more documentation can be found at www.postgresql.org in the PL/pgSQL chapter ( for pgsql 6.5 Programmer's Guide, III volume, cap. 42 ).
Creating the trigger
CREATE TABLE tritab (
idr int4,
nome text,
start int4,
len int4);
Here you created the table. idr is a unique record identifier (you cannot have two records with the same identifier). start is the starting reservation time. len is the reservation time.
CREATE FUNCTION trifun () RETURNS OPAQUE AS '
DECLARE
myrec RECORD;
BEGIN
/* se insert verifica se ci sono record vecchi che si intersecano */
/* if insert verifies if there are old intersecting records */
IF TG_OP = ''INSERT'' THEN
SELECT * INTO myrec FROM tritab WHERE
start < (NEW.start + NEW.len) AND
(start + len) > NEW.start;
IF FOUND THEN
RAISE EXCEPTION ''INSERT failed:
intersection with record % at (%,%)'',
myrec.idr, myrec.start, myrec.len;
END IF;
END IF;
/* se update come insert con in piu il constraint che l id non sia quello del record modificato */
/* if update like insert plus check on id, assuring that it's not the one modified */
IF TG_OP = ''UPDATE'' THEN
SELECT * INTO myrec FROM tritab WHERE
start < (NEW.start + NEW.len) AND
(start + len) > NEW.start AND
idr <> OLD.idr;
IF FOUND THEN
RAISE EXCEPTION ''UPDATE failed:
intersection with record % at (%,%)'',
myrec.idr, myrec.start, myrec.len;
END IF;
END IF;
RETURN NEW;
END;
' LANGUAGE 'plpgsql';
Here you created the function to be triggered. I'll promptly give you a private explanation after you have studied that chapter in the PostgreSQL manual. Some tips :
TG_OP contains "INSERT" when the trigger is called for inserting and "UPDATE" when it's called for a change. This distinction was necessary since in the change case the record had to be excluded from the "SELECT" because at procedure call time it's still not modified in the table.
NEW is a type RECORD macro which contains the new record.
OLD is a type RECORD macro which contains the old record (in cases where changes are occuring).
FOUND is a boolean macro which is TRUE if the select has found at least one record.
RAISE exits the procedure and returns a NULL value (for which the trigger makes the insert or change fail).
CREATE TRIGGER tritri BEFORE INSERT OR UPDATE on tritab
FOR EACH ROW EXECUTE PROCEDURE trifun();
This linked the function as a trigger to the table.
Now, when launching sequentially these two example insertions :
INSERT INTO tritab ( idr, nome, start, len ) values ( 1, 'one', 10, 2);
INSERT INTO tritab ( idr, nome, start, len ) values ( 2, 'two', 11, 2);
the second will fail.
Goal.
Thanks to
DaDoS e NdK di ircnet/#programmazione
Copyright © 2001 Matteo Nastasi (matteo.nastasi@milug.org),
Translated by Martino Bana (martino.bana@milug.org)
반응형