Re: [SQL] Querying a list field against another list

[m]

Try this:
CREATE OR REPLACE FUNCTION csv_matches(TEXT, TEXT) RETURNS BOOLEAN AS
$$
DECLARE
-- $1 is the field text, $2 is the list of ints to try and match.
       m TEXT;
       f TEXT;
       i INTEGER := 1;
       j INTEGER;
BEGIN
       IF $1 IS NULL THEN
               RETURN 'f';
       ELSIF $2 IS NULL THEN
               RETURN 'f';
       END IF;
       LOOP
               m := split_part($2, ',', i);
               IF m LIKE '' THEN
                       RETURN 'f';
               END IF;
               j := 1;
               LOOP
                       f := split_part($1, ',', j);
                       IF f LIKE '' THEN
                               EXIT;
                       END IF;
                       IF f LIKE m THEN
                               RETURN 't';
                       END IF;
                       j := j + 1;
               END LOOP;
               i = i + 1;
       END LOOP;
END;
$$ LANGUAGE 'plpgsql';
Then you can do "select * from foo where csv_matches(da_list, '1,4');"
-Mark.
Aarni Ruuhim�ki wrote:
Hi,
I tried to mail this to the novice list I believe it was rejected:
 

The original message was received at 2004-11-26 14:55:09 +0100
from postoffice.local [10.0.0.1]
  ----- The following addresses had permanent fatal errors -----
<[EMAIL PROTECTED]>
  -----Transcript of session follows -----
... while talking to postoffice.local.:
 

RCPT To:<[EMAIL PROTECTED]>
       

<<< 550 5.1.1 unknown or illegal alias: [EMAIL PROTECTED]
550 <[EMAIL PROTECTED]>... User unknown
 

So here's my question.
Hi people,
This is not quite a pg question, but any suggestions are most welcome.
How can one query a list of values against a db field that contains a list of
values ?
Table foo
foo_id | foo_name | da_list
--------------------------------------
1 | x | 1,2,3,4,5
2 | y | 1,4,5
3 | z | 4,5,11
4 | xyz | 14,15,33
As a result from another query I have parameter bar = '1,4' and want to find
all rows from foo where da_list contains '1' or '4'. So loop over bar to loop
over da_list in foo ?
My humble thanks,
Aarni
--------------
This is a bugfree broadcast to you
from **Kmail**
on **Fedora Core 2** linux system
--------------
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
   (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
 


---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
              http://www.postgresql.org/docs/faqs/FAQ.html