How to execute plpgsql anonymous block in Oracle SQL Developer?

Question

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)

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) — user330315
– user330315, Commented Jan 5, 2015 at 12:47
Then I guess Oracle's SQL Developer is not a good choice for working with Postgres — user330315
– user330315, Commented Jan 5, 2015 at 14:20

Community · Accepted Answer · 2017-05-23 12:12:49Z

1

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

Exceptions when creating a trigger in PostgreSQL 9.1

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';

edited May 23, 2017 at 12:12

CommunityBot

11 silver badge

answered Jan 7, 2015 at 2:41

Erwin Brandstetter

668k159 gold badges1.2k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Ragnar Over a year ago

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"

Erwin Brandstetter Over a year ago

@Ragnar: I would then try to set a different query terminator. See added note in the answer and also the linked answer.

Collectives™ on Stack Overflow

How to execute plpgsql anonymous block in Oracle SQL Developer?

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related