Skip site navigation (1) Skip section navigation (2)

Re: Querying a list field against another list

From: m <mail(at)markreid(dot)org>
To: aarni(at)kymi(dot)com
Cc: pgsql-sql(at)postgresql(dot)org
Subject: Re: Querying a list field against another list
Date: 2004-11-26 23:04:08
Message-ID: 41A7B668.8050904@markreid.org (view raw or flat)
Thread:
Lists: pgsql-sql
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)
>  
>


In response to

pgsql-sql by date

Next:From: Jonathan KnoppDate: 2004-11-26 23:56:52
Subject: Re: Way to stop recursion?
Previous:From: Pierre-Frédéric CaillaudDate: 2004-11-26 22:21:51
Subject: Re: Way to stop recursion?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group