0

I just learned about the meaning of dollar-quoting in PostgreSQL. Now I was wondering, how would I handle dollar-quoting when executing an SQL statement from psql commandline?

If possible, I would like to avoid using escaping with single quotes.

The statement I want to execute looks something like this:

sudo -u postgres psql -d db_name -c "CREATE FUNCTION my_function(name text)
  RETURNS void AS
$BODY$
        ...
$BODY$
  LANGUAGE plpgsql;"

But I get a syntax error at or near "$" and don't know how to handle this. I couldn't find any hint out there.

ulrich
  • 1,431
  • 3
  • 17
  • 46
  • 1
    That's probably `bash` expanding your dollars as variables. Try using single quotes (`-c 'CREATE ...`) or escaping the dollars (`\$BODY\$`) – Andomar Sep 05 '17 at 13:55
  • Wow, nice. That escaping with back-slash seems to work! – ulrich Sep 05 '17 at 14:00
  • 3
    This kind of problems can be avoided if you put the SQL into a script file and then run it using `psql -f create.sql` that is much more reliable. Plus: you can put that DDL script under version control as well. –  Sep 05 '17 at 14:08
  • @a_horse_with_no_name Yes, I will probably do this, which also came to my mind right now. I have the code unter version control anyway but that way will be much cleaner. – ulrich Sep 05 '17 at 14:13

0 Answers0