DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references;
	b=YTFBr50MGd7cpWbFEqdXLOjDirFpYlpj3TUUFlltsN9MYX+KYetABUt57hf6ccwFLXJawRFLSDM30JtUiL1k4JgPcB+NReTE02ejNO2i6nk/PckJzPulObSqX9z5huHtNOCAkTIGUnFc0spaeVsMRrWZVdilxgwAqT+Bcr5brxc=
Message-ID: <758d5e7f0802271634q7d8bbdc0t29cb94e932526f22@mail.gmail.com>
Date: Thu, 28 Feb 2008 01:34:42 +0100
From: "Dawid Kuroczko" <qnex42@gmail.com>
To: josh@agliodbs.com
Subject: Re: Subselects returning array and ANY...
Cc: pgsql-sql@postgresql.org
In-Reply-To: <200802271611.50928.josh@agliodbs.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
References: <758d5e7f0802271601m592a932bh5916b10eccecbb4a@mail.gmail.com>
	<200802271611.50928.josh@agliodbs.com>

On Thu, Feb 28, 2008 at 1:11 AM, Josh Berkus <josh@agliodbs.com> wrote:
> Dawid,
>  >    SELECT * FROM foo WHERE t = ANY (SELECT '{x4,5,zzz}'::text[]);
>  > ERROR:  operator does not exist: text = text[]
>  > HINT:  No operator matches the given name and argument type(s). You
>  > might need to add explicit type casts.
>
>  Drop the second SELECT, I think.
>
>  postgres=# select 'x' = ANY ( '{x,y,z}'::TEXT[] );
>   ?column?
>  ----------
>   t

Probably I didn't make myself clear enough. :)  I know this works (see
above two examples).
I need to connect two tables, one is:

CREATE TEMP TABLE haystack (straw text);
INSERT INTO haystack SELECT n FROM generate_series(1,100) AS x(n);

CREATE TEMP TABLE needles (id int, straws text[]);
INSERT INTO needles VALUES (1, '{10,15,aaa}');

SELECT * FROM haystack WHERE straw = ANY (SELECT straws FROM needles
WHERE id=1);
ERROR:  operator does not exist: text = text[]
HINT:  No operator matches the given name and argument type(s). You
might need to add explicit type casts.

But you are right, though!  I just need to leave off the subselect!  Thanks!
SELECT h.* FROM haystack h JOIN needles ON straw = ANY (straws);

(not exactly what I was looking for, but it works ;))