Обсуждение: [SQL] Odd unfamiliar Postgres SQL syntax
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_
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;
--
Sonny.
----------------------------------------------------------------------------
Be true to your work, your word, and your friend. Henry David Thoreau.
----------------------------------------------------------------------------
Be true to your work, your word, and your friend. Henry David Thoreau.
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.
Basically:
i = relation name
keys = composite column name
x = component name within the composite
"keys.x" doesn't work because the system thinks that "keys" should be a relation name when written that way.
David J.
Hi
2017-07-31 18:42 GMT+02:00 Sonny <sonny.chee@gmail.com>:
Hey GuysCan 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.
Sonny <sonny.chee@gmail.com> writes:
> 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.
"(i.keys).n" is selecting the column named "n" from the composite-valued
column "i.keys", where "i" is a table alias exposed by the FROM clause.
The reason we don't just write "i.keys.n" is that the SQL standard says
that should mean column "n" in a table "i.keys" exposed by the FROM
clause --- that is, "i" would be a schema name not a table alias, and
"keys" would be a table name not a column name.
regards, tom lane