From: bensmailinglists at gmail.com (Bendik Rognlien Johansen)
Date: Wed, 13 Sep 2006 13:09:14 +0200
Subject: [Pljava-dev] Function returning array of rows
Message-ID: <8ECC1E9C-4C83-43DD-B918-E7859309D97C@gmail.com>

Hello, I have the following tables (simplified):

records (~8 million rows)
- id integer
- name varchar

addresses (~9 million rows)
- id integer
- record integer
- address varchar
- postalcode varchar
- postalsite varchar

contacts (~12 million rows)
- id integer
- record integer
- value varchar
- type integer



I need to fetch everything to build a Lucene index. My options as I  
see it:

1: Join records, addresses and contacts, sort by record.id. Given a  
record with 2 addresses and 3 contacts, the result would look  
something like this:

name			address			contact
John Doe		Elm street		555-123456
John Doe		P.O Box 123		555-123456
John Doe		Elm street		555-997788
John Doe		P.O Box 123		555-997788
John Doe		Elm street		555-666666
John Doe		P.O Box 123		555-666666


This is sloow due to sorting, and a lot of redundant data is fetched.  
No good


2. Same as 1 but GROUP by record.id, using a custom aggregate  
function to create an array of addresses and contacts.
Too slow and error prone.


3. I was hoping to find a way of doing something like this:
SELECT r.*
, MY_NEW_FUNCTION(SELECT * FROM addresses a WHERE a.record = r.id) AS  
addresses
,  MY_NEW_FUNCTION(SELECT * FROM contacts c WHERE c.record = r.id) AS  
contacts
FROM records r;

Where "MY_NEW_FUNCTION" would return an array of the user defined  
type "Address" or "Contact".


I have found subselects to perform very well. Does this look  
reasonable, or am I being silly?

Any tips on writing the "MY_NEW_FUNCTION"?

Thanks!