named parameters in SQL functions

Andrew Dunstan wrote:

At Tom's suggestion I am looking at allowing use of parameter names in
SQL functions instead of requiring use of $1 etc. That raises the
question of how we would disambiguate a parameter name from a column
name. Essentially, ISTM, we could use some special marker such as @
(c.f. SQL Server) or : (c.f. ecpg) or else we could have some rule that
says which name takes precedence. I think I prefer a special marker,
other things being equal. Is there a standard on this?

I like the special marker idea. A '$' would be nice because its already in use
for similar purposes, but I think that would lead to ambiguity with dollar quoting.

Would this be limited to sql functions? I only ask because for non-sql
functions we currently prefix parameter names with an underscore, but a built-in
special marker would be much more desirable.

--
Andrew Chernow
eSilo, LLC
every bit counts
http://www.esilo.com/

2009/11/15 Andrew Dunstan <andrew@dunslane.net>:

At Tom's suggestion I am looking at allowing use of parameter names in SQL
functions instead of requiring use of $1 etc. That raises the question of
how we would disambiguate a parameter name from a column name. Essentially,
ISTM, we could use some special marker such as @ (c.f. SQL Server) or :
(c.f. ecpg) or else we could have some rule that says which name takes
precedence. I think I prefer a special marker, other things being equal. Is
there a standard on this?

what about $name ?

Personally I prefer :name, but this colidates with psql local variables :(

Pavel

2009/11/15 Andrew Chernow <ac@esilo.com>:

Andrew Dunstan wrote:

At Tom's suggestion I am looking at allowing use of parameter names in SQL
functions instead of requiring use of $1 etc. That raises the question of
how we would disambiguate a parameter name from a column name. Essentially,
ISTM, we could use some special marker such as @ (c.f. SQL Server) or :
(c.f. ecpg) or else we could have some rule that says which name takes
precedence. I think I prefer a special marker, other things being equal. Is
there a standard on this?

I like the special marker idea.  A '$' would be nice because its already in
use for similar purposes, but I think that would lead to ambiguity with
dollar quoting.

no, it should be safe (if you don't use for dollar quoting some like
$variablename$)

Pavel

On Sun, Nov 15, 2009 at 12:37 PM, Andrew Dunstan <andrew@dunslane.net> wrote:

At Tom's suggestion I am looking at allowing use of parameter names in SQL
functions instead of requiring use of $1 etc. That raises the question of
how we would disambiguate a parameter name from a column name. Essentially,
ISTM, we could use some special marker such as @ (c.f. SQL Server) or :
(c.f. ecpg) or else we could have some rule that says which name takes
precedence. I think I prefer a special marker, other things being equal. Is
there a standard on this?

We could also just throw an error if there is any ambiguity. I kind
of like the idea of a special marker for both SQL and PL/pgsql, but
Tom has been negative on that idea in the past.

...Robert

On Sun, Nov 15, 2009 at 5:49 PM, Andrew Chernow <ac@esilo.com> wrote:

Andrew Dunstan wrote:

At Tom's suggestion I am looking at allowing use of parameter names in SQL
functions instead of requiring use of $1 etc. That raises the question of
how we would disambiguate a parameter name from a column name. Essentially,
ISTM, we could use some special marker such as @ (c.f. SQL Server) or :
(c.f. ecpg) or else we could have some rule that says which name takes
precedence. I think I prefer a special marker, other things being equal. Is
there a standard on this?

I like the special marker idea.  A '$' would be nice because its already in
use for similar purposes, but I think that would lead to ambiguity with
dollar quoting.

I think that would be a big break with everything else and very
non-sql-ish. We don't use these in plpgsql and we don't use them
anywhere else in sql.

Moreover you would still have conflicts possible because sql can quote
identifiers so people can have columns named "$foo". You would have a
weird syntactic detail where "$foo" would mean something different
than $foo even though they're both valid identifiers.

I'm not sure it wouldn't conflict with some drivers either. DBI uses
:foo and ? but I have a vague recollection some drivers did use $foo.

--
greg

On Nov 15, 2009, at 10:19 AM, Greg Stark wrote:

I like the special marker idea. A '$' would be nice because its already in
use for similar purposes, but I think that would lead to ambiguity with
dollar quoting.

I think that would be a big break with everything else and very
non-sql-ish. We don't use these in plpgsql and we don't use them
anywhere else in sql.

*ahem* $1 *ahem*

Moreover you would still have conflicts possible because sql can quote
identifiers so people can have columns named "$foo". You would have a
weird syntactic detail where "$foo" would mean something different
than $foo even though they're both valid identifiers.

Same with Foo and "Foo", no?

I'm not sure it wouldn't conflict with some drivers either. DBI uses
:foo and ? but I have a vague recollection some drivers did use $foo.

I don't think that would come up, because the $vars are in the body of the function, not in a typical driver call.

Personally, I like $var, but @var would be okay, and @@var is acceptable. But I'm JAPH, so my biases should be obvious.

Best,

David

2009/11/15 David E. Wheeler <david@kineticode.com>:

On Nov 15, 2009, at 10:19 AM, Greg Stark wrote:

I like the special marker idea.  A '$' would be nice because its already in
use for similar purposes, but I think that would lead to ambiguity with
dollar quoting.

I think that would be a big break with everything else and very
non-sql-ish. We don't use these in plpgsql and we don't use them
anywhere else in sql.

*ahem* $1 *ahem*

Moreover you would still have conflicts possible because sql can quote
identifiers so people can have columns named "$foo". You would have a
weird syntactic detail where "$foo" would mean something different
than $foo even though they're both valid identifiers.

Same with Foo and "Foo", no?

I'm not sure it wouldn't conflict with some drivers either. DBI uses
:foo and ? but I have a vague recollection some drivers did use $foo.

I don't think that would come up, because the $vars are in the body of the function, not in a typical driver call.

Personally, I like $var, but @var would be okay, and @@var is acceptable. But I'm JAPH, so my biases should be obvious.

@var or @@var should be a break for people from MySQL. @var are r/w in
MySQL and @@var are global in T-SQL. So people could be confused.

Regards
Pavel

On Sun, Nov 15, 2009 at 6:42 PM, Pavel Stehule <pavel.stehule@gmail.com> wrote:

Personally, I like $var, but @var would be okay, and @@var is acceptable. But I'm JAPH, so my biases should be obvious.

@var or @@var should be a break for people from MySQL. @var are r/w in
MySQL and @@var are global in T-SQL. So people could be confused.

Besides, do we think MySQL and T-SQL are the heights of good language design?

--
greg

On Sun, Nov 15, 2009 at 6:26 PM, David E. Wheeler <david@kineticode.com> wrote:

Personally, I like $var, but @var would be okay, and @@var is acceptable. But I'm JAPH, so my biases should be obvious.

I'm japh too -- but that doesn't mean grabbing one little aesthetic
from Perl without copying the whole concept behind it makes any sense.
Perl sigils are an important part of the language and are a basic part
of the syntax. They aren't just a "this is a variable" marker.
Dropping one use of them into a language that doesn't use them
anywhere else just makes the language into a mishmash.

I don't see any purpose to using such markers anyways. We have a
parser, we have a symbol table, we should use them; these identifiers
are just like other identifiers.

--
greg

2009/11/16 Andrew Dunstan <andrew@dunslane.net>:

At Tom's suggestion I am looking at allowing use of parameter names in SQL
functions instead of requiring use of $1 etc. That raises the question of
how we would disambiguate a parameter name from a column name. Essentially,
ISTM, we could use some special marker such as @ (c.f. SQL Server) or :
(c.f. ecpg) or else we could have some rule that says which name takes
precedence. I think I prefer a special marker, other things being equal. Is
there a standard on this?

Sorry if I'm missing something important here, but why not just
resolve the parameter names in whatever way PL/PgSQL has been doing
it? It seems to work well.

FWIW I always prefix my parameter names with _ to differentiate them
from columns.

Cheers,
BJ

2009/11/15 Greg Stark <gsstark@mit.edu>:

On Sun, Nov 15, 2009 at 6:42 PM, Pavel Stehule <pavel.stehule@gmail.com> wrote:

Personally, I like $var, but @var would be okay, and @@var is acceptable. But I'm JAPH, so my biases should be obvious.

@var or @@var should be a break for people from MySQL. @var are r/w in
MySQL and @@var are global in T-SQL. So people could be confused.

Besides, do we think MySQL and T-SQL are the heights of good language design?

sure no. But same arguments against to :var should be used to @var.
pgscript use it. I don't know the best semantic. But I am not happy
from this proposals. I don't see any consistency.

Pavel

On Nov 15, 2009, at 10:54 AM, Greg Stark wrote:

I'm japh too -- but that doesn't mean grabbing one little aesthetic
from Perl without copying the whole concept behind it makes any sense.
Perl sigils are an important part of the language and are a basic part
of the syntax. They aren't just a "this is a variable" marker.
Dropping one use of them into a language that doesn't use them
anywhere else just makes the language into a mishmash.

Well, no, just because we're talking about adopting $var doesn't mean we're trying to turn SQL or PL/pgSQL into Perl. It means that we want to signify that a token is a variable, as opposed to something else (hence “sigil”). That doesn't make it a mishmash unless you think you suddenly have Perl (or shell) semantics, which would be a pretty weird expectation.

I don't see any purpose to using such markers anyways. We have a
parser, we have a symbol table, we should use them; these identifiers
are just like other identifiers.

See the discussion of conflicts with column names in the recent thread. A sigil would eliminate that problem -- and we already have $1 and friends, so this is just an extension of that in my view.

Best,

David

I like the special marker idea. A '$' would be nice because its already in
use for similar purposes, but I think that would lead to ambiguity with
dollar quoting.

no, it should be safe (if you don't use for dollar quoting some like
$variablename$)

Actually, I was thinking of something like $abc$def, where abc and def are
variables. Although, this is much less likely than column name conflicts.

Other possibles are: $(var), @var@, or %var%. I'd perfer a single character
marker but that may not fly.

--
Andrew Chernow
eSilo, LLC
every bit counts
http://www.esilo.com/

2009/11/15 Andrew Chernow <ac@esilo.com>:

I like the special marker idea.  A '$' would be nice because its already
in
use for similar purposes, but I think that would lead to ambiguity with
dollar quoting.

no, it should be safe (if you don't use for dollar quoting some like
$variablename$)

Actually, I was thinking of something like $abc$def, where abc and def are
variables.  Although, this is much less likely than column name conflicts.

Other possibles are: $(var), @var@, or %var%.  I'd perfer a single character
marker but that may not fly.

single character is my preference too.

Pavel

On Sun, Nov 15, 2009 at 6:26 PM, David E. Wheeler <david@kineticode.com> wrote:

Moreover you would still have conflicts possible because sql can quote
identifiers so people can have columns named "$foo". You would have a
weird syntactic detail where "$foo" would mean something different
than $foo even though they're both valid identifiers.

Same with Foo and "Foo", no?

No, that's not the same.

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.
You're introducing a meaning for $foo but saying there's no valid way
to quote the identifier to get the same thing. And worse, if you do
quote it you get something else entirely different.

--
greg

On Nov 15, 2009, at 11:21 AM, Greg Stark wrote:

No, that's not the same.

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.
You're introducing a meaning for $foo but saying there's no valid way
to quote the identifier to get the same thing. And worse, if you do
quote it you get something else entirely different.

$foo should be killed off as a valid identifier, IMNSHO.

But failing that, some other sigil would be most welcome.

Best,

David

On Sun, Nov 15, 2009 at 7:25 PM, David E. Wheeler <david@kineticode.com> wrote:

On Nov 15, 2009, at 11:21 AM, Greg Stark wrote:

$foo should be killed off as a valid identifier, IMNSHO.

But failing that, some other sigil would be most welcome.

I don't think SQL is the height of language design either. But trying
to turn it into another language piece by piece is not gong to make it
any nicer.

A sigil here doesn't accomplish anything. The identifiers in question
are *just* like other identifiers. They can be used in expressions
just like other columns, they have various types, they have the same
syntax as other columns, the sigil doesn't mean anything.

I think what may be making this tempting is that they look vaguely
like ODBC/JDBC/DBI placeholders like :foo. However they're very very
different. In those cases the sigil is marking the sigil outside the
SQL syntax. They will be replaced textually without parsing the SQL at
all. It's actually very confusing having $foo indicate something
within SQL since it makes it look like it's some external thing from
another layer like the placeholders.

--
greg

David E. Wheeler wrote:

$foo should be killed off as a valid identifier, IMNSHO.

It's only legal when quoted. Unquoted indetifiers can't begin with $.
see scan.l:

ident_start [A-Za-z\200-\377_]
ident_cont [A-Za-z\200-\377_0-9\$]
identifier {ident_start}{ident_cont}*

cheers

andrew

On Nov 15, 2009, at 11:35 AM, Greg Stark wrote:

I don't think SQL is the height of language design either. But trying
to turn it into another language piece by piece is not gong to make it
any nicer.

I don't know of anyone suggesting such a thing.

A sigil here doesn't accomplish anything. The identifiers in question
are *just* like other identifiers. They can be used in expressions
just like other columns, they have various types, they have the same
syntax as other columns, the sigil doesn't mean anything.

So what is the $ for in $1, $2, etc.?

I think what may be making this tempting is that they look vaguely
like ODBC/JDBC/DBI placeholders like :foo. However they're very very
different. In those cases the sigil is marking the sigil outside the
SQL syntax. They will be replaced textually without parsing the SQL at
all. It's actually very confusing having $foo indicate something
within SQL since it makes it look like it's some external thing from
another layer like the placeholders.

It's not in SQL; it's in SQL functions (and DO blocks). AFAIK, the major database vendors all use some sort of character to identify variables within functions. It's proven, avoids conflicts (you can't have an identifier named $foo, as Andrew just pointed out), and just generally makes maintenance easier.

Best,

David