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
Views: Raw Message | Whole Thread | Download mbox | Resend email
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

Browse pgsql-general by date

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