On Thu, May 22, 2008 at 10:41 PM, Hiroaki Nakamura <[EMAIL PROTECTED]> 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, -- [EMAIL PROTECTED] [EMAIL PROTECTED] gmail | hotmail | indiatimes | yahoo }.com EnterpriseDB http://www.enterprisedb.com Mail sent from my BlackLaptop device
