CODEWITHSUNDEEP
sqlbashpostgresql
Robbie Torrens
Robbie Torrens

Reputation: 125

Bash script & PostgreSQL: How to access column values returned from SELECT statement

I have a database in PostgreSQL called customers, customers has a table called CustomerInfo. CustomerInfo contains 3 columns ID, Name and address. I would like to write a bash script to get the information from the CustomerInfo table but i am not sure how to access the individual rows once i have the results of the query. Here is the script i have written:

#!/bin/bash  

results=`psql -d customers -c "select * from CustomerInfo where name = 'Dave'"`

echo $results['name']

The query runs correctly and returns the correct results but the echo command will just print everything in results. I know this is not the correct way of doing this, does anyone know of a way to get the query results as an array, or would i just have to write my own function for parsing the results?

Thanks!

Upvotes: 8

Views: 17460

Answers (4)

yokeho
yokeho

Reputation: 177

I had success using the 

psql | while read do

approach:

$PG_HOME/bin/psql \
-h 127.0.0.1 \
-U $DB_USER \
-c 'SELECT recipient_email, name FROM report_recipients' \
--set ON_ERROR_STOP=on \
--no-align \
--quiet \
--no-password \
--tuples-only \
--field-separator ' ' \
--pset footer=off \
--dbname=$DATABASE \
| while read EMAIL_ADDRESS NAME   ; do
    echo "$SCRIPT: addr=$EMAIL_ADDRESS  name=$NAME"
done

There is a great resource for this and similar things at: manniwood.com's PostgreSQL and bash Stuff page

Upvotes: 8

bckim
bckim

Reputation: 91

You can store your results into an array and loop through it using a while loop.

psql -d customers -c "select * from CustomerInfo where name = 'Dave'"
| while read -a Record ; do
    # ${Record[0]} is your ID field
    # ${Record[1]} is your Name field
    # ${Record[2]} is your address field
done

Upvotes: 9

Chris Travers
Chris Travers

Reputation: 26474

If I were tasked with doing this (really, Perl is better, take the advice of @daniel-verite), here is what I would do.

  1. Get a list of column names by piping the initial results through head
  2. Create a function which parses the rest using awk into a useful format.
  3. sed or awk to extract the relevant information from a row.

Now, the above assumes no embedded newlines (which would complicate things)

This amount of effort is anything but trivial. You really are better off learning Perl for a task like this.

Upvotes: -2

sat
sat

Reputation: 14979

You can't. You have to write your own function for paring the query results. Backtick( ` ) will execute the command and returns the output. In your case, results will have output of your query.

Upvotes: 0

Related Questions