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: <1098452309.2655.2.camel@dicaprio.akademie1.de>
References: <41A11FC4.4040909@uol.com.br>
	<1098452309.2655.2.camel@dicaprio.akademie1.de>
Content-Type: text/plain; charset=UTF-8
Message-Id: <1099040938.2653.3.camel@dicaprio.akademie1.de>
Mime-Version: 1.0
Date: Fri, 29 Oct 2004 11:08:58 +0200
Content-Transfer-Encoding: quoted-printable

=D0=92 =D0=9F=D1=82=D0=BD, 22.10.2004, =D0=B2 15:38, Markus Bertheau =D0=BF=
=D0=B8=D1=88=D0=B5=D1=82:

> CREATE TYPE foo_type AS (cod_aluno TEXT, nome TEXT, cpf TEXT);
> CREATE FUNCTION bar(int4)
> 	RETURNS SETOF foo_type
> 	LANGUAGE 'SQL'
That should be LANGUAGE 'plpgsql'
> 	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;
> ';

And if you want that function in SQL, there are two kinds of situations,
for which the solutions differ: If the record structure that the
function should return is the same as the structure of a table, you can
use the table name as the type. If this is not the case, you have to
create a custom type:

CREATE FUNCTION bar(int4)
	RETURNS table_name or custom_type_name
	LANGUAGE 'SQL'
	AS '
SELECT whatever FROM table WHERE field =3D $1 AND foo;
END;
';

$1 is the value of the first argument.

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