On 13 Aug 2002, Rod Taylor wrote:
> On Tue, 2002-08-13 at 07:49, Oleg Bartunov wrote:
> > We have a patch for pg_dump which adds a possibility to dump
> > a part of table, for example:
> >
> > dump Top.Science.Astronomy heirarchy from dmoz catalog
> >
> > pg_dump -d -t dmoz -w "select * from dmoz where path <@ 'Top.Science.Astronomy'"
>dmoz
> >
> > We found it's very useful. We'd like to extend it to use also with COPY
> > but it has no support for select (of course it will works only for
> > special case when ALL columns retrieved).
> >
> > The question is: Is't worth to submit patch for pg_dump and look into
> > copy code ?
>
> I've been asked by co-workers for information on how to do this type of
> thing. They do partial table dumps to a development system for the
> purpose of finding / eliminating bugs.
>
> That said, it may be smart to make the 'select * from <table>' part
> yourself, and let the user supply a where clause.
>
find patch (7.2) in attachement. Note, it works with -d (insert mode) option.
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to [EMAIL PROTECTED] so that your
> message can get through to the mailing list cleanly
>
Regards,
Oleg
_____________________________________________________________
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: [EMAIL PROTECTED], http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83
--- pg_dump.c.old Fri Aug 9 19:41:08 2002
+++ pg_dump.c Fri Aug 9 19:28:34 2002
@@ -128,6 +128,7 @@
TableInfo *tblinfo;
int tblidx;
bool oids;
+ const char *select_command;
} DumpContext;
static void
@@ -390,6 +391,7 @@
{
const DumpContext *dctx = (DumpContext *) dctxv;
const char *classname = dctx->tblinfo[dctx->tblidx].relname;
+ const char *select_command = dctx->select_command;
PGresult *res;
PQExpBuffer q = createPQExpBuffer();
@@ -397,9 +399,23 @@
int field;
if (fout->remoteVersion >= 70100)
- appendPQExpBuffer(q, "DECLARE _pg_dump_cursor CURSOR FOR SELECT * FROM
ONLY %s", fmtId(classname, force_quotes));
+ if (select_command)
+ {
+ appendPQExpBuffer(q, "DECLARE _pg_dump_cursor CURSOR FOR %s",
+select_command);
+ }
+ else
+ {
+ appendPQExpBuffer(q, "DECLARE _pg_dump_cursor CURSOR FOR
+SELECT * FROM ONLY %s", fmtId(classname, force_quotes));
+ }
else
- appendPQExpBuffer(q, "DECLARE _pg_dump_cursor CURSOR FOR SELECT * FROM
%s", fmtId(classname, force_quotes));
+ if (select_command)
+ {
+ appendPQExpBuffer(q, "DECLARE _pg_dump_cursor CURSOR FOR %s",
+select_command);
+ }
+ else
+ {
+ appendPQExpBuffer(q, "DECLARE _pg_dump_cursor CURSOR FOR
+SELECT * FROM %s", fmtId(classname, force_quotes));
+ }
res = PQexec(g_conn, q->data);
if (!res ||
@@ -547,7 +563,7 @@
*/
static void
dumpClasses(const TableInfo *tblinfo, const int numTables, Archive *fout,
- const char *onlytable, const bool oids, const bool force_quotes)
+ const char *onlytable, const bool oids, const bool force_quotes,
+const char *select_command)
{
int i;
DataDumperPtr dumpFn;
@@ -587,6 +603,7 @@
dumpCtx->tblinfo = (TableInfo *) tblinfo;
dumpCtx->tblidx = i;
dumpCtx->oids = oids;
+ dumpCtx->select_command = NULL;
if (!dumpData)
{
@@ -602,6 +619,10 @@
/* Restore using INSERT */
dumpFn = dumpClasses_dumpData;
copyStmt = NULL;
+ if (onlytable && (strcmp(classname, onlytable) == 0))
+ {
+ dumpCtx->select_command = select_command;
+ }
}
ArchiveEntry(fout, tblinfo[i].oid, tblinfo[i].relname,
@@ -648,6 +669,7 @@
const char *pghost = NULL;
const char *pgport = NULL;
const char *username = NULL;
+ const char *select_command = NULL;
char *tablename = NULL;
bool oids = false;
TableInfo *tblinfo;
@@ -694,6 +716,7 @@
{"no-privileges", no_argument, NULL, 'x'},
{"no-acl", no_argument, NULL, 'x'},
{"compress", required_argument, NULL, 'Z'},
+ {"where-clause", required_argument, NULL, 'w'},
{"help", no_argument, NULL, '?'},
{"version", no_argument, NULL, 'V'},
@@ -748,9 +771,9 @@
}
#ifdef HAVE_GETOPT_LONG
- while ((c = getopt_long(argc, argv, "abcCdDf:F:h:inNoOp:RsS:t:uU:vWxX:zZ:V?",
long_options, &optindex)) != -1)
+ while ((c = getopt_long(argc, argv,
+"abcCdDf:F:h:inNoOp:RsS:t:uU:vWxX:zZ:w:V?", long_options, &optindex)) != -1)
#else
- while ((c = getopt(argc, argv, "abcCdDf:F:h:inNoOp:RsS:t:uU:vWxX:zZ:V?-")) !=
-1)
+ while ((c = getopt(argc, argv, "abcCdDf:F:h:inNoOp:RsS:t:uU:vWxX:zZ:w:V?-"))
+!= -1)
#endif
{
@@ -881,6 +904,9 @@
case 'v': /* verbose */
g_verbose = true;
break;
+ case 'w': /* select query */
+ select_command = optarg;
+ break;
case 'W':
force_password = true;
@@ -1063,7 +1089,7 @@
tblinfo = dumpSchema(g_fout, &numTables, tablename, aclsSkip, oids,
schemaOnly, dataOnly);
if (!schemaOnly)
- dumpClasses(tblinfo, numTables, g_fout, tablename, oids,
force_quotes);
+ dumpClasses(tblinfo, numTables, g_fout, tablename, oids, force_quotes,
+select_command);
if (outputBlobs)
ArchiveEntry(g_fout, "0", "BLOBS", "BLOBS", NULL, "", "", "", "",
dumpBlobs, 0);
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
