[SQL] None numeric exclusion constraints using GIST
I'll give it another try. :) Is there provision for creating none numeric exclusion constraints on GIST indexes? >From my reading of the following, ti doesn't appear to be possible: http://www.postgresql.org/docs/9.1/static/sql-createtable.html#SQL-CREATETABLE-EXCLUDE http://www.postgresql.org/docs/9.1/static/datatype-geometric.html http://www.postgresql.org/docs/9.1/static/sql-createindex.html http://www.depesz.com/index.php/2010/01/03/waiting-for-8-5-exclusion-constraints/ Is this correct? -- Regards, Richard Broersma Jr. -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] FOR EACH STATEMENT trigger ?
Hi there I am trying to get an example of SET BASED trigger logic with FOR EACH STATEMENT, but I cannot find any example involving the pseudo table NEW (or OLD) in the trigger function SQL statement. Let me give you a real life example. Suppose we have the above table : CREATE TABLE T_PRODUIT_DISPO_PDD (PRD_ID INT NOT NULL, PDD_BEGIN DATENOT NULL, PDD_ENDDATE, PDD_QUANTITY FLOAT NOT NULL); We want to never have more thant one PDD_END = NULL for the same PRD_ID. The assertion we can do is : ALTER TABLE T_PRODUIT_DISPO_PDD ADD CONSTRAINT CK_PDD_PRD_FIN_UNIQUENULL CHECK (NOT EXISTS(SELECT 0 FROM T_PRODUIT_DISPO_PDD WHERE PDD_FIN IS NULL GROUP BY PRD_ID HAVING COUNT(*) > 1)) Which is not supported by PG So I wuld like to do this with a FOR EACH STATEMENT trigger and not by a FOR EACH ROW. Here is the code I try : CREATE OR REPLACE FUNCTION F_UNIQUE_NULL_PRD_END() RETURNS trigger AS $code$ DECLARE n_rows integer; BEGIN SELECT COUNT(*) INTO n_rows WHERE EXISTS(SELECT 0 FROM T_PRODUIT_DISPO_PDD WHERE PRD_ID IN(SELECT NEW.PRD_ID FROM NEW) AS T AND PDD_END IS NULL GROUP BY PRD_ID HAVING COUNT(*) > 1); IF ( n_rows IS NOT NULL ) THEN RAISE EXCEPTION 'Violation de la contrainte d''unicité sur le couple de colonne PRD_ID + PDD_FIN'; ROLLBACK TRANSACTION; END IF; RETURN NULL; END $code$ LANGUAGE 'plpgsql' VOLATILE Which produce an error ! Of course I can do that with a FOR EACH STATEMENT like this one : CREATE OR REPLACE FUNCTION F_UNIQUE_NULL_PRD_FIN() RETURNS trigger AS $code$ DECLARE n_rows integer; BEGIN SELECT 1 INTO n_rows WHERE EXISTS(SELECT 0 FROM T_PRODUIT_DISPO_PDD WHERE PRD_ID = NEW.PRD_ID AND PDD_FIN IS NULL GROUP BY PRD_ID HAVING COUNT(*) > 1); IF ( n_rows IS NOT NULL ) THEN RAISE EXCEPTION 'Violation de la contrainte d''unicité sur le couple de colonne PRD_ID + PDD_FIN'; ROLLBACK TRANSACTION; END IF; RETURN NULL; END $code$ LANGUAGE 'plpgsql' VOLATILE CREATE TRIGGER E_IU_PRD AFTER INSERT OR UPDATE ON T_PRODUIT_DISPO_PDD FOR EACH ROW EXECUTE PROCEDURE F_UNIQUE_NULL_PRD_FIN(); But it is absolutly not that I Want Thanks -- Frédéric BROUARD - expert SGBDR et SQL - MVP SQL Server - 06 11 86 40 66 Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com Enseignant Arts & Métiers PACA, ISEN Toulon et CESI/EXIA Aix en Provence Audit, conseil, expertise, formation, modélisation, tuning, optimisation *** http://www.sqlspot.com * -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] FOR EACH STATEMENT trigger ?
Hello, 2011/5/6 F. BROUARD / SQLpro : > > Hi there > > I am trying to get an example of SET BASED trigger logic with FOR EACH > STATEMENT, but I cannot find any example involving the pseudo table NEW > (or OLD) in the trigger function SQL statement. > PostgreSQL doesn't support NEW or OLD tables in statement triggers. You should to use ROW triggers. Regards Pavel Stehule > Let me give you a real life example. > > Suppose we have the above table : > > CREATE TABLE T_PRODUIT_DISPO_PDD > (PRD_ID INT NOT NULL, > PDD_BEGIN DATE NOT NULL, > PDD_END DATE, > PDD_QUANTITY FLOAT NOT NULL); > > We want to never have more thant one PDD_END = NULL for the same PRD_ID. > > The assertion we can do is : > > ALTER TABLE T_PRODUIT_DISPO_PDD > ADD CONSTRAINT CK_PDD_PRD_FIN_UNIQUENULL > CHECK (NOT EXISTS(SELECT 0 > FROM T_PRODUIT_DISPO_PDD > WHERE PDD_FIN IS NULL > GROUP BY PRD_ID > HAVING COUNT(*) > 1)) > > Which is not supported by PG > > So I wuld like to do this with a FOR EACH STATEMENT trigger and not by a > FOR EACH ROW. > > Here is the code I try : > > CREATE OR REPLACE FUNCTION F_UNIQUE_NULL_PRD_END() RETURNS trigger AS > $code$ > DECLARE n_rows integer; > BEGIN > SELECT COUNT(*) INTO n_rows > WHERE EXISTS(SELECT 0 > FROM T_PRODUIT_DISPO_PDD > WHERE PRD_ID IN(SELECT NEW.PRD_ID > FROM NEW) AS T > AND PDD_END IS NULL > GROUP BY PRD_ID > HAVING COUNT(*) > 1); > IF ( n_rows IS NOT NULL ) > THEN RAISE EXCEPTION 'Violation de la contrainte d''unicité sur le > couple de colonne PRD_ID + PDD_FIN'; > ROLLBACK TRANSACTION; > END IF; > RETURN NULL; > END > $code$ LANGUAGE 'plpgsql' VOLATILE > > Which produce an error ! > > Of course I can do that with a FOR EACH STATEMENT like this one : > > CREATE OR REPLACE FUNCTION F_UNIQUE_NULL_PRD_FIN() RETURNS trigger AS > $code$ > DECLARE n_rows integer; > BEGIN > SELECT 1 INTO n_rows > WHERE EXISTS(SELECT 0 > FROM T_PRODUIT_DISPO_PDD > WHERE PRD_ID = NEW.PRD_ID > AND PDD_FIN IS NULL > GROUP BY PRD_ID > HAVING COUNT(*) > 1); > IF ( n_rows IS NOT NULL ) > THEN RAISE EXCEPTION 'Violation de la contrainte d''unicité sur le > couple de colonne PRD_ID + PDD_FIN'; > ROLLBACK TRANSACTION; > END IF; > RETURN NULL; > END > $code$ LANGUAGE 'plpgsql' VOLATILE > > CREATE TRIGGER E_IU_PRD > AFTER INSERT OR UPDATE > ON T_PRODUIT_DISPO_PDD > FOR EACH ROW EXECUTE PROCEDURE F_UNIQUE_NULL_PRD_FIN(); > > > But it is absolutly not that I Want > > Thanks > > > -- > Frédéric BROUARD - expert SGBDR et SQL - MVP SQL Server - 06 11 86 40 66 > Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com > Enseignant Arts & Métiers PACA, ISEN Toulon et CESI/EXIA Aix en Provence > Audit, conseil, expertise, formation, modélisation, tuning, optimisation > *** http://www.sqlspot.com * > > > -- > Sent via pgsql-sql mailing list ([email protected]) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql > -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
