Using pgAdmin - PostgreSQL: Up and Running - page 71

Loop through records using LINES function.
WHILE @ I < LINES ( @ labels )
BEGIN
SET @ tdef = @ tdef + ', ' + @ labels [ @ I ][ 0 ] + ' numeric(12,3) ' ;
SET @ I = @ I + 1 ;
END
SET @ tdef = @ tdef + ')' ;
Print out table def.
PRINT @ tdef ;
create the table.
CREATE TABLE @ tdef ;
Although pgScript does not have an execute command that allows you to run dynam‐
ically generated SQL, we accomplished the same in Example 4-1 by assigning a SQL
string to a variable. Example 4-2 pushes the envelope a bit further by populating the
census.hisp_pop table we just created.
Example 4-2. Populating tables with pgScript loop
DECLARE @ I , @ labels , @ tload , @ tcols , @ fact_types ;
SET @ I = 0 ;
SET @ labels =
SELECT
quote_ident (
replace (
replace (
lower ( COALESCE ( fact_subcats [ 4 ], fact_subcats [ 3 ])), ' ' ,
'_' ), ':' , ''
)
) As col_name ,
fact_type_id
FROM census . lu_fact_types
WHERE category = 'Population' AND fact_subcats [ 3 ] ILIKE 'Hispanic or Latino%'
ORDER BY short_name ;
SET @ tload = 'tract_id' ;
SET @ tcols = 'tract_id' ;
SET @ fact_types = '-1' ;
WHILE @ I < LINES ( @ labels )
BEGIN
SET @ tcols = @ tcols + ', ' + @ labels [ @ I ][ 0 ] ;
SET @ tload = @ tload +
', MAX(CASE WHEN fact_type_id = ' +
CAST ( @ labels [ @ I ][ 1 ] AS STRING ) +
' THEN val ELSE NULL END)' ;
SET @ fact_types = @ fact_types + ', ' + CAST ( @ labels [ @ I ][ 1 ] As STRING );
SET @ I = @ I + 1 ;
END