From: | "David E(dot) Wheeler" <david(at)kineticode(dot)com> |
---|---|
To: | Greg Stark <gsstark(at)mit(dot)edu> |
Cc: | Andrew Chernow <ac(at)esilo(dot)com>, Andrew Dunstan <andrew(at)dunslane(dot)net>, PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: named parameters in SQL functions |
Date: | 2009-11-15 20:16:34 |
Message-ID: | 25225372-8D35-4D32-8E6A-764D99A67F2E@kineticode.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Heikki Linnakangas | 2009-11-15 20:23:45 | Re: Hot standby, race condition between recovery snapshot and commit |
Previous Message | Heikki Linnakangas | 2009-11-15 20:10:53 | Re: Summary and Plan for Hot Standby |