Thread: pg_dump: multiple tables, schemas with exclusions and wildcards
Here's the latest pg_dump patch I've been (too sporadically) working on. I abandoned building linked lists and decided to make the backend do all the work, from building the list of good relations, to doing the POSIX regex matching. I've added numerous examples to the docs, but it may still need some more explaining. It should be nearly 100% backwards compatible with any existing scripts that use a single -t as well. -- Greg Sabino Mullane greg@turnstep.com End Point Corporation PGP Key: 0x14964AC8 200607162215 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
Attachment
Greg Sabino Mullane wrote: -- Start of PGP signed section. > Here's the latest pg_dump patch I've been (too sporadically) working on. > I abandoned building linked lists and decided to make the backend do all > the work, from building the list of good relations, to doing the POSIX > regex matching. I've added numerous examples to the docs, but it may > still need some more explaining. It should be nearly 100% backwards > compatible with any existing scripts that use a single -t as well. I have adjusted your code for clarity, and clarified the documentation a little. Please test and make sure it is OK for you. Thanks. -- Bruce Momjian bruce@momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + Index: doc/src/sgml/ref/pg_dump.sgml =================================================================== RCS file: /cvsroot/pgsql/doc/src/sgml/ref/pg_dump.sgml,v retrieving revision 1.86 diff -c -c -r1.86 pg_dump.sgml *** doc/src/sgml/ref/pg_dump.sgml 13 May 2006 17:10:35 -0000 1.86 --- doc/src/sgml/ref/pg_dump.sgml 1 Aug 2006 04:53:52 -0000 *************** *** 398,415 **** <listitem> <para> Dump data for <replaceable class="parameter">table</replaceable> ! only. It is possible for there to be ! multiple tables with the same name in different schemas; if that ! is the case, all matching tables will be dumped. Specify both ! <option>--schema</> and <option>--table</> to select just one table. </para> <note> <para> In this mode, <application>pg_dump</application> makes no ! attempt to dump any other database objects that the selected table may depend upon. Therefore, there is no guarantee ! that the results of a single-table dump can be successfully restored by themselves into a clean database. </para> </note> --- 398,460 ---- <listitem> <para> Dump data for <replaceable class="parameter">table</replaceable> ! only. It is possible for there to be multiple tables with the same ! name in different schemas; if that is the case, all matching tables ! will be dumped. Also, if any POSIX regular expression character appears ! in the table name (<literal>([{\.?+</>, the string will be interpreted ! as a regular expression. Note that when in regular expression mode, the ! string will not be anchored to the start/end unless <literal>^</> and ! <literal>$</> are used at the beginning/end of the string. </para> + <para> + The options <option>-t</>, <option>-T</>, <option>-n</>, and <option>-N</> + can be used together to achieve a high degree of control over what is + dumped. Multiple arguments can be used, and are parsed in the order + given to build a list of vaid tables and schemas. The schema options are + parsed first to create a list of schemas to dump, and then the table options + are parsed to only find tables in the matching schemas. + </para> + + <para>For examples, to dump a single table named <literal>pg_class</>: + + <screen> + <prompt>$</prompt> <userinput>pg_dump -t pg_class mydb > db.out</userinput> + </screen> + </para> + + <para>To dump all tables starting with <literal>employee</> in the + <literal>detroit</> schema, except for the table named <literal>employee_log</literal>: + + <screen> + <prompt>$</prompt> <userinput>pg_dump -n detroit -t ^employee -T employee_log mydb > db.out</userinput> + </screen> + </para> + + <para>To dump all schemas starting with <literal>east</> or <literal>west</> and ending in + <literal>gsm</>, but not schemas that contain the letters <literal>test</>, except for + one named <literal>east_alpha_test_five</>: + + <screen> + <prompt>$</prompt> <userinput>pg_dump -n "^(east|west).*gsm$" -N test -n east_alpha_test_five mydb > db.out</userinput> + </screen> + </para> + + + <para>To dump all tables except for those beginning with <literal>ts_</literal>: + + <screen> + <prompt>$</prompt> <userinput>pg_dump -T "^ts_" mydb > db.out</userinput> + </screen> + </para> + + <note> <para> In this mode, <application>pg_dump</application> makes no ! attempt to dump any other database objects that the selected tables may depend upon. Therefore, there is no guarantee ! that the results of a specific-table dump can be successfully restored by themselves into a clean database. </para> </note> *************** *** 417,422 **** --- 462,505 ---- </varlistentry> <varlistentry> + <term><option>-T <replaceable class="parameter">table</replaceable></option></term> + <term><option>--exclude-table=<replaceable class="parameter">table</replaceable></option></term> + <listitem> + <para> + Do not dump any matching <replaceable class="parameter">tables</replaceable>. + More than one option may be used, and POSIX regular expressions are handled just + like <literal>-t</>. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><option>-n <replaceable class="parameter">schema</replaceable></option></term> + <term><option>--schema=<replaceable class="parameter">schema</replaceable></option></term> + <listitem> + <para> + Dump only the matching <replaceable class="parameter">schemas</replaceable>. + More than one option may be used, and POSIX regular expressions are handled just + like <literal>-t</>. + </para> + + </listitem> + </varlistentry> + + <varlistentry> + <term><option>-N <replaceable class="parameter">schema</replaceable></option></term> + <term><option>--exclude-schema=<replaceable class="parameter">schema</replaceable></option></term> + <listitem> + <para> + Do not dump the matching <replaceable class="parameter">schemas</replaceable>. + More than one option may be used, and POSIX regular expressions are handled just + like <literal>-t</>. + </para> + + </listitem> + </varlistentry> + + <varlistentry> <term><option>-v</></term> <term><option>--verbose</></term> <listitem> Index: src/bin/pg_dump/common.c =================================================================== RCS file: /cvsroot/pgsql/src/bin/pg_dump/common.c,v retrieving revision 1.91 diff -c -c -r1.91 common.c *** src/bin/pg_dump/common.c 14 Jul 2006 14:52:26 -0000 1.91 --- src/bin/pg_dump/common.c 1 Aug 2006 04:53:54 -0000 *************** *** 72,80 **** * Collect information about all potentially dumpable objects */ TableInfo * ! getSchemaData(int *numTablesPtr, ! const bool schemaOnly, ! const bool dataOnly) { NamespaceInfo *nsinfo; AggInfo *agginfo; --- 72,78 ---- * Collect information about all potentially dumpable objects */ TableInfo * ! getSchemaData(int *numTablesPtr) { NamespaceInfo *nsinfo; AggInfo *agginfo; Index: src/bin/pg_dump/pg_dump.c =================================================================== RCS file: /cvsroot/pgsql/src/bin/pg_dump/pg_dump.c,v retrieving revision 1.442 diff -c -c -r1.442 pg_dump.c *** src/bin/pg_dump/pg_dump.c 27 Jul 2006 19:52:06 -0000 1.442 --- src/bin/pg_dump/pg_dump.c 1 Aug 2006 04:53:59 -0000 *************** *** 91,98 **** /* obsolete as of 7.3: */ static Oid g_last_builtin_oid; /* value of the last builtin oid */ ! static char *selectTableName = NULL; /* name of a single table to dump */ ! static char *selectSchemaName = NULL; /* name of a single schema to dump */ char g_opaque_type[10]; /* name for the opaque type */ --- 91,109 ---- /* obsolete as of 7.3: */ static Oid g_last_builtin_oid; /* value of the last builtin oid */ ! /* select and exclude tables and schemas */ ! struct _relnameArg { ! char *name; /* name of the relation */ ! bool is_include; /* include/exclude? */ ! struct _relnameArg *next; ! }; ! typedef struct _relnameArg relnameArg; ! ! relnameArg *schemaList = NULL; /* List of schemas to include/exclude */ ! relnameArg *tableList = NULL; /* List of tables to include/exclude */ ! ! char *goodSchemas = NULL; /* Final list of schemas to dump by oid */ ! char *goodTables = NULL; /* Final list of tables to dump by oid */ char g_opaque_type[10]; /* name for the opaque type */ *************** *** 180,185 **** --- 191,201 ---- int main(int argc, char **argv) { + relnameArg *RelationList = NULL; + PQExpBuffer query = createPQExpBuffer(); + PGresult *res; + relnameArg *newrelname; + relnameArg *thisrelname = NULL; int c; const char *filename = NULL; const char *format = "p"; *************** *** 194,200 **** int numTables; DumpableObject **dobjs; int numObjs; ! int i; bool force_password = false; int compressLevel = -1; bool ignore_version = false; --- 210,217 ---- int numTables; DumpableObject **dobjs; int numObjs; ! int i, j; ! bool include_exclude_switch; bool force_password = false; int compressLevel = -1; bool ignore_version = false; *************** *** 226,234 **** --- 243,253 ---- {"no-owner", no_argument, NULL, 'O'}, {"port", required_argument, NULL, 'p'}, {"schema", required_argument, NULL, 'n'}, + {"exclude-schema", required_argument, NULL, 'N'}, {"schema-only", no_argument, NULL, 's'}, {"superuser", required_argument, NULL, 'S'}, {"table", required_argument, NULL, 't'}, + {"exclude-table", required_argument, NULL, 'T'}, {"password", no_argument, NULL, 'W'}, {"username", required_argument, NULL, 'U'}, {"verbose", no_argument, NULL, 'v'}, *************** *** 281,287 **** } } ! while ((c = getopt_long(argc, argv, "abcCdDE:f:F:h:in:oOp:RsS:t:uU:vWxX:Z:", long_options, &optindex)) != -1) { switch (c) --- 300,306 ---- } } ! while ((c = getopt_long(argc, argv, "abcCdDE:f:F:h:in:N:oOp:RsS:t:T:uU:vWxX:Z:", long_options, &optindex)) != -1) { switch (c) *************** *** 332,339 **** ignore_version = true; break; ! case 'n': /* Dump data for this schema only */ ! selectSchemaName = strdup(optarg); break; case 'o': /* Dump oids */ --- 351,386 ---- ignore_version = true; break; ! case 'n': /* Specifically include matching schemas */ ! case 'N': /* Specifically exclude matching schemas */ ! case 't': /* Specifically include matching tables */ ! case 'T': /* Specifically exclude matching tables */ ! ! if (strlen(optarg) < 1) ! { ! fprintf(stderr, _("%s: invalid -%c option\n"), progname, c); ! exit(1); ! } ! ! /* Create a struct for this name */ ! newrelname = (relnameArg *) malloc (sizeof(relnameArg)); ! newrelname->next = NULL; ! newrelname->name = strdup(optarg); ! newrelname->is_include = (c == 'n' || c == 't') ? true : false; ! RelationList = (c == 't' || c == 'T') ? tableList : schemaList; ! if (RelationList == NULL) ! { ! if (c == 't' || c == 'T') ! tableList = thisrelname = newrelname; ! else ! schemaList = thisrelname = newrelname; ! } ! else ! { ! thisrelname->next = newrelname; ! thisrelname = newrelname; ! } ! break; case 'o': /* Dump oids */ *************** *** 361,370 **** outputSuperuser = strdup(optarg); break; - case 't': /* Dump data for this table only */ - selectTableName = strdup(optarg); - break; - case 'u': force_password = true; username = simple_prompt("User name: ", 100, true); --- 408,413 ---- *************** *** 449,455 **** exit(1); } ! if (selectTableName != NULL || selectSchemaName != NULL) outputBlobs = false; if (dumpInserts == true && oids == true) --- 492,498 ---- exit(1); } ! if (goodTables != NULL || goodSchemas != NULL) outputBlobs = false; if (dumpInserts == true && oids == true) *************** *** 568,578 **** write_msg(NULL, "last built-in OID is %u\n", g_last_builtin_oid); } /* * Now scan the database and create DumpableObject structs for all the * objects we intend to dump. */ ! tblinfo = getSchemaData(&numTables, schemaOnly, dataOnly); if (!schemaOnly) getTableData(tblinfo, numTables, oids); --- 611,763 ---- write_msg(NULL, "last built-in OID is %u\n", g_last_builtin_oid); } + + if (schemaList != NULL && g_fout->remoteVersion < 70300) + { + write_msg(NULL, "Postgres must be at least version 7.3 to use schema switches\n"); + exit_nicely(); + } + + /* Check schema selection flags */ + resetPQExpBuffer(query); + include_exclude_switch = true; + for (thisrelname = schemaList; thisrelname; thisrelname = thisrelname->next) + { + if (include_exclude_switch) + { + /* Special case for when -N is the first argument */ + if (thisrelname == RelationList && !thisrelname->is_include) + appendPQExpBuffer(query, "SELECT oid FROM pg_catalog.pg_namespace EXCEPT\n"); + + appendPQExpBuffer(query, "SELECT oid FROM pg_catalog.pg_namespace WHERE"); + } + + appendPQExpBuffer(query, "%s nspname %c ", include_exclude_switch ? "" : " OR", + /* any meta-characters? */ + strpbrk(thisrelname->name,"([{\\.?+") == NULL ? '=' : '~'); + appendStringLiteralAH(query, thisrelname->name, g_fout); + + if (thisrelname->next && thisrelname->next->is_include == thisrelname->is_include) + include_exclude_switch = false; + else + { + include_exclude_switch = true; + + /* Add the joiner if needed */ + if (thisrelname->next) + appendPQExpBuffer(query, "\n%s\n", + thisrelname->next->is_include ? "UNION" : "EXCEPT"); + } + } + + /* Construct OID list of matching schemas */ + if (schemaList) + { + res = PQexec(g_conn, query->data); + check_sql_result(res, g_conn, query->data, PGRES_TUPLES_OK); + if (PQntuples(res) == 0) + { + write_msg(NULL, "No matching schemas were found\n"); + exit_nicely(); + } + + for (i = 0, j = strlen(" 0 "); i < PQntuples(res); i++) + j += strlen(PQgetvalue(res, i, 0)) + 1; + + /* + * Need to use comma separators so it can be used by IN. zero + * is a dummy placeholder. + */ + goodSchemas = malloc(j + 1); + strcpy(goodSchemas, " "); + for (i = 0; i < PQntuples(res); i++) + { + strcat(goodSchemas, PQgetvalue(res, i, 0)); + strcat(goodSchemas, " "); + } + strcat(goodSchemas, "0 "); + } + + /* Check table selection flags */ + resetPQExpBuffer(query); + include_exclude_switch = true; + for (thisrelname = tableList; thisrelname; thisrelname = thisrelname->next) + { + if (include_exclude_switch) + { + /* Special case for when -T is the first argument */ + if (thisrelname == RelationList && !thisrelname->is_include && !strlen(query->data)) + appendPQExpBuffer(query, "SELECT oid FROM pg_catalog.pg_class WHERE relkind='r' EXCEPT\n"); + + appendPQExpBuffer(query, "SELECT oid FROM pg_catalog.pg_class WHERE relkind='r' AND ("); + } + + appendPQExpBuffer(query, "%srelname %c ", include_exclude_switch ? "" : " OR ", + /* any meta-characters? */ + strpbrk(thisrelname->name,"([{\\.?+") == NULL ? '=' : '~'); + appendStringLiteralAH(query, thisrelname->name, g_fout); + + if (thisrelname->next && thisrelname->next->is_include == thisrelname->is_include) + include_exclude_switch = false; + else + { + include_exclude_switch = true; + appendPQExpBuffer(query, ")"); + + /* Add the joiner if needed */ + if (thisrelname->next) + appendPQExpBuffer(query, "\n%s\n", thisrelname->next->is_include ? + "UNION" : "EXCEPT"); + } + } + + /* Construct OID list of matching tables */ + if (tableList) + { + if (goodSchemas != NULL) + { + char *goodSchemas_commas = strdup(goodSchemas + 1), *p; + + /* Construct SQL string by making all spaces commas, skip first/last. */ + for (p = goodSchemas_commas; *(p+1); p++) + { + if (*p == ' ') + *p = ','; + } + *p = '\0'; + + appendPQExpBuffer(query, + "\nINTERSECT\nSELECT oid FROM pg_catalog.pg_class WHERE relkind='r' AND relnamespace IN (%s)\n", + goodSchemas_commas); + } + + res = PQexec(g_conn, query->data); + check_sql_result(res, g_conn, query->data, PGRES_TUPLES_OK); + if (PQntuples(res) == 0) + { + write_msg(NULL, "No matching tables were found\n"); + exit_nicely(); + } + + for (i = 0, j = strlen(" "); i < PQntuples(res); i++) + j += strlen(PQgetvalue(res, i, 0)) + 1; + + goodTables = malloc(j + 1); + strcpy(goodTables, " "); + for (i = 0; i < PQntuples(res); i++) + { + strcat(goodTables, PQgetvalue(res, i, 0)); + strcat(goodTables, " "); + } + } + + destroyPQExpBuffer(query); + /* * Now scan the database and create DumpableObject structs for all the * objects we intend to dump. */ ! tblinfo = getSchemaData(&numTables); if (!schemaOnly) getTableData(tblinfo, numTables, oids); *************** *** 628,634 **** dumpStdStrings(g_fout); /* The database item is always next, unless we don't want it at all */ ! if (!dataOnly && selectTableName == NULL && selectSchemaName == NULL) dumpDatabase(g_fout); /* Now the rearrangeable objects. */ --- 813,819 ---- dumpStdStrings(g_fout); /* The database item is always next, unless we don't want it at all */ ! if (!dataOnly && goodTables == NULL && goodSchemas == NULL) dumpDatabase(g_fout); /* Now the rearrangeable objects. */ *************** *** 687,714 **** printf(_(" --version output version information, then exit\n")); printf(_("\nOptions controlling the output content:\n")); ! printf(_(" -a, --data-only dump only the data, not the schema\n")); ! printf(_(" -c, --clean clean (drop) schema prior to create\n")); ! printf(_(" -C, --create include commands to create database in dump\n")); ! printf(_(" -d, --inserts dump data as INSERT, rather than COPY, commands\n")); ! printf(_(" -D, --column-inserts dump data as INSERT commands with column names\n")); ! printf(_(" -E, --encoding=ENCODING dump the data in encoding ENCODING\n")); ! printf(_(" -n, --schema=SCHEMA dump the named schema only\n")); ! printf(_(" -o, --oids include OIDs in dump\n")); ! printf(_(" -O, --no-owner skip restoration of object ownership\n" ! " in plain text format\n")); ! printf(_(" -s, --schema-only dump only the schema, no data\n")); ! 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(_(" -x, --no-privileges do not dump privileges (grant/revoke)\n")); printf(_(" -X disable-dollar-quoting, --disable-dollar-quoting\n" ! " disable dollar quoting, use SQL standard quoting\n")); printf(_(" -X disable-triggers, --disable-triggers\n" ! " disable triggers during data-only restore\n")); printf(_(" -X use-set-session-authorization, --use-set-session-authorization\n" ! " use SESSION AUTHORIZATION commands instead of\n" ! " OWNER TO commands\n")); printf(_("\nConnection options:\n")); printf(_(" -h, --host=HOSTNAME database server host or socket directory\n")); --- 872,901 ---- printf(_(" --version output version information, then exit\n")); printf(_("\nOptions controlling the output content:\n")); ! printf(_(" -a, --data-only dump only the data, not the schema\n")); ! printf(_(" -c, --clean clean (drop) schema prior to create\n")); ! printf(_(" -C, --create include commands to create database in dump\n")); ! printf(_(" -d, --inserts dump data as INSERT, rather than COPY, commands\n")); ! printf(_(" -D, --column-inserts dump data as INSERT commands with column names\n")); ! printf(_(" -E, --encoding=ENCODING dump the data in encoding ENCODING\n")); ! printf(_(" -n, --schema=SCHEMA dump the named schema only\n")); ! printf(_(" -N, --exclude-schema=SCHEMA do NOT dump the named schema\n")); ! printf(_(" -o, --oids include OIDs in dump\n")); ! printf(_(" -O, --no-owner skip restoration of object ownership\n" ! " in plain text format\n")); ! printf(_(" -s, --schema-only dump only the schema, no data\n")); ! 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(_(" -T, --exclude-table=TABLE do NOT dump the named table\n")); ! printf(_(" -x, --no-privileges do not dump privileges (grant/revoke)\n")); printf(_(" -X disable-dollar-quoting, --disable-dollar-quoting\n" ! " disable dollar quoting, use SQL standard quoting\n")); printf(_(" -X disable-triggers, --disable-triggers\n" ! " disable triggers during data-only restore\n")); printf(_(" -X use-set-session-authorization, --use-set-session-authorization\n" ! " use SESSION AUTHORIZATION commands instead of\n" ! " OWNER TO commands\n")); printf(_("\nConnection options:\n")); printf(_(" -h, --host=HOSTNAME database server host or socket directory\n")); *************** *** 724,729 **** --- 911,936 ---- void exit_nicely(void) { + /* Free up any relation names */ + relnameArg *nextrelname; + + free(goodTables); + free(goodSchemas); + while (tableList != NULL) + { + nextrelname = tableList->next; + free(tableList->name); + free(tableList); + tableList = nextrelname; + } + while (schemaList != NULL) + { + nextrelname = schemaList->next; + free(schemaList->name); + free(schemaList); + schemaList = nextrelname; + } + PQfinish(g_conn); if (g_verbose) write_msg(NULL, "*** aborted because of error\n"); *************** *** 738,755 **** selectDumpableNamespace(NamespaceInfo *nsinfo) { /* ! * If a specific table is being dumped, do not dump any complete ! * namespaces. If a specific namespace is being dumped, dump just that ! * namespace. Otherwise, dump all non-system namespaces. */ ! if (selectTableName != NULL) nsinfo->dobj.dump = false; ! else if (selectSchemaName != NULL) { ! if (strcmp(nsinfo->dobj.name, selectSchemaName) == 0) nsinfo->dobj.dump = true; ! else ! nsinfo->dobj.dump = false; } else if (strncmp(nsinfo->dobj.name, "pg_", 3) == 0 || strcmp(nsinfo->dobj.name, "information_schema") == 0) --- 945,964 ---- selectDumpableNamespace(NamespaceInfo *nsinfo) { /* ! * If specific tables are being dumped, do not dump any complete ! * namespaces. If specific namespaces are being dumped, dump just ! * those namespaces. Otherwise, dump all non-system namespaces. */ ! if (goodTables != NULL) nsinfo->dobj.dump = false; ! else if (goodSchemas != NULL) { ! char *searchname = NULL; ! searchname = malloc(20); ! sprintf(searchname, " %d ", nsinfo->dobj.catId.oid); ! if (strstr(goodSchemas, searchname) != NULL) nsinfo->dobj.dump = true; ! free(searchname); } else if (strncmp(nsinfo->dobj.name, "pg_", 3) == 0 || strcmp(nsinfo->dobj.name, "information_schema") == 0) *************** *** 771,786 **** * dump. */ tbinfo->dobj.dump = false; ! if (tbinfo->dobj.namespace->dobj.dump) tbinfo->dobj.dump = true; ! else if (selectTableName != NULL && ! strcmp(tbinfo->dobj.name, selectTableName) == 0) { ! /* If both -s and -t specified, must match both to dump */ ! if (selectSchemaName == NULL) ! tbinfo->dobj.dump = true; ! else if (strcmp(tbinfo->dobj.namespace->dobj.name, selectSchemaName) == 0) tbinfo->dobj.dump = true; } } --- 980,995 ---- * dump. */ tbinfo->dobj.dump = false; ! if (tbinfo->dobj.namespace->dobj.dump || goodTables == NULL) tbinfo->dobj.dump = true; ! else { ! char *searchname = NULL; ! searchname = malloc(20); ! sprintf(searchname, " %d ", tbinfo->dobj.catId.oid); ! if (strstr(goodTables, searchname) != NULL) tbinfo->dobj.dump = true; + free(searchname); } } *************** *** 1722,1746 **** nsinfo[i].dobj.name); } - /* - * If the user attempted to dump a specific namespace, check to ensure - * that the specified namespace actually exists. - */ - if (selectSchemaName) - { - for (i = 0; i < ntups; i++) - if (strcmp(nsinfo[i].dobj.name, selectSchemaName) == 0) - break; - - /* Didn't find a match */ - if (i == ntups) - { - write_msg(NULL, "specified schema \"%s\" does not exist\n", - selectSchemaName); - exit_nicely(); - } - } - PQclear(res); destroyPQExpBuffer(query); --- 1931,1936 ---- *************** *** 2905,2930 **** tblinfo[i].dobj.name); } - /* - * If the user is attempting to dump a specific table, check to ensure - * that the specified table actually exists. (This is a bit simplistic - * since we don't fully check the combination of -n and -t switches.) - */ - if (selectTableName) - { - for (i = 0; i < ntups; i++) - if (strcmp(tblinfo[i].dobj.name, selectTableName) == 0) - break; - - /* Didn't find a match */ - if (i == ntups) - { - write_msg(NULL, "specified table \"%s\" does not exist\n", - selectTableName); - exit_nicely(); - } - } - PQclear(res); destroyPQExpBuffer(query); destroyPQExpBuffer(delqry); --- 3095,3100 ---- *************** *** 5438,5444 **** static bool shouldDumpProcLangs(void) { ! if (selectTableName != NULL || selectSchemaName != NULL) return false; /* And they're schema not data */ if (dataOnly) --- 5608,5614 ---- static bool shouldDumpProcLangs(void) { ! if (goodTables != NULL || goodSchemas != NULL) return false; /* And they're schema not data */ if (dataOnly) Index: src/bin/pg_dump/pg_dump.h =================================================================== RCS file: /cvsroot/pgsql/src/bin/pg_dump/pg_dump.h,v retrieving revision 1.127 diff -c -c -r1.127 pg_dump.h *** src/bin/pg_dump/pg_dump.h 27 Jul 2006 19:52:06 -0000 1.127 --- src/bin/pg_dump/pg_dump.h 1 Aug 2006 04:53:59 -0000 *************** *** 340,348 **** * common utility functions */ ! extern TableInfo *getSchemaData(int *numTablesPtr, ! const bool schemaOnly, ! const bool dataOnly); typedef enum _OidOptions { --- 340,346 ---- * common utility functions */ ! extern TableInfo *getSchemaData(int *numTablesPtr); typedef enum _OidOptions {
Greg Sabino Mullane wrote: -- Start of PGP signed section. > Here's the latest pg_dump patch I've been (too sporadically) working on. > I abandoned building linked lists and decided to make the backend do all > the work, from building the list of good relations, to doing the POSIX > regex matching. I've added numerous examples to the docs, but it may > still need some more explaining. It should be nearly 100% backwards > compatible with any existing scripts that use a single -t as well. Very updated patch attached and applied. I did reformatting, variable renaming, and some cleanup on the linked list handling. Thanks. I am very glad to get this long-overdue TODO item done. -- Bruce Momjian bruce@momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + Index: doc/src/sgml/ref/pg_dump.sgml =================================================================== RCS file: /cvsroot/pgsql/doc/src/sgml/ref/pg_dump.sgml,v retrieving revision 1.86 diff -c -c -r1.86 pg_dump.sgml *** doc/src/sgml/ref/pg_dump.sgml 13 May 2006 17:10:35 -0000 1.86 --- doc/src/sgml/ref/pg_dump.sgml 1 Aug 2006 17:44:56 -0000 *************** *** 398,415 **** <listitem> <para> Dump data for <replaceable class="parameter">table</replaceable> ! only. It is possible for there to be ! multiple tables with the same name in different schemas; if that ! is the case, all matching tables will be dumped. Specify both ! <option>--schema</> and <option>--table</> to select just one table. </para> <note> <para> In this mode, <application>pg_dump</application> makes no ! attempt to dump any other database objects that the selected table may depend upon. Therefore, there is no guarantee ! that the results of a single-table dump can be successfully restored by themselves into a clean database. </para> </note> --- 398,460 ---- <listitem> <para> Dump data for <replaceable class="parameter">table</replaceable> ! only. It is possible for there to be multiple tables with the same ! name in different schemas; if that is the case, all matching tables ! will be dumped. Also, if any POSIX regular expression character appears ! in the table name (<literal>([{\.?+</>, the string will be interpreted ! as a regular expression. Note that when in regular expression mode, the ! string will not be anchored to the start/end unless <literal>^</> and ! <literal>$</> are used at the beginning/end of the string. </para> + <para> + The options <option>-t</>, <option>-T</>, <option>-n</>, and <option>-N</> + can be used together to achieve a high degree of control over what is + dumped. Multiple arguments can be used, and are parsed in the order + given to build a list of valid tables and schemas. The schema options are + parsed first to create a list of schemas to dump, and then the table options + are parsed to only find tables in the matching schemas. + </para> + + <para>For example, to dump a single table named <literal>pg_class</>: + + <screen> + <prompt>$</prompt> <userinput>pg_dump -t pg_class mydb > db.out</userinput> + </screen> + </para> + + <para>To dump all tables starting with <literal>employee</> in the + <literal>detroit</> schema, except for the table named <literal>employee_log</literal>: + + <screen> + <prompt>$</prompt> <userinput>pg_dump -n detroit -t ^employee -T employee_log mydb > db.out</userinput> + </screen> + </para> + + <para>To dump all schemas starting with <literal>east</> or <literal>west</> and ending in + <literal>gsm</>, but not schemas that contain the letters <literal>test</>, except for + one named <literal>east_alpha_test_five</>: + + <screen> + <prompt>$</prompt> <userinput>pg_dump -n "^(east|west).*gsm$" -N test -n east_alpha_test_five mydb > db.out</userinput> + </screen> + </para> + + + <para>To dump all tables except for those beginning with <literal>ts_</literal>: + + <screen> + <prompt>$</prompt> <userinput>pg_dump -T "^ts_" mydb > db.out</userinput> + </screen> + </para> + + <note> <para> In this mode, <application>pg_dump</application> makes no ! attempt to dump any other database objects that the selected tables may depend upon. Therefore, there is no guarantee ! that the results of a specific-table dump can be successfully restored by themselves into a clean database. </para> </note> *************** *** 417,422 **** --- 462,505 ---- </varlistentry> <varlistentry> + <term><option>-T <replaceable class="parameter">table</replaceable></option></term> + <term><option>--exclude-table=<replaceable class="parameter">table</replaceable></option></term> + <listitem> + <para> + Do not dump any matching <replaceable class="parameter">tables</replaceable>. + More than one option can be used, and POSIX regular expressions are handled just + like <literal>-t</>. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><option>-n <replaceable class="parameter">schema</replaceable></option></term> + <term><option>--schema=<replaceable class="parameter">schema</replaceable></option></term> + <listitem> + <para> + Dump only the matching <replaceable class="parameter">schemas</replaceable>. + More than one option can be used, and POSIX regular expressions are handled just + like <literal>-t</>. + </para> + + </listitem> + </varlistentry> + + <varlistentry> + <term><option>-N <replaceable class="parameter">schema</replaceable></option></term> + <term><option>--exclude-schema=<replaceable class="parameter">schema</replaceable></option></term> + <listitem> + <para> + Do not dump the matching <replaceable class="parameter">schemas</replaceable>. + More than one option can be used, and POSIX regular expressions are handled just + like <literal>-t</>. + </para> + + </listitem> + </varlistentry> + + <varlistentry> <term><option>-v</></term> <term><option>--verbose</></term> <listitem> Index: src/bin/pg_dump/common.c =================================================================== RCS file: /cvsroot/pgsql/src/bin/pg_dump/common.c,v retrieving revision 1.91 diff -c -c -r1.91 common.c *** src/bin/pg_dump/common.c 14 Jul 2006 14:52:26 -0000 1.91 --- src/bin/pg_dump/common.c 1 Aug 2006 17:44:57 -0000 *************** *** 72,80 **** * Collect information about all potentially dumpable objects */ TableInfo * ! getSchemaData(int *numTablesPtr, ! const bool schemaOnly, ! const bool dataOnly) { NamespaceInfo *nsinfo; AggInfo *agginfo; --- 72,78 ---- * Collect information about all potentially dumpable objects */ TableInfo * ! getSchemaData(int *numTablesPtr) { NamespaceInfo *nsinfo; AggInfo *agginfo; Index: src/bin/pg_dump/pg_dump.c =================================================================== RCS file: /cvsroot/pgsql/src/bin/pg_dump/pg_dump.c,v retrieving revision 1.442 diff -c -c -r1.442 pg_dump.c *** src/bin/pg_dump/pg_dump.c 27 Jul 2006 19:52:06 -0000 1.442 --- src/bin/pg_dump/pg_dump.c 1 Aug 2006 17:44:59 -0000 *************** *** 91,98 **** /* obsolete as of 7.3: */ static Oid g_last_builtin_oid; /* value of the last builtin oid */ ! static char *selectTableName = NULL; /* name of a single table to dump */ ! static char *selectSchemaName = NULL; /* name of a single schema to dump */ char g_opaque_type[10]; /* name for the opaque type */ --- 91,109 ---- /* obsolete as of 7.3: */ static Oid g_last_builtin_oid; /* value of the last builtin oid */ ! /* select and exclude tables and schemas */ ! typedef struct objnameArg ! { ! struct objnameArg *next; ! char *name; /* name of the relation */ ! bool is_include; /* include/exclude? */ ! } objnameArg; ! ! objnameArg *schemaList = NULL; /* List of schemas to include/exclude */ ! objnameArg *tableList = NULL; /* List of tables to include/exclude */ ! ! char *matchingSchemas = NULL; /* Final list of schemas to dump by oid */ ! char *matchingTables = NULL; /* Final list of tables to dump by oid */ char g_opaque_type[10]; /* name for the opaque type */ *************** *** 180,185 **** --- 191,199 ---- int main(int argc, char **argv) { + PQExpBuffer query = createPQExpBuffer(); + PGresult *res; + objnameArg *this_obj_name = NULL; int c; const char *filename = NULL; const char *format = "p"; *************** *** 195,200 **** --- 209,215 ---- DumpableObject **dobjs; int numObjs; int i; + bool switch_include_exclude; bool force_password = false; int compressLevel = -1; bool ignore_version = false; *************** *** 226,234 **** --- 241,251 ---- {"no-owner", no_argument, NULL, 'O'}, {"port", required_argument, NULL, 'p'}, {"schema", required_argument, NULL, 'n'}, + {"exclude-schema", required_argument, NULL, 'N'}, {"schema-only", no_argument, NULL, 's'}, {"superuser", required_argument, NULL, 'S'}, {"table", required_argument, NULL, 't'}, + {"exclude-table", required_argument, NULL, 'T'}, {"password", no_argument, NULL, 'W'}, {"username", required_argument, NULL, 'U'}, {"verbose", no_argument, NULL, 'v'}, *************** *** 281,289 **** } } ! while ((c = getopt_long(argc, argv, "abcCdDE:f:F:h:in:oOp:RsS:t:uU:vWxX:Z:", long_options, &optindex)) != -1) { switch (c) { case 'a': /* Dump data only */ --- 298,308 ---- } } ! while ((c = getopt_long(argc, argv, "abcCdDE:f:F:h:in:N:oOp:RsS:t:T:uU:vWxX:Z:", long_options, &optindex)) != -1) { + objnameArg *schemaList_tail = NULL, *tableList_tail = NULL; + switch (c) { case 'a': /* Dump data only */ *************** *** 332,339 **** ignore_version = true; break; ! case 'n': /* Dump data for this schema only */ ! selectSchemaName = strdup(optarg); break; case 'o': /* Dump oids */ --- 351,392 ---- ignore_version = true; break; ! case 'n': /* Include schemas */ ! case 'N': /* Exclude schemas */ ! case 't': /* Include tables */ ! case 'T': /* Exclude tables */ ! ! if (strlen(optarg) < 1) ! { ! fprintf(stderr, _("%s: invalid -%c option\n"), progname, c); ! exit(1); ! } ! ! { ! /* Create a struct for this name */ ! objnameArg *new_obj_name = (objnameArg *) ! malloc(sizeof(objnameArg)); ! ! new_obj_name->next = NULL; ! new_obj_name->name = strdup(optarg); ! new_obj_name->is_include = islower(c) ? true : false; ! ! /* add new entry to the proper list */ ! if (tolower(c) == 'n') ! { ! if (!schemaList_tail) ! schemaList_tail = schemaList = new_obj_name; ! else ! schemaList_tail = schemaList_tail->next = new_obj_name; ! } ! else ! { ! if (!tableList_tail) ! tableList_tail = tableList = new_obj_name; ! else ! tableList_tail = tableList_tail->next = new_obj_name; ! } ! } break; case 'o': /* Dump oids */ *************** *** 361,370 **** outputSuperuser = strdup(optarg); break; - case 't': /* Dump data for this table only */ - selectTableName = strdup(optarg); - break; - case 'u': force_password = true; username = simple_prompt("User name: ", 100, true); --- 414,419 ---- *************** *** 449,455 **** exit(1); } ! if (selectTableName != NULL || selectSchemaName != NULL) outputBlobs = false; if (dumpInserts == true && oids == true) --- 498,504 ---- exit(1); } ! if (matchingTables != NULL || matchingSchemas != NULL) outputBlobs = false; if (dumpInserts == true && oids == true) *************** *** 568,578 **** write_msg(NULL, "last built-in OID is %u\n", g_last_builtin_oid); } /* * Now scan the database and create DumpableObject structs for all the * objects we intend to dump. */ ! tblinfo = getSchemaData(&numTables, schemaOnly, dataOnly); if (!schemaOnly) getTableData(tblinfo, numTables, oids); --- 617,773 ---- write_msg(NULL, "last built-in OID is %u\n", g_last_builtin_oid); } + + if (schemaList != NULL && g_fout->remoteVersion < 70300) + { + write_msg(NULL, "Postgres must be at least version 7.3 to use schema switches\n"); + exit_nicely(); + } + + /* Check schema selection flags */ + resetPQExpBuffer(query); + switch_include_exclude = true; + for (this_obj_name = schemaList; this_obj_name; this_obj_name = this_obj_name->next) + { + if (switch_include_exclude) + { + /* Special case for when -N is the first argument */ + if (this_obj_name == schemaList && !this_obj_name->is_include) + appendPQExpBuffer(query, "SELECT oid FROM pg_catalog.pg_namespace EXCEPT\n"); + + appendPQExpBuffer(query, "SELECT oid FROM pg_catalog.pg_namespace WHERE"); + } + + appendPQExpBuffer(query, "%s nspname %c ", switch_include_exclude ? "" : " OR", + /* any meta-characters? */ + strpbrk(this_obj_name->name,"([{\\.?+") == NULL ? '=' : '~'); + appendStringLiteralAH(query, this_obj_name->name, g_fout); + + if (this_obj_name->next && this_obj_name->next->is_include == this_obj_name->is_include) + switch_include_exclude = false; + else + { + switch_include_exclude = true; + + /* Add the joiner if needed */ + if (this_obj_name->next) + appendPQExpBuffer(query, "\n%s\n", + this_obj_name->next->is_include ? "UNION" : "EXCEPT"); + } + } + + /* Construct OID list of matching schemas */ + if (schemaList) + { + int len; + + res = PQexec(g_conn, query->data); + check_sql_result(res, g_conn, query->data, PGRES_TUPLES_OK); + if (PQntuples(res) == 0) + { + write_msg(NULL, "No matching schemas were found\n"); + exit_nicely(); + } + + for (i = 0, len = strlen(" "); i < PQntuples(res); i++) + len += strlen(PQgetvalue(res, i, 0)) + 1; + + /* + * Need to use comma separators so it can be used by IN. zero + * is a dummy placeholder. Format is " oid oid oid ". + */ + matchingSchemas = malloc(len + 1); + strcpy(matchingSchemas, " "); + for (i = 0; i < PQntuples(res); i++) + { + strcat(matchingSchemas, PQgetvalue(res, i, 0)); + strcat(matchingSchemas, " "); + } + } + + /* Check table selection flags */ + resetPQExpBuffer(query); + switch_include_exclude = true; + for (this_obj_name = tableList; this_obj_name; this_obj_name = this_obj_name->next) + { + if (switch_include_exclude) + { + /* Special case for when -T is the first argument */ + if (this_obj_name == tableList && !this_obj_name->is_include && !strlen(query->data)) + appendPQExpBuffer(query, "SELECT oid FROM pg_catalog.pg_class WHERE relkind='r' EXCEPT\n"); + + appendPQExpBuffer(query, "SELECT oid FROM pg_catalog.pg_class WHERE relkind='r' AND ("); + } + + appendPQExpBuffer(query, "%srelname %c ", switch_include_exclude ? "" : " OR ", + /* any meta-characters? */ + strpbrk(this_obj_name->name,"([{\\.?+") == NULL ? '=' : '~'); + appendStringLiteralAH(query, this_obj_name->name, g_fout); + + if (this_obj_name->next && this_obj_name->next->is_include == this_obj_name->is_include) + switch_include_exclude = false; + else + { + switch_include_exclude = true; + appendPQExpBuffer(query, ")"); + + /* Add the joiner if needed */ + if (this_obj_name->next) + appendPQExpBuffer(query, "\n%s\n", this_obj_name->next->is_include ? + "UNION" : "EXCEPT"); + } + } + + /* Construct OID list of matching tables */ + if (tableList) + { + int len; + + /* Restrict by schema? */ + if (matchingSchemas != NULL) + { + char *matchingSchemas_commas = strdup(matchingSchemas), *p; + + /* Construct "IN" SQL string by adding commas, " oid, oid, oid " */ + for (p = matchingSchemas_commas; *p; p++) + { + /* No commas for first/last characters */ + if (*p == ' ' && p != matchingSchemas_commas && *(p+1)) + *p = ','; + } + + appendPQExpBuffer(query, + "\nINTERSECT\nSELECT oid FROM pg_catalog.pg_class WHERE relkind='r' AND relnamespace IN (%s)\n", + matchingSchemas_commas); + } + + res = PQexec(g_conn, query->data); + check_sql_result(res, g_conn, query->data, PGRES_TUPLES_OK); + if (PQntuples(res) == 0) + { + write_msg(NULL, "No matching tables were found\n"); + exit_nicely(); + } + + for (i = 0, len = strlen(" "); i < PQntuples(res); i++) + len += strlen(PQgetvalue(res, i, 0)) + 1; + + matchingTables = malloc(len + 1); + strcpy(matchingTables, " "); + for (i = 0; i < PQntuples(res); i++) + { + strcat(matchingTables, PQgetvalue(res, i, 0)); + strcat(matchingTables, " "); + } + } + + destroyPQExpBuffer(query); + /* * Now scan the database and create DumpableObject structs for all the * objects we intend to dump. */ ! tblinfo = getSchemaData(&numTables); if (!schemaOnly) getTableData(tblinfo, numTables, oids); *************** *** 628,634 **** dumpStdStrings(g_fout); /* The database item is always next, unless we don't want it at all */ ! if (!dataOnly && selectTableName == NULL && selectSchemaName == NULL) dumpDatabase(g_fout); /* Now the rearrangeable objects. */ --- 823,829 ---- dumpStdStrings(g_fout); /* The database item is always next, unless we don't want it at all */ ! if (!dataOnly && matchingTables == NULL && matchingSchemas == NULL) dumpDatabase(g_fout); /* Now the rearrangeable objects. */ *************** *** 687,714 **** printf(_(" --version output version information, then exit\n")); printf(_("\nOptions controlling the output content:\n")); ! printf(_(" -a, --data-only dump only the data, not the schema\n")); ! printf(_(" -c, --clean clean (drop) schema prior to create\n")); ! printf(_(" -C, --create include commands to create database in dump\n")); ! printf(_(" -d, --inserts dump data as INSERT, rather than COPY, commands\n")); ! printf(_(" -D, --column-inserts dump data as INSERT commands with column names\n")); ! printf(_(" -E, --encoding=ENCODING dump the data in encoding ENCODING\n")); ! printf(_(" -n, --schema=SCHEMA dump the named schema only\n")); ! printf(_(" -o, --oids include OIDs in dump\n")); ! printf(_(" -O, --no-owner skip restoration of object ownership\n" ! " in plain text format\n")); ! printf(_(" -s, --schema-only dump only the schema, no data\n")); ! 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(_(" -x, --no-privileges do not dump privileges (grant/revoke)\n")); printf(_(" -X disable-dollar-quoting, --disable-dollar-quoting\n" ! " disable dollar quoting, use SQL standard quoting\n")); printf(_(" -X disable-triggers, --disable-triggers\n" ! " disable triggers during data-only restore\n")); printf(_(" -X use-set-session-authorization, --use-set-session-authorization\n" ! " use SESSION AUTHORIZATION commands instead of\n" ! " OWNER TO commands\n")); printf(_("\nConnection options:\n")); printf(_(" -h, --host=HOSTNAME database server host or socket directory\n")); --- 882,911 ---- printf(_(" --version output version information, then exit\n")); printf(_("\nOptions controlling the output content:\n")); ! printf(_(" -a, --data-only dump only the data, not the schema\n")); ! printf(_(" -c, --clean clean (drop) schema prior to create\n")); ! printf(_(" -C, --create include commands to create database in dump\n")); ! printf(_(" -d, --inserts dump data as INSERT, rather than COPY, commands\n")); ! printf(_(" -D, --column-inserts dump data as INSERT commands with column names\n")); ! printf(_(" -E, --encoding=ENCODING dump the data in encoding ENCODING\n")); ! printf(_(" -n, --schema=SCHEMA dump the named schema only\n")); ! printf(_(" -N, --exclude-schema=SCHEMA do NOT dump the named schema\n")); ! printf(_(" -o, --oids include OIDs in dump\n")); ! printf(_(" -O, --no-owner skip restoration of object ownership\n" ! " in plain text format\n")); ! printf(_(" -s, --schema-only dump only the schema, no data\n")); ! 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(_(" -T, --exclude-table=TABLE do NOT dump the named table\n")); ! printf(_(" -x, --no-privileges do not dump privileges (grant/revoke)\n")); printf(_(" -X disable-dollar-quoting, --disable-dollar-quoting\n" ! " disable dollar quoting, use SQL standard quoting\n")); printf(_(" -X disable-triggers, --disable-triggers\n" ! " disable triggers during data-only restore\n")); printf(_(" -X use-set-session-authorization, --use-set-session-authorization\n" ! " use SESSION AUTHORIZATION commands instead of\n" ! " OWNER TO commands\n")); printf(_("\nConnection options:\n")); printf(_(" -h, --host=HOSTNAME database server host or socket directory\n")); *************** *** 738,755 **** selectDumpableNamespace(NamespaceInfo *nsinfo) { /* ! * If a specific table is being dumped, do not dump any complete ! * namespaces. If a specific namespace is being dumped, dump just that ! * namespace. Otherwise, dump all non-system namespaces. */ ! if (selectTableName != NULL) nsinfo->dobj.dump = false; ! else if (selectSchemaName != NULL) { ! if (strcmp(nsinfo->dobj.name, selectSchemaName) == 0) nsinfo->dobj.dump = true; ! else ! nsinfo->dobj.dump = false; } else if (strncmp(nsinfo->dobj.name, "pg_", 3) == 0 || strcmp(nsinfo->dobj.name, "information_schema") == 0) --- 935,954 ---- selectDumpableNamespace(NamespaceInfo *nsinfo) { /* ! * If specific tables are being dumped, do not dump any complete ! * namespaces. If specific namespaces are being dumped, dump just ! * those namespaces. Otherwise, dump all non-system namespaces. */ ! if (matchingTables != NULL) nsinfo->dobj.dump = false; ! else if (matchingSchemas != NULL) { ! char *searchname = NULL; ! searchname = malloc(20); ! sprintf(searchname, " %d ", nsinfo->dobj.catId.oid); ! if (strstr(matchingSchemas, searchname) != NULL) nsinfo->dobj.dump = true; ! free(searchname); } else if (strncmp(nsinfo->dobj.name, "pg_", 3) == 0 || strcmp(nsinfo->dobj.name, "information_schema") == 0) *************** *** 771,786 **** * dump. */ tbinfo->dobj.dump = false; ! if (tbinfo->dobj.namespace->dobj.dump) tbinfo->dobj.dump = true; ! else if (selectTableName != NULL && ! strcmp(tbinfo->dobj.name, selectTableName) == 0) { ! /* If both -s and -t specified, must match both to dump */ ! if (selectSchemaName == NULL) ! tbinfo->dobj.dump = true; ! else if (strcmp(tbinfo->dobj.namespace->dobj.name, selectSchemaName) == 0) tbinfo->dobj.dump = true; } } --- 970,985 ---- * dump. */ tbinfo->dobj.dump = false; ! if (tbinfo->dobj.namespace->dobj.dump || matchingTables == NULL) tbinfo->dobj.dump = true; ! else { ! char *searchname = NULL; ! searchname = malloc(20); ! sprintf(searchname, " %d ", tbinfo->dobj.catId.oid); ! if (strstr(matchingTables, searchname) != NULL) tbinfo->dobj.dump = true; + free(searchname); } } *************** *** 1722,1746 **** nsinfo[i].dobj.name); } - /* - * If the user attempted to dump a specific namespace, check to ensure - * that the specified namespace actually exists. - */ - if (selectSchemaName) - { - for (i = 0; i < ntups; i++) - if (strcmp(nsinfo[i].dobj.name, selectSchemaName) == 0) - break; - - /* Didn't find a match */ - if (i == ntups) - { - write_msg(NULL, "specified schema \"%s\" does not exist\n", - selectSchemaName); - exit_nicely(); - } - } - PQclear(res); destroyPQExpBuffer(query); --- 1921,1926 ---- *************** *** 2905,2930 **** tblinfo[i].dobj.name); } - /* - * If the user is attempting to dump a specific table, check to ensure - * that the specified table actually exists. (This is a bit simplistic - * since we don't fully check the combination of -n and -t switches.) - */ - if (selectTableName) - { - for (i = 0; i < ntups; i++) - if (strcmp(tblinfo[i].dobj.name, selectTableName) == 0) - break; - - /* Didn't find a match */ - if (i == ntups) - { - write_msg(NULL, "specified table \"%s\" does not exist\n", - selectTableName); - exit_nicely(); - } - } - PQclear(res); destroyPQExpBuffer(query); destroyPQExpBuffer(delqry); --- 3085,3090 ---- *************** *** 5438,5444 **** static bool shouldDumpProcLangs(void) { ! if (selectTableName != NULL || selectSchemaName != NULL) return false; /* And they're schema not data */ if (dataOnly) --- 5598,5604 ---- static bool shouldDumpProcLangs(void) { ! if (matchingTables != NULL || matchingSchemas != NULL) return false; /* And they're schema not data */ if (dataOnly) Index: src/bin/pg_dump/pg_dump.h =================================================================== RCS file: /cvsroot/pgsql/src/bin/pg_dump/pg_dump.h,v retrieving revision 1.127 diff -c -c -r1.127 pg_dump.h *** src/bin/pg_dump/pg_dump.h 27 Jul 2006 19:52:06 -0000 1.127 --- src/bin/pg_dump/pg_dump.h 1 Aug 2006 17:44:59 -0000 *************** *** 340,348 **** * common utility functions */ ! extern TableInfo *getSchemaData(int *numTablesPtr, ! const bool schemaOnly, ! const bool dataOnly); typedef enum _OidOptions { --- 340,346 ---- * common utility functions */ ! extern TableInfo *getSchemaData(int *numTablesPtr); typedef enum _OidOptions {