named parameters in SQL functions
in [PgSql]
|
From: Greg Stark on 15 Nov 2009 15:09 On Sun, Nov 15, 2009 at 7:56 PM, Andrew Chernow <ac(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(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: "David E. Wheeler" on 15 Nov 2009 15:16 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(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: Tom Lane on 15 Nov 2009 16:40 Andrew Dunstan <andrew(a)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(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: Andrew Dunstan on 15 Nov 2009 20:22 Tom Lane wrote: > Andrew Dunstan <andrew(a)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(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: Robert Haas on 15 Nov 2009 20:35
On Sun, Nov 15, 2009 at 8:22 PM, Andrew Dunstan <andrew(a)dunslane.net> wrote: > Tom Lane wrote: >> >> Andrew Dunstan <andrew(a)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(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers |