How can I interpolate psql variables in function bodies?

Поиск
Список
Период
Сортировка
От J. Greg Davidson
Тема How can I interpolate psql variables in function bodies?
Дата
Msg-id 1245104934.15935.32.camel@shevek.puuhonua.org
обсуждение исходный текст
Ответы Re: How can I interpolate psql variables in function bodies?  ("Albe Laurenz" <laurenz.albe@wien.gv.at>)
Re: How can I interpolate psql variables in function bodies? - workaround  ("J. Greg Davidson" <jgd@well.com>)
Список pgsql-general
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


В списке pgsql-general по дате отправления:

Предыдущее
От: Glyn Astill
Дата:
Сообщение: Re: DB Migration 8.4 -> 8.3
Следующее
От: David Kerr
Дата:
Сообщение: Re: Amazon EC2 | Any recent developments