PostgreSQL unterminated quoted identifier

Question

I have this groovy code which drops a remote postgres schema from commandline:

def dropSchema = "psql --dbname=postgres://$user:$pass@localhost:$port/$targetDb -c \"DROP SCHEMA ${applicationName}_${uid} CASCADE;\"".execute()

This code is working fine when it's run on a windows machine, but when it's on a Linux distribution, it gives me these errors:

psql: warning: extra command-line argument "appName_uid" ignored

psql: warning: extra command-line argument "CASCADE;"" ignored

ERROR: unterminated quoted identifier at or near ""DROP"

LINE 1: "DROP ^

Does anyone know how to fix this ?

Thanks.

Answer 1

Never ever use a string with .execute() like "ls 'my fancy file'".execute(). It splits on whitespace and that is most likely never what you want (same as ["ls", "'my", "fancy", "file'"].execute() for that example).

Also .execute() runs the command via the regular exec of your OS -- not the shell. So quoting or other things, that needs to be done for a shell command actually make things worse - since no shell is involved to interpret your intention.

Instead use an array, where all params are their own (don't quote for a shell, that is never used)

[
 "psql", 
 "--dbname=postgres://$user:$pass@localhost:$port/$targetDb", 
 "-c", "DROP SCHEMA ${applicationName}_${uid} CASCADE;"
].execute()

If you prefer to reuse an existing shell command, then run it with a shell:

["/bin/sh", "-c", "psql ... -c \"DROP ...\" ..."].execute()

Here you have to quote for the shell, as it is executed like a shell command.