Re: How can I interpolate psql variables in function bodies? - workaround

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