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