CODEWITHSUNDEEP
postgresqlansibleansible-2.x
scoder
scoder

Reputation: 2611

ansible suddenly gives syntax error when running sql files

I have an ansible role which runs the few queries from an sql file

- name: sample query
  postgresql_query:
    db: test_db
    path_to_script: /tmp/test.sql

And the file contains (I have just added one query to show where it is failing )

DO
$do$
BEGIN
   IF NOT EXISTS ( SELECT FROM pg_roles  
                   WHERE  rolname = 'my_user') THEN

      CREATE USER myuser NOLOGIN;
      GRANT CONNECT on DATABSE myDataBase to myuser;
      GRANT USAGE ON SCHEAMA myschema to myuser;
   END IF;
END
$do$;

It used to work well but suddenly I started getting syntax issue " ansible postgresql_query unterminated dollar-quoted string at or near \"$$\begin\"

The same untouched file used to work but now has stopped. I run this from a remote machine where ansible and other installation is controlled by an admin.

Are there any changes in ansible settings or version that could explain this? If so, how can I force postgress_query to treat my multiple line query as a single one (something like setSingleline=false)?

Upvotes: 1

Views: 590

Answers (1)

Tobias Ernst
Tobias Ernst

Reputation: 4654

Using postgresql_db instead of postgresql_query worked out for ansible>3.

Working solution for ansible>3:

- postgresql_db:
    db: "your-db"
    state: restore
    ...
    target: "/path/to/script-containing-dollar-quotes.sql"

Working solution for ansible<3:

- postgresql_query:
    db: "your-db"
    ...
    path_to_script: "/path/to/script-containing-dollar-quotes.sql"

Upvotes: 1

Related Questions