Re: Trigger functions with dynamic SQL

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Hi!

I have solved the problem!

Tom Lane wrote:
> Andreas Haumer <andreas(at)xss(dot)co(dot)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(at)xss(dot)co(dot)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-----