From: | "J(dot) Greg Davidson" <jgd(at)well(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: How can I interpolate psql variables in function bodies? - workaround |
Date: | 2009-06-25 01:32:10 |
Message-ID: | 1245893530.5757.14.camel@shevek.puuhonua.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | J. Greg Davidson | 2009-06-25 03:13:14 | Re: How can I interpolate psql variables in function bodies? |
Previous Message | Tom Lane | 2009-06-24 23:49:36 | Re: Serious JDBC problem |