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(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, '')
)FROM(SELECTDISTINCTON(addid) addid, (g1.geo).*FROM(SELECT addid, (geocode(address))As geo
FROM(SELECT*FROM addr_to_geocode WHERE ag.rating ISNULLORDERBY addid LIMIT 500)As ag
--5picklowestratingORDERBY addid, rating)As g
WHERE g.addid = addr_to_geocode.addid;