Thread: Problem with triggers and cursors

Hi all.

I'm doing my first steps with Postgres and triggers and run into a problem.

I want to keep a log for all inserts, updates and deletes for a bunch of
tables.

All rows in the tables have unique keys which consist of a serverid and
a running number created by a sequence for the table which fires the
trigger.

The basic steps are as follows:
INSERT:
  - Get the serverid part of the unique key from the view
    v_systemparameter.
  - Get the next number from the sequence for the table which fired the
    trigger.
  - Insert a new row to the log table with the name for the table which
    fired the trigger.
  - Edit the new row for the original table and set the fields
    pkey_server and pkey_id for the unique key.

UPDATE:
  - Insert a new row to the log table with the unique key of the
    original row.

DELETE:
  - Insert a new row to the log table with the unique key of the
    original row.


The log table has the following layout:

CREATE TABLE notifyinfos
(
  tablename varchar(25) NOT NULL,
  pkey_server varchar(100),
  pkey_id numeric (20, 0),
  aktion varchar(10) NOT NULL,
  PRIMARY KEY (tablename, pkey_server, pkey_id)
)
WITHOUT OIDS;


I use a trigger on the tables as defined below.

When i try to add the trigger to the database i get the following error:

ERROR:  syntax error at "CURSOR"
DETAIL:  Expected FOR to open a reference cursor.
KONTEXT:  compile of PL/pgSQL function "notifytrigger" near line 17

Can someone show me the cause of this error?

One other questions:
Is using a cursor the preferred way to fetch data from another table?

I'm using Postgres 8.1 under Windows.


Any advice is very welcome.

Thanks in advanced.


Karsten


CREATE OR REPLACE FUNCTION notifytrigger()
  RETURNS "trigger" AS
$BODY$
DECLARE
  cSrvID CURSOR FOR SELECT * FROM v_systemparameter WHERE
systemparameter = 'serverid';
  rSrvID RECORD;
  cRowID refcursor;
  rRowID RECORD;
begin

  IF (TG_OP = 'INSERT') THEN
    OPEN cSrvID;
    FETCH cSrvID INTO rSrvID;
    CLOSE cSrvID;
    OPEN cRowID CURSOR FOR SELECT nextval('seq_' || TG_RELNAME);
    FETCH cRowID INTO rRowID;
    CLOSE cRowID;
    INSERT INTO notifyinfos (TG_RELNAME, rSrvID.parameterwert,
rRowID.nextval, 'INSERT');
    NEW.key_server := rSrvID.parameterwert;
    NEW.key_id := rRowID.nextval;
    RETURN NEW;
  END IF;

  IF (TG_OP = 'UPDATE') THEN
    INSERT INTO notifyinfos (TG_RELNAME, OLD.pkey_server, OLD.pkey_id,
'UPDATE');
    RETURN NEW;
  END IF;

  IF (TG_OP = 'DELETE') THEN
    INSERT INTO notifyinfos (TG_RELNAME, OLD.pkey_server, OLD.pkey_id,
'DELETE');
    RETURN OLD;
  END IF;

end;
$BODY$
  LANGUAGE 'plpgsql' IMMUTABLE;
ALTER FUNCTION notifytrigger() OWNER TO dbuser;

Karsten Hoffrath <khoffrath@khoffrath.de> writes:
> Is using a cursor the preferred way to fetch data from another table?

It's the hard way, not to mention that you've not got the syntax of OPEN
quite right.

Instead of

> DECLARE
>   cSrvID CURSOR FOR SELECT * FROM v_systemparameter WHERE
> systemparameter = 'serverid';
>   rSrvID RECORD;
>   cRowID refcursor;
>   rRowID RECORD;
> ...
>     OPEN cSrvID;
>     FETCH cSrvID INTO rSrvID;
>     CLOSE cSrvID;
>     OPEN cRowID CURSOR FOR SELECT nextval('seq_' || TG_RELNAME);
>     FETCH cRowID INTO rRowID;
>     CLOSE cRowID;

I'd just do

DECLARE
  rSrvID RECORD;
  rowID bigint; -- or integer, it's not clear which you want
...
    SELECT * INTO rSrvID FROM v_systemparameter WHERE systemparameter = 'serverid';
    rowID := nextval('seq_' || TG_RELNAME);
...


            regards, tom lane