Record as a parameter to a function
-----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-----
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/
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Michael Fuhr wrote:
| 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.
Thanks... but does this means that you can't do that with version 7.4.x?
Carlos
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.0 (GNU/Linux)
Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org
iD8DBQFCaGIe90uzwjA1SJURAth1AJwJwKTO2kEQXCDAEtgHo8HsbhhU7wCfUCmh
FsjjfW9J8plrHc1oT8JsCwA=
=KFaE
-----END PGP SIGNATURE-----
On Fri, Apr 22, 2005 at 03:31:59AM +0100, Carlos Correia wrote:
Michael Fuhr wrote:
| 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.Thanks... but does this means that you can't do that with version 7.4.x?
Not as far as I know, or at least not the same way.
--
Michael Fuhr
http://www.fuhr.org/~mfuhr/
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Michael Fuhr wrote:
| On Fri, Apr 22, 2005 at 03:31:59AM +0100, Carlos Correia wrote:
|
|>Michael Fuhr wrote:
|>| 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.
|>
|>Thanks... but does this means that you can't do that with version 7.4.x?
|
|
| Not as far as I know, or at least not the same way.
|
Well... I guess I'll have to do it the hard way (no big deal)
Thank you very much ;-)
Carlos
- --
MEM�RIA PERSISTENTE, Lda.
Tel.: 219 291 591 - GSM: 967 511 762
e-mail: geral@m16e.com - URL: http://www.m16e.com
AIM: m16e - ICQ: 257488263 - Jabber: m16e@amessage.de
Skype.com username (VoIP): m16e.com
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.0 (GNU/Linux)
Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org
iD8DBQFCaXOQ90uzwjA1SJURAq8FAJ9Bz+NZpjdG3Ig3QaFXyMGY0JNs2wCaAmNy
InMlLAsINv/mEBCDtt6YWwc=
=2NMd
-----END PGP SIGNATURE-----