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

J. Greg Davidson wrote:
> Hi dear colleagues,
>
> I'm trying to pull some platform-specific constants out of my
> code by using psql variables, e.g.:
>
> $ psql -v TypeLength=4
>
> # CREATE TYPE tref (
> INTERNALLENGTH = :TRefTypeLength,
> INPUT = tref_in,
> OUTPUT = tref_out,
> PASSEDBYVALUE
> );
>
> which works fine, but when I need such a constant in a function
> it is not substituted. A simplified example:
>
> $ psql -v foo=10
>
> # select :foo;
> ?column?
> ----------
> 10
> (1 row)
>
> # create function foo() returns integer as 'select '(:foo) language sql;
> ERROR: syntax error at or near "(" at character 51
>
> I'm sure that I could do something horrible by using EXECUTE inside of
> a plpgsql function, and I'm hoping that someone will have a simpler
> alternative. For example, is there some kind of quoting mechanism I can
> use which will not impede psql from doing substitutions?

I don't think that there is a convenient way, as variables are not
substituted inside string constants.

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