Search Postgresql Archives

Record as a parameter to a function

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux