From: | Richard Huxton <dev(at)archonet(dot)com> |
---|---|
To: | "Lenorovitz, Joel" <Joel(dot)Lenorovitz(at)usap(dot)gov> |
Cc: | pgsql-novice(at)postgresql(dot)org, pgsql-general(at)postgresql(dot)org |
Subject: | Re: Trouble with plpgsql generic trigger function using |
Date: | 2006-11-01 08:32:50 |
Message-ID: | 45485BB2.6040208@archonet.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general pgsql-novice |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Richard Huxton | 2006-11-01 08:45:28 | Re: Compiling/Installing as a non-admin user |
Previous Message | Alvaro Herrera | 2006-11-01 08:12:33 | Re: Compiling/Installing as a non-admin user |
From | Date | Subject | |
---|---|---|---|
Next Message | Alban Hertroys | 2006-11-01 09:03:53 | Re: Trouble with plpgsql generic trigger function using |
Previous Message | Lenorovitz, Joel | 2006-10-31 20:20:48 | Trouble with plpgsql generic trigger function using special variables |