469,630 Members | 1,222 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,630 developers. It's quick & easy.

SELECT a value from various tables depending on a column value

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

Nov 23 '05 #1
1 4444
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)

Nov 23 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

5 posts views Thread by Reestit Mutton | last post: by
14 posts views Thread by Jos? | last post: by
2 posts views Thread by Chris Plowman | last post: by
4 posts views Thread by Aryan | last post: by
33 posts views Thread by bill | last post: by
3 posts views Thread by Jeff | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.