Re: Is this correct usage of generate_series?

On Thu, May 22, 2008 at 10:41 PM, Hiroaki Nakamura <hnakamur(at)gmail(dot)com>
wrote:

> Hi, there.
>
> These three queries below works as I expect, which is wonderful, but are
> these correct usages?
> If these are intended features, I would like them to be documented at
> http://www.postgresql.org/docs/8.3/interactive/functions-srf.html
>
> => select generate_series(1, 3) as i;
> i
> ---
> 1
> 2
> 3
> (3 rows)
>
> => select 'a' as a, generate_series(1, 3) as i;
> a | i
> ---+---
> a | 1
> a | 2
> a | 3
> (3 rows)
>
> => select 'a' as a, i from generate_series(1, 3) i;
> a | i
> ---+---
> a | 1
> a | 2
> a | 3
> (3 rows)
>
> Here is an example using this technique, which shows column positions and
> names in
> the specified index.
>
> select attnum, attname
> from pg_catalog.pg_attribute a
> join (
> select
> indrelid, indkey[i] as pos
> from (
> select
> indrelid, indkey, generate_series(lb, ub) as i
> from (
> select indrelid, indkey, array_lower(indkey, 1) as lb,
> array_upper(indkey, 1) as ub
> from pg_catalog.pg_index
> where indexrelid = (
> select oid
> from pg_catalog.pg_class
> where relnamespace = (select oid from
> pg_catalog.pg_namespace where nspname = 'public')
> and relkind = 'i'
> and relname = '<your_index_name_here>'
> )
> ) x
> ) y
> ) z on a.attrelid = z.indrelid and a.attnum = z.pos;
>
>
>
All three of these are correct usages. This is an SRF (Set Returning
Function), and Postgres allows you to use an SRF in the SELECT list. People
have been using tricks similar to what you showed above, and it works great.

I have seen discussions in the past on the -hackers mailing list about
deprecating the usage of SRFs in select list, but honestly, I don't see this
usage being deprecated anytime soon; it's pretty useful, and I would call it
an extension to the SQL language. And as long as it's nit buggy, and ALA it
doesn't surprise people in a wrong way, I think such usage will remain.

Best regards,

--
gurjeet[(dot)singh](at)EnterpriseDB(dot)com
singh(dot)gurjeet(at){ gmail | hotmail | indiatimes | yahoo }.com

EnterpriseDB http://www.enterprisedb.com

Mail sent from my BlackLaptop device