Re: WIP: Allow SQL-language functions to reference parameters by parameter name

On Tue, Apr 5, 2011 at 2:33 PM, Peter Eisentraut <peter_e(at)gmx(dot)net> wrote:
> On tis, 2011-04-05 at 15:05 -0400, Robert Haas wrote:
>> On Tue, Apr 5, 2011 at 1:45 PM, Peter Eisentraut <peter_e(at)gmx(dot)net> wrote:
>> > On tis, 2011-04-05 at 11:21 -0500, Merlin Moncure wrote:
>> >> +1 on using $foo.  Even with the standardization risk I think it's the
>> >> best choice. Prefer $"foo" to ${foo} though.
>> >
>> > What standardization risk?  The standard has already existed for >10
>> > years and is widely implemented.
>>
>> What is the standard, and who is it that has implemented it that way?
>
> As mentioned earlier, see under clause on <identifier chain>.  The
> summary is that in
>
>    CREATE FUNCTION foo(a int)
>
> you can refer to the parameter as either of
>
>    a
>    foo.a
>
> with some scoping rules to resolve ambiguities with column references.
> (These are essentially the same scoping rules that tell you what "a"
> refers to when you have multiple tables with an "a" column in a query.)
>
> As far as I can tell, the syntax is implemented, more or less, at least
> in Oracle, DB2, MySQL, Firebird, and HSQL.  I haven't checked what they
> do with the scoping rules, of course.

Talking about the standards compliance of functions is a bit silly:
our implementation of functions isn't even close to approximating what
looks to be the standard (according to this at least:
http://farrago.sourceforge.net/design/UserDefinedTypesAndRoutines.html)
and there is no point pretending that it is. In practice, database
functions and procedures are 100% vendor incompatible with each other,
and with the standard. I was just talking about $ getting reserved
for some special meaning in the future.

mysql supports psm, which we don't. oracle supports pl/sql, which is
similar to pl/pgsql, but means nothing in terms of postgresql sql
language argument disambiguation afaict. It's our language and we
should be able to extend it.

merlin