Thread: Trouble with plpgsql generic trigger function using special variables

I'd like to create a trigger function whose use can extend to multiple
tables by employing the special variables available (e.g., TG_RELNAME).
Below is a simple version of such a function that ought to prevent
insertion of greater than 4 total records in the table that calls it.
I'm not sure that I'm using or dereferencing the trigger variables
correctly, however, particularly in the query.  I have tried many
syntax, type casting, and alternate variable assignment variations, but,
aside from parsing successfully, this code does not seem to work as
intended.    Can somebody correct this specific example to have it work
properly and/or further explain how to use these variables?  Any advice
on outputting the values of the variables to the console for inspection
during testing would be welcome as well (RAISE EXCEPTION doesn't allow a
variable value in the message string, plus it seems a little harsh).

Thanks,
JL

CREATE OR REPLACE FUNCTION trigger_fxn() RETURNS TRIGGER AS $$
    BEGIN
        IF ((TG_OP = 'INSERT') AND (TG_WHEN = 'BEFORE')) THEN
            IF (SELECT COUNT(*) FROM text(TG_RELNAME)) < 4
THEN
                RETURN NEW;
            ELSE
                RETURN NULL;
            END IF;
        END IF;
    END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER test_bi BEFORE INSERT ON test
    FOR EACH ROW EXECUTE PROCEDURE trigger_fxn();

Lenorovitz, Joel wrote:
> I'd like to create a trigger function whose use can extend to multiple
> tables by employing the special variables available (e.g., TG_RELNAME).
[snip]
> Any advice
> on outputting the values of the variables to the console for inspection
> during testing would be welcome as well (RAISE EXCEPTION doesn't allow a
> variable value in the message string, plus it seems a little harsh).

You can embed variables into RAISEd messages. You'd normally use RAISE
NOTICE for this sort of thing.
   RAISE <LEVEL> 'My variables % and %', var1, var2;

> CREATE OR REPLACE FUNCTION trigger_fxn() RETURNS TRIGGER AS $$
>     BEGIN
>         IF ((TG_OP = 'INSERT') AND (TG_WHEN = 'BEFORE')) THEN
>             IF (SELECT COUNT(*) FROM text(TG_RELNAME)) < 4

This won't work, because plpgsql pre-plans queries. You'll need to use
the EXECUTE facility:
   EXECUTE 'SELECT COUNT(*) FROM ' || TG_RELNAME;
You'll want the FOR ... IN ... EXECUTE form to read a value into a
variable. See "Looping through query results" in the manual for details.

Some of the other procedural languages treat queries as text anyway, so
they'll let you do what you're trying.

--
   Richard Huxton
   Archonet Ltd

Lenorovitz, Joel wrote:
> I'd like to create a trigger function whose use can extend to multiple
> tables by employing the special variables available (e.g., TG_RELNAME).
> Below is a simple version of such a function that ought to prevent
> insertion of greater than 4 total records in the table that calls it.
> I'm not sure that I'm using or dereferencing the trigger variables
> correctly, however, particularly in the query.  I have tried many
> syntax, type casting, and alternate variable assignment variations, but,
> aside from parsing successfully, this code does not seem to work as
> intended.    Can somebody correct this specific example to have it work

You need to use EXECUTE to execute your dynamic query. You can't just
put a string in a query and have it be handled as an identifier.

> during testing would be welcome as well (RAISE EXCEPTION doesn't allow a
> variable value in the message string, plus it seems a little harsh).

Not true, and you don't need to raise an exception; a notice'd do just fine.

Try this:
RAISE NOTICE 'Trigger fired on table %', TG_RELNAME;

> CREATE OR REPLACE FUNCTION trigger_fxn() RETURNS TRIGGER AS $$
>     BEGIN
>         IF ((TG_OP = 'INSERT') AND (TG_WHEN = 'BEFORE')) THEN
>             IF (SELECT COUNT(*) FROM text(TG_RELNAME)) < 4

You'll want to DECLARE an integer variable and use SELECT INTO with it.
And EXECUTE, as mentioned.


--
Alban Hertroys
alban@magproductions.nl

magproductions b.v.

T: ++31(0)534346874
F: ++31(0)534346876
M:
I: www.magproductions.nl
A: Postbus 416
    7500 AK Enschede

// Integrate Your World //