Problem while logging primary key of updated field. - Mailing list pgsql-general

From Gurunandan R. Bhat
Subject Problem while logging primary key of updated field.
Date
Msg-id Pine.LNX.4.33.0109220139470.1326-100000@suman.greenfields.universe
Whole thread Raw
In response to Problem: Failed Make on Linux Pgres v. 7.1.2  (R Talbot <rjtalbo@attglobal.net>)
List pgsql-general
Hi,

I want to log the primary key of a table whenever a new row is inserted.
To achieve this, I created the following function as trigger on update.
However I get the following error on inserts:

ERROR:  NEW used in non-rule query

Here is my function:
The first select inside the function body gives me the name of the primary
key field.

-----------------------------------------------------------------------
create function log_insert() returns opaque as '
       declare
       currtime timestamp := ''now'';
       pkeyname name;
       query text;
       begin
       select into pkeyname c.attname
       from    pg_class a,
           pg_index b,
           pg_attribute c
       where
         a.relname = TG_RELNAME and
         a.oid = b.indrelid and
         a.oid = c.attrelid and
         b.indkey[0] = c.attnum and
         b.indisprimary=''t'';
       query :=  ''insert into logtable (pkvalue, tablename, time)
             values (NEW.'' ||
             quote_ident(pkeyname) ||
             '', '' ||
             quote_ident(TG_RELNAME) ||
             '', '' ||
             quote_ident(currtime) ||
             '');'';
       execute query;
       return null;
       end;
' language 'plpgsql';
----------------------------------------------------------------------

I would be extremely grateful for any help. I might add that when I print
the dynamic query with a "raise notice" the query looks fine.

Thanks in advance

Gurunandan


pgsql-general by date:

Previous
From: Tom Lane
Date:
Subject: Re: Are duplicated OIDs troublesome?
Next
From: Chris
Date:
Subject: Re: creating "user" table