SELECT a value from various tables depending on a column value

[Eric E]

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

[Paul Tillotson]

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

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)