Re: [GENERAL] Trouble with plpgsql generic trigger function using

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 //