Re: CREATE TABLESPACE dynamically

William Garrison wrote:
> I am writing scripts to create a database that I want to run in my
> development, testing, and production environments. That means I need to
> be able to do something like
>
> IF <condition>
> CREATE TABLESPACE foo LOCATION E'C:\database';
> ELSE
> CREATE TABLESPACE foo LOCATION E'Z:\database';
>
> I can't seem to find a way to do this, for two reasons:
> 1) I can't do IF statements unless I create a PL/PGSQL function. And a
> PL/PGSQL function cannot issue a CREATE TABLESPACE command.

Try constructing a string containing the command and using EXECUTE from
plpgsql.

If you can't get that to work, you need to move the IF outside the
database. You might do this with psql's variable interpolation (careful
with the quoting) or a judicious mix of make/grep/sed/awk/perl etc.

Hmm - just noticed you're on Windows, so Perl etc won't be installed by
default. I just install perl and/or cygwin, but presumably you have your
own alternatives. I'm curious - what do you use for this sort of general
utility & text-file mangling as a Windows developer?

--
Richard Huxton
Archonet Ltd