Re: Record as a parameter to a function - Mailing list pgsql-general

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/