Thread: psql substitution variables

can anybody think of of a way to sneak these into dollar quoted
strings for substitution into create function?

would i would ideally like to do is (from inside psql shell)

\set foo 500

create function bar() returns int as
$$
  declare
    baz int default :foo;
[...]

that would eliminate the need for using the c preprocessor (or similar
tool) in some cases that require substitution in that way and (best of
all) allow psql to grok pre defined constants defined externally.

merlin

In article <b42b73150701230831t317595f6o3beb37d43f363412@mail.gmail.com>,
"Merlin Moncure" <mmoncure@gmail.com> writes:

> can anybody think of of a way to sneak these into dollar quoted
> strings for substitution into create function?

> would i would ideally like to do is (from inside psql shell)

> \set foo 500

> create function bar() returns int as
> $$
>   declare
>     baz int default :foo;
> [...]

Can't you put the psql call into a shell script and use shell variable
substitution for that?

On 24 Jan 2007 00:21:44 +0100, Harald Fuchs <hf1110x@protecting.net> wrote:
> In article <b42b73150701230831t317595f6o3beb37d43f363412@mail.gmail.com>,
> "Merlin Moncure" <mmoncure@gmail.com> writes:
>
> > can anybody think of of a way to sneak these into dollar quoted
> > strings for substitution into create function?

>
> Can't you put the psql call into a shell script and use shell variable
> substitution for that?

right.  that works, actually I was using gcc to preprocess my sql
files.   This has some side benefits: with a little work you can
actually have C code (or php or whatever) and sql scripts share a
common header, but you can no longer paste sql into psql which is
basically how I like to develop.

psql variables sidestep that tradeoff but are not usable inside
function definitions which is a critical drawback.  This is on top of
the fact that create function takes a string but not a string
expression, so you can't run translate() and the like on it.

I'm wondering if I can use some tricky variable substition using sed
or something similar but I'm drawing a blank at the moment.

merlin