How can I interpolate psql variables in function bodies?

From: "J(dot) Greg Davidson" <jgd(at)well(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: How can I interpolate psql variables in function bodies?
Date: 2009-06-15 22:28:54
Message-ID: 1245104934.15935.32.camel@shevek.puuhonua.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Eoghan Murray 2009-06-15 22:39:56 Re: DB Migration 8.4 -> 8.3
Previous Message Glyn Astill 2009-06-15 21:54:48 Re: DB Migration 8.4 -> 8.3