Odd unfamiliar Postgres SQL syntax

From: Sonny <sonny(dot)chee(at)gmail(dot)com>
To: pgsql-sql(at)postgresql(dot)org
Subject: Odd unfamiliar Postgres SQL syntax
Date: 2017-07-31 16:42:12
Message-ID: CAF1JCQGvmMJiEYssyKe9+PRqrfJNE9yHNfrSvpQkNrW_drQM=Q@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

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;

--
Sonny.
----------------------------------------------------------------------------
Be true to your work, your word, and your friend. Henry David Thoreau.

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message David G. Johnston 2017-07-31 19:01:14 Re: Odd unfamiliar Postgres SQL syntax
Previous Message srilinux 2017-07-31 14:57:05 Database duplicate copy