Re: query from a list of ids

am Tue, dem 24.04.2007, um 14:19:05 -0700 mailte finecur folgendes:
> Hi,
>
> Here is my first table:
>
> Table1
>
> name| ids
> -------------------------
> Peter| 2, 3, 4, 5
> Jack| 100, 34, 3
>
> Both name and ids are in text format.
>
> Here is my second table
>
> Table2
>
> id | Flag | Title
> ---------------------
> 2 | Red | good
> 3 | Blue | poor
> 4 | Green| middle
>
> id is in integer (serial) format.
>
> I would like to list all the rows in table 2 where the id is in the
> ids field of peter. So I did
>
> select * from tables where id in (select ids from table1 where
> name='Peter')
>
> It did not work. How can I do the query?

Broken design, you should normalise your schema.

Okay, let me try a solution:

First, create a function:

create or replace function ids(text) returns setof t2 as $$
declare
_ids text;
_sql text;
_rec record;
begin
select into _ids ids from t1 where name = $1;
_sql := 'select * from t2 where id in (' || coalesce(_ids,'NULL') || ');';
for _rec in execute _sql loop
return next _rec;
end loop;
end;
$$ language plpgsql;

Hint: the coalesce avoid errors if there are no result.

Okay, we have 2 tables:

test=*# select * from t1;
name | ids
-------+------------
Peter | 2, 3, 4, 5
Jack | 100, 34, 3
(2 rows)

test=*# select * from t2;
id | flag | title
----+-------+--------
2 | Red | good
3 | Blue | poo
4 | Green | middle
(3 rows)

test=*# select * from ids('Peter');
id | flag | title
----+-------+--------
2 | Red | good
3 | Blue | poo
4 | Green | middle
(3 rows)

test=*# select * from ids('Jack');
id | flag | title
----+------+-------
3 | Blue | poo
(1 row)

test=*# select * from ids('nobody');
id | flag | title
----+------+-------
(0 rows)

Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net