Re: Functions return a select in a table, which data type I

From: Markus Bertheau <twanger(at)bluetwanger(dot)de>
To: André Toscano <andre(dot)toscano(at)uol(dot)com(dot)br>
Cc: pgsql-sql(at)postgresql(dot)org
Subject: Re: Functions return a select in a table, which data type I
Date: 2004-10-29 09:08:58
Message-ID: 1099040938.2653.3.camel@dicaprio.akademie1.de
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

В Птн, 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(at)bluetwanger(dot)de>

In response to

Browse pgsql-sql by date

  From Date Subject
Next Message Niall Linden 2004-10-29 14:04:25 paertially restoring a DB
Previous Message Murphy Pope 2004-10-28 18:36:36 Re: ORDER BY and NULLs