Postgres Pro
Downloads
Home   >   mailing lists

Thread: Regexp matching + typecasts

Regexp matching + typecasts

From
Ilya Ivanov
Date:
I need some help with a query. I've the following tables structure:

# select triggerid,expression,status from triggers
triggerid | expression                         | status
19298    | {23033}=0                          |      0
17041    | {20211}#0 | {20210}           |      1
18875    | {22975}<8 & {22976}>50   |      0

Numbers in "{}" are itemid's:

# select itemid,status from items;
 itemid | status
 24324 |      1
 34842 |      0
 36078 |      1

I need to find all active (status=0) items not belonging to any active trigger. The best I could come up with is this:

select count(itemid) from items where status='0' and itemid not in (select cast(regexp_matches(expression,'{([^}]+)}','g') as integer) from triggers where status='0');

However, the cast doesn't work:
ERROR:  cannot cast type text[] to integer
Or maybe I'm not using it right.
Anyone could provide an insight?

This is on postgresql 9.2.

--
Ilya.

Re: Regexp matching + typecasts

From
Tom Lane
Date:
Ilya Ivanov <forn@ngs.ru> writes:
> I need to find all active (status=0) items not belonging to any active
> trigger. The best I could come up with is this:

> select count(itemid) from items where status='0' and itemid not in (select
> cast(regexp_matches(expression,'{([^}]+)}','g') as integer) from triggers
> where status='0');

> However, the cast doesn't work:
> ERROR:  cannot cast type text[] to integer

Well, yeah.  You need to convert the possibly-multiple match results into
a set.  Try putting unnest() around the regexp_matches call.

            regards, tom lane

Re: Regexp matching + typecasts

From
Ilya Ivanov
Date:
Thanks Tom, that worked!


On Mon, Mar 24, 2014 at 8:47 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Ilya Ivanov <forn@ngs.ru> writes:
> I need to find all active (status=0) items not belonging to any active
> trigger. The best I could come up with is this:

> select count(itemid) from items where status='0' and itemid not in (select
> cast(regexp_matches(expression,'{([^}]+)}','g') as integer) from triggers
> where status='0');

> However, the cast doesn't work:
> ERROR:  cannot cast type text[] to integer

Well, yeah.  You need to convert the possibly-multiple match results into
a set.  Try putting unnest() around the regexp_matches call.

                        regards, tom lane



--
Ilya.