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?
Avoid using the optional 'LANGUAGE' clause in a 'DO' statement.
Specifying language plpgsql brings no benefit with respect to omitting theLANGUAGE
clause altogether. You can assume that all developers know that the only supported implementation language is PL/pgSQL. Yugabyte therefore recommends that you avoid cluttering your code and simply always omit the optional LANGUAGE
clause.
Syntax
The syntax diagram omits the optional 'LANGUAGE' clause.
The syntax diagram respects the advice that the tip Avoid using the optional 'LANGUAGE' clause in a 'DO' statement, above, gives.do ::= DO ' plpgsql_block_stmt '
do
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.