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.
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';
(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).
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:
pg_get_functiondef()
is your best option in standard Postgres. (Looks like a pretty good option to me.)