Problem while logging primary key of updated field.
От | Gurunandan R. Bhat |
---|---|
Тема | Problem while logging primary key of updated field. |
Дата | |
Msg-id | Pine.LNX.4.33.0109222253580.932-100000@suman.greenfields.universe обсуждение исходный текст |
Список | 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 по дате отправления: