Re: named parameters in SQL functions

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?

cheers

andrew

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(at)dunslane(dot)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

>
> cheers
>
> andrew
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>

2009/11/15 Andrew Chernow <ac(at)esilo(dot)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

>
> 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/
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>

On Sun, Nov 15, 2009 at 12:37 PM, Andrew Dunstan <andrew(at)dunslane(dot)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(at)esilo(dot)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(at)kineticode(dot)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

>
> Best,
>
> David
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>

On Sun, Nov 15, 2009 at 6:42 PM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)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(at)kineticode(dot)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(at)dunslane(dot)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(at)mit(dot)edu>:
> On Sun, Nov 15, 2009 at 6:42 PM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)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

>
> --
> greg
>

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(at)esilo(dot)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
> --
> Andrew Chernow
> eSilo, LLC
> every bit counts
> http://www.esilo.com/
>

On Sun, Nov 15, 2009 at 6:26 PM, David E. Wheeler <david(at)kineticode(dot)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(at)kineticode(dot)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

> 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.

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

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

On Nov 15, 2009, at 12:09 PM, Greg Stark wrote:

> 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.

What? You can't have a column named "$foo" without the quotes.

> 2) What would the default names for columns be if you did something like
>
> create function f(foo) as 'select $foo'

It would be "f" (without the quotes), just like now:

try=# create function f(int) RETURNS int as 'SELECT $1' LANGUAGE sql;
CREATE FUNCTION
try=# select f(1);
f
---
1
(1 row)

> If I then use this in another function
>
> create function g(foo) as 'select "$foo"+$foo from f()'
>
> I have to quote the column?

No, that's a syntax error. It would be `SELECT f + $foo from f();`

> 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.

You already have to quote everything, because $foo isn't a valid column name. And functions use the function name as the default column name, not a variable name. The same is true of set-returning functions, BTW:

try=# create function b(int) RETURNS setof int as 'values ($1), ($1)' LANGUAGE sql; CREATE FUNCTION
try=# select b(1);
b
---
1
1
(2 rows)

So there is no leaking out. The variables are scoped within the function.

Best,

David

Andrew Dunstan <andrew(at)dunslane(dot)net> writes:
> 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.

Throw error if ambiguous. We already resolved this in the context of
plpgsql.

regards, tom lane

Tom Lane wrote:
> Andrew Dunstan <andrew(at)dunslane(dot)net> writes:
>
>> 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.
>>
>
> Throw error if ambiguous. We already resolved this in the context of
> plpgsql.
>
>
>

Well, if the funcname.varname gadget will work, as you suggest elsewhere
it could, I think that would suffice. I had assumed that was just
something in the plpgsql engine.

cheers

andrew

On Sun, Nov 15, 2009 at 8:22 PM, Andrew Dunstan <andrew(at)dunslane(dot)net> wrote:
> Tom Lane wrote:
>>
>> Andrew Dunstan <andrew(at)dunslane(dot)net> writes:
>>
>>>
>>> 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.
>>>
>>
>> Throw error if ambiguous.  We already resolved this in the context of
>> plpgsql.
>>
>>
>>
>
> Well, if the funcname.varname gadget will work, as you suggest elsewhere it
> could, I think that would suffice. I had assumed that was just something in
> the plpgsql engine.

That gadget isn't horribly convenient for me since my function names
tend to be 30 or 40 characters long. I wish we had something shorter,
and maybe constant. But I guess that's a topic for a separate
(inevitably rejected) patch.

...Robert

Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> On Sun, Nov 15, 2009 at 8:22 PM, Andrew Dunstan <andrew(at)dunslane(dot)net> wrote:
>> Well, if the funcname.varname gadget will work, as you suggest elsewhere it
>> could, I think that would suffice. I had assumed that was just something in
>> the plpgsql engine.

> That gadget isn't horribly convenient for me since my function names
> tend to be 30 or 40 characters long. I wish we had something shorter,
> and maybe constant. But I guess that's a topic for a separate
> (inevitably rejected) patch.

You're only going to need that if you insist on choosing parameter names
that conflict with columns of the tables the function manipulates. Even
then, attaching column aliases to the tables could be used instead.
I don't see that this is any different from or worse than the extra
typing you'll incur if you insist on using 40-character table names.

(But having said that, an alternate qualification name is something
that could be implemented if there were any agreement on what to use.)

regards, tom lane

Tom Lane wrote:
> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
>> On Sun, Nov 15, 2009 at 8:22 PM, Andrew Dunstan <andrew(at)dunslane(dot)net> wrote:
>>> Well, if the funcname.varname gadget will work, as you suggest elsewhere it
>>> could, I think that would suffice. I had assumed that was just something in
>>> the plpgsql engine.
>
>> That gadget isn't horribly convenient for me since my function names
>> tend to be 30 or 40 characters long. I wish we had something shorter,
>> and maybe constant. But I guess that's a topic for a separate
>> (inevitably rejected) patch.
>
> You're only going to need that if you insist on choosing parameter names
> that conflict with columns of the tables the function manipulates. Even
> then, attaching column aliases to the tables could be used instead.
> I don't see that this is any different from or worse than the extra
> typing you'll incur if you insist on using 40-character table names.
>
> (But having said that, an alternate qualification name is something
> that could be implemented if there were any agreement on what to use.)

Would something like ARG.name be acceptable?

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

Andrew Chernow <ac(at)esilo(dot)com> writes:
> Tom Lane wrote:
>> (But having said that, an alternate qualification name is something
>> that could be implemented if there were any agreement on what to use.)

> Would something like ARG.name be acceptable?

It all depends on how likely you think it is that the function would use
a table name or alias matching ARG (or any other proposal).

It's certainly true that the function name itself is not immune from
conflicts of that sort ... in fact I think we saw a bug report recently
from someone who had intentionally chosen a plpgsql function name equal
to a table name used in the function :-(. So I'm not wedded to the
function name entirely. But it has precedent in plpgsql, and that
precedent came from Oracle, so I don't think we should lightly make SQL
functions do something different.

regards, tom lane

Tom Lane wrote:
> Andrew Chernow <ac(at)esilo(dot)com> writes:
>> Tom Lane wrote:
>>> (But having said that, an alternate qualification name is something
>>> that could be implemented if there were any agreement on what to use.)
>
>> Would something like ARG.name be acceptable?
>
> It all depends on how likely you think it is that the function would use
> a table name or alias matching ARG (or any other proposal).
>
> It's certainly true that the function name itself is not immune from
> conflicts of that sort ... in fact I think we saw a bug report recently
> from someone who had intentionally chosen a plpgsql function name equal
> to a table name used in the function :-(. So I'm not wedded to the
> function name entirely. But it has precedent in plpgsql, and that
> precedent came from Oracle, so I don't think we should lightly make SQL
> functions do something different.
>

If the concern is portability, (ANYTHING).name won't work. You would have to
stick with function.name or support both styles.

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

Andrew Chernow <ac(at)esilo(dot)com> writes:
> Tom Lane wrote:
>> It's certainly true that the function name itself is not immune from
>> conflicts of that sort ... in fact I think we saw a bug report recently
>> from someone who had intentionally chosen a plpgsql function name equal
>> to a table name used in the function :-(. So I'm not wedded to the
>> function name entirely. But it has precedent in plpgsql, and that
>> precedent came from Oracle, so I don't think we should lightly make SQL
>> functions do something different.

> If the concern is portability, (ANYTHING).name won't work. You would have to
> stick with function.name or support both styles.

I find the recent SQL drafts pretty darn opaque, but I think that
SQL:2008 6.6 <identifier chain> syntax rule 8)b)ii)

If N = 2 and PIC1 is equivalent to the <qualified identifier> of
a <routine name> RN whose scope contains IC and whose associated <SQL
parameter declaration list> includes an SQL parameter SP whose <SQL
parameter name> is equivalent to I2, then PIC2 is a candidate basis of
IC, the scope of PIC2 is the scope of SP, and the referent of PIC2 is
SP.

is describing the style "function_name.argument_name". So it's not just
Oracle setting that precedent.

regards, tom lane

On Sun, Nov 15, 2009 at 8:40 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
>> On Sun, Nov 15, 2009 at 8:22 PM, Andrew Dunstan <andrew(at)dunslane(dot)net> wrote:
>>> Well, if the funcname.varname gadget will work, as you suggest elsewhere it
>>> could, I think that would suffice. I had assumed that was just something in
>>> the plpgsql engine.
>
>> That gadget isn't horribly convenient for me since my function names
>> tend to be 30 or 40 characters long.  I wish we had something shorter,
>> and maybe constant.  But I guess that's a topic for a separate
>> (inevitably rejected) patch.
>
> You're only going to need that if you insist on choosing parameter names
> that conflict with columns of the tables the function manipulates.  Even
> then, attaching column aliases to the tables could be used instead.
> I don't see that this is any different from or worse than the extra
> typing you'll incur if you insist on using 40-character table names.

It's true, but that often seems like a natural thing to do. Someone
passes you the ID of a project and you want to look up all the tasks
associated with that project and do some computation on them. Well
the task table has a project_id column, and that's also the obvious
name for the parameter. You can call it pid or my_project_id or
v_project_id or any of the other alternatives that are frequently
suggested, or just leave it unnamed and refer to it as $1, but to my
way of thinking project_id is the most natural choice.

> (But having said that, an alternate qualification name is something
> that could be implemented if there were any agreement on what to use.)

Well that is the tricky part, for sure. I would personally prefer
something like ${name} rather than a prefix, but I think you're likely
to veto that outright. So, anything reasonably short would be an
improvement over the status quo. self? this? my?

...Robert

Robert Haas wrote:
>> (But having said that, an alternate qualification name is something
>> that could be implemented if there were any agreement on what to use.)
>>
>
> Well that is the tricky part, for sure. I would personally prefer
> something like ${name} rather than a prefix, but I think you're likely
> to veto that outright. So, anything reasonably short would be an
> improvement over the status quo. self? this? my?
>
>
>
I think it would have to be a reserved word. The obvious existing
keyword to use is "function" but unless I'm mistaken we'd need to move
it from unreserved keyword to reserved, and I'm not sure this would
justify that.

cheers

andrew

On Sun, Nov 15, 2009 at 9:52 PM, Andrew Dunstan <andrew(at)dunslane(dot)net> wrote:
> Robert Haas wrote:
>>>
>>> (But having said that, an alternate qualification name is something
>>> that could be implemented if there were any agreement on what to use.)
>>>
>>
>> Well that is the tricky part, for sure.  I would personally prefer
>> something like ${name} rather than a prefix, but I think you're likely
>> to veto that outright.  So, anything reasonably short would be an
>> improvement over the status quo.  self?  this?  my?
>
> I think it would have to be a reserved word. The obvious existing keyword to
> use is "function" but unless I'm mistaken we'd need to move it from
> unreserved keyword to reserved, and I'm not sure this would justify that.

I don't see why it would need to be a reserved word. We're not
changing how it gets parsed, just what it means. At any rate
"FUNCTION." is a 9-character prefix, which is rather longer than I
would prefer. PL/pgsql is a tiresomely long-winded language in
general, IMHO, although some of Tom's changes for 8.5 will help with
that.

...Robert

Andrew Dunstan <andrew(at)dunslane(dot)net> writes:
> Robert Haas wrote:
>> Well that is the tricky part, for sure. I would personally prefer
>> something like ${name} rather than a prefix, but I think you're likely
>> to veto that outright. So, anything reasonably short would be an
>> improvement over the status quo. self? this? my?
>>
> I think it would have to be a reserved word. The obvious existing
> keyword to use is "function" but unless I'm mistaken we'd need to move
> it from unreserved keyword to reserved, and I'm not sure this would
> justify that.

All of these are fundamentally illegal syntax, which as was already
pointed out up-thread is likely to introduce nasty side-effects of its
own. Not to mention being an order of magnitude harder to implement.
We have a hook to implement resolving a ColumnRef, ie a qualified
identifier. We don't have support for random other things.

regards, tom lane

Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> I don't see why it would need to be a reserved word. We're not
> changing how it gets parsed, just what it means. At any rate
> "FUNCTION." is a 9-character prefix, which is rather longer than I
> would prefer.

This from the guy who likes 40-character function names?

regards, tom lane

Robert Haas wrote:
> On Sun, Nov 15, 2009 at 9:52 PM, Andrew Dunstan <andrew(at)dunslane(dot)net> wrote:
>
>> Robert Haas wrote:
>>
>>>> (But having said that, an alternate qualification name is something
>>>> that could be implemented if there were any agreement on what to use.)
>>>>
>>>>
>>> Well that is the tricky part, for sure. I would personally prefer
>>> something like ${name} rather than a prefix, but I think you're likely
>>> to veto that outright. So, anything reasonably short would be an
>>> improvement over the status quo. self? this? my?
>>>
>> I think it would have to be a reserved word. The obvious existing keyword to
>> use is "function" but unless I'm mistaken we'd need to move it from
>> unreserved keyword to reserved, and I'm not sure this would justify that.
>>
>
> I don't see why it would need to be a reserved word. We're not
> changing how it gets parsed, just what it means. At any rate
> "FUNCTION." is a 9-character prefix, which is rather longer than I
> would prefer. PL/pgsql is a tiresomely long-winded language in
> general, IMHO, although some of Tom's changes for 8.5 will help with
> that.
>
>
>

Umm, what has this to do with plpgsql? We're talking about what to use
in pure SQL functions.

If you find plpgsql tiresome, use something else. There are plenty of
alternatives.

I think the debate is likely to be pointless in any case - it seems
clear that there are objections to anything other than
funcname.paramname as a disambiguating mechanism, so let's just go with
that. It will still be a considerable advance.

cheers

andrew

On Sun, Nov 15, 2009 at 10:05 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
>> I don't see why it would need to be a reserved word.  We're not
>> changing how it gets parsed, just what it means.  At any rate
>> "FUNCTION." is a 9-character prefix, which is rather longer than I
>> would prefer.
>
> This from the guy who likes 40-character function names?

Hrm... I think this is bikeshedding at its finest - but having said
that, thinking about what I usually do a little more, my PL/pgsql
functions tend to be either triggers, which tend to have names like
tablename_postupdate() [so around 20 characters, depending on the
length of the table name] or else they tend to be functions that
update some sort of summary statistics... like, oh, say, updating the
task table with the total amount of time worked on each task by
aggregating over a time log table. Those functions tend to get a name
like update_task_time_worked(). That's only 23, but task is a pretty
short word, so some of them might be a bit longer than that. So maybe
40 is an overestimate, although I probably do have a few that are
close to that long.

...Robert

On Sun, Nov 15, 2009 at 10:14 PM, Andrew Dunstan <andrew(at)dunslane(dot)net> wrote:
> Robert Haas wrote:
>> On Sun, Nov 15, 2009 at 9:52 PM, Andrew Dunstan <andrew(at)dunslane(dot)net>
>> wrote:
>>> Robert Haas wrote:
>>>>> (But having said that, an alternate qualification name is something
>>>>> that could be implemented if there were any agreement on what to use.)
>>>>
>>>> Well that is the tricky part, for sure.  I would personally prefer
>>>> something like ${name} rather than a prefix, but I think you're likely
>>>> to veto that outright.  So, anything reasonably short would be an
>>>> improvement over the status quo.  self?  this?  my?
>>>
>>> I think it would have to be a reserved word. The obvious existing keyword
>>> to
>>> use is "function" but unless I'm mistaken we'd need to move it from
>>> unreserved keyword to reserved, and I'm not sure this would justify that.
>>
>> I don't see why it would need to be a reserved word.  We're not
>> changing how it gets parsed, just what it means.  At any rate
>> "FUNCTION." is a 9-character prefix, which is rather longer than I
>> would prefer.  PL/pgsql is a tiresomely long-winded language in
>> general, IMHO, although some of Tom's changes for 8.5 will help with
>> that.
>
> Umm, what has this to do with plpgsql? We're talking about what to use in
> pure SQL functions.

I assume that we might consider implementing the same thing in both
languages, if we get consensus on what it is. Perhaps I'm all wet.

> If you find plpgsql tiresome, use something else. There are plenty of
> alternatives.

Actually, I find PL/pgsql to be awesome. The only thing I find
tiresome about it is that is quite longwinded. But it at least has
the advantage that you can embed SQL queries directly into it, without
having to wrap yet another layer of quoting around them, so for the
sorts of things I typically do it tends to be better than any of the
alternatives with which I'm familiar. RETURN QUERY is a big step in
the right direction (avoiding the need to write FOR x IN <query> LOOP
RETURN NEXT x END LOOP). Tom's changes to enable short-circuit IF
evaluation should improve this quite a bit, too. But anything else we
can do is all to the good as far as I'm concerned.

> I think the debate is likely to be pointless in any case - it seems clear
> that there are objections to anything other than funcname.paramname as a
> disambiguating mechanism, so let's just go with that. It will still be a
> considerable advance.

OK, onto the next windmill.

...Robert

2009/11/16 Andrew Dunstan <andrew(at)dunslane(dot)net>:
>
>
> Robert Haas wrote:
>>
>> On Sun, Nov 15, 2009 at 9:52 PM, Andrew Dunstan <andrew(at)dunslane(dot)net>
>> wrote:
>>
>>>
>>> Robert Haas wrote:
>>>
>>>>>
>>>>> (But having said that, an alternate qualification name is something
>>>>> that could be implemented if there were any agreement on what to use.)
>>>>>
>>>>>
>>>>
>>>> Well that is the tricky part, for sure.  I would personally prefer
>>>> something like ${name} rather than a prefix, but I think you're likely
>>>> to veto that outright.  So, anything reasonably short would be an
>>>> improvement over the status quo.  self?  this?  my?
>>>>
>>>
>>> I think it would have to be a reserved word. The obvious existing keyword
>>> to
>>> use is "function" but unless I'm mistaken we'd need to move it from
>>> unreserved keyword to reserved, and I'm not sure this would justify that.
>>>
>>
>> I don't see why it would need to be a reserved word.  We're not
>> changing how it gets parsed, just what it means.  At any rate
>> "FUNCTION." is a 9-character prefix, which is rather longer than I
>> would prefer.  PL/pgsql is a tiresomely long-winded language in
>> general, IMHO, although some of Tom's changes for 8.5 will help with
>> that.

we should to use some like #option from plpgsql too.

create or replace function verylongname(p1 integer)
returns int as $$
#option paramalias v
SELECT ... WHERE x = v.p1

Pavel

>>
>>
>>
>
> Umm, what has this to do with plpgsql? We're talking about what to use in
> pure SQL functions.
>
> If you find plpgsql tiresome, use something else. There are plenty of
> alternatives.
>
> I think the debate is likely to be pointless in any case - it seems clear
> that there are objections to anything other than funcname.paramname as a
> disambiguating mechanism, so let's just go with that. It will still be a
> considerable advance.
>
> cheers
>
> andrew
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>

2009/11/16 Robert Haas <robertmhaas(at)gmail(dot)com>:
> On Sun, Nov 15, 2009 at 10:14 PM, Andrew Dunstan <andrew(at)dunslane(dot)net> wrote:
>> Robert Haas wrote:
>>> On Sun, Nov 15, 2009 at 9:52 PM, Andrew Dunstan <andrew(at)dunslane(dot)net>
>>> wrote:
>>>> Robert Haas wrote:
>>>>>> (But having said that, an alternate qualification name is something
>>>>>> that could be implemented if there were any agreement on what to use.)
>>>>>
>>>>> Well that is the tricky part, for sure.  I would personally prefer
>>>>> something like ${name} rather than a prefix, but I think you're likely
>>>>> to veto that outright.  So, anything reasonably short would be an
>>>>> improvement over the status quo.  self?  this?  my?
>>>>
>>>> I think it would have to be a reserved word. The obvious existing keyword
>>>> to
>>>> use is "function" but unless I'm mistaken we'd need to move it from
>>>> unreserved keyword to reserved, and I'm not sure this would justify that.
>>>
>>> I don't see why it would need to be a reserved word.  We're not
>>> changing how it gets parsed, just what it means.  At any rate
>>> "FUNCTION." is a 9-character prefix, which is rather longer than I
>>> would prefer.  PL/pgsql is a tiresomely long-winded language in
>>> general, IMHO, although some of Tom's changes for 8.5 will help with
>>> that.
>>
>> Umm, what has this to do with plpgsql? We're talking about what to use in
>> pure SQL functions.
>
> I assume that we might consider implementing the same thing in both
> languages, if we get consensus on what it is.  Perhaps I'm all wet.
>
>> If you find plpgsql tiresome, use something else. There are plenty of
>> alternatives.
>
> Actually, I find PL/pgsql to be awesome.  The only thing I find
> tiresome about it is that is quite longwinded.  But it at least has
> the advantage that you can embed SQL queries directly into it, without
> having to wrap yet another layer of quoting around them, so for the
> sorts of things I typically do it tends to be better than any of the
> alternatives with which I'm familiar.  RETURN QUERY is a big step in
> the right direction (avoiding the need to write FOR x IN <query> LOOP
> RETURN NEXT x END LOOP). Tom's changes to enable short-circuit IF
> evaluation should improve this quite a bit, too.  But anything else we
> can do is all to the good as far as I'm concerned.

I inclined to return back to plpgpsm reimplementation. PSM should have
very short and clean code.

CREATE OR REPLACE FUNCTION srffunc(param) RETURNS TABLE(param) AS
$$
RETURN SELECT * FROM foo;
$$ LANGUAGE plpgpsm;

This one return statement function should be little bit better
optimalised then plpgsql (from srf view).

Pavel

>
>> I think the debate is likely to be pointless in any case - it seems clear
>> that there are objections to anything other than funcname.paramname as a
>> disambiguating mechanism, so let's just go with that. It will still be a
>> considerable advance.
>
> OK, onto the next windmill.
>
> ...Robert
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>

On sön, 2009-11-15 at 12:37 -0500, 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?

Yes, search the SQL standard for <SQL parameter reference>.