Re: Why does this array query fail?

>
> Can we please follow list norms (or at least my example since I was the
> first to respond) and bottom-post.

Absolutely. Gmail did it without my realizing, but my bad and I'm all for
following the list conventions.

Thanks for taking the time to explain this stuff, which I appreciate.
Mostly it makes sense, but a couple of things remain puzzling to me.

1) On what exactly does PG base its decision to interpret the ANY as
scalar or not? Or are you saying a sub-query will always be treated as
non-scalar, unless it is explicitly cast to an array?

2) Regarding:

> In this context PostgreSQL goes looking for an operator -
> e.g., equals(=) - with the right-side argument being of the type of the
> sub-query column.

Why? In this case you have ANY (varchar[]), which as I understand it "="
needs to compare with a varchar. So why is it looking for an array? If it
was just varchar = varchar[], I'd get that, but in this case it's
ANY(varchar[]), so does PG extract the type of the argument to ANY without
paying attention to the ANY itself?

There is quite a bit more to this that can be gleaned by reading the
> documentation for sub-queries.

I'm not sure if there's something specific you're referring to. I had
looked at the page on subquery expressions (
http://www.postgresql.org/docs/9.0/static/functions-subquery.html), as well
as the following page on "row and array comparisons" to see the two forms
of ANY, but don't see anything that covers these nuances in greater depth.
Is there another page I should be looking at?

Note that "(SELECT ARRAY[...])::text[]" is only a proper solution if...

Yeah, I tried to boil down my example, but this is closer to what I was
really trying to do:

CREATE TEMP TABLE mytable ( codes varchar[] );
INSERT INTO mytable VALUES ( array[ 'pass','fail'] );
INSERT INTO mytable VALUES ( array[ 'found'] );
SELECT 'found' WHERE 'found' =ANY(
(SELECT array_agg(code) FROM (SELECT unnest(codes) AS code FROM
mytable) foo
)
);

And for immediate purposes, found this worked just as well (as a non-scalar
subquery, I guess):

SELECT 'found' WHERE 'found' =ANY(
(SELECT unnest(codes) AS code FROM mytable)
);

Thanks again for your help and explanations!

Ken