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

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?

Thanks,

_Greg

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

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

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