Is this correct usage of generate_series?
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
On Thu, May 22, 2008 at 10:41 PM, Hiroaki Nakamura <hnakamur@gmail.com>
wrote:
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;
All three of these are correct usages. This is an SRF (Set Returning
Function), and Postgres allows you to use an SRF in the SELECT list. People
have been using tricks similar to what you showed above, and it works great.
I have seen discussions in the past on the -hackers mailing list about
deprecating the usage of SRFs in select list, but honestly, I don't see this
usage being deprecated anytime soon; it's pretty useful, and I would call it
an extension to the SQL language. And as long as it's nit buggy, and ALA it
doesn't surprise people in a wrong way, I think such usage will remain.
Best regards,
--
gurjeet[.singh]@EnterpriseDB.com
singh.gurjeet@{ gmail | hotmail | indiatimes | yahoo }.com
EnterpriseDB http://www.enterprisedb.com
Mail sent from my BlackLaptop device
On Thu, May 22, 2008 at 9:38 PM, Gurjeet Singh <singh.gurjeet@gmail.com> wrote:
I have seen discussions in the past on the -hackers mailing list about
deprecating the usage of SRFs in select list, but honestly, I don't see this
usage being deprecated anytime soon; it's pretty useful, and I would call it
an extension to the SQL language. And as long as it's nit buggy, and ALA it
doesn't surprise people in a wrong way, I think such usage will remain.
I think it's useful too...I have a good example of how it can simplify
a query in the archives.
My main gripe with 'SRF in select' is multiple SRF in the select list...
select a(), b() from foo;
The current behavior gives you LCM(a(), b()) * foo records...I think
the expected behavior would be a * b * foo (foo being number of
records in foo).
merlin