4

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.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
nyctef
  • 474
  • 6
  • 12

1 Answers1

2

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 a LANGUAGE internal function from it like this:

CREATE OR REPLACE FUNCTION pg_get_function_sqlbody(oid)
  RETURNS text
  LANGUAGE internal STABLE PARALLEL SAFE STRICT
AS 'pg_get_function_sqlbody';

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):

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

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 ...

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.)

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • Unfortunately in our case we'd need a solution that can run on customer databases, so the permissions required here probably aren't viable, but this is an interesting solution – nyctef Aug 30 '23 at 15:42
  • although on second thoughts maybe it would be acceptable to try creating the function with an exception handler just in case we have those permissions available? – nyctef Aug 30 '23 at 15:50
  • @nyctef: You only need superuser privileges *once* for the creation of the function. (Still a problem on hosted services ...) – Erwin Brandstetter Aug 30 '23 at 16:01