Thread

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

Matthew Draper <matthew@trebex.net> — 2011-03-25T19:33:24Z

Attached is a WIP patch that allows SQL-language functions to reference
their parameters by name.

It uses p_post_columnref_hook, so potentially ambiguous references
prefer the column... that seems to make the most sense, both because it
avoids a backwards incompatibility, and it conforms with SQL's usual
notion of assuming you mean the "nearest" name.

It allows the parameter name to be qualified with the function name, for
when you really mean you want the parameter.


This being my first foray into the PostgreSQL source, I expect the code
is horribly wrong in a variety of ways. Other than that, the regression
tests I've been using are a slight modification of existing queries; I
imagine they should look measurably different.

And finally, conspicuously absent are the documentation changes that
will obviously need to accompany a real patch.

(This builds & passes `make check` on current HEAD, a4425e3)


Thanks!

Matthew


-- 
matthew@trebex.net

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

Tom Lane <tgl@sss.pgh.pa.us> — 2011-03-25T19:55:04Z

Matthew Draper <matthew@trebex.net> writes:
> Attached is a WIP patch that allows SQL-language functions to reference
> their parameters by name.

> It uses p_post_columnref_hook, so potentially ambiguous references
> prefer the column... that seems to make the most sense, both because it
> avoids a backwards incompatibility, and it conforms with SQL's usual
> notion of assuming you mean the "nearest" name.

Personally I'd vote for *not* having any such dangerous semantics as
that.  We should have learned better by now from plpgsql experience.
I think the best idea is to throw error for ambiguous references,
period.  That means you do need ways to disambiguate in both directions.
For column references you can just qualify with the table name/alias.
If the parameter reference is intended, allow qualification with the
function name.

			regards, tom lane

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

Pavel Stehule <pavel.stehule@gmail.com> — 2011-03-25T20:08:24Z

2011/3/25 Tom Lane <tgl@sss.pgh.pa.us>:
> Matthew Draper <matthew@trebex.net> writes:
>> Attached is a WIP patch that allows SQL-language functions to reference
>> their parameters by name.
>
>> It uses p_post_columnref_hook, so potentially ambiguous references
>> prefer the column... that seems to make the most sense, both because it
>> avoids a backwards incompatibility, and it conforms with SQL's usual
>> notion of assuming you mean the "nearest" name.
>
> Personally I'd vote for *not* having any such dangerous semantics as
> that.  We should have learned better by now from plpgsql experience.
> I think the best idea is to throw error for ambiguous references,
> period.  That means you do need ways to disambiguate in both directions.
> For column references you can just qualify with the table name/alias.
> If the parameter reference is intended, allow qualification with the
> function name.

I agree with Tom.

There can be GUC for controlling use or don't use a parameter names. I
am for GUC, because there will be a bilion conflicts. But a talk about
priorities - sql identifier or parameter is useless.

Regards

Pavel Stehule

>
>                        regards, tom lane
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>

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

Tom Lane <tgl@sss.pgh.pa.us> — 2011-03-25T20:20:03Z

Pavel Stehule <pavel.stehule@gmail.com> writes:
> 2011/3/25 Tom Lane <tgl@sss.pgh.pa.us>:
>> I think the best idea is to throw error for ambiguous references,
>> period.

> There can be GUC for controlling use or don't use a parameter names. I
> am for GUC, because there will be a bilion conflicts. But a talk about
> priorities - sql identifier or parameter is useless.

GUCs are not tremendously helpful for problems such as this.  If we
actually wanted to preserve full backwards compatibility, we'd need to
think of a way to mark SQL functions per-function as to what to do.
But I don't think that's necessary.  Up to now there's been relatively
little use for naming the parameters of SQL functions, so I think there
will be few conflicts in the field if we just change the behavior.  The
mess and complication we have for the comparable behavior in plpgsql
seemed necessary because of the number of existing usages that would
certainly break --- but I doubt that SQL-language functions will have
anywhere near as big a problem.

			regards, tom lane

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

Pavel Stehule <pavel.stehule@gmail.com> — 2011-03-25T21:14:34Z

2011/3/25 Tom Lane <tgl@sss.pgh.pa.us>:
> Pavel Stehule <pavel.stehule@gmail.com> writes:
>> 2011/3/25 Tom Lane <tgl@sss.pgh.pa.us>:
>>> I think the best idea is to throw error for ambiguous references,
>>> period.
>
>> There can be GUC for controlling use or don't use a parameter names. I
>> am for GUC, because there will be a bilion conflicts. But a talk about
>> priorities - sql identifier or parameter is useless.
>
> GUCs are not tremendously helpful for problems such as this.  If we
> actually wanted to preserve full backwards compatibility, we'd need to
> think of a way to mark SQL functions per-function as to what to do.
> But I don't think that's necessary.  Up to now there's been relatively
> little use for naming the parameters of SQL functions, so I think there
> will be few conflicts in the field if we just change the behavior.  The
> mess and complication we have for the comparable behavior in plpgsql
> seemed necessary because of the number of existing usages that would
> certainly break --- but I doubt that SQL-language functions will have
> anywhere near as big a problem.

should be nice some converting tool for pg_dump or pg_upgrade. It can
dump SQL functions with only qualified identifiers.

Pavel

>
>                        regards, tom lane
>

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

Robert Haas <robertmhaas@gmail.com> — 2011-03-25T22:59:09Z

On Mar 25, 2011, at 4:20 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> GUCs are not tremendously helpful for problems such as this.  If we
> actually wanted to preserve full backwards compatibility, we'd need to
> think of a way to mark SQL functions per-function as to what to do.
> But I don't think that's necessary.  Up to now there's been relatively
> little use for naming the parameters of SQL functions, so I think there
> will be few conflicts in the field if we just change the behavior. 

Oh wow, I don't agree with that at all. People may name the parameters for documentation purposes, and then have things like WHERE foo = $1, foo happening also to be the name associated with $1.  Boom!

In any case, I think this is 9.2 material. We need to get a beta out the door, and I emphatically think we should be focusing on resolving the issues with features already committed, not committing new ones.

...Robert

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

Tom Lane <tgl@sss.pgh.pa.us> — 2011-03-25T23:45:07Z

Robert Haas <robertmhaas@gmail.com> writes:
> On Mar 25, 2011, at 4:20 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> But I don't think that's necessary.  Up to now there's been relatively
>> little use for naming the parameters of SQL functions, so I think there
>> will be few conflicts in the field if we just change the behavior. 

> Oh wow, I don't agree with that at all. People may name the parameters for documentation purposes, and then have things like WHERE foo = $1, foo happening also to be the name associated with $1.  Boom!

Well, maybe, but it's not like it's subtle or hard to fix.

> In any case, I think this is 9.2 material.

Oh, of course.  It *is* just a WIP patch, anyway.

			regards, tom lane

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

Robert Haas <robertmhaas@gmail.com> — 2011-03-26T00:42:14Z

On Mar 25, 2011, at 7:45 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Well, maybe, but it's not like it's subtle or hard to fix.

Depends how much of it you have. I've become very skeptical of anything that breaks pg_dump-and-reload-ability.  And doubly so now that such problems also mean breaking pg_upgrade after the old cluster has been shut down.

...Robert

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

Tom Lane <tgl@sss.pgh.pa.us> — 2011-03-26T00:58:47Z

Robert Haas <robertmhaas@gmail.com> writes:
> On Mar 25, 2011, at 7:45 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> Well, maybe, but it's not like it's subtle or hard to fix.

> Depends how much of it you have. I've become very skeptical of
> anything that breaks pg_dump-and-reload-ability.

This wouldn't break pg_dump scripts, because they disable
check_function_bodies.  You would get a failure on first *use*
of a function, which is something different.

Basically my concern here is that in the name of easing a short-term
conversion issue, we'll be condemning users to a future of subtle,
hard-to-find bugs due to ambiguous names.  How many hundreds of
reports have we seen about the equivalent problem in plpgsql?

You could argue that the frequency of plpgsql issues was at least partly
due to having a poor choice of which way to resolve the ambiguity, but
I don't think it can be entirely blamed on that.

			regards, tom lane

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

Robert Haas <robertmhaas@gmail.com> — 2011-03-26T01:12:33Z

On Fri, Mar 25, 2011 at 8:58 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Robert Haas <robertmhaas@gmail.com> writes:
>> On Mar 25, 2011, at 7:45 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>>> Well, maybe, but it's not like it's subtle or hard to fix.
>
>> Depends how much of it you have. I've become very skeptical of
>> anything that breaks pg_dump-and-reload-ability.
>
> This wouldn't break pg_dump scripts, because they disable
> check_function_bodies.  You would get a failure on first *use*
> of a function, which is something different.
>
> Basically my concern here is that in the name of easing a short-term
> conversion issue, we'll be condemning users to a future of subtle,
> hard-to-find bugs due to ambiguous names.  How many hundreds of
> reports have we seen about the equivalent problem in plpgsql?
>
> You could argue that the frequency of plpgsql issues was at least partly
> due to having a poor choice of which way to resolve the ambiguity, but
> I don't think it can be entirely blamed on that.

As I've said before, I believe that the root cause of this problem is
that using the same syntax for variables and column names is a bad
idea in the first place.  If we used $foo or ?foo or ${foo} or $.foo
or &&foo!!$#? to mean "the parameter called foo", then this would all
be a non-issue.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

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

Josh Berkus <josh@agliodbs.com> — 2011-03-26T01:22:48Z

Tom,

> Personally I'd vote for *not* having any such dangerous semantics as
> that. We should have learned better by now from plpgsql experience.
> I think the best idea is to throw error for ambiguous references,
> period. 

As a likely heavy user of this feature, I agree with Tom here.  I really don't want the column being silently preferred in SQL functions, when PL/pgSQL functions are throwing an error.  I'd end up spending hours debugging this.

Also, I don't understand why this would be a dump/reload issue if $1 and $2 continue to work.

-- 
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com
San Francisco

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

Robert Haas <robertmhaas@gmail.com> — 2011-03-26T02:45:13Z

On Mar 25, 2011, at 9:22 PM, Joshua Berkus <josh@agliodbs.com> wrote:
> Tom,
> 
>> Personally I'd vote for *not* having any such dangerous semantics as
>> that. We should have learned better by now from plpgsql experience.
>> I think the best idea is to throw error for ambiguous references,
>> period. 
> 
> As a likely heavy user of this feature, I agree with Tom here.  I really don't want the column being silently preferred in SQL functions, when PL/pgSQL functions are throwing an error.  I'd end up spending hours debugging this.
> 
> Also, I don't understand why this would be a dump/reload issue if $1 and $2 continue to work.
> 

Because an identifier that previously referred unambiguously to a column might now be ambiguous, if there is a parameter with the same name.

...Robert

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

Tom Lane <tgl@sss.pgh.pa.us> — 2011-03-26T03:01:45Z

Robert Haas <robertmhaas@gmail.com> writes:
> On Mar 25, 2011, at 9:22 PM, Joshua Berkus <josh@agliodbs.com> wrote:
>> Also, I don't understand why this would be a dump/reload issue if $1 and $2 continue to work.

> Because an identifier that previously referred unambiguously to a column might now be ambiguous, if there is a parameter with the same name.

Yes, a function that previously worked might now throw error, if we make
ambiguous names be errors.  But this is *not* a failure that would occur
during dump/reload.  You'd have to actually run the function.

			regards, tom lane

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

David Wheeler <david@kineticode.com> — 2011-03-26T03:05:50Z

On Mar 25, 2011, at 9:12 PM, Robert Haas wrote:

> 
> As I've said before, I believe that the root cause of this problem is
> that using the same syntax for variables and column names is a bad
> idea in the first place.  If we used $foo or ?foo or ${foo} or $.foo
> or &&foo!!$#? to mean "the parameter called foo", then this would all
> be a non-issue.

Yes *please*. Man that would make maintenance of such functions easier.

Best,

David

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

Tom Lane <tgl@sss.pgh.pa.us> — 2011-03-26T03:23:42Z

Robert Haas <robertmhaas@gmail.com> writes:
> As I've said before, I believe that the root cause of this problem is
> that using the same syntax for variables and column names is a bad
> idea in the first place.  If we used $foo or ?foo or ${foo} or $.foo
> or &&foo!!$#? to mean "the parameter called foo", then this would all
> be a non-issue.

If this were PL/perl, or PL/almost-anything-except-SQL, I could get
behind such a proposal.  But it's not, it's SQL; and SQL doesn't do
things that way.  SQL's idea of disambiguation is qualified names.

And even more to the point: to the extent you think that weird syntax
might be a suitable solution, you have to keep in mind that the SQL
committee could take over any such syntax at the drop of a hat.
See the recent unpleasantness concerning => ...

			regards, tom lane

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

Darren Duncan <darren@darrenduncan.net> — 2011-03-26T03:50:21Z

Robert Haas wrote:
> On Mar 25, 2011, at 9:22 PM, Joshua Berkus <josh@agliodbs.com> wrote:
>> Tom,
>>
>>> Personally I'd vote for *not* having any such dangerous semantics as
>>> that. We should have learned better by now from plpgsql experience.
>>> I think the best idea is to throw error for ambiguous references,
>>> period. 
>> As a likely heavy user of this feature, I agree with Tom here.  I really don't want the column being silently preferred in SQL functions, when PL/pgSQL functions are throwing an error.  I'd end up spending hours debugging this.
>>
>> Also, I don't understand why this would be a dump/reload issue if $1 and $2 continue to work.
> 
> Because an identifier that previously referred unambiguously to a column might now be ambiguous, if there is a parameter with the same name.

I mention 2 possible solutions here, both which involve syntax alterations, each 
between the ---------- lines.  I personally like the second/lower option more.

------------

Might it be reasonable, perhaps as a 9.2 feature, to add top-level-namespaces so 
that one could always explicitly qualify what they are referring to?

For example, you could have the 3 "sch", "lex", "attr" (I may have missed some 
useful ones).

The "sch" TLN would unambiguously refer directly to a schema object, such as a 
database table.

The "lex" TLN would unambiguously refer directly to a lexical, either a 
parameter of the current routine or to a lexical variable.

The "attr" TLN would unambiguously refer to a table/etc column/attribute in the 
manner typical for SQL.

Use them like:

   sch.foo - the table/etc foo
   lex.foo - the lexical variable foo
   attr.foo - the column foo

Use of these TLN are optional where there is no ambiguity.

The TLN are not reserved words, but if one has an entity named the same, then 
references to it must be TLN-qualified; eg:

   lex.sch
   lex.lex
   lex.attr

Now these are just examples.  You may find a different set works better.

--------------

There are also alternate solutions.

For example, it could be mandated that lexical-scope aliases for any 
data/var-like schema object are required in routines, where the aliases are 
distinct from all lexical vars/params/etc, and then all SQL/code in the routines 
may only refer to the schema objects by the aliases.

Effectively this makes it so that routines can no longer see non-lexical vars 
but for those from parameters, and this aliasing is defining a parameter whose 
argument is supplied by the DBMS automatically rather than as an explicit 
routine caller argument.

That way, inside a routine body there are only lexical names for things, and so 
no namespace-qualification is ever needed by the regular SQL.

Similarly, if you always think of table column names as referring to an 
attribute or element of a table variable, then just reference the column 
qualified by the table name (or the lexical alias thereof).  Same as you do in 
any other programming language.  Of course, sometimes you don't have to qualify 
column name references as context could make it unambiguous.  Or, a shorthand 
like a simple leading "." could unambiguously say you're referring to a column 
of the particular table in context.

With those in place, all unqualified references are straight to lexical 
variables or parameters.

And so, this is also an effective way to resolve the ambiguity and I prefer the 
latter design personally.

Here's an example in quasi-PL/PgSQL:

   create function myrtn (myparam integer, mytbl ::= mydb.myschema.mytbl) as
   declare
       myvar integer := 5;
   $body$
   begin
       select (.mycol + myvar * myparam) as mynewcol from mytbl;
   end;
   $body$

Note that I've already thought through this last example as these methods of 
avoiding ambiguity are loosely-speaking how my language Muldis D avoids the 
problem faced by many SQL procedures.

The ".mycol" syntax specifically was inspired originally for me by Perl 6 where 
the lack of something just before the "." means that the implicit topic variable 
is referred to, like if you said "$_.mycol".

A Perl 6 analogy being something like:

   $mytbl.map:{ .mycol + $myvar * $myparam }

aka:

   $mytbl.map:{ $_.mycol + $myvar * $myparam }

--------------

-- Darren Duncan

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

David Wheeler <david@kineticode.com> — 2011-03-26T06:06:21Z

On Mar 25, 2011, at 11:23 PM, Tom Lane wrote:

> If this were PL/perl, or PL/almost-anything-except-SQL, I could get
> behind such a proposal.  But it's not, it's SQL; and SQL doesn't do
> things that way.  SQL's idea of disambiguation is qualified names.
> 
> And even more to the point: to the extent you think that weird syntax
> might be a suitable solution, you have to keep in mind that the SQL
> committee could take over any such syntax at the drop of a hat.
> See the recent unpleasantness concerning => ...

Perhaps we could use `=>varname`. ;-P

David

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

Pavel Stehule <pavel.stehule@gmail.com> — 2011-03-26T07:24:09Z

2011/3/26 Darren Duncan <darren@darrenduncan.net>:
> Robert Haas wrote:
>>
>> On Mar 25, 2011, at 9:22 PM, Joshua Berkus <josh@agliodbs.com> wrote:
>>>
>>> Tom,
>>>
>>>> Personally I'd vote for *not* having any such dangerous semantics as
>>>> that. We should have learned better by now from plpgsql experience.
>>>> I think the best idea is to throw error for ambiguous references,
>>>> period.
>>>
>>> As a likely heavy user of this feature, I agree with Tom here.  I really
>>> don't want the column being silently preferred in SQL functions, when
>>> PL/pgSQL functions are throwing an error.  I'd end up spending hours
>>> debugging this.
>>>
>>> Also, I don't understand why this would be a dump/reload issue if $1 and
>>> $2 continue to work.
>>
>> Because an identifier that previously referred unambiguously to a column
>> might now be ambiguous, if there is a parameter with the same name.
>
> I mention 2 possible solutions here, both which involve syntax alterations,
> each between the ---------- lines.  I personally like the second/lower
> option more.
>
> ------------
>
> Might it be reasonable, perhaps as a 9.2 feature, to add
> top-level-namespaces so that one could always explicitly qualify what they
> are referring to?
>
> For example, you could have the 3 "sch", "lex", "attr" (I may have missed
> some useful ones).
>
> The "sch" TLN would unambiguously refer directly to a schema object, such as
> a database table.
>
> The "lex" TLN would unambiguously refer directly to a lexical, either a
> parameter of the current routine or to a lexical variable.
>
> The "attr" TLN would unambiguously refer to a table/etc column/attribute in
> the manner typical for SQL.
>
> Use them like:
>
>  sch.foo - the table/etc foo
>  lex.foo - the lexical variable foo
>  attr.foo - the column foo
>
> Use of these TLN are optional where there is no ambiguity.
>
> The TLN are not reserved words, but if one has an entity named the same,
> then references to it must be TLN-qualified; eg:
>
>  lex.sch
>  lex.lex
>  lex.attr
>
> Now these are just examples.  You may find a different set works better.

-1

this is not based on any pattern on SQL. It's not simple, and it
introduce a reserved keywords

Regards

Pavel

>
> --------------
>
> There are also alternate solutions.
>
> For example, it could be mandated that lexical-scope aliases for any
> data/var-like schema object are required in routines, where the aliases are
> distinct from all lexical vars/params/etc, and then all SQL/code in the
> routines may only refer to the schema objects by the aliases.
>
> Effectively this makes it so that routines can no longer see non-lexical
> vars but for those from parameters, and this aliasing is defining a
> parameter whose argument is supplied by the DBMS automatically rather than
> as an explicit routine caller argument.
>
> That way, inside a routine body there are only lexical names for things, and
> so no namespace-qualification is ever needed by the regular SQL.
>
> Similarly, if you always think of table column names as referring to an
> attribute or element of a table variable, then just reference the column
> qualified by the table name (or the lexical alias thereof).  Same as you do
> in any other programming language.  Of course, sometimes you don't have to
> qualify column name references as context could make it unambiguous.  Or, a
> shorthand like a simple leading "." could unambiguously say you're referring
> to a column of the particular table in context.
>
> With those in place, all unqualified references are straight to lexical
> variables or parameters.
>
> And so, this is also an effective way to resolve the ambiguity and I prefer
> the latter design personally.
>
> Here's an example in quasi-PL/PgSQL:
>
>  create function myrtn (myparam integer, mytbl ::= mydb.myschema.mytbl) as
>  declare
>      myvar integer := 5;
>  $body$
>  begin
>      select (.mycol + myvar * myparam) as mynewcol from mytbl;
>  end;
>  $body$
>
> Note that I've already thought through this last example as these methods of
> avoiding ambiguity are loosely-speaking how my language Muldis D avoids the
> problem faced by many SQL procedures.
>
> The ".mycol" syntax specifically was inspired originally for me by Perl 6
> where the lack of something just before the "." means that the implicit
> topic variable is referred to, like if you said "$_.mycol".
>
> A Perl 6 analogy being something like:
>
>  $mytbl.map:{ .mycol + $myvar * $myparam }
>
> aka:
>
>  $mytbl.map:{ $_.mycol + $myvar * $myparam }
>
> --------------
>
> -- Darren Duncan
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>

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

Robert Haas <robertmhaas@gmail.com> — 2011-03-26T13:41:46Z

On Mar 25, 2011, at 11:23 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> If this were PL/perl, or PL/almost-anything-except-SQL, I could get
> behind such a proposal.  But it's not, it's SQL; and SQL doesn't do
> things that way.  SQL's idea of disambiguation is qualified names.
> 
> And even more to the point: to the extent you think that weird syntax
> might be a suitable solution, you have to keep in mind that the SQL
> committee could take over any such syntax at the drop of a hat.
> See the recent unpleasantness concerning => ...

You can't be guaranteed that they won't standardize something incompatible no matter what we do.  We could choose to do it as you've proposed and they could then standardize some weird syntax - the => is a fairly relevant example of exactly that.

...Robert

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

Darren Duncan <darren@darrenduncan.net> — 2011-03-26T20:38:37Z

Pavel Stehule wrote:
> 2011/3/26 Darren Duncan <darren@darrenduncan.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?

-- Darren Duncan

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

Pavel Stehule <pavel.stehule@gmail.com> — 2011-03-26T20:53:11Z

2011/3/26 Darren Duncan <darren@darrenduncan.net>:
> Pavel Stehule wrote:
>>
>> 2011/3/26 Darren Duncan <darren@darrenduncan.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
>

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

Darren Duncan <darren@darrenduncan.net> — 2011-03-26T21:04:14Z

Pavel Stehule wrote:
> 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.

Well, going forward, I know I would much rather have to say "mytbl.mycol" than 
have to say "myfunc.myparam".  And I certainly would want to expect that when 
one says "... as foo" that this "foo" is treated as a declaration unambiguously 
and is never substituted for some parameter or there be other grief as I seem to 
recall having in 8.4. -- Darren Duncan

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

Dimitri Fontaine <dimitri@2ndquadrant.fr> — 2011-03-26T21:19:12Z

Joshua Berkus <josh@agliodbs.com> writes:
>> Personally I'd vote for *not* having any such dangerous semantics as
>> that. We should have learned better by now from plpgsql experience.
>> I think the best idea is to throw error for ambiguous references,
>> period. 
>
> As a likely heavy user of this feature, I agree with Tom here.  I really
> don't want the column being silently preferred in SQL functions, when
> PL/pgSQL functions are throwing an error.  I'd end up spending hours
> debugging this.

+1

I think the best choice is to only accept qualified parameter names in
SQL functions (function_name.parameter_name).  If a referenced table
share the function's name, ERROR out and HINT to alias the table name.

If we allow more than that, we're opening the door to ambiguity, bug
reports, and more than that costly migrations.  I don't see any benefit
in having to audit all SQL functions for ambiguity on a flag day, when
this could be avoided easily.

Regards,
-- 
Dimitri Fontaine
http://2ndQuadrant.fr     PostgreSQL : Expertise, Formation et Support

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

Pavel Stehule <pavel.stehule@gmail.com> — 2011-03-26T21:26:55Z

2011/3/26 Dimitri Fontaine <dimitri@2ndquadrant.fr>:
> Joshua Berkus <josh@agliodbs.com> writes:
>>> Personally I'd vote for *not* having any such dangerous semantics as
>>> that. We should have learned better by now from plpgsql experience.
>>> I think the best idea is to throw error for ambiguous references,
>>> period.
>>
>> As a likely heavy user of this feature, I agree with Tom here.  I really
>> don't want the column being silently preferred in SQL functions, when
>> PL/pgSQL functions are throwing an error.  I'd end up spending hours
>> debugging this.
>
> +1
>
> I think the best choice is to only accept qualified parameter names in
> SQL functions (function_name.parameter_name).  If a referenced table
> share the function's name, ERROR out and HINT to alias the table name.

it's maybe too hard. I agree so we should to use a function_name alias
when collision is possible. Still there are more use cases, where SQL
function is used as macro, and there a alias isn't necessary

CREATE OR REPLACE FUNCTION greatest(VARIADIC "values" anyarray)
RETURNS anyelement AS $$
SELECT max(v) FROM unnest("values")
$$ LANGUAGE sql;

Regards

Pavel



>
> If we allow more than that, we're opening the door to ambiguity, bug
> reports, and more than that costly migrations.  I don't see any benefit
> in having to audit all SQL functions for ambiguity on a flag day, when
> this could be avoided easily.
>
> Regards,
> --
> Dimitri Fontaine
> http://2ndQuadrant.fr     PostgreSQL : Expertise, Formation et Support
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>

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

Robert Haas <robertmhaas@gmail.com> — 2011-03-26T22:06:05Z

On Sat, Mar 26, 2011 at 5:19 PM, Dimitri Fontaine
<dimitri@2ndquadrant.fr> wrote:
> I think the best choice is to only accept qualified parameter names in
> SQL functions (function_name.parameter_name).  If a referenced table
> share the function's name, ERROR out and HINT to alias the table name.
>
> If we allow more than that, we're opening the door to ambiguity, bug
> reports, and more than that costly migrations.  I don't see any benefit
> in having to audit all SQL functions for ambiguity on a flag day, when
> this could be avoided easily.

That syntax is sufficiently unwieldly that few people will want to use
it in real life, but certainly the backward compatibility problem is
much less than with what Tom proposed.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

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

Dimitri Fontaine <dimitri@2ndquadrant.fr> — 2011-03-27T10:11:09Z

Robert Haas <robertmhaas@gmail.com> writes:

> On Sat, Mar 26, 2011 at 5:19 PM, Dimitri Fontaine
> <dimitri@2ndquadrant.fr> wrote:
>> I think the best choice is to only accept qualified parameter names in
>> SQL functions (function_name.parameter_name).  If a referenced table
>> share the function's name, ERROR out and HINT to alias the table name.
>>
>> If we allow more than that, we're opening the door to ambiguity, bug
>> reports, and more than that costly migrations.  I don't see any benefit
>> in having to audit all SQL functions for ambiguity on a flag day, when
>> this could be avoided easily.
>
> That syntax is sufficiently unwieldly that few people will want to use
> it in real life, but certainly the backward compatibility problem is
> much less than with what Tom proposed.

Well, we would still support positional arguments like $1 $2 etc, right?

In Pavel's example I wouldn't mind about using the "values" parameter
name but would stick to using $1.

Regards,
-- 
Dimitri Fontaine
http://2ndQuadrant.fr     PostgreSQL : Expertise, Formation et Support

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

Robert Haas <robertmhaas@gmail.com> — 2011-03-27T11:14:38Z

On Mar 27, 2011, at 6:11 AM, Dimitri Fontaine <dimitri@2ndQuadrant.fr> wrote:
> Robert Haas <robertmhaas@gmail.com> writes:
>> That syntax is sufficiently unwieldly that few people will want to use
>> it in real life, but certainly the backward compatibility problem is
>> much less than with what Tom proposed.
> 
> Well, we would still support positional arguments like $1 $2 etc, right?

Yeah, that's not going away.

...Robert

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

David Fetter <david@fetter.org> — 2011-03-27T13:42:33Z

On Fri, Mar 25, 2011 at 09:12:33PM -0400, Robert Haas wrote:
> On Fri, Mar 25, 2011 at 8:58 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> > Robert Haas <robertmhaas@gmail.com> writes:
> >> On Mar 25, 2011, at 7:45 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> >>> Well, maybe, but it's not like it's subtle or hard to fix.
> >
> >> Depends how much of it you have. I've become very skeptical of
> >> anything that breaks pg_dump-and-reload-ability.
> >
> > This wouldn't break pg_dump scripts, because they disable
> > check_function_bodies.  You would get a failure on first *use*
> > of a function, which is something different.
> >
> > Basically my concern here is that in the name of easing a short-term
> > conversion issue, we'll be condemning users to a future of subtle,
> > hard-to-find bugs due to ambiguous names.  How many hundreds of
> > reports have we seen about the equivalent problem in plpgsql?
> >
> > You could argue that the frequency of plpgsql issues was at least partly
> > due to having a poor choice of which way to resolve the ambiguity, but
> > I don't think it can be entirely blamed on that.
> 
> As I've said before, I believe that the root cause of this problem is
> that using the same syntax for variables and column names is a bad
> idea in the first place.  If we used $foo or ?foo or ${foo} or $.foo
> or &&foo!!$#? to mean "the parameter called foo", then this would all
> be a non-issue.

How about psql's :foo syntax?

Cheers,
David.
-- 
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter      XMPP: david.fetter@gmail.com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

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

Andrew Dunstan <andrew@dunslane.net> — 2011-03-27T14:45:19Z


On 03/27/2011 09:42 AM, David Fetter wrote:
> On Fri, Mar 25, 2011 at 09:12:33PM -0400, Robert Haas wrote:
>> As I've said before, I believe that the root cause of this problem is
>> that using the same syntax for variables and column names is a bad
>> idea in the first place.  If we used $foo or ?foo or ${foo} or $.foo
>> or&&foo!!$#? to mean "the parameter called foo", then this would all
>> be a non-issue.
> How about psql's :foo syntax?
>
>

Surely the time has long gone when we could have made such a choice. And 
the choice was not made in a vacuum.

cheers

andrew

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

Pavel Stehule <pavel.stehule@gmail.com> — 2011-03-27T16:52:03Z

2011/3/27 Andrew Dunstan <andrew@dunslane.net>:
>
>
> On 03/27/2011 09:42 AM, David Fetter wrote:
>>
>> On Fri, Mar 25, 2011 at 09:12:33PM -0400, Robert Haas wrote:
>>>
>>> As I've said before, I believe that the root cause of this problem is
>>> that using the same syntax for variables and column names is a bad
>>> idea in the first place.  If we used $foo or ?foo or ${foo} or $.foo
>>> or&&foo!!$#? to mean "the parameter called foo", then this would all
>>> be a non-issue.
>>
>> How about psql's :foo syntax?
>>
>>
>
> Surely the time has long gone when we could have made such a choice. And the
> choice was not made in a vacuum.
>

Syntax for named parameters should be consistent with prepared
statement. Is there any comments in standard?

Regards

Pavel

SQL/PSM doesn't use any prefix - but it is little bit different
chapter then prepared statements.



> cheers
>
> andrew
>

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

Martijn van Oosterhout <kleptog@svana.org> — 2011-03-27T17:10:44Z

On Sun, Mar 27, 2011 at 06:52:03PM +0200, Pavel Stehule wrote:
> Syntax for named parameters should be consistent with prepared
> statement. Is there any comments in standard?

Well, there's section 4.24 which says:

"In SQL-statements that are executed dynamically, the parameters are
called dynamic parameters (<dynamic parameter specification>s) and are
represented in SQL language by a <question mark> (?)."

which I think we can all agree is not a useful guide.

Have a nice day,

-- 
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> Patriotism is when love of your own people comes first; nationalism,
> when hate for people other than your own comes first. 
>                                       - Charles de Gaulle

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

Pavel Stehule <pavel.stehule@gmail.com> — 2011-03-27T17:30:43Z

2011/3/27 Martijn van Oosterhout <kleptog@svana.org>:
> On Sun, Mar 27, 2011 at 06:52:03PM +0200, Pavel Stehule wrote:
>> Syntax for named parameters should be consistent with prepared
>> statement. Is there any comments in standard?
>
> Well, there's section 4.24 which says:
>
> "In SQL-statements that are executed dynamically, the parameters are
> called dynamic parameters (<dynamic parameter specification>s) and are
> represented in SQL language by a <question mark> (?)."
>

ok, so my preferences:

1. no prefix - using a qualified identifiers - it's consistent with
plpgsql and sql/psm
2 :name - it uses a Oracle and it can help to people who migrate from Oracle
3. $name - it is like enhancing of $number parameters - lot of
languages uses this notation

Regards

Pavel Stehule


> which I think we can all agree is not a useful guide.
>
> Have a nice day,
>
> --
> Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
>> Patriotism is when love of your own people comes first; nationalism,
>> when hate for people other than your own comes first.
>>                                       - Charles de Gaulle
>
> -----BEGIN PGP SIGNATURE-----
> Version: GnuPG v1.4.9 (GNU/Linux)
>
> iD8DBQFNj2+UIB7bNG8LQkwRAjGeAJ99K0Zrp1EM/ike6h+cL5ZEH1Lb2QCfY28L
> 7ETpiWfLtD90ISCVMvEgaSs=
> =YEx2
> -----END PGP SIGNATURE-----
>
>

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

Peter Eisentraut <peter_e@gmx.net> — 2011-03-28T20:18:16Z

On lör, 2011-03-26 at 09:41 -0400, Robert Haas wrote:
> You can't be guaranteed that they won't standardize something
> incompatible no matter what we do.  We could choose to do it as you've
> proposed and they could then standardize some weird syntax - the => is
> a fairly relevant example of exactly that.

The matter of how to resolve SQL parameter names is already
standardized.  See clause on <identifier chain>.

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

Jim Nasby <jim@nasby.net> — 2011-04-05T15:10:24Z

On Mar 28, 2011, at 3:18 PM, Peter Eisentraut wrote:
> On lör, 2011-03-26 at 09:41 -0400, Robert Haas wrote:
>> You can't be guaranteed that they won't standardize something
>> incompatible no matter what we do.  We could choose to do it as you've
>> proposed and they could then standardize some weird syntax - the => is
>> a fairly relevant example of exactly that.
> 
> The matter of how to resolve SQL parameter names is already
> standardized.  See clause on <identifier chain>.

Was there a final consensus on this?

FWIW, if we go with using function name, it'd be nice to be allowed to alias that. I don't have a strong opinion between that and using : or $ or whatever. I do feel strongly that we must continue to support existing SQL functions in a reasonable fashion. Having the function blow up on the first invocation is no better than breaking the dump. There should be either a backwards-compatibility mode, or better yet, a way to automatically convert functions to be compatible with the new syntax.
--
Jim C. Nasby, Database Architect                   jim@nasby.net
512.569.9461 (cell)                         http://jim.nasby.net

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

Merlin Moncure <mmoncure@gmail.com> — 2011-04-05T16:21:20Z

On Fri, Mar 25, 2011 at 10:05 PM, David E. Wheeler <david@kineticode.com> wrote:
> On Mar 25, 2011, at 9:12 PM, Robert Haas wrote:
>
>>
>> As I've said before, I believe that the root cause of this problem is
>> that using the same syntax for variables and column names is a bad
>> idea in the first place.  If we used $foo or ?foo or ${foo} or $.foo
>> or &&foo!!$#? to mean "the parameter called foo", then this would all
>> be a non-issue.
>
> Yes *please*. Man that would make maintenance of such functions easier.

+1 on using $foo.  Even with the standardization risk I think it's the
best choice. Prefer $"foo" to ${foo} though.

merlin

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

Peter Eisentraut <peter_e@gmx.net> — 2011-04-05T17:45:28Z

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.

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

Robert Haas <robertmhaas@gmail.com> — 2011-04-05T19:05:14Z

On Tue, Apr 5, 2011 at 1:45 PM, Peter Eisentraut <peter_e@gmx.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?

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

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

Darren Duncan <darren@darrenduncan.net> — 2011-04-05T19:32:21Z

Merlin Moncure wrote:
> On Fri, Mar 25, 2011 at 10:05 PM, David E. Wheeler <david@kineticode.com> wrote:
>> On Mar 25, 2011, at 9:12 PM, Robert Haas wrote:
>>
>>> As I've said before, I believe that the root cause of this problem is
>>> that using the same syntax for variables and column names is a bad
>>> idea in the first place.  If we used $foo or ?foo or ${foo} or $.foo
>>> or &&foo!!$#? to mean "the parameter called foo", then this would all
>>> be a non-issue.
>> Yes *please*. Man that would make maintenance of such functions easier.
> 
> +1 on using $foo.  Even with the standardization risk I think it's the
> best choice. Prefer $"foo" to ${foo} though.

The "foo" syntax should be orthogonal to everything else and not have anything 
specifically to do with parameters.  Rather, "foo" anywhere is just a delimited 
case-sensitive identifier and can be used anywhere that foo can where the latter 
is a case-insensitive identifier.

As for the SQL standard for bind parameters, as I recall they use :foo and so 
:"foo" would be the sensitive more general case of that.

-- Darren Duncan

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

Peter Eisentraut <peter_e@gmx.net> — 2011-04-05T19:33:00Z

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

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

Merlin Moncure <mmoncure@gmail.com> — 2011-04-05T19:45:55Z

On Tue, Apr 5, 2011 at 2:33 PM, Peter Eisentraut <peter_e@gmx.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@gmx.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

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

Pavel Stehule <pavel.stehule@gmail.com> — 2011-04-05T19:46:10Z

2011/4/5 Peter Eisentraut <peter_e@gmx.net>:
> 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@gmx.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.)

This is a good design. If we disallow a ambiguities, there isn't a
space for bugs. And if anybody needs to accent any parameter, then
there are still $n notation.

There is lot of notation and I don't think so it is necessary to add new one

MySQL, MSSQL uses @, DB2, ANSI SQL no prefix, Oracle and Firebird uses
":", but in different context.

simply - chaos.

There was request for some alias on function name. It could be.
PL/pgSQL knows a #option, so there can be some similar in SQL.

CREATE OR REPLACE FUNCTION longnamefunc(param integer)
RETURNS ... AS $$
   #alias longnamefunc ln
  SELECT ln.param;
$$

Regards

Pavel Stehule

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

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

Pavel Stehule <pavel.stehule@gmail.com> — 2011-04-05T19:52:15Z

2011/4/5 Merlin Moncure <mmoncure@gmail.com>:
> On Tue, Apr 5, 2011 at 2:33 PM, Peter Eisentraut <peter_e@gmx.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@gmx.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.


A PSM support for PostgreSQL is almost done. I expect a production
quality for 9.2.

MySQL support own language based on PSM with lot of inspiration in T-SQL.

In MySQL - local variables are clasic, only session variables has a prefix @.

Regards

Pavel

 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
>

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

Peter Eisentraut <peter_e@gmx.net> — 2011-04-05T20:47:40Z

On tis, 2011-04-05 at 14:45 -0500, Merlin Moncure wrote:
> 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

That doesn't mean it couldn't be better in the future.  We shouldn't
take it further away, in any case.

As long as we use LANGUAGE SQL, we are both technically and morally in
standards-space.

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

Merlin Moncure <mmoncure@gmail.com> — 2011-04-05T21:30:58Z

On Tue, Apr 5, 2011 at 3:47 PM, Peter Eisentraut <peter_e@gmx.net> wrote:
> On tis, 2011-04-05 at 14:45 -0500, Merlin Moncure wrote:
>> 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
>
> That doesn't mean it couldn't be better in the future.  We shouldn't
> take it further away, in any case.
>
> As long as we use LANGUAGE SQL, we are both technically and morally in
> standards-space.

sql standard functions are psm routines aiui.  Are you making the case
that 'language sql' in postgresql could or should in fact be psm at
some point in the future?  I say that's not the case -- our 'language
sql' is not psm.

That said, if you well and truly stated that it was project objective
to allow psm constructions in 'language sql', and you could figure out
a way to do that without breaking current sql code, I would have to
say i'm coming around to your point of view.  Either way, our $N
notation is already non-standard and highly in use - what's the big
deal about making it more useful?

merlin

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

Andrew Dunstan <andrew@dunslane.net> — 2011-04-05T21:52:58Z


On 04/05/2011 03:45 PM, Merlin Moncure wrote:
> 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.
>
>

That doesn't mean we should arbitrarily break compatibility with pl/sql, 
nor that we should feel free to add on warts such as $varname that are 
completely at odds with the style of the rest of the language. That 
doesn't do anything except produce a mess.

cheers

andrew

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

Robert Haas <robertmhaas@gmail.com> — 2011-04-05T22:36:19Z

On Tue, Apr 5, 2011 at 5:52 PM, Andrew Dunstan <andrew@dunslane.net> wrote:
> That doesn't mean we should arbitrarily break compatibility with pl/sql, nor
> that we should feel free to add on warts such as $varname that are
> completely at odds with the style of the rest of the language. That doesn't
> do anything except produce a mess.

Well, what it does is avoid breaking compatibility with previous
versions of PostgreSQL.  I think that actually does have some value.
Otherwise, we'd be folding to upper-case by default.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

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

Tom Lane <tgl@sss.pgh.pa.us> — 2011-04-08T01:58:50Z

Robert Haas <robertmhaas@gmail.com> writes:
> On Tue, Apr 5, 2011 at 5:52 PM, Andrew Dunstan <andrew@dunslane.net> wrote:
>> That doesn't mean we should arbitrarily break compatibility with pl/sql, nor
>> that we should feel free to add on warts such as $varname that are
>> completely at odds with the style of the rest of the language. That doesn't
>> do anything except produce a mess.

> Well, what it does is avoid breaking compatibility with previous
> versions of PostgreSQL.  I think that actually does have some value.
> Otherwise, we'd be folding to upper-case by default.

Well, if we're going to consider 100% backwards compatibility a "must",
then we should just stick with what the submitted patch does, ie,
unqualified names are matched first to query columns, and to parameters
only if there's no column match.  This is also per spec if I interpreted
Peter's comments correctly.  The whole thread started because I
suggested that throwing an error for ambiguous cases might be a better
design in the long run, but apparently long term ease of code
maintenance is far down our list of priorities ...

			regards, tom lane

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

David Wheeler <david@kineticode.com> — 2011-04-08T03:06:14Z

On Apr 7, 2011, at 6:58 PM, Tom Lane wrote:

> Well, if we're going to consider 100% backwards compatibility a "must",
> then we should just stick with what the submitted patch does, ie,
> unqualified names are matched first to query columns, and to parameters
> only if there's no column match.  This is also per spec if I interpreted
> Peter's comments correctly.  The whole thread started because I
> suggested that throwing an error for ambiguous cases might be a better
> design in the long run, but apparently long term ease of code
> maintenance is far down our list of priorities ...

I agree with you that it should throw an error, at least optionally. Could we not recycle the settings that control this for plpgsql functions?

Best,

David

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

Robert Haas <robertmhaas@gmail.com> — 2011-04-08T03:30:50Z

On Thu, Apr 7, 2011 at 9:58 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Robert Haas <robertmhaas@gmail.com> writes:
>> On Tue, Apr 5, 2011 at 5:52 PM, Andrew Dunstan <andrew@dunslane.net> wrote:
>>> That doesn't mean we should arbitrarily break compatibility with pl/sql, nor
>>> that we should feel free to add on warts such as $varname that are
>>> completely at odds with the style of the rest of the language. That doesn't
>>> do anything except produce a mess.
>
>> Well, what it does is avoid breaking compatibility with previous
>> versions of PostgreSQL.  I think that actually does have some value.
>> Otherwise, we'd be folding to upper-case by default.
>
> Well, if we're going to consider 100% backwards compatibility a "must",
> then we should just stick with what the submitted patch does, ie,
> unqualified names are matched first to query columns, and to parameters
> only if there's no column match.  This is also per spec if I interpreted
> Peter's comments correctly.  The whole thread started because I
> suggested that throwing an error for ambiguous cases might be a better
> design in the long run, but apparently long term ease of code
> maintenance is far down our list of priorities ...

Not really.  But if you're going to shout down my proposal because
it's not in the spec, then it's a bit hard to see how you can argue
that we should implement your non-spec-compliant behavior instead,
especially at the cost of a painful backward compatibility break.  I
actually am 100% in agreement with you that allowing ambiguous
references to resolve either way is a recipe for bugs, bugs, and more
bugs.  But breaking people's code is not a better answer.  We still
have people on 8.2 because the pain of upgrading to 8.3 is more than
they can bear, and how many releases have we spent trying to get
standard_conforming_strings worked out?  I admit this probably
wouldn't be as bad, but we've managed to put out several releases in a
row now that are relatively painless to upgrade between, and I think
that's a trend we should try to keep going.

I am halfway tempted to say that we need to invent our own procedural
language that is designed not for compatibility with the SQL standard
or Oracle, but for non-crappiness.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

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

Darren Duncan <darren@darrenduncan.net> — 2011-04-08T03:41:49Z

Robert Haas wrote:
> I am halfway tempted to say that we need to invent our own procedural
> language that is designed not for compatibility with the SQL standard
> or Oracle, but for non-crappiness.

I'm way ahead of you on that one. -- Darren Duncan

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

Andrew Dunstan <andrew@dunslane.net> — 2011-04-08T14:50:19Z


On 04/07/2011 09:58 PM, Tom Lane wrote:
> Robert Haas<robertmhaas@gmail.com>  writes:
>> On Tue, Apr 5, 2011 at 5:52 PM, Andrew Dunstan<andrew@dunslane.net>  wrote:
>>> That doesn't mean we should arbitrarily break compatibility with pl/sql, nor
>>> that we should feel free to add on warts such as $varname that are
>>> completely at odds with the style of the rest of the language. That doesn't
>>> do anything except produce a mess.
>> Well, what it does is avoid breaking compatibility with previous
>> versions of PostgreSQL.  I think that actually does have some value.
>> Otherwise, we'd be folding to upper-case by default.
> Well, if we're going to consider 100% backwards compatibility a "must",
> then we should just stick with what the submitted patch does, ie,
> unqualified names are matched first to query columns, and to parameters
> only if there's no column match.  This is also per spec if I interpreted
> Peter's comments correctly.  The whole thread started because I
> suggested that throwing an error for ambiguous cases might be a better
> design in the long run, but apparently long term ease of code
> maintenance is far down our list of priorities ...
>
> 			

I think the discussion went off into the weeds somewhat, and I'm guilty 
of responding to suggestions that don't refer to the original subject.

For SQL language functions, I think you're right. The only caveat I have 
is that if your function name is very long, having to use it as a 
disambiguating qualifier can be a bit ugly.

cheers

andrew

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

Pavel Stehule <pavel.stehule@gmail.com> — 2011-04-08T14:53:27Z

Hello

>>
>> Well, if we're going to consider 100% backwards compatibility a "must",
>> then we should just stick with what the submitted patch does, ie,
>> unqualified names are matched first to query columns, and to parameters
>> only if there's no column match.  This is also per spec if I interpreted
>> Peter's comments correctly.  The whole thread started because I
>> suggested that throwing an error for ambiguous cases might be a better
>> design in the long run, but apparently long term ease of code
>> maintenance is far down our list of priorities ...
>>
>>
>
> I think the discussion went off into the weeds somewhat, and I'm guilty of
> responding to suggestions that don't refer to the original subject.
>
> For SQL language functions, I think you're right. The only caveat I have is
> that if your function name is very long, having to use it as a
> disambiguating qualifier can be a bit ugly.

same mechanism works well in plpgsql and nobody requested a some
special shortcut.

Regards

Pavel

>
> cheers
>
> andrew
>

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

Andrew Dunstan <andrew@dunslane.net> — 2011-04-08T15:05:36Z


On 04/08/2011 10:53 AM, Pavel Stehule wrote:
>> For SQL language functions, I think you're right. The only caveat I have is
>> that if your function name is very long, having to use it as a
>> disambiguating qualifier can be a bit ugly.
> same mechanism works well in plpgsql and nobody requested a some
> special shortcut.
>

I get annoyed by it there too, that's why I mentioned it :-)

cheers

andrew

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

Robert Haas <robertmhaas@gmail.com> — 2011-04-08T15:05:58Z

On Fri, Apr 8, 2011 at 10:53 AM, Pavel Stehule <pavel.stehule@gmail.com> wrote:
> same mechanism works well in plpgsql and nobody requested a some
> special shortcut.

I did.  That mechanism sucks.  But I think we're committed to doing
what the standard and/or Oracle do, so oh well.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

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

Andres Freund <andres@anarazel.de> — 2011-04-08T15:09:50Z

On Friday, April 08, 2011 04:53:27 PM Pavel Stehule wrote:
> same mechanism works well in plpgsql and nobody requested a some
> special shortcut.
Well, for one it sucks there as well. For another it has been introduced for 
quite some time and most people have introduced naming like p_param or v_param 
for parameternames.

That has not been the case for sql functions. So I find it way much more 
painfull there...

Andres

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

David Wheeler <david@kineticode.com> — 2011-04-08T15:51:43Z

On Apr 8, 2011, at 8:05 AM, Robert Haas wrote:

>> same mechanism works well in plpgsql and nobody requested a some
>> special shortcut.
> 
> I did.  That mechanism sucks.  But I think we're committed to doing
> what the standard and/or Oracle do, so oh well.

I think I've worked around that in PL/pgSQL using ALIAS…

David

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

Robert Haas <robertmhaas@gmail.com> — 2011-04-08T15:57:57Z

On Fri, Apr 8, 2011 at 11:51 AM, David E. Wheeler <david@kineticode.com> wrote:
> On Apr 8, 2011, at 8:05 AM, Robert Haas wrote:
>
>>> same mechanism works well in plpgsql and nobody requested a some
>>> special shortcut.
>>
>> I did.  That mechanism sucks.  But I think we're committed to doing
>> what the standard and/or Oracle do, so oh well.
>
> I think I've worked around that in PL/pgSQL using ALIAS…

I've worked around it, too, using various techniques.  That doesn't
mean it doesn't suck.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

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

Merlin Moncure <mmoncure@gmail.com> — 2011-04-08T16:06:09Z

On Thu, Apr 7, 2011 at 8:58 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Robert Haas <robertmhaas@gmail.com> writes:
>> On Tue, Apr 5, 2011 at 5:52 PM, Andrew Dunstan <andrew@dunslane.net> wrote:
>>> That doesn't mean we should arbitrarily break compatibility with pl/sql, nor
>>> that we should feel free to add on warts such as $varname that are
>>> completely at odds with the style of the rest of the language. That doesn't
>>> do anything except produce a mess.
>
>> Well, what it does is avoid breaking compatibility with previous
>> versions of PostgreSQL.  I think that actually does have some value.
>> Otherwise, we'd be folding to upper-case by default.
>
> Well, if we're going to consider 100% backwards compatibility a "must",
> then we should just stick with what the submitted patch does, ie,
> unqualified names are matched first to query columns, and to parameters
> only if there's no column match.  This is also per spec if I interpreted
> Peter's comments correctly.  The whole thread started because I
> suggested that throwing an error for ambiguous cases might be a better
> design in the long run, but apparently long term ease of code
> maintenance is far down our list of priorities ...

+1, as long as you are 100.0% sure this is not going to break any
existing code.  For example, what happens if the argument is named the
same as a table?

merlin

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

Tom Lane <tgl@sss.pgh.pa.us> — 2011-04-08T16:11:37Z

Merlin Moncure <mmoncure@gmail.com> writes:
> On Thu, Apr 7, 2011 at 8:58 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> Well, if we're going to consider 100% backwards compatibility a "must",
>> then we should just stick with what the submitted patch does, ie,
>> unqualified names are matched first to query columns, and to parameters
>> only if there's no column match. This is also per spec if I interpreted
>> Peter's comments correctly. The whole thread started because I
>> suggested that throwing an error for ambiguous cases might be a better
>> design in the long run, but apparently long term ease of code
>> maintenance is far down our list of priorities ...

> +1, as long as you are 100.0% sure this is not going to break any
> existing code.  For example, what happens if the argument is named the
> same as a table?

I was a bit sloppy in my statement above --- what the code is actually
doing (or should be doing) is matching to parameters only after the core
parser fails to find any match.  So "unqualified reference to whole-row"
would take precedence too.

			regards, tom lane

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

Josh Berkus <josh@agliodbs.com> — 2011-04-08T19:56:49Z

> But breaking people's code is not a better answer.  We still
> have people on 8.2 because the pain of upgrading to 8.3 is more than
> they can bear, and how many releases have we spent trying to get
> standard_conforming_strings worked out?  I admit this probably
> wouldn't be as bad, but we've managed to put out several releases in a
> row now that are relatively painless to upgrade between, and I think
> that's a trend we should try to keep going.

I guess I'm not understanding the backwards compatibility problem.  I've
looked up the thread, and I still don't see a real-world issue.  If we
(by default) throw an error on ambiguity, and have GUC to turn that off
(in which case, it resolves column-first), I really don't see what
problem anyone could have upgrading.

Can you explain it to me?

-- 
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com

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

Robert Haas <robertmhaas@gmail.com> — 2011-04-08T20:10:05Z

On Fri, Apr 8, 2011 at 3:56 PM, Josh Berkus <josh@agliodbs.com> wrote:
>> But breaking people's code is not a better answer.  We still
>> have people on 8.2 because the pain of upgrading to 8.3 is more than
>> they can bear, and how many releases have we spent trying to get
>> standard_conforming_strings worked out?  I admit this probably
>> wouldn't be as bad, but we've managed to put out several releases in a
>> row now that are relatively painless to upgrade between, and I think
>> that's a trend we should try to keep going.
>
> I guess I'm not understanding the backwards compatibility problem.  I've
> looked up the thread, and I still don't see a real-world issue.  If we
> (by default) throw an error on ambiguity, and have GUC to turn that off
> (in which case, it resolves column-first), I really don't see what
> problem anyone could have upgrading.
>
> Can you explain it to me?

Consider:

rhaas=# CREATE TABLE developer (id serial primary key, name text not null);
NOTICE:  CREATE TABLE will create implicit sequence "developer_id_seq"
for serial column "developer.id"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index
"developer_pkey" for table "developer"
CREATE TABLE
rhaas=# insert into developer (name) values ('Tom'), ('Bruce');
INSERT 0 2
rhaas=# CREATE OR REPLACE FUNCTION developer_lookup(id integer)
RETURNS text AS $$SELECT name FROM developer WHERE id = $1$$ LANGUAGE
sql STABLE;
CREATE FUNCTION
rhaas=# SELECT developer_lookup(1);
 developer_lookup
------------------
 Tom
(1 row)

Now, when this person attempts to recreate this function on a
hypothetical version of PostgreSQL that thinks "id" is ambiguous, it
doesn't work.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

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

Josh Berkus <josh@agliodbs.com> — 2011-04-08T20:32:58Z

> Now, when this person attempts to recreate this function on a
> hypothetical version of PostgreSQL that thinks "id" is ambiguous, it
> doesn't work.

Hence the GUC.   Where's the issue?

-- 
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com

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

Robert Haas <robertmhaas@gmail.com> — 2011-04-08T21:05:10Z

On Fri, Apr 8, 2011 at 4:32 PM, Josh Berkus <josh@agliodbs.com> wrote:
>> Now, when this person attempts to recreate this function on a
>> hypothetical version of PostgreSQL that thinks "id" is ambiguous, it
>> doesn't work.
>
> Hence the GUC.   Where's the issue?

Behavior-changing GUCs for this kind of thing cause a lot of problems.
 If you need one GUC setting for your application to work, and the
extension you have installed needs the other setting, you're screwed.
In the worst case, if a security-definer function is involved, you can
create a security hole, for example by convincing the system that id =
$1 is intended to mean $1 = $1, or some such.  You can of course
attach the GUC settings to each individual function, but that doesn't
really work either unless you do it for every function in the system.
The fundamental problem here is that GUCs are dynamically scoped,
while this problem is lexically scoped.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

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

Tom Lane <tgl@sss.pgh.pa.us> — 2011-04-08T23:30:26Z

Robert Haas <robertmhaas@gmail.com> writes:
> On Fri, Apr 8, 2011 at 4:32 PM, Josh Berkus <josh@agliodbs.com> wrote:
>> Hence the GUC.  Where's the issue?

> Behavior-changing GUCs for this kind of thing cause a lot of problems.
>  If you need one GUC setting for your application to work, and the
> extension you have installed needs the other setting, you're screwed.
> In the worst case, if a security-definer function is involved, you can
> create a security hole, for example by convincing the system that id =
> $1 is intended to mean $1 = $1, or some such.  You can of course
> attach the GUC settings to each individual function, but that doesn't
> really work either unless you do it for every function in the system.
> The fundamental problem here is that GUCs are dynamically scoped,
> while this problem is lexically scoped.

Yeah.  In the plpgsql case, we did make provisions to control the
behavior per-function.  In principle we could do the same for SQL
functions, but it'd be rather a PITA I think.  (In particular, the "easy
way out" of attaching SET clauses to the functions would be a bad idea
because it would defeat inlining.)

			regards, tom lane

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

Pavel Stehule <pavel.stehule@gmail.com> — 2011-04-09T03:19:45Z

2011/4/9 Tom Lane <tgl@sss.pgh.pa.us>:
> Robert Haas <robertmhaas@gmail.com> writes:
>> On Fri, Apr 8, 2011 at 4:32 PM, Josh Berkus <josh@agliodbs.com> wrote:
>>> Hence the GUC.   Where's the issue?
>
>> Behavior-changing GUCs for this kind of thing cause a lot of problems.
>>  If you need one GUC setting for your application to work, and the
>> extension you have installed needs the other setting, you're screwed.
>> In the worst case, if a security-definer function is involved, you can
>> create a security hole, for example by convincing the system that id =
>> $1 is intended to mean $1 = $1, or some such.  You can of course
>> attach the GUC settings to each individual function, but that doesn't
>> really work either unless you do it for every function in the system.
>> The fundamental problem here is that GUCs are dynamically scoped,
>> while this problem is lexically scoped.
>
> Yeah.  In the plpgsql case, we did make provisions to control the
> behavior per-function.  In principle we could do the same for SQL
> functions, but it'd be rather a PITA I think.  (In particular, the "easy
> way out" of attaching SET clauses to the functions would be a bad idea
> because it would defeat inlining.)

what about a new language like SQLc? - like SQL compatibility.
pg_upgrade can move old code into this compatibility language when
detect some posible problems.

Pavel

>
>                        regards, tom lane
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>

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

Dimitri Fontaine <dimitri@2ndquadrant.fr> — 2011-04-14T18:37:47Z

Robert Haas <robertmhaas@gmail.com> writes:
> rhaas=# CREATE OR REPLACE FUNCTION developer_lookup(id integer)
> RETURNS text AS $$SELECT name FROM developer WHERE id = $1$$ LANGUAGE
> sql STABLE;
>
> Now, when this person attempts to recreate this function on a
> hypothetical version of PostgreSQL that thinks "id" is ambiguous, it
> doesn't work.

Unless we make it so that no such version ever exists.  Meaning that the
code works fine as is or using WHERE id = developer_lookup.id.  AS id
can't ever be the parameter in this case, you're just fine.

Bearing in mind that $1 etc shortcuts still are available, I don't
really see this qualification of parameter names with function names so
big a problem that we should find a way to avoid it and risk breaking
compatibility.

Don't forget that any ambiguity here will mean *huge* migration costs.

Regards,
-- 
Dimitri Fontaine
http://2ndQuadrant.fr     PostgreSQL : Expertise, Formation et Support

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

Robert Haas <robertmhaas@gmail.com> — 2011-04-14T18:54:39Z

On Thu, Apr 14, 2011 at 11:37 AM, Dimitri Fontaine
<dimitri@2ndquadrant.fr> wrote:
> Robert Haas <robertmhaas@gmail.com> writes:
>> rhaas=# CREATE OR REPLACE FUNCTION developer_lookup(id integer)
>> RETURNS text AS $$SELECT name FROM developer WHERE id = $1$$ LANGUAGE
>> sql STABLE;
>>
>> Now, when this person attempts to recreate this function on a
>> hypothetical version of PostgreSQL that thinks "id" is ambiguous, it
>> doesn't work.
>
> Unless we make it so that no such version ever exists.  Meaning that the
> code works fine as is or using WHERE id = developer_lookup.id.  AS id
> can't ever be the parameter in this case, you're just fine.
>
> Bearing in mind that $1 etc shortcuts still are available, I don't
> really see this qualification of parameter names with function names so
> big a problem that we should find a way to avoid it and risk breaking
> compatibility.
>
> Don't forget that any ambiguity here will mean *huge* migration costs.

If I'm reading your email correctly, we're in agreement.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

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

Tom Lane <tgl@sss.pgh.pa.us> — 2011-04-14T20:55:46Z

Robert Haas <robertmhaas@gmail.com> writes:
> On Thu, Apr 14, 2011 at 11:37 AM, Dimitri Fontaine
> <dimitri@2ndquadrant.fr> wrote:
>> Unless we make it so that no such version ever exists. Meaning that the
>> code works fine as is or using WHERE id = developer_lookup.id. AS id
>> can't ever be the parameter in this case, you're just fine.
>> 
>> Bearing in mind that $1 etc shortcuts still are available, I don't
>> really see this qualification of parameter names with function names so
>> big a problem that we should find a way to avoid it and risk breaking
>> compatibility.
>> 
>> Don't forget that any ambiguity here will mean *huge* migration costs.

> If I'm reading your email correctly, we're in agreement.

Hmm, what I read Dimitri to be proposing is that we *require* parameter
names to be qualified with the function name.  I don't recall hearing
that before.  It would solve the problem perhaps, but I think the moans
and groans will be numerous.

			regards, tom lane

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

Robert Haas <robertmhaas@gmail.com> — 2011-04-14T21:10:11Z

On Thu, Apr 14, 2011 at 1:55 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Robert Haas <robertmhaas@gmail.com> writes:
>> On Thu, Apr 14, 2011 at 11:37 AM, Dimitri Fontaine
>> <dimitri@2ndquadrant.fr> wrote:
>>> Unless we make it so that no such version ever exists.  Meaning that the
>>> code works fine as is or using WHERE id = developer_lookup.id.  AS id
>>> can't ever be the parameter in this case, you're just fine.
>>>
>>> Bearing in mind that $1 etc shortcuts still are available, I don't
>>> really see this qualification of parameter names with function names so
>>> big a problem that we should find a way to avoid it and risk breaking
>>> compatibility.
>>>
>>> Don't forget that any ambiguity here will mean *huge* migration costs.
>
>> If I'm reading your email correctly, we're in agreement.
>
> Hmm, what I read Dimitri to be proposing is that we *require* parameter
> names to be qualified with the function name.  I don't recall hearing
> that before.  It would solve the problem perhaps, but I think the moans
> and groans will be numerous.

So far the most promising proposal I've seen seems to be to let id
mean the parameter called id only when it can't refer to anything in
the query.

Tabula raza, I'd prefer your proposal to make any ambiguity an error,
but it's not worth the breakage.  I'd be fine with having a way to
explicitly request that behavior though, a la Perl's "use strict".

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

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

Tom Lane <tgl@sss.pgh.pa.us> — 2011-04-14T21:16:24Z

Robert Haas <robertmhaas@gmail.com> writes:
> On Thu, Apr 14, 2011 at 1:55 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> Hmm, what I read Dimitri to be proposing is that we *require* parameter
>> names to be qualified with the function name. I don't recall hearing
>> that before. It would solve the problem perhaps, but I think the moans
>> and groans will be numerous.

> So far the most promising proposal I've seen seems to be to let id
> mean the parameter called id only when it can't refer to anything in
> the query.

> Tabula raza, I'd prefer your proposal to make any ambiguity an error,
> but it's not worth the breakage.

Yeah, I've come round to that position too.  I think allowing parameter
names to be checked only after query names is probably the best answer.

> I'd be fine with having a way to
> explicitly request that behavior though, a la Perl's "use strict".

This is possible but it's not clear it's worth the work.

			regards, tom lane

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

Kevin Grittner <kevin.grittner@wicourts.gov> — 2011-04-14T21:20:32Z

Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Robert Haas <robertmhaas@gmail.com> writes:
 
>> So far the most promising proposal I've seen seems to be to let
>> id mean the parameter called id only when it can't refer to
>> anything in the query.
 
> Yeah, I've come round to that position too.  I think allowing
> parameter names to be checked only after query names is probably
> the best answer.
 
+1
 
That seems the most useful and least surprising approach to me.
 
-Kevin

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

Jim Nasby <jim@nasby.net> — 2011-04-15T02:07:19Z

On Apr 14, 2011, at 4:20 PM, Kevin Grittner wrote:
> Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> Robert Haas <robertmhaas@gmail.com> writes:
> 
>>> So far the most promising proposal I've seen seems to be to let
>>> id mean the parameter called id only when it can't refer to
>>> anything in the query.
> 
>> Yeah, I've come round to that position too.  I think allowing
>> parameter names to be checked only after query names is probably
>> the best answer.
> 
> +1
> 
> That seems the most useful and least surprising approach to me.

As part of this, can we also allow specifying an alias for the function name? That would make it far less onerous to disambiguate parameters. Unfortunately we obviously couldn't use AS as the keyword for this alias; maybe we could use ALIAS instead? IE:

CREATE FUNCTION function_with_really_really_descriptive_name (
 some_parameter int
) RETURNS int LANGUAGE SQL ALIAS fwrrdn AS $$
	SELECT fwrrdn.some_parameter
$$;
--
Jim C. Nasby, Database Architect                   jim@nasby.net
512.569.9461 (cell)                         http://jim.nasby.net

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

Pavel Stehule <pavel.stehule@gmail.com> — 2011-04-15T02:56:10Z

2011/4/15 Jim Nasby <jim@nasby.net>:
> On Apr 14, 2011, at 4:20 PM, Kevin Grittner wrote:
>> Tom Lane <tgl@sss.pgh.pa.us> wrote:
>>> Robert Haas <robertmhaas@gmail.com> writes:
>>
>>>> So far the most promising proposal I've seen seems to be to let
>>>> id mean the parameter called id only when it can't refer to
>>>> anything in the query.
>>
>>> Yeah, I've come round to that position too.  I think allowing
>>> parameter names to be checked only after query names is probably
>>> the best answer.
>>
>> +1
>>
>> That seems the most useful and least surprising approach to me.
>
> As part of this, can we also allow specifying an alias for the function name? That would make it far less onerous to disambiguate parameters. Unfortunately we obviously couldn't use AS as the keyword for this alias; maybe we could use ALIAS instead? IE:
>
> CREATE FUNCTION function_with_really_really_descriptive_name (
>  some_parameter int
> ) RETURNS int LANGUAGE SQL ALIAS fwrrdn AS $$
>        SELECT fwrrdn.some_parameter
> $$;
> --

I see this can be problem for other languages - mainly for PLpgSQL.
There should be aliases supported too. And this small feature can be
terible when somebody will try to port your code to other platforms.
Personally I am thinking, so it isn't necessary

-1

Regards

Pavel Stehule

> Jim C. Nasby, Database Architect                   jim@nasby.net
> 512.569.9461 (cell)                         http://jim.nasby.net
>
>
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>

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

Dimitri Fontaine <dimitri@2ndquadrant.fr> — 2011-04-15T08:32:52Z

(oops this mail never reached out, it seems, resending)

"Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes:
> Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> Yeah, I've come round to that position too.  I think allowing
>> parameter names to be checked only after query names is probably
>> the best answer.
>  
> That seems the most useful and least surprising approach to me.

Allow me to disagree.  I don't want to recheck all my SQL functions
whenever I ALTER TABLE … ADD|DROP|RENAME COLUMN …;

We don't even have a nice way to list functions dependencies towards
objects that are part of their body to help me out here, or do we?

Regards,
-- 
Dimitri Fontaine
http://2ndQuadrant.fr     PostgreSQL : Expertise, Formation et Support

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

Robert Haas <robertmhaas@gmail.com> — 2011-04-18T12:33:06Z

On Thu, Apr 14, 2011 at 10:56 PM, Pavel Stehule <pavel.stehule@gmail.com> wrote:
> 2011/4/15 Jim Nasby <jim@nasby.net>:
>> On Apr 14, 2011, at 4:20 PM, Kevin Grittner wrote:
>>> Tom Lane <tgl@sss.pgh.pa.us> wrote:
>>>> Robert Haas <robertmhaas@gmail.com> writes:
>>>
>>>>> So far the most promising proposal I've seen seems to be to let
>>>>> id mean the parameter called id only when it can't refer to
>>>>> anything in the query.
>>>
>>>> Yeah, I've come round to that position too.  I think allowing
>>>> parameter names to be checked only after query names is probably
>>>> the best answer.
>>>
>>> +1
>>>
>>> That seems the most useful and least surprising approach to me.
>>
>> As part of this, can we also allow specifying an alias for the function name? That would make it far less onerous to disambiguate parameters. Unfortunately we obviously couldn't use AS as the keyword for this alias; maybe we could use ALIAS instead? IE:
>>
>> CREATE FUNCTION function_with_really_really_descriptive_name (
>>  some_parameter int
>> ) RETURNS int LANGUAGE SQL ALIAS fwrrdn AS $$
>>        SELECT fwrrdn.some_parameter
>> $$;
>> --
>
> I see this can be problem for other languages - mainly for PLpgSQL.
> There should be aliases supported too. And this small feature can be
> terible when somebody will try to port your code to other platforms.
> Personally I am thinking, so it isn't necessary
>
> -1

I don't much like Jim's syntax suggestion (the alias really ought to
be declared within the function body, I think, not added to the CREATE
FUNCTION statement) but I don't necessarily think it's a bad idea.
What would be even better, in my view, is having a short alias that is
defined by default, but all previous proposals in this vein have been
shot down by Tom and Andrew.  As a practical matter, though, I think
what Jim is talking about speaks to a real need - people want to make
SQL function names long and descriptive, but they do NOT want to spell
out that long function name 16 times inside the function body.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

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

Alvaro Herrera <alvherre@commandprompt.com> — 2011-04-18T16:33:13Z

Excerpts from Robert Haas's message of lun abr 18 09:33:06 -0300 2011:

> I don't much like Jim's syntax suggestion (the alias really ought to
> be declared within the function body, I think, not added to the CREATE
> FUNCTION statement) but I don't necessarily think it's a bad idea.
> What would be even better, in my view, is having a short alias that is
> defined by default, but all previous proposals in this vein have been
> shot down by Tom and Andrew.  As a practical matter, though, I think
> what Jim is talking about speaks to a real need - people want to make
> SQL function names long and descriptive, but they do NOT want to spell
> out that long function name 16 times inside the function body.

plpgsql has the #option thing in functions; why can't we have something
similar in SQL functions?

 CREATE FUNCTION function_with_really_really_descriptive_name (
  some_parameter int
 ) RETURNS int LANGUAGE SQL AS $$
     #option function_alias fwrrdn
        SELECT fwrrdn.some_parameter
 $$;

Not necessarily that exact syntax.

-- 
Álvaro Herrera <alvherre@commandprompt.com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

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

Merlin Moncure <mmoncure@gmail.com> — 2011-04-18T16:51:12Z

On Mon, Apr 18, 2011 at 11:33 AM, Alvaro Herrera
<alvherre@commandprompt.com> wrote:
> Excerpts from Robert Haas's message of lun abr 18 09:33:06 -0300 2011:
>
>> I don't much like Jim's syntax suggestion (the alias really ought to
>> be declared within the function body, I think, not added to the CREATE
>> FUNCTION statement) but I don't necessarily think it's a bad idea.
>> What would be even better, in my view, is having a short alias that is
>> defined by default, but all previous proposals in this vein have been
>> shot down by Tom and Andrew.  As a practical matter, though, I think
>> what Jim is talking about speaks to a real need - people want to make
>> SQL function names long and descriptive, but they do NOT want to spell
>> out that long function name 16 times inside the function body.
>
> plpgsql has the #option thing in functions; why can't we have something
> similar in SQL functions?
>
>  CREATE FUNCTION function_with_really_really_descriptive_name (
>   some_parameter int
>  ) RETURNS int LANGUAGE SQL AS $$
>     #option function_alias fwrrdn
>         SELECT fwrrdn.some_parameter
>  $$;
>
> Not necessarily that exact syntax.

If we are rejecting $foo on grounds of deviating from sql standard,
shouldn't this be rejected on the same grounds?  There is no such
syntax in sql/psm.

merlin