psql variables

On Mon, 14 Mar 2005 11:06:32 -0500, Paul Cunningham
<paul@blueseacommunications.com> wrote:

I use a bash script (similar to following example) to update tables.

psql -v passed_in_var=\'some_value\' -f script_name ....

Is it possible to pass a value back from psql to the bash script?

You can use '\! [ command ]' to execute shell commands within psql.
This may not be what you want though.

George Essig

In article <4235B688.8000407@blueseacommunications.com>,
Paul Cunningham <paul@blueseacommunications.com> wrote:
% I use a bash script (similar to following example) to update tables.
%
% psql -v passed_in_var=\'some_value\' -f script_name ....
%
% Is it possible to pass a value back from psql to the bash script?

If you run it like this

$(psql -v passed_in_var=\'some_value\' -f script_name)

and arrange for all the script output to be in the form

var1="value 1" var2="value 2"
var3="value 3" ...

then var1, var2, etc will be set in bash (or ksh, or the posix shell).
Note that there are no spaces around the equals signs.
--

Patrick TJ McPhee
North York Canada
ptjm@interlog.com

On Fri, 25 Mar 2005, Patrick TJ McPhee wrote:

In article <4235B688.8000407@blueseacommunications.com>,
Paul Cunningham <paul@blueseacommunications.com> wrote:
% I use a bash script (similar to following example) to update tables.
%
% psql -v passed_in_var=\'some_value\' -f script_name ....
%
% Is it possible to pass a value back from psql to the bash script?

If you run it like this

$(psql -v passed_in_var=\'some_value\' -f script_name)

and arrange for all the script output to be in the form

var1="value 1" var2="value 2"
var3="value 3" ...

then var1, var2, etc will be set in bash (or ksh, or the posix shell).
Note that there are no spaces around the equals signs.

btw, how to expand psql variable in -c "" ?
For example, this works as expected
psql discovery-test -v a1=\'message_parts\' -f tt.sql
but not
psql discovery-test -v a1=\'message_parts\' -c "select * from iostat where relname=:a1;"
ERROR: parse error at or near ":" at character 36
LINE 1: select * from iostat where relname=:a1;

Regards,
Oleg
_____________________________________________________________
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83