Thread: SQL functions and triggers?
Hi all,
In the past, when writing trigger functions, I’ve always used pl/pgsql without giving it a second thought. Today I was
modifyinga database creation script that was originally intended for Firebird to work with Postgres and the example
triggerprocedures in there were very close to pure SQL.
Hence, I started rewriting them as SQL functions, but is that really possible? The return-type for trigger functions is
usually‘returns trigger’, which I don’t know how to translate to SQL. Next to that, what should the SQL statement
insidethe function be returning?
This got a bit puzzling and now I wonder whether this is possible at all? And if so, what’s the correct syntax?
Say I have the below, how to actually return a value of type trigger?:
create or replace function pUpdateFoo()
returns trigger
as $$
update Bar
set baz = baz + NEW.feep - OLD.feep
where foo = NEW.foo;
$$;
create trigger tUpdateFoo
after update on Foo
for each row
execute procedure pUpdateFoo();
Glad to finally have a proper opportunity to apply PG again - it’s been too long!
Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.
On Tue, 25 Nov 2014 21:41:12 +0100 Alban Hertroys <haramrae@gmail.com> wrote: > Hi all, > > In the past, when writing trigger functions, I?ve always used pl/pgsql without giving it a second thought. Today I wasmodifying a database creation script that was originally intended for Firebird to work with Postgres and the example triggerprocedures in there were very close to pure SQL. > > Hence, I started rewriting them as SQL functions, but is that really possible? The return-type for trigger functions isusually ?returns trigger?, which I don?t know how to translate to SQL. Next to that, what should the SQL statement insidethe function be returning? Last I checked, triggers had to be written in a language that could "return trigger". A look at the 9.4 documentation seems to suggest that this is still a requirement, although I didn't find a definitive statement to that effect. Since stored procedures written in SQL are unable to return the trigger type, it's not currently possible to write triggers in SQL. -- Bill Moran I need your help to succeed: http://gamesbybill.com
Alban Hertroys <haramrae@gmail.com> writes:
> In the past, when writing trigger functions, I’ve always used pl/pgsql without giving it a second thought. Today I
wasmodifying a database creation script that was originally intended for Firebird to work with Postgres and the example
triggerprocedures in there were very close to pure SQL.
> Hence, I started rewriting them as SQL functions, but is that really
> possible?
No, nobody's ever tried to make that work. It could probably be done
with sufficiently many round tuits, but it's not clear that there's
any benefit that would justify the work. Surely dropping some SQL
commands into plpgsql isn't very hard ...
regards, tom lane
> On 25 Nov 2014, at 22:24, Tom Lane <tgl@sss.pgh.pa.us> wrote: > > Alban Hertroys <haramrae@gmail.com> writes: >> In the past, when writing trigger functions, I’ve always used pl/pgsql without giving it a second thought. Today I wasmodifying a database creation script that was originally intended for Firebird to work with Postgres and the example triggerprocedures in there were very close to pure SQL. > >> Hence, I started rewriting them as SQL functions, but is that really >> possible? > > No, nobody's ever tried to make that work. It could probably be done > with sufficiently many round tuits, but it's not clear that there's > any benefit that would justify the work. Surely dropping some SQL > commands into plpgsql isn't very hard … It isn’t. I was just wondering whether I was missing something obvious to make an SQL function return a trigger type value.I didn’t think there was, but it never hurts to ask ;) Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll find there is no forest.