From: Franco Bruno Borghesi <franco@akyasociados.com.ar>
Organization: AK y Asociados S.R.L.
To: Joe Conway <mail@joeconway.com>
Subject: Re: returning composite types.
Date: Sat, 29 Mar 2003 14:35:04 -0300
User-Agent: KMail/1.5
Cc: list@racistnames.com, pgsql-sql@postgresql.org
References: <200303282020.25997.franco@akyasociados.com.ar>
	<200303291320.05734.franco@akyasociados.com.ar>
	<3E85CE8B.3010700@joeconway.com>
In-Reply-To: <3E85CE8B.3010700@joeconway.com>
MIME-Version: 1.0
Content-Type: multipart/signed; protocol="application/pgp-signature";
	micalg=pgp-sha1; boundary="Boundary-02=_Ildh+/YLyyMsvt6";
	charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
Message-Id: <200303291435.04727.franco@akyasociados.com.ar>

--Boundary-02=_Ildh+/YLyyMsvt6
Content-Type: text/plain;
  charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Content-Description: signed data
Content-Disposition: inline

yes, i was trying to do something like that, but it breaks always in the sa=
me=20
place, first I thought that it was because of the way I was assigning value=
s=20
to the fields of my row, but now I'm beginning to think that the reason is=
=20
the way I pass the row to f2.=20

Here is the error:
franco=3D# 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=20=20=20
   result mytype%ROWTYPE;
BEGIN
   result.val1:=3D1;=20=20=20
   SELECT val2, val3 INTO result.val2, result.val3 FROM f2(result);
   RETURN result;=20=20=20=20=20=20
END;
' LANGUAGE 'plpgsql';

CREATE OR REPLACE FUNCTION f2(mytype) RETURNS mytype AS '
DECLARE=20=20=20
   arg ALIAS FOR $1;
   result mytype%ROWTYPE;
BEGIN
   arg.val2:=3D2;
   arg.val3:=3D3;
   SELECT arg.val1, arg.val2, arg.val3 INTO result.val1, result.val2,=20
result.val3;
   RETURN result;=20=20=20
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=3D# select * from retfoo(2,'b');
>   f1 | f2
> ----+----
>    2 | b
> (1 row)
>
> Joe

--Boundary-02=_Ildh+/YLyyMsvt6
Content-Type: application/pgp-signature
Content-Description: signature

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.1 (FreeBSD)

iD8DBQA+hdlIz/PwDAvtu7oRAoAhAJ9rRdOhNZ9BeE19VlxPncgmh8VxSQCeMcqO
B6OZva2otGEUOjYHs6TkGtY=
=Gkw7
-----END PGP SIGNATURE-----

--Boundary-02=_Ildh+/YLyyMsvt6--