Re: 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).
[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