Thread: joining a table whose name is stored in the primary record
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. Thanks. John Gunther Bucks vs Bytes Inc
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°
On Sun, Jun 17, 2007 at 04:39:51AM -0400, John Gunther wrote: > functions and using subqueries without success. I think I need someone > to point me in the right conceptual direction. Well, the right SQL-esque conceptual direction is not to have different tables at all. That's not a very normal-form thing to do, because the data has been broken into pieces dependent on the data itself, rather than the kind of data it is. A -- Andrew Sullivan | ajs@crankycanuck.ca In the future this spectacle of the middle classes shocking the avant- garde will probably become the textbook definition of Postmodernism. --Brad Holland
Andrew Sullivan wrote: <blockquote cite="mid20070617155441.GB13671@phlogiston.dyndns.org" type="cite"><pre wrap="">On Sun,Jun 17, 2007 at 04:39:51AM -0400, John Gunther wrote: </pre><blockquote type="cite"><pre wrap="">functions and usingsubqueries without success. I think I need someone to point me in the right conceptual direction. </pre></blockquote><pre wrap=""> Well, the right SQL-esque conceptual direction is not to have different tables at all. That's not a very normal-form thing to do, because the data has been broken into pieces dependent on the data itself, rather than the kind of data it is. A </pre></blockquote> Well, Andrew, you're certainly right but I made an exception because of the data needs. The real applicationis a sales tax table, where the tax jurisdiction can depend on any element of the address hierarchy. In differentareas, it could depend on zip/postal code, city, state/province, nation, or even street address. I originally consideredstoring all address elements in one giant table with parent/child relationships (zip 11208's parent is Brooklyn,Brooklyn's parent is Kings County, Kings County's parent is NY, etc but brief analysis showed that address elementsfrequently have more than one parent. So I decided to keep the elements in separate tables (nation, city, street,zip, etc) to allow more complex relationships. That led me to the sales tax jurisdiction problem. I could just assigna tax jurisdiction to every street segment but that would create a daunting data maintenance problem as it requirespropagating jurisdiction changes down through every segment and ensuring every valid street segment has a record.<br/><br /> It's an interesting problem.<br /><br /> Another respondent suggested a programmed function that willdo the job. I was hoping for a pure SQL solution but his approach will definitely work.<br /><br /> John<br />
Andreas Kretschmer wrote: > 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; > Thanks, Andrew. I was hoping for a pure SQL solution but your idea will certainly work. John
On Tue, Jun 19, 2007 at 02:02:46PM -0400, John Gunther wrote: > Well, Andrew, you're certainly right but I made an exception because of > the data needs. Ah, well, in that case, you'll need something other than SQL for sure. A function as suggested is probably your friend. A -- Andrew Sullivan | ajs@crankycanuck.ca Users never remark, "Wow, this software may be buggy and hard to use, but at least there is a lot of code underneath." --Damien Katz