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 this 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 pasisuser;