Home > mailing lists

Thread: psql question

psql question

From

"Little, Douglas"

Date:

31 January 2013, 00:51:44

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 312.894.5164 | Cell 847-997-5741

Douglas.Little@orbitz.com

Attachment

image001.jpg

Re: psql question

From

Steve Crawford

Date:

31 January 2013, 01:48:58

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

Re: psql question

From

Tom Lane

Date:

31 January 2013, 02:42:55

Steve Crawford <scrawford@pinpointresearch.com> writes:
> On 01/30/2013 01:51 PM, Little, Douglas wrote:
>> 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

Re: psql question

From

"Little, Douglas"

Date:

31 January 2013, 19:54:15

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?