Re: resolving SQL ambiguity (was Re: WIP: Allow SQL-lang funcs to ref params by param name)

Pavel Stehule wrote:
> 2011/3/26 Darren Duncan <darren(at)darrenduncan(dot)net>:
>> I mention 2 possible solutions here, both which involve syntax alterations,
>> each between the ---------- lines. I personally like the second/lower
>> option more.
>
> -1
>
> this is not based on any pattern on SQL. It's not simple, and it
> introduce a reserved keywords

Okay, here's a much simpler proposal with the most important bit of the old one.

1. In all situations where there is ambiguity such that an identifier reference
(not declaration) may be referring to either a lexical variable/parameter of the
current routine, or to the name of the table column of the contextually current
table of the current SQL statement, the ambiguity is always resolved in favor of
the lexical var/param. If I am not mistaken, that is what PL/PgSQL already does
since 9.0.

2. If an identifier reference has a leading "." then that will force it to be
interpreted as a column instead (and the code will fail if there is no such
column), and so ".colname" is a shorthand for "tablename.colname"; but like with
the old "colname" it only works when just 1 of the source tables has "colname"
else it is still ambiguous like before.

Example:

select (.mycol + myvar * myparam) as mynewcol from mytbl;

This solution is a very terse and understandable change.

There are no reserved keywords. Legacy user code has no change where there were
no conflicts before. Legacy user code has no change in the case of conflict if
it was previously resolved to favor the lexical var/param.

Legacy user code only gains a leading "." in the few places where conflict was
resolved in favor of a column name before where a same-named lexical/param existed.

So what's not to like about this?

-- Darren Duncan