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

Question

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?

Answer 1

That'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 example

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;