Re: Regexp matching + typecasts

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