pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  

help / color / mirror / Atom feed

plpgsql triggers question -> foo := NEW ?
3+ messages / 2 participants
[nested] [flat]

* plpgsql triggers question -> foo := NEW ?
@ 2003-09-18 18:59  Mihail Changalov <news1@faith.digsys.bg>
  0 siblings, 1 reply; 3+ messages in thread

From: Mihail Changalov @ 2003-09-18 18:59 UTC (permalink / raw)
  To: pgsql-sql

Hi list,

I've searched the interactive docs and found this link:

http://www.postgresql.org/docs/7.3/interactive/plpgsql-trigger.html

There is no answer to the question below:  How to set foo:= NEW or foo:= 
OLD in plpgsql trigger function
(I have the same problem)


Any help is appreciated.

Thanks

Mihail Changalov




^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* plpgsql triggers question -> foo := NEW ?
@ 2003-09-24 12:48  Mihail Changalov <news1@faith.digsys.bg>
  parent: Mihail Changalov <news1@faith.digsys.bg>
  0 siblings, 0 replies; 3+ messages in thread

From: Mihail Changalov @ 2003-09-24 12:48 UTC (permalink / raw)
  To: pgsql-sql

Maybe I've hit the wrong list or didn't elaborate what is my question

Please advise me where to ask about NEW and OLD records in pl/pgsql 
trigger functions

Thanks




^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: plpgsql triggers question -> foo := NEW ?
@ 2003-09-25 09:57  Christoph Haller <ch@rodos.fzk.de>
  0 siblings, 0 replies; 3+ messages in thread

From: Christoph Haller @ 2003-09-25 09:57 UTC (permalink / raw)
  To: pgsql-sql; news1@faith.digsys.bg; msouth@fulcrum.org

>
> 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';






^ permalink  raw  reply  [nested|flat] 3+ messages in thread

end of thread, other threads:[~2003-09-25 09:57 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-09-18 18:59 plpgsql triggers question -> foo := NEW ? Mihail Changalov <news1@faith.digsys.bg>
2003-09-24 12:48 ` Mihail Changalov <news1@faith.digsys.bg>
2003-09-25 09:57 Re: plpgsql triggers question -> foo := NEW ? Christoph Haller <ch@rodos.fzk.de>

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