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 15:56:26 -0300
User-Agent: KMail/1.5
Cc: list@racistnames.com, pgsql-sql@postgresql.org
References: <200303282020.25997.franco@akyasociados.com.ar>
	<200303291435.04727.franco@akyasociados.com.ar>
	<3E85E50A.4040107@joeconway.com>
In-Reply-To: <3E85E50A.4040107@joeconway.com>
MIME-Version: 1.0
Content-Type: multipart/signed; protocol="application/pgp-signature";
	micalg=pgp-sha1; boundary="Boundary-02=_bxeh+p3KWHupT9Q";
	charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
Message-Id: <200303291556.27245.franco@akyasociados.com.ar>

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

so what you're saying is that it's not a syntactical error, it's just an=20
unsupported feature.=20

Ok then, I will just call my functions sending the values from my composite=
=20
type individually.

thanks guys!

On Saturday 29 March 2003 15:25, Joe Conway wrote:
> Franco Bruno Borghesi wrote:
> > 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=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
> >    result mytype%ROWTYPE;
> > BEGIN
> >    result.val1:=3D1;
> >    SELECT val2, val3 INTO result.val2, result.val3 FROM f2(result);
>
> It looks like plpgsql doesn't support composite type variables as
> arguments to functions that are called from within a function. The error
> is saying it cannot find an attribute named result -- that's because
> there is no *attribute* called result, there is a rowtype variable.
>
> I'm not sure right off what is involved in fixing this, but you can
> always pass the individual attributes to f2:
>
> CREATE TYPE mytype AS (val1 INTEGER, val2 INTEGER, val3 INTEGER);
>
> CREATE OR REPLACE FUNCTION f2(int,int,int) RETURNS mytype AS '
> DECLARE
>     arg1 ALIAS FOR $1;
>     arg2 ALIAS FOR $2;
>     arg3 ALIAS FOR $3;
>     newval2 int;
>     newval3 int;
>     result mytype%ROWTYPE;
> BEGIN
>     newval2 :=3D coalesce(arg2,2) * arg1;
>     newval3 :=3D coalesce(arg3,3) * arg1;
>     SELECT INTO result.val1, result.val2, result.val3
>       arg1, newval2, newval3;
>     RETURN result;
> END;
> ' LANGUAGE 'plpgsql';
>
> CREATE OR REPLACE FUNCTION f1(int) RETURNS mytype AS '
> DECLARE
>     result mytype%ROWTYPE;
> BEGIN
>     result.val1:=3D$1;
>     SELECT INTO result.val2, result.val3 val2, val3
>       FROM f2(result.val1, result.val2, result.val3);
>     RETURN result;
> END;
> ' LANGUAGE 'plpgsql';
>
> regression=3D# select * from f1(2);
>   val1 | val2 | val3
> ------+------+------
>      2 |    4 |    6
> (1 row)
>
> HTH,
>
> Joe

--Boundary-02=_bxeh+p3KWHupT9Q
Content-Type: application/pgp-signature
Content-Description: signature

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

iD8DBQA+hexbz/PwDAvtu7oRAkzKAKCGUlNx2jNrzIKBoXyNFoSMH+UO6wCfQIvK
0hWRlZLfPxVEMxIX6GkbqT0=
=5q2I
-----END PGP SIGNATURE-----

--Boundary-02=_bxeh+p3KWHupT9Q--