John Gunther <postgresql@bucksvsbytes.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°
