Trigger functions with dynamic SQL - Mailing list pgsql-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-----