Using shell script store PostgreSQL query on a variable

Question

I want to store following postgreSQL query result in a variable. I am writing command on the shell script.

psql -p $port -c "select pg_relation_size ('tableName')" postgres

I need variable to save the result on a file. I have tried following but it is not working

var= 'psql -p $port -c "select pg_relation_size ('tableName')" '

please don't forget to accept an answer or ask for more information. — Eduard Gamonal
– Eduard Gamonal, Commented May 11, 2014 at 16:22

joop · Accepted Answer · 2013-09-27 08:34:41Z

5

Use a shell HERE document like:

#!/bin/sh
COUNT=`psql -A -t -q -U username mydb << THE_END
SELECT count (DISTINCT topic_id) AS the_count
FROM react
THE_END`

echo COUNT=${COUNT}

The whole psql <<the_end ... stuff here ... the_end statement is packed into backticks
the output of the execution of the statement inside the backticks is used as a value for the COUNT shell variable
The -A -t -q are needed to suppress column headers and error output
inside a here document, shell variable substitution works, even in single quotes!

So, you could even do:

#!/bin/sh

DB_NAME="my_db"
USR_NAME="my_name"
TBL_NAME="my_table"
COL_NAME="my_column"

COUNT=`psql -A -t -q -U ${USR_NAME} ${DB_NAME} << THE_END
SELECT COUNT(DISTINCT ${COL_NAME} ) AS the_count
FROM ${TBL_NAME}
THE_END`

echo COUNT=${COUNT}

edited Sep 27, 2013 at 8:34

answered Sep 27, 2013 at 8:26

joop

4,5431 gold badge18 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Eduard Gamonal · Accepted Answer · 2013-09-27 06:22:52Z

to run a query inline you have to wrap it in grave accents, not single quotes:

 $ vim `which fancyexecfileinpath`

psql lets you run queries from command line, but I guess you should be inputting complete information. you might be missing the database name.

postgres@slovenia:~$ psql -d mydbname -c "select * from applications_application;" 
postgres@slovenia:~$ specialvar=`psql -d flango -c "select * from     applications_application;"`
postgres@slovenia:~$ echo $specialvar
id | name | entities | folder | def_lang_id | ... | 2013-07-09 15:16:57.33656+02 | /img/app3.png (1 row)
postgres@slovenia:~$

notice the grave accents when assigning it to specialvar otherwise you'll be setting it to a string. There shouldn't be any space between the variable and the equals sign ("=") and the value ( http://genepath.med.harvard.edu/mw/Bash:HOW_TO:_Set_an_environment_variable_in_the_bash_shell )

Collectives™ on Stack Overflow

Using shell script store PostgreSQL query on a variable

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related