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

2011/3/26 Darren Duncan <darren(at)darrenduncan(dot)net>:
> 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?

sorry - I dislike this. The design is correct, but it is against to
SQL verbosity. A reader must to thinking about missing tablenames. I
dont't think so it is good solution, because it doesn't solve a
backing compatibility problem - somebody must to fix a function still,
and I think so it is much preferable to fix like:

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

your proposal saves a five chars, but it has a negative impacts on
readability - there should be more tables.

There are no reason to introduce a new concepts - SQL knows a aliases.

Regards

Pavel

>
> -- Darren Duncan
>