Re: Problem with triggers and cursors - Mailing list pgsql-novice

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