Data Types - PostgreSQL: Up and Running - page 103

unnest ( xpath ( '/family/member' , profile )
) As f FROM families ) x ;
family | relation | mem_name
--------+----------+----------
Gomez | padre | Alex
Gomez | madre | Sonia
Gomez | hijo | Brandon
Gomez | hija | Azaleah
Get the text element in the relation and name tags of each member element. We
need to use array subscripting because xpath always returns an array, even if
only one element is returned.
Get the name attribute from family root. For this we use @ attribute_name .
Break into subelements <member> , <relation> , </relation> , <name> , </
name> , and </member> tags. The slash is a way of getting at subtag elements. For
example, xpath('/family/member', 'profile') will return an array of all
members in each family that is defined in a profile. The @ sign is used to select
attributes of a an element. So, for example, family/@name returns the name
attribute of a family . By default, xpath always returns an element, including the
tag part. The text() forces a return of just the text body of an element.
Custom and Composite Data Types
This section demonstrates how to define and use a custom type. The composite (aka
record , row ) object type is often used to build an object that is then cast to a custom
type, or as a return type for functions needing to return multiple columns.
All Tables Are Custom Data Types
PostgreSQL automatically creates custom types for all tables. For all intents and pur‐
poses, you can use custom types just as you would any other built-in type. So we could
conceivably create a table that has a column type that is another table's custom type, and
we can go even further and make an array of that type. We demonstrate this “turducken”
in Example 5-33 .
Example 5-33. Turducken
CREATE TABLE chickens ( id integer PRIMARY KEY );
CREATE TABLE ducks ( id integer PRIMARY KEY , chickens chickens []);
CREATE TABLE turkeys ( id integer PRIMARY KEY , ducks ducks []);
INSERT INTO ducks VALUES ( 1 , ARRAY [ ROW ( 1 ):: chickens , ROW ( 1 ):: chickens ]);
INSERT INTO turkeys VALUES ( 1 , array ( SELECT d FROM ducks d ));