SQL select return into PSQL variables.
Hello,
I want to get a sql select output into a psql variable. Any ideas how I might need to do this.
My script executes a function which returns a TESTID. I'd like to imbed the testid in the script output filenames.
I see that psql can set environment variables with the psql \i command.
But got any idea how I can get sql output into a psql variable?
Thanks
Doug
psql
orbitz=# \!testvar=1234
orbitz=# \!export testvar
orbitz=# \!echo $testvar
1234
orbitz=# \q
-bash-3.00$ echo $testvar
1234
But I'm unsure how I can get the pgsql return code into a psql variable
The shell script
current
psql p1gp1 <<QUIT >>$LOGFile 2>&1
\set ON_ERROR_STOP
select da_test.QATestBuild(false)
QUIT
I'd like to do something like this from my shell script
psql p1gp1 <<QUIT >>$LOGFile 2>&1
\set ON_ERROR_STOP
select da_test.QATestBuild(false) into :testid
\!testid=:testid
\!export $testid
QUIT
Echo $testid
1234
Doug Little
Sr. Data Warehouse Architect | Enterprise Data Management | 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:image002.jpg@01CAB07C.769E4E80] 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/>
Attachments:
On Thu, Feb 18, 2010 at 10:33 AM, Little, Douglas <DOUGLAS.LITTLE@orbitz.com
wrote:
psql
orbitz=# \!testvar=1234
orbitz=# \!export testvar
orbitz=# \!echo $testvar
1234
orbitz=# \q
-bash-3.00$ echo
$testvar1234
What shell are you using that allows a child process to alter the parent
process' environment? ohhhhh. you must be on windows.... this is not
normal unix behavior: the child process (psql) cannot alter the parent
(shell) environment, and every \! command you run fires a new subshell. On
unix you see this:
[yertle]% psql
Timing is on.
Welcome to psql 8.3.9 (server 8.3.7), the PostgreSQL interactive terminal.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit
khera=> \!testvar=1234
khera=> \!export testvar
khera=> \!echo $testvar
khera=> \q
[yertle]% echo $testvar
testvar: Undefined variable.
[yertle]%
On Thu, 2010-02-18 at 09:33 -0600, Little, Douglas wrote:
psql p1gp1 <<QUIT >>$LOGFile 2>&1
\set ON_ERROR_STOP
select da_test.QATestBuild(false)
QUIT
mod to your needs...
$ cat dummy.sql
#MYTESTID=`psql -t -c "select da_test.QATestBuild(false)" dbname`
MYTS=`psql -t -c "select to_char(now(), 'YYYYMMDDHH24MISS')" test |sed 's/^ //'`
#echo "[$MYTS]"
fn=`basename $0`
LOGFile=${fn}_${MYTS}.log
psql test <<QUIT >>$LOGFile 2>&1
\set ON_ERROR_STOP
select * from stores;
QUIT