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
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? |