To: pgsql-sql@postgresql.org
From: Thomas Kellerer <spam_eater@gmx.net>
Subject: Re: avoid the creating the type for setof
Date: Fri, 30 Dec 2011 15:48:30 +0100
Lines: 33
Message-ID: <jdkivn$7m5$1@dough.gmane.org>
References: <1762087.ynHxkPSFx3@linux-12>
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; de;
	rv:1.8.1.21) Gecko/20090302 Thunderbird/2.0.0.21 Mnenhy/0.7.5.666
In-Reply-To: <1762087.ynHxkPSFx3@linux-12>

John Fabiani wrote on 30.12.2011 15:26:
> Hi,
> I recall somewhere I saw a simple plpgsql function that returned a table with
> more than one record that did not use a defined type or a temp table ( at
> least I think I did).  Is it possible to create such a function that will
> return more than one record and not require a record type or temp table?
>
> something like
>
> return setof record as
> return query select ...
>
> return

Sure:

create or replace function
   returns table (f1 text, f2 integer)
as
$$
    select col1, col2 from some table;
$$
language sql;

If you are using PL/pgSQL you need to use "RETURN QUERY SELECT ..." inside the function.

For more details see the examples in the manual:
http://www.postgresql.org/docs/current/static/sql-createfunction.html
http://www.postgresql.org/docs/current/static/plpgsql-control-structures.html#PLPGSQL-STATEMENTS-RETURNING

Thomas