Friday, March 26, 2010

How to Execute PostgreSQL Commands Inside Unix Shell Scripts


Question: How do I executed PostgreSQL Commands inside a Linux / UNIX shell script?
Answer: With the help of the psql interactive terminal, you can execute the psql commands from the shell script. For this purpose, you should enable the password less login by pg_hba.conf, or .pgpass.

Syntax

psql DBNAME USERNAME << EOF
statement 1;
statement 2;
.
.
statement n;
EOF

PostgreSQL: Executing SQL from shell scripts

Provide all the postgreSQL commands between the EOF block as shown below.
#!/bin/sh

dbname="test"
username="test"
psql $dbname $username << EOF
SELECT * FROM test;
EOF

PostgreSQL: Using variables in SQL from shell scripts

You can also use shell script variable inside the EOF block as shown below.
#!/bin/sh

dbname="test"
username="test"
wherecond="tgs"
psql $dbname $username << EOF
SELECT * FROM test WHERE col_name = '$wherecond';
EOF

No comments:

Post a Comment