From: Franco Bruno Borghesi <franco@akyasociados.com.ar>
Organization: AK y Asociados S.R.L.
To: list@racistnames.com, pgsql-sql@postgresql.org
Subject: Re: returning composite types.
Date: Sat, 29 Mar 2003 13:20:05 -0300
User-Agent: KMail/1.5
References: <200303282020.25997.franco@akyasociados.com.ar>
	<3E84E983.10206@racistnames.com>
In-Reply-To: <3E84E983.10206@racistnames.com>
MIME-Version: 1.0
Content-Type: multipart/signed; protocol="application/pgp-signature";
	micalg=pgp-sha1; boundary="Boundary-02=_1ech+1+8mgKZsn2";
	charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
Message-Id: <200303291320.05734.franco@akyasociados.com.ar>

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

ok, soy you're telling me that the only way to return a composite type is=
=20
using a set of them, even if I know my function will allways return 1 recor=
d.

And tell me: can I assign values to this record without using the "for" as =
you=20
did? maybe a SELECT INTO, or something.

I'll tell you what I'm trying to do, maybe that will help: I have 4 functio=
ns,=20
that do specific tasks, and they are called in a chained mode. I want the=
=20
first function in the chain to call the other functions, and return all the=
=20
results in a composite type, like this:

CREATE TYPE mytype AS(
  val1 INTEGER,
  val2 INTEGER,
  val3 INTEGER,
  val4 INTEGER
);

CREATE FUNCTION f1() RETURNS SETOF mytype AS '
DECLARE
   result mytype%ROWTYPE;
BEGIN
   result.val1=3D/*something*/;
   SELECT * INTO result FROM f2(result);=20=20=20
   RETURN NEXT result;
   RETURN;
END;=20=20=20
' LANGUAGE 'plpgsql';

CREATE FUNCTION f2(mytype) RETURNS SETOF mytype AS '
DECLARE
   result ALIAS FOR $1;
BEGIN
   result.val2=3D2;
   IF (result.val1>50) THEN
      SELECT * INTO result FROM f3(result);=20=20=20=20=20=20=20=20=20
   ELSE
      SELECT * INTO result FROM f4(result);
   END IF;=20=20=20=20=20=20
   RETURN NEXT result;
   RETURN;
END;=20=20=20
' LANGUAGE 'plpgsql';

CREATE FUNCTION f3(mytype) RETURNS SETOF mytype AS '
DECLARE
   result ALIAS FOR $1;
BEGIN
   /*do something with result*/
   RETURN NEXT result;
   RETURN;
END;=20=20=20
' LANGUAGE 'plpgsql';
=20=20=20
CREATE FUNCTION f4(mytype) RETURNS SETOF mytype AS '
DECLARE
   result ALIAS FOR $1;
BEGIN
   /*do something with result*/
   RETURN NEXT result;
   RETURN;
END;=20=20=20
' LANGUAGE 'plpgsql';

In a programming language I would pass mytype by reference, and I was tryin=
g=20
to something like that here in plpgsql.=20


On Friday 28 March 2003 21:32, Jordan S. Jones wrote:
> Here is how I have been doing mine:
>
>     CREATE FUNCTION "myFunction" () RETURNS SETOF mytype
>     AS
>     '
>     DECLARE
>         r      mytype%ROWTYPE;
>     BEGIN
>         FOR r IN [SELECT STATEMENT]
>         LOOP
>            RETURN NEXT r;
>         END LOOP;
>         RETURN;
>     END;
>     '
>     LANGUAGE 'plpgsql';
>
> Hope this Helps..
>
> Jordan S. Jones
>
> Franco Bruno Borghesi wrote:
> >Hi guys.
> >
> >I'm working with functions in my database, using plpgsql, but I reached a
> >point where I realize I'm missing a concept: how do I return composite
> > types from a function? I'll give you an example:
> >
> >CREATE TYPE mytype AS(
> >   val1 INTEGER,
> >   val2 INTEGER,
> >   val3 INTEGER,
> >   val4 INTEGER
> >);
> >
> >If I want my function to return a "mytype" type, should I declare it as:
> >CREATE FUNCTION myFunction() RETURNS mytype AS ...
> >or maybe
> >CREATE FUNCTION myFunction() RETURNS SETOF mytype AS ...
> >
> >and in any case, inside the function, how should I declare the variable
> >holding the return value?:
> >
> >DECLARE
> >   result mytype;
> >BEGIN
> >...
> >   RETURN result;
> >END;
> >
> >or maybe
> >
> >DECLARE
> >   result mytype%ROWTYPE;
> >BEGIN
> >...
> >   RETURN result;
> >END;
> >
> >I've read the documentation and the examples in it, but I still don't
> >understand what the right way is. If you could give an example of a
> > function filling "mytipe" and returning it, it would really help me.
> >
> >Thanks in advance.

--Boundary-02=_1ech+1+8mgKZsn2
Content-Type: application/pgp-signature
Content-Description: signature

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

iD8DBQA+hce1z/PwDAvtu7oRAlZvAJ9q0J+dMp4lFJLfrIsk4eQSgfhpTwCghjR2
Fn76cCwGIPWprZaAETd2oKc=
=+Dve
-----END PGP SIGNATURE-----

--Boundary-02=_1ech+1+8mgKZsn2--