[PATCH] Add pg_get_policy_ddl() function to reconstruct CREATE POLICY statement

Akshay Joshi <akshay.joshi@enterprisedb.com>

From: Akshay Joshi <akshay.joshi@enterprisedb.com>
To: pgsql-hackers <pgsql-hackers@postgresql.org>
Date: 2025-10-15T13:37:12Z
Lists: pgsql-hackers

Attachments

Hi Hackers,

I’m submitting a patch as part of the broader Retail DDL Functions project
described by Andrew Dunstan
https://www.postgresql.org/message-id/945db7c5-be75-45bf-b55b-cb1e56f2e3e9%40dunslane.net

This patch adds a new system function pg_get_policy_ddl(table, policy_name,
pretty), which reconstructs the CREATE POLICY statement for a given table
and policy. When the pretty flag is set to true, the function returns a
neatly formatted, multi-line DDL statement instead of a single-line
statement.

Usage examples:

1) SELECT pg_get_policy_ddl('rls_tbl_1', 'rls_p8', false);  -- *non-pretty
formatted DDL*
                                               pg_get_policy_ddl

---------------------------------------------------------------------------------------------------------------------------------------------------------------
CREATE POLICY rls_p8 ON rls_tbl_1 AS PERMISSIVE FOR ALL TO
regress_rls_alice, regress_rls_dave USING (true);

2) SELECT pg_get_policy_ddl('rls_tbl_1', 'rls_p8', true);   -- *pretty
formatted DDL*
               pg_get_policy_ddl
------------------------------------------------
 CREATE POLICY rls_p8 ON rls_tbl_1
         AS PERMISSIVE
         FOR ALL
         TO regress_rls_alice, regress_rls_dave
         USING (true)
 ;

The patch includes documentation, in-code comments, and regression tests,
all of which pass successfully.

-----
Regards,
Akshay Joshi
EDB (EnterpriseDB)