Re: psql question

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(at)[local] => \set env foo
steve(at)[local] => \set scriptname :env '.psql'
steve(at)[local] => \i :scriptname
now
-------------------------------
2013-01-30 14:45:36.423836-08

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

Cheers,
Steve