From: m <mail@markreid.org>
To: aarni@kymi.com
Cc: pgsql-sql@postgresql.org
Subject: Re: Querying a list field against another list
Date: Fri, 26 Nov 2004 15:04:08 -0800
Message-ID: <41A7B668.8050904@markreid.org> (raw)
In-Reply-To: <200411261617.57381.aarni@kymi.com>
References: <200411261617.57381.aarni@kymi.com>
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)
>
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: mail@markreid.org, aarni@kymi.com
Subject: Re: Querying a list field against another list
In-Reply-To: <41A7B668.8050904@markreid.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox