Skip site navigation (1) Skip section navigation (2)

SELECT a value from various tables depending on a column value

From: Eric E <whalesuit(at)bonbon(dot)net>
To: pgsql-general(at)postgresql(dot)org
Subject: SELECT a value from various tables depending on a column value
Date: 2004-10-29 19:20:52
Message-ID: 41829814.8070808@bonbon.net (view raw or flat)
Thread:
Lists: pgsql-general
Hi,
    I'm trying to write a recordset-returning function that returns a 
values from a base table, and one column from a joined table, where the 
joined table varies according to a field of the base table.  I'm looking 
for an efficieint way to do this, and I don't think I know enough about 
Postgres' capabilities to know how to do this.

I imagine fetching my base table rows in order of the table reference 
column, looping over my base table, and setting a refcursor to a new 
joined table when the table reference column changes.  I would then 
fetch from the appropriate joined table cursor to get the joined value 
for each row.

So my question is a performance one: is this a sensible way to do this, 
or am I missing something altogether about hierarchies of tables. 

Or can I fetch a bunch of rows into memory and loop over them there, 
thus avoid queries to look up individual rows over and over.

Any ideas would be much appreciated.

Many thanks,

Eric


Responses

pgsql-general by date

Next:From: BenDate: 2004-10-29 19:24:35
Subject: column updates
Previous:From: Thomas F.O'ConnellDate: 2004-10-29 18:21:02
Subject: (b)trim anomalies

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group