How to read the function body of a Postgres standard-SQL function?

Question

In Postgres 14 a new kind of syntax was added to CREATE FUNCTION where the body of the function can be specified directly in SQL rather than being contained in a string. The string bodies of functions are easily accessible in pg_proc.prosrc, while the new function bodies appear to be stored in pg_proc.prosqlbody.

The prosqlbody column is of type pg_node_tree, which would normally get passed to pg_get_expr in order to turn it back into readable SQL. However, various attempts to call pg_get_expr on this column either return NULL or an error ('input is a query, not an expression'). Is this a bug, or is there some alternative to pg_get_expr that should be used in this case?

The only other alternative I can see is using pg_get_functiondef to get the entire CREATE FUNCTION statement, and then scraping past all of the initial function properties to find the function body. This would work, but it feels fragile and is more effort than I would be hoping for.

The context is that we're working on some code to generate update scripts between two Postgres databases - while it would be simpler to just work with the entire CREATE FUNCTION statement from pg_get_functiondef and stick that in a script like pg_dump does, when altering an existing function that may not be an option. So being able to access the function definition as individual parts helps a lot.

Answer 1

Solution

There is a function pg_get_function_sqlbody(oid) in the source code, and installed in vanilla Postgres 14+. But it's not documented. You can find it with:

SELECT * FROM pg_proc WHERE proname ~ 'pg_get_function_sqlbody';

fiddle

(I had instructions to create a custom LANGUAGE interal function here, but that's not needed as Morris found out.)

It takes the OID of a function and reconstructs (only) the sql_body of a standard-SQL function. Any role can call it like (among other ways):

SELECT pg_get_function_sqlbody('myschema.myfunc()'::regprocedure);

Tested in Postgres 14 - 16.

Since the function is not documented, you cannot rely on compatibility across major versions. The project is free to drop or change it as needed (even if very unlikely).

Some explanation

Quoting the manual, pg_get_expr() only ...

Decompiles the internal form of an expression [...]

Bold emphasis mine.

The body of a standard-SQL function can either be a single statement:

RETURN expression

or a block:

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

Either way, what's actually stored is a parse tree, not a simple expression. (Though either uses the data type pg_node_tree.) So pg_get_expr() cannot handle it, not even the "single statement" variant, and you get:

ERROR:  input is a query, not an expression 

(Or null when called with null as 2nd parameter.)

Related:

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

pg_get_functiondef() is your best option in standard Postgres. (Looks like a pretty good option to me.)