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 ----
+
+
+
+
+ Dump only table data for which the given WHERE
+ 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.
+
+
+
+
+ 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 pg_dump.
+
+
+
+
+
+
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;
}