7

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?

1 Answer 1

9

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:

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 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;
2
  • 1
    "This form tracks dependencies between the function and objects used in the function body" - does that mean it also tracks renames? And is no longer susceptible to shadowing through search_path, like default expressions?
    – Bergi
    Oct 11, 2021 at 0:37
  • 2
    @Bergi: That's what it means, among other things. The function body is parsed at creation time, the original function body string isn't even saved (means you lose formating and comments in the function body!) - as opposed to the traditional CREATE FUNCTION syntax that saves the given body as string literal for call-time parsing etc. Early binding vs. Late binding, each has its advantages. Oct 11, 2021 at 0:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.