Message-ID: <41A7B668.8050904@markreid.org>
Date: Fri, 26 Nov 2004 15:04:08 -0800
From: m <mail@markreid.org>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US;
	rv:1.7.3) Gecko/20040910
MIME-Version: 1.0
To: aarni@kymi.com
Cc: pgsql-sql@postgresql.org
Subject: Re: Querying a list field against another list
References: <200411261617.57381.aarni@kymi.com>
In-Reply-To: <200411261617.57381.aarni@kymi.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 8bit

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)
>  
>