Add a DO command

[lukaseder]

In PostgreSQL, we can run anonymous blocks using the DO command, for example:

DO $$
BEGIN
  FOR i IN 1 .. 10 LOOP
    RAISE INFO '%', i;
  END LOOP;
END;
$$

H2 seems to have taken inspiration by PostgreSQL by allowing for "procedural logic" being embedded in $$ quoted strings. There are examples in the grammar when creating aliases or triggers. However, there seems to be no way to run an anonymous block on the fly, which can be useful for migrations:

DO $$
for (int i = 1; i <= 10; i++)
  System.out.println(i);
$$

[lukaseder]

This could even be implemented purely as syntax sugar. The above example could be mapped to the following logic:

CREATE ALIAS x AS $$
void x() {
  for (int i = 1; i <= 10; i++)
    System.out.println(i);
}
$$;

CALL x();

DROP ALIAS x;

Where x would have to be a name that never causes any conflicts, including with other such aliases

[ebocher]

What about using groovy to emulate a procedural language in H2 ? Something like that CREATE ALIAS X AS GROOVY

[grandinj]

we already support groovy

[ebocher]

oupss slipped under the radar sorry

[lukaseder]

@ebocher The content of the DO $$ ... $$ statement shouldn't matter. The idea is that any such content can be run anonymously, without giving it a name first.

[katzyn]

I does matter, because Java etc. code may only be allowed for users with ADMIN permission for security reasons, we can't control what such code does.

[lukaseder]

Sure, it matters in general, but probably not for the scope of this issue. A database user that can CREATE ALIAS and CALL ... should probably also be able to run DO with the same rules, if you do decide to add such a DO command.

[stokito]

For me is not clear why to use dollars DO $$ if inside the same SQL statements.

Also FireBird also has anon block but with some more verbose syntax EXECUTE BLOCK

[katzyn]

Because it's just a string literal with the Java, Groovy, etc. code. Standard literals ('string') aren't very usable here, because ' can be used in the source code frequently. Non-standard $$-wrapped literals are more useful.

[lukaseder]

I just picked the syntax from PostgreSQL https://www.postgresql.org/docs/current/sql-syntax-lexical.html

... which in fact also allows for placing tags between the dollars. This is also astring literal:

select $abc$hello world$abc$

It works like other languages' heredoc