pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedTable returning functions
4+ messages / 2 participants
[nested] [flat]
* Table returning functions
@ 2010-09-08 13:35 Jann Röder <roederja@ethz.ch>
2010-09-19 23:04 ` Re: Table returning functions Jann Röder <roederja@ethz.ch>
0 siblings, 1 reply; 4+ messages in thread
From: Jann Röder @ 2010-09-08 13:35 UTC (permalink / raw)
To: pgsql-sql
Hi,
I have a question which does not seem to be covered in the
documentation: I have a function f(i) that returns a table with three
columns (a, b, c).
In the documentation
(http://www.postgresql.org/docs/8.4/interactive/xfunc-sql.html#XFUNC-SQL-FUNCTIONS-RETURNING-SET)
it says that (even though deprecated) you can call such functions in a
SELECT clause like so:
SELECT id, f(id) FROM some_table .
This will give me a two column table that looks like this:
id | (a, b, c)
However since I want a 4 column table like this
id | a | b | c
, I do this instead:
SELECT id, (f(id)).a, (f(id)).b, (f(id)).c FROM some_table.
However I'm not quite sure if this really gives me what I want (i.e. the
same as in the first query only with the tuple elements as separate
columns).
Of course I'm open to suggestions on how to do this more elegantly and
also how one can do something like this without using the deprecated
feature of calling a set returning function in SELECT.
Jann
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Table returning functions
2010-09-08 13:35 Table returning functions Jann Röder <roederja@ethz.ch>
@ 2010-09-19 23:04 ` Jann Röder <roederja@ethz.ch>
2010-09-19 23:58 ` Re: Table returning functions Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 4+ messages in thread
From: Jann Röder @ 2010-09-19 23:04 UTC (permalink / raw)
To: pgsql-sql
Ok I now know that it really seems to do what I expected. But I still
wonder what it does if I use two functions f() and g() that return a
different number of rows.
I'm guessing a query such as
SELECT id, (f(id)).a, (g(id)).b when f returns 3 rows and g returns two
row would yield something like this:
id | a1 | b1
id | a2 | b2
id | a3 | <NULL>
I haven't tried this, so it could be something completely different. In
any case I think this should be documented.
Jann
Am 08.09.10 15:35, schrieb Jann Röder:
> Hi,
> I have a question which does not seem to be covered in the
> documentation: I have a function f(i) that returns a table with three
> columns (a, b, c).
>
> In the documentation
> (http://www.postgresql.org/docs/8.4/interactive/xfunc-sql.html#XFUNC-SQL-FUNCTIONS-RETURNING-SET)
> it says that (even though deprecated) you can call such functions in a
> SELECT clause like so:
>
> SELECT id, f(id) FROM some_table .
>
> This will give me a two column table that looks like this:
>
> id | (a, b, c)
>
> However since I want a 4 column table like this
> id | a | b | c
>
> , I do this instead:
>
> SELECT id, (f(id)).a, (f(id)).b, (f(id)).c FROM some_table.
>
> However I'm not quite sure if this really gives me what I want (i.e. the
> same as in the first query only with the tuple elements as separate
> columns).
>
> Of course I'm open to suggestions on how to do this more elegantly and
> also how one can do something like this without using the deprecated
> feature of calling a set returning function in SELECT.
>
> Jann
>
>
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Table returning functions
2010-09-08 13:35 Table returning functions Jann Röder <roederja@ethz.ch>
2010-09-19 23:04 ` Re: Table returning functions Jann Röder <roederja@ethz.ch>
@ 2010-09-19 23:58 ` Tom Lane <tgl@sss.pgh.pa.us>
2010-09-20 07:43 ` Re: Table returning functions Jann Röder <roederja@ethz.ch>
0 siblings, 1 reply; 4+ messages in thread
From: Tom Lane @ 2010-09-19 23:58 UTC (permalink / raw)
To: Jann Röder <roederja@ethz.ch>; +Cc: pgsql-sql
=?UTF-8?B?SmFubiBSw7ZkZXI=?= <roederja@ethz.ch> writes:
> Ok I now know that it really seems to do what I expected. But I still
> wonder what it does if I use two functions f() and g() that return a
> different number of rows.
You get the least common multiple of their periods. It's ugly, and the
lack of any very sane way to define the behavior is the main argument
for deprecating SRFs in the targetlist.
regards, tom lane
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Table returning functions
2010-09-08 13:35 Table returning functions Jann Röder <roederja@ethz.ch>
2010-09-19 23:04 ` Re: Table returning functions Jann Röder <roederja@ethz.ch>
2010-09-19 23:58 ` Re: Table returning functions Tom Lane <tgl@sss.pgh.pa.us>
@ 2010-09-20 07:43 ` Jann Röder <roederja@ethz.ch>
0 siblings, 0 replies; 4+ messages in thread
From: Jann Röder @ 2010-09-20 07:43 UTC (permalink / raw)
To: pgsql-sql
True,
but it still allowed me to write queries that I do not know how to
express otherwise.
Am 20.09.10 01:58, schrieb Tom Lane:
> =?UTF-8?B?SmFubiBSw7ZkZXI=?= <roederja@ethz.ch> writes:
>> Ok I now know that it really seems to do what I expected. But I still
>> wonder what it does if I use two functions f() and g() that return a
>> different number of rows.
>
> You get the least common multiple of their periods. It's ugly, and the
> lack of any very sane way to define the behavior is the main argument
> for deprecating SRFs in the targetlist.
>
> regards, tom lane
>
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2010-09-20 07:43 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-09-08 13:35 Table returning functions Jann Röder <roederja@ethz.ch>
2010-09-19 23:04 ` Jann Röder <roederja@ethz.ch>
2010-09-19 23:58 ` Tom Lane <tgl@sss.pgh.pa.us>
2010-09-20 07:43 ` Jann Röder <roederja@ethz.ch>
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