Обсуждение: 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