Thread: 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/> | 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/> 
>

-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
isdone 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/> | 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/> 
>

-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
isdone 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/> | 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/> 
> >
>
> -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