Re: Querying a list field against another list

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 -----
><jens@headlong.se>
>
>   -----Transcript of session follows -----
>... while talking to postoffice.local.:
>  
>
>>>>RCPT To:<jens@headlong.se>
>>>>        
>>>>
><<< 550 5.1.1 unknown or illegal alias: jens@headlong.se
>550 <jens@headlong.se>... 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 majordomo@postgresql.org)
>  
>