To: pgsql-sql@postgresql.org
From: =?UTF-8?B?SmFubiBSw7ZkZXI=?= <roederja@ethz.ch>
Subject: Re: Table returning functions
Date: Mon, 20 Sep 2010 01:04:07 +0200
Lines: 54
Message-ID: <i764t8$clq$1@dough.gmane.org>
References: <i683es$v8b$1@dough.gmane.org>
Mime-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit
User-Agent: Mozilla/5.0 (Macintosh; U; Intel Mac OS X 10.6; de;
	rv:1.9.2.8) Gecko/20100802 Thunderbird/3.1.2
In-Reply-To: <i683es$v8b$1@dough.gmane.org>

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
> 
>