Re: [PATCH] Add pg_get_role_ddl() functions for role recreation

On Thu, Jan 8, 2026 at 8:20 PM Mario González Troncoso
<gonzalemario(at)gmail(dot)com> wrote:
>
> Cool. I rebased this morning and it passed just fine.
>
hi.

--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -12683,4 +12683,28 @@
proname => 'hashoid8extended', prorettype => 'int8',
proargtypes => 'oid8 int8', prosrc => 'hashoid8extended' },

+# pg_get_role_ddl - return DDL to recreate a role (compact format)
+{ oid => '9991', descr => 'get SQL commands to recreate role',
+ proname => 'pg_get_role_ddl', pronamespace => 'pg_catalog',
+ proowner => 'POSTGRES', prolang => 'internal', procost => '1',
prorows => '0',
+ provariadic => '0', prosupport => '0', prokind => 'f', prosecdef => 'f',
+ proleakproof => 'f', proisstrict => 't', proretset => 'f',
provolatile => 's',
+ proparallel => 's', pronargs => '1', pronargdefaults => '0',
+ prorettype => 'text', proargtypes => 'regrole', proallargtypes => '_null_',
+ proargmodes => '_null_', proargnames => '_null_', proargdefaults => '_null_',
+ protrftypes => '_null_', prosrc => 'pg_get_role_ddl', probin => '_null_',
+ prosqlbody => '_null_', proconfig => '_null_', proacl => '_null_' },
+
+# pg_get_role_ddl_statements - return DDL as separate statements
(compact format)
+{ oid => '9992', descr => 'get SQL commands to recreate role as row set',
+ proname => 'pg_get_role_ddl_statements', pronamespace => 'pg_catalog',
+ proowner => 'POSTGRES', prolang => 'internal', procost => '1',
+ prorows => '10', provariadic => '0', prosupport => '0', prokind => 'f',
+ prosecdef => 'f', proleakproof => 'f', proisstrict => 't', proretset => 't',
+ provolatile => 's', proparallel => 's', pronargs => '1',
+ pronargdefaults => '0', prorettype => 'text', proargtypes => 'regrole',
+ proallargtypes => '_null_', proargmodes => '_null_', proargnames => '_null_',
+ proargdefaults => '_null_', protrftypes => '_null_',
+ prosrc => 'pg_get_role_ddl_statements', probin => '_null_',
+ prosqlbody => '_null_', proconfig => '_null_', proacl => '_null_' },
]'

you don't need to specify proowner.
if you want these two functions only available for superuser, you can
change it at
src/backend/catalog/system_functions.sql, see line 660.

many other fields also no need explicitly specified, see
src/include/catalog/pg_proc.h,
BKI_DEFAULT.

create_role.sql, roleattributes.sql have so many CREATE ROLE statements. we can
just add more tests on these two files, no need for extra
src/test/regress/sql/role_ddl.sql file, IMHO.