高级解决方案

在源代码中有一个函数**pg_get_function_sqlbody(oid)。但它没有在标准Postgres中公开。如果你有足够的权限**（超级用户做的），你可以像这样从它创建一个LANGUAGE internal函数：

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

字符串
它接受一个函数的函数名，并完全按照您的要求执行：（仅）重构标准SQL函数的sql_body。
任何角色都可以像这样调用这个自定义函数（以及其他方式）：

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

型
在Postgres 15中进行了测试。在Postgres 14中可能也是如此。
由于该函数没有文档记录，因此您不能依赖主要版本的兼容性。项目可以根据需要自由删除或更改它（即使可能性很小）。

一些解释

引用手册，pg_get_expr()只有...
反编译表达式的内部形式[...]

大胆强调我的。

标准SQL函数can either be的主体是单个语句：

RETURN expression

型
或者一个block：

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

型
无论哪种方式，实际存储的是一个解析树，而不是一个简单的表达式。（尽管两者都使用了pg_node_tree数据类型。）所以pg_get_expr()不能处理它，甚至不能处理“single statement”变体，你得到：

ERROR:  input is a query, not an expression

型
(Or null（当以null作为第二个参数调用时）。
相关：

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

pg_get_functiondef()是你在标准Postgres中的最佳选择。（在我看来是一个不错的选择。）

@ErwinBrandstetter，谢谢你提供这个很棒的提示！我在本地启动并运行了自定义别名函数，没有问题。在我们的RDS部署中，没有超级用户访问，没有乐趣。
但这里有一些好消息：pg_get_function_sqlbody(old)直接工作 * 没有 * RDS上的自定义函数。

/*
D'oh! Still failing on major version upgrades as pg_dump/pg_restore doesn't seem to handle our
dependency chains correctly. Mainly, if not exclusively, this is for our UPSERT system, were
each table+version has a custom view to implicitly define a compound type, and an UPSERT function
that takes my_table_v3[] input. Tom Lane put in a fix for this in PG 15, but I'm still not getting
simple upgrades. I have not put the time into building and testing a simplest proof database to
report the problem. I suck, stipulated.

Note that I'm only guessing that this is what's blocking my 15.5 -> 16.1 upgrades on RDS. During
the 14 -> 15 period, I uncovered some RDS bugs around this that they resolved, and they also agreed
to expose more logs. Unfortunately, the _details_ of why pg_dump or pg_upgrade have failed are
not visible. RDS generates an upgrade log that tells you the exact line and log file name to review,
but you have to open a ticket with RDS support to try and get at those details. At least, that's
how it was, and how it looks to be now.

Honestly, it's faster for me to drop everything, upgrade, and rebuild than to wait on RDS support.

*/

WITH 
begin_atomic_routines AS (

SELECT pronamespace::regnamespace               AS schema_name,
       proname                                  AS routine_name,
       pg_get_function_identity_arguments(oid)  AS argument_list,
       pg_get_function_sqlbody(oid)             AS stored_query -- This calls an undocumented internal function that's just what we need here. Thanks to Erwin Brandstetter!
              
  FROM pg_proc

-- https://www.postgresql.org/docs/current/catalog-pg-proc.html
 WHERE prosqlbody                        IS NOT NULL -- Uses BEGIN ATOMIC (SQL-standard block, first added in PG 14) instead of string literal.
   AND pronamespace::regnamespace::text       NOT IN ('pg_catalog','information_schema','extensions')
 ),
 
 -- It can help to have a squint at things first, including the reconstructed source query code.
 double_check_list AS (
  select * from begin_atomic_routines order by schema_name, routine_name, argument_list
),

drop_routines_list AS (
   SELECT 'DROP FUNCTION IF EXISTS ' || schema_name || '.' || routine_name || ' (' || argument_list || ');' AS drop_command
     FROM begin_atomic_routines
ORDER BY schema_name, routine_name, argument_list
)

-- Wrap these results in BEGIN....ROLLBACK to safely test for any lurking dependencies.
-- Then, you need to build a patch to manually restore all of the functions, after the upgrade.
-- select * from drop_routines_list   

 
 select * from drop_routines_list;

字符串

稍后

我是为工作中的人们写这篇文章的，注意到pg_get_functiondef()和pg_get_function_sqlbody()都可以工作，至少在15.5版本中是这样。输出不一样，pg_get_functiondef的结果包括基本的CREATE OR REPLACE FUNCTION标题。
我猜这是偷偷进入一些点释放，或什么的。在任何情况下，它给了我另一个选择去思考。

select pg_get_functiondef      (11348905),
       pg_get_function_sqlbody (11348905);

型
x1c 0d1x的数据

再生功能

我浏览了更多的pg_catalog和系统信息函数，编写了一个例程来重新生成例程，基于存储在当前数据库中的详细信息。我在生成的代码体中发现了一些异常，这些代码体在系统之间和系统内部都不一致。比如，在一个UPSERT中，EXCLUDED.被更改为excluded_1。不跟踪它，我甚至不确定我能不能追踪到它，我将使用我的源代码树手动构建一个补丁来重建例程。
然而，如果你想尝试重新生成代码，并且只有SQL访问Postgres，那么这段代码有一堆你可能会发现有用的片段。我喜欢在那里爬来爬去.

------------------------------------
-- Function definition
------------------------------------
/*
The code below regenerates an existing routine, drawing all details from the database. 

Notes:
* This routine is wildly opinionated. 

* I've only tested this on functions, and only as far as I need it.

* I'm using ALTER ROUTINE, and similar, instead of ALTER FUNCTION/ALTER PROCEDURE.
  Why? Saves me from having to put in a bunch of branching code in a SQL CASE/switch
  off of pg_proc.prokind. Don't need it here. FYI, the current codes from the docs: 
  
     f for a normal function, 
     p for a procedure,
     a for an aggregate function
     w for a window function
  
* Postgres doesn't store in-code comments, but does support COMMENT ON.

* Language, volatility, SECURITY DEFINER, and parallel hints/contracts are all preserved. 

* The GRANTs here are _very_ unlikely to look like any explicit grants you've set, but they should match.

* Speaking of GRANTS, either I've over-complicated it, or it's over-complicated ;-) Nicer to stick 
  this bit into a function like regenerate_routine_grants(oid). Not doing that here.
  
* Speaking of functions, I'll set this query up as a function with $1 routine_oid oid as the argument list.

Bugs:
I found *one* function in my system where an ON CONFLICT was restored using excluded_1. instead of EXCLUDED.
This fails, don't know why this one particular case is translated out by pg_get_functiondef() and 
pg_get_function_sqlbody() both work this way. (Same code internally, presumably, for this bit.) And, yes,
this function uses BEGIN ATOMIC. As do 130+ other similar functions, all machine-generated.  So, test your
work with BEGIN.....all the things....ROLLBACK. Transactional DDL! Postgres _is_ amazing.

Corrections and Improvements:
...are most welcome.

*/

CREATE OR REPLACE FUNCTION dba.get_rebuild_routine_code(routine_oid_in oid)
  RETURNS text
  LANGUAGE sql 
  STRICT
 
AS $BODY$
 
WITH 

-------------------------------------------------------------------- 
-- pg_catalog views and functions are...cryptic. Pull out what's
-- needed and give each bit a simple label to make the later 
-- concatenation code more readable.
--------------------------------------------------------------------
routine_details AS (
  select pg_get_functiondef(oid)                   AS routine_body,     -- Works for BEGIN ATOMIC or string-literal function bodies.
         obj_description(oid, 'pg_proc' )          AS routine_comments, 
         pronamespace::regnamespace                AS schema_name,
         proname::text                             AS routine_name,
         pg_get_function_identity_arguments(oid)   AS argument_list,    -- This is what we need here, but see the docs for variants. 
         proowner::regrole::text                   AS owner_name,
         proacl                                    AS grants_list       -- Array of ACL items, we'll spend some time with this below.
                                 
    from pg_proc
   where oid = routine_oid_in
   -- (select 'tools.format_uuid(uuid)'::regprocedure) -- This will be $1 in a function. 
   --  Tip: Use the magic ::regprocedure casting to safely resolve a full routine path into its local OID:
   --       'public.example(uuid)'::regprocedure
          
), -- select * from routine_details

-------------------------------------------------------------------- 
-- GRANTS are stored in an ACL list (array). This takes more
-- unpacking, cleaning, and repacking than I know how to do
-- without a ton of code in SQL. Improvements welcome!
--------------------------------------------------------------------
/*
Too much unnesting to stick this in the CTE above, make two CTEs, one to unpack, the other to repack as GRANTs.
The unnested ACL list entries look like this sample:

group_api_users=X/user_bender

That's role=privilege/grantor. I think.

All this code does is strip off the =X/grantor_name bit. Routines only support the 'X' grant, so no more parsing needed. 

Note: There's either an easier way to reconstruct the GRANTS, or this gnarled up code should be hidden in a subroutine.
 
 Tip: Also see aclexplode() and the relevant bits and pieces in the docs.
*/

granted_role_names AS (
    select substring(unnest(grants_list)::text FROM '^([^=]+)=') as role_name
     from routine_details 
), -- select * from granted_role_names: This is a rowset with the roles in the ACL.

grant_role_line AS (
    -- GRANT EXECUTE ON FUNCTION dba.get_rebuild_routine_code(oid) TO rds_super;
   select 'GRANT EXECUTE ON FUNCTION '        || 
           schema_name || '.' || routine_name || 
           '(' || argument_list || ') TO '    || 
           role_name || ';' AS user_grant_code
           
        from routine_details 
cross join granted_role_names
  order by role_name
),

-- Collapse the rowset above down to a single text block.
combined_grants AS (
  select string_agg(user_grant_code, chr(10)) AS grants_block from grant_role_line
),--  select * from grant_line_code

-------------------------------------------------------------------- 
-- Put in a bit of conditional logic for elements that may be NULL,
-- add whitespace, etc.
--------------------------------------------------------------------
routine_components AS (
    
    SELECT 
    
    -----------------------------------------------------------------------
    -- Header
    -----------------------------------------------------------------------
     -- I'll be combining hundred(s) of routines, it's nice to have a visual break in the output at the top of each method.
        '--------------------------------------------------------------------------------'   || chr(10) ||   
        '-- ' || schema_name || '.' ||   routine_name || '(' || argument_list  || ')'        || chr(10) ||
        '--------------------------------------------------------------------------------'   || chr(10) ||   
        '-- Regenerated at: ' || now()                                         || chr(10)    || chr(10)      AS header,

    -----------------------------------------------------------------------
    -- CREATE OR REPLACE and full routine code
    -----------------------------------------------------------------------
    -- Note: This automatically includes LANGUAGE and any volatility level, 
    -- parallel promises, and SECURITY DEFINER settings.

    routine_body || ';' || chr(10)                                       AS create_or_replace_and_body,

    -----------------------------------------------------------------------
    -- COMMENT ON, if it's defined for this routine
    -----------------------------------------------------------------------
    /*
    COMMENT ON ROUTINE schema_name.routine_name(argument, list) IS
    $Comments here.$;
    */                                  
    CASE
    WHEN routine_comments IS NULL THEN ''
    
    ELSE chr(10) ||
     'COMMENT ON ROUTINE '                      ||
      schema_name                               || '.'       || routine_name ||
      '(' || argument_list|| ') IS '            || chr(10)   ||
      '''$' || REPLACE(routine_comments,'$','') || '$'';'  || chr(10)   || chr(10) -- The REPLACE is a total hack for _already_ dollar-quoted comments. YMMV.
     
    END                                                                  AS set_comments,
    
    -----------------------------------------------------------------------------
    -- ALTER FUNCTION...OWNER TO...
    -----------------------------------------------------------------------------
    /*
    ALTER FUNCTION dba.get_tables(text)
    OWNER TO user_bender;
    */
    'ALTER FUNCTION ' || 
    schema_name                        || '.'     || routine_name ||
    '(' || argument_list|| ')'         || chr(10) ||
    '    OWNER TO ' || owner_name      || ';'     || chr(10)             AS set_owner_name,
        
    -----------------------------------------------------------------------------
    -- GRANTs
    -----------------------------------------------------------------------------
    chr(10) || (SELECT grants_block FROM combined_grants) || chr(10)     AS grants_block

        
   FROM     routine_details    
) -- select * from routine_components

-------------------------------------------------------------------- 
-- We have arrived. Stitch everything together.
--------------------------------------------------------------------
select header                     ||
       create_or_replace_and_body || 
       set_comments               || 
       set_owner_name             ||
       grants_block                                                      AS rebuild_routine_code
       
  from routine_components
    
$BODY$;
  
 -----------------------------------------------------
 -- Comment
 -----------------------------------------------------
COMMENT ON FUNCTION dba.get_rebuild_routine_code(oid) IS
'Reconstruct a routine, based on the details stored in the current database.';

型
下面是一个示例脚本的更新版本，它构建了一堆列表和代码块：

WITH 
begin_atomic_routines AS (

SELECT pronamespace::regnamespace               AS schema_name,
       proname                                  AS routine_name,
       pg_get_function_identity_arguments(oid)  AS argument_list, -- Better for ALTER FUNCTION, see https://www.postgresql.org/docs/current/functions-info.html
       pg_get_functiondef(oid)                  AS reconstructed_query,
       
       -- prokind char: f for a normal function, p for a procedure, a for an aggregate function, or w for a window function
       CASE prokind  -- This CASE syntax behaves like SWITCH in many languages.
           WHEN 'f' THEN 'Function'
           WHEN 'p' THEN 'Procedure'
           WHEN 'a' THEN 'Aggregate'
           WHEN 'a' THEN 'Window Function'
        ELSE
           'Unknown prokind char code of ' || prokind::text
        END AS routine_kind,
        
        get_rebuild_routine_code(oid) 
              
  FROM pg_proc

-- https://www.postgresql.org/docs/current/catalog-pg-proc.html
 WHERE prosqlbody                        IS NOT NULL -- Uses BEGIN ATOMIC (SQL-standard block, first added in PG 14) instead of string literal.
   AND pronamespace::regnamespace::text       NOT IN ('pg_catalog','information_schema','extensions')
 ),
 
 -- It can help to have a squint at things first, including the reconstructed source query code.
 double_check_list AS (
  select * from begin_atomic_routines order by schema_name, routine_name, argument_list
),

-- DROP ROUTINE? It's a generic for DROP FUNCTION or DROP PROCEDURE. All of our BEGIN ATOMIC code is implemented
-- as stored functions right now. But, you never know.
drop_routines_list AS (
   SELECT 'DROP ROUTINE IF EXISTS ' || schema_name || '.' || routine_name || ' (' || argument_list || ');' AS drop_command
     FROM begin_atomic_routines
ORDER BY schema_name, routine_name, argument_list
)

select * from double_check_list

-- Wrap these results in BEGIN....ROLLBACK; to safely test for any lurking dependencies.
-- Then, you need to build a patch to manually restore all of the functions, after the upgrade.
-- select * from drop_routines_list;

型