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