Обсуждение: psql & unix env variables
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;’
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
orbitz.com | ebookers.com | hotelclub.com | cheaptickets.com | ratestogo.com | asiahotels.com
Вложения
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