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