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 modifying a database creation script that was originally intended for Firebird to work with Postgres and the example trigger procedures 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 inside the 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.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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 was modifying a database creation script that was originally intended for Firebird to work with Postgres and the example trigger procedures 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 inside the 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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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 was modifying a database creation script that was originally intended for Firebird to work with Postgres and the example trigger procedures 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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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 was modifying a database creation script that was originally intended for Firebird to work with Postgres and the example trigger procedures 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.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general