Bash script to run SQL query and return result to variable

Question

I am running this bash script in order to connect to a PostgreSQL database, run some query, and simply check whether it returns an empty result or not. Then, I want to print out whether any rows were returned.

#!/bin/sh
DATABASE=dbname
USERNAME=user
HOSTNAME=somehost.com
export PGPASSWORD=password

queryResult () {
psql -h $HOSTNAME -U $USERNAME -d $DATABASE <<SQL
SELECT * FROM myTable WHERE valueA > 5.0 OR valueB > 5.0;
IF @@ROWCOUNT > 0 THEN 1 ELSE 0;
SQL
}

valuesFound=$(queryResult)

echo $valuesFound

There are two issues I'm having with this:

1) It stores the result of the first query (SELECT * FROM myTable...) into valuesFound and prints it, and I don't want that. All I care about is whether the IF statement returns 1 or 0.

2) The second query (IF @@ROWCOUNT...) throws a syntax error: Syntax error at or near IF

GMB · Accepted Answer · 2019-10-18 22:59:51Z

3

I think that your script can be simplified by moving the logic to a unique statement:

queryResult () {
    psql -h $HOSTNAME -U $USERNAME -d $DATABASE <<SQL
        SELECT CASE WHEN COUNT(*) = 0 THEN 0 ELSE 1 END
        FROM myTable 
        WHERE valueA > 5.0 OR valueB > 5.0;
    SQL
}

Or better yet, using EXISTS to avoid a potentially expensive aggregation:

queryResult () {
    psql -h $HOSTNAME -U $USERNAME -d $DATABASE <<SQL
        SELECT CASE WHEN EXISTS (
            SELECT 1 FROM myTable WHERE valueA > 5.0 OR valueB > 5.0
        ) THEN 1 ELSE 0 END;
    SQL
}

answered Oct 18, 2019 at 22:59

GMB

224k25 gold badges103 silver badges151 bronze badges

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

1 Comment

user-2147482428 Over a year ago

To anyone else who might have a similar question, edit the query as follows using the -t option in order to obtain only a tuple result: psql -h $HOSTNAME -U $USERNAME -d $DATABASE -t <<SQL ... SQL

Abelisto · Accepted Answer · 2019-10-19 06:36:55Z

1

IF @@ROWCOUNT > 0 THEN 1 ELSE 0; is T-SQL feature which PostgreSQL does not support.

The general solution could be:

myvar=$(psql -c "<SQL query>")
if [ -z "$myvar" ]; then
    # Do something when no data found
else
    # Data found, handle it
fi

answered Oct 19, 2019 at 6:36

Abelisto

15.8k3 gold badges38 silver badges47 bronze badges

1 Comment

Mesco Over a year ago

psql returns 0 error code when no error happen, it is not related to data found or not!

Collectives™ on Stack Overflow

Bash script to run SQL query and return result to variable

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related