Re: plpgsql triggers question -> foo := NEW ? - Mailing list pgsql-sql

>
> 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 givenIF NEW.empname ISNULL THEN    RAISE EXCEPTION ''empname cannot be NULL
value'';ENDIF;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 whenNEW.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 givenIF NEW.empname ISNULL THEN    RAISE EXCEPTION ''empname cannot be NULL
value'';ENDIF;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 whenNEW.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';