Skip site navigation (1) Skip section navigation (2)

Re: Trouble with plpgsql generic trigger function using

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 (view raw or flat)
Thread:
Lists: pgsql-generalpgsql-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

In response to

pgsql-novice by date

Next:From: Alban HertroysDate: 2006-11-01 09:03:53
Subject: Re: Trouble with plpgsql generic trigger function using
Previous:From: Lenorovitz, JoelDate: 2006-10-31 20:20:48
Subject: Trouble with plpgsql generic trigger function using special variables

pgsql-general by date

Next:From: Richard HuxtonDate: 2006-11-01 08:45:28
Subject: Re: Compiling/Installing as a non-admin user
Previous:From: Alvaro HerreraDate: 2006-11-01 08:12:33
Subject: Re: Compiling/Installing as a non-admin user

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group