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;




---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

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/i...l-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);


---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

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();




---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

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°

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq