psql & unix env variables

On Wednesday, August 29, 2012 12:35:32 PM Little, Douglas wrote:

Is there a method for having unix env variables incorporated into a psql sql
statement? Ie
Export var='dev'
Psql =c 'select count(*) from $var.customer;'

Use double-quotes, not single-quotes. Bash won't interpolate variables into
single-quoted strings.

On Wed, Aug 29, 2012 at 12:35:32PM -0500, Little, Douglas wrote:

Is there a method for having unix env variables incorporated into a psql sql statement?
Ie
Export var='dev'
Psql =c 'select count(*) from $var.customer;'

export FOO="bar"
psql -c "select count(*) from $FOO.customer;"

Note the double quotes. That allows your shell to interpolate the string
into your query. Note that the interpolation is done by your shell, and
not psql.

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<mailto:Douglas.Little@orbitz.com>
[cid:image001.jpg@01CD85E2.C7732E50] orbitz.com<http://www.orbitz.com/&gt; | ebookers.com<http://www.ebookers.com/&gt; | hotelclub.com<http://www.hotelclub.com/&gt; | cheaptickets.com<http://www.cheaptickets.com/&gt; | ratestogo.com<http://www.ratestogo.com/&gt; | asiahotels.com<http://www.asiahotels.com/&gt;

-Ryan Kelly

Thanks

-----Original Message-----
From: Ryan Kelly [mailto:rpkelly22@gmail.com]
Sent: Wednesday, August 29, 2012 12:41 PM
To: Little, Douglas
Cc: PostgreSQL General (pgsql-general@postgresql.org)
Subject: Re: [GENERAL] psql & unix env variables

On Wed, Aug 29, 2012 at 12:35:32PM -0500, Little, Douglas wrote:

Is there a method for having unix env variables incorporated into a psql sql statement?
Ie
Export var='dev'
Psql =c 'select count(*) from $var.customer;'

export FOO="bar"
psql -c "select count(*) from $FOO.customer;"

Note the double quotes. That allows your shell to interpolate the string into your query. Note that the interpolation is done by your shell, and not psql.

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<mailto:Douglas.Little@orbitz.com>
[cid:image001.jpg@01CD85E2.C7732E50] orbitz.com<http://www.orbitz.com/&gt; | ebookers.com<http://www.ebookers.com/&gt; | hotelclub.com<http://www.hotelclub.com/&gt; | cheaptickets.com<http://www.cheaptickets.com/&gt; | ratestogo.com<http://www.ratestogo.com/&gt; | asiahotels.com<http://www.asiahotels.com/&gt;

-Ryan Kelly

I have found useful the use of variable assignment in psql, e.g.

#!/bin/sh

# lets say you have some var with a value, or even populate some var with a value from
# psql as shown below
somevar=`psql -P pager=off -q -t -c "SELECT foo from bar" | head -1 | sed -e 's/ //g'`

# now use that variable in psql, (what you want to achieve), but in more tight manner
# than simple shell substitution (see -v switch and : notation)

psql -P pager=off -q -v somevar=$somevar -c "select foo2 from bar2 where var=:somevar"

On Τετ 29 Αυγ 2012 15:00:29 Little, Douglas wrote:

Thanks

-----Original Message-----
From: Ryan Kelly [mailto:rpkelly22@gmail.com]
Sent: Wednesday, August 29, 2012 12:41 PM
To: Little, Douglas
Cc: PostgreSQL General (pgsql-general@postgresql.org)
Subject: Re: [GENERAL] psql & unix env variables

On Wed, Aug 29, 2012 at 12:35:32PM -0500, Little, Douglas wrote:

Is there a method for having unix env variables incorporated into a psql sql statement?
Ie
Export var='dev'
Psql =c 'select count(*) from $var.customer;'

export FOO="bar"
psql -c "select count(*) from $FOO.customer;"

Note the double quotes. That allows your shell to interpolate the string into your query. Note that the interpolation is done by your shell, and not psql.

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<mailto:Douglas.Little@orbitz.com>
[cid:image001.jpg@01CD85E2.C7732E50] orbitz.com<http://www.orbitz.com/&gt; | ebookers.com<http://www.ebookers.com/&gt; | hotelclub.com<http://www.hotelclub.com/&gt; | cheaptickets.com<http://www.cheaptickets.com/&gt; | ratestogo.com<http://www.ratestogo.com/&gt; | asiahotels.com<http://www.asiahotels.com/&gt;

-Ryan Kelly

-
Achilleas Mantzios
IT DEPT

On Thu, Aug 30, 2012 at 4:42 PM, Achilleas Mantzios
<achill@smadev.internal.net> wrote:

I have found useful the use of variable assignment in psql, e.g.

#!/bin/sh

# lets say you have some var with a value, or even populate some var with a value from
# psql as shown below
somevar=`psql -P pager=off -q -t -c "SELECT foo from bar" | head -1 | sed -e 's/ //g'`

# now use that variable in psql, (what you want to achieve), but in more tight manner
# than simple shell substitution (see -v switch and : notation)

psql -P pager=off -q -v somevar=$somevar -c "select foo2 from bar2 where var=:somevar"

At this point, I have to ask: Why not switch to a language with actual
Postgres bindings? Try Python, or Pike, or something; I'm sure it's
going to be easier than doing everything through shell scripts.

ChrisA

On 08/30/2012 04:19 PM, Chris Angelico wrote:

On Thu, Aug 30, 2012 at 4:42 PM, Achilleas Mantzios
<achill@smadev.internal.net> wrote:

I have found useful the use of variable assignment in psql, e.g.

#!/bin/sh

# lets say you have some var with a value, or even populate some var with a value from
# psql as shown below
somevar=`psql -P pager=off -q -t -c "SELECT foo from bar" | head -1 | sed -e 's/ //g'`

# now use that variable in psql, (what you want to achieve), but in more tight manner
# than simple shell substitution (see -v switch and : notation)

psql -P pager=off -q -v somevar=$somevar -c "select foo2 from bar2 where var=:somevar"

At this point, I have to ask: Why not switch to a language with actual
Postgres bindings? Try Python, or Pike, or something; I'm sure it's
going to be easier than doing everything through shell scripts.

There is always ShellSQL. Not sure how current it is, but maybe worth a
look:
http://shellsql.sourceforge.net/

ChrisA

--
Adrian Klaver
adrian.klaver@gmail.com

On 08/30/2012 02:42 PM, Achilleas Mantzios wrote:

I have found useful the use of variable assignment in psql, e.g.

If you're going to to that, why not drive psql as a coprocess:

http://stackoverflow.com/a/8305578/398670

or if at all possible, use a language with sane PostgreSQL bindings.

--
Craig Ringer

On Παρ 31 Αυγ 2012 09:19:26 Chris Angelico wrote:

On Thu, Aug 30, 2012 at 4:42 PM, Achilleas Mantzios
<achill@smadev.internal.net> wrote:

I have found useful the use of variable assignment in psql, e.g.

#!/bin/sh

# lets say you have some var with a value, or even populate some var with a value from
# psql as shown below
somevar=`psql -P pager=off -q -t -c "SELECT foo from bar" | head -1 | sed -e 's/ //g'`

# now use that variable in psql, (what you want to achieve), but in more tight manner
# than simple shell substitution (see -v switch and : notation)

psql -P pager=off -q -v somevar=$somevar -c "select foo2 from bar2 where var=:somevar"

At this point, I have to ask: Why not switch to a language with actual
Postgres bindings? Try Python, or Pike, or something; I'm sure it's
going to be easier than doing everything through shell scripts.

or perl, or php, or java, etc...
actually we "switched" to java some 11 years ago to build our infrastructure,
but occasionally (or not so occasionally, but rather being part of the architecture)
still sh/bach/tcsh/perl are heavily used and have their place.
Its all about taste/preference and not easily jumping into overkill mode.

OTOH the OP asked for doing exactly what -v (--set) var assignments are supposed to do.
(lift shell substitution ambiguities about escaping and var expansion)

ChrisA

-
Achilleas Mantzios
IT DEPT

On Παρ 31 Αυγ 2012 09:37:05 Craig Ringer wrote:

On 08/30/2012 02:42 PM, Achilleas Mantzios wrote:

I have found useful the use of variable assignment in psql, e.g.

If you're going to to that, why not drive psql as a coprocess:

because it is completely irrelevant with what the OP asked for.

http://stackoverflow.com/a/8305578/398670

or if at all possible, use a language with sane PostgreSQL bindings.

--
Craig Ringer

-
Achilleas Mantzios
IT DEPT

On Παρ 31 Αυγ 2012 09:19:26 Chris Angelico wrote:

On Thu, Aug 30, 2012 at 4:42 PM, Achilleas Mantzios
<achill@smadev.internal.net> wrote:

I have found useful the use of variable assignment in psql, e.g.

#!/bin/sh

# lets say you have some var with a value, or even populate some var with a value from
# psql as shown below
somevar=`psql -P pager=off -q -t -c "SELECT foo from bar" | head -1 | sed -e 's/ //g'`

# now use that variable in psql, (what you want to achieve), but in more tight manner
# than simple shell substitution (see -v switch and : notation)

psql -P pager=off -q -v somevar=$somevar -c "select foo2 from bar2 where var=:somevar"

At this point, I have to ask: Why not switch to a language with actual
Postgres bindings? Try Python, or Pike, or something; I'm sure it's
going to be easier than doing everything through shell scripts.

or perl, or php, or java, etc...
actually we "switched" to java some 11 years ago to build our infrastructure,
but occasionally (or not so occasionally, but rather being part of the architecture)
still sh/bach/tcsh/perl are heavily used and have their place.
Its all about taste/preference and not easily jumping into overkill mode.

OTOH the OP asked for doing exactly what -v (--set) var assignments are supposed to do.
(lift shell substitution ambiguities about escaping and var expansion)

ChrisA

-
Achilleas Mantzios
IT DEPT

On Παρ 31 Αυγ 2012 09:37:05 Craig Ringer wrote:

On 08/30/2012 02:42 PM, Achilleas Mantzios wrote:

I have found useful the use of variable assignment in psql, e.g.

If you're going to to that, why not drive psql as a coprocess:

because it is completely irrelevant with what the OP asked for.

http://stackoverflow.com/a/8305578/398670

or if at all possible, use a language with sane PostgreSQL bindings.

--
Craig Ringer

-
Achilleas Mantzios
IT DEPT