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 appreciated.

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;

On Mon, 11 Sep 2006, Karsten Hoffrath wrote:

> 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?

http://www.postgresql.org/docs/8.1/interactive/plpgsql-cursors.html seems
to imply that you should not be using CURSOR in the open for query.

Instead of OPEN cRowID CURSOR FOR SELECT nextval('seq_' || TG_RELNAME);
it looks like the correct incantation would be OPEN cRowID FOR
SELECT nextval('seq_' || TG_RELNAME);

However, I'm not sure you really want a cursor in this case.

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

If you want to loop through many query results in pl/pgsql, you'd possibly
be better off using FOR recordvar IN query LOOP (see the pl/pgsql docs).

For getting a single value, you might be better off with a
variable and something like
SELECT INTO variable nextval('seq' || TG_RELNAME);

Hi Stephan,

thanks for your reply, it's working now.
Because i just want to fetch just one row i followed your suggestion to
use SELECT INTO and it works like a charm.

I append the working function in case someone should need it.


Best regards

Karsten



CREATE OR REPLACE FUNCTION notifytrigger()
  RETURNS "trigger" AS
$BODY$
DECLARE
  SrvID varchar(50);
begin

  IF (TG_OP = 'INSERT') THEN

    SELECT INTO SrvID parameterwert FROM v_systemparameter WHERE
systemparameter = 'serverid';
    NEW.serverid := SrvID;
    INSERT INTO notifyinfos (tablename, pkey_server, pkey_id, aktion)
VALUES (TG_RELNAME, SrvID, NEW.rowid, 'INSERT');

  END IF;

  IF (TG_OP = 'UPDATE') THEN
    INSERT INTO notifyinfos (tablename, pkey_server, pkey_id, aktion)
VALUES (TG_RELNAME, OLD.serverid, OLD.rowid, 'UPDATE');
  END IF;

  IF (TG_OP = 'DELETE') THEN
    INSERT INTO notifyinfos (tablename, pkey_server, pkey_id, aktion)
VALUES (TG_RELNAME, OLD.serverid, OLD.rowid, 'DELETE');
  END IF;

  RETURN NEW;
end;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE;
ALTER FUNCTION notifytrigger() OWNER TO db_user;

CREATE TRIGGER t_notifytest BEFORE INSERT OR UPDATE OR DELETE
   ON notifydata FOR EACH ROW
   EXECUTE PROCEDURE notifytrigger();

Karsten Hoffrath <maillists@khoffrath.de> schrieb:

>
>
> 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.

You can use tablog for this: http://pgfoundry.org/projects/tablelog/


Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect.                              (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."    (unknow)
Kaufbach, Saxony, Germany, Europe.              N 51.05082°, E 13.56889°