Re: [HACKERS] named parameters in SQL functions
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. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] named parameters in SQL functions
Andrew Dunstan wrote: At Tom's suggestion I am looking at allowing use of parameter names in SQL functions instead of requiring use of $1 etc. That raises the question of how we would disambiguate a parameter name from a column name. Essentially, ISTM, we could use some special marker such as @ (c.f. SQL Server) or : (c.f. ecpg) or else we could have some rule that says which name takes precedence. I think I prefer a special marker, other things being equal. Is there a standard on this? I like the special marker idea. A '$' would be nice because its already in use for similar purposes, but I think that would lead to ambiguity with dollar quoting. Would this be limited to sql functions? I only ask because for non-sql functions we currently prefix parameter names with an underscore, but a built-in special marker would be much more desirable. -- Andrew Chernow eSilo, LLC every bit counts http://www.esilo.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] named parameters in SQL functions
2009/11/15 Andrew Dunstan and...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] named parameters in SQL functions
2009/11/15 Andrew Chernow a...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] named parameters in SQL functions
On Sun, Nov 15, 2009 at 12:37 PM, Andrew Dunstan and...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] named parameters in SQL functions
On Sun, Nov 15, 2009 at 5:49 PM, Andrew Chernow a...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] named parameters in SQL functions
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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] named parameters in SQL functions
2009/11/15 David E. Wheeler da...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] named parameters in SQL functions
On Sun, Nov 15, 2009 at 6:42 PM, Pavel Stehule pavel.steh...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] named parameters in SQL functions
On Sun, Nov 15, 2009 at 6:26 PM, David E. Wheeler da...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] named parameters in SQL functions
2009/11/16 Andrew Dunstan and...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] named parameters in SQL functions
2009/11/15 Greg Stark gsst...@mit.edu: On Sun, Nov 15, 2009 at 6:42 PM, Pavel Stehule pavel.steh...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] named parameters in SQL functions
On Nov 15, 2009, at 10:54 AM, Greg Stark wrote: I'm japh too -- but that doesn't mean grabbing one little aesthetic from Perl without copying the whole concept behind it makes any sense. Perl sigils are an important part of the language and are a basic part of the syntax. They aren't just a this is a variable marker. Dropping one use of them into a language that doesn't use them anywhere else just makes the language into a mishmash. Well, no, just because we're talking about adopting $var doesn't mean we're trying to turn SQL or PL/pgSQL into Perl. It means that we want to signify that a token is a variable, as opposed to something else (hence “sigil”). That doesn't make it a mishmash unless you think you suddenly have Perl (or shell) semantics, which would be a pretty weird expectation. I don't see any purpose to using such markers anyways. We have a parser, we have a symbol table, we should use them; these identifiers are just like other identifiers. See the discussion of conflicts with column names in the recent thread. A sigil would eliminate that problem -- and we already have $1 and friends, so this is just an extension of that in my view. Best, David -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] named parameters in SQL functions
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/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] named parameters in SQL functions
2009/11/15 Andrew Chernow a...@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/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] named parameters in SQL functions
On Sun, Nov 15, 2009 at 6:26 PM, David E. Wheeler da...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] named parameters in SQL functions
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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] named parameters in SQL functions
On Sun, Nov 15, 2009 at 7:25 PM, David E. Wheeler da...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] named parameters in SQL functions
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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] named parameters in SQL functions
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/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] named parameters in SQL functions
On Sun, Nov 15, 2009 at 7:56 PM, Andrew Chernow a...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] named parameters in SQL functions
On Nov 15, 2009, at 12:09 PM, Greg Stark wrote: 1) Error messages which mention column names are supposed to quote the column name to set it apart from the error string. This also guarantees that weird column names are referenced correctly as foo bar or $foo so the reference in the error string is unambiguous and can be pasted into queries. This won't work for $foo which would have to be embedded in the error text without quotes. What? You can't have a column named $foo without the quotes. 2) What would the default names for columns be if you did something like create function f(foo) as 'select $foo' It would be f (without the quotes), just like now: try=# create function f(int) RETURNS int as 'SELECT $1' LANGUAGE sql; CREATE FUNCTION try=# select f(1); f --- 1 (1 row) If I then use this in another function create function g(foo) as 'select $foo+$foo from f()' I have to quote the column? No, that's a syntax error. It would be `SELECT f + $foo from f();` 3) If I have a report generator which takes a list of columns to include in the report, or an ORM which tries to generate queries the usual way to write such things is to just routinely quote every identifier. This is less error-prone and simpler to code than trying to identify which identifiers need quoting and which don't. However in if the query is then dropped into a function the ORM or query generator would have to know which columns cannot be quoted based on syntactic information it can't really deduce. You already have to quote everything, because $foo isn't a valid column name. And functions use the function name as the default column name, not a variable name. The same is true of set-returning functions, BTW: try=# create function b(int) RETURNS setof int as 'values ($1), ($1)' LANGUAGE sql; CREATE FUNCTION try=# select b(1); b --- 1 1 (2 rows) So there is no leaking out. The variables are scoped within the function. Best, David -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] named parameters in SQL functions
On Nov 15, 2009, at 11:35 AM, Greg Stark wrote: I don't think SQL is the height of language design either. But trying to turn it into another language piece by piece is not gong to make it any nicer. I don't know of anyone suggesting such a thing. A sigil here doesn't accomplish anything. The identifiers in question are *just* like other identifiers. They can be used in expressions just like other columns, they have various types, they have the same syntax as other columns, the sigil doesn't mean anything. So what is the $ for in $1, $2, etc.? I think what may be making this tempting is that they look vaguely like ODBC/JDBC/DBI placeholders like :foo. However they're very very different. In those cases the sigil is marking the sigil outside the SQL syntax. They will be replaced textually without parsing the SQL at all. It's actually very confusing having $foo indicate something within SQL since it makes it look like it's some external thing from another layer like the placeholders. It's not in SQL; it's in SQL functions (and DO blocks). AFAIK, the major database vendors all use some sort of character to identify variables within functions. It's proven, avoids conflicts (you can't have an identifier named $foo, as Andrew just pointed out), and just generally makes maintenance easier. Best, David -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] named parameters in SQL functions
Andrew Dunstan and...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] named parameters in SQL functions
Tom Lane wrote: Andrew Dunstan and...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] named parameters in SQL functions
On Sun, Nov 15, 2009 at 8:22 PM, Andrew Dunstan and...@dunslane.net wrote: Tom Lane wrote: Andrew Dunstan and...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] named parameters in SQL functions
Robert Haas robertmh...@gmail.com writes: On Sun, Nov 15, 2009 at 8:22 PM, Andrew Dunstan and...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] named parameters in SQL functions
Tom Lane wrote: Robert Haas robertmh...@gmail.com writes: On Sun, Nov 15, 2009 at 8:22 PM, Andrew Dunstan and...@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/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] named parameters in SQL functions
Andrew Chernow a...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] named parameters in SQL functions
Tom Lane wrote: Andrew Chernow a...@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/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] named parameters in SQL functions
Andrew Chernow a...@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 whose associated SQL parameter declaration list includes an SQL parameter SP whose SQL parameter name is equivalent to I2, then PIC2 is a candidate basis of IC, the scope of PIC2 is the scope of SP, and the referent of PIC2 is SP. is describing the style function_name.argument_name. So it's not just Oracle setting that precedent. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] named parameters in SQL functions
On Sun, Nov 15, 2009 at 8:40 PM, Tom Lane t...@sss.pgh.pa.us wrote:
Robert Haas robertmh...@gmail.com writes:
On Sun, Nov 15, 2009 at 8:22 PM, Andrew Dunstan and...@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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] named parameters in SQL functions
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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] named parameters in SQL functions
On Sun, Nov 15, 2009 at 9:52 PM, Andrew Dunstan and...@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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] named parameters in SQL functions
Andrew Dunstan and...@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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] named parameters in SQL functions
Robert Haas robertmh...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] named parameters in SQL functions
Robert Haas wrote:
On Sun, Nov 15, 2009 at 9:52 PM, Andrew Dunstan and...@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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] named parameters in SQL functions
On Sun, Nov 15, 2009 at 10:05 PM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] named parameters in SQL functions
On Sun, Nov 15, 2009 at 10:14 PM, Andrew Dunstan and...@dunslane.net wrote:
Robert Haas wrote:
On Sun, Nov 15, 2009 at 9:52 PM, Andrew Dunstan and...@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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] named parameters in SQL functions
2009/11/16 Andrew Dunstan and...@dunslane.net:
Robert Haas wrote:
On Sun, Nov 15, 2009 at 9:52 PM, Andrew Dunstan and...@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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] named parameters in SQL functions
2009/11/16 Robert Haas robertmh...@gmail.com:
On Sun, Nov 15, 2009 at 10:14 PM, Andrew Dunstan and...@dunslane.net wrote:
Robert Haas wrote:
On Sun, Nov 15, 2009 at 9:52 PM, Andrew Dunstan and...@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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
