Date: Sun, 17 Jun 2007 14:06:46 +0200
From: Andreas Kretschmer <akretschmer@spamfence.net>
To: pgsql-sql@postgresql.org
Subject: Re: joining a table whose name is stored in the primary record
Message-ID: <20070617120646.GA29549@KanotixBox>
References: <4674F357.5040409@bucksvsbytes.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: <4674F357.5040409@bucksvsbytes.com>
User-Agent: mutt-ng 1.5.9i (Linux)

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°