Обсуждение: Querying a list field against another list
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 --------------
On Fri, Nov 26, 2004 at 16:17:57 +0200, Aarni Ruuhimäki <aarni@kymi.com> wrote: > Hi, > > I tried to mail this to the novice list I believe it was rejected: No you weren't. The mail server that is reporting that error is sending the response to the wrong place. It is supposed to be going to the envelope sender address, not the address in the from header. The message only indicates that that one address didn't get a copy of your message. > > >> > 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 > >>
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)
>
>