Postgres Pro
Downloads
Home   >   mailing lists

Thread: Record as a parameter to a function

Record as a parameter to a function

From
Carlos Correia
Date:
-----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-----

Re: Record as a parameter to a function

From
Michael Fuhr
Date:
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/

Re: Record as a parameter to a function

From
Carlos Correia
Date:
-----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-----

Re: Record as a parameter to a function

From
Michael Fuhr
Date:
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/

Re: Record as a parameter to a function

From
Carlos Correia
Date:
-----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-----