pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedQuerying a list field against another list
3+ messages / 3 participants
[nested] [flat]
* Querying a list field against another list
@ 2004-11-26 14:17 Aarni Ruuhimäki <aarni@kymi.com>
2004-11-26 15:56 ` Re: Querying a list field against another list Bruno Wolff III <bruno@wolff.to>
2004-11-26 23:04 ` Re: Querying a list field against another list m <mail@markreid.org>
0 siblings, 2 replies; 3+ messages in thread
From: Aarni Ruuhimäki @ 2004-11-26 14:17 UTC (permalink / raw)
To: pgsql-sql
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
--------------
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Querying a list field against another list
2004-11-26 14:17 Querying a list field against another list Aarni Ruuhimäki <aarni@kymi.com>
@ 2004-11-26 15:56 ` Bruno Wolff III <bruno@wolff.to>
1 sibling, 0 replies; 3+ messages in thread
From: Bruno Wolff III @ 2004-11-26 15:56 UTC (permalink / raw)
To: Aarni Ruuhimäki <aarni@kymi.com>; +Cc: pgsql-sql
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
> >>
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Querying a list field against another list
2004-11-26 14:17 Querying a list field against another list Aarni Ruuhimäki <aarni@kymi.com>
@ 2004-11-26 23:04 ` m <mail@markreid.org>
1 sibling, 0 replies; 3+ messages in thread
From: m @ 2004-11-26 23:04 UTC (permalink / raw)
To: aarni@kymi.com; +Cc: 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@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)
>
>
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2004-11-26 23:04 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-11-26 14:17 Querying a list field against another list Aarni Ruuhimäki <aarni@kymi.com>
2004-11-26 15:56 ` Bruno Wolff III <bruno@wolff.to>
2004-11-26 23:04 ` m <mail@markreid.org>
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