Recursive update
От | Thomas Braad Toft |
---|---|
Тема | Recursive update |
Дата | |
Msg-id | 41D47E5B.6000505@magicx.dk обсуждение исходный текст |
Список | pgsql-general |
Hi, I'm doing some PL/pgSQL programming at work and today I realized a small glitch in my application design. The following recreates my problem... I have two tables with triggers on each: CREATE TABLE "public"."tableone" ( "id" SERIAL, "columnone" VARCHAR(64), "columntwo" VARCHAR(64), "checkfield" BOOLEAN, CONSTRAINT "tableone_pkey" PRIMARY KEY("id") ) WITH OIDS; CREATE TRIGGER "tableone_update" BEFORE UPDATE ON "public"."tableone" FOR EACH ROW EXECUTE PROCEDURE "public"."tableone_update"(); CREATE TABLE "public"."tabletwo" ( "id" SERIAL, "name" VARCHAR(64), CONSTRAINT "tabletwo_pkey" PRIMARY KEY("id") ) WITH OIDS; CREATE TRIGGER "tabletwo_insert" BEFORE INSERT ON "public"."tabletwo" FOR EACH ROW EXECUTE PROCEDURE "public"."tabletwo_insert"(); Then I have the trigger functions: CREATE OR REPLACE FUNCTION "public"."tableone_update" () RETURNS trigger AS' begin if ((new.columntwo!=old.columntwo) OR (old.columntwo is null AND new.columntwo is not null)) then return new; end if; -- Call the "insert function". insert into tabletwo (name) values (''inserted by function insert_into_tabletwo''); return new; end; 'LANGUAGE 'plpgsql' IMMUTABLE CALLED ON NULL INPUT SECURITY INVOKER; CREATE OR REPLACE FUNCTION "public"."tabletwo_insert" () RETURNS trigger AS' begin if exists (select * from tableone where (checkfield=false OR checkfield is null) and id=1) then update tableone set columntwo=''updated by insert in tabletwo'',checkfield=true WHERE id=1; end if; return new; end; 'LANGUAGE 'plpgsql' IMMUTABLE CALLED ON NULL INPUT SECURITY INVOKER; Now I do the following: INSERT INTO tableone (id,columnone, columntwo,checkfield) VALUES (1,'from initial insert','from initial insert',false); UPDATE tableone set columnone='updated by me' WHERE id=1; The content of tableone is then: id | columnone | columntwo | checkfield ----+---------------------+-------------------------------+------------ 1 | from initial insert | updated by insert in tabletwo | true I believe I'm having the same issue as in this thread: http://groups-beta.google.com/group/comp.databases.postgresql.general/browse_thread/thread/5ead4260393ecd57/37c8b4a1b7562221?q=recursive+update+postgresql&_done=%2Fgroups%3Fq%3Drecursive+update+postgresql%26hl%3Den%26lr%3D%26client%3Dfirefox-a%26rls%3Dorg.mozilla:en-US:official%26sa%3DN%26tab%3Dwg%26&_doneTitle=Back+to+Search&&d#37c8b4a1b7562221 I'm trying to do an update on a table which results in another subsequent update of the same table. Only the second (subsequent) is actually executed on the table. I'm not sure I understand why this is not allowed, because I can see many cases where recursive updates would be a very nice thing to have available. Is this maybe a thing which should be raised for the PostgreSQL developer team? Thanks in advance and happy new year! -- Thomas Braad Toft
В списке pgsql-general по дате отправления: