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.
SET @I = 0;
WHILE @I < 20000
SET (rating, norm_address, pt)
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, '')
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
ORDER BY addid, rating) As g
WHERE g.addid = addr_to_geocode.addid;
SET @I = @I + 1;