Using pgAdmin - PostgreSQL: Up and Running - page 70

pgScript
pgScript is a built-in scripting tool in pgAdmin. It's most useful for running repetitive
SQL tasks. pgScript can make better use of memory, and thus be more efficient, than
equivalent PostgreSQL functions. This is because stored functions maintain all their
work in memory and commit all the results of a function in a single batch. In contrast,
pgScript commits each SQL insert or update statement as it runs through the script.
This makes pgScript particularly handy for memory-hungry processes that you don't
need completed as a single transaction. Once a particular transaction commits, memory
is available for the next one. You can see an example of where we use it for batch geo‐
coding at a Using pgScript for Geocoding .

The pgScript language is lazily typed and supports conditionals, loops, data generators,
basic print statements, and record variables. The general syntax is similar to that of
Transact SQL, the stored procedure language of Microsoft SQL Server. Variables, pre‐
pended with @ , can hold scalars or arrays, including the results of SQL commands.
Commands such as DECLARE and SET , and control constructs such as IF-ELSE and WHILE
loops, are part of the pgScript language.
Launch pgScript by opening a regular SQL query window. After typing in your script,
execute it by clicking the pgScript icon ( ).

We'll now show you some examples of pgScripts. Example 4-1 demonstrates how to use
pgScript record variables and loops to build a cross-tab table, using the lu_fact_types
table we create in Example 7-18 . The pgScript creates an empty table called cen
sus.hisp_pop with numeric columns: hispanic_or_latino , white_alone ,
black_or_african_american_alone , and so on.

Example 4-1. Create table using record variables in pgScript
DECLARE @ I , @ labels , @ tdef ;
SET @ I = 0 ;

Labels will hold records.
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 @ tdef = 'census.hisp_pop(tract_id varchar(11) PRIMARY KEY ' ;