Re:Heroku Postgres: SQL Database-as-a-Service
by einhverfr on Wednesday November 23, 2011 @01:37AM
(#3269066)
you can do the same in PostgreSQL with joins.
For example:
select i.* , array_agg(ROW(l.*)::text)
from invoice i
join invoice_line l ON i.id = l.invoice_id
where i.id = 1023;
This returns a single row for invoice, along with an array of tuples representation of all invoice lines. You could also use casts and stored procedure languages to convert to xml or whatever if you want.
But you're not actually joining any data. You return one document, and then exploit the properties of sorting to return the related document(s) next in the collection. In SQL-speak, each relation comes as a distinct row and are only connected by the order in which the rows are returned. There is a writeup on the specifics here, if you are curious: http://wiki.apache.org/couchdb/View_collation
It is a pretty clever trick that can save you a round trip to the DB, but doesn't have nearly the power that a real join does.