Re: Odd unfamiliar Postgres SQL syntax

Hi

2017-07-31 18:42 GMT+02:00 Sonny <sonny(dot)chee(at)gmail(dot)com>:

> Hey Guys
>
> 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.
>