Record as a parameter to a function

From: Carlos Correia <carlos(at)m16e(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Record as a parameter to a function
Date: 2005-04-22 00:44:24
Message-ID: 426848E8.3080001@m16e.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Hi,

I would like to write a trigger that will do complex calculations on a
row, so the idea is to slipt the work throught several functions, and as
the row has 23 fields, it's not an option to pass them one by one, so I
tried to pass the record itself (NEW)...

While doing the home work before posting I found a piece of code that
ilustrastes this problem
(http://groups.google.pt/groups?hl=en-US&lr=&threadm=200201210419.2467%40th00.opsion.fr&rnum=1&prev=/groups%3Fhl%3Dpt-PT%26lr%3D%26q%3Drecord%2Bparameter%26btnG%3DPesquisar%26meta%3Dgroup%253Dmuc.lists.postgres.questions)

The code (from cnliou):

CREATE TABLE test(c1 TEXT);

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

CREATE FUNCTION tftest() RETURNS OPAQUE AS '
BEGIN
~ PERFORM test1(NEW);
~ RETURN NEW;
END;' LANGUAGE 'plpgsql';

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

...and the error I get when inserting a row:

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
carlos=#

In the comments to the original post it is said that Postgres "doesn't
work very well with composite (rowtype) parameters"... since the posts
are more then 2 years old, any one can tell me what's the problem with
this code or if there is another kind of solution to this problem?

Thanks,

Carlos

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.0 (GNU/Linux)
Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org

iD8DBQFCaEjn90uzwjA1SJURAvc0AJwPfh6QzwLOgGXnaVUEkhsQxu/+1ACgpTAC
GRjw2uEDM/RXd/WKd9NjzIM=
=26wD
-----END PGP SIGNATURE-----

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Steve - DND 2005-04-22 00:56:41 Re: timezone() with timeofday() converts the wrong direction?
Previous Message Typing80wpm 2005-04-22 00:28:17 psqlodbc MSAccess and Postgresql