Search Postgresql Archives

Re: query from a list of ids

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux