Re: 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 
---11
(2 rows)

So there is no leaking out. The variables are scoped within the function.

Best,

David