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

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
>
>