How can I interpolate psql variables in function bodies?

["J. Greg Davidson" <jgd@xxxxxxxx>]

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


-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general