Atached is a patch to allow pretty printing of system objects (constraints, indexes, rules, and views) when doing a pg_dump via a --pretty-print flag along with a warning in the docs to be careful about doing so :)
-- Greg Sabino Mullane [EMAIL PROTECTED] [EMAIL PROTECTED] End Point Corporation PGP Key: 0x14964AC8 200701191958 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
Index: doc/src/sgml/ref/pg_dump.sgml
===================================================================
RCS file: /projects/cvsroot/pgsql/doc/src/sgml/ref/pg_dump.sgml,v
retrieving revision 1.92
diff -c -r1.92 pg_dump.sgml
*** doc/src/sgml/ref/pg_dump.sgml 28 Nov 2006 22:54:18 -0000 1.92
--- doc/src/sgml/ref/pg_dump.sgml 20 Jan 2007 00:51:43 -0000
***************
*** 546,551 ****
--- 546,563 ----
</varlistentry>
<varlistentry>
+ <term><option>--pretty-print</></term>
+ <listitem>
+ <para>
+ This option pretty-prints all constraint, index, rule, and view
+ definitions. While this result is more readable, it is less
+ likely to be interpreted the same way by future versions of
+ <productname>PostgreSQL</> and should be used carefully.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
<term><option>--disable-dollar-quoting</></term>
<listitem>
<para>
Index: doc/src/sgml/ref/pg_dumpall.sgml
===================================================================
RCS file: /projects/cvsroot/pgsql/doc/src/sgml/ref/pg_dumpall.sgml,v
retrieving revision 1.59
diff -c -r1.59 pg_dumpall.sgml
*** doc/src/sgml/ref/pg_dumpall.sgml 15 Jan 2007 17:22:46 -0000 1.59
--- doc/src/sgml/ref/pg_dumpall.sgml 20 Jan 2007 00:51:43 -0000
***************
*** 241,246 ****
--- 241,258 ----
</varlistentry>
<varlistentry>
+ <term><option>--pretty-print</></term>
+ <listitem>
+ <para>
+ This option pretty-prints all constraint, index, rule, and view
+ definitions. While this result is more readable, it is less
+ likely to be interpreted the same way by future versions of
+ <productname>PostgreSQL</> and should be used carefully.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
<term><option>--disable-dollar-quoting</></term>
<listitem>
<para>
Index: src/bin/pg_dump/pg_dump.c
===================================================================
RCS file: /projects/cvsroot/pgsql/src/bin/pg_dump/pg_dump.c,v
retrieving revision 1.456
diff -c -r1.456 pg_dump.c
*** src/bin/pg_dump/pg_dump.c 5 Jan 2007 22:19:48 -0000 1.456
--- src/bin/pg_dump/pg_dump.c 20 Jan 2007 00:51:43 -0000
***************
*** 119,124 ****
--- 119,126 ----
/* flag to turn on/off dollar quoting */
static int disable_dollar_quoting = 0;
+ /* flag to turn on/off pretty printing of views and rules */
+ static int pretty_print = 0;
static void help(const char *progname);
static void expand_schema_name_patterns(SimpleStringList *patterns,
***************
*** 260,265 ****
--- 262,268 ----
*/
{"disable-dollar-quoting", no_argument, &disable_dollar_quoting, 1},
{"disable-triggers", no_argument, &disable_triggers, 1},
+ {"pretty-print", no_argument, &pretty_print, 1},
{"use-set-session-authorization", no_argument, &use_setsessauth, 1},
{NULL, 0, NULL, 0}
***************
*** 416,421 ****
--- 419,426 ----
disable_dollar_quoting = 1;
else if (strcmp(optarg, "disable-triggers") == 0)
disable_triggers = 1;
+ else if (strcmp(optarg, "pretty-print") == 0)
+ pretty_print = 1;
else if (strcmp(optarg, "use-set-session-authorization") == 0)
use_setsessauth = 1;
else
***************
*** 759,764 ****
--- 764,770 ----
printf(_(" -x, --no-privileges do not dump privileges (grant/revoke)\n"));
printf(_(" --disable-dollar-quoting disable dollar quoting, use SQL standard quoting\n"));
printf(_(" --disable-triggers disable triggers during data-only restore\n"));
+ printf(_(" --pretty-print print rules and views in a more readable format\n"));
printf(_(" --use-set-session-authorization\n"
" use SESSION AUTHORIZATION commands instead of\n"
" ALTER OWNER commands to set ownership\n"));
***************
*** 3194,3200 ****
appendPQExpBuffer(query,
"SELECT t.tableoid, t.oid, "
"t.relname as indexname, "
! "pg_catalog.pg_get_indexdef(i.indexrelid) as indexdef, "
"t.relnatts as indnkeys, "
"i.indkey, i.indisclustered, "
"c.contype, c.conname, "
--- 3200,3206 ----
appendPQExpBuffer(query,
"SELECT t.tableoid, t.oid, "
"t.relname as indexname, "
! "pg_catalog.pg_get_indexdef(i.indexrelid,0,%s) as indexdef, "
"t.relnatts as indnkeys, "
"i.indkey, i.indisclustered, "
"c.contype, c.conname, "
***************
*** 3213,3218 ****
--- 3219,3225 ----
"AND d.refobjid = c.oid) "
"WHERE i.indrelid = '%u'::pg_catalog.oid "
"ORDER BY indexname",
+ pretty_print ? "true" : "false",
tbinfo->dobj.catId.oid);
}
else if (g_fout->remoteVersion >= 80000)
***************
*** 3220,3226 ****
appendPQExpBuffer(query,
"SELECT t.tableoid, t.oid, "
"t.relname as indexname, "
! "pg_catalog.pg_get_indexdef(i.indexrelid) as indexdef, "
"t.relnatts as indnkeys, "
"i.indkey, i.indisclustered, "
"c.contype, c.conname, "
--- 3227,3233 ----
appendPQExpBuffer(query,
"SELECT t.tableoid, t.oid, "
"t.relname as indexname, "
! "pg_catalog.pg_get_indexdef(i.indexrelid,0,%s) as indexdef, "
"t.relnatts as indnkeys, "
"i.indkey, i.indisclustered, "
"c.contype, c.conname, "
***************
*** 3239,3244 ****
--- 3246,3252 ----
"AND d.refobjid = c.oid) "
"WHERE i.indrelid = '%u'::pg_catalog.oid "
"ORDER BY indexname",
+ pretty_print ? "true" : "false",
tbinfo->dobj.catId.oid);
}
else if (g_fout->remoteVersion >= 70300)
***************
*** 3456,3465 ****
resetPQExpBuffer(query);
appendPQExpBuffer(query,
"SELECT tableoid, oid, conname, "
! "pg_catalog.pg_get_constraintdef(oid) as condef "
"FROM pg_catalog.pg_constraint "
"WHERE conrelid = '%u'::pg_catalog.oid "
"AND contype = 'f'",
tbinfo->dobj.catId.oid);
res = PQexec(g_conn, query->data);
check_sql_result(res, g_conn, query->data, PGRES_TUPLES_OK);
--- 3464,3474 ----
resetPQExpBuffer(query);
appendPQExpBuffer(query,
"SELECT tableoid, oid, conname, "
! "pg_catalog.pg_get_constraintdef(oid%s) as condef "
"FROM pg_catalog.pg_constraint "
"WHERE conrelid = '%u'::pg_catalog.oid "
"AND contype = 'f'",
+ (pretty_print && g_fout->remoteVersion >= 70400) ? ",true" : "",
tbinfo->dobj.catId.oid);
res = PQexec(g_conn, query->data);
check_sql_result(res, g_conn, query->data, PGRES_TUPLES_OK);
***************
*** 3528,3537 ****
if (g_fout->remoteVersion >= 70400)
appendPQExpBuffer(query, "SELECT tableoid, oid, conname, "
! "pg_catalog.pg_get_constraintdef(oid) AS consrc "
"FROM pg_catalog.pg_constraint "
"WHERE contypid = '%u'::pg_catalog.oid "
"ORDER BY conname",
tinfo->dobj.catId.oid);
else
appendPQExpBuffer(query, "SELECT tableoid, oid, conname, "
--- 3537,3547 ----
if (g_fout->remoteVersion >= 70400)
appendPQExpBuffer(query, "SELECT tableoid, oid, conname, "
! "pg_catalog.pg_get_constraintdef(oid,%s) AS consrc "
"FROM pg_catalog.pg_constraint "
"WHERE contypid = '%u'::pg_catalog.oid "
"ORDER BY conname",
+ pretty_print ? "true" : "false",
tinfo->dobj.catId.oid);
else
appendPQExpBuffer(query, "SELECT tableoid, oid, conname, "
***************
*** 4407,4417 ****
if (g_fout->remoteVersion >= 70400)
{
appendPQExpBuffer(q, "SELECT tableoid, oid, conname, "
! "pg_catalog.pg_get_constraintdef(oid) AS consrc "
"FROM pg_catalog.pg_constraint "
"WHERE conrelid = '%u'::pg_catalog.oid "
" AND contype = 'c' "
"ORDER BY conname",
tbinfo->dobj.catId.oid);
}
else if (g_fout->remoteVersion >= 70300)
--- 4417,4428 ----
if (g_fout->remoteVersion >= 70400)
{
appendPQExpBuffer(q, "SELECT tableoid, oid, conname, "
! "pg_catalog.pg_get_constraintdef(oid,%s) AS consrc "
"FROM pg_catalog.pg_constraint "
"WHERE conrelid = '%u'::pg_catalog.oid "
" AND contype = 'c' "
"ORDER BY conname",
+ pretty_print ? "true" : "false",
tbinfo->dobj.catId.oid);
}
else if (g_fout->remoteVersion >= 70300)
***************
*** 7332,7339 ****
{
/* Beginning in 7.3, viewname is not unique; rely on OID */
appendPQExpBuffer(query,
! "SELECT pg_catalog.pg_get_viewdef('%u'::pg_catalog.oid) as viewdef",
! tbinfo->dobj.catId.oid);
}
else
{
--- 7343,7351 ----
{
/* Beginning in 7.3, viewname is not unique; rely on OID */
appendPQExpBuffer(query,
! "SELECT pg_catalog.pg_get_viewdef('%u'::pg_catalog.oid%s) as viewdef",
! tbinfo->dobj.catId.oid,
! (pretty_print && g_fout->remoteVersion >= 70400) ? ",true" : "");
}
else
{
***************
*** 8448,8455 ****
if (g_fout->remoteVersion >= 70300)
{
appendPQExpBuffer(query,
! "SELECT pg_catalog.pg_get_ruledef('%u'::pg_catalog.oid) AS definition",
! rinfo->dobj.catId.oid);
}
else
{
--- 8460,8468 ----
if (g_fout->remoteVersion >= 70300)
{
appendPQExpBuffer(query,
! "SELECT pg_catalog.pg_get_ruledef('%u'::pg_catalog.oid%s) AS definition",
! rinfo->dobj.catId.oid,
! (pretty_print && g_fout->remoteVersion >= 70400) ? ",true" : "");
}
else
{
Index: src/bin/pg_dump/pg_dumpall.c
===================================================================
RCS file: /projects/cvsroot/pgsql/src/bin/pg_dump/pg_dumpall.c,v
retrieving revision 1.86
diff -c -r1.86 pg_dumpall.c
*** src/bin/pg_dump/pg_dumpall.c 5 Jan 2007 22:19:48 -0000 1.86
--- src/bin/pg_dump/pg_dumpall.c 20 Jan 2007 00:51:43 -0000
***************
*** 65,70 ****
--- 65,71 ----
static int disable_dollar_quoting = 0;
static int disable_triggers = 0;
+ static int pretty_print = 0;
static int use_setsessauth = 0;
static int server_version;
***************
*** 110,115 ****
--- 111,117 ----
*/
{"disable-dollar-quoting", no_argument, &disable_dollar_quoting, 1},
{"disable-triggers", no_argument, &disable_triggers, 1},
+ {"pretty-print", no_argument, &pretty_print, 1},
{"use-set-session-authorization", no_argument, &use_setsessauth, 1},
{NULL, 0, NULL, 0}
***************
*** 258,263 ****
--- 260,267 ----
appendPQExpBuffer(pgdumpopts, " --disable-dollar-quoting");
else if (strcmp(optarg, "disable-triggers") == 0)
appendPQExpBuffer(pgdumpopts, " --disable-triggers");
+ else if (strcmp(optarg, "pretty-print") == 0)
+ pretty_print = 1;
else if (strcmp(optarg, "use-set-session-authorization") == 0)
/* no-op, still allowed for compatibility */ ;
else
***************
*** 284,289 ****
--- 288,295 ----
appendPQExpBuffer(pgdumpopts, " --disable-dollar-quoting");
if (disable_triggers)
appendPQExpBuffer(pgdumpopts, " --disable-triggers");
+ if (pretty_print)
+ appendPQExpBuffer(pgdumpopts, " --pretty-print");
if (use_setsessauth)
appendPQExpBuffer(pgdumpopts, " --use-set-session-authorization");
***************
*** 390,395 ****
--- 396,402 ----
printf(_(" --disable-dollar-quoting\n"
" disable dollar quoting, use SQL standard quoting\n"));
printf(_(" --disable-triggers disable triggers during data-only restore\n"));
+ printf(_(" --pretty-print print rules and views in a more readable format\n"));
printf(_(" --use-set-session-authorization\n"
" use SESSION AUTHORIZATION commands instead of\n"
" OWNER TO commands\n"));
signature.asc
Description: This is a digitally signed message part
