Postgres Pro
Downloads
Home   >   mailing lists

Thread: SQL select return into PSQL variables.

SQL select return into PSQL variables.

From
"Little, Douglas"
Date:

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

   orbitz.com | ebookers.com | hotelclub.com | cheaptickets.com | ratestogo.com | asiahotels.com 

 

Attachment

Re: SQL select return into PSQL variables.

From
Vick Khera
Date:
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 $testvar                                                                                                                                    

1234

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]% 

Re: SQL select return into PSQL variables.

From
Reid Thompson
Date:
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