Thread: Trigger functions with dynamic SQL
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Hi!
I could use some help in writing PL/PGSQL trigger functions
with dynamic SQL statements. The problem is not exactly an easy
one but I hope it is of general interest to this list :-)
I'm currently working on a database with several temporal
tables. All temporal tables have some attributes which
define the validity of a tuple in the time dimension.
(For more info about temporal tables please take a look
at "Developing Time-Oriented Database Applications in SQL"
by Richard T. Snodgrass, for example)
Example for temporal tables (reduced to the columns necessary):
CREATE TABLE t_role ( id serial NOT NULL, begin_val timestamp DEFAULT now() NOT NULL, end_val timestamp DEFAULT
'infinity'NOT NULL, name character varying(50) NOT NULL, CONSTRAINT ck_role_begin_end CHECK (begin < end)
);
CREATE TABLE t_person ( id serial NOT NULL, begin_val timestamp DEFAULT now() NOT NULL, end_val timestamp DEFAULT
'infinity'NOT NULL, username character varying(50) NOT NULL, role integer, CONSTRAINT ck_person_begin_end CHECK (begin
<end)
);
Columns "begin_val" and "end_val" define the interval of
validity for the rows in the tables (I my real application
I use table inheritance for all temporal tables, but I do
not want to make the example more complex than necessary)
Al time intervals are "closed-open" intervals. Gaps in history
are not allowed.
With this definition I can have the following rows in the
table "t_role":
id begin_val end_val name
============================================================
1 2004-01-01 12:00:00 2004-01-03 13:00:00 sysadmin
1 2004-01-03 13:00:00 infinity System Administrator
2 2004-01-01 12:00:00 infinity Guest
Table "t_person" could have the following contents:
id begin_val end_val username role
=================================================================
1 2004-01-02 10:00:00 infinity andreas 1
2 2004-01-02 10:03:00 infinity guest 2
I have set up views for all temporal tables to provide
easy access to the current snapshot (including rules for
easy data modifications) as well as triggers to maintain
primary key as well as referential integrity constraints.
For example, a current foreign key trigger function may look
like this:
CREATE FUNCTION func_fk_person_role() RETURNS "trigger" AS '
BEGIN IF EXISTS ( SELECT * FROM t_person AS SRC WHERE SRC.end_val = ''infinity''
AND NOT EXISTS ( SELECT * FROM t_role AS DST
WHERESRC.role = DST.id AND DST.end_val = ''infinity'') ) THEN
RAISEEXCEPTION ''FK_person_role referential integrity violation - key referenced from temporal table t_person not found
intemporal table t_role''; END IF;
RETURN new;
END;
' LANGUAGE plpgsql;
The triggers on t_role and t_person are then defined as follows:
CREATE TRIGGER trigger_fk_role_person AFTER DELETE OR UPDATE ON t_role FOR EACH ROW EXECUTE PROCEDURE
func_fk_person_role();
CREATE TRIGGER trigger_fk_person_role AFTER INSERT OR UPDATE ON t_person FOR EACH ROW EXECUTE PROCEDURE
func_fk_person_role();
As I have lots of temporal tables and all foreign key
trigger definitions follow the same pattern, I would like
to write a general trigger function which gets specialized by
use of arguments to the trigger function.
I want to have a single, general foreign key trigger function
parametrized with variables "referer_tab", "referer_col",
"referenced_tab" and "referenced_col" like this (note:
the example is NOT working as it is!):
CREATE FUNCTION func_fk_temporal () RETURNS "trigger" AS '
BEGIN IF EXISTS ( SELECT * FROM referer_tab WHERE referer_tab.end_val = ''infinity''
AND NOT EXISTS ( SELECT * FROM referenced_tab
WHERE referer_tab.referer_col = referenced_tab.referenced_col AND
referenced_tab.end_val= ''infinity'') ) THEN RAISE EXCEPTION ''referential integrity violation - key
referencedfrom temporal table referer_tab not found in temporal table referenced_tab''; END IF;
RETURN new;
END;
' LANGUAGE plpgsql;
and write the trigger definition like this:
CREATE TRIGGER trigger_fk_role_person AFTER DELETE OR UPDATE ON t_role FOR EACH ROW EXECUTE PROCEDURE
func_fk_temporal(t_person,role, t_role, id);
CREATE TRIGGER trigger_fk_person_role AFTER INSERT OR UPDATE ON t_person FOR EACH ROW EXECUTE PROCEDURE
func_fk_temporal(t_person,role, t_role, id);
That way I could have one single FK trigger function for all my
temporal tables.
The manual for PostgreSQL-7.4.2 says the following in chapter 35,
"Triggers":
[...]
When a trigger is being defined, arguments can be specified for it.
The purpose of including arguments in the trigger definition is to
allow different triggers with similar requirements to call the same
function. As an example, there could be a generalized trigger function
that takes as its arguments two column names and puts the current user
in one and the current time stamp in the other.
[...]
This is exactly what I want to do. But _how_ can it be done?
It seems I can't just use TG_ARGV[0] as variable holding the name
of the referer table or TG_ARGV[1] as referer column name in the
SELECT statement described above (the function will badly fail
if you do)
It seems I would have to use EXECUTE on dynamically constructed
PL/PGSQL statements in order to have my trigger function recognize
the parameters given to the trigger in TG_ARGV[]
I tried lot of different versions but all I get are syntax errors.
I tried up to zillions of quotation marks in my dynamically
generated EXECUTE statement but I can't seem to get the statements
right :-(
So, IMHO all this can be reduced to the following question:
how can I use trigger function arguments inside of PL/PGSQL
trigger functions to write generalized SELECT statements (or
any other otherwise valid PL/PGSQL statement)?
I couldn't find an example which fits for my problem so far.
Can it be done anyway? (the manual says so, but lacks a proof :-)
Any real world example anyone?
Any help is appreciated!
- - andreas
- --
Andreas Haumer | mailto:andreas@xss.co.at
*x Software + Systeme | http://www.xss.co.at/
Karmarschgasse 51/2/20 | Tel: +43-1-6060114-0
A-1100 Vienna, Austria | Fax: +43-1-6060114-71
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.1 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org
iD8DBQFBAnKqxJmyeGcXPhERAu61AKCU9MYIpf3PeVLtGH4oV+SIrSl9agCfQ2JR
RWt5Qbwt6OMPEjmeVlk0r2c=
=cDui
-----END PGP SIGNATURE-----
Andreas Haumer <andreas@xss.co.at> writes:
> It seems I would have to use EXECUTE on dynamically constructed
> PL/PGSQL statements in order to have my trigger function recognize
> the parameters given to the trigger in TG_ARGV[]
Yup, that's exactly right. plpgsql isn't designed for this; it's
designed for situations where it can pre-plan and cache plans for
queries, and dynamically-inserted table and column names would just
break that completely. So you have to fall back to the mechanisms
for fully general constructed-on-the-fly queries, which work but
are a bit painful to use.
You might want to look at pltcl instead, which is much friendlier
to dynamically generated queries (since that's the only way it
does things). Of course, if you've never used Tcl there'll be
a bit of a learning curve :-(
regards, tom lane
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 Hi! Many thanks for your reply! Tom Lane wrote: > Andreas Haumer <andreas@xss.co.at> writes: > >>It seems I would have to use EXECUTE on dynamically constructed >>PL/PGSQL statements in order to have my trigger function recognize >>the parameters given to the trigger in TG_ARGV[] > > > Yup, that's exactly right. plpgsql isn't designed for this; it's Ok, that's what I thought. Thanks for confirmation. > designed for situations where it can pre-plan and cache plans for > queries, and dynamically-inserted table and column names would just > break that completely. So you have to fall back to the mechanisms > for fully general constructed-on-the-fly queries, which work but > are a bit painful to use. > "a bit painful" is the understatement of the year! :-) I just can't figure out where and how many quotation marks I have to place in my function. Some examples would be helpful (the examples in the manual are quite simple and always end where it begins to become interesting for me...) Also: what is the performance impact of using EXECUTE and constructed-on-the-fly queries? > You might want to look at pltcl instead, which is much friendlier > to dynamically generated queries (since that's the only way it > does things). Of course, if you've never used Tcl there'll be > a bit of a learning curve :-( > My experience with Tcl is almost zero. :-( What about writing trigger functions in C? It should be quite easy to dynamically create the SQL statements needed here and it seems I can access the trigger arguments through the tg_trigger->tgargs array. Would this be a better way to go? (faster code, but perhaps painful to write as well, too) I found some examples in the PostgreSQL sources under .../contrib/spi/ I think I will have to take a deeper look at that... - - andreas - -- Andreas Haumer | mailto:andreas@xss.co.at *x Software + Systeme | http://www.xss.co.at/ Karmarschgasse 51/2/20 | Tel: +43-1-6060114-0 A-1100 Vienna, Austria | Fax: +43-1-6060114-71 -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.2.1 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQFBAoRMxJmyeGcXPhERAg/OAJ9Mg5Ecp1urDhJAnCQ+k8A9N7sv+QCfe0w9 Cdlbkwt0QITR2bU+lIO0TtU= =5tJf -----END PGP SIGNATURE-----
Andreas Haumer <andreas@xss.co.at> writes:
> I just can't figure out where and how many quotation marks
> I have to place in my function.
It's messy all right. The "dollar quoting" feature in 7.5 should make
it a lot less painful, since you can stop having to double and re-double
quote marks. If you're interested in using beta-quality code, you can
have that today. An example would go something like
CREATE FUNCTION mytrigger() RETURNS trigger AS $PROC$ DECLARE ... EXECUTE $$ SELECT ... FROM $$ || tgargv[0] || $$
WHEREcol = 'key' $$; ... END
$PROC$ LANGUAGE plpgsql;
Here I've used minimal dollar quotes ($$) for the literal constant parts
of the EXECUTE'd query, which allows me not to have to double the quote
marks that I actually want in the query text (the ones around "key").
And I used dollar quotes with a label ($PROC$) at the outermost level
to quote the entire function body, so that there's no conflict with the
embedded dollar quotes. In 7.4 the same EXECUTE command would have to
be written
EXECUTE '' SELECT ... FROM '' || tgargv[0] || '' WHERE col = ''''key'''' '';
which is already getting painful, and more complex cases get rapidly
worse. With dollar quoting you can write the constant parts of your
query the same way you normally would.
> What about writing trigger functions in C?
Seems like the hard way to me. I doubt it would be better than plpgsql,
but it's all a matter of opinion...
regards, tom lane
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Hi!
I have solved the problem!
Tom Lane wrote:
> Andreas Haumer <andreas@xss.co.at> writes:
>
>>I just can't figure out where and how many quotation marks
>>I have to place in my function.
>
>
> It's messy all right. The "dollar quoting" feature in 7.5 should make
> it a lot less painful, since you can stop having to double and re-double
> quote marks. If you're interested in using beta-quality code, you can
> have that today. An example would go something like
>
> CREATE FUNCTION mytrigger() RETURNS trigger AS $PROC$
> DECLARE
> ...
> EXECUTE $$ SELECT ... FROM $$ || tgargv[0] || $$ WHERE col = 'key' $$;
> ...
> END
> $PROC$ LANGUAGE plpgsql;
>
> Here I've used minimal dollar quotes ($$) for the literal constant parts
> of the EXECUTE'd query, which allows me not to have to double the quote
> marks that I actually want in the query text (the ones around "key").
> And I used dollar quotes with a label ($PROC$) at the outermost level
> to quote the entire function body, so that there's no conflict with the
Yes, this "dollar quoting" seems to make things a lot clearer!
> embedded dollar quotes. In 7.4 the same EXECUTE command would have to
> be written
>
> EXECUTE '' SELECT ... FROM '' || tgargv[0] || '' WHERE col = ''''key'''' '';
>
> which is already getting painful, and more complex cases get rapidly
> worse. With dollar quoting you can write the constant parts of your
> query the same way you normally would.
>
As I currently use 7.4 I had to stick with lots of quotation marks,
but I finally managed to get it right and it works!
My plpgsql generic trigger function for maintaining referential
integrity with temporal tables now looks as follows:
CREATE FUNCTION func_fk_temporal_trigger () RETURNS "trigger" AS '
DECLARE referer_tab text; referer_col text; referenced_tab text; referenced_col text; stmt varchar(4000); result
record;
BEGIN referer_tab := TG_ARGV[0]; referer_col := TG_ARGV[1]; referenced_tab := TG_ARGV[2]; referenced_col :=
TG_ARGV[3];
stmt := '' SELECT id FROM '' || quote_ident(referer_tab); stmt := stmt || '' WHERE '' || quote_ident(referer_tab) ||
''.bis= ''''infinity'''' ''; stmt := stmt || '' AND '' || quote_ident(referer_tab) || ''.'' || quote_ident(referer_col)
||'' IS NOT NULL''; stmt := stmt || '' AND NOT EXISTS (SELECT id FROM '' || quote_ident(referenced_tab); stmt := stmt
||'' WHERE '' || quote_ident(referer_tab) || ''.'' || quote_ident(referer_col) || '' = '' ||
quote_ident(referenced_tab)|| ''.'' || quote_ident(referenced_col); stmt := stmt || '' AND '' ||
quote_ident(referenced_tab)|| ''.bis = ''''infinity'''')'';
FOR result IN EXECUTE stmt LOOP RAISE EXCEPTION ''temporal table referential integrity violation - key referenced
from%.% not found in %.%'', referer_tab, referer_col, referenced_tab, referenced_col; END LOOP;
RETURN new;
END;
' LANGUAGE plpgsql;
And these are some of the trigger definitions which use this function:
CREATE TRIGGER trigger_fk_pns_ug AFTER INSERT OR UPDATE ON t_pns FOR EACH ROW EXECUTE PROCEDURE
func_fk_temporal_trigger('t_pns', 'ug', 't_ug', 'id');
CREATE TRIGGER trigger_fk_ug_pns AFTER DELETE OR UPDATE ON t_ug FOR EACH ROW EXECUTE PROCEDURE
func_fk_temporal_trigger('t_pns', 'ug', 't_ug', 'id');
CREATE TRIGGER trigger_fk_pnspar_pns AFTER INSERT OR UPDATE ON t_pnspar FOR EACH ROW EXECUTE PROCEDURE
func_fk_temporal_trigger('t_pnspar', 'pns', 't_pns', 'id');
CREATE TRIGGER trigger_fk_pnspar_par AFTER DELETE OR UPDATE ON t_pnspar FOR EACH ROW EXECUTE PROCEDURE
func_fk_temporal_trigger('t_pnspar', 'par', 't_par', 'id');
[...]
With this single generic trigger function I can now save myself
of writing (and maintaining) literally dozends of specialized
functions. From what I can say after a few tests it seems to
work quite fine!
Note that I use "FOR result IN EXECUTE stmt LOOP" to process the
results of the dynamic query. To avoid this, I first tried to put
the whole function body including the "RAISE EXCEPTION" statement
into the dynamic query, but it seems the plsql parser doesn't like
this.
May I suggest to put this or a similar example into the
PostgreSQL manual in chapter 35 (Triggers) and/or 37.6.4
(Executing Dynamic Commands)? I'm sure this will save
other peoples time (ok, one can also use google to find
this mail in the archives... ;-)
Regards,
- - andreas
- --
Andreas Haumer | mailto:andreas@xss.co.at
*x Software + Systeme | http://www.xss.co.at/
Karmarschgasse 51/2/20 | Tel: +43-1-6060114-0
A-1100 Vienna, Austria | Fax: +43-1-6060114-71
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.1 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org
iD8DBQFBBR1OxJmyeGcXPhERAo20AKDDv5pOi/3PMx1RhbKzQqIMv9rdOwCgvQIS
XAzqpB+j1i92ao0FHOkh/kY=
=0xfX
-----END PGP SIGNATURE-----