Subject: Re: Functions return a select in a table, which data type I
From: Markus Bertheau <twanger@bluetwanger.de>
To: =?ISO-8859-1?Q?Andr=E9?= Toscano <andre.toscano@uol.com.br>
Cc: pgsql-sql@postgresql.org
In-Reply-To: <41A11FC4.4040909@uol.com.br>
References: <41A11FC4.4040909@uol.com.br>
Content-Type: text/plain; charset=UTF-8
Message-Id: <1098452309.2655.2.camel@dicaprio.akademie1.de>
Mime-Version: 1.0
Date: Fri, 22 Oct 2004 15:38:29 +0200
Content-Transfer-Encoding: quoted-printable

=D0=92 =D0=9F=D0=BD=D0=B4, 22.11.2004, =D0=B2 00:07, Andr=C3=A9 Toscano =D0=
=BF=D0=B8=D1=88=D0=B5=D1=82:
> Hello, friends.
>=20
> If anybody can help, how can I do a FUNCTION return a result from a=20
> SELECT in a table in PostgreSQL?
> My Problem is the RETURN TYPE from a FUNCTION, I don=C2=B4t know what I h=
ave=20
> to use to return the data as a select result.
>=20
> Example:
> ----------------
>=20
> DROP FUNCTION ACADEMICO.teste(int4);
>=20
> CREATE FUNCTION ACADEMICO.teste(int4)
> RETURNS ?????
> AS
> '
>     select cod_aluno, nome, cpf from ACADEMICO.TB_alunos
>=20
> '
> LANGUAGE 'SQL';

CREATE TYPE foo_type AS (cod_aluno TEXT, nome TEXT, cpf TEXT);
CREATE FUNCTION bar(int4)
	RETURNS SETOF foo_type
	LANGUAGE 'SQL'
	AS '
DECLARE
	var_rec foo_type;
BEGIN
	FOR var_rec IN SELECT cod_aluno, nome, cpf FROM table WHERE ... 	LOOP
		RETURN NEXT var_rec;
	END LOOP;
	RETURN;
END;
';

--=20
Markus Bertheau <twanger@bluetwanger.de>