current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 1 down vote favorite
1

I am a novice bash script user. I am trying to execute this postgresql command (which outputs commands that drops tables whose name is like r_395)

SELECT 'DROP TABLE ' || tablename FROM pg_catalog.pg_tables where tablename like 'r_395%';

output of this query is:

?column?      
--------------------
 DROP TABLE r_395_0
 DROP TABLE r_395_1
 DROP TABLE r_395_2
 DROP TABLE r_395_3
 DROP TABLE r_395_4
 DROP TABLE r_395_5
 DROP TABLE r_395_6
 DROP TABLE r_395_7
 DROP TABLE r_395_8
 DROP TABLE r_395_9
(10 rows)

using bash with this command: 

/bin/su - postgres -c "/usr/bin/psql database -c SELECT 'DROP TABLE ' || tablename FROM pg_catalog.pg_tables where tablename like 'r_395%'" > droptables

But I am getting this error:

psql: FATAL:  role "DROP TABLE " does not exist
-bash: tablename: command not found

What am I doing wrong?

share|improve this question

1 Answer 1

up vote 2 down vote accepted

Probably due to the quotes being reinterpreted as you're including a -c. You need something like:

su - postgres -c "/usr/bin/psql database -c \"SELECT 'DROP TABLE ' || ....

i.e. you need to quote the arg to psql's -c option again.

This rapidly turns into a mess. I suggest you instead get the command you want psql to execute produced as output and then just have psql execute stdin (by not passing -c at all). This avoids the quoting hell, and makes testing easier (just take out the pipe to psql and you see what it will be getting). That is:

echo "SELECT 'DROP TABLE ' || tablename ... " | su - postgres -c "psql database"

or use a "here document":

su - postgres -c "psql database" <<EOF
SELECT 'DROP TABLE ' || tablename ...
EOF
share|improve this answer
    
thanks for your kind answer. I also realized that using backslashes solves my problem. But I couldn't answer my question because I needed to wait 8 hours according to Stackoverflow swh. But I learned from your answer a lot, thanks again. –  lamostreta Oct 14 '11 at 10:53

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.