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

Re: SELECT a value from various tables depending on a column

From: Paul Tillotson <pntil(at)shentel(dot)net>
To: Postgres General <pgsql-general(at)postgresql(dot)org>
Subject: Re: SELECT a value from various tables depending on a column
Date: 2004-10-29 23:43:07
Message-ID: 4182D58B.3060602@shentel.net (view raw or flat)
Thread:
Lists: pgsql-general
What solution to use depends how many other tables and the relative 
sizes of tables, but the following option has a reasonably good chance:

Suppose you have basetable, and joined1, and joined2.  
Basetable.tablename tells which of the secondary tables to join against 
(contains either 'joined1' or 'joined2').

Then join ALL the tables together and use a CASE statement to pick the 
column you want.

SELECT
    basetable.*,
    case when basetable.tablename = 'joined1' then joined1.salary else 
joined2.bingo_money end
FROM basetable
    LEFT JOIN joined1 USING (basetableid)
    LEFT JOIN joined2 USING (basetableid)
WHERE ....

Or something like this.  This will avoid writing any set-returning 
functions or any user code.

Paul

> 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
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings
>
>


In response to

pgsql-general by date

Next:From: Ed L.Date: 2004-10-29 23:46:16
Subject: can't shrink relation
Previous:From: BenDate: 2004-10-29 23:03:45
Subject: Re: column updates

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