1

If I execute single SQL statements in worksheet (eg CREATE ROLE my_user LOGIN PASSWORD 'my_pwd' VALID UNTIL 'infinity';) then is works correctly. Wrapping it in anonymous block like this:

DO
$$
BEGIN 
  CREATE ROLE my_user LOGIN PASSWORD 'my_pwd' VALID UNTIL 'infinity';
END
$$;

and I get following error message:

Error starting at line : 3 in command -
BEGIN 
  CREATE ROLE my_user LOGIN PASSWORD 'my_pwd' VALID UNTIL 'infinity';
END
$$;
Error report -
ERROR: syntax error at or near "CREATE"
  Position: 10

However, the same script works fine when I execute it in psql shell. Am I missing something obvious?

Using:
PostgreSQL version 9.4
Postgres JDBC driver postgresql-9.3-1102.jdbc41
SQL Developer version 4.0 (jdk 1.7.0_71)

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
Ragnar
  • 1,387
  • 4
  • 20
  • 29
  • 1
    Did you try a `/` on a single line after the block? I don't think **Oracle** SQL Developer understands Postgres' dollar quoting. So you might also try to use regular single quotes instead of `$$` (of course you need to double the embedded single quotes then) –  Jan 05 '15 at 12:47
  • Tried and got the same result. – Ragnar Jan 05 '15 at 13:46
  • 1
    Then I guess Oracle's SQL Developer is not a good choice for working with Postgres –  Jan 05 '15 at 14:20

1 Answers1

1

The source of the error is JDBC's inability to deal with dollar-quoting correctly (yet). Related answer:

You might be able to circumvent the problem in this case with:

DO
'
BEGIN 
  CREATE ROLE my_user LOGIN PASSWORD ''my_pwd'' VALID UNTIL ''infinity'';
END
';

If that doesn't do the trick, try to set a different query terminator, like advised in the linked answer.

You seem to be aware that you do not need a DO statement for the example code at all. Just:

CREATE ROLE my_user LOGIN PASSWORD 'my_pwd' VALID UNTIL 'infinity';
Community
  • 1
  • 1
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • I used single statement only for this example but actually I need to combine several statements. For example: `BEGIN IF NOT EXISTS (SELECT 1 FROM pg_roles WHERE rolname = ''my_user'') THEN CREATE ROLE my_user LOGIN PASSWORD ''my_pwd'' VALID UNTIL ''infinity''; END IF; END`. I tried what You suggested and it still didn't work `ERROR: syntax error at or near "CREATE"` – Ragnar Jan 07 '15 at 09:46
  • @Ragnar: I would then try to set a different query terminator. See added note in the answer and also the [linked answer](http://stackoverflow.com/questions/22747225/exceptions-when-creating-a-trigger-in-postgresql-9-1/22748778#22748778). – Erwin Brandstetter Jan 09 '15 at 03:38