pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedavoid the creating the type for setof
4+ messages / 3 participants
[nested] [flat]
* avoid the creating the type for setof
@ 2011-12-30 14:26 John Fabiani <johnf@jfcomputer.com>
2011-12-30 14:48 ` Re: avoid the creating the type for setof Thomas Kellerer <spam_eater@gmx.net>
2011-12-30 14:49 ` Re: avoid the creating the type for setof Adrian Klaver <adrian.klaver@gmail.com>
2011-12-30 15:08 ` Re: avoid the creating the type for setof John Fabiani <johnf@jfcomputer.com>
0 siblings, 3 replies; 4+ messages in thread
From: John Fabiani @ 2011-12-30 14:26 UTC (permalink / raw)
To: pgsql-sql
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
Johnf
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: avoid the creating the type for setof
2011-12-30 14:26 avoid the creating the type for setof John Fabiani <johnf@jfcomputer.com>
@ 2011-12-30 14:48 ` Thomas Kellerer <spam_eater@gmx.net>
2 siblings, 0 replies; 4+ messages in thread
From: Thomas Kellerer @ 2011-12-30 14:48 UTC (permalink / raw)
To: pgsql-sql
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-RET...
Thomas
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: avoid the creating the type for setof
2011-12-30 14:26 avoid the creating the type for setof John Fabiani <johnf@jfcomputer.com>
@ 2011-12-30 14:49 ` Adrian Klaver <adrian.klaver@gmail.com>
2 siblings, 0 replies; 4+ messages in thread
From: Adrian Klaver @ 2011-12-30 14:49 UTC (permalink / raw)
To: pgsql-sql; +Cc: John Fabiani <johnf@jfcomputer.com>
On Friday, December 30, 2011 6:26:19 am John Fabiani wrote:
> 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 ...
http://www.postgresql.org/docs/9.0/interactive/plpgsql-declarations.html#PLPGSQL-DECLARATION-PARAMET...
39.3.1. Declaring Function Parameters
Search for RETURNS TABLE
>
> return
>
>
> Johnf
--
Adrian Klaver
adrian.klaver@gmail.com
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: avoid the creating the type for setof
2011-12-30 14:26 avoid the creating the type for setof John Fabiani <johnf@jfcomputer.com>
@ 2011-12-30 15:08 ` John Fabiani <johnf@jfcomputer.com>
2 siblings, 0 replies; 4+ messages in thread
From: John Fabiani @ 2011-12-30 15:08 UTC (permalink / raw)
To: pgsql-sql
On Friday, December 30, 2011 06:26:19 AM John Fabiani wrote:
> 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
>
>
> Johnf
Thanks all - I knew I had seen it in the past.
Johnf
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2011-12-30 15:08 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-12-30 14:26 avoid the creating the type for setof John Fabiani <johnf@jfcomputer.com>
2011-12-30 14:48 ` Thomas Kellerer <spam_eater@gmx.net>
2011-12-30 14:49 ` Adrian Klaver <adrian.klaver@gmail.com>
2011-12-30 15:08 ` John Fabiani <johnf@jfcomputer.com>
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