Postgres how do return a message if no results for SELECT query

Question

Have been trying to work out how to return a message if the result is empty.

I am running a select statement as below:

select * from maxIDValue where max_value > 1000000 order by max_value desc;

I am running this in a bash script via psql however the results will be sent out in an email which I have working.

What I want to know is if the results return nothing could I have it so a message is at least inputted instead of (0 rows), even if it was just a message to say there are no Values or something to that extent.

I have tried via bash also: Note: the .sql file is the above query

{
if [ "$( psql -h $SERVERNAME -U test -d Titan -tAf "/home/postgres/tools/testmaxresults.sql" )" = '1' ]
then
    psql -h ${SERVERNAME} -U test -d Titan -f /home/postgres/tools/testmaxresults.sql >>${TMPFILE01}
else
    echo "No Value Exists" >>${TMPFILE01}
fi
}

To sum up

I want the bash to run the script and if there is data i.e results then run and report (the reporting part works in terms of email etc)

But the select runs and there is 0 results then print/echo "No Values"

I want to run this query against multiple databases. How would I have this enclosed in a bash script so I can run this against each database.

If I run this it only reports the last database as opposed to both.

Any help is appreciated.

Answer 1

An easy psql solution would be to reinject the :ROW_COUNT psql variable immediately after the query into another query to return the "No value exists" text when :ROW_COUNT is zero.

Example:

psql -At <<EOF

select * from pg_class where false; -- sample query that returns nothing

select case when :ROW_COUNT = 0 then 'The previous query returned nothing' end;

EOF

Either the first query will cause :ROW_COUNT to be non-zero and the second query will not output anything, or :ROW_COUNT is zero and the second query will output the text.

---

Another solution outside of psql (say for some reason you can't change the SQL script) would be to redirect the results into a dedicated file, then test if it's empty, and if yes, put the "No rows" text into it. With bash:

tmpf=$(mktemp)
psql -Atf script.sql > $tmpf
if [[ ! -s $tmpf ]]; then echo >$tmpf "No rows"; fi
cat $tmpf >> $main_output_file
rm $tmpf