>
> 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';
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings
