Search Postgresql Archives

Re: psql question

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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@xxxxxxxxxxxxxxxxxxxx]
Sent: Wednesday, January 30, 2013 4:49 PM
To: Little, Douglas
Cc: PostgreSQL General (pgsql-general@xxxxxxxxxxxxxx)
Subject: 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@[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


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux