|The PostgreSQL 9.0 Reference Manual - Volume 1A - SQL Language Reference
by The PostgreSQL Global Development Group
Paperback (6"x9"), 454 pages
RRP £14.95 ($19.95)
Sales of this book support the PostgreSQL project! Get a printed copy>>>
6.15.3 Accessing Composite Types
To access a field of a composite column, one writes a dot and the field
name, much like selecting a field from a table name. In fact, it's so
much like selecting from a table name that you often have to use parentheses
to keep from confusing the parser. For example, you might try to select
some subfields from our
on_hand example table with something
SELECT item.name FROM on_hand WHERE item.price > 9.99;
This will not work since the name
item is taken to be a table
name, not a column name of
on_hand, per SQL syntax rules.
You must write it like this:
SELECT (item).name FROM on_hand WHERE (item).price > 9.99;
or if you need to use the table name as well (for instance in a multitable query), like this:
SELECT (on_hand.item).name FROM on_hand WHERE (on_hand.item).price > 9.99;
Now the parenthesized object is correctly interpreted as a reference to
item column, and then the subfield can be selected from it.
Similar syntactic issues apply whenever you select a field from a composite value. For instance, to select just one field from the result of a function that returns a composite value, you'd need to write something like:
SELECT (my_func(...)).field FROM ...
Without the extra parentheses, this will generate a syntax error.
|ISBN 9781906966041||The PostgreSQL 9.0 Reference Manual - Volume 1A - SQL Language Reference||See the print edition|