How to iterate over SQL result in shell script
We sometimes need to iterate SQL result in shell script to do something. For example, if we need to re-format data or need to run a Linux command against SQL result for each record, we need to iterate SQL result in shell script. In this article, we are going to run a SQL in PostgreSQL and get the result, then iterate them.Check SQL and its result
First off, let's see how SQL and its result look like.The following SQL gets a list of PostgreSQL system tables by querying "pg_tables" table.
Below is the SQL and its result. We are going to iterate the result in shell script.
=> SELECT schemaname, tablename FROM pg_tables WHERE schemaname = 'pg_catalog' ORDER BY schemaname, tablename LIMIT 10; ------------+----------------- schemaname | tablename ------------+----------------- pg_catalog | pg_aggregate pg_catalog | pg_am pg_catalog | pg_amop pg_catalog | pg_amproc pg_catalog | pg_attrdef pg_catalog | pg_attribute pg_catalog | pg_auth_members pg_catalog | pg_authid pg_catalog | pg_cast pg_catalog | pg_class ------------+----------------- (10 rows)
Create a shell script
The following shell script iterates SQL result records. Please create "test.sh" with the following contents.#!/bin/bash
SQL=$(cat << EOD
SELECT
schemaname,
tablename
FROM pg_tables
WHERE schemaname = 'pg_catalog'
ORDER BY
schemaname,
tablename
LIMIT 10
EOD
)
echo ${SQL} | psql \
-h 127.0.0.1 \
-p 5432 \
-d hoge \
-U postgres \
--no-align \
-t \
--field-separator '|' \
-q \
| while IFS='|' read schemaname tablename ; do
cat <<EOD
=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=
[Table Information]
schemaname : ${schemaname}
tablename : ${tablename}
EOD
done
What the script does is that run SQL and get the result, then the result is passed to pipe (|), so that the result is iterated.
Run shell script
Once we created the test.sh script, let's run. As a result of that, we can see how data is processed one by one in the shell script.$ chmod 744 ./test.sh $ ./test.sh =+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+= [Table Information] schemaname : pg_catalog tablename : pg_statistic =+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+= [Table Information] schemaname : pg_catalog tablename : pg_type =+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+= [Table Information] schemaname : pg_catalog tablename : pg_attribute =+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+= [Table Information] schemaname : pg_catalog tablename : pg_authid =+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+= [Table Information] schemaname : pg_catalog tablename : pg_proc =+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+= [Table Information] schemaname : pg_catalog tablename : pg_class =+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+= [Table Information] schemaname : pg_catalog tablename : pg_user_mapping =+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+= [Table Information] schemaname : pg_catalog tablename : pg_constraint =+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+= [Table Information] schemaname : pg_catalog tablename : pg_inherits =+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+= [Table Information] schemaname : pg_catalog tablename : pg_index
Please modify the shell script based on a requirement. We are also able to run like the shell script through cron job.
