6

I'm trying to declare some variables using DBeaver and keep hitting this error.

Unterminated dollar-quoted string at or near "$$

 DO $$
 DECLARE A integer; B integer;

BEGIN   
END$$;

Any ideas?

user1158745
  • 2,402
  • 9
  • 41
  • 60
  • http://www.postgresql.org/docs/current/static/sql-do.html and http://www.postgresql.org/docs/9.4/static/sql-syntax-lexical.html#SQL-SYNTAX-DOLLAR-QUOTING – Milen A. Radev Sep 17 '15 at 15:55

4 Answers4

5

DBeaver was the issue. Switched to PGAdmin and no more problems.

user1158745
  • 2,402
  • 9
  • 41
  • 60
4

As of DBeaver 6, you can execute the script with ALT-X (on Windows), which does not attempt to do variable capture/interpolation involving dollar signs.

Hobotron
  • 51
  • 2
  • According to the icons in the editor, alt+x executes the whole script. This is the easier solution, but you can also highlight the entire query and execute the single statement and it won't give any errors. – GammaGames Oct 28 '19 at 14:34
1

The syntax posted is fine. Your problem is that the client application or driver is mangling the query, probably because it doesn't understand dollar-quoting.

It might be trying to split it into separate statements on semicolons, running:

  • DO $$ DECLARE A integer;
  • B integer;
  • BEGIN END$$;

as three separate statements. This would result in the reported error, e.g.

$ psql -c 'DO $$ DECLARE A integer;'
ERROR:  unterminated dollar-quoted string at or near "$$ DECLARE A integer;"
LINE 1: DO $$ DECLARE A integer;
           ^

This is why you must specify your client driver/application when asking questions.

Another possibility with some clients is that it might treat $ as an escaped query-parameter placeholder and replace it with a single $ or try to substitute it for a server-side placeholder like $1. That's not what's happening here, though.

Craig Ringer
  • 307,061
  • 76
  • 688
  • 778
0

DBeaver also gives this error when there is a SQL syntax error in the script. In my case, it was a pair of mismatched parenthesis in a select calculated column.

Joel MC
  • 107
  • 2
  • 7