named parameters in SQL functions
in [PgSql]
|
From: Andrew Chernow on 15 Nov 2009 12:49 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(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: Pavel Stehule on 15 Nov 2009 12:51 2009/11/15 Andrew Dunstan <andrew(a)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(a)postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-hackers > -- 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: Pavel Stehule on 15 Nov 2009 12:54 2009/11/15 Andrew Chernow <ac(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(a)postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-hackers > -- 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 13:16 On Sun, Nov 15, 2009 at 12:37 PM, Andrew Dunstan <andrew(a)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(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: Greg Stark on 15 Nov 2009 13:19
On Sun, Nov 15, 2009 at 5:49 PM, Andrew Chernow <ac(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(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers |