I've found a workaround using the new pg 8.3 feature of default values
for function arguments. It is not a completely general workaround (it
won't, e.g. allow type or table names to be interpolated) but it does
what I need and perhaps others may find this trick useful.
To briefly recap the problem:
On Mon, 2009-06-15 at 15:28 -0700, J. Greg Davidson wrote:
> Hi dear colleagues,
>
> When I need such a constant in a function
> it is not substituted:
> $ psql -v foo=10
> # create function foo() returns integer as 'select '(:foo) language sql;
> ERROR: syntax error at or near "(" at character 51
The workaround:
CREATE FUNCTION foo(integer DEFAULT :foo) RETURNS integer AS $$
SELECT $1
$$ LANGUAGE sql;
In other words, I add an extra DEFAULT argument for each psql variable I
need to use in the function body.
I'm wondering if there is any advantage in psql's declining to do
substitutions inside of quotes, especially $$ oxford quotes $$. Perhaps
a future version of psql can make things easier.
_Greg
J. Greg Davidson
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
