From: Markus Bertheau <twanger@bluetwanger.de>
To: André Toscano <andre.toscano@uol.com.br>
Cc: pgsql-sql@postgresql.org
Subject: Re: Functions return a select in a table, which data type I
Date: Fri, 29 Oct 2004 11:08:58 +0200
Message-ID: <1099040938.2653.3.camel@dicaprio.akademie1.de> (raw)
In-Reply-To: <1098452309.2655.2.camel@dicaprio.akademie1.de>
References: <41A11FC4.4040909@uol.com.br>
<1098452309.2655.2.camel@dicaprio.akademie1.de>
В Птн, 22.10.2004, в 15:38, Markus Bertheau пишет:
> 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 = $1 AND foo;
END;
';
$1 is the value of the first argument.
--
Markus Bertheau <twanger@bluetwanger.de>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: twanger@bluetwanger.de, andre.toscano@uol.com.br
Subject: Re: Functions return a select in a table, which data type I
In-Reply-To: <1099040938.2653.3.camel@dicaprio.akademie1.de>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox