psql question

[Little, Douglas]

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?

Thanks




Doug Little

Sr. Data Warehouse Architect | Business Intelligence Architecture | Orbitz Worldwide
500 W. Madison, Suite 1000 Chicago IL 60661| Office 312.260.2588 | Fax [phone] | Cell 847-997-5741
***@orbitz.com<mailto:***@orbitz.com>
[cid:***@01CDFF01.6452A380] orbitz.com<http://www.orbitz.com/> | ebookers.com<http://www.ebookers.com/> | hotelclub.com<http://www.hotelclub.com/> | cheaptickets.com<http://www.cheaptickets.com/> | ratestogo.com<http://www.ratestogo.com/> | asiahotels.com<http://www.asiahotels.com/>

[Steve Crawford]

Post by Little, Douglas
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

[Tom Lane]

Post by Steve Crawford

Post by Little, Douglas
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.

FWIW, Douglas' original coding works for me in 9.2 and HEAD:

regression=# \set env dev
regression=# \i P1GP1_ETL_STAGE_TBLS_BIO6113_100.:env.sql
P1GP1_ETL_STAGE_TBLS_BIO6113_100.dev.sql: No such file or directory

I think we fixed some bugs associated with expansion of variable names
embedded within larger words, but didn't back-patch for fear of breaking
existing apps in a minor release. Your alternative looks to me like
it's basically dodging those bugs ...

regards, tom lane

[Little, Douglas]

Thanks to steve and Al for the suggestions.

I did get the variable concatenation solution to work.
\set env `echo $TARGETSERVER`
\echo env :env
\set envfile 'P1GP1_ETL_STAGE_TBLS_BIO6113_100.':env'.sql'
\echo envfile :envfile
-- envfile P1GP1_ETL_STAGE_TBLS_BIO6113_100.DEV.sql

However it seems that I can't use the psql variables within sql.
Without quotes the variable name is used in the sql and the table is created, but since it's created with the variable name and not value it won't execute.
With quotes, the variable gets resolved but the syntax is invalid. The syntax requires that the file location be in quotes.

I'm using greenplum's external table feature and the external filename needs to change by environment.
....
Create external table .... (
Col_a ....
)
LOCATION (
'gphdfs://':filepath'/DimSiteVisit/part*'
)


Runtime log
ERROR: syntax error at or near ":"
LINE 44: 'gphdfs://':filepath'/DimSiteVisit/part*'

I think I'm going to shift down to using shell features.


Thanks again for the help.

From: Steve Crawford [mailto:***@pinpointresearch.com]
Sent: Wednesday, January 30, 2013 4:49 PM
To: Little, Douglas
Cc: PostgreSQL General (pgsql-***@postgresql.org)
Subject: Re: [GENERAL] 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@[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