Re: joining a table whose name is stored in the primary record

John Gunther <postgresql(at)bucksvsbytes(dot)com> schrieb:

> I've tried everything I can think of here to join records when the join
> table varies and is named in the primary record, but to no avail. Here's an
> example with all non-essentials stripped out.
>
> I have 3 tables:
>
> create table zip (
> id serial primary key,
> name text,
> parent_tbl text,
> parent_id int
> );
>
> create table city (
> id serial primary key,
> name text
> );
>
> create table county (
> id serial primary key,
> name text
> );
>
> The zip table has 2 records as follows:
> id|name|parent_tbl|parent_id
> -----------------------------
> 1 |10001|city |12
> 2 |19999|county |99
>
> The possible parent tables can be many more than the two examples, city and
> county.
>
> In a single psql statement, I want to retrieve zip records joined with the
> record of their respective parents. The join id is in zip.parent_id but the
> obvious issue is that the join table varies and is only found in
> zip.parent_tbl. Obviously, I can select from zip, then step through the
> results and select the joined data separately for each zip result. How can
> I get these results in one statement? I've tried writing SQL functions and
> using subqueries without success. I think I need someone to point me in the
> right conceptual direction.

Something like this:

test=*# select * from zip;
id | name | parent_tbl | parent_id
----+-------+------------+-----------
1 | 10001 | city | 12
2 | 19999 | country | 99
(2 rows)

Time: 0.834 ms
test=*# select * from city;
id | name
----+----------
12 | value 12
(1 row)

Time: 0.790 ms
test=*# select * from country;
id | name
----+----------
99 | value 99
(1 row)

Now I create a function:

create or replace function zip_foo(OUT out_id int, OUT out_name text, OUT out_name2 text) returns setof record as $$
declare
my_rec RECORD;
my_name TEXT;
begin
for my_rec in select id, name, parent_tbl, parent_id from zip LOOP
execute 'select name from ' || my_rec.parent_tbl || ' where id = ' || my_rec.parent_id || ';' into my_name;
out_id := my_rec.id;
out_name := my_rec.name;
out_name2 := my_name;
return next;
end loop;
end;
$$ language plpgsql;

And now a test:

test=*# select * from zip_foo();
out_id | out_name | out_name2
--------+----------+-----------
1 | 10001 | value 12
2 | 19999 | value 99
(2 rows)

HTH, Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect. (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly." (unknow)
Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°