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_database_ddl(database_name/database_oid, pretty), which reconstructs
the CREATE DATABASE statement for a given database name or database oid.
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_database_ddl('test_get_database_ddl_builtin'); -- *non-pretty
formatted DDL*
pg_get_database_ddl
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
CREATE DATABASE test_get_database_ddl_builtin WITH OWNER =
regress_ddl_database ENCODING = "UTF8" LC_COLLATE = "C" LC_CTYPE = "C"
BUILTIN_LOCALE = "C.UTF-8" COLLATION_VERSION = "1" LOCALE_PROVIDER =
'builtin' TABLESPACE = pg_default ALLOW_CONNECTIONS = true CONNECTION LIMIT
= -1;
2) SELECT pg_get_database_ddl('test_get_database_ddl_builtin', true);
-- *pretty
formatted DDL*
CREATE DATABASE test_get_database_ddl_builtin
WITH
OWNER = regress_ddl_database
ENCODING = "UTF8"
LC_COLLATE = "C"
LC_CTYPE = "C"
BUILTIN_LOCALE = "C.UTF-8"
COLLATION_VERSION = "1"
LOCALE_PROVIDER = 'builtin'
TABLESPACE = pg_default
ALLOW_CONNECTIONS = true
CONNECTION LIMIT = -1;
3) SELECT pg_get_database_ddl(16835); -- *non-pretty formatted DDL for
OID*
4) SELECT pg_get_database_ddl(16835, true); -- *pretty formatted DDL for
OID*
The patch includes documentation, in-code comments, and regression tests,
all of which pass successfully.
*Note:* To run the regression tests, particularly the pg_upgrade tests
successfully, I had to add a helper function, ddl_filter (in database.sql),
which removes locale and collation-related information from the
pg_get_database_ddl output.
-----
Regards,
Akshay Joshi
EDB (EnterpriseDB)
0001-Add-pg_get_database_ddl-function-to-reconstruct-CREATE.patch
Description: Binary data
