From: Christoph Haller <ch@rodos.fzk.de>
To: pgsql-sql@postgresql.org
To: news1@faith.digsys.bg
To: msouth@fulcrum.org
Subject: Re: plpgsql triggers question -> foo := NEW ?
Date: Thu, 25 Sep 2003 11:57:53 +0200
Message-ID: <3F72BC20.60996D18@rodos.fzk.de> (raw)
>
> Thanks a lot for Your reply by I wanted to create some *foo* variable
(
> in declare part of pl/pgsql trigger function) and to set :
> foo := NEW
>
> That's why I've posted the link from interactive docs - there is the
> same question
>
> http://www.postgresql.org/docs/7.3/interactive/plpgsql-trigger.html
>
I see your point. I have found two possible workarounds,
based on the PL/pgSQL Trigger Procedure Example from the link above.
But still, you have to assign the individual fields one by one.
First one would be restricted to the table in question.
Second one would not be restricted to the table,
but the columns, not necessarily the data type,
but the name at least. Furthermore, to assign the foo RECORD
substructure, a dynamic query has to be executed every time,
which will slow down execution.
Maybe others can see a better way now.
Regards, Christoph
(1)
CREATE OR REPLACE FUNCTION emp_stamp () RETURNS TRIGGER AS '
DECLARE
foo emp%ROWTYPE ;
BEGIN
foo.empname := NEW.empname;
-- Check that empname and salary are given
IF NEW.empname ISNULL THEN
RAISE EXCEPTION ''empname cannot be NULL value'';
END IF;
IF NEW.salary ISNULL THEN
RAISE EXCEPTION ''% cannot have NULL salary'', NEW.empname;
END IF;
-- Who works for us when she must pay for?
IF NEW.salary < 0 THEN
RAISE EXCEPTION ''% cannot have a negative salary'', NEW.empname;
END IF;
-- Remember who changed the payroll when
NEW.last_date := ''now'';
NEW.last_user := current_user;
foo.last_date := NEW.last_date;
foo.last_user := NEW.last_user;
-- RETURN NEW;
RETURN foo;
END;
' LANGUAGE 'plpgsql';
(2)
CREATE OR REPLACE FUNCTION emp_stamp () RETURNS TRIGGER AS '
DECLARE
foo RECORD ;
BEGIN
FOR foo IN EXECUTE
''SELECT * FROM '' || quote_ident(TG_RELNAME) || '' LIMIT 1''
LOOP
END LOOP;
foo.empname := NEW.empname;
-- Check that empname and salary are given
IF NEW.empname ISNULL THEN
RAISE EXCEPTION ''empname cannot be NULL value'';
END IF;
IF NEW.salary ISNULL THEN
RAISE EXCEPTION ''% cannot have NULL salary'', NEW.empname;
END IF;
-- Who works for us when she must pay for?
IF NEW.salary < 0 THEN
RAISE EXCEPTION ''% cannot have a negative salary'', NEW.empname;
END IF;
-- Remember who changed the payroll when
NEW.last_date := ''now'';
NEW.last_user := current_user;
foo.last_date := NEW.last_date;
foo.last_user := NEW.last_user;
-- RETURN NEW;
RETURN foo;
END;
' LANGUAGE 'plpgsql';
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: ch@rodos.fzk.de, news1@faith.digsys.bg, msouth@fulcrum.org
Subject: Re: plpgsql triggers question -> foo := NEW ?
In-Reply-To: <3F72BC20.60996D18@rodos.fzk.de>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox