From: Christoph Haller <ch@rodos.fzk.de>
To: lud_nowhere_man@yahoo.com
Cc: pgsql-sql@postgresql.org
Subject: Re: Passing OLD/NEW as composite type PL/PGSQL
Date: Wed, 13 Nov 2002 13:09:15 +0100
Message-ID: <3DD240EB.3FD87862@rodos.fzk.de> (raw)
>
> 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: ch@rodos.fzk.de, lud_nowhere_man@yahoo.com
Subject: Re: Passing OLD/NEW as composite type PL/PGSQL
In-Reply-To: <3DD240EB.3FD87862@rodos.fzk.de>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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