Database Reference
In-Depth Information
Example 7-17. Query to JSON using json_agg
SELECT
json_agg
(
f
)
As
cats
FROM
(
SELECT
MAX
(
fact_type_id
)
As
max_type
,
category
FROM
census
.
lu_fact_types
GROUP
BY
category
)
As
f
;
DO
The
DO
command allows you to inject a piece of procedural code into your SQL on the
fly. As an example, we'll load the data collected in
Example 3-7
into production tables
from our staging table. We'll use PL/pgSQL for our procedural snippet, but you're free
to use other languages.
Example 7-18
generates a series of
INSERT INTO SELECT
statements. The SQL also per‐
forms an unpivot operation to convert columnar data into rows.
Example 7-18
is only a partial listing of code needed to build
lu_fact
_types. For full code, refer to the
building_cen‐
sus_tables.sql
file that is part of the topic code and data download.
Example 7-18. Using DO to generate dynamic SQL
set
search_path
=
census
;
DROP
TABLE
IF
EXISTS
lu_fact_types
;
CREATE
TABLE
lu_fact_types
(
fact_type_id
serial
,
category
varchar
(
100
),
fact_subcats
varchar
(
255
)[],
short_name
varchar
(
50
),
CONSTRAINT
pk_lu_fact_types
PRIMARY
KEY
(
fact_type_id
)
);
DO
language
plpgsql
$$
DECLARE
var_sql
text
;
BEGIN
var_sql
:
=
string_agg
(
'INSERT INTO lu_fact_types(category, fact_subcats, short_name)
SELECT
''Housing'',
array_agg(s'
||
lpad
(
i
::
text
,
2
,
'0'
)
||
') As fact_subcats,
'
||
quote_literal
(
's'
||
lpad
(
i
::
text
,
2
,
'0'
))
||
' As short_name
FROM staging.factfinder_import
WHERE s'
||
lpad
(
I
::
text
,
2
,
'0'
)
||
' ~ ''^[a-zA-Z]+'' '
,
';'
)
FROM
generate_series
(
1
,
51
)
As
I
;
