Re: named parameters in SQL functions

On Sun, Nov 15, 2009 at 7:56 PM, Andrew Chernow <ac(at)esilo(dot)com> wrote:
>> The point is that $ is a perfectly valid SQL identifier character and
>> $foo is a perfectly valid identifier. You can always quote any
>> identifier (yes, after case smashing) so you would expect if $foo is a
>> valid identifier then "$foo" would refer to the same identifier.
>>
>
> This case already exists via $1 and "$1".  Making '$' a marker for
> parameters wouldn't introduce it.

True, $1 etc were already very non-sqlish, but that doesn't mean we
have to compound things.

So here are some examples where you can see what having this wart
would introduce:

1) Error messages which mention column names are supposed to quote the
column name to set it apart from the error string. This also
guarantees that weird column names are referenced correctly as "foo
bar" or "$foo" so the reference in the error string is unambiguous and
can be pasted into queries. This won't work for $foo which would have
to be embedded in the error text without quotes.

2) What would the default names for columns be if you did something like

create function f(foo) as 'select $foo'

If I then use this in another function

create function g(foo) as 'select "$foo"+$foo from f()'

I have to quote the column? The point here is that these sigils will
leak out, they don't mean much to begin with except to indicate that
this identifier is immune to the regular scoping rules but things get
more confusing when they leak out and they start appearing in places
that are subject to the regular scoping rules.

3) If I have a report generator which takes a list of columns to
include in the report, or an ORM which tries to generate queries the
usual way to write such things is to just routinely quote every
identifier. This is less error-prone and simpler to code than trying
to identify which identifiers need quoting and which don't. However in
if the query is then dropped into a function the ORM or query
generator would have to know which columns cannot be quoted based on
syntactic information it can't really deduce.

--
greg