From: | Thomas Braad Toft <pgsql-general(at)magicx(dot)dk> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Recursive update |
Date: | 2004-12-30 22:16:59 |
Message-ID: | 41D47E5B.6000505@magicx.dk |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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
From | Date | Subject | |
---|---|---|---|
Next Message | Miles Keaton | 2004-12-30 22:42:59 | Re: possible to DELETE CASCADE? |
Previous Message | Derik Barclay | 2004-12-30 21:59:06 | Re: Making a varchar bigger |