agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedOdd unfamiliar Postgres SQL syntax
4+ messages / 4 participants
[nested] [flat]
* Odd unfamiliar Postgres SQL syntax
@ 2017-07-31 16:42 Sonny <sonny.chee@gmail.com>
0 siblings, 3 replies; 4+ messages in thread
From: Sonny @ 2017-07-31 16:42 UTC (permalink / raw)
To: 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.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Odd unfamiliar Postgres SQL syntax
@ 2017-07-31 19:01 David G. Johnston <david.g.johnston@gmail.com>
parent: Sonny <sonny.chee@gmail.com>
2 siblings, 0 replies; 4+ messages in thread
From: David G. Johnston @ 2017-07-31 19:01 UTC (permalink / raw)
To: Sonny <sonny.chee@gmail.com>; +Cc: pgsql-sql
On Mon, Jul 31, 2017 at 9:42 AM, Sonny <sonny.chee@gmail.com> wrote:
> 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.
>
https://www.postgresql.org/docs/9.6/static/sql-expressions.html#FIELD-SELECTION
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.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Odd unfamiliar Postgres SQL syntax
@ 2017-07-31 19:02 Pavel Stehule <pavel.stehule@gmail.com>
parent: Sonny <sonny.chee@gmail.com>
2 siblings, 0 replies; 4+ messages in thread
From: Pavel Stehule @ 2017-07-31 19:02 UTC (permalink / raw)
To: Sonny <sonny.chee@gmail.com>; +Cc: pgsql-sql
Hi
2017-07-31 18:42 GMT+02:00 Sonny <sonny.chee@gmail.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.
>
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Odd unfamiliar Postgres SQL syntax
@ 2017-07-31 19:03 Tom Lane <tgl@sss.pgh.pa.us>
parent: Sonny <sonny.chee@gmail.com>
2 siblings, 0 replies; 4+ messages in thread
From: Tom Lane @ 2017-07-31 19:03 UTC (permalink / raw)
To: Sonny <sonny.chee@gmail.com>; +Cc: pgsql-sql
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
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2017-07-31 19:03 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2017-07-31 16:42 Odd unfamiliar Postgres SQL syntax Sonny <sonny.chee@gmail.com>
2017-07-31 19:01 ` David G. Johnston <david.g.johnston@gmail.com>
2017-07-31 19:02 ` Pavel Stehule <pavel.stehule@gmail.com>
2017-07-31 19:03 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox