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

On Fri, Dec 17, 2010 at 1:38 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> "David E. Wheeler" <david@kineticode.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.

Unfortunately, there are likely to be a limited number of such
keywords available.  While I agree it's helpful to have a clear
distinction between what FOR does and what FOREACH does, it's wholly
conventional here and won't be obvious without careful reading of the
documentation.  If we had FOR and FOREACH and FOREVERY and, uh,
FORGET, it'd quickly become notational soup.  I am still wondering if
there's a way to make something like "FOR ELEMENT e IN a" work.  I
suspect we'd be less likely to paint ourselves into a corner that way.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company