Skip site navigation (1) Skip section navigation (2)

Re: Trigger functions with dynamic SQL

From: Andreas Haumer <andreas(at)xss(dot)co(dot)at>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: pgsql-sql(at)postgresql(dot)org
Subject: Re: Trigger functions with dynamic SQL
Date: 2004-07-26 15:03:44
Message-ID: 41051D50.5060409@xss.co.at (view raw or flat)
Thread:
Lists: pgsql-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-----


In response to

pgsql-sql by date

Next:From: Karsten HilbertDate: 2004-07-26 20:34:41
Subject: Re: surrogate key or not?
Previous:From: sadDate: 2004-07-26 11:13:37
Subject: Re: surrogate key or not?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group