Skip site navigation (1) Skip section navigation (2)

Re: Fail to search in array, produced by subquery - is it a bug?

From: Merlin Moncure <mmoncure(at)gmail(dot)com>
To: Dmitry Fefelov <fozzy(at)ac-sw(dot)com>
Cc: pgsql-hackers(at)postgresql(dot)org
Subject: Re: Fail to search in array, produced by subquery - is it a bug?
Date: 2011-04-27 13:32:48
Message-ID: BANLkTin0ntd9uRH+1bno-9_OGm0ap8Weyw@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-hackers
On Tue, Apr 26, 2011 at 10:29 PM, Dmitry Fefelov <fozzy(at)ac-sw(dot)com> wrote:
> With Postgres 8.4 query like
>
> SELECT *
>  FROM core.tag_links ctl
>  WHERE (ctl.tag_id = ANY (
>      SELECT array_agg(ct.id)
>        FROM core.tags ct
>        WHERE (LOWER(ct.tag) LIKE LOWER(('search tag')::text || '%') ESCAPE
>            E'\\')
>    ));

well, if you *had* to use any you could rewrite that as:
SELECT *
 FROM core.tag_links ctl
 WHERE (ctl.tag_id = ANY ( array (
     SELECT ct.id
       FROM core.tags ct
       WHERE (LOWER(ct.tag) LIKE LOWER(('search tag')::text || '%') ESCAPE
           E'\\'))
   ));

but you're far better off still using 'where in/'where exists' for
this query.   You could also expand an array with 'unnest' and use
'where in'.

according to the documentation, 'any' only takes array
expressions...this feels really awkward but i'm not sure if it's a
bug.  the semantics of 'any' suck and I prefer not to use it :(.
this has come up a bunch of times in the archives (unfortunately,
searching for 'any' isn't pleasant) and I think there's an explanation
behind the current behavior.  Couldn't find it though, so I'm not
sure.

merlin

In response to

Responses

pgsql-hackers by date

Next:From: Merlin MoncureDate: 2011-04-27 13:58:19
Subject: Re: "stored procedures" - use cases?
Previous:From: Peter EisentrautDate: 2011-04-27 13:00:14
Subject: Re: fixing INT64_FORMAT warnings on Mingw

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group