Skip site navigation (1) Skip section navigation (2)

Is this correct usage of generate_series?

From: "Hiroaki Nakamura" <hnakamur(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Is this correct usage of generate_series?
Date: 2008-05-22 17:11:57
Message-ID: 394dc22a0805221011r540a18bbk617be0ca0fcc5645@mail.gmail.com (view raw or flat)
Thread:
Lists: 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;

Thanks in advance,
Hiroaki Nakamura

Responses

pgsql-general by date

Next:From: Reece HartDate: 2008-05-22 17:43:24
Subject: Re: Short-circuiting FK check for a newly-added field
Previous:From: Hiroaki NakamuraDate: 2008-05-22 17:08:51
Subject: Is this correct usage of generate_series?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group