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:scrawford@pinpointresearch.com]
Sent: Wednesday, January 30, 2013 4:49 PM
To: Little, Douglas
Cc: PostgreSQL General (pgsql-general@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
