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 [EMAIL PROTECTED])
