Re: Trigger functions with dynamic SQL - Mailing list pgsql-sql

-----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-----