Thread: SQL function triggers
I would like to write a function as a set of SQL statements, and then use that function in a trigger. However, since triggers require a return type of opaque, and SQL functions cannot return type opaque, this doesn't look possible. Am I missing something? The SQL that I would expect to do this is below. Incidentally, on an insert, would the trigger fire once, twice, or infinitely? create table test( a integer, b integer ) without oids; create function test_func( test.a%TYPE ) RETURNS ????? AS 'update test set b = a where a = $1;' language SQL with (isstrict); create trigger test_trig after update on test for each row execute procedure test_func( a );
Brian Blaha <bblaha@umr.edu> writes: > I would like to write a function as a set of SQL statements, and then > use that function > in a trigger. However, since triggers require a return type of opaque, > and SQL functions > cannot return type opaque, this doesn't look possible. You could call a SQL function from a trigger, but it can't be a trigger itself; at present triggers have to be in C, plpgsql, or pltcl (maybe plpython? Not sure about that one). For what you are doing (transferring info from one table to another) I suspect a rule might work better than a trigger anyway. regards, tom lane
Brian Blaha wrote: > > I would like to write a function as a set of SQL statements, and then > use that function > in a trigger. However, since triggers require a return type of opaque, > and SQL functions > cannot return type opaque, this doesn't look possible. Am I missing > something? The SQL > that I would expect to do this is below. Incidentally, on an insert, > would the trigger fire > once, twice, or infinitely? > > create table test( > a integer, > b integer > ) without oids; > > create function test_func( test.a%TYPE ) > RETURNS ????? > AS > 'update test set b = a where a = $1;' > language SQL > with (isstrict); > > create trigger test_trig after update > on test for each row > execute procedure test_func( a ); If you intend to modify the updated row only with information available in the row itself (as in your example), a BEFORE trigger in PL/pgSQL modifying and returning NEW should get the job done. Jan -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #================================================== JanWieck@Yahoo.com #