agora inbox for pljava-dev@postgresql.org  

help / color / mirror / Atom feed

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

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!

Message-ID: <8ECC1E9C-4C83-43DD-B918-E7859309D97C@gmail.com>
Permalink:  ../8ECC1E9C-4C83-43DD-B918-E7859309D97C@gmail.com/
Also on:    postgresql.org/message-id/8ECC1E9C-4C83-43DD-B918-E7859309D97C@gmail.com

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pljava-dev@postgresql.org
  Subject: Re: [Pljava-dev] Function returning array of rows
  In-Reply-To: <8ECC1E9C-4C83-43DD-B918-E7859309D97C@gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox