(trigger function) -> ERROR: NEW used in non-rule query

From: Terence Kearns <terencek(at)isd(dot)canberra(dot)edu(dot)au>
To: pgsql-sql(at)postgresql(dot)org
Subject: (trigger function) -> ERROR: NEW used in non-rule query
Date: 2003-07-18 08:22:16
Message-ID: 3F17AE38.5020302@cts.canberra.edu.au
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

I'm writing a trigger which wants to reference the pre-defined NEW record.

I can do this
idval := NEW.blah;

This works fine!!!

What I really need to do is
idval := NEW.(quote_ident(TG_ARGV[3]));
or this
idval := NEW.(TG_ARGV[3]);

unfortunately this returns
"ERROR: NEW used in non-rule query"

I've also tried using
EXECUTE ''SELECT NEW.'' || quote_ident(TG_ARGV[3]);
(to test if I can use a FOR-IN-EXECUTE to extract it)

and it produces exactly the same error. Obviously NEW goes out of scope
for some reason. I can understand the case with EXECUTE but the other
statements should have worked :(

This is very frustrating because it is the *ONLY* thing standing in the
way of me solving a larger problem (which I will use to repsond to
another thread in this forum).

Does anyone know how to access an arbitarily specified field of the NEW
record in a trigger function. I need this because arguments to a
function in the trigger definition cannot be NEW.blah, they have to be
static/literals (which is likely to be for the same reason that NEW goes
out of scope any time I try to do something dynamic when evaluating a
field on it).

FYI: ref doco at
http://www.postgresql.org/docs/7.3/interactive/plpgsql-trigger.html

using pg 7.3.3

--
Terence Kearns ~ ph: +61 2 6201 5516
IT Database/Applications Developer
Enterprise Information Systems
Client Services Division
University of Canberra
www.canberra.edu.au

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Richard Huxton 2003-07-18 09:03:42 Re: (trigger function) -> ERROR: NEW used in non-rule query
Previous Message Richard Huxton 2003-07-18 07:06:58 Re: Table Partitioning and Rules