In order to support some automation, I needed the ability to pull out
subsets of postgres tables in a format which could be loaded into
other postgres databases. Instead of writing a utility to replicate
the functionality in pg_dump, I created a new flag, --where, which
allows you to postpend where (or order or limit or whatever) sql
fragments at the end of the select done by pg_dump.
You must use the --insert option with this so that it will generate
inserts (which is done via select) instead of copy (which is done via
copy).
You almost certain will need to also use the --table option since most
likely the sql fragment will only be valid for one table instead of
all tables.
I find it very useful and hope you do to. This patch was created with
7.x postgresql, but if it doesn't apply cleanly against 8.x, I can
probably update it for you.
Index: doc/src/sgml/ref/pg_dump.sgml
===================================================================
RCS file: /src/cvs/postgres/doc/src/sgml/ref/pg_dump.sgml,v
retrieving revision 1.1.1.5
diff -u -u -r1.1.1.5 pg_dump.sgml
--- doc/src/sgml/ref/pg_dump.sgml 21 Oct 2004 22:49:04 -0000 1.1.1.5
+++ doc/src/sgml/ref/pg_dump.sgml 9 Sep 2005 23:31:28 -0000
@@ -412,6 +412,23 @@
</varlistentry>
<varlistentry>
+ <term><option>-w <replaceable class="parameter">sql
fragment</replaceable></option></term>
+ <term><option>--where <replaceable class="parameter">sql
fragment</replaceable></option></term>
+ <listitem>
+ <para>
+ When performing a <option>--table</option> and
<option>--insert</option> dump, allow an
+ additional restriction (or other SQL fragment such as ORDER or LIMIT)
+ to be placed on the data being retrieved for output. Example:
+
+<screen>
+<prompt>$</prompt> <userinput>pg_dump -dt pg_database -w "WHERE datname NOT
LIKE 'template%'"</userinput>
+</screen>
+
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
<term><option>-x</></term>
<term><option>--no-privileges</></term>
<term><option>--no-acl</></term>
Index: src/bin/pg_dump/pg_dump.c
===================================================================
RCS file: /src/cvs/postgres/src/bin/pg_dump/pg_dump.c,v
retrieving revision 1.1.1.6
diff -u -u -r1.1.1.6 pg_dump.c
--- src/bin/pg_dump/pg_dump.c 26 May 2004 18:27:23 -0000 1.1.1.6
+++ src/bin/pg_dump/pg_dump.c 9 Sep 2005 22:02:17 -0000
@@ -141,6 +141,7 @@
static char *selectTableName = NULL; /* name of a single table to dump */
static char *selectSchemaName = NULL; /* name of a single schema to dump */
+static char *selectWhere = NULL; /* restriction to place on (typically)
single table you are dumping */
char g_opaque_type[10]; /* name for the opaque type */
@@ -209,6 +210,7 @@
{"compress", required_argument, NULL, 'Z'},
{"help", no_argument, NULL, '?'},
{"version", no_argument, NULL, 'V'},
+ {"where", required_argument, NULL, 'w'},
/*
* the following options don't have an equivalent short option
@@ -258,7 +260,7 @@
}
}
- while ((c = getopt_long(argc, argv,
"abcCdDf:F:h:in:oOp:RsS:t:uU:vWxX:Z:",
+ while ((c = getopt_long(argc, argv,
"abcCdDf:F:h:in:oOp:RsS:t:uU:vw:WxX:Z:",
long_options,
&optindex)) != -1)
{
switch (c)
@@ -352,6 +354,10 @@
g_verbose = true;
break;
+ case 'w':
+ selectWhere = strdup(optarg);
+ break;
+
case 'W':
force_password = true;
break;
@@ -640,6 +646,8 @@
printf(_(" -S, --superuser=NAME specify the superuser user name to
use in\n"
" plain text format\n"));
printf(_(" -t, --table=TABLE dump the named table only\n"));
+ printf(_(" -w, --where='SQLfrag' If dumping named table and dumping
as insert,\n"
+ " append sql (including WHERE) to
select subset\n"));
printf(_(" -x, --no-privileges do not dump privileges
(grant/revoke)\n"));
printf(_(" -X disable-triggers, --disable-triggers\n"
" disable triggers during
data-only restore\n"));
@@ -905,16 +913,16 @@
if (fout->remoteVersion >= 70100)
{
appendPQExpBuffer(q, "DECLARE _pg_dump_cursor CURSOR FOR "
- "SELECT * FROM ONLY %s",
+ "SELECT * FROM ONLY %s %s",
fmtQualifiedId(tbinfo->relnamespace->nspname,
-
classname));
+
classname), selectWhere?selectWhere:"");
}
else
{
appendPQExpBuffer(q, "DECLARE _pg_dump_cursor CURSOR FOR "
- "SELECT * FROM %s",
+ "SELECT * FROM %s %s",
fmtQualifiedId(tbinfo->relnamespace->nspname,
-
classname));
+
classname), selectWhere?selectWhere:"");
}
res = PQexec(g_conn, q->data);
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
