Re: Is this correct usage of generate_series? - Mailing list pgsql-general

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;