Обсуждение: named parameters in SQL functions
At Tom's suggestion I am looking at allowing use of parameter names in SQL functions instead of requiring use of $1 etc. That raises the question of how we would disambiguate a parameter name from a column name. Essentially, ISTM, we could use some special marker such as @ (c.f. SQL Server) or : (c.f. ecpg) or else we could have some rule that says which name takes precedence. I think I prefer a special marker, other things being equal. Is there a standard on this? cheers andrew
Andrew Dunstan wrote: > > At Tom's suggestion I am looking at allowing use of parameter names in > SQL functions instead of requiring use of $1 etc. That raises the > question of how we would disambiguate a parameter name from a column > name. Essentially, ISTM, we could use some special marker such as @ > (c.f. SQL Server) or : (c.f. ecpg) or else we could have some rule that > says which name takes precedence. I think I prefer a special marker, > other things being equal. Is there a standard on this? > I like the special marker idea. A '$' would be nice because its already in use for similar purposes, but I think that would lead to ambiguity with dollar quoting. Would this be limited to sql functions? I only ask because for non-sql functions we currently prefix parameter names with an underscore, but a built-in special marker would be much more desirable. -- Andrew Chernow eSilo, LLC every bit counts http://www.esilo.com/
2009/11/15 Andrew Dunstan <andrew@dunslane.net>: > > At Tom's suggestion I am looking at allowing use of parameter names in SQL > functions instead of requiring use of $1 etc. That raises the question of > how we would disambiguate a parameter name from a column name. Essentially, > ISTM, we could use some special marker such as @ (c.f. SQL Server) or : > (c.f. ecpg) or else we could have some rule that says which name takes > precedence. I think I prefer a special marker, other things being equal. Is > there a standard on this? what about $name ? Personally I prefer :name, but this colidates with psql local variables :( Pavel > > cheers > > andrew > > -- > Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-hackers >
2009/11/15 Andrew Chernow <ac@esilo.com>: > Andrew Dunstan wrote: >> >> At Tom's suggestion I am looking at allowing use of parameter names in SQL >> functions instead of requiring use of $1 etc. That raises the question of >> how we would disambiguate a parameter name from a column name. Essentially, >> ISTM, we could use some special marker such as @ (c.f. SQL Server) or : >> (c.f. ecpg) or else we could have some rule that says which name takes >> precedence. I think I prefer a special marker, other things being equal. Is >> there a standard on this? >> > > I like the special marker idea. A '$' would be nice because its already in > use for similar purposes, but I think that would lead to ambiguity with > dollar quoting. no, it should be safe (if you don't use for dollar quoting some like $variablename$) Pavel > > Would this be limited to sql functions? I only ask because for non-sql > functions we currently prefix parameter names with an underscore, but a > built-in special marker would be much more desirable. > > -- > Andrew Chernow > eSilo, LLC > every bit counts > http://www.esilo.com/ > > -- > Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-hackers >
On Sun, Nov 15, 2009 at 12:37 PM, Andrew Dunstan <andrew@dunslane.net> wrote: > At Tom's suggestion I am looking at allowing use of parameter names in SQL > functions instead of requiring use of $1 etc. That raises the question of > how we would disambiguate a parameter name from a column name. Essentially, > ISTM, we could use some special marker such as @ (c.f. SQL Server) or : > (c.f. ecpg) or else we could have some rule that says which name takes > precedence. I think I prefer a special marker, other things being equal. Is > there a standard on this? We could also just throw an error if there is any ambiguity. I kind of like the idea of a special marker for both SQL and PL/pgsql, but Tom has been negative on that idea in the past. ...Robert
On Sun, Nov 15, 2009 at 5:49 PM, Andrew Chernow <ac@esilo.com> wrote: > Andrew Dunstan wrote: >> >> At Tom's suggestion I am looking at allowing use of parameter names in SQL >> functions instead of requiring use of $1 etc. That raises the question of >> how we would disambiguate a parameter name from a column name. Essentially, >> ISTM, we could use some special marker such as @ (c.f. SQL Server) or : >> (c.f. ecpg) or else we could have some rule that says which name takes >> precedence. I think I prefer a special marker, other things being equal. Is >> there a standard on this? >> > > I like the special marker idea. A '$' would be nice because its already in > use for similar purposes, but I think that would lead to ambiguity with > dollar quoting. I think that would be a big break with everything else and very non-sql-ish. We don't use these in plpgsql and we don't use them anywhere else in sql. Moreover you would still have conflicts possible because sql can quote identifiers so people can have columns named "$foo". You would have a weird syntactic detail where "$foo" would mean something different than $foo even though they're both valid identifiers. I'm not sure it wouldn't conflict with some drivers either. DBI uses :foo and ? but I have a vague recollection some drivers did use $foo. -- greg
On Nov 15, 2009, at 10:19 AM, Greg Stark wrote: >> I like the special marker idea. A '$' would be nice because its already in >> use for similar purposes, but I think that would lead to ambiguity with >> dollar quoting. > > I think that would be a big break with everything else and very > non-sql-ish. We don't use these in plpgsql and we don't use them > anywhere else in sql. *ahem* $1 *ahem* > Moreover you would still have conflicts possible because sql can quote > identifiers so people can have columns named "$foo". You would have a > weird syntactic detail where "$foo" would mean something different > than $foo even though they're both valid identifiers. Same with Foo and "Foo", no? > I'm not sure it wouldn't conflict with some drivers either. DBI uses > :foo and ? but I have a vague recollection some drivers did use $foo. I don't think that would come up, because the $vars are in the body of the function, not in a typical driver call. Personally, I like $var, but @var would be okay, and @@var is acceptable. But I'm JAPH, so my biases should be obvious. Best, David
2009/11/15 David E. Wheeler <david@kineticode.com>: > On Nov 15, 2009, at 10:19 AM, Greg Stark wrote: > >>> I like the special marker idea. A '$' would be nice because its already in >>> use for similar purposes, but I think that would lead to ambiguity with >>> dollar quoting. >> >> I think that would be a big break with everything else and very >> non-sql-ish. We don't use these in plpgsql and we don't use them >> anywhere else in sql. > > *ahem* $1 *ahem* > >> Moreover you would still have conflicts possible because sql can quote >> identifiers so people can have columns named "$foo". You would have a >> weird syntactic detail where "$foo" would mean something different >> than $foo even though they're both valid identifiers. > > Same with Foo and "Foo", no? > >> I'm not sure it wouldn't conflict with some drivers either. DBI uses >> :foo and ? but I have a vague recollection some drivers did use $foo. > > I don't think that would come up, because the $vars are in the body of the function, not in a typical driver call. > > Personally, I like $var, but @var would be okay, and @@var is acceptable. But I'm JAPH, so my biases should be obvious. @var or @@var should be a break for people from MySQL. @var are r/w in MySQL and @@var are global in T-SQL. So people could be confused. Regards Pavel > > Best, > > David > -- > Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-hackers >
On Sun, Nov 15, 2009 at 6:42 PM, Pavel Stehule <pavel.stehule@gmail.com> wrote: >> Personally, I like $var, but @var would be okay, and @@var is acceptable. But I'm JAPH, so my biases should be obvious. > > @var or @@var should be a break for people from MySQL. @var are r/w in > MySQL and @@var are global in T-SQL. So people could be confused. Besides, do we think MySQL and T-SQL are the heights of good language design? -- greg
On Sun, Nov 15, 2009 at 6:26 PM, David E. Wheeler <david@kineticode.com> wrote: > Personally, I like $var, but @var would be okay, and @@var is acceptable. But I'm JAPH, so my biases should be obvious. I'm japh too -- but that doesn't mean grabbing one little aesthetic from Perl without copying the whole concept behind it makes any sense. Perl sigils are an important part of the language and are a basic part of the syntax. They aren't just a "this is a variable" marker. Dropping one use of them into a language that doesn't use them anywhere else just makes the language into a mishmash. I don't see any purpose to using such markers anyways. We have a parser, we have a symbol table, we should use them; these identifiers are just like other identifiers. -- greg
2009/11/16 Andrew Dunstan <andrew@dunslane.net>: > At Tom's suggestion I am looking at allowing use of parameter names in SQL > functions instead of requiring use of $1 etc. That raises the question of > how we would disambiguate a parameter name from a column name. Essentially, > ISTM, we could use some special marker such as @ (c.f. SQL Server) or : > (c.f. ecpg) or else we could have some rule that says which name takes > precedence. I think I prefer a special marker, other things being equal. Is > there a standard on this? Sorry if I'm missing something important here, but why not just resolve the parameter names in whatever way PL/PgSQL has been doing it? It seems to work well. FWIW I always prefix my parameter names with _ to differentiate them from columns. Cheers, BJ
On Nov 15, 2009, at 10:54 AM, Greg Stark wrote: > I'm japh too -- but that doesn't mean grabbing one little aesthetic > from Perl without copying the whole concept behind it makes any sense. > Perl sigils are an important part of the language and are a basic part > of the syntax. They aren't just a "this is a variable" marker. > Dropping one use of them into a language that doesn't use them > anywhere else just makes the language into a mishmash. Well, no, just because we're talking about adopting $var doesn't mean we're trying to turn SQL or PL/pgSQL into Perl. Itmeans that we want to signify that a token is a variable, as opposed to something else (hence “sigil”). That doesn't makeit a mishmash unless you think you suddenly have Perl (or shell) semantics, which would be a pretty weird expectation. > I don't see any purpose to using such markers anyways. We have a > parser, we have a symbol table, we should use them; these identifiers > are just like other identifiers. See the discussion of conflicts with column names in the recent thread. A sigil would eliminate that problem -- and we alreadyhave $1 and friends, so this is just an extension of that in my view. Best, David
2009/11/15 Greg Stark <gsstark@mit.edu>: > On Sun, Nov 15, 2009 at 6:42 PM, Pavel Stehule <pavel.stehule@gmail.com> wrote: >>> Personally, I like $var, but @var would be okay, and @@var is acceptable. But I'm JAPH, so my biases should be obvious. >> >> @var or @@var should be a break for people from MySQL. @var are r/w in >> MySQL and @@var are global in T-SQL. So people could be confused. > > Besides, do we think MySQL and T-SQL are the heights of good language design? > sure no. But same arguments against to :var should be used to @var. pgscript use it. I don't know the best semantic. But I am not happy from this proposals. I don't see any consistency. Pavel > > -- > greg >
>> I like the special marker idea. A '$' would be nice because its already in >> use for similar purposes, but I think that would lead to ambiguity with >> dollar quoting. > > no, it should be safe (if you don't use for dollar quoting some like > $variablename$) > Actually, I was thinking of something like $abc$def, where abc and def are variables. Although, this is much less likely than column name conflicts. Other possibles are: $(var), @var@, or %var%. I'd perfer a single character marker but that may not fly. -- Andrew Chernow eSilo, LLC every bit counts http://www.esilo.com/
2009/11/15 Andrew Chernow <ac@esilo.com>: > >>> I like the special marker idea. A '$' would be nice because its already >>> in >>> use for similar purposes, but I think that would lead to ambiguity with >>> dollar quoting. >> >> no, it should be safe (if you don't use for dollar quoting some like >> $variablename$) >> > > Actually, I was thinking of something like $abc$def, where abc and def are > variables. Although, this is much less likely than column name conflicts. > > Other possibles are: $(var), @var@, or %var%. I'd perfer a single character > marker but that may not fly. > single character is my preference too. Pavel > -- > Andrew Chernow > eSilo, LLC > every bit counts > http://www.esilo.com/ >
On Sun, Nov 15, 2009 at 6:26 PM, David E. Wheeler <david@kineticode.com> wrote: >> Moreover you would still have conflicts possible because sql can quote >> identifiers so people can have columns named "$foo". You would have a >> weird syntactic detail where "$foo" would mean something different >> than $foo even though they're both valid identifiers. > > Same with Foo and "Foo", no? No, that's not the same. The point is that $ is a perfectly valid SQL identifier character and $foo is a perfectly valid identifier. You can always quote any identifier (yes, after case smashing) so you would expect if $foo is a valid identifier then "$foo" would refer to the same identifier. You're introducing a meaning for $foo but saying there's no valid way to quote the identifier to get the same thing. And worse, if you do quote it you get something else entirely different. -- greg
On Nov 15, 2009, at 11:21 AM, Greg Stark wrote: > No, that's not the same. > > The point is that $ is a perfectly valid SQL identifier character and > $foo is a perfectly valid identifier. You can always quote any > identifier (yes, after case smashing) so you would expect if $foo is a > valid identifier then "$foo" would refer to the same identifier. > You're introducing a meaning for $foo but saying there's no valid way > to quote the identifier to get the same thing. And worse, if you do > quote it you get something else entirely different. $foo should be killed off as a valid identifier, IMNSHO. But failing that, some other sigil would be most welcome. Best, David
On Sun, Nov 15, 2009 at 7:25 PM, David E. Wheeler <david@kineticode.com> wrote: > On Nov 15, 2009, at 11:21 AM, Greg Stark wrote: > > > $foo should be killed off as a valid identifier, IMNSHO. > > But failing that, some other sigil would be most welcome. I don't think SQL is the height of language design either. But trying to turn it into another language piece by piece is not gong to make it any nicer. A sigil here doesn't accomplish anything. The identifiers in question are *just* like other identifiers. They can be used in expressions just like other columns, they have various types, they have the same syntax as other columns, the sigil doesn't mean anything. I think what may be making this tempting is that they look vaguely like ODBC/JDBC/DBI placeholders like :foo. However they're very very different. In those cases the sigil is marking the sigil outside the SQL syntax. They will be replaced textually without parsing the SQL at all. It's actually very confusing having $foo indicate something within SQL since it makes it look like it's some external thing from another layer like the placeholders. -- greg
David E. Wheeler wrote: > > $foo should be killed off as a valid identifier, IMNSHO. > > > It's only legal when quoted. Unquoted indetifiers can't begin with $. see scan.l: ident_start [A-Za-z\200-\377_] ident_cont [A-Za-z\200-\377_0-9\$] identifier {ident_start}{ident_cont}* cheers andrew
On Nov 15, 2009, at 11:35 AM, Greg Stark wrote: > I don't think SQL is the height of language design either. But trying > to turn it into another language piece by piece is not gong to make it > any nicer. I don't know of anyone suggesting such a thing. > A sigil here doesn't accomplish anything. The identifiers in question > are *just* like other identifiers. They can be used in expressions > just like other columns, they have various types, they have the same > syntax as other columns, the sigil doesn't mean anything. So what is the $ for in $1, $2, etc.? > I think what may be making this tempting is that they look vaguely > like ODBC/JDBC/DBI placeholders like :foo. However they're very very > different. In those cases the sigil is marking the sigil outside the > SQL syntax. They will be replaced textually without parsing the SQL at > all. It's actually very confusing having $foo indicate something > within SQL since it makes it look like it's some external thing from > another layer like the placeholders. It's not in SQL; it's in SQL functions (and DO blocks). AFAIK, the major database vendors all use some sort of characterto identify variables within functions. It's proven, avoids conflicts (you can't have an identifier named $foo,as Andrew just pointed out), and just generally makes maintenance easier. Best, David
> The point is that $ is a perfectly valid SQL identifier character and > $foo is a perfectly valid identifier. You can always quote any > identifier (yes, after case smashing) so you would expect if $foo is a > valid identifier then "$foo" would refer to the same identifier. > This case already exists via $1 and "$1". Making '$' a marker for parameters wouldn't introduce it. -- Andrew Chernow eSilo, LLC every bit counts http://www.esilo.com/
On Sun, Nov 15, 2009 at 7:56 PM, Andrew Chernow <ac@esilo.com> wrote: >> The point is that $ is a perfectly valid SQL identifier character and >> $foo is a perfectly valid identifier. You can always quote any >> identifier (yes, after case smashing) so you would expect if $foo is a >> valid identifier then "$foo" would refer to the same identifier. >> > > This case already exists via $1 and "$1". Making '$' a marker for > parameters wouldn't introduce it. True, $1 etc were already very non-sqlish, but that doesn't mean we have to compound things. So here are some examples where you can see what having this wart would introduce: 1) Error messages which mention column names are supposed to quote the column name to set it apart from the error string. This also guarantees that weird column names are referenced correctly as "foo bar" or "$foo" so the reference in the error string is unambiguous and can be pasted into queries. This won't work for $foo which would have to be embedded in the error text without quotes. 2) What would the default names for columns be if you did something like create function f(foo) as 'select $foo' If I then use this in another function create function g(foo) as 'select "$foo"+$foo from f()' I have to quote the column? The point here is that these sigils will leak out, they don't mean much to begin with except to indicate that this identifier is immune to the regular scoping rules but things get more confusing when they leak out and they start appearing in places that are subject to the regular scoping rules. 3) If I have a report generator which takes a list of columns to include in the report, or an ORM which tries to generate queries the usual way to write such things is to just routinely quote every identifier. This is less error-prone and simpler to code than trying to identify which identifiers need quoting and which don't. However in if the query is then dropped into a function the ORM or query generator would have to know which columns cannot be quoted based on syntactic information it can't really deduce. -- greg
On Nov 15, 2009, at 12:09 PM, Greg Stark wrote: > 1) Error messages which mention column names are supposed to quote the > column name to set it apart from the error string. This also > guarantees that weird column names are referenced correctly as "foo > bar" or "$foo" so the reference in the error string is unambiguous and > can be pasted into queries. This won't work for $foo which would have > to be embedded in the error text without quotes. What? You can't have a column named "$foo" without the quotes. > 2) What would the default names for columns be if you did something like > > create function f(foo) as 'select $foo' It would be "f" (without the quotes), just like now: try=# create function f(int) RETURNS int as 'SELECT $1' LANGUAGE sql; CREATE FUNCTION try=# select f(1);f ---1 (1 row) > If I then use this in another function > > create function g(foo) as 'select "$foo"+$foo from f()' > > I have to quote the column? No, that's a syntax error. It would be `SELECT f + $foo from f();` > 3) If I have a report generator which takes a list of columns to > include in the report, or an ORM which tries to generate queries the > usual way to write such things is to just routinely quote every > identifier. This is less error-prone and simpler to code than trying > to identify which identifiers need quoting and which don't. However in > if the query is then dropped into a function the ORM or query > generator would have to know which columns cannot be quoted based on > syntactic information it can't really deduce. You already have to quote everything, because $foo isn't a valid column name. And functions use the function name as thedefault column name, not a variable name. The same is true of set-returning functions, BTW: try=# create function b(int) RETURNS setof int as 'values ($1), ($1)' LANGUAGE sql; CREATE FUNCTION try=# select b(1);b ---11 (2 rows) So there is no leaking out. The variables are scoped within the function. Best, David
Andrew Dunstan <andrew@dunslane.net> writes: > At Tom's suggestion I am looking at allowing use of parameter names in > SQL functions instead of requiring use of $1 etc. That raises the > question of how we would disambiguate a parameter name from a column > name. Throw error if ambiguous. We already resolved this in the context of plpgsql. regards, tom lane
Tom Lane wrote: > Andrew Dunstan <andrew@dunslane.net> writes: > >> At Tom's suggestion I am looking at allowing use of parameter names in >> SQL functions instead of requiring use of $1 etc. That raises the >> question of how we would disambiguate a parameter name from a column >> name. >> > > Throw error if ambiguous. We already resolved this in the context of > plpgsql. > > > Well, if the funcname.varname gadget will work, as you suggest elsewhere it could, I think that would suffice. I had assumed that was just something in the plpgsql engine. cheers andrew
On Sun, Nov 15, 2009 at 8:22 PM, Andrew Dunstan <andrew@dunslane.net> wrote: > Tom Lane wrote: >> >> Andrew Dunstan <andrew@dunslane.net> writes: >> >>> >>> At Tom's suggestion I am looking at allowing use of parameter names in >>> SQL functions instead of requiring use of $1 etc. That raises the question >>> of how we would disambiguate a parameter name from a column name. >>> >> >> Throw error if ambiguous. We already resolved this in the context of >> plpgsql. >> >> >> > > Well, if the funcname.varname gadget will work, as you suggest elsewhere it > could, I think that would suffice. I had assumed that was just something in > the plpgsql engine. That gadget isn't horribly convenient for me since my function names tend to be 30 or 40 characters long. I wish we had something shorter, and maybe constant. But I guess that's a topic for a separate (inevitably rejected) patch. ...Robert
Robert Haas <robertmhaas@gmail.com> writes: > On Sun, Nov 15, 2009 at 8:22 PM, Andrew Dunstan <andrew@dunslane.net> wrote: >> Well, if the funcname.varname gadget will work, as you suggest elsewhere it >> could, I think that would suffice. I had assumed that was just something in >> the plpgsql engine. > That gadget isn't horribly convenient for me since my function names > tend to be 30 or 40 characters long. I wish we had something shorter, > and maybe constant. But I guess that's a topic for a separate > (inevitably rejected) patch. You're only going to need that if you insist on choosing parameter names that conflict with columns of the tables the function manipulates. Even then, attaching column aliases to the tables could be used instead. I don't see that this is any different from or worse than the extra typing you'll incur if you insist on using 40-character table names. (But having said that, an alternate qualification name is something that could be implemented if there were any agreement on what to use.) regards, tom lane
Tom Lane wrote: > Robert Haas <robertmhaas@gmail.com> writes: >> On Sun, Nov 15, 2009 at 8:22 PM, Andrew Dunstan <andrew@dunslane.net> wrote: >>> Well, if the funcname.varname gadget will work, as you suggest elsewhere it >>> could, I think that would suffice. I had assumed that was just something in >>> the plpgsql engine. > >> That gadget isn't horribly convenient for me since my function names >> tend to be 30 or 40 characters long. I wish we had something shorter, >> and maybe constant. But I guess that's a topic for a separate >> (inevitably rejected) patch. > > You're only going to need that if you insist on choosing parameter names > that conflict with columns of the tables the function manipulates. Even > then, attaching column aliases to the tables could be used instead. > I don't see that this is any different from or worse than the extra > typing you'll incur if you insist on using 40-character table names. > > (But having said that, an alternate qualification name is something > that could be implemented if there were any agreement on what to use.) Would something like ARG.name be acceptable? -- Andrew Chernow eSilo, LLC every bit counts http://www.esilo.com/
Andrew Chernow <ac@esilo.com> writes: > Tom Lane wrote: >> (But having said that, an alternate qualification name is something >> that could be implemented if there were any agreement on what to use.) > Would something like ARG.name be acceptable? It all depends on how likely you think it is that the function would use a table name or alias matching ARG (or any other proposal). It's certainly true that the function name itself is not immune from conflicts of that sort ... in fact I think we saw a bug report recently from someone who had intentionally chosen a plpgsql function name equal to a table name used in the function :-(. So I'm not wedded to the function name entirely. But it has precedent in plpgsql, and that precedent came from Oracle, so I don't think we should lightly make SQL functions do something different. regards, tom lane
Tom Lane wrote: > Andrew Chernow <ac@esilo.com> writes: >> Tom Lane wrote: >>> (But having said that, an alternate qualification name is something >>> that could be implemented if there were any agreement on what to use.) > >> Would something like ARG.name be acceptable? > > It all depends on how likely you think it is that the function would use > a table name or alias matching ARG (or any other proposal). > > It's certainly true that the function name itself is not immune from > conflicts of that sort ... in fact I think we saw a bug report recently > from someone who had intentionally chosen a plpgsql function name equal > to a table name used in the function :-(. So I'm not wedded to the > function name entirely. But it has precedent in plpgsql, and that > precedent came from Oracle, so I don't think we should lightly make SQL > functions do something different. > If the concern is portability, (ANYTHING).name won't work. You would have to stick with function.name or support both styles. -- Andrew Chernow eSilo, LLC every bit counts http://www.esilo.com/
Andrew Chernow <ac@esilo.com> writes: > Tom Lane wrote: >> It's certainly true that the function name itself is not immune from >> conflicts of that sort ... in fact I think we saw a bug report recently >> from someone who had intentionally chosen a plpgsql function name equal >> to a table name used in the function :-(. So I'm not wedded to the >> function name entirely. But it has precedent in plpgsql, and that >> precedent came from Oracle, so I don't think we should lightly make SQL >> functions do something different. > If the concern is portability, (ANYTHING).name won't work. You would have to > stick with function.name or support both styles. I find the recent SQL drafts pretty darn opaque, but I think that SQL:2008 6.6 <identifier chain> syntax rule 8)b)ii) If N = 2 and PIC1 is equivalent to the <qualified identifier> of a <routine name> RN whose scope contains IC and whoseassociated <SQL parameter declaration list> includes an SQL parameter SP whose <SQL parameter name> is equivalentto I2, then PIC2 is a candidate basis of IC, the scope of PIC2 is the scope of SP, and the referent of PIC2 is SP. is describing the style "function_name.argument_name". So it's not just Oracle setting that precedent. regards, tom lane
On Sun, Nov 15, 2009 at 8:40 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Robert Haas <robertmhaas@gmail.com> writes: >> On Sun, Nov 15, 2009 at 8:22 PM, Andrew Dunstan <andrew@dunslane.net> wrote: >>> Well, if the funcname.varname gadget will work, as you suggest elsewhere it >>> could, I think that would suffice. I had assumed that was just something in >>> the plpgsql engine. > >> That gadget isn't horribly convenient for me since my function names >> tend to be 30 or 40 characters long. I wish we had something shorter, >> and maybe constant. But I guess that's a topic for a separate >> (inevitably rejected) patch. > > You're only going to need that if you insist on choosing parameter names > that conflict with columns of the tables the function manipulates. Even > then, attaching column aliases to the tables could be used instead. > I don't see that this is any different from or worse than the extra > typing you'll incur if you insist on using 40-character table names. It's true, but that often seems like a natural thing to do. Someone passes you the ID of a project and you want to look up all the tasks associated with that project and do some computation on them. Well the task table has a project_id column, and that's also the obvious name for the parameter. You can call it pid or my_project_id or v_project_id or any of the other alternatives that are frequently suggested, or just leave it unnamed and refer to it as $1, but to my way of thinking project_id is the most natural choice. > (But having said that, an alternate qualification name is something > that could be implemented if there were any agreement on what to use.) Well that is the tricky part, for sure. I would personally prefer something like ${name} rather than a prefix, but I think you're likely to veto that outright. So, anything reasonably short would be an improvement over the status quo. self? this? my? ...Robert
Robert Haas wrote: >> (But having said that, an alternate qualification name is something >> that could be implemented if there were any agreement on what to use.) >> > > Well that is the tricky part, for sure. I would personally prefer > something like ${name} rather than a prefix, but I think you're likely > to veto that outright. So, anything reasonably short would be an > improvement over the status quo. self? this? my? > > > I think it would have to be a reserved word. The obvious existing keyword to use is "function" but unless I'm mistaken we'd need to move it from unreserved keyword to reserved, and I'm not sure this would justify that. cheers andrew
On Sun, Nov 15, 2009 at 9:52 PM, Andrew Dunstan <andrew@dunslane.net> wrote: > Robert Haas wrote: >>> >>> (But having said that, an alternate qualification name is something >>> that could be implemented if there were any agreement on what to use.) >>> >> >> Well that is the tricky part, for sure. I would personally prefer >> something like ${name} rather than a prefix, but I think you're likely >> to veto that outright. So, anything reasonably short would be an >> improvement over the status quo. self? this? my? > > I think it would have to be a reserved word. The obvious existing keyword to > use is "function" but unless I'm mistaken we'd need to move it from > unreserved keyword to reserved, and I'm not sure this would justify that. I don't see why it would need to be a reserved word. We're not changing how it gets parsed, just what it means. At any rate "FUNCTION." is a 9-character prefix, which is rather longer than I would prefer. PL/pgsql is a tiresomely long-winded language in general, IMHO, although some of Tom's changes for 8.5 will help with that. ...Robert
Andrew Dunstan <andrew@dunslane.net> writes: > Robert Haas wrote: >> Well that is the tricky part, for sure. I would personally prefer >> something like ${name} rather than a prefix, but I think you're likely >> to veto that outright. So, anything reasonably short would be an >> improvement over the status quo. self? this? my? >> > I think it would have to be a reserved word. The obvious existing > keyword to use is "function" but unless I'm mistaken we'd need to move > it from unreserved keyword to reserved, and I'm not sure this would > justify that. All of these are fundamentally illegal syntax, which as was already pointed out up-thread is likely to introduce nasty side-effects of its own. Not to mention being an order of magnitude harder to implement. We have a hook to implement resolving a ColumnRef, ie a qualified identifier. We don't have support for random other things. regards, tom lane
Robert Haas <robertmhaas@gmail.com> writes: > I don't see why it would need to be a reserved word. We're not > changing how it gets parsed, just what it means. At any rate > "FUNCTION." is a 9-character prefix, which is rather longer than I > would prefer. This from the guy who likes 40-character function names? regards, tom lane
Robert Haas wrote: > On Sun, Nov 15, 2009 at 9:52 PM, Andrew Dunstan <andrew@dunslane.net> wrote: > >> Robert Haas wrote: >> >>>> (But having said that, an alternate qualification name is something >>>> that could be implemented if there were any agreement on what to use.) >>>> >>>> >>> Well that is the tricky part, for sure. I would personally prefer >>> something like ${name} rather than a prefix, but I think you're likely >>> to veto that outright. So, anything reasonably short would be an >>> improvement over the status quo. self? this? my? >>> >> I think it would have to be a reserved word. The obvious existing keyword to >> use is "function" but unless I'm mistaken we'd need to move it from >> unreserved keyword to reserved, and I'm not sure this would justify that. >> > > I don't see why it would need to be a reserved word. We're not > changing how it gets parsed, just what it means. At any rate > "FUNCTION." is a 9-character prefix, which is rather longer than I > would prefer. PL/pgsql is a tiresomely long-winded language in > general, IMHO, although some of Tom's changes for 8.5 will help with > that. > > > Umm, what has this to do with plpgsql? We're talking about what to use in pure SQL functions. If you find plpgsql tiresome, use something else. There are plenty of alternatives. I think the debate is likely to be pointless in any case - it seems clear that there are objections to anything other than funcname.paramname as a disambiguating mechanism, so let's just go with that. It will still be a considerable advance. cheers andrew
On Sun, Nov 15, 2009 at 10:05 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Robert Haas <robertmhaas@gmail.com> writes: >> I don't see why it would need to be a reserved word. We're not >> changing how it gets parsed, just what it means. At any rate >> "FUNCTION." is a 9-character prefix, which is rather longer than I >> would prefer. > > This from the guy who likes 40-character function names? Hrm... I think this is bikeshedding at its finest - but having said that, thinking about what I usually do a little more, my PL/pgsql functions tend to be either triggers, which tend to have names like tablename_postupdate() [so around 20 characters, depending on the length of the table name] or else they tend to be functions that update some sort of summary statistics... like, oh, say, updating the task table with the total amount of time worked on each task by aggregating over a time log table. Those functions tend to get a name like update_task_time_worked(). That's only 23, but task is a pretty short word, so some of them might be a bit longer than that. So maybe 40 is an overestimate, although I probably do have a few that are close to that long. ...Robert
On Sun, Nov 15, 2009 at 10:14 PM, Andrew Dunstan <andrew@dunslane.net> wrote: > Robert Haas wrote: >> On Sun, Nov 15, 2009 at 9:52 PM, Andrew Dunstan <andrew@dunslane.net> >> wrote: >>> Robert Haas wrote: >>>>> (But having said that, an alternate qualification name is something >>>>> that could be implemented if there were any agreement on what to use.) >>>> >>>> Well that is the tricky part, for sure. I would personally prefer >>>> something like ${name} rather than a prefix, but I think you're likely >>>> to veto that outright. So, anything reasonably short would be an >>>> improvement over the status quo. self? this? my? >>> >>> I think it would have to be a reserved word. The obvious existing keyword >>> to >>> use is "function" but unless I'm mistaken we'd need to move it from >>> unreserved keyword to reserved, and I'm not sure this would justify that. >> >> I don't see why it would need to be a reserved word. We're not >> changing how it gets parsed, just what it means. At any rate >> "FUNCTION." is a 9-character prefix, which is rather longer than I >> would prefer. PL/pgsql is a tiresomely long-winded language in >> general, IMHO, although some of Tom's changes for 8.5 will help with >> that. > > Umm, what has this to do with plpgsql? We're talking about what to use in > pure SQL functions. I assume that we might consider implementing the same thing in both languages, if we get consensus on what it is. Perhaps I'm all wet. > If you find plpgsql tiresome, use something else. There are plenty of > alternatives. Actually, I find PL/pgsql to be awesome. The only thing I find tiresome about it is that is quite longwinded. But it at least has the advantage that you can embed SQL queries directly into it, without having to wrap yet another layer of quoting around them, so for the sorts of things I typically do it tends to be better than any of the alternatives with which I'm familiar. RETURN QUERY is a big step in the right direction (avoiding the need to write FOR x IN <query> LOOP RETURN NEXT x END LOOP). Tom's changes to enable short-circuit IF evaluation should improve this quite a bit, too. But anything else we can do is all to the good as far as I'm concerned. > I think the debate is likely to be pointless in any case - it seems clear > that there are objections to anything other than funcname.paramname as a > disambiguating mechanism, so let's just go with that. It will still be a > considerable advance. OK, onto the next windmill. ...Robert
2009/11/16 Andrew Dunstan <andrew@dunslane.net>: > > > Robert Haas wrote: >> >> On Sun, Nov 15, 2009 at 9:52 PM, Andrew Dunstan <andrew@dunslane.net> >> wrote: >> >>> >>> Robert Haas wrote: >>> >>>>> >>>>> (But having said that, an alternate qualification name is something >>>>> that could be implemented if there were any agreement on what to use.) >>>>> >>>>> >>>> >>>> Well that is the tricky part, for sure. I would personally prefer >>>> something like ${name} rather than a prefix, but I think you're likely >>>> to veto that outright. So, anything reasonably short would be an >>>> improvement over the status quo. self? this? my? >>>> >>> >>> I think it would have to be a reserved word. The obvious existing keyword >>> to >>> use is "function" but unless I'm mistaken we'd need to move it from >>> unreserved keyword to reserved, and I'm not sure this would justify that. >>> >> >> I don't see why it would need to be a reserved word. We're not >> changing how it gets parsed, just what it means. At any rate >> "FUNCTION." is a 9-character prefix, which is rather longer than I >> would prefer. PL/pgsql is a tiresomely long-winded language in >> general, IMHO, although some of Tom's changes for 8.5 will help with >> that. we should to use some like #option from plpgsql too. create or replace function verylongname(p1 integer) returns int as $$ #option paramalias v SELECT ... WHERE x = v.p1 Pavel >> >> >> > > Umm, what has this to do with plpgsql? We're talking about what to use in > pure SQL functions. > > If you find plpgsql tiresome, use something else. There are plenty of > alternatives. > > I think the debate is likely to be pointless in any case - it seems clear > that there are objections to anything other than funcname.paramname as a > disambiguating mechanism, so let's just go with that. It will still be a > considerable advance. > > cheers > > andrew > > -- > Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-hackers >
2009/11/16 Robert Haas <robertmhaas@gmail.com>: > On Sun, Nov 15, 2009 at 10:14 PM, Andrew Dunstan <andrew@dunslane.net> wrote: >> Robert Haas wrote: >>> On Sun, Nov 15, 2009 at 9:52 PM, Andrew Dunstan <andrew@dunslane.net> >>> wrote: >>>> Robert Haas wrote: >>>>>> (But having said that, an alternate qualification name is something >>>>>> that could be implemented if there were any agreement on what to use.) >>>>> >>>>> Well that is the tricky part, for sure. I would personally prefer >>>>> something like ${name} rather than a prefix, but I think you're likely >>>>> to veto that outright. So, anything reasonably short would be an >>>>> improvement over the status quo. self? this? my? >>>> >>>> I think it would have to be a reserved word. The obvious existing keyword >>>> to >>>> use is "function" but unless I'm mistaken we'd need to move it from >>>> unreserved keyword to reserved, and I'm not sure this would justify that. >>> >>> I don't see why it would need to be a reserved word. We're not >>> changing how it gets parsed, just what it means. At any rate >>> "FUNCTION." is a 9-character prefix, which is rather longer than I >>> would prefer. PL/pgsql is a tiresomely long-winded language in >>> general, IMHO, although some of Tom's changes for 8.5 will help with >>> that. >> >> Umm, what has this to do with plpgsql? We're talking about what to use in >> pure SQL functions. > > I assume that we might consider implementing the same thing in both > languages, if we get consensus on what it is. Perhaps I'm all wet. > >> If you find plpgsql tiresome, use something else. There are plenty of >> alternatives. > > Actually, I find PL/pgsql to be awesome. The only thing I find > tiresome about it is that is quite longwinded. But it at least has > the advantage that you can embed SQL queries directly into it, without > having to wrap yet another layer of quoting around them, so for the > sorts of things I typically do it tends to be better than any of the > alternatives with which I'm familiar. RETURN QUERY is a big step in > the right direction (avoiding the need to write FOR x IN <query> LOOP > RETURN NEXT x END LOOP). Tom's changes to enable short-circuit IF > evaluation should improve this quite a bit, too. But anything else we > can do is all to the good as far as I'm concerned. I inclined to return back to plpgpsm reimplementation. PSM should have very short and clean code. CREATE OR REPLACE FUNCTION srffunc(param) RETURNS TABLE(param) AS $$ RETURN SELECT * FROM foo; $$ LANGUAGE plpgpsm; This one return statement function should be little bit better optimalised then plpgsql (from srf view). Pavel > >> I think the debate is likely to be pointless in any case - it seems clear >> that there are objections to anything other than funcname.paramname as a >> disambiguating mechanism, so let's just go with that. It will still be a >> considerable advance. > > OK, onto the next windmill. > > ...Robert > > -- > Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-hackers >
On sön, 2009-11-15 at 12:37 -0500, Andrew Dunstan wrote: > At Tom's suggestion I am looking at allowing use of parameter names in > SQL functions instead of requiring use of $1 etc. That raises the > question of how we would disambiguate a parameter name from a column > name. Essentially, ISTM, we could use some special marker such as @ > (c.f. SQL Server) or : (c.f. ecpg) or else we could have some rule that > says which name takes precedence. I think I prefer a special marker, > other things being equal. Is there a standard on this? Yes, search the SQL standard for <SQL parameter reference>.