Thread: CREATE TABLESPACE dynamically

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.
2) The CREATE TABLESPACE command does not allow expressions.
   CREATE TABLESPACE foo LOCATION 'FOO' | 'BAR'
is not valid.  I'm not sure I understand why since that is an expression
that yields a string.  Parenthesis don't help either.

Is there any way I can do this?

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

Richard Huxton wrote:
> 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.

Windows (at least XP & 2003 Server) actually supports real symbolic
links, even if they are not exposed through the UI provided by explorer.
They are called "junktions" on windows, and you can only create linsk to
directories, not to files. But you could use them to e.g. create a link
from C:\postgres.data to E:\postgres.data on one machine, and to
F:\postgres.data on another. Than you can just write "LOCATION
C:\postgres.data" in your script, and it will still use the correct
partition.

This is not an answer to your question, but maybe it's an easier
solution to your problem.

greetings, Florian Pflug