calling programs/REST URLs from Postgres

Postgres already has good JSON support. It can copy to/from external files, which is great for easily exporting a table or three, or importing data from a CSV file.  As of 9.3, it can copy from a program or URL!

Example:

# CREATE TABLE worldbank_json (data json);
CREATE TABLE


# COPY worldbank_json FROM PROGRAM 'curl "http://api.worldbank.org/countries?format=json&per_page=100&page=1"';
COPY 1
# COPY worldbank_json FROM PROGRAM 'curl "http://api.worldbank.org/countries?format=json&per_page=100&page=2"';
COPY 1
# COPY worldbank_json FROM PROGRAM 'curl "http://api.worldbank.org/countries?format=json&per_page=100&page=3"';
COPY 1

WITH je AS (
            SELECT json_array_elements(data->1) AS jd FROM  worldbank_json
)
SELECT jd->>'id' AS id, jd->>'name' As country, 
jd#>>'{adminregion,id}' As region_id
FROM je limit 20;

 id  |       country        | region_id
-----+----------------------+-----------
 ABW | Aruba                |
 AFG | Afghanistan          | SAS
 AFR | Africa               |
 AGO | Angola               | SSA
 ALB | Albania              | ECA
 AND | Andorra              |
 ANR | Andean Region        |
 ARB | Arab World           |
 ARE | United Arab Emirates |
 ARG | Argentina            |
 ARM | Armenia              | ECA
 ASM | American Samoa       | EAP
 ATG | Antigua and Barbuda  |
 AUS | Australia            |
 AUT | Austria              |
 AZE | Azerbaijan           | ECA
 BDI | Burundi              | SSA
 BEL | Belgium              |
 BEN | Benin                | SSA
 BFA | Burkina Faso         | SSA
 (20 rows)

Rows marked with "#" are typed into the Postgres PSQL prompt.

Unsurprisingly, there are limitations:
- you must be superuser

Borrowed from http://www.postgresonline.com/journal/archives/325-Using-wget-directly-from-PostgreSQL-using-COPY-FROM-PROGRAM.html