Re: Record as a parameter to a function

On Fri, Apr 22, 2005 at 01:44:24AM +0100, Carlos Correia wrote:
>
> carlos=# insert into test (c1) values( 'test');
> ERROR: NEW used in query that is not in a rule
> CONTEXT: PL/pgSQL function "tftest" line 2 at perform

PostgreSQL 8.0 and later have improved support for composite types.
Here's the example you posted, slightly rewritten:

CREATE TABLE test (c1 text);

CREATE FUNCTION test1(InRec test) RETURNS boolean AS $$
BEGIN
RAISE NOTICE '%', InRec.c1;
RETURN TRUE;
END;
$$ LANGUAGE plpgsql;

CREATE FUNCTION tftest() RETURNS trigger AS $$
BEGIN
PERFORM test1(NEW);
RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER TriggerTest AFTER INSERT ON test
FOR EACH ROW EXECUTE PROCEDURE tftest();

If I execute the above statements in an 8.0.2 database, I get the
following when I insert a new record:

test=> INSERT INTO test (c1) VALUES ('Test');
NOTICE: Test
CONTEXT: SQL statement "SELECT test1( $1 )"
PL/pgSQL function "tftest" line 2 at perform
INSERT 0 1

In psql you can change the verbosity so you don't see the context
messages:

test=> \set VERBOSITY terse
test=> INSERT INTO test (c1) VALUES ('Test');
NOTICE: Test
INSERT 0 1

Hope this helps.

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/