Oh well, it happened again. I was crying of frustration and anger , bit into the desk and shouted wild curses through the office space. Reason for this was a small shell script which iterates through a list of database tables which should be dropped. To do so I called
psql with some arguments and a very simple "
DROP TABLE $i CASCADE;". This is how the initial attempt of the script looked like.
for i in $TABLES
do
${PSQL} -U ${PSQLUSER} -p ${PORT} -c "DROP TABLE ${i} CASCADE;" ${PGDB}
done
This exploded right into my face with
psql: warning: extra command-line argument "postgres.testtable" ignored
psql: warning: extra command-line argument "CASCADE;" ignored
psql: warning: extra command-line argument "postgres" ignored
psql: FATAL: database "TABLE" does not exist
Eeeek, of course it does that because BASH (in scripts,not command line) ignores my attempt to tell it that my SQL command is one argument and not four. The fact that used quotes is nice but futile. After a lot of try and only errors of escaping, quoting, variable encapsulating , forking shells, using eval and cursing I gave up and started to read documentation.
So finally the right way to do so ,without fiddling with $IFS, is to construct the command you want to run as an array. Following code works as expected.
for i in $TABLES
do
PSQLARGS=( -U ${PSQLUSER} -p ${PORT} -c )
PSQLARGS+=("DROP TABLE ${i} CASCADE;")
PSQLARGS+=(${PGDB})
${PSQL} "${PSQLARGS[@]}"
done
The first call of
PSQLARGS defines the array with five elements (separated by spaces), then I add the SQL command as only
one element to the array and finally the database. Now running
psql with the array of arguments results in a working version of the whole command line.
