Thread: Problem while logging primary key of updated field.

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