pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedjoining a table whose name is stored in the primary record
6+ messages / 3 participants
[nested] [flat]
* joining a table whose name is stored in the primary record
@ 2007-06-17 08:39 John Gunther <postgresql@bucksvsbytes.com>
0 siblings, 2 replies; 6+ messages in thread
From: John Gunther @ 2007-06-17 08:39 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: joining a table whose name is stored in the primary record
@ 2007-06-17 12:06 Andreas Kretschmer <akretschmer@spamfence.net>
parent: John Gunther <postgresql@bucksvsbytes.com>
1 sibling, 1 reply; 6+ messages in thread
From: Andreas Kretschmer @ 2007-06-17 12:06 UTC (permalink / raw)
To: pgsql-sql
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°
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: joining a table whose name is stored in the primary record
@ 2007-06-17 15:54 Andrew Sullivan <ajs@crankycanuck.ca>
parent: John Gunther <postgresql@bucksvsbytes.com>
1 sibling, 1 reply; 6+ messages in thread
From: Andrew Sullivan @ 2007-06-17 15:54 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: joining a table whose name is stored in the primary record
@ 2007-06-19 18:02 John Gunther <postgresql@bucksvsbytes.com>
parent: Andrew Sullivan <ajs@crankycanuck.ca>
0 siblings, 1 reply; 6+ messages in thread
From: John Gunther @ 2007-06-19 18:02 UTC (permalink / raw)
To: pgsql-sql
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
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 using subqueries 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 application is a sales tax table, where the
tax jurisdiction can depend on any element of the address hierarchy. In
different areas, it could depend on zip/postal code, city,
state/province, nation, or even street address. I originally considered
storing 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 elements frequently 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 assign a tax jurisdiction to
every street segment but that would create a daunting data maintenance
problem as it requires propagating 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 will do the
job. I was hoping for a pure SQL solution but his approach will
definitely work.<br>
<br>
John<br>
</body>
</html>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: joining a table whose name is stored in the primary record
@ 2007-06-19 18:04 John Gunther <postgresql@bucksvsbytes.com>
parent: Andreas Kretschmer <akretschmer@spamfence.net>
0 siblings, 0 replies; 6+ messages in thread
From: John Gunther @ 2007-06-19 18:04 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: joining a table whose name is stored in the primary record
@ 2007-06-19 18:29 Andrew Sullivan <ajs@crankycanuck.ca>
parent: John Gunther <postgresql@bucksvsbytes.com>
0 siblings, 0 replies; 6+ messages in thread
From: Andrew Sullivan @ 2007-06-19 18:29 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2007-06-19 18:29 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-06-17 08:39 joining a table whose name is stored in the primary record John Gunther <postgresql@bucksvsbytes.com>
2007-06-17 12:06 ` Andreas Kretschmer <akretschmer@spamfence.net>
2007-06-19 18:04 ` John Gunther <postgresql@bucksvsbytes.com>
2007-06-17 15:54 ` Andrew Sullivan <ajs@crankycanuck.ca>
2007-06-19 18:02 ` John Gunther <postgresql@bucksvsbytes.com>
2007-06-19 18:29 ` Andrew Sullivan <ajs@crankycanuck.ca>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox