sql: support DO statement for running running anonymous code blocks

[giangpham712]

Describe the problem

Running the following SQL

DO $EF$
BEGIN
    IF NOT EXISTS(SELECT 1 FROM pg_namespace WHERE nspname = 'test') THEN
        CREATE SCHEMA test;
    END IF;
END $EF$;

will return error ERROR: at or near "do": syntax error

See the Postgres documentation for more details on the DO statement.

Additional context

What was the impact?

This issue is affecting a number of tests in efcore.pg library

Jira issue: CRDB-30000

[blathers-crl]

Hello, I am Blathers. I am here to help you get the issue triaged.

Hoot - a bug! Though bugs are the bane of my existence, rest assured the wretched thing will get the best of care here.

I have CC'd a few people who may be able to assist you:

• @cockroachdb/sql-foundations (found keywords: pg_)

If we have not gotten back to your issue within a few business days, you can try the following:

• Join our community slack channel and ask on #cockroachdb.
• Try find someone from here if you know they worked closely on the area and CC them.

_{🦉 Hoot! I am a Blathers, a bot for CockroachDB. My owner is dev-inf.}

[rafiss]

Tagging the SQL Queries team, as this may be related to ongoing work with plpgsql. (And there may already be a different issue to track this)

[Enalmada]

Could we get an update here? Drizzle ORM migrations output DO for duplicate_object checking and I don't believe that is going to change anytime soon. Supporting this would go a long way toward being able to get Drizzle users to seriously consider CockroachDB as a viable option.

DO $$ BEGIN
 CREATE TYPE "status" AS ENUM('ACTIVE', 'COMPLETED');
EXCEPTION
 WHEN duplicate_object THEN null;
END $$;

[mgartner]

Thanks for the feedback @Enalmada. In v23.2 we're supporting PLpgSQL functions and procedures for the first time, which brings us a step closer towards supporting anonymous code blocks. We'll keep this issue updated with more information when we have it.