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.
Advanced solution
There is a function
pg_get_function_sqlbody(oid)
in the source code. But it's not exposed in standard Postgres. If you have sufficient privileges (superuser does it) you can create aLANGUAGE internal
function from it like this:It takes the OID of a function and does exactly what you are asking for: reconstructs (only) the
sql_body
of a standard-SQL function.Any role can call this custom function like (among other ways):
Tested in Postgres 15. Most probably works the same in Postgres 14.
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 ...Bold emphasis mine.
The body of a standard-SQL function can either be a single statement:
or a block:
Either way, what's actually stored is a parse tree, not a simple expression. (Though either uses the data type
pg_node_tree
.) Sopg_get_expr()
cannot handle it, not even the "single statement" variant, and you get:(Or
null
when called withnull
as 2nd parameter.)Related:
pg_get_functiondef()
is your best option in standard Postgres. (Looks like a pretty good option to me.)