Re: proposal: FOREACH-IN-ARRAY (probably for 9.2?)

"David E. Wheeler" <david(at)kineticode(dot)com> writes:
> On Dec 17, 2010, at 9:31 AM, Tom Lane wrote:
>> Well, we did beat up Pavel over trying to shoehorn this facility into
>> the existing FOR syntax, so I can hardly blame him for thinking this
>> way. The question is whether we're willing to assume that FOREACH will
>> be limited to iterating over arrays, meaning we'll be stuck with
>> inventing yet another initial keyword if some other fundamentally
>> different concept comes along. Right at the moment I can't think of
>> any plausible candidates, but ...

> FOREACH pair IN HSTORE

I don't actually see any problem with allowing that (or any other
"collection" kind of object) with the same syntax as for arrays.

The issue that we had with adding this to FOR was that it wasn't clear
whether the expression after IN should be thought of as a source of
rows, or as a "scalar" expression yielding a collection object that
should get iterated through --- and because SQL allows sub-SELECT as a
kind of expression, this was an actual formal ambiguity not just the
sort of near-ambiguity that trips up users. If you will, it wouldn't
have been clear whether to iterate vertically or horizontally.

The direction that this proposal establishes is that FOR is for vertical
iteration and FOREACH is for horizontal iteration; that is, the argument
of FOREACH is a scalar expression in SQL terms, but it yields some kind
of collection object that we are going to iterate through the members
of. Given that understanding, I'm not seeing a need for the syntax to
distinguish whether the collection object is an array, an hstore, or
some other kind of collection. It's sufficient if we can determine this
by examining the type of the expression.

We would need an extra keyword if there were some third kind of
iteration that was fundamentally different from either of these, but
like I said, I don't see a plausible candidate. So right at the moment,
I'm leaning to the position that we could do without the ARRAY keyword
in FOREACH. If we do think of something else that could need its own
keyword there, it's arguably going to be different enough that a
different leading keyword would be a better idea anyhow.

regards, tom lane