Message-ID: <3DD240EB.3FD87862@rodos.fzk.de>
Date: Wed, 13 Nov 2002 13:09:15 +0100
From: Christoph Haller <ch@rodos.fzk.de>
MIME-Version: 1.0
To: lud_nowhere_man@yahoo.com
Cc: pgsql-sql@postgresql.org
Subject: Re: Passing OLD/NEW as composite type PL/PGSQL
Content-Type: text/plain; charset=iso-8859-2
Content-Transfer-Encoding: 7bit

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