Re: psql question

[Steve Crawford <scrawford@xxxxxxxxxxxxxxxxxxxx>]

On 01/30/2013 01:51 PM, Little, Douglas wrote:

I’m looking for a way where I can tailor DDL scripts for deployment with environment variables.

Support I have a requirement to prefix table names with dev_ , fqa_, or prod_

 

I’d like to have a file for each env with their own unique settings – host, dbname

Dev.sql

                \set env dev

Fqa

                \set env fqa

 

prod

                \set env prod

 

and then

 

my deployment script would have

ddl.sql

\i :env.sql

Create table schema.:env_tablename….

 

 

I tried it and didn’t work.

p1gp1=> \set env dev

p1gp1=> \i P1GP1_ETL_STAGE_TBLS_BIO6113_100.:env.sql

P1GP1_ETL_STAGE_TBLS_BIO6113_100.:env.sql: No such file or directory

 

Any thoughts on how I might get this to work?

Perhaps try concatenating variables then executing the result. For example, given a file "foo.psql" containing "select now();" and "bar.psql" containing "select 'Hello world';"

steve@[local] => \set env foo
steve@[local] => \set scriptname :env '.psql'
steve@[local] => \i :scriptname
              now             
-------------------------------
 2013-01-30 14:45:36.423836-08

steve@[local] => \set env bar
steve@[local] => \set scriptname :env '.psql'
steve@[local] => \i :scriptname
  ?column?  
-------------
 Hello world

Cheers,
Steve