Greetings,
I have developed a fairly simple patch to the pg_dump utility. It is
against version 8.3.1 source code.
I have added a new parameter, -w/--where=EXPR
This lets you specify an expression that will be used in a WHERE
clause when the data is dumped.
I have implemented and tested that it works when generating either
COPY statements (the default), or INSERT statements (-d and -D). These
two modes of operation have two different sections of code that select
the data to be dumped.
Though this change could arguably be removed, when a -w/--where
expression is specified, it is also indicated in the comments of the
dump output so one viewing the dump can see that it was not necessarily
all of the data.
When -w/--where is not specified, the dump output is just as if this
patch had not been applied.
I've also updated the pg_dump.sgml file to add a description of this
new flag.
The code changes should also conform to the existing code style within
pg_dump.
The patch should be applied from the root of the source tree with a
-p1 option to the patch command.
Please give any feedback if the patch needs improvement
Thanks for a great DB!
diff -cr postgresql-8.3.1.orig/doc/src/sgml/ref/pg_dump.sgml postgresql-8.3.1/doc/src/sgml/ref/pg_dump.sgml
*** postgresql-8.3.1.orig/doc/src/sgml/ref/pg_dump.sgml 2008-05-31 17:48:38.000000000 -0500
--- postgresql-8.3.1/doc/src/sgml/ref/pg_dump.sgml 2008-06-01 00:23:32.000000000 -0500
***************
*** 535,540 ****
--- 535,562 ----
</varlistentry>
<varlistentry>
+ <term><option>-w <replaceable class="parameter">expression</replaceable></option></term>
+ <term><option>--where=<replaceable class="parameter">expression</replaceable></option></term>
+ <listitem>
+ <para>
+ Dump only table data for which the given <command>WHERE</command>
+ clause expression is true. The given expression is used for all
+ tables that will be dumped. Therefore, an error will occur if the
+ expression refers to columns that do not exist in the table being
+ dumped.
+ </para>
+
+ <note>
+ <para>
+ When giving the expression at the shell prompt, it must be correctly
+ quoted and escaped since otherwise it may be received as multiple
+ arguments to <application>pg_dump</application>.
+ </para>
+ </note>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
<term><option>-x</></term>
<term><option>--no-privileges</></term>
<term><option>--no-acl</></term>
diff -cr postgresql-8.3.1.orig/src/bin/pg_dump/pg_dump.c postgresql-8.3.1/src/bin/pg_dump/pg_dump.c
*** postgresql-8.3.1.orig/src/bin/pg_dump/pg_dump.c 2008-05-31 17:48:25.000000000 -0500
--- postgresql-8.3.1/src/bin/pg_dump/pg_dump.c 2008-06-01 00:33:30.000000000 -0500
***************
*** 76,81 ****
--- 76,82 ----
bool attrNames; /* put attr names into insert strings */
bool schemaOnly;
bool dataOnly;
+ char *whereClauseExpr;
bool aclsSkip;
/* subquery used to convert user ID (eg, datdba) to user name */
***************
*** 191,197 ****
static void dumpEncoding(Archive *AH);
static void dumpStdStrings(Archive *AH);
static const char *getAttrName(int attrnum, TableInfo *tblInfo);
! static const char *fmtCopyColumnList(const TableInfo *ti);
static void do_sql_command(PGconn *conn, const char *query);
static void check_sql_result(PGresult *res, PGconn *conn, const char *query,
ExecStatusType expected);
--- 192,198 ----
static void dumpEncoding(Archive *AH);
static void dumpStdStrings(Archive *AH);
static const char *getAttrName(int attrnum, TableInfo *tblInfo);
! static const char *fmtCopyColumnList(const TableInfo *ti, bool addParens);
static void do_sql_command(PGconn *conn, const char *query);
static void check_sql_result(PGresult *res, PGconn *conn, const char *query,
ExecStatusType expected);
***************
*** 231,236 ****
--- 232,238 ----
static struct option long_options[] = {
{"data-only", no_argument, NULL, 'a'},
+ {"where", no_argument, NULL, 'w'},
{"blobs", no_argument, NULL, 'b'},
{"clean", no_argument, NULL, 'c'},
{"create", no_argument, NULL, 'C'},
***************
*** 281,286 ****
--- 283,289 ----
strcpy(g_opaque_type, "opaque");
dataOnly = schemaOnly = dumpInserts = attrNames = false;
+ whereClauseExpr = NULL;
progname = get_progname(argv[0]);
***************
*** 302,308 ****
}
}
! while ((c = getopt_long(argc, argv, "abcCdDE:f:F:h:in:N:oOp:RsS:t:T:U:vWxX:Z:",
long_options, &optindex)) != -1)
{
switch (c)
--- 305,311 ----
}
}
! while ((c = getopt_long(argc, argv, "abcCdDE:f:F:h:in:N:oOp:RsS:t:T:U:vw:WxX:Z:",
long_options, &optindex)) != -1)
{
switch (c)
***************
*** 403,408 ****
--- 406,415 ----
g_verbose = true;
break;
+ case 'w': /* where clause expression */
+ whereClauseExpr = strdup(optarg);
+ break;
+
case 'W':
force_password = true;
break;
***************
*** 462,467 ****
--- 469,480 ----
exit(1);
}
+ if (whereClauseExpr && schemaOnly)
+ {
+ write_msg(NULL, "options -w/--where and -s/--schema-only cannot be used together\n");
+ exit(1);
+ }
+
if (dataOnly && outputClean)
{
write_msg(NULL, "options -c/--clean and -a/--data-only cannot be used together\n");
***************
*** 765,770 ****
--- 778,786 ----
" plain text format\n"));
printf(_(" -t, --table=TABLE dump the named table(s) only\n"));
printf(_(" -T, --exclude-table=TABLE do NOT dump the named table(s)\n"));
+ printf(_(" -w, --where=EXPR specify a WHERE clause expression when dumping any\n"
+ " table data. NOTE: Be sure to quote the entire \n"
+ " expression from the shell.\n"));
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"));
***************
*** 1016,1022 ****
PGresult *res;
int ret;
char *copybuf;
- const char *column_list;
if (g_verbose)
write_msg(NULL, "dumping contents of table %s\n", classname);
--- 1032,1037 ----
***************
*** 1029,1059 ****
*/
selectSourceSchema(tbinfo->dobj.namespace->dobj.name);
! /*
! * If possible, specify the column list explicitly so that we have no
! * possibility of retrieving data in the wrong column order. (The default
! * column ordering of COPY will not be what we want in certain corner
! * cases involving ADD COLUMN and inheritance.)
! */
! if (g_fout->remoteVersion >= 70300)
! column_list = fmtCopyColumnList(tbinfo);
! else
! column_list = ""; /* can't select columns in COPY */
! if (oids && hasoids)
{
! appendPQExpBuffer(q, "COPY %s %s WITH OIDS TO stdout;",
fmtQualifiedId(tbinfo->dobj.namespace->dobj.name,
classname),
! column_list);
}
else
{
! appendPQExpBuffer(q, "COPY %s %s TO stdout;",
fmtQualifiedId(tbinfo->dobj.namespace->dobj.name,
classname),
column_list);
}
res = PQexec(g_conn, q->data);
check_sql_result(res, g_conn, q->data, PGRES_COPY_OUT);
PQclear(res);
--- 1044,1087 ----
*/
selectSourceSchema(tbinfo->dobj.namespace->dobj.name);
! appendPQExpBuffer(q, "COPY");
! if (whereClauseExpr)
{
! appendPQExpBuffer(q, " (SELECT %s FROM %s WHERE %s)",
! fmtCopyColumnList(tbinfo, false),
fmtQualifiedId(tbinfo->dobj.namespace->dobj.name,
classname),
! whereClauseExpr);
}
else
{
! const char *column_list;
! /*
! * If possible, specify the column list explicitly so that we have no
! * possibility of retrieving data in the wrong column order. (The default
! * column ordering of COPY will not be what we want in certain corner
! * cases involving ADD COLUMN and inheritance.)
! */
! if (g_fout->remoteVersion >= 70300)
! column_list = fmtCopyColumnList(tbinfo, true);
! else
! column_list = ""; /* can't select columns in COPY */
!
! appendPQExpBuffer(q, " %s %s",
fmtQualifiedId(tbinfo->dobj.namespace->dobj.name,
classname),
column_list);
}
+
+ if (oids && hasoids)
+ {
+ appendPQExpBuffer(q, " WITH OIDS TO stdout;");
+ }
+ else
+ {
+ appendPQExpBuffer(q, " TO stdout;");
+ }
res = PQexec(g_conn, q->data);
check_sql_result(res, g_conn, q->data, PGRES_COPY_OUT);
PQclear(res);
***************
*** 1172,1177 ****
--- 1200,1210 ----
classname));
}
+ if (whereClauseExpr)
+ {
+ appendPQExpBuffer(q, " WHERE %s", whereClauseExpr);
+ }
+
res = PQexec(g_conn, q->data);
check_sql_result(res, g_conn, q->data, PGRES_COMMAND_OK);
***************
*** 1298,1303 ****
--- 1331,1337 ----
PQExpBuffer copyBuf = createPQExpBuffer();
DataDumperPtr dumpFn;
char *copyStmt;
+ char *desc;
if (!dumpInserts)
{
***************
*** 1307,1313 ****
appendPQExpBuffer(copyBuf, "COPY %s ",
fmtId(tbinfo->dobj.name));
appendPQExpBuffer(copyBuf, "%s %sFROM stdin;\n",
! fmtCopyColumnList(tbinfo),
(tdinfo->oids && tbinfo->hasoids) ? "WITH OIDS " : "");
copyStmt = copyBuf->data;
}
--- 1341,1347 ----
appendPQExpBuffer(copyBuf, "COPY %s ",
fmtId(tbinfo->dobj.name));
appendPQExpBuffer(copyBuf, "%s %sFROM stdin;\n",
! fmtCopyColumnList(tbinfo, true),
(tdinfo->oids && tbinfo->hasoids) ? "WITH OIDS " : "");
copyStmt = copyBuf->data;
}
***************
*** 1318,1332 ****
copyStmt = NULL;
}
ArchiveEntry(fout, tdinfo->dobj.catId, tdinfo->dobj.dumpId,
tbinfo->dobj.name,
tbinfo->dobj.namespace->dobj.name,
NULL,
tbinfo->rolname, false,
! "TABLE DATA", "", "", copyStmt,
tdinfo->dobj.dependencies, tdinfo->dobj.nDeps,
dumpFn, tdinfo);
destroyPQExpBuffer(copyBuf);
}
--- 1352,1387 ----
copyStmt = NULL;
}
+ if (whereClauseExpr)
+ {
+ int l;
+ desc = malloc(64 + strlen(whereClauseExpr));
+ sprintf(desc, "TABLE DATA (WHERE %s)", whereClauseExpr);
+
+ /* don't allow \n or \r in what will be part of a comment in the output */
+ l = strlen(desc);
+ for(l=l-1; l>=0; l--)
+ {
+ if (desc[l] == '\n' || desc[l] == '\r')
+ desc[l] = ' ';
+ }
+ }
+ else
+ {
+ desc = strdup("TABLE DATA");
+ }
+
ArchiveEntry(fout, tdinfo->dobj.catId, tdinfo->dobj.dumpId,
tbinfo->dobj.name,
tbinfo->dobj.namespace->dobj.name,
NULL,
tbinfo->rolname, false,
! desc, "", "", copyStmt,
tdinfo->dobj.dependencies, tdinfo->dobj.nDeps,
dumpFn, tdinfo);
+ free(desc);
+
destroyPQExpBuffer(copyBuf);
}
***************
*** 10227,10233 ****
* "", not an invalid "()" column list.
*/
static const char *
! fmtCopyColumnList(const TableInfo *ti)
{
static PQExpBuffer q = NULL;
int numatts = ti->numatts;
--- 10282,10288 ----
* "", not an invalid "()" column list.
*/
static const char *
! fmtCopyColumnList(const TableInfo *ti, bool addParens)
{
static PQExpBuffer q = NULL;
int numatts = ti->numatts;
***************
*** 10241,10247 ****
else
q = createPQExpBuffer();
! appendPQExpBuffer(q, "(");
needComma = false;
for (i = 0; i < numatts; i++)
{
--- 10296,10303 ----
else
q = createPQExpBuffer();
! if (addParens)
! appendPQExpBuffer(q, "(");
needComma = false;
for (i = 0; i < numatts; i++)
{
***************
*** 10256,10262 ****
if (!needComma)
return ""; /* no undropped columns */
! appendPQExpBuffer(q, ")");
return q->data;
}
--- 10312,10319 ----
if (!needComma)
return ""; /* no undropped columns */
! if (addParens)
! appendPQExpBuffer(q, ")");
return q->data;
}
--
Sent via pgsql-patches mailing list (pgsql-patches@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-patches
