Database Reference
In-Depth Information
tableoid | schemaname | tablename
--------+------------+----------------------------------
3152249 | public | logs
3152260 | public | logs->logs_2011
3152272 | public | logs->logs_2011->logs_2011_01_02
Get a list of all tables that have child tables but no parent table.
This is the recursive part; it gets all children of tables in
tbls
.
The names of the child tables start with the parental name.
Return parents and all child tables. Because we sort by the table name, which
prepends the parent name, all child tables will follow their parents in their
output.
Lateral Joins
LATERAL
is a new ANSI SQL construction in version 9.3. Here's the motivation behind
it: suppose you perform joins on two tables or subqueries; normally, the pair partici‐
pating in the join are independent units and can't read data from each other. For example,
the following interaction would generate an error because
l.year = 2011
is not a col‐
umn in righthand side of the join:
SELECT
*
FROM
census
.
facts
L
INNER
JOIN
(
SELECT
*
FROM
census
.
lu_fact_types
WHERE
category
=
CASE
WHEN
L
.
yr
=
2011
THEN
'Housing'
ELSE
category
END
)
R
ON
L
.
fact_type_id
=
R
.
fact_type_id
;
Now add the
LATERAL
keyword, and the error is gone:
SELECT
*
FROM
census
.
facts
L
INNER
JOIN
LATERAL
(
SELECT
*
FROM
census
.
lu_fact_types
WHERE
category
=
CASE
WHEN
L
.
yr
=
2011
THEN
'Housing'
ELSE
category
END
)
R
ON
L
.
fact_type_id
=
R
.
fact_type_id
;
LATERAL
lets you share data in columns across two tables in a
FROM
clause. However, it
works only in one direction: the righthand side can draw from the left side, but not vice
versa.
There are situations when you should avail yourself of
LATERAL
to avoid extremely
convoluted syntax. In
Example 7-32
, a column in the left side serves as a parameter in
the
generate_series
function of the right side:
