Can someone help me understand the following SQL? What does the notation (i.keys).n and (i.keys).x mean... as indicated by the <<<=== in the query. Any help would be appreciated.
SELECT NULL AS TABLE_CAT , n.nspname AS TABLE_SCHEM , ct.relname AS TABLE_NAME , a.attname AS COLUMN_NAME , (i.keys).n AS KEY_SEQ <<<============== , ci.relname AS PK_NAME FROM pg_catalog.pg_class ct JOIN pg_catalog.pg_attribute a ON (ct.oid = a.attrelid) JOIN pg_catalog.pg_namespace n ON (ct.relnamespace = n.oid) JOIN ( SELECT i.indexrelid , i.indrelid , i.indisprimary , information_schema._pg_expandarray(i.indkey) AS keys FROM pg_catalog.pg_index i ) i ON (a.attnum = (i.keys).x AND a.attrelid = i.indrelid) <<<=========== JOIN pg_catalog.pg_class ci ON (ci.oid = i.indexrelid) WHERE n.nspname = 'edw' AND ct.relname = 'campaign_dim' AND i.indisprimary ORDER BY table_name , pk_name , key_seq;
It is used for a access to field of composite value
create type foo as (a int, b int);
create table xx (f1 foo, f2 foo);
insert into xx values(row(10,20), row(30,40));
postgres=# select * from xx;
┌─────────┬─────────┐
│ f1 │ f2 │
╞═════════╪═════════╡
│ (10,20) │ (30,40) │
└─────────┴─────────┘
(1 row)
postgres=# select (xx.f1).a from xx;
┌────┐
│ a │
╞════╡
│ 10 │
└────┘
(1 row)
Regards
Pavel
--
Sonny. ---------------------------------------------------------------------------- Be true to your work, your word, and your friend. Henry David Thoreau.
