What does BEGIN ATOMIC mean in a PostgreSQL SQL function / procedure?

On PG 14's documentation, in the CREATE FUNCTION section, the manual says that the body of a LANGUAGE SQL function can either be a single statement:

RETURN expression

or a block:

BEGIN ATOMIC
statement;
statement;
...
statement;
END

without giving any explanation of the semantics of that block. This looks similar to a BEGIN ... END; block in PL/pgSQL, but seems to be something different.

What's the difference between writing a set of statements within or without BEGIN ATOMIC ... END? When is it necessary to use such a block? Is the ATOMIC keyword mandatory?

It's the new (in Postgres 14) syntax variant for SQL-standard functions.

The release notes:

Allow SQL-language functions and procedures to use SQL-standard function bodies (Peter Eisentraut)

Previously only string-literal function bodies were supported. When writing a function or procedure in SQL-standard syntax, the body is parsed immediately and stored as a parse tree. This allows better tracking of function dependencies, and can have security benefits.

Traditional Postgres functions and procedures save the body as literal string to be parsed at execution time, typically using dollar-quoting. See:

• What are '$$' used for in PL/pgSQL

The new syntax BEGIN ATOMIC ... END (with mandatory ATOMIC!) does not quote the function body, which is parsed at creation time. It only looks similar to a PL/pgSQL block, which is decorated with BEGIN ... END. Both are very much distinct. The new syntax variant is only allowed for LANGUAGE sql. In fact, that language is assumed without declaration. The manual:

The default is sql if sql_body is specified.

You can write multiple pure-SQL statements, much like in traditional string-literal function or procedure bodies. But everything is parsed at function-creation time. So "early binding" vs. "late binding" for the traditional string-literal form. This has a number of side effects.

There is good explanation for SQL-standard syntax the in the manual:

This is similar to writing the text of the function body as a string
constant (see definition above), but there are some differences:
This form only works for LANGUAGE SQL, the string constant form
works for all languages. This form is parsed at function definition
time, the string constant form is parsed at execution time; therefore
this form cannot support polymorphic argument types and other
constructs that are not resolvable at function definition time. This
form tracks dependencies between the function and objects used in the
function body, so DROP ... CASCADE will work correctly, whereas the
form using string literals may leave dangling functions. Finally, this
form is more compatible with the SQL standard and other SQL implementations.

The "SQL-standard" form can also be "inlined". See:

• How can I find out if a SQL function can be inlined?

The new syntax variant will typically be preferable for simple SQL functions.
Code examples

Short form for a single expression with RETURN (and no ATOMIC keyword):

CREATE OR REPLACE FUNCTION asterisk(n int)
  RETURNS text
  LANGUAGE sql IMMUTABLE STRICT PARALLEL SAFE
RETURN repeat('*', n);

Full form:

CREATE OR REPLACE FUNCTION asterisks(n int)
  RETURNS SETOF text
  LANGUAGE sql IMMUTABLE STRICT PARALLEL SAFE
BEGIN ATOMIC
SELECT repeat('*', g) FROM generate_series (1, n) g;
END;

For reference, the same as traditional SQL function with string-literal body:

CREATE OR REPLACE FUNCTION asterisks(n int)
  RETURNS SETOF text
  LANGUAGE sql IMMUTABLE STRICT PARALLEL SAFE AS
$func$
SELECT repeat('*', g) FROM generate_series (1, n) g;
$func$;

Or as PL/pgSQL function:

CREATE OR REPLACE FUNCTION asterisks(n int)
  RETURNS SETOF text
  LANGUAGE plpgsql IMMUTABLE STRICT PARALLEL SAFE AS
$func$
BEGIN
   RETURN QUERY
   SELECT repeat('*', g) FROM generate_series (1, n) g;
END
$func$;

StackExchange Database Administrators Q#300507, answer score: 21