Re: [GENERAL] psql unix env variables
On Î Î±Ï 31 ÎÏ Î³ 2012 09:19:26 Chris Angelico wrote: On Thu, Aug 30, 2012 at 4:42 PM, Achilleas Mantzios ach...@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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] psql unix env variables
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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] psql unix env variables
On Î Î±Ï 31 ÎÏ Î³ 2012 09:19:26 Chris Angelico wrote: On Thu, Aug 30, 2012 at 4:42 PM, Achilleas Mantzios ach...@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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] psql unix env variables
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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] psql unix env variables
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:rpkell...@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.lit...@orbitz.commailto:douglas.lit...@orbitz.com [cid:image001.jpg@01CD85E2.C7732E50] orbitz.comhttp://www.orbitz.com/ | ebookers.comhttp://www.ebookers.com/ | hotelclub.comhttp://www.hotelclub.com/ | cheaptickets.comhttp://www.cheaptickets.com/ | ratestogo.comhttp://www.ratestogo.com/ | asiahotels.comhttp://www.asiahotels.com/ -Ryan Kelly - Achilleas Mantzios IT DEPT -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] psql unix env variables
On Thu, Aug 30, 2012 at 4:42 PM, Achilleas Mantzios ach...@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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] psql unix env variables
On 08/30/2012 04:19 PM, Chris Angelico wrote: On Thu, Aug 30, 2012 at 4:42 PM, Achilleas Mantzios ach...@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.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] psql unix env variables
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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] 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 douglas.lit...@orbitz.commailto:douglas.lit...@orbitz.com [cid:image001.jpg@01CD85E2.C7732E50] orbitz.comhttp://www.orbitz.com/ | ebookers.comhttp://www.ebookers.com/ | hotelclub.comhttp://www.hotelclub.com/ | cheaptickets.comhttp://www.cheaptickets.com/ | ratestogo.comhttp://www.ratestogo.com/ | asiahotels.comhttp://www.asiahotels.com/ inline: image001.jpg
Re: [GENERAL] 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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
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.lit...@orbitz.commailto:douglas.lit...@orbitz.com [cid:image001.jpg@01CD85E2.C7732E50] orbitz.comhttp://www.orbitz.com/ | ebookers.comhttp://www.ebookers.com/ | hotelclub.comhttp://www.hotelclub.com/ | cheaptickets.comhttp://www.cheaptickets.com/ | ratestogo.comhttp://www.ratestogo.com/ | asiahotels.comhttp://www.asiahotels.com/ -Ryan Kelly -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] psql unix env variables
Thanks -Original Message- From: Ryan Kelly [mailto:rpkell...@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.lit...@orbitz.commailto:douglas.lit...@orbitz.com [cid:image001.jpg@01CD85E2.C7732E50] orbitz.comhttp://www.orbitz.com/ | ebookers.comhttp://www.ebookers.com/ | hotelclub.comhttp://www.hotelclub.com/ | cheaptickets.comhttp://www.cheaptickets.com/ | ratestogo.comhttp://www.ratestogo.com/ | asiahotels.comhttp://www.asiahotels.com/ -Ryan Kelly -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
