Leonardo's blog

Hi, today I've read the news about the new PostgreSql 9.3 and, as usual, I went directly to the Release Anouncement, pretty impressive stuff. The part that caught my attention was the JSON related part, so I've read a little more here and couldn't stop the desire to do some tests.

So, the first thing was installing the brand new version on my Ubuntu 12.04:


1) Add this line to /etc/apt/sources.list

deb http://apt.postgresql.org/pub/repos/apt/ precise-pgdg main

2) Execute this command:

sudo wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | \
sudo apt-key add -

3) sudo apt-get update
4) sudo apt-get install postgresql-9.3


Now, let's create a sample database, in this case please download the SQL metadata from: Here

Scriptable version:

wget http://www.commandprompt.com/ppbook/booktown.sql
psql -h 127.0.0.1 -U postgres < booktown.sql

Now, please take a look at the database using PgAdmin III or directly with the command line tool psql.

The database has many tables related to books. I'll create a very simple query to get all the books and its author:

select *
from books b
join authors a on b.author_id=a.id

Now the fun part beggins. To get the results as JSON, replace the former query by this:

The first step is to replace the "select *" with fields:

select
  b.id as book_id,
  title,
  last_name,
  first_name
from books b
join authors a on b.author_id=a.id

The second step is to use slightly modify the original query, to let use the "json_agg" function:

select json_agg(resultset) as json from (
select
  b.id as book_id,
  title,
  last_name,
  first_name
from books b
join authors a on b.author_id=a.id) as resultset

This returns just one string containing our json:

"[{"book_id":41472,"title":"Practical PostgreSQL","last_name":"Worsley","first_name":"John"},
 {"book_id":25908,"title":"Franklin in the Dark","last_name":"Bourgeois","first_name":"Paulette"},
 {"book_id":1234,"title":"The Velveteen Rabbit","last_name":"Bianco","first_name":"Margery Williams"},
 {"book_id":190,"title":"Little Women","last_name":"Alcott","first_name":"Louisa May"},
 {"book_id":7808,"title":"The Shining","last_name":"King","first_name":"Stephen"},
 {"book_id":4513,"title":"Dune","last_name":"Herbert","first_name":"Frank"},
 {"book_id":2038,"title":"Dynamic Anatomy","last_name":"Hogarth","first_name":"Burne"},
 {"book_id":1501,"title":"Goodnight Moon","last_name":"Brown","first_name":"Margaret Wise"},
 {"book_id":156,"title":"The Tell-Tale Heart","last_name":"Poe","first_name":"Edgar Allen"},
 {"book_id":41477,"title":"Learning Python","last_name":"Lutz","first_name":"Mark"},
 {"book_id":41473,"title":"Programming Python","last_name":"Lutz","first_name":"Mark"},
 {"book_id":41478,"title":"Perl Cookbook","last_name":"Christiansen","first_name":"Tom"},
 {"book_id":4267,"title":"2001: A Space Odyssey","last_name":"Clarke","first_name":"Arthur C."},
 {"book_id":1590,"title":"Bartholomew and the Oobleck","last_name":"Geisel","first_name":"Theodor Seuss"},
 {"book_id":1608,"title":"The Cat in the Hat","last_name":"Geisel","first_name":"Theodor Seuss"}]"

Now, from your Lazarus/fpc program you can easily get the JSON string.

One possible usage for this that comes to my mind, is a CGI app that returns a resultset to an EXTjs grid.  The CGI just executes the query and return the string in the TResponse handler as ContentType "application/json".