From: Franco Bruno Borghesi <franco@akyasociados.com.ar>
To: Joe Conway <mail@joeconway.com>
Cc: list@racistnames.com
Cc: pgsql-sql@postgresql.org
Subject: Re: returning composite types.
Date: Sat, 29 Mar 2003 14:35:04 -0300
Message-ID: <200303291435.04727.franco@akyasociados.com.ar> (raw)
In-Reply-To: <3E85CE8B.3010700@joeconway.com>
References: <200303282020.25997.franco@akyasociados.com.ar>
<200303291320.05734.franco@akyasociados.com.ar>
<3E85CE8B.3010700@joeconway.com>
yes, i was trying to do something like that, but it breaks always in the same
place, first I thought that it was because of the way I was assigning values
to the fields of my row, but now I'm beginning to think that the reason is
the way I pass the row to f2.
Here is the error:
franco=# SELECT f1();
WARNING: Error occurred while executing PL/pgSQL function f1
WARNING: line 5 at select into variables
ERROR: Attribute "result" not found
CREATE TYPE mytype AS (val1 INTEGER, val2 INTEGER, val3 INTEGER);
CREATE OR REPLACE FUNCTION f1() RETURNS mytype AS '
DECLARE
result mytype%ROWTYPE;
BEGIN
result.val1:=1;
SELECT val2, val3 INTO result.val2, result.val3 FROM f2(result);
RETURN result;
END;
' LANGUAGE 'plpgsql';
CREATE OR REPLACE FUNCTION f2(mytype) RETURNS mytype AS '
DECLARE
arg ALIAS FOR $1;
result mytype%ROWTYPE;
BEGIN
arg.val2:=2;
arg.val3:=3;
SELECT arg.val1, arg.val2, arg.val3 INTO result.val1, result.val2,
result.val3;
RETURN result;
END;
' LANGUAGE 'plpgsql';
what do you guys think?
On Saturday 29 March 2003 13:49, Joe Conway wrote:
> Franco Bruno Borghesi wrote:
> > ok, soy you're telling me that the only way to return a composite type is
> > using a set of them, even if I know my function will allways return 1
> > record.
>
> Try this:
>
> create type foo as (f1 int, f2 text);
> create or replace function retfoo(int, text) returns foo as '
> declare
> result foo%ROWTYPE;
> begin
> select into result $1, $2;
> return result;
> end;
> ' language 'plpgsql';
>
> regression=# select * from retfoo(2,'b');
> f1 | f2
> ----+----
> 2 | b
> (1 row)
>
> Joe
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: franco@akyasociados.com.ar, mail@joeconway.com, list@racistnames.com
Subject: Re: returning composite types.
In-Reply-To: <200303291435.04727.franco@akyasociados.com.ar>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox