DO

Synopsis

Use the DO statement to execute an anonymous PL/pgSQL block statement—in other words, a transient anonymous PL/pgSQL procedure. The plpgsql_block_stmt is treated as though it were the body of a procedure with no parameters:

• Any SQL statement that the block statement encounters is treated in the same way as when it is encountered in a language plpgsql subprogram so that if a DO statement is repeatedly executed in the same session, using the textually identical block statement, then the second and subsequent executions of the contained SQL statement benefit from the syntax and semantics analysis that was done when it was first encountered.

The grammar allows an optional LANGUAGE clause that can be written either before or after the code block. However, the only supported choice is language plpgsql. For example, an attempt to execute a DO statement that specified language sql causes the 0A000 run-time error:

language "sql" does not support inline code execution

See the thread on the pgsql-general email list, Why can't I have a "language sql" anonymous block?

Syntax

• Grammar
• Diagram

do ::= DO ' plpgsql_block_stmt '

Semantics

plpgsql_block_stmt

The procedural language code to be executed, plpgsql_block_stmt. This must be specified as a string literal, just as in CREATE FUNCTION and CREATE PROCEDURE. Yugabyte recommends that you use dollar-quoting and standardize on, for example, $body$.

lang_name

Specifies the name of the procedural language that the code is written in. The default is plpgsql. See the tip Avoid using the optional 'LANGUAGE' clause in a 'DO' statement, above. This code is legal. And it runs without error and has the intended effect.

do
  language plpgsql
$body$
begin
  raise info 'Block statement started at %',
    to_char((statement_timestamp() at time zone 'UTC'), 'hh24:mi:ss Dy');
end;
$body$;

And the effect is identical if language plpgsql is omitted.

Notes

See the section Issuing "commit" in user-defined subprograms and anonymous blocks.

Example

do $body$
begin
  drop schema if exists s cascade;
  create schema s;

  create table s.masters(
    mk serial primary key,
    mv text not null unique);

  create table s.details(
    dk serial primary key,
    mk int not null references s.masters(mk),
    dv text not null);
end;
$body$;

Suppose that at the moment the DO statement is executed, the schema s already exists but is owned by a user other than what the current_role built-in function returns (and that this current role is not a superuser). Assume, too, that there is currently no ongoing transaction so that the block statement is executed in single statement automatic transaction mode (see the section Semantics of issuing non-transaction-control SQL statements when no transaction is ongoing.)

The drop schema if exists s cascade attempt will cause the 42501 error:

must be owner of schema s

The block will then exit immediately with an unhandled exception and the run-time system will automatically issue an under-the-covers commit—which will have the same effect, here, as rollback. Compare this behavior with that of encapsulating the same statements in an explicit start transaction; ... commit; encapsulation to use multistatement manual transaction mode:

start transaction;
  drop schema if exists s cascade;
  create schema s;

  create table s.masters(
    mk serial primary key,
    mv text not null unique);

  create table s.details(
    dk serial primary key,
    mk int not null references s.masters(mk),
    dv text not null);
commit;

Now four errors are reported: first, the 42501 error is reported, just as when the DO statement is executed in single statement automatic transaction mode; but then three occurrences of the 25P02 error (current transaction is aborted, commands ignored until end of transaction block) are reported.

The DO statement approach therefore provides the better encapsulation for the four-statement implementation of the business requirement than does the start transaction; ... commit; approach.