pgAdmin pgScript

pgAdmin has this feature called a pgScript. Its a very simple scripting language for running tasks in a pgAdmin SQL window. The documentation is PgScript manual.

Why would you use it over say writing a plpgsql function?

One main reason we use it is to run quick ad-hoc batch jobs such as geocoding addresses and so forth. The benefit it has over running a stored function is that you don't have to run it as a single transaction.

This is important for certain kinds of tasks where you just want to run something in a loop and have each loop commit separately. To us the syntax with the @ resembles SQL Server Transact-SQL more than it does any PostgreSQL language. WhenI first saw pgScript I thought Wow PgAdmin talks Transact-SQL; now -- what will they think of next :).

Here is an example somewhat adapted from our upcoming Chapter 10: PostGIS in Action book. You would run a pgScript from a PgAdmin Query window by clicking the icon.

This code batch geocodes 500 records at a time committing every 500 records and repeats 20000 times.

DECLARE @I;
SET @I = 0;

WHILE @I < 20000
BEGIN

    UPDATE addr_to_geocode
    SET (rating, norm_address, pt)
    = (g.rating,
        COALESCE ((g.addy).address::text, '')
        || COALESCE(' ' || (g.addy).predirabbrev, '')
        || COALESCE(' ' || (g.addy).streetname,'')
        || ' ' || COALESCE(' ' || (g.addy).streettypeabbrev, '')
        || COALESCE(' ' || (g.addy).location || ', ', '')
        || COALESCE(' ' || (g.addy).stateabbrev, '')
        || COALESCE(' ' || (g.addy).zip, '')
    ,
    g.geomout
    )
        FROM (SELECT DISTINCT ON (addid) addid, (g1.geo).*
        FROM (SELECT addid, (geocode(address)) As geo
        FROM (SELECT * FROM addr_to_geocode WHERE ag.rating IS NULL ORDER BY addid LIMIT 500) As ag
    ) As g1
    -- 5 pick lowest rating
    ORDER BY addid, rating) As g
    WHERE g.addid = addr_to_geocode.addid;


    SET @I = @I + 1;
    PRINT @I;
END