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

On Tue, 2009-06-16 at 09:20 +0200, Albe Laurenz wrote:

> I came up with the following:
> 
> psql -v foo=10
> test=> \set funcbody `echo "'"SELECT\ `:foo`echo "'"`
> test=> create function foo() returns integer as :funcbody language sql;
> 
> But that is ugly as hell and depends on the underlying operating
> system to have a Bourne-like shell.
> 
> Yours,
> Laurenz Albe

Here is a similar trick:

psql -v f=10
\set g '''select ' :f '::integer;'''
create function g() returns integer as :g language sql immutable;

g() can be used in the bodies of other functions where it should
be inline substituted since it's immutable.

I'm concerned as to whether this might break if psql slightly
changes how it it does substitution.  The documentation does not
fully specify how substitution behaves.

_Greg

J. Greg Davidson