From: Andreas Haumer <andreas@xss.co.at>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-sql@postgresql.org
Subject: Re: Trigger functions with dynamic SQL
Date: Mon, 26 Jul 2004 17:03:44 +0200
Message-ID: <41051D50.5060409@xss.co.at> (raw)
In-Reply-To: <4810.1090685599@sss.pgh.pa.us>
References: <410272AD.9010100@xss.co.at>
<4370.1090681307@sss.pgh.pa.us>
<41028459.6030002@xss.co.at>
<4810.1090685599@sss.pgh.pa.us>
-----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-----
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: andreas@xss.co.at, tgl@sss.pgh.pa.us
Subject: Re: Trigger functions with dynamic SQL
In-Reply-To: <41051D50.5060409@xss.co.at>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox