Regexp matching + typecasts

From: Ilya Ivanov <forn(at)ngs(dot)ru>
To: pgsql-general(at)postgresql(dot)org
Subject: Regexp matching + typecasts
Date: 2014-03-22 16:37:47
Message-ID: CAA=KoeJMfwpn970tZ8--0Jndc2vYNOkRvqBYZi8Pe3wPyxXqtQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message James Harper 2014-03-22 23:17:47 spi in bgworker
Previous Message Martijn van Oosterhout 2014-03-22 09:30:08 Re: 9.3 debian install setup failure