Обсуждение: Regexp matching + typecasts
I need some help with a query. I've the following tables structure:
# select triggerid,expression,status from triggers
19298 | {23033}=0 | 0
17041 | {20211}#0 | {20210} | 1
18875 | {22975}<8 & {22976}>50 | 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
# 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');
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
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.
Ilya.
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
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:Well, yeah. You need to convert the possibly-multiple match results into
> 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
a set. Try putting unnest() around the regexp_matches call.
regards, tom lane
--
Ilya.