8

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!

Improve this question
2
  • 1
    The effort needed to do that sort of thing with bash is better spent learning Perl basics. Commented Dec 2, 2013 at 12:38
  • Before trying to write anything yourself you have to realize what you are doing here: Executing a command and storing its output(text) in a variable. Therefore you would need to parse and disassemble that text in order to make this thing work. Commented Dec 20, 2013 at 7:43

4 Answers 4

Reset to default
9

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
Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

If you'd like to only iterate over the values (and not the header describing the column) add a -t to the psql command.
Am I doing something wrong I just get the entire row to end up in ${Record[0]}. It looks like 1|ed393959-6c5f-4f79-a9b9-11516c3c8990|Recordblabla|2021-06-16
8

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

Improve this answer

1 Comment

Get a strange error on above approach trying to pipe the psql output to while: line 18: syntax error at line 31: `|' unexpected For reference the relevant line: ``` $PSQL \ -X \ -h $host \ -U $username \ -c "$id_sql" \ --single-transaction \ --set AUTOCOMMIT=off \ --set ON_ERROR_STOP=on \ --no-align \ -t \ --field-separator ' ' \ --dbname $dbname \ --quiet | while read id_num name ; do ```
0

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.

Improve this answer

Comments

-2

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.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.