pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  

help / color / mirror / Atom feed

From: Jann Röder <roederja@ethz.ch>
To: pgsql-sql@postgresql.org
Subject: Re: Table returning functions
Date: Mon, 20 Sep 2010 01:04:07 +0200
Message-ID: <i764t8$clq$1@dough.gmane.org> (raw)
In-Reply-To: <i683es$v8b$1@dough.gmane.org>
References: <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
> 
> 

view thread (4+ messages)  latest in thread

Message-ID: <i764t8$clq$1@dough.gmane.org>
Permalink:  ../i764t8$clq$1@dough.gmane.org/
Also on:    postgresql.org/message-id/i764t8$clq$1@dough.gmane.org

copy link · copy postgr.es

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: roederja@ethz.ch
  Subject: Re: Table returning functions
  In-Reply-To: <i764t8$clq$1@dough.gmane.org>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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