Recursive update

Recursive update

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