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

help / color / mirror / Atom feed

Passing OLD/NEW as composite type PL/PGSQL
2+ messages / 2 participants
[nested] [flat]

* Passing OLD/NEW as composite type PL/PGSQL
@ 2002-11-09 02:50  Ludwig Lim <lud_nowhere_man@yahoo.com>
  0 siblings, 0 replies; 2+ messages in thread

From: Ludwig Lim @ 2002-11-09 02:50 UTC (permalink / raw)
  To: pgsql-sql

Hi:

   Can I pass the the variables OLD and NEW (type
OPAQUE) to another function is expecting a composite
type as parameter?

   Are opaque considered as composite type?

Thank you in advance,

ludwig.

__________________________________________________
Do you Yahoo!?
New DSL Internet Access from SBC & Yahoo!
http://sbc.yahoo.com



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

* Re: Passing OLD/NEW as composite type PL/PGSQL
@ 2002-11-13 12:09  Christoph Haller <ch@rodos.fzk.de>
  0 siblings, 0 replies; 2+ messages in thread

From: Christoph Haller @ 2002-11-13 12:09 UTC (permalink / raw)
  To: lud_nowhere_man@yahoo.com; +Cc: pgsql-sql

>
>    Can I pass the the variables OLD and NEW (type
> OPAQUE) to another function is expecting a composite
> type as parameter?
>
>    Are opaque considered as composite type?
>
Did you receive any other response?
Did you check if it works by simply trying?
As far as I understand the documentation,
OPAQUE can be considered (among others) as composite type.

If it's not working, did you think of copying the OLD resp. NEW
to a rowtype variable?

I am thinking of something like this
(taken from the Trigger Procedure Example within the documentation)

CREATE TABLE emp (
       empname text,
       salary integer,
       last_date timestamp,
       last_user text
   );
CREATE FUNCTION process_old_emp_row(emp%ROWTYPE) RETURNS ... ;
CREATE FUNCTION process_new_emp_row(emp%ROWTYPE) RETURNS ... ;

   CREATE FUNCTION emp_stamp () RETURNS OPAQUE AS '
       DECLARE
        old_emp_row emp%ROWTYPE;
        new_emp_row emp%ROWTYPE;
       BEGIN
           -- copy OLD to old_emp_row, call process_old_emp_row
           old_emp_row.empname := OLD.empname;
           old_emp_row.salary := OLD.salary;
           old_emp_row.last_date := OLD.last_date;
           old_emp_row.last_user := OLD.last_user;
           process_old_emp_row(old_emp_row);

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

           -- copy NEW to new_emp_row, call process_new_emp_row
           new_emp_row.empname := NEW.empname;
           new_emp_row.salary := NEW.salary;
           new_emp_row.last_date := NEW.last_date;
           new_emp_row.last_user := NEW.last_user;
           process_new_emp_row(new_emp_row);

           RETURN NEW;
       END;
   ' LANGUAGE 'plpgsql';

Regards, Christoph




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

end of thread, other threads:[~2002-11-13 12:09 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-11-09 02:50 Passing OLD/NEW as composite type PL/PGSQL Ludwig Lim <lud_nowhere_man@yahoo.com>
2002-11-13 12:09 Re: Passing OLD/NEW as composite type PL/PGSQL 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