From: | Ilya Ivanov <forn(at)ngs(dot)ru> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Regexp matching + typecasts |
Date: | 2014-04-06 08:29:33 |
Message-ID: | CAA=KoeJynz8Yr0izWMr74XbznuqQ2wPV+8R9ZT0kgWy6H2Z3Uw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Thanks Tom, that worked!
On Mon, Mar 24, 2014 at 8:47 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Ilya Ivanov <forn(at)ngs(dot)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.
From | Date | Subject | |
---|---|---|---|
Next Message | Alban Hertroys | 2014-04-06 09:49:22 | Re: Any way to insert rows with ID used in another column |
Previous Message | bricklen | 2014-04-06 02:02:09 | Re: Log file monitoring and event notification |