Database Reference
In-Depth Information
Census Tract 2002, Dukes County, Massachusetts
Census Tract 2003, Dukes County, Massachusetts
Census Tract 2004, Dukes County, Massachusetts
Census Tract 9900, Dukes County, Massachusetts
Returning Functions
PostgreSQL allows functions that return sets to appear in the
SELECT
clause of an SQL
statement. This is not true of many other databases, in which only scalar functions can
appear in the
SELECT
.
Interweaving some set-returning functions inside an already complicated query could
easily produce results that are beyond what you expect, because these functions usually
result in the creation of new rows in the results. You must anticipate this if you'll be
using the results as a subquery. In
Example 7-13
, we demonstrate this with a temporal
version of
generate_series
. The example uses a table that we construct with:
CREATE
TABLE
interval_periods
(
i_type
interval
);
INSERT
INTO
interval_periods
(
i_type
)
VALUES
(
'5 months'
),
(
'132 days'
),
(
'4862 hours'
);
Example 7-13. Set-returning function in SELECT
SELECT
i_type
,
generate_series
(
'2012-01-01'
::
date
,
'2012-12-31'
::
date
,
i_type
)
As
dt
FROM
interval_periods
;
i_type | dt
-----------+------------------------
5 months | 2012-01-01 00:00:00-05
5 months | 2012-06-01 00:00:00-04
5 months | 2012-11-01 00:00:00-04
132 days | 2012-01-01 00:00:00-05
132 days | 2012-05-12 00:00:00-04
132 days | 2012-09-21 00:00:00-04
4862 hours | 2012-01-01 00:00:00-05
4862 hours | 2012-07-21 15:00:00-04
Restricting DELETE, UPDATE, SELECT from Inherited Tables
When you query from a table that has child tables, the query drills down into the chil‐
dren, creating a union of all the child records satisfying the query condition.
DELETE
and
UPDATE
work the same way, drilling down the hierarchy for victims. Sometimes this
is not desirable and you want data to come only from the table you specified, without
the kids tagging along.
This is where the
ONLY
keyword comes in handy. We show an example of its use in
Example 7-30
, where we want to delete only those records from the production table
that haven't migrated to the log table. Without the
ONLY
modifier, we'd end up deleting
records from the child table that might have already been moved previously.
