2011/07/11

postgres tips: How to Execute PostgreSQL Commands Inside Unix Shell Scripts

I've used to use -c option (method1) when execute postgreSQL command inside unix shell scripts. However, method1 supports single postgreSQL command and requires backslash '\' when postgreSQL is written by multiple lines. On the contrary, method2 supports multiple command at a time and does not require any backslash 8^). I think method2 is better than what I used to do(method1)

Method1: Using -c option

#!/bin/sh

dbname="mydb"
dbuser="postgres"
table1="test1"
table2="test2"

psql -U ${dbuser} -d ${dbname} -c "
DROP TABLE ${table1};"
psql -U ${dbuser} -d ${dbname} -c "
DROP TABLE ${table2};"
psql -U ${dbuser} -d ${dbname} -c "
CREATE TABLE ${table1} \
(id int, \
name text);"

psql -U ${dbuser} -d ${dbname} -c "
CREATE TABLE ${table2} \
(id int, \
name text);"


Method2: Using EOF operator


#!/bin/sh

dbname="mydb"
dbuser="postgres"
table1="test1"
table2="test2"

psql -d ${dbname} -U ${dbuser} << EOF
DROP TABLE ${table1};
DROP TABLE ${table2};
CREATE TABLE ${table1}
(id int,
name text);
CREATE TABLE ${table2}
(id int,
name text);
EOF

No comments:

Post a Comment

100