1 /*-------------------------------------------------------------------------
4 * pg_dump is a utility for dumping out a postgres database
7 * Portions Copyright (c) 1996-2003, PostgreSQL Global Development Group
8 * Portions Copyright (c) 1994, Regents of the University of California
10 * pg_dump will read the system catalogs in a database and dump out a
11 * script that reproduces the schema in terms of SQL that is understood
15 * $PostgreSQL: pgsql/src/bin/pg_dump/pg_dump.c,v 1.361 2003/12/19 14:21:56 petere Exp $
17 *-------------------------------------------------------------------------
21 * Although this is not a backend module, we must include postgres.h anyway
22 * so that we can include a bunch of backend include files. pg_dump has
23 * never pretended to be very independent of the backend anyhow ...
40 #include "getopt_long.h"
46 #include "access/attnum.h"
47 #include "access/htup.h"
48 #include "catalog/pg_class.h"
49 #include "catalog/pg_proc.h"
50 #include "catalog/pg_trigger.h"
51 #include "catalog/pg_type.h"
53 #include "commands/sequence.h"
56 #include "libpq/libpq-fs.h"
59 #include "pg_backup.h"
60 #include "pg_backup_archiver.h"
61 #include "dumputils.h"
63 #define _(x) gettext((x))
71 bool g_verbose; /* User wants verbose narration of our
73 Archive *g_fout; /* the script file */
74 PGconn *g_conn; /* the database connection */
76 /* various user-settable parameters */
77 bool dumpInserts; /* dump data using proper insert strings */
78 bool attrNames; /* put attr names into insert strings */
83 /* obsolete as of 7.3: */
84 static Oid g_last_builtin_oid; /* value of the last builtin oid */
86 static char *selectTableName = NULL; /* name of a single table to dump */
87 static char *selectSchemaName = NULL; /* name of a single schema to dump */
89 char g_opaque_type[10]; /* name for the opaque type */
91 /* placeholders for the delimiters for comments */
92 char g_comment_start[10];
93 char g_comment_end[10];
95 static const CatalogId nilCatalogId = { 0, 0 };
97 /* these are to avoid passing around info for findNamespace() */
98 static NamespaceInfo *g_namespaces;
99 static int g_numNamespaces;
102 static void help(const char *progname);
103 static NamespaceInfo *findNamespace(Oid nsoid, Oid objoid);
104 static void dumpTableData(Archive *fout, TableDataInfo *tdinfo);
105 static void dumpComment(Archive *fout, const char *target,
106 const char *namespace, const char *owner,
107 CatalogId catalogId, int subid, DumpId dumpId);
108 static void dumpDumpableObject(Archive *fout, DumpableObject *dobj);
109 static void dumpNamespace(Archive *fout, NamespaceInfo *nspinfo);
110 static void dumpType(Archive *fout, TypeInfo *tinfo);
111 static void dumpBaseType(Archive *fout, TypeInfo *tinfo);
112 static void dumpDomain(Archive *fout, TypeInfo *tinfo);
113 static void dumpCompositeType(Archive *fout, TypeInfo *tinfo);
114 static void dumpProcLang(Archive *fout, ProcLangInfo *plang);
115 static void dumpFunc(Archive *fout, FuncInfo *finfo);
116 static void dumpCast(Archive *fout, CastInfo *cast);
117 static void dumpOpr(Archive *fout, OprInfo *oprinfo);
118 static void dumpOpclass(Archive *fout, OpclassInfo *opcinfo);
119 static void dumpConversion(Archive *fout, ConvInfo *convinfo);
120 static void dumpRule(Archive *fout, RuleInfo *rinfo);
121 static void dumpAgg(Archive *fout, AggInfo *agginfo);
122 static void dumpTrigger(Archive *fout, TriggerInfo *tginfo);
123 static void dumpTable(Archive *fout, TableInfo *tbinfo);
124 static void dumpTableSchema(Archive *fout, TableInfo *tbinfo);
125 static void dumpAttrDef(Archive *fout, AttrDefInfo *adinfo);
126 static void dumpSequence(Archive *fout, TableInfo *tbinfo);
127 static void dumpIndex(Archive *fout, IndxInfo *indxinfo);
128 static void dumpConstraint(Archive *fout, ConstraintInfo *coninfo);
130 static void dumpACL(Archive *fout, CatalogId objCatId, DumpId objDumpId,
131 const char *type, const char *name,
132 const char *tag, const char *nspname, const char *owner,
135 static void getDependencies(void);
136 static void getDomainConstraints(TypeInfo *tinfo);
137 static void getTableData(TableInfo *tblinfo, int numTables, bool oids);
138 static char *format_function_signature(FuncInfo *finfo, bool honor_quotes);
139 static const char *convertRegProcReference(const char *proc);
140 static const char *convertOperatorReference(const char *opr);
141 static Oid findLastBuiltinOid_V71(const char *);
142 static Oid findLastBuiltinOid_V70(void);
143 static void setMaxOid(Archive *fout);
144 static void selectSourceSchema(const char *schemaName);
145 static char *getFormattedTypeName(Oid oid, OidOptions opts);
146 static char *myFormatType(const char *typname, int32 typmod);
147 static const char *fmtQualifiedId(const char *schema, const char *id);
148 static int dumpBlobs(Archive *AH, void *arg);
149 static void dumpDatabase(Archive *AH);
150 static const char *getAttrName(int attrnum, TableInfo *tblInfo);
151 static const char *fmtCopyColumnList(const TableInfo *ti);
152 static void do_sql_command(PGconn *conn, const char *query);
153 static void check_sql_result(PGresult *res, PGconn *conn, const char *query,
154 ExecStatusType expected);
158 main(int argc, char **argv)
161 const char *filename = NULL;
162 const char *format = "p";
163 const char *dbname = NULL;
164 const char *pghost = NULL;
165 const char *pgport = NULL;
166 const char *username = NULL;
170 DumpableObject **dobjs;
173 bool force_password = false;
174 int compressLevel = -1;
175 bool ignore_version = false;
178 int outputCreate = 0;
180 int outputNoOwner = 0;
181 static int use_setsessauth = 0;
182 static int disable_triggers = 0;
183 char *outputSuperuser = NULL;
185 RestoreOptions *ropt;
187 static struct option long_options[] = {
188 {"data-only", no_argument, NULL, 'a'},
189 {"blobs", no_argument, NULL, 'b'},
190 {"clean", no_argument, NULL, 'c'},
191 {"create", no_argument, NULL, 'C'},
192 {"file", required_argument, NULL, 'f'},
193 {"format", required_argument, NULL, 'F'},
194 {"inserts", no_argument, NULL, 'd'},
195 {"attribute-inserts", no_argument, NULL, 'D'},
196 {"column-inserts", no_argument, NULL, 'D'},
197 {"host", required_argument, NULL, 'h'},
198 {"ignore-version", no_argument, NULL, 'i'},
199 {"no-reconnect", no_argument, NULL, 'R'},
200 {"oids", no_argument, NULL, 'o'},
201 {"no-owner", no_argument, NULL, 'O'},
202 {"port", required_argument, NULL, 'p'},
203 {"schema", required_argument, NULL, 'n'},
204 {"schema-only", no_argument, NULL, 's'},
205 {"superuser", required_argument, NULL, 'S'},
206 {"table", required_argument, NULL, 't'},
207 {"password", no_argument, NULL, 'W'},
208 {"username", required_argument, NULL, 'U'},
209 {"verbose", no_argument, NULL, 'v'},
210 {"no-privileges", no_argument, NULL, 'x'},
211 {"no-acl", no_argument, NULL, 'x'},
212 {"compress", required_argument, NULL, 'Z'},
213 {"help", no_argument, NULL, '?'},
214 {"version", no_argument, NULL, 'V'},
217 * the following options don't have an equivalent short option
218 * letter, but are available as '-X long-name'
220 {"use-set-session-authorization", no_argument, &use_setsessauth, 1},
221 {"disable-triggers", no_argument, &disable_triggers, 1},
228 setlocale(LC_ALL, "");
229 bindtextdomain("pg_dump", LOCALEDIR);
230 textdomain("pg_dump");
235 strcpy(g_comment_start, "-- ");
236 g_comment_end[0] = '\0';
237 strcpy(g_opaque_type, "opaque");
239 dataOnly = schemaOnly = dumpInserts = attrNames = false;
241 progname = get_progname(argv[0]);
243 /* Set default options based on progname */
244 if (strcmp(progname, "pg_backup") == 0)
252 if (strcmp(argv[1], "--help") == 0 || strcmp(argv[1], "-?") == 0)
257 if (strcmp(argv[1], "--version") == 0 || strcmp(argv[1], "-V") == 0)
259 puts("pg_dump (PostgreSQL) " PG_VERSION);
264 while ((c = getopt_long(argc, argv, "abcCdDf:F:h:in:oOp:RsS:t:uU:vWxX:Z:",
265 long_options, &optindex)) != -1)
269 case 'a': /* Dump data only */
273 case 'b': /* Dump blobs */
277 case 'c': /* clean (i.e., drop) schema prior to
282 case 'C': /* Create DB */
286 case 'd': /* dump data as proper insert strings */
290 case 'D': /* dump data as proper insert strings with
304 case 'h': /* server host */
308 case 'i': /* ignore database version mismatch */
309 ignore_version = true;
312 case 'n': /* Dump data for this schema only */
313 selectSchemaName = strdup(optarg);
316 case 'o': /* Dump oids */
320 case 'O': /* Don't reconnect to match owner */
324 case 'p': /* server port */
329 /* no-op, still accepted for backwards compatibility */
332 case 's': /* dump schema only */
336 case 'S': /* Username for superuser in plain text
338 outputSuperuser = strdup(optarg);
341 case 't': /* Dump data for this table only */
342 selectTableName = strdup(optarg);
346 force_password = true;
347 username = simple_prompt("User name: ", 100, true);
354 case 'v': /* verbose */
359 force_password = true;
362 case 'x': /* skip ACL dump */
367 * Option letters were getting scarce, so I invented this
368 * new scheme: '-X feature' turns on some feature. Compare
369 * to the -f option in GCC. You should also add an
370 * equivalent GNU-style option --feature. Features that
371 * require arguments should use '-X feature=foo'.
374 if (strcmp(optarg, "use-set-session-authorization") == 0)
375 /* no-op, still allowed for compatibility */ ;
376 else if (strcmp(optarg, "disable-triggers") == 0)
377 disable_triggers = 1;
381 _("%s: invalid -X option -- %s\n"),
383 fprintf(stderr, _("Try \"%s --help\" for more information.\n"), progname);
388 case 'Z': /* Compression Level */
389 compressLevel = atoi(optarg);
391 /* This covers the long options equivalent to -X xxx. */
397 fprintf(stderr, _("Try \"%s --help\" for more information.\n"), progname);
402 if (optind < (argc - 1))
404 fprintf(stderr, _("%s: too many command-line arguments (first is \"%s\")\n"),
405 progname, argv[optind + 1]);
406 fprintf(stderr, _("Try \"%s --help\" for more information.\n"),
411 /* Get database name from command line */
413 dbname = argv[optind];
415 if (dataOnly && schemaOnly)
417 write_msg(NULL, "options \"schema only\" (-s) and \"data only\" (-a) cannot be used together\n");
421 if (dataOnly && outputClean)
423 write_msg(NULL, "options \"clean\" (-c) and \"data only\" (-a) cannot be used together\n");
427 if (outputBlobs && selectTableName != NULL)
429 write_msg(NULL, "large-object output not supported for a single table\n");
430 write_msg(NULL, "use a full dump instead\n");
434 if (outputBlobs && selectSchemaName != NULL)
436 write_msg(NULL, "large-object output not supported for a single schema\n");
437 write_msg(NULL, "use a full dump instead\n");
441 if (dumpInserts == true && oids == true)
443 write_msg(NULL, "INSERT (-d, -D) and OID (-o) options cannot be used together\n");
444 write_msg(NULL, "(The INSERT command cannot set OIDs.)\n");
448 if (outputBlobs == true && (format[0] == 'p' || format[0] == 'P'))
450 write_msg(NULL, "large-object output is not supported for plain-text dump files\n");
451 write_msg(NULL, "(Use a different output format.)\n");
455 /* open the output file */
460 g_fout = CreateArchive(filename, archCustom, compressLevel);
465 g_fout = CreateArchive(filename, archFiles, compressLevel);
471 g_fout = CreateArchive(filename, archNull, 0);
476 g_fout = CreateArchive(filename, archTar, compressLevel);
480 write_msg(NULL, "invalid output format \"%s\" specified\n", format);
486 write_msg(NULL, "could not open output file \"%s\" for writing\n", filename);
490 /* Let the archiver know how noisy to be */
491 g_fout->verbose = g_verbose;
493 g_fout->minRemoteVersion = 70000; /* we can handle back to 7.0 */
494 g_fout->maxRemoteVersion = parse_version(PG_VERSION);
495 if (g_fout->maxRemoteVersion < 0)
497 write_msg(NULL, "could not parse version string \"%s\"\n", PG_VERSION);
502 * Open the database using the Archiver, so it knows about it. Errors
505 g_conn = ConnectDatabase(g_fout, dbname, pghost, pgport,
506 username, force_password, ignore_version);
509 * Start serializable transaction to dump consistent data.
511 do_sql_command(g_conn, "BEGIN");
513 do_sql_command(g_conn, "SET TRANSACTION ISOLATION LEVEL SERIALIZABLE");
515 /* Set the datestyle to ISO to ensure the dump's portability */
516 do_sql_command(g_conn, "SET DATESTYLE = ISO");
519 * If supported, set extra_float_digits so that we can dump float data
520 * exactly (given correctly implemented float I/O code, anyway)
522 if (g_fout->remoteVersion >= 70400)
523 do_sql_command(g_conn, "SET extra_float_digits TO 2");
525 /* Find the last built-in OID, if needed */
526 if (g_fout->remoteVersion < 70300)
528 if (g_fout->remoteVersion >= 70100)
529 g_last_builtin_oid = findLastBuiltinOid_V71(PQdb(g_conn));
531 g_last_builtin_oid = findLastBuiltinOid_V70();
533 write_msg(NULL, "last built-in OID is %u\n", g_last_builtin_oid);
537 * Now scan the database and create DumpableObject structs for all the
538 * objects we intend to dump.
540 tblinfo = getSchemaData(&numTables, schemaOnly, dataOnly);
543 getTableData(tblinfo, numTables, oids);
546 * Collect dependency data to assist in ordering the objects.
551 * Sort the objects into a safe dump order (no forward references).
553 getDumpableObjects(&dobjs, &numObjs);
555 sortDumpableObjectsByType(dobjs, numObjs);
556 sortDumpableObjects(dobjs, numObjs);
559 * Create archive TOC entries for all the objects to be dumped,
563 /* The database item is always first. */
565 dumpDatabase(g_fout);
567 /* Max OID is second. */
571 /* Now the rearrangeable objects. */
572 for (i = 0; i < numObjs; i++)
574 dumpDumpableObject(g_fout, dobjs[i]);
577 /* BLOBs are always last. */
579 ArchiveEntry(g_fout, nilCatalogId, createDumpId(),
581 "BLOBS", "", "", NULL,
586 * And finally we can do the actual output.
590 ropt = NewRestoreOptions();
591 ropt->filename = (char *) filename;
592 ropt->dropSchema = outputClean;
593 ropt->aclsSkip = aclsSkip;
594 ropt->superuser = outputSuperuser;
595 ropt->create = outputCreate;
596 ropt->noOwner = outputNoOwner;
597 ropt->disable_triggers = disable_triggers;
599 if (compressLevel == -1)
600 ropt->compression = 0;
602 ropt->compression = compressLevel;
604 ropt->suppressDumpWarnings = true; /* We've already shown
607 RestoreArchive(g_fout, ropt);
610 CloseArchive(g_fout);
619 help(const char *progname)
621 printf(_("%s dumps a database as a text file or to other formats.\n\n"), progname);
622 printf(_("Usage:\n"));
623 printf(_(" %s [OPTION]... [DBNAME]\n"), progname);
625 printf(_("\nGeneral options:\n"));
626 printf(_(" -f, --file=FILENAME output file name\n"));
627 printf(_(" -F, --format=c|t|p output file format (custom, tar, plain text)\n"));
628 printf(_(" -i, --ignore-version proceed even when server version mismatches\n"
629 " pg_dump version\n"));
630 printf(_(" -v, --verbose verbose mode\n"));
631 printf(_(" -Z, --compress=0-9 compression level for compressed formats\n"));
632 printf(_(" --help show this help, then exit\n"));
633 printf(_(" --version output version information, then exit\n"));
635 printf(_("\nOptions controlling the output content:\n"));
636 printf(_(" -a, --data-only dump only the data, not the schema\n"));
637 printf(_(" -b, --blobs include large objects in dump\n"));
638 printf(_(" -c, --clean clean (drop) schema prior to create\n"));
639 printf(_(" -C, --create include commands to create database in dump\n"));
640 printf(_(" -d, --inserts dump data as INSERT, rather than COPY, commands\n"));
641 printf(_(" -D, --column-inserts dump data as INSERT commands with column names\n"));
642 printf(_(" -n, --schema=SCHEMA dump the named schema only\n"));
643 printf(_(" -o, --oids include OIDs in dump\n"));
644 printf(_(" -O, --no-owner do not output commands to set object ownership\n"
645 " in plain text format\n"));
646 printf(_(" -s, --schema-only dump only the schema, no data\n"));
647 printf(_(" -S, --superuser=NAME specify the superuser user name to use in\n"
648 " plain text format\n"));
649 printf(_(" -t, --table=TABLE dump the named table only\n"));
650 printf(_(" -x, --no-privileges do not dump privileges (grant/revoke)\n"));
651 printf(_(" -X disable-triggers, --disable-triggers\n"
652 " disable triggers during data-only restore\n"));
654 printf(_("\nConnection options:\n"));
655 printf(_(" -h, --host=HOSTNAME database server host or socket directory\n"));
656 printf(_(" -p, --port=PORT database server port number\n"));
657 printf(_(" -U, --username=NAME connect as specified database user\n"));
658 printf(_(" -W, --password force password prompt (should happen automatically)\n"));
660 printf(_("\nIf no database name is supplied, then the PGDATABASE environment\n"
661 "variable value is used.\n\n"));
662 printf(_("Report bugs to <pgsql-bugs@postgresql.org>.\n"));
670 write_msg(NULL, "*** aborted because of error\n");
675 * selectDumpableNamespace: policy-setting subroutine
676 * Mark a namespace as to be dumped or not
679 selectDumpableNamespace(NamespaceInfo *nsinfo)
682 * If a specific table is being dumped, do not dump any complete
683 * namespaces. If a specific namespace is being dumped, dump just
684 * that namespace. Otherwise, dump all non-system namespaces.
686 if (selectTableName != NULL)
687 nsinfo->dump = false;
688 else if (selectSchemaName != NULL)
690 if (strcmp(nsinfo->nspname, selectSchemaName) == 0)
693 nsinfo->dump = false;
695 else if (strncmp(nsinfo->nspname, "pg_", 3) == 0 ||
696 strcmp(nsinfo->nspname, "information_schema") == 0)
697 nsinfo->dump = false;
703 * selectDumpableTable: policy-setting subroutine
704 * Mark a table as to be dumped or not
707 selectDumpableTable(TableInfo *tbinfo)
710 * Always dump if dumping parent namespace; else, if a particular
711 * tablename has been specified, dump matching table name; else, do
714 tbinfo->dump = false;
715 if (tbinfo->relnamespace->dump)
717 else if (selectTableName != NULL &&
718 strcmp(tbinfo->relname, selectTableName) == 0)
720 /* If both -s and -t specified, must match both to dump */
721 if (selectSchemaName == NULL)
723 else if (strcmp(tbinfo->relnamespace->nspname, selectSchemaName) == 0)
729 * Dump a table's contents for loading using the COPY command
730 * - this routine is called by the Archiver when it wants the table
734 #define COPYBUFSIZ 8192
737 dumpTableData_copy(Archive *fout, void *dcontext)
739 TableDataInfo *tdinfo = (TableDataInfo *) dcontext;
740 TableInfo *tbinfo = tdinfo->tdtable;
741 const char *classname = tbinfo->relname;
742 const bool hasoids = tbinfo->hasoids;
743 const bool oids = tdinfo->oids;
744 PQExpBuffer q = createPQExpBuffer();
748 char copybuf[COPYBUFSIZ];
749 const char *column_list;
752 write_msg(NULL, "dumping contents of table %s\n", classname);
755 * Make sure we are in proper schema. We will qualify the table name
756 * below anyway (in case its name conflicts with a pg_catalog table);
757 * but this ensures reproducible results in case the table contains
758 * regproc, regclass, etc columns.
760 selectSourceSchema(tbinfo->relnamespace->nspname);
763 * If possible, specify the column list explicitly so that we have no
764 * possibility of retrieving data in the wrong column order. (The
765 * default column ordering of COPY will not be what we want in certain
766 * corner cases involving ADD COLUMN and inheritance.)
768 if (g_fout->remoteVersion >= 70300)
769 column_list = fmtCopyColumnList(tbinfo);
771 column_list = ""; /* can't select columns in COPY */
775 appendPQExpBuffer(q, "COPY %s %s WITH OIDS TO stdout;",
776 fmtQualifiedId(tbinfo->relnamespace->nspname,
782 appendPQExpBuffer(q, "COPY %s %s TO stdout;",
783 fmtQualifiedId(tbinfo->relnamespace->nspname,
787 res = PQexec(g_conn, q->data);
788 check_sql_result(res, g_conn, q->data, PGRES_COPY_OUT);
794 ret = PQgetline(g_conn, copybuf, COPYBUFSIZ);
796 if (copybuf[0] == '\\' &&
800 copydone = true; /* don't print this... */
804 archputs(copybuf, fout);
811 archputs("\n", fout);
821 * There was considerable discussion in late July, 2000 regarding
822 * slowing down pg_dump when backing up large tables. Users with
823 * both slow & fast (muti-processor) machines experienced
824 * performance degradation when doing a backup.
826 * Initial attempts based on sleeping for a number of ms for each ms
827 * of work were deemed too complex, then a simple 'sleep in each
828 * loop' implementation was suggested. The latter failed because
829 * the loop was too tight. Finally, the following was implemented:
831 * If throttle is non-zero, then See how long since the last sleep.
832 * Work out how long to sleep (based on ratio). If sleep is more
833 * than 100ms, then sleep reset timer EndIf EndIf
835 * where the throttle value was the number of ms to sleep per ms of
836 * work. The calculation was done in each loop.
838 * Most of the hard work is done in the backend, and this solution
839 * still did not work particularly well: on slow machines, the
840 * ratio was 50:1, and on medium paced machines, 1:1, and on fast
841 * multi-processor machines, it had little or no effect, for
842 * reasons that were unclear.
844 * Further discussion ensued, and the proposal was dropped.
846 * For those people who want this feature, it can be implemented
847 * using gettimeofday in each loop, calculating the time since
848 * last sleep, multiplying that by the sleep ratio, then if the
849 * result is more than a preset 'minimum sleep time' (say 100ms),
850 * call the 'select' function to sleep for a subsecond period ie.
852 * select(0, NULL, NULL, NULL, &tvi);
854 * This will return after the interval specified in the structure
855 * tvi. Finally, call gettimeofday again to save the 'last sleep
859 archprintf(fout, "\\.\n\n\n");
861 ret = PQendcopy(g_conn);
864 write_msg(NULL, "SQL command to dump the contents of table \"%s\" failed: PQendcopy() failed.\n", classname);
865 write_msg(NULL, "Error message from server: %s", PQerrorMessage(g_conn));
866 write_msg(NULL, "The command was: %s\n", q->data);
871 destroyPQExpBuffer(q);
876 dumpTableData_insert(Archive *fout, void *dcontext)
878 TableDataInfo *tdinfo = (TableDataInfo *) dcontext;
879 TableInfo *tbinfo = tdinfo->tdtable;
880 const char *classname = tbinfo->relname;
881 PQExpBuffer q = createPQExpBuffer();
888 * Make sure we are in proper schema. We will qualify the table name
889 * below anyway (in case its name conflicts with a pg_catalog table);
890 * but this ensures reproducible results in case the table contains
891 * regproc, regclass, etc columns.
893 selectSourceSchema(tbinfo->relnamespace->nspname);
895 if (fout->remoteVersion >= 70100)
897 appendPQExpBuffer(q, "DECLARE _pg_dump_cursor CURSOR FOR "
898 "SELECT * FROM ONLY %s",
899 fmtQualifiedId(tbinfo->relnamespace->nspname,
904 appendPQExpBuffer(q, "DECLARE _pg_dump_cursor CURSOR FOR "
906 fmtQualifiedId(tbinfo->relnamespace->nspname,
910 res = PQexec(g_conn, q->data);
911 check_sql_result(res, g_conn, q->data, PGRES_COMMAND_OK);
917 res = PQexec(g_conn, "FETCH 100 FROM _pg_dump_cursor");
918 check_sql_result(res, g_conn, "FETCH 100 FROM _pg_dump_cursor",
920 nfields = PQnfields(res);
921 for (tuple = 0; tuple < PQntuples(res); tuple++)
923 archprintf(fout, "INSERT INTO %s ", fmtId(classname));
926 /* corner case for zero-column table */
927 archprintf(fout, "DEFAULT VALUES;\n");
930 if (attrNames == true)
933 appendPQExpBuffer(q, "(");
934 for (field = 0; field < nfields; field++)
937 appendPQExpBuffer(q, ", ");
938 appendPQExpBuffer(q, fmtId(PQfname(res, field)));
940 appendPQExpBuffer(q, ") ");
941 archprintf(fout, "%s", q->data);
943 archprintf(fout, "VALUES (");
944 for (field = 0; field < nfields; field++)
947 archprintf(fout, ", ");
948 if (PQgetisnull(res, tuple, field))
950 archprintf(fout, "NULL");
954 /* XXX This code is partially duplicated in ruleutils.c */
955 switch (PQftype(res, field))
966 * These types are printed without quotes
967 * unless they contain values that aren't
968 * accepted by the scanner unquoted (e.g.,
969 * 'NaN'). Note that strtod() and friends
970 * might accept NaN, so we can't use that to
973 * In reality we only need to defend against
974 * infinity and NaN, so we need not get too
975 * crazy about pattern matching here.
977 const char *s = PQgetvalue(res, tuple, field);
979 if (strspn(s, "0123456789 +-eE.") == strlen(s))
980 archprintf(fout, "%s", s);
982 archprintf(fout, "'%s'", s);
988 archprintf(fout, "B'%s'",
989 PQgetvalue(res, tuple, field));
993 if (strcmp(PQgetvalue(res, tuple, field), "t") == 0)
994 archprintf(fout, "true");
996 archprintf(fout, "false");
1000 /* All other types are printed as string literals. */
1001 resetPQExpBuffer(q);
1002 appendStringLiteral(q, PQgetvalue(res, tuple, field), false);
1003 archprintf(fout, "%s", q->data);
1007 archprintf(fout, ");\n");
1009 } while (PQntuples(res) > 0);
1013 archprintf(fout, "\n\n");
1015 do_sql_command(g_conn, "CLOSE _pg_dump_cursor");
1017 destroyPQExpBuffer(q);
1024 * dump the contents of a single table
1026 * Actually, this just makes an ArchiveEntry for the table contents.
1029 dumpTableData(Archive *fout, TableDataInfo *tdinfo)
1031 TableInfo *tbinfo = tdinfo->tdtable;
1032 PQExpBuffer copyBuf = createPQExpBuffer();
1033 DataDumperPtr dumpFn;
1038 /* Dump/restore using COPY */
1039 dumpFn = dumpTableData_copy;
1040 /* must use 2 steps here 'cause fmtId is nonreentrant */
1041 appendPQExpBuffer(copyBuf, "COPY %s ",
1042 fmtId(tbinfo->relname));
1043 appendPQExpBuffer(copyBuf, "%s %sFROM stdin;\n",
1044 fmtCopyColumnList(tbinfo),
1045 (tdinfo->oids && tbinfo->hasoids) ? "WITH OIDS " : "");
1046 copyStmt = copyBuf->data;
1050 /* Restore using INSERT */
1051 dumpFn = dumpTableData_insert;
1055 ArchiveEntry(fout, tdinfo->dobj.catId, tdinfo->dobj.dumpId,
1057 tbinfo->relnamespace->nspname,
1059 "TABLE DATA", "", "", copyStmt,
1060 tdinfo->dobj.dependencies, tdinfo->dobj.nDeps,
1063 destroyPQExpBuffer(copyBuf);
1068 * set up dumpable objects representing the contents of tables
1071 getTableData(TableInfo *tblinfo, int numTables, bool oids)
1075 for (i = 0; i < numTables; i++)
1077 /* Skip VIEWs (no data to dump) */
1078 if (tblinfo[i].relkind == RELKIND_VIEW)
1080 /* Skip SEQUENCEs (handled elsewhere) */
1081 if (tblinfo[i].relkind == RELKIND_SEQUENCE)
1084 if (tblinfo[i].dump)
1086 TableDataInfo *tdinfo;
1088 tdinfo = (TableDataInfo *) malloc(sizeof(TableDataInfo));
1090 tdinfo->dobj.objType = DO_TABLE_DATA;
1092 * Note: use tableoid 0 so that this object won't be mistaken
1093 * for something that pg_depend entries apply to.
1095 tdinfo->dobj.catId.tableoid = 0;
1096 tdinfo->dobj.catId.oid = tblinfo[i].dobj.catId.oid;
1097 AssignDumpId(&tdinfo->dobj);
1098 tdinfo->tdtable = &(tblinfo[i]);
1099 tdinfo->oids = oids;
1100 addObjectDependency(&tdinfo->dobj, tblinfo[i].dobj.dumpId);
1108 * dump the database definition
1111 dumpDatabase(Archive *AH)
1113 PQExpBuffer dbQry = createPQExpBuffer();
1114 PQExpBuffer delQry = createPQExpBuffer();
1115 PQExpBuffer creaQry = createPQExpBuffer();
1125 const char *datname,
1130 datname = PQdb(g_conn);
1133 write_msg(NULL, "saving database definition\n");
1135 /* Make sure we are in proper schema */
1136 selectSourceSchema("pg_catalog");
1138 /* Get the database owner and parameters from pg_database */
1139 if (g_fout->remoteVersion >= 70100)
1141 appendPQExpBuffer(dbQry, "SELECT tableoid, oid, "
1142 "(SELECT usename FROM pg_user WHERE usesysid = datdba) as dba, "
1143 "pg_encoding_to_char(encoding) as encoding, "
1146 "WHERE datname = ");
1147 appendStringLiteral(dbQry, datname, true);
1151 appendPQExpBuffer(dbQry, "SELECT "
1152 "(SELECT oid FROM pg_class WHERE relname = 'pg_database') AS tableoid, "
1154 "(SELECT usename FROM pg_user WHERE usesysid = datdba) as dba, "
1155 "pg_encoding_to_char(encoding) as encoding, "
1158 "WHERE datname = ");
1159 appendStringLiteral(dbQry, datname, true);
1162 res = PQexec(g_conn, dbQry->data);
1163 check_sql_result(res, g_conn, dbQry->data, PGRES_TUPLES_OK);
1165 ntups = PQntuples(res);
1169 write_msg(NULL, "missing pg_database entry for database \"%s\"\n",
1176 write_msg(NULL, "query returned more than one (%d) pg_database entry for database \"%s\"\n",
1181 i_tableoid = PQfnumber(res, "tableoid");
1182 i_oid = PQfnumber(res, "oid");
1183 i_dba = PQfnumber(res, "dba");
1184 i_encoding = PQfnumber(res, "encoding");
1185 i_datpath = PQfnumber(res, "datpath");
1187 dbCatId.tableoid = atooid(PQgetvalue(res, 0, i_tableoid));
1188 dbCatId.oid = atooid(PQgetvalue(res, 0, i_oid));
1189 dba = PQgetvalue(res, 0, i_dba);
1190 encoding = PQgetvalue(res, 0, i_encoding);
1191 datpath = PQgetvalue(res, 0, i_datpath);
1193 appendPQExpBuffer(creaQry, "CREATE DATABASE %s WITH TEMPLATE = template0",
1195 if (strlen(datpath) > 0)
1197 appendPQExpBuffer(creaQry, " LOCATION = ");
1198 appendStringLiteral(creaQry, datpath, true);
1200 if (strlen(encoding) > 0)
1202 appendPQExpBuffer(creaQry, " ENCODING = ");
1203 appendStringLiteral(creaQry, encoding, true);
1205 appendPQExpBuffer(creaQry, ";\n");
1207 appendPQExpBuffer(delQry, "DROP DATABASE %s;\n",
1210 dbDumpId = createDumpId();
1213 dbCatId, /* catalog ID */
1214 dbDumpId, /* dump ID */
1216 NULL, /* Namespace */
1218 "DATABASE", /* Desc */
1219 creaQry->data, /* Create */
1220 delQry->data, /* Del */
1225 NULL); /* Dumper Arg */
1227 /* Dump DB comment if any */
1228 resetPQExpBuffer(dbQry);
1229 appendPQExpBuffer(dbQry, "DATABASE %s", fmtId(datname));
1230 dumpComment(AH, dbQry->data, NULL, "",
1231 dbCatId, 0, dbDumpId);
1235 destroyPQExpBuffer(dbQry);
1236 destroyPQExpBuffer(delQry);
1237 destroyPQExpBuffer(creaQry);
1247 #define loBufSize 16384
1248 #define loFetchSize 1000
1251 dumpBlobs(Archive *AH, void *arg)
1253 PQExpBuffer oidQry = createPQExpBuffer();
1254 PQExpBuffer oidFetchQry = createPQExpBuffer();
1258 char buf[loBufSize];
1263 write_msg(NULL, "saving large objects\n");
1265 /* Make sure we are in proper schema */
1266 selectSourceSchema("pg_catalog");
1268 /* Cursor to get all BLOB tables */
1269 if (AH->remoteVersion >= 70100)
1270 appendPQExpBuffer(oidQry, "Declare blobOid Cursor for SELECT DISTINCT loid FROM pg_largeobject");
1272 appendPQExpBuffer(oidQry, "Declare blobOid Cursor for SELECT oid from pg_class where relkind = 'l'");
1274 res = PQexec(g_conn, oidQry->data);
1275 check_sql_result(res, g_conn, oidQry->data, PGRES_COMMAND_OK);
1277 /* Fetch for cursor */
1278 appendPQExpBuffer(oidFetchQry, "Fetch %d in blobOid", loFetchSize);
1285 res = PQexec(g_conn, oidFetchQry->data);
1286 check_sql_result(res, g_conn, oidFetchQry->data, PGRES_TUPLES_OK);
1288 /* Process the tuples, if any */
1289 for (i = 0; i < PQntuples(res); i++)
1291 blobOid = atooid(PQgetvalue(res, i, 0));
1293 loFd = lo_open(g_conn, blobOid, INV_READ);
1296 write_msg(NULL, "dumpBlobs(): could not open large object: %s",
1297 PQerrorMessage(g_conn));
1301 StartBlob(AH, blobOid);
1303 /* Now read it in chunks, sending data to archive */
1306 cnt = lo_read(g_conn, loFd, buf, loBufSize);
1309 write_msg(NULL, "dumpBlobs(): error reading large object: %s",
1310 PQerrorMessage(g_conn));
1314 WriteData(AH, buf, cnt);
1318 lo_close(g_conn, loFd);
1320 EndBlob(AH, blobOid);
1323 } while (PQntuples(res) > 0);
1325 destroyPQExpBuffer(oidQry);
1326 destroyPQExpBuffer(oidFetchQry);
1333 * read all namespaces in the system catalogs and return them in the
1334 * NamespaceInfo* structure
1336 * numNamespaces is set to the number of namespaces read in
1339 getNamespaces(int *numNamespaces)
1345 NamespaceInfo *nsinfo;
1353 * Before 7.3, there are no real namespaces; create two dummy entries,
1354 * one for user stuff and one for system stuff.
1356 if (g_fout->remoteVersion < 70300)
1358 nsinfo = (NamespaceInfo *) malloc(2 * sizeof(NamespaceInfo));
1360 nsinfo[0].dobj.objType = DO_NAMESPACE;
1361 nsinfo[0].dobj.catId.tableoid = 0;
1362 nsinfo[0].dobj.catId.oid = 0;
1363 AssignDumpId(&nsinfo[0].dobj);
1364 nsinfo[0].nspname = strdup("");
1365 nsinfo[0].usename = strdup("");
1366 nsinfo[0].nspacl = strdup("");
1368 selectDumpableNamespace(&nsinfo[0]);
1370 nsinfo[1].dobj.objType = DO_NAMESPACE;
1371 nsinfo[1].dobj.catId.tableoid = 0;
1372 nsinfo[1].dobj.catId.oid = 1;
1373 AssignDumpId(&nsinfo[1].dobj);
1374 nsinfo[1].nspname = strdup("pg_catalog");
1375 nsinfo[1].usename = strdup("");
1376 nsinfo[1].nspacl = strdup("");
1378 selectDumpableNamespace(&nsinfo[1]);
1380 g_namespaces = nsinfo;
1381 g_numNamespaces = *numNamespaces = 2;
1386 query = createPQExpBuffer();
1388 /* Make sure we are in proper schema */
1389 selectSourceSchema("pg_catalog");
1392 * we fetch all namespaces including system ones, so that every object
1393 * we read in can be linked to a containing namespace.
1395 appendPQExpBuffer(query, "SELECT tableoid, oid, nspname, "
1396 "(select usename from pg_user where nspowner = usesysid) as usename, "
1398 "FROM pg_namespace");
1400 res = PQexec(g_conn, query->data);
1401 check_sql_result(res, g_conn, query->data, PGRES_TUPLES_OK);
1403 ntups = PQntuples(res);
1405 nsinfo = (NamespaceInfo *) malloc(ntups * sizeof(NamespaceInfo));
1407 i_tableoid = PQfnumber(res, "tableoid");
1408 i_oid = PQfnumber(res, "oid");
1409 i_nspname = PQfnumber(res, "nspname");
1410 i_usename = PQfnumber(res, "usename");
1411 i_nspacl = PQfnumber(res, "nspacl");
1413 for (i = 0; i < ntups; i++)
1415 nsinfo[i].dobj.objType = DO_NAMESPACE;
1416 nsinfo[i].dobj.catId.tableoid = atooid(PQgetvalue(res, i, i_tableoid));
1417 nsinfo[i].dobj.catId.oid = atooid(PQgetvalue(res, i, i_oid));
1418 AssignDumpId(&nsinfo[i].dobj);
1419 nsinfo[i].nspname = strdup(PQgetvalue(res, i, i_nspname));
1420 nsinfo[i].usename = strdup(PQgetvalue(res, i, i_usename));
1421 nsinfo[i].nspacl = strdup(PQgetvalue(res, i, i_nspacl));
1423 /* Decide whether to dump this namespace */
1424 selectDumpableNamespace(&nsinfo[i]);
1426 if (strlen(nsinfo[i].usename) == 0)
1427 write_msg(NULL, "WARNING: owner of schema \"%s\" appears to be invalid\n",
1432 * If the user attempted to dump a specific namespace, check to ensure
1433 * that the specified namespace actually exists.
1435 if (selectSchemaName)
1437 for (i = 0; i < ntups; i++)
1438 if (strcmp(nsinfo[i].nspname, selectSchemaName) == 0)
1441 /* Didn't find a match */
1444 write_msg(NULL, "specified schema \"%s\" does not exist\n",
1451 destroyPQExpBuffer(query);
1453 g_namespaces = nsinfo;
1454 g_numNamespaces = *numNamespaces = ntups;
1461 * given a namespace OID and an object OID, look up the info read by
1464 * NB: for pre-7.3 source database, we use object OID to guess whether it's
1465 * a system object or not. In 7.3 and later there is no guessing.
1467 static NamespaceInfo *
1468 findNamespace(Oid nsoid, Oid objoid)
1472 if (g_fout->remoteVersion >= 70300)
1474 for (i = 0; i < g_numNamespaces; i++)
1476 NamespaceInfo *nsinfo = &g_namespaces[i];
1478 if (nsoid == nsinfo->dobj.catId.oid)
1481 write_msg(NULL, "schema with OID %u does not exist\n", nsoid);
1486 /* This code depends on the layout set up by getNamespaces. */
1487 if (objoid > g_last_builtin_oid)
1488 i = 0; /* user object */
1490 i = 1; /* system object */
1491 return &g_namespaces[i];
1494 return NULL; /* keep compiler quiet */
1499 * read all types in the system catalogs and return them in the
1500 * TypeInfo* structure
1502 * numTypes is set to the number of types read in
1504 * NB: this must run after getFuncs() because we assume we can do
1508 getTypes(int *numTypes)
1513 PQExpBuffer query = createPQExpBuffer();
1529 * we include even the built-in types because those may be used as
1530 * array elements by user-defined types
1532 * we filter out the built-in types when we dump out the types
1534 * same approach for undefined (shell) types
1537 /* Make sure we are in proper schema */
1538 selectSourceSchema("pg_catalog");
1540 if (g_fout->remoteVersion >= 70300)
1542 appendPQExpBuffer(query, "SELECT tableoid, oid, typname, "
1544 "(select usename from pg_user where typowner = usesysid) as usename, "
1545 "typinput::oid as typinput, "
1546 "typoutput::oid as typoutput, typelem, typrelid, "
1547 "CASE WHEN typrelid = 0 THEN ' '::\"char\" "
1548 "ELSE (SELECT relkind FROM pg_class WHERE oid = typrelid) END as typrelkind, "
1549 "typtype, typisdefined "
1552 else if (g_fout->remoteVersion >= 70100)
1554 appendPQExpBuffer(query, "SELECT tableoid, oid, typname, "
1555 "0::oid as typnamespace, "
1556 "(select usename from pg_user where typowner = usesysid) as usename, "
1557 "typinput::oid as typinput, "
1558 "typoutput::oid as typoutput, typelem, typrelid, "
1559 "CASE WHEN typrelid = 0 THEN ' '::\"char\" "
1560 "ELSE (SELECT relkind FROM pg_class WHERE oid = typrelid) END as typrelkind, "
1561 "typtype, typisdefined "
1566 appendPQExpBuffer(query, "SELECT "
1567 "(SELECT oid FROM pg_class WHERE relname = 'pg_type') AS tableoid, "
1569 "0::oid as typnamespace, "
1570 "(select usename from pg_user where typowner = usesysid) as usename, "
1571 "typinput::oid as typinput, "
1572 "typoutput::oid as typoutput, typelem, typrelid, "
1573 "CASE WHEN typrelid = 0 THEN ' '::\"char\" "
1574 "ELSE (SELECT relkind FROM pg_class WHERE oid = typrelid) END as typrelkind, "
1575 "typtype, typisdefined "
1579 res = PQexec(g_conn, query->data);
1580 check_sql_result(res, g_conn, query->data, PGRES_TUPLES_OK);
1582 ntups = PQntuples(res);
1584 tinfo = (TypeInfo *) malloc(ntups * sizeof(TypeInfo));
1586 i_tableoid = PQfnumber(res, "tableoid");
1587 i_oid = PQfnumber(res, "oid");
1588 i_typname = PQfnumber(res, "typname");
1589 i_typnamespace = PQfnumber(res, "typnamespace");
1590 i_usename = PQfnumber(res, "usename");
1591 i_typinput = PQfnumber(res, "typinput");
1592 i_typoutput = PQfnumber(res, "typoutput");
1593 i_typelem = PQfnumber(res, "typelem");
1594 i_typrelid = PQfnumber(res, "typrelid");
1595 i_typrelkind = PQfnumber(res, "typrelkind");
1596 i_typtype = PQfnumber(res, "typtype");
1597 i_typisdefined = PQfnumber(res, "typisdefined");
1599 for (i = 0; i < ntups; i++)
1604 tinfo[i].dobj.objType = DO_TYPE;
1605 tinfo[i].dobj.catId.tableoid = atooid(PQgetvalue(res, i, i_tableoid));
1606 tinfo[i].dobj.catId.oid = atooid(PQgetvalue(res, i, i_oid));
1607 AssignDumpId(&tinfo[i].dobj);
1608 tinfo[i].typname = strdup(PQgetvalue(res, i, i_typname));
1609 tinfo[i].typnamespace = findNamespace(atooid(PQgetvalue(res, i, i_typnamespace)),
1610 tinfo[i].dobj.catId.oid);
1611 tinfo[i].usename = strdup(PQgetvalue(res, i, i_usename));
1612 tinfo[i].typinput = atooid(PQgetvalue(res, i, i_typinput));
1613 typoutput = atooid(PQgetvalue(res, i, i_typoutput));
1614 tinfo[i].typelem = atooid(PQgetvalue(res, i, i_typelem));
1615 tinfo[i].typrelid = atooid(PQgetvalue(res, i, i_typrelid));
1616 tinfo[i].typrelkind = *PQgetvalue(res, i, i_typrelkind);
1617 tinfo[i].typtype = *PQgetvalue(res, i, i_typtype);
1620 * check for user-defined array types, omit system generated ones
1622 if (OidIsValid(tinfo[i].typelem) &&
1623 tinfo[i].typname[0] != '_')
1624 tinfo[i].isArray = true;
1626 tinfo[i].isArray = false;
1628 if (strcmp(PQgetvalue(res, i, i_typisdefined), "t") == 0)
1629 tinfo[i].isDefined = true;
1631 tinfo[i].isDefined = false;
1634 * If it's a domain, fetch info about its constraints, if any
1636 tinfo[i].nDomChecks = 0;
1637 tinfo[i].domChecks = NULL;
1638 if (tinfo[i].typtype == 'd')
1639 getDomainConstraints(&(tinfo[i]));
1642 * Make sure there are dependencies from the type to its input and
1643 * output functions. (We don't worry about typsend/typreceive since
1644 * those are only valid in 7.4 and later, wherein the standard
1645 * dependency mechanism will pick them up.)
1647 funcInfo = findFuncByOid(tinfo[i].typinput);
1649 addObjectDependency(&tinfo[i].dobj,
1650 funcInfo->dobj.dumpId);
1651 funcInfo = findFuncByOid(typoutput);
1653 addObjectDependency(&tinfo[i].dobj,
1654 funcInfo->dobj.dumpId);
1656 if (strlen(tinfo[i].usename) == 0 && tinfo[i].isDefined)
1657 write_msg(NULL, "WARNING: owner of data type \"%s\" appears to be invalid\n",
1665 destroyPQExpBuffer(query);
1672 * read all operators in the system catalogs and return them in the
1673 * OprInfo* structure
1675 * numOprs is set to the number of operators read in
1678 getOperators(int *numOprs)
1683 PQExpBuffer query = createPQExpBuffer();
1693 * find all operators, including builtin operators; we filter out
1694 * system-defined operators at dump-out time.
1697 /* Make sure we are in proper schema */
1698 selectSourceSchema("pg_catalog");
1700 if (g_fout->remoteVersion >= 70300)
1702 appendPQExpBuffer(query, "SELECT tableoid, oid, oprname, "
1704 "(select usename from pg_user where oprowner = usesysid) as usename, "
1705 "oprcode::oid as oprcode "
1706 "FROM pg_operator");
1708 else if (g_fout->remoteVersion >= 70100)
1710 appendPQExpBuffer(query, "SELECT tableoid, oid, oprname, "
1711 "0::oid as oprnamespace, "
1712 "(select usename from pg_user where oprowner = usesysid) as usename, "
1713 "oprcode::oid as oprcode "
1714 "FROM pg_operator");
1718 appendPQExpBuffer(query, "SELECT "
1719 "(SELECT oid FROM pg_class WHERE relname = 'pg_operator') AS tableoid, "
1721 "0::oid as oprnamespace, "
1722 "(select usename from pg_user where oprowner = usesysid) as usename, "
1723 "oprcode::oid as oprcode "
1724 "FROM pg_operator");
1727 res = PQexec(g_conn, query->data);
1728 check_sql_result(res, g_conn, query->data, PGRES_TUPLES_OK);
1730 ntups = PQntuples(res);
1733 oprinfo = (OprInfo *) malloc(ntups * sizeof(OprInfo));
1735 i_tableoid = PQfnumber(res, "tableoid");
1736 i_oid = PQfnumber(res, "oid");
1737 i_oprname = PQfnumber(res, "oprname");
1738 i_oprnamespace = PQfnumber(res, "oprnamespace");
1739 i_usename = PQfnumber(res, "usename");
1740 i_oprcode = PQfnumber(res, "oprcode");
1742 for (i = 0; i < ntups; i++)
1744 oprinfo[i].dobj.objType = DO_OPERATOR;
1745 oprinfo[i].dobj.catId.tableoid = atooid(PQgetvalue(res, i, i_tableoid));
1746 oprinfo[i].dobj.catId.oid = atooid(PQgetvalue(res, i, i_oid));
1747 AssignDumpId(&oprinfo[i].dobj);
1748 oprinfo[i].oprname = strdup(PQgetvalue(res, i, i_oprname));
1749 oprinfo[i].oprnamespace = findNamespace(atooid(PQgetvalue(res, i, i_oprnamespace)),
1750 oprinfo[i].dobj.catId.oid);
1751 oprinfo[i].usename = strdup(PQgetvalue(res, i, i_usename));
1752 oprinfo[i].oprcode = atooid(PQgetvalue(res, i, i_oprcode));
1754 if (strlen(oprinfo[i].usename) == 0)
1755 write_msg(NULL, "WARNING: owner of operator \"%s\" appears to be invalid\n",
1756 oprinfo[i].oprname);
1761 destroyPQExpBuffer(query);
1768 * read all conversions in the system catalogs and return them in the
1769 * ConvInfo* structure
1771 * numConversions is set to the number of conversions read in
1774 getConversions(int *numConversions)
1779 PQExpBuffer query = createPQExpBuffer();
1787 /* Conversions didn't exist pre-7.3 */
1788 if (g_fout->remoteVersion < 70300) {
1789 *numConversions = 0;
1794 * find all conversions, including builtin conversions; we filter out
1795 * system-defined conversions at dump-out time.
1798 /* Make sure we are in proper schema */
1799 selectSourceSchema("pg_catalog");
1801 appendPQExpBuffer(query, "SELECT tableoid, oid, conname, "
1803 "(select usename from pg_user where conowner = usesysid) as usename "
1804 "FROM pg_conversion");
1806 res = PQexec(g_conn, query->data);
1807 check_sql_result(res, g_conn, query->data, PGRES_TUPLES_OK);
1809 ntups = PQntuples(res);
1810 *numConversions = ntups;
1812 convinfo = (ConvInfo *) malloc(ntups * sizeof(ConvInfo));
1814 i_tableoid = PQfnumber(res, "tableoid");
1815 i_oid = PQfnumber(res, "oid");
1816 i_conname = PQfnumber(res, "conname");
1817 i_connamespace = PQfnumber(res, "connamespace");
1818 i_usename = PQfnumber(res, "usename");
1820 for (i = 0; i < ntups; i++)
1822 convinfo[i].dobj.objType = DO_CONVERSION;
1823 convinfo[i].dobj.catId.tableoid = atooid(PQgetvalue(res, i, i_tableoid));
1824 convinfo[i].dobj.catId.oid = atooid(PQgetvalue(res, i, i_oid));
1825 AssignDumpId(&convinfo[i].dobj);
1826 convinfo[i].conname = strdup(PQgetvalue(res, i, i_conname));
1827 convinfo[i].connamespace = findNamespace(atooid(PQgetvalue(res, i, i_connamespace)),
1828 convinfo[i].dobj.catId.oid);
1829 convinfo[i].usename = strdup(PQgetvalue(res, i, i_usename));
1834 destroyPQExpBuffer(query);
1841 * read all opclasses in the system catalogs and return them in the
1842 * OpclassInfo* structure
1844 * numOpclasses is set to the number of opclasses read in
1847 getOpclasses(int *numOpclasses)
1852 PQExpBuffer query = createPQExpBuffer();
1853 OpclassInfo *opcinfo;
1861 * find all opclasses, including builtin opclasses; we filter out
1862 * system-defined opclasses at dump-out time.
1865 /* Make sure we are in proper schema */
1866 selectSourceSchema("pg_catalog");
1868 if (g_fout->remoteVersion >= 70300)
1870 appendPQExpBuffer(query, "SELECT tableoid, oid, opcname, "
1872 "(select usename from pg_user where opcowner = usesysid) as usename "
1875 else if (g_fout->remoteVersion >= 70100)
1877 appendPQExpBuffer(query, "SELECT tableoid, oid, opcname, "
1878 "0::oid as opcnamespace, "
1879 "''::name as usename "
1884 appendPQExpBuffer(query, "SELECT "
1885 "(SELECT oid FROM pg_class WHERE relname = 'pg_opclass') AS tableoid, "
1887 "0::oid as opcnamespace, "
1888 "''::name as usename "
1892 res = PQexec(g_conn, query->data);
1893 check_sql_result(res, g_conn, query->data, PGRES_TUPLES_OK);
1895 ntups = PQntuples(res);
1896 *numOpclasses = ntups;
1898 opcinfo = (OpclassInfo *) malloc(ntups * sizeof(OpclassInfo));
1900 i_tableoid = PQfnumber(res, "tableoid");
1901 i_oid = PQfnumber(res, "oid");
1902 i_opcname = PQfnumber(res, "opcname");
1903 i_opcnamespace = PQfnumber(res, "opcnamespace");
1904 i_usename = PQfnumber(res, "usename");
1906 for (i = 0; i < ntups; i++)
1908 opcinfo[i].dobj.objType = DO_OPCLASS;
1909 opcinfo[i].dobj.catId.tableoid = atooid(PQgetvalue(res, i, i_tableoid));
1910 opcinfo[i].dobj.catId.oid = atooid(PQgetvalue(res, i, i_oid));
1911 AssignDumpId(&opcinfo[i].dobj);
1912 opcinfo[i].opcname = strdup(PQgetvalue(res, i, i_opcname));
1913 opcinfo[i].opcnamespace = findNamespace(atooid(PQgetvalue(res, i, i_opcnamespace)),
1914 opcinfo[i].dobj.catId.oid);
1915 opcinfo[i].usename = strdup(PQgetvalue(res, i, i_usename));
1917 if (g_fout->remoteVersion >= 70300)
1919 if (strlen(opcinfo[i].usename) == 0)
1920 write_msg(NULL, "WARNING: owner of operator class \"%s\" appears to be invalid\n",
1921 opcinfo[i].opcname);
1927 destroyPQExpBuffer(query);
1934 * read all the user-defined aggregates in the system catalogs and
1935 * return them in the AggInfo* structure
1937 * numAggs is set to the number of aggregates read in
1940 getAggregates(int *numAggs)
1945 PQExpBuffer query = createPQExpBuffer();
1955 /* Make sure we are in proper schema */
1956 selectSourceSchema("pg_catalog");
1958 /* find all user-defined aggregates */
1960 if (g_fout->remoteVersion >= 70300)
1962 appendPQExpBuffer(query, "SELECT tableoid, oid, proname as aggname, "
1963 "pronamespace as aggnamespace, "
1964 "proargtypes[0] as aggbasetype, "
1965 "(select usename from pg_user where proowner = usesysid) as usename, "
1969 "AND pronamespace != "
1970 "(select oid from pg_namespace where nspname = 'pg_catalog')");
1972 else if (g_fout->remoteVersion >= 70100)
1974 appendPQExpBuffer(query, "SELECT tableoid, oid, aggname, "
1975 "0::oid as aggnamespace, "
1977 "(select usename from pg_user where aggowner = usesysid) as usename, "
1979 "FROM pg_aggregate "
1980 "where oid > '%u'::oid",
1981 g_last_builtin_oid);
1985 appendPQExpBuffer(query, "SELECT "
1986 "(SELECT oid FROM pg_class WHERE relname = 'pg_aggregate') AS tableoid, "
1988 "0::oid as aggnamespace, "
1990 "(select usename from pg_user where aggowner = usesysid) as usename, "
1992 "FROM pg_aggregate "
1993 "where oid > '%u'::oid",
1994 g_last_builtin_oid);
1997 res = PQexec(g_conn, query->data);
1998 check_sql_result(res, g_conn, query->data, PGRES_TUPLES_OK);
2000 ntups = PQntuples(res);
2003 agginfo = (AggInfo *) malloc(ntups * sizeof(AggInfo));
2005 i_tableoid = PQfnumber(res, "tableoid");
2006 i_oid = PQfnumber(res, "oid");
2007 i_aggname = PQfnumber(res, "aggname");
2008 i_aggnamespace = PQfnumber(res, "aggnamespace");
2009 i_aggbasetype = PQfnumber(res, "aggbasetype");
2010 i_usename = PQfnumber(res, "usename");
2011 i_aggacl = PQfnumber(res, "aggacl");
2013 for (i = 0; i < ntups; i++)
2015 agginfo[i].aggfn.dobj.objType = DO_AGG;
2016 agginfo[i].aggfn.dobj.catId.tableoid = atooid(PQgetvalue(res, i, i_tableoid));
2017 agginfo[i].aggfn.dobj.catId.oid = atooid(PQgetvalue(res, i, i_oid));
2018 AssignDumpId(&agginfo[i].aggfn.dobj);
2019 agginfo[i].aggfn.proname = strdup(PQgetvalue(res, i, i_aggname));
2020 agginfo[i].aggfn.pronamespace = findNamespace(atooid(PQgetvalue(res, i, i_aggnamespace)),
2021 agginfo[i].aggfn.dobj.catId.oid);
2022 agginfo[i].aggfn.usename = strdup(PQgetvalue(res, i, i_usename));
2023 if (strlen(agginfo[i].aggfn.usename) == 0)
2024 write_msg(NULL, "WARNING: owner of aggregate function \"%s\" appears to be invalid\n",
2025 agginfo[i].aggfn.proname);
2026 agginfo[i].aggfn.lang = InvalidOid; /* not currently interesting */
2027 agginfo[i].aggfn.nargs = 1;
2028 agginfo[i].aggfn.argtypes = (Oid *) malloc(sizeof(Oid));
2029 agginfo[i].aggfn.argtypes[0] = atooid(PQgetvalue(res, i, i_aggbasetype));
2030 agginfo[i].aggfn.prorettype = InvalidOid; /* not saved */
2031 agginfo[i].aggfn.proacl = strdup(PQgetvalue(res, i, i_aggacl));
2032 agginfo[i].anybasetype = false; /* computed when it's dumped */
2033 agginfo[i].fmtbasetype = NULL; /* computed when it's dumped */
2038 destroyPQExpBuffer(query);
2045 * read all the user-defined functions in the system catalogs and
2046 * return them in the FuncInfo* structure
2048 * numFuncs is set to the number of functions read in
2051 getFuncs(int *numFuncs)
2056 PQExpBuffer query = createPQExpBuffer();
2069 /* Make sure we are in proper schema */
2070 selectSourceSchema("pg_catalog");
2072 /* find all user-defined funcs */
2074 if (g_fout->remoteVersion >= 70300)
2076 appendPQExpBuffer(query,
2077 "SELECT tableoid, oid, proname, prolang, "
2078 "pronargs, proargtypes, prorettype, proacl, "
2080 "(select usename from pg_user where proowner = usesysid) as usename "
2082 "WHERE NOT proisagg "
2083 "AND pronamespace != "
2084 "(select oid from pg_namespace where nspname = 'pg_catalog')");
2086 else if (g_fout->remoteVersion >= 70100)
2088 appendPQExpBuffer(query,
2089 "SELECT tableoid, oid, proname, prolang, "
2090 "pronargs, proargtypes, prorettype, "
2091 "'{=X}' as proacl, "
2092 "0::oid as pronamespace, "
2093 "(select usename from pg_user where proowner = usesysid) as usename "
2095 "where pg_proc.oid > '%u'::oid",
2096 g_last_builtin_oid);
2100 appendPQExpBuffer(query,
2102 "(SELECT oid FROM pg_class WHERE relname = 'pg_proc') AS tableoid, "
2103 "oid, proname, prolang, "
2104 "pronargs, proargtypes, prorettype, "
2105 "'{=X}' as proacl, "
2106 "0::oid as pronamespace, "
2107 "(select usename from pg_user where proowner = usesysid) as usename "
2109 "where pg_proc.oid > '%u'::oid",
2110 g_last_builtin_oid);
2113 res = PQexec(g_conn, query->data);
2114 check_sql_result(res, g_conn, query->data, PGRES_TUPLES_OK);
2116 ntups = PQntuples(res);
2120 finfo = (FuncInfo *) calloc(ntups, sizeof(FuncInfo));
2122 i_tableoid = PQfnumber(res, "tableoid");
2123 i_oid = PQfnumber(res, "oid");
2124 i_proname = PQfnumber(res, "proname");
2125 i_pronamespace = PQfnumber(res, "pronamespace");
2126 i_usename = PQfnumber(res, "usename");
2127 i_prolang = PQfnumber(res, "prolang");
2128 i_pronargs = PQfnumber(res, "pronargs");
2129 i_proargtypes = PQfnumber(res, "proargtypes");
2130 i_prorettype = PQfnumber(res, "prorettype");
2131 i_proacl = PQfnumber(res, "proacl");
2133 for (i = 0; i < ntups; i++)
2135 finfo[i].dobj.objType = DO_FUNC;
2136 finfo[i].dobj.catId.tableoid = atooid(PQgetvalue(res, i, i_tableoid));
2137 finfo[i].dobj.catId.oid = atooid(PQgetvalue(res, i, i_oid));
2138 AssignDumpId(&finfo[i].dobj);
2139 finfo[i].proname = strdup(PQgetvalue(res, i, i_proname));
2140 finfo[i].pronamespace = findNamespace(atooid(PQgetvalue(res, i, i_pronamespace)),
2141 finfo[i].dobj.catId.oid);
2142 finfo[i].usename = strdup(PQgetvalue(res, i, i_usename));
2143 finfo[i].lang = atooid(PQgetvalue(res, i, i_prolang));
2144 finfo[i].prorettype = atooid(PQgetvalue(res, i, i_prorettype));
2145 finfo[i].proacl = strdup(PQgetvalue(res, i, i_proacl));
2146 finfo[i].nargs = atoi(PQgetvalue(res, i, i_pronargs));
2147 if (finfo[i].nargs == 0)
2148 finfo[i].argtypes = NULL;
2151 finfo[i].argtypes = (Oid *) malloc(finfo[i].nargs * sizeof(Oid));
2152 parseOidArray(PQgetvalue(res, i, i_proargtypes),
2153 finfo[i].argtypes, finfo[i].nargs);
2156 if (strlen(finfo[i].usename) == 0)
2157 write_msg(NULL, "WARNING: owner of function \"%s\" appears to be invalid\n",
2163 destroyPQExpBuffer(query);
2170 * read all the user-defined tables (no indexes, no catalogs)
2171 * in the system catalogs return them in the TableInfo* structure
2173 * numTables is set to the number of tables read in
2176 getTables(int *numTables)
2181 PQExpBuffer query = createPQExpBuffer();
2182 PQExpBuffer delqry = createPQExpBuffer();
2183 PQExpBuffer lockquery = createPQExpBuffer();
2200 /* Make sure we are in proper schema */
2201 selectSourceSchema("pg_catalog");
2204 * Find all the tables (including views and sequences).
2206 * We include system catalogs, so that we can work if a user table is
2207 * defined to inherit from a system catalog (pretty weird, but...)
2209 * We ignore tables that are not type 'r' (ordinary relation) or 'S'
2210 * (sequence) or 'v' (view).
2212 * Note: in this phase we should collect only a minimal amount of
2213 * information about each table, basically just enough to decide if it
2214 * is interesting. We must fetch all tables in this phase because
2215 * otherwise we cannot correctly identify inherited columns, serial
2219 if (g_fout->remoteVersion >= 70300)
2222 * Left join to pick up dependency info linking sequences to their
2223 * serial column, if any
2225 appendPQExpBuffer(query,
2226 "SELECT c.tableoid, c.oid, relname, "
2227 "relacl, relkind, relnamespace, "
2228 "(select usename from pg_user where relowner = usesysid) as usename, "
2229 "relchecks, reltriggers, "
2230 "relhasindex, relhasrules, relhasoids, "
2231 "d.refobjid as owning_tab, "
2232 "d.refobjsubid as owning_col "
2234 "left join pg_depend d on "
2235 "(c.relkind = '%c' and "
2236 "d.classid = c.tableoid and d.objid = c.oid and "
2237 "d.objsubid = 0 and "
2238 "d.refclassid = c.tableoid and d.deptype = 'i') "
2239 "where relkind in ('%c', '%c', '%c') "
2242 RELKIND_RELATION, RELKIND_SEQUENCE, RELKIND_VIEW);
2244 else if (g_fout->remoteVersion >= 70200)
2246 appendPQExpBuffer(query,
2247 "SELECT tableoid, oid, relname, relacl, relkind, "
2248 "0::oid as relnamespace, "
2249 "(select usename from pg_user where relowner = usesysid) as usename, "
2250 "relchecks, reltriggers, "
2251 "relhasindex, relhasrules, relhasoids, "
2252 "NULL::oid as owning_tab, "
2253 "NULL::int4 as owning_col "
2255 "where relkind in ('%c', '%c', '%c') "
2257 RELKIND_RELATION, RELKIND_SEQUENCE, RELKIND_VIEW);
2259 else if (g_fout->remoteVersion >= 70100)
2261 /* all tables have oids in 7.1 */
2262 appendPQExpBuffer(query,
2263 "SELECT tableoid, oid, relname, relacl, relkind, "
2264 "0::oid as relnamespace, "
2265 "(select usename from pg_user where relowner = usesysid) as usename, "
2266 "relchecks, reltriggers, "
2267 "relhasindex, relhasrules, "
2268 "'t'::bool as relhasoids, "
2269 "NULL::oid as owning_tab, "
2270 "NULL::int4 as owning_col "
2272 "where relkind in ('%c', '%c', '%c') "
2274 RELKIND_RELATION, RELKIND_SEQUENCE, RELKIND_VIEW);
2279 * Before 7.1, view relkind was not set to 'v', so we must check
2280 * if we have a view by looking for a rule in pg_rewrite.
2282 appendPQExpBuffer(query,
2284 "(SELECT oid FROM pg_class WHERE relname = 'pg_class') AS tableoid, "
2285 "oid, relname, relacl, "
2286 "CASE WHEN relhasrules and relkind = 'r' "
2287 " and EXISTS(SELECT rulename FROM pg_rewrite r WHERE "
2288 " r.ev_class = c.oid AND r.ev_type = '1') "
2289 "THEN '%c'::\"char\" "
2290 "ELSE relkind END AS relkind,"
2291 "0::oid as relnamespace, "
2292 "(select usename from pg_user where relowner = usesysid) as usename, "
2293 "relchecks, reltriggers, "
2294 "relhasindex, relhasrules, "
2295 "'t'::bool as relhasoids, "
2296 "NULL::oid as owning_tab, "
2297 "NULL::int4 as owning_col "
2299 "where relkind in ('%c', '%c') "
2302 RELKIND_RELATION, RELKIND_SEQUENCE);
2305 res = PQexec(g_conn, query->data);
2306 check_sql_result(res, g_conn, query->data, PGRES_TUPLES_OK);
2308 ntups = PQntuples(res);
2313 * Extract data from result and lock dumpable tables. We do the
2314 * locking before anything else, to minimize the window wherein a
2315 * table could disappear under us.
2317 * Note that we have to save info about all tables here, even when
2318 * dumping only one, because we don't yet know which tables might be
2319 * inheritance ancestors of the target table.
2321 tblinfo = (TableInfo *) calloc(ntups, sizeof(TableInfo));
2323 i_reltableoid = PQfnumber(res, "tableoid");
2324 i_reloid = PQfnumber(res, "oid");
2325 i_relname = PQfnumber(res, "relname");
2326 i_relnamespace = PQfnumber(res, "relnamespace");
2327 i_relacl = PQfnumber(res, "relacl");
2328 i_relkind = PQfnumber(res, "relkind");
2329 i_usename = PQfnumber(res, "usename");
2330 i_relchecks = PQfnumber(res, "relchecks");
2331 i_reltriggers = PQfnumber(res, "reltriggers");
2332 i_relhasindex = PQfnumber(res, "relhasindex");
2333 i_relhasrules = PQfnumber(res, "relhasrules");
2334 i_relhasoids = PQfnumber(res, "relhasoids");
2335 i_owning_tab = PQfnumber(res, "owning_tab");
2336 i_owning_col = PQfnumber(res, "owning_col");
2338 for (i = 0; i < ntups; i++)
2340 tblinfo[i].dobj.objType = DO_TABLE;
2341 tblinfo[i].dobj.catId.tableoid = atooid(PQgetvalue(res, i, i_reltableoid));
2342 tblinfo[i].dobj.catId.oid = atooid(PQgetvalue(res, i, i_reloid));
2343 AssignDumpId(&tblinfo[i].dobj);
2344 tblinfo[i].relname = strdup(PQgetvalue(res, i, i_relname));
2345 tblinfo[i].relnamespace = findNamespace(atooid(PQgetvalue(res, i, i_relnamespace)),
2346 tblinfo[i].dobj.catId.oid);
2347 tblinfo[i].usename = strdup(PQgetvalue(res, i, i_usename));
2348 tblinfo[i].relacl = strdup(PQgetvalue(res, i, i_relacl));
2349 tblinfo[i].relkind = *(PQgetvalue(res, i, i_relkind));
2350 tblinfo[i].hasindex = (strcmp(PQgetvalue(res, i, i_relhasindex), "t") == 0);
2351 tblinfo[i].hasrules = (strcmp(PQgetvalue(res, i, i_relhasrules), "t") == 0);
2352 tblinfo[i].hasoids = (strcmp(PQgetvalue(res, i, i_relhasoids), "t") == 0);
2353 tblinfo[i].ncheck = atoi(PQgetvalue(res, i, i_relchecks));
2354 tblinfo[i].ntrig = atoi(PQgetvalue(res, i, i_reltriggers));
2355 if (PQgetisnull(res, i, i_owning_tab))
2357 tblinfo[i].owning_tab = InvalidOid;
2358 tblinfo[i].owning_col = 0;
2362 tblinfo[i].owning_tab = atooid(PQgetvalue(res, i, i_owning_tab));
2363 tblinfo[i].owning_col = atoi(PQgetvalue(res, i, i_owning_col));
2366 /* other fields were zeroed above */
2369 * Decide whether we want to dump this table. Sequences owned by
2370 * serial columns are never dumpable on their own; we will
2371 * transpose their owning table's dump flag to them below.
2373 if (OidIsValid(tblinfo[i].owning_tab))
2374 tblinfo[i].dump = false;
2376 selectDumpableTable(&tblinfo[i]);
2377 tblinfo[i].interesting = tblinfo[i].dump;
2380 * Read-lock target tables to make sure they aren't DROPPED or
2381 * altered in schema before we get around to dumping them.
2383 * Note that we don't explicitly lock parents of the target tables;
2384 * we assume our lock on the child is enough to prevent schema
2385 * alterations to parent tables.
2387 * NOTE: it'd be kinda nice to lock views and sequences too, not only
2388 * plain tables, but the backend doesn't presently allow that.
2390 if (tblinfo[i].dump && tblinfo[i].relkind == RELKIND_RELATION)
2392 resetPQExpBuffer(lockquery);
2393 appendPQExpBuffer(lockquery,
2394 "LOCK TABLE %s IN ACCESS SHARE MODE",
2395 fmtQualifiedId(tblinfo[i].relnamespace->nspname,
2396 tblinfo[i].relname));
2397 do_sql_command(g_conn, lockquery->data);
2400 /* Emit notice if join for owner failed */
2401 if (strlen(tblinfo[i].usename) == 0)
2402 write_msg(NULL, "WARNING: owner of table \"%s\" appears to be invalid\n",
2403 tblinfo[i].relname);
2407 * If the user is attempting to dump a specific table, check to ensure
2408 * that the specified table actually exists. (This is a bit simplistic
2409 * since we don't fully check the combination of -n and -t switches.)
2411 if (selectTableName)
2413 for (i = 0; i < ntups; i++)
2414 if (strcmp(tblinfo[i].relname, selectTableName) == 0)
2417 /* Didn't find a match */
2420 write_msg(NULL, "specified table \"%s\" does not exist\n",
2427 destroyPQExpBuffer(query);
2428 destroyPQExpBuffer(delqry);
2429 destroyPQExpBuffer(lockquery);
2436 * read all the inheritance information
2437 * from the system catalogs return them in the InhInfo* structure
2439 * numInherits is set to the number of pairs read in
2442 getInherits(int *numInherits)
2447 PQExpBuffer query = createPQExpBuffer();
2453 /* Make sure we are in proper schema */
2454 selectSourceSchema("pg_catalog");
2456 /* find all the inheritance information */
2458 appendPQExpBuffer(query, "SELECT inhrelid, inhparent from pg_inherits");
2460 res = PQexec(g_conn, query->data);
2461 check_sql_result(res, g_conn, query->data, PGRES_TUPLES_OK);
2463 ntups = PQntuples(res);
2465 *numInherits = ntups;
2467 inhinfo = (InhInfo *) malloc(ntups * sizeof(InhInfo));
2469 i_inhrelid = PQfnumber(res, "inhrelid");
2470 i_inhparent = PQfnumber(res, "inhparent");
2472 for (i = 0; i < ntups; i++)
2474 inhinfo[i].inhrelid = atooid(PQgetvalue(res, i, i_inhrelid));
2475 inhinfo[i].inhparent = atooid(PQgetvalue(res, i, i_inhparent));
2480 destroyPQExpBuffer(query);
2487 * get information about every index on a dumpable table
2489 * Note: index data is not returned directly to the caller, but it
2490 * does get entered into the DumpableObject tables.
2493 getIndexes(TableInfo tblinfo[], int numTables)
2497 PQExpBuffer query = createPQExpBuffer();
2500 ConstraintInfo *constrinfo;
2514 for (i = 0; i < numTables; i++)
2516 TableInfo *tbinfo = &tblinfo[i];
2518 /* Only plain tables have indexes */
2519 if (tbinfo->relkind != RELKIND_RELATION || !tbinfo->hasindex)
2526 write_msg(NULL, "reading indexes for table \"%s\"\n",
2529 /* Make sure we are in proper schema so indexdef is right */
2530 selectSourceSchema(tbinfo->relnamespace->nspname);
2533 * The point of the messy-looking outer join is to find a
2534 * constraint that is related by an internal dependency link to
2535 * the index. If we find one, create a CONSTRAINT entry linked
2536 * to the INDEX entry. We assume an index won't have more than
2537 * one internal dependency.
2539 resetPQExpBuffer(query);
2540 if (g_fout->remoteVersion >= 70300)
2542 appendPQExpBuffer(query,
2543 "SELECT t.tableoid, t.oid, "
2544 "t.relname as indexname, "
2545 "pg_catalog.pg_get_indexdef(i.indexrelid) as indexdef, "
2546 "t.relnatts as indnkeys, "
2547 "i.indkey, i.indisclustered, "
2548 "c.contype, c.conname, "
2549 "c.tableoid as contableoid, "
2551 "FROM pg_catalog.pg_index i "
2552 "JOIN pg_catalog.pg_class t ON (t.oid = i.indexrelid) "
2553 "LEFT JOIN pg_catalog.pg_depend d "
2554 "ON (d.classid = t.tableoid "
2555 "AND d.objid = t.oid "
2556 "AND d.deptype = 'i') "
2557 "LEFT JOIN pg_catalog.pg_constraint c "
2558 "ON (d.refclassid = c.tableoid "
2559 "AND d.refobjid = c.oid) "
2560 "WHERE i.indrelid = '%u'::pg_catalog.oid "
2561 "ORDER BY indexname",
2562 tbinfo->dobj.catId.oid);
2564 else if (g_fout->remoteVersion >= 70100)
2566 appendPQExpBuffer(query,
2567 "SELECT t.tableoid, t.oid, "
2568 "t.relname as indexname, "
2569 "pg_get_indexdef(i.indexrelid) as indexdef, "
2570 "t.relnatts as indnkeys, "
2571 "i.indkey, false as indisclustered, "
2572 "CASE WHEN i.indisprimary THEN 'p'::char "
2573 "ELSE '0'::char END as contype, "
2574 "t.relname as conname, "
2575 "0::oid as contableoid, "
2577 "FROM pg_index i, pg_class t "
2578 "WHERE t.oid = i.indexrelid "
2579 "AND i.indrelid = '%u'::oid "
2580 "ORDER BY indexname",
2581 tbinfo->dobj.catId.oid);
2585 appendPQExpBuffer(query,
2587 "(SELECT oid FROM pg_class WHERE relname = 'pg_class') AS tableoid, "
2589 "t.relname as indexname, "
2590 "pg_get_indexdef(i.indexrelid) as indexdef, "
2591 "t.relnatts as indnkeys, "
2592 "i.indkey, false as indisclustered, "
2593 "CASE WHEN i.indisprimary THEN 'p'::char "
2594 "ELSE '0'::char END as contype, "
2595 "t.relname as conname, "
2596 "0::oid as contableoid, "
2598 "FROM pg_index i, pg_class t "
2599 "WHERE t.oid = i.indexrelid "
2600 "AND i.indrelid = '%u'::oid "
2601 "ORDER BY indexname",
2602 tbinfo->dobj.catId.oid);
2605 res = PQexec(g_conn, query->data);
2606 check_sql_result(res, g_conn, query->data, PGRES_TUPLES_OK);
2608 ntups = PQntuples(res);
2610 i_tableoid = PQfnumber(res, "tableoid");
2611 i_oid = PQfnumber(res, "oid");
2612 i_indexname = PQfnumber(res, "indexname");
2613 i_indexdef = PQfnumber(res, "indexdef");
2614 i_indnkeys = PQfnumber(res, "indnkeys");
2615 i_indkey = PQfnumber(res, "indkey");
2616 i_indisclustered = PQfnumber(res, "indisclustered");
2617 i_contype = PQfnumber(res, "contype");
2618 i_conname = PQfnumber(res, "conname");
2619 i_contableoid = PQfnumber(res, "contableoid");
2620 i_conoid = PQfnumber(res, "conoid");
2622 indxinfo = (IndxInfo *) malloc(ntups * sizeof(IndxInfo));
2623 constrinfo = (ConstraintInfo *) malloc(ntups * sizeof(ConstraintInfo));
2625 for (j = 0; j < ntups; j++)
2629 indxinfo[j].dobj.objType = DO_INDEX;
2630 indxinfo[j].dobj.catId.tableoid = atooid(PQgetvalue(res, j, i_tableoid));
2631 indxinfo[j].dobj.catId.oid = atooid(PQgetvalue(res, j, i_oid));
2632 AssignDumpId(&indxinfo[j].dobj);
2633 indxinfo[j].indexname = strdup(PQgetvalue(res, j, i_indexname));
2634 indxinfo[j].indextable = tbinfo;
2635 indxinfo[j].indexdef = strdup(PQgetvalue(res, j, i_indexdef));
2636 indxinfo[j].indnkeys = atoi(PQgetvalue(res, j, i_indnkeys));
2638 * In pre-7.4 releases, indkeys may contain more entries than
2639 * indnkeys says (since indnkeys will be 1 for a functional
2640 * index). We don't actually care about this case since we don't
2641 * examine indkeys except for indexes associated with PRIMARY
2642 * and UNIQUE constraints, which are never functional indexes.
2643 * But we have to allocate enough space to keep parseOidArray
2646 indxinfo[j].indkeys = (Oid *) malloc(INDEX_MAX_KEYS * sizeof(Oid));
2647 parseOidArray(PQgetvalue(res, j, i_indkey),
2648 indxinfo[j].indkeys, INDEX_MAX_KEYS);
2649 indxinfo[j].indisclustered = (PQgetvalue(res, j, i_indisclustered)[0] == 't');
2650 contype = *(PQgetvalue(res, j, i_contype));
2652 if (contype == 'p' || contype == 'u')
2655 * If we found a constraint matching the index, create an
2658 * In a pre-7.3 database, we take this path iff the index was
2659 * marked indisprimary.
2661 constrinfo[j].dobj.objType = DO_CONSTRAINT;
2662 constrinfo[j].dobj.catId.tableoid = atooid(PQgetvalue(res, j, i_contableoid));
2663 constrinfo[j].dobj.catId.oid = atooid(PQgetvalue(res, j, i_conoid));
2664 AssignDumpId(&constrinfo[j].dobj);
2666 constrinfo[j].conname = strdup(PQgetvalue(res, j, i_conname));
2667 constrinfo[j].contable = tbinfo;
2668 constrinfo[j].condomain = NULL;
2669 constrinfo[j].contype = contype;
2670 constrinfo[j].condef = NULL;
2671 constrinfo[j].conindex = indxinfo[j].dobj.dumpId;
2672 constrinfo[j].coninherited = false;
2673 constrinfo[j].separate = true;
2675 indxinfo[j].indexconstraint = constrinfo[j].dobj.dumpId;
2677 /* If pre-7.3 DB, better make sure table comes first */
2678 addObjectDependency(&constrinfo[j].dobj,
2679 tbinfo->dobj.dumpId);
2683 /* Plain secondary index */
2684 indxinfo[j].indexconstraint = 0;
2691 destroyPQExpBuffer(query);
2697 * Get info about constraints on dumpable tables.
2699 * Currently handles foreign keys only.
2700 * Unique and primary key constraints are handled with indexes,
2701 * while check constraints are processed in getTableAttrs().
2704 getConstraints(TableInfo tblinfo[], int numTables)
2708 ConstraintInfo *constrinfo;
2717 /* pg_constraint was created in 7.3, so nothing to do if older */
2718 if (g_fout->remoteVersion < 70300)
2721 query = createPQExpBuffer();
2723 for (i = 0; i < numTables; i++)
2725 TableInfo *tbinfo = &tblinfo[i];
2727 if (tbinfo->ntrig == 0 || !tbinfo->dump)
2731 write_msg(NULL, "reading foreign key constraints for table \"%s\"\n",
2735 * select table schema to ensure constraint expr is qualified if
2738 selectSourceSchema(tbinfo->relnamespace->nspname);
2740 resetPQExpBuffer(query);
2741 appendPQExpBuffer(query,
2742 "SELECT tableoid, oid, conname, "
2743 "pg_catalog.pg_get_constraintdef(oid) as condef "
2744 "FROM pg_catalog.pg_constraint "
2745 "WHERE conrelid = '%u'::pg_catalog.oid "
2746 "AND contype = 'f'",
2747 tbinfo->dobj.catId.oid);
2748 res = PQexec(g_conn, query->data);
2749 check_sql_result(res, g_conn, query->data, PGRES_TUPLES_OK);
2751 ntups = PQntuples(res);
2753 i_contableoid = PQfnumber(res, "tableoid");
2754 i_conoid = PQfnumber(res, "oid");
2755 i_conname = PQfnumber(res, "conname");
2756 i_condef = PQfnumber(res, "condef");
2758 constrinfo = (ConstraintInfo *) malloc(ntups * sizeof(ConstraintInfo));
2760 for (j = 0; j < ntups; j++)
2762 constrinfo[j].dobj.objType = DO_FK_CONSTRAINT;
2763 constrinfo[j].dobj.catId.tableoid = atooid(PQgetvalue(res, j, i_contableoid));
2764 constrinfo[j].dobj.catId.oid = atooid(PQgetvalue(res, j, i_conoid));
2765 AssignDumpId(&constrinfo[j].dobj);
2766 constrinfo[j].conname = strdup(PQgetvalue(res, j, i_conname));
2767 constrinfo[j].contable = tbinfo;
2768 constrinfo[j].condomain = NULL;
2769 constrinfo[j].contype = 'f';
2770 constrinfo[j].condef = strdup(PQgetvalue(res, j, i_condef));
2771 constrinfo[j].conindex = 0;
2772 constrinfo[j].coninherited = false;
2773 constrinfo[j].separate = true;
2779 destroyPQExpBuffer(query);
2783 * getDomainConstraints
2785 * Get info about constraints on a domain.
2788 getDomainConstraints(TypeInfo *tinfo)
2791 ConstraintInfo *constrinfo;
2800 /* pg_constraint was created in 7.3, so nothing to do if older */
2801 if (g_fout->remoteVersion < 70300)
2805 * select appropriate schema to ensure names in constraint are properly
2808 selectSourceSchema(tinfo->typnamespace->nspname);
2810 query = createPQExpBuffer();
2812 if (g_fout->remoteVersion >= 70400)
2813 appendPQExpBuffer(query, "SELECT tableoid, oid, conname, "
2814 "pg_catalog.pg_get_constraintdef(oid) AS consrc "
2815 "FROM pg_catalog.pg_constraint "
2816 "WHERE contypid = '%u'::pg_catalog.oid "
2818 tinfo->dobj.catId.oid);
2820 appendPQExpBuffer(query, "SELECT tableoid, oid, conname, "
2821 "'CHECK (' || consrc || ')' AS consrc "
2822 "FROM pg_catalog.pg_constraint "
2823 "WHERE contypid = '%u'::pg_catalog.oid "
2825 tinfo->dobj.catId.oid);
2827 res = PQexec(g_conn, query->data);
2828 check_sql_result(res, g_conn, query->data, PGRES_TUPLES_OK);
2830 ntups = PQntuples(res);
2832 i_tableoid = PQfnumber(res, "tableoid");
2833 i_oid = PQfnumber(res, "oid");
2834 i_conname = PQfnumber(res, "conname");
2835 i_consrc = PQfnumber(res, "consrc");
2837 constrinfo = (ConstraintInfo *) malloc(ntups * sizeof(ConstraintInfo));
2839 tinfo->nDomChecks = ntups;
2840 tinfo->domChecks = constrinfo;
2842 for (i = 0; i < ntups; i++)
2844 constrinfo[i].dobj.objType = DO_CONSTRAINT;
2845 constrinfo[i].dobj.catId.tableoid = atooid(PQgetvalue(res, i, i_tableoid));
2846 constrinfo[i].dobj.catId.oid = atooid(PQgetvalue(res, i, i_oid));
2847 AssignDumpId(&constrinfo[i].dobj);
2848 constrinfo[i].conname = strdup(PQgetvalue(res, i, i_conname));
2849 constrinfo[i].contable = NULL;
2850 constrinfo[i].condomain = tinfo;
2851 constrinfo[i].contype = 'c';
2852 constrinfo[i].condef = strdup(PQgetvalue(res, i, i_consrc));
2853 constrinfo[i].conindex = 0;
2854 constrinfo[i].coninherited = false;
2855 constrinfo[i].separate = false;
2857 * Make the domain depend on the constraint, ensuring it won't
2858 * be output till any constraint dependencies are OK.
2860 addObjectDependency(&tinfo->dobj,
2861 constrinfo[i].dobj.dumpId);
2866 destroyPQExpBuffer(query);
2871 * get basic information about every rule in the system
2873 * numRules is set to the number of rules read in
2876 getRules(int *numRules)
2881 PQExpBuffer query = createPQExpBuffer();
2890 /* Make sure we are in proper schema */
2891 selectSourceSchema("pg_catalog");
2893 if (g_fout->remoteVersion >= 70100)
2895 appendPQExpBuffer(query, "SELECT "
2896 "tableoid, oid, rulename, "
2897 "ev_class as ruletable, ev_type, is_instead "
2903 appendPQExpBuffer(query, "SELECT "
2904 "(SELECT oid FROM pg_class WHERE relname = 'pg_rewrite') AS tableoid, "
2906 "ev_class as ruletable, ev_type, is_instead "
2911 res = PQexec(g_conn, query->data);
2912 check_sql_result(res, g_conn, query->data, PGRES_TUPLES_OK);
2914 ntups = PQntuples(res);
2918 ruleinfo = (RuleInfo *) malloc(ntups * sizeof(RuleInfo));
2920 i_tableoid = PQfnumber(res, "tableoid");
2921 i_oid = PQfnumber(res, "oid");
2922 i_rulename = PQfnumber(res, "rulename");
2923 i_ruletable = PQfnumber(res, "ruletable");
2924 i_ev_type = PQfnumber(res, "ev_type");
2925 i_is_instead = PQfnumber(res, "is_instead");
2927 for (i = 0; i < ntups; i++)
2931 ruleinfo[i].dobj.objType = DO_RULE;
2932 ruleinfo[i].dobj.catId.tableoid = atooid(PQgetvalue(res, i, i_tableoid));
2933 ruleinfo[i].dobj.catId.oid = atooid(PQgetvalue(res, i, i_oid));
2934 AssignDumpId(&ruleinfo[i].dobj);
2935 ruleinfo[i].rulename = strdup(PQgetvalue(res, i, i_rulename));
2936 ruletableoid = atooid(PQgetvalue(res, i, i_ruletable));
2937 ruleinfo[i].ruletable = findTableByOid(ruletableoid);
2938 ruleinfo[i].ev_type = *(PQgetvalue(res, i, i_ev_type));
2939 ruleinfo[i].is_instead = *(PQgetvalue(res, i, i_is_instead)) == 't';
2940 if (ruleinfo[i].ruletable)
2943 * If the table is a view, force its ON SELECT rule to be sorted
2944 * before the view itself --- this ensures that any dependencies
2945 * for the rule affect the table's positioning. Other rules
2946 * are forced to appear after their table.
2948 if (ruleinfo[i].ruletable->relkind == RELKIND_VIEW &&
2949 ruleinfo[i].ev_type == '1' && ruleinfo[i].is_instead)
2950 addObjectDependency(&ruleinfo[i].ruletable->dobj,
2951 ruleinfo[i].dobj.dumpId);
2953 addObjectDependency(&ruleinfo[i].dobj,
2954 ruleinfo[i].ruletable->dobj.dumpId);
2960 destroyPQExpBuffer(query);
2967 * get information about every trigger on a dumpable table
2969 * Note: trigger data is not returned directly to the caller, but it
2970 * does get entered into the DumpableObject tables.
2973 getTriggers(TableInfo tblinfo[], int numTables)
2977 PQExpBuffer query = createPQExpBuffer();
2979 TriggerInfo *tginfo;
2995 for (i = 0; i < numTables; i++)
2997 TableInfo *tbinfo = &tblinfo[i];
2999 if (tbinfo->ntrig == 0 || !tbinfo->dump)
3003 write_msg(NULL, "reading triggers for table \"%s\"\n",
3007 * select table schema to ensure regproc name is qualified if
3010 selectSourceSchema(tbinfo->relnamespace->nspname);
3012 resetPQExpBuffer(query);
3013 if (g_fout->remoteVersion >= 70300)
3016 * We ignore triggers that are tied to a foreign-key
3019 appendPQExpBuffer(query,
3021 "tgfoid::pg_catalog.regproc as tgfname, "
3022 "tgtype, tgnargs, tgargs, "
3023 "tgisconstraint, tgconstrname, tgdeferrable, "
3024 "tgconstrrelid, tginitdeferred, tableoid, oid, "
3025 "tgconstrrelid::pg_catalog.regclass as tgconstrrelname "
3026 "from pg_catalog.pg_trigger t "
3027 "where tgrelid = '%u'::pg_catalog.oid "
3028 "and (not tgisconstraint "
3030 " (SELECT 1 FROM pg_catalog.pg_depend d "
3031 " JOIN pg_catalog.pg_constraint c ON (d.refclassid = c.tableoid AND d.refobjid = c.oid) "
3032 " WHERE d.classid = t.tableoid AND d.objid = t.oid AND d.deptype = 'i' AND c.contype = 'f'))",
3033 tbinfo->dobj.catId.oid);
3035 else if (g_fout->remoteVersion >= 70100)
3037 appendPQExpBuffer(query,
3038 "SELECT tgname, tgfoid::regproc as tgfname, "
3039 "tgtype, tgnargs, tgargs, "
3040 "tgisconstraint, tgconstrname, tgdeferrable, "
3041 "tgconstrrelid, tginitdeferred, tableoid, oid, "
3042 "(select relname from pg_class where oid = tgconstrrelid) "
3043 " as tgconstrrelname "
3045 "where tgrelid = '%u'::oid",
3046 tbinfo->dobj.catId.oid);
3050 appendPQExpBuffer(query,
3051 "SELECT tgname, tgfoid::regproc as tgfname, "
3052 "tgtype, tgnargs, tgargs, "
3053 "tgisconstraint, tgconstrname, tgdeferrable, "
3054 "tgconstrrelid, tginitdeferred, "
3055 "(SELECT oid FROM pg_class WHERE relname = 'pg_trigger') AS tableoid, "
3058 "(select relname from pg_class where oid = tgconstrrelid) "
3059 " as tgconstrrelname "
3061 "where tgrelid = '%u'::oid",
3062 tbinfo->dobj.catId.oid);
3064 res = PQexec(g_conn, query->data);
3065 check_sql_result(res, g_conn, query->data, PGRES_TUPLES_OK);
3067 ntups = PQntuples(res);
3070 * We may have less triggers than recorded due to having ignored
3071 * foreign-key triggers
3073 if (ntups > tbinfo->ntrig)
3075 write_msg(NULL, "expected %d triggers on table \"%s\" but found %d\n",
3076 tbinfo->ntrig, tbinfo->relname, ntups);
3079 i_tableoid = PQfnumber(res, "tableoid");
3080 i_oid = PQfnumber(res, "oid");
3081 i_tgname = PQfnumber(res, "tgname");
3082 i_tgfname = PQfnumber(res, "tgfname");
3083 i_tgtype = PQfnumber(res, "tgtype");
3084 i_tgnargs = PQfnumber(res, "tgnargs");
3085 i_tgargs = PQfnumber(res, "tgargs");
3086 i_tgisconstraint = PQfnumber(res, "tgisconstraint");
3087 i_tgconstrname = PQfnumber(res, "tgconstrname");
3088 i_tgconstrrelid = PQfnumber(res, "tgconstrrelid");
3089 i_tgconstrrelname = PQfnumber(res, "tgconstrrelname");
3090 i_tgdeferrable = PQfnumber(res, "tgdeferrable");
3091 i_tginitdeferred = PQfnumber(res, "tginitdeferred");
3093 tginfo = (TriggerInfo *) malloc(ntups * sizeof(TriggerInfo));
3095 for (j = 0; j < ntups; j++)
3097 tginfo[j].dobj.objType = DO_TRIGGER;
3098 tginfo[j].dobj.catId.tableoid = atooid(PQgetvalue(res, j, i_tableoid));
3099 tginfo[j].dobj.catId.oid = atooid(PQgetvalue(res, j, i_oid));
3100 AssignDumpId(&tginfo[j].dobj);
3101 tginfo[j].tgtable = tbinfo;
3102 tginfo[j].tgname = strdup(PQgetvalue(res, j, i_tgname));
3103 tginfo[j].tgfname = strdup(PQgetvalue(res, j, i_tgfname));
3104 tginfo[j].tgtype = atoi(PQgetvalue(res, j, i_tgtype));
3105 tginfo[j].tgnargs = atoi(PQgetvalue(res, j, i_tgnargs));
3106 tginfo[j].tgargs = strdup(PQgetvalue(res, j, i_tgargs));
3107 tginfo[j].tgisconstraint = *(PQgetvalue(res, j, i_tgisconstraint)) == 't';
3108 tginfo[j].tgdeferrable = *(PQgetvalue(res, j, i_tgdeferrable)) == 't';
3109 tginfo[j].tginitdeferred = *(PQgetvalue(res, j, i_tginitdeferred)) == 't';
3111 if (tginfo[j].tgisconstraint)
3113 tginfo[j].tgconstrname = strdup(PQgetvalue(res, j, i_tgconstrname));
3114 tginfo[j].tgconstrrelid = atooid(PQgetvalue(res, j, i_tgconstrrelid));
3115 if (OidIsValid(tginfo[j].tgconstrrelid))
3117 if (PQgetisnull(res, j, i_tgconstrrelname))
3119 write_msg(NULL, "query produced null referenced table name for foreign key trigger \"%s\" on table \"%s\" (OID of table: %u)\n",
3120 tginfo[j].tgname, tbinfo->relname,
3121 tginfo[j].tgconstrrelid);
3124 tginfo[j].tgconstrrelname = strdup(PQgetvalue(res, j, i_tgconstrrelname));
3127 tginfo[j].tgconstrrelname = NULL;
3131 tginfo[j].tgconstrname = NULL;
3132 tginfo[j].tgconstrrelid = InvalidOid;
3133 tginfo[j].tgconstrrelname = NULL;
3140 destroyPQExpBuffer(query);
3145 * get basic information about every procedural language in the system
3147 * numProcLangs is set to the number of langs read in
3149 * NB: this must run after getFuncs() because we assume we can do
3153 getProcLangs(int *numProcLangs)
3158 PQExpBuffer query = createPQExpBuffer();
3159 ProcLangInfo *planginfo;
3164 int i_lanplcallfoid;
3165 int i_lanvalidator = -1;
3168 /* Make sure we are in proper schema */
3169 selectSourceSchema("pg_catalog");
3171 if (g_fout->remoteVersion >= 70100)
3173 appendPQExpBuffer(query, "SELECT tableoid, oid, * FROM pg_language "
3179 appendPQExpBuffer(query, "SELECT "
3180 "(SELECT oid FROM pg_class WHERE relname = 'pg_language') AS tableoid, "
3181 "oid, * FROM pg_language "
3186 res = PQexec(g_conn, query->data);
3187 check_sql_result(res, g_conn, query->data, PGRES_TUPLES_OK);
3189 ntups = PQntuples(res);
3191 *numProcLangs = ntups;
3193 planginfo = (ProcLangInfo *) malloc(ntups * sizeof(ProcLangInfo));
3195 i_tableoid = PQfnumber(res, "tableoid");
3196 i_oid = PQfnumber(res, "oid");
3197 i_lanname = PQfnumber(res, "lanname");
3198 i_lanpltrusted = PQfnumber(res, "lanpltrusted");
3199 i_lanplcallfoid = PQfnumber(res, "lanplcallfoid");
3200 if (g_fout->remoteVersion >= 70300)
3202 i_lanvalidator = PQfnumber(res, "lanvalidator");
3203 i_lanacl = PQfnumber(res, "lanacl");
3206 for (i = 0; i < ntups; i++)
3208 planginfo[i].dobj.objType = DO_PROCLANG;
3209 planginfo[i].dobj.catId.tableoid = atooid(PQgetvalue(res, i, i_tableoid));
3210 planginfo[i].dobj.catId.oid = atooid(PQgetvalue(res, i, i_oid));
3211 AssignDumpId(&planginfo[i].dobj);
3213 planginfo[i].lanname = strdup(PQgetvalue(res, i, i_lanname));
3214 planginfo[i].lanpltrusted = *(PQgetvalue(res, i, i_lanpltrusted)) == 't';
3215 planginfo[i].lanplcallfoid = atooid(PQgetvalue(res, i, i_lanplcallfoid));
3216 if (g_fout->remoteVersion >= 70300)
3218 planginfo[i].lanvalidator = atooid(PQgetvalue(res, i, i_lanvalidator));
3219 planginfo[i].lanacl = strdup(PQgetvalue(res, i, i_lanacl));
3225 planginfo[i].lanvalidator = InvalidOid;
3226 planginfo[i].lanacl = strdup("{=U}");
3228 * We need to make a dependency to ensure the function will
3229 * be dumped first. (In 7.3 and later the regular dependency
3230 * mechanism will handle this for us.)
3232 funcInfo = findFuncByOid(planginfo[i].lanplcallfoid);
3234 addObjectDependency(&planginfo[i].dobj,
3235 funcInfo->dobj.dumpId);
3241 destroyPQExpBuffer(query);
3248 * get basic information about every cast in the system
3250 * numCasts is set to the number of casts read in
3253 getCasts(int *numCasts)
3258 PQExpBuffer query = createPQExpBuffer();
3267 /* Make sure we are in proper schema */
3268 selectSourceSchema("pg_catalog");
3270 if (g_fout->remoteVersion >= 70300)
3272 appendPQExpBuffer(query, "SELECT tableoid, oid, "
3273 "castsource, casttarget, castfunc, castcontext "
3274 "FROM pg_cast ORDER BY 3,4");
3278 appendPQExpBuffer(query, "SELECT 0 as tableoid, p.oid, "
3279 "t1.oid as castsource, t2.oid as casttarget, "
3280 "p.oid as castfunc, 'e' as castcontext "
3281 "FROM pg_type t1, pg_type t2, pg_proc p "
3282 "WHERE p.pronargs = 1 AND "
3283 "p.proargtypes[0] = t1.oid AND "
3284 "p.prorettype = t2.oid AND p.proname = t2.typname "
3288 res = PQexec(g_conn, query->data);
3289 check_sql_result(res, g_conn, query->data, PGRES_TUPLES_OK);
3291 ntups = PQntuples(res);
3295 castinfo = (CastInfo *) malloc(ntups * sizeof(CastInfo));
3297 i_tableoid = PQfnumber(res, "tableoid");
3298 i_oid = PQfnumber(res, "oid");
3299 i_castsource = PQfnumber(res, "castsource");
3300 i_casttarget = PQfnumber(res, "casttarget");
3301 i_castfunc = PQfnumber(res, "castfunc");
3302 i_castcontext = PQfnumber(res, "castcontext");
3304 for (i = 0; i < ntups; i++)
3306 castinfo[i].dobj.objType = DO_CAST;
3307 castinfo[i].dobj.catId.tableoid = atooid(PQgetvalue(res, i, i_tableoid));
3308 castinfo[i].dobj.catId.oid = atooid(PQgetvalue(res, i, i_oid));
3309 AssignDumpId(&castinfo[i].dobj);
3310 castinfo[i].castsource = atooid(PQgetvalue(res, i, i_castsource));
3311 castinfo[i].casttarget = atooid(PQgetvalue(res, i, i_casttarget));
3312 castinfo[i].castfunc = atooid(PQgetvalue(res, i, i_castfunc));
3313 castinfo[i].castcontext = *(PQgetvalue(res, i, i_castcontext));
3315 if (OidIsValid(castinfo[i].castfunc))
3318 * We need to make a dependency to ensure the function will
3319 * be dumped first. (In 7.3 and later the regular dependency
3320 * mechanism will handle this for us.)
3324 funcInfo = findFuncByOid(castinfo[i].castfunc);
3326 addObjectDependency(&castinfo[i].dobj,
3327 funcInfo->dobj.dumpId);
3333 destroyPQExpBuffer(query);
3340 * for each interesting table, read info about its attributes
3341 * (names, types, default values, CHECK constraints, etc)
3343 * This is implemented in a very inefficient way right now, looping
3344 * through the tblinfo and doing a join per table to find the attrs and their
3345 * types. However, because we want type names and so forth to be named
3346 * relative to the schema of each table, we couldn't do it in just one
3347 * query. (Maybe one query per schema?)
3352 getTableAttrs(TableInfo *tblinfo, int numTables)
3357 PQExpBuffer q = createPQExpBuffer();
3362 int i_attstattarget;
3373 for (i = 0; i < numTables; i++)
3375 TableInfo *tbinfo = &tblinfo[i];
3377 /* Don't bother to collect info for sequences */
3378 if (tbinfo->relkind == RELKIND_SEQUENCE)
3381 /* Don't bother with uninteresting tables, either */
3382 if (!tbinfo->interesting)
3386 * Make sure we are in proper schema for this table; this allows
3387 * correct retrieval of formatted type names and default exprs
3389 selectSourceSchema(tbinfo->relnamespace->nspname);
3391 /* find all the user attributes and their types */
3394 * we must read the attribute names in attribute number order!
3395 * because we will use the attnum to index into the attnames array
3396 * later. We actually ask to order by "attrelid, attnum" because
3397 * (at least up to 7.3) the planner is not smart enough to realize
3398 * it needn't re-sort the output of an indexscan on
3399 * pg_attribute_relid_attnum_index.
3402 write_msg(NULL, "finding the columns and types of table \"%s\"\n",
3405 resetPQExpBuffer(q);
3407 if (g_fout->remoteVersion >= 70300)
3409 /* need left join here to not fail on dropped columns ... */
3410 appendPQExpBuffer(q, "SELECT a.attnum, a.attname, a.atttypmod, a.attstattarget, a.attstorage, t.typstorage, "
3411 "a.attnotnull, a.atthasdef, a.attisdropped, a.attislocal, "
3412 "pg_catalog.format_type(t.oid,a.atttypmod) as atttypname "
3413 "from pg_catalog.pg_attribute a left join pg_catalog.pg_type t "
3414 "on a.atttypid = t.oid "
3415 "where a.attrelid = '%u'::pg_catalog.oid "
3416 "and a.attnum > 0::pg_catalog.int2 "
3417 "order by a.attrelid, a.attnum",
3418 tbinfo->dobj.catId.oid);
3420 else if (g_fout->remoteVersion >= 70100)
3423 * attstattarget doesn't exist in 7.1. It does exist in 7.2,
3424 * but we don't dump it because we can't tell whether it's
3425 * been explicitly set or was just a default.
3427 appendPQExpBuffer(q, "SELECT a.attnum, a.attname, a.atttypmod, -1 as attstattarget, a.attstorage, t.typstorage, "
3428 "a.attnotnull, a.atthasdef, false as attisdropped, false as attislocal, "
3429 "format_type(t.oid,a.atttypmod) as atttypname "
3430 "from pg_attribute a left join pg_type t "
3431 "on a.atttypid = t.oid "
3432 "where a.attrelid = '%u'::oid "
3433 "and a.attnum > 0::int2 "
3434 "order by a.attrelid, a.attnum",
3435 tbinfo->dobj.catId.oid);
3439 /* format_type not available before 7.1 */
3440 appendPQExpBuffer(q, "SELECT attnum, attname, atttypmod, -1 as attstattarget, attstorage, attstorage as typstorage, "
3441 "attnotnull, atthasdef, false as attisdropped, false as attislocal, "
3442 "(select typname from pg_type where oid = atttypid) as atttypname "
3443 "from pg_attribute a "
3444 "where attrelid = '%u'::oid "
3445 "and attnum > 0::int2 "
3446 "order by attrelid, attnum",
3447 tbinfo->dobj.catId.oid);
3450 res = PQexec(g_conn, q->data);
3451 check_sql_result(res, g_conn, q->data, PGRES_TUPLES_OK);
3453 ntups = PQntuples(res);
3455 i_attnum = PQfnumber(res, "attnum");
3456 i_attname = PQfnumber(res, "attname");
3457 i_atttypname = PQfnumber(res, "atttypname");
3458 i_atttypmod = PQfnumber(res, "atttypmod");
3459 i_attstattarget = PQfnumber(res, "attstattarget");
3460 i_attstorage = PQfnumber(res, "attstorage");
3461 i_typstorage = PQfnumber(res, "typstorage");
3462 i_attnotnull = PQfnumber(res, "attnotnull");
3463 i_atthasdef = PQfnumber(res, "atthasdef");
3464 i_attisdropped = PQfnumber(res, "attisdropped");
3465 i_attislocal = PQfnumber(res, "attislocal");
3467 tbinfo->numatts = ntups;
3468 tbinfo->attnames = (char **) malloc(ntups * sizeof(char *));
3469 tbinfo->atttypnames = (char **) malloc(ntups * sizeof(char *));
3470 tbinfo->atttypmod = (int *) malloc(ntups * sizeof(int));
3471 tbinfo->attstattarget = (int *) malloc(ntups * sizeof(int));
3472 tbinfo->attstorage = (char *) malloc(ntups * sizeof(char));
3473 tbinfo->typstorage = (char *) malloc(ntups * sizeof(char));
3474 tbinfo->attisdropped = (bool *) malloc(ntups * sizeof(bool));
3475 tbinfo->attislocal = (bool *) malloc(ntups * sizeof(bool));
3476 tbinfo->attisserial = (bool *) malloc(ntups * sizeof(bool));
3477 tbinfo->notnull = (bool *) malloc(ntups * sizeof(bool));
3478 tbinfo->attrdefs = (AttrDefInfo **) malloc(ntups * sizeof(AttrDefInfo *));
3479 tbinfo->inhAttrs = (bool *) malloc(ntups * sizeof(bool));
3480 tbinfo->inhAttrDef = (bool *) malloc(ntups * sizeof(bool));
3481 tbinfo->inhNotNull = (bool *) malloc(ntups * sizeof(bool));
3482 hasdefaults = false;
3484 for (j = 0; j < ntups; j++)
3486 if (j + 1 != atoi(PQgetvalue(res, j, i_attnum)))
3488 write_msg(NULL, "invalid column numbering in table \"%s\"\n",
3492 tbinfo->attnames[j] = strdup(PQgetvalue(res, j, i_attname));
3493 tbinfo->atttypnames[j] = strdup(PQgetvalue(res, j, i_atttypname));
3494 tbinfo->atttypmod[j] = atoi(PQgetvalue(res, j, i_atttypmod));
3495 tbinfo->attstattarget[j] = atoi(PQgetvalue(res, j, i_attstattarget));
3496 tbinfo->attstorage[j] = *(PQgetvalue(res, j, i_attstorage));
3497 tbinfo->typstorage[j] = *(PQgetvalue(res, j, i_typstorage));
3498 tbinfo->attisdropped[j] = (PQgetvalue(res, j, i_attisdropped)[0] == 't');
3499 tbinfo->attislocal[j] = (PQgetvalue(res, j, i_attislocal)[0] == 't');
3500 tbinfo->attisserial[j] = false; /* fix below */
3501 tbinfo->notnull[j] = (PQgetvalue(res, j, i_attnotnull)[0] == 't');
3502 tbinfo->attrdefs[j] = NULL; /* fix below */
3503 if (PQgetvalue(res, j, i_atthasdef)[0] == 't')
3505 /* these flags will be set in flagInhAttrs() */
3506 tbinfo->inhAttrs[j] = false;
3507 tbinfo->inhAttrDef[j] = false;
3508 tbinfo->inhNotNull[j] = false;
3514 * Get info about column defaults
3518 AttrDefInfo *attrdefs;
3522 write_msg(NULL, "finding default expressions of table \"%s\"\n",
3525 resetPQExpBuffer(q);
3526 if (g_fout->remoteVersion >= 70300)
3528 appendPQExpBuffer(q, "SELECT tableoid, oid, adnum, "
3529 "pg_catalog.pg_get_expr(adbin, adrelid) AS adsrc "
3530 "FROM pg_catalog.pg_attrdef "
3531 "WHERE adrelid = '%u'::pg_catalog.oid",
3532 tbinfo->dobj.catId.oid);
3534 else if (g_fout->remoteVersion >= 70200)
3536 /* 7.2 did not have OIDs in pg_attrdef */
3537 appendPQExpBuffer(q, "SELECT tableoid, 0 as oid, adnum, "
3538 "pg_get_expr(adbin, adrelid) AS adsrc "
3540 "WHERE adrelid = '%u'::oid",
3541 tbinfo->dobj.catId.oid);
3543 else if (g_fout->remoteVersion >= 70100)
3545 /* no pg_get_expr, so must rely on adsrc */
3546 appendPQExpBuffer(q, "SELECT tableoid, oid, adnum, adsrc "
3548 "WHERE adrelid = '%u'::oid",
3549 tbinfo->dobj.catId.oid);
3553 /* no pg_get_expr, no tableoid either */
3554 appendPQExpBuffer(q, "SELECT "
3555 "(SELECT oid FROM pg_class WHERE relname = 'pg_attrdef') AS tableoid, "
3556 "oid, adnum, adsrc "
3558 "WHERE adrelid = '%u'::oid",
3559 tbinfo->dobj.catId.oid);
3561 res = PQexec(g_conn, q->data);
3562 check_sql_result(res, g_conn, q->data, PGRES_TUPLES_OK);
3564 numDefaults = PQntuples(res);
3565 attrdefs = (AttrDefInfo *) malloc(numDefaults * sizeof(AttrDefInfo));
3567 for (j = 0; j < numDefaults; j++)
3571 attrdefs[j].dobj.objType = DO_ATTRDEF;
3572 attrdefs[j].dobj.catId.tableoid = atooid(PQgetvalue(res, j, 0));
3573 attrdefs[j].dobj.catId.oid = atooid(PQgetvalue(res, j, 1));
3574 AssignDumpId(&attrdefs[j].dobj);
3575 attrdefs[j].adtable = tbinfo;
3576 attrdefs[j].adnum = adnum = atoi(PQgetvalue(res, j, 2));
3577 attrdefs[j].adef_expr = strdup(PQgetvalue(res, j, 3));
3580 * Defaults on a VIEW must always be dumped as separate
3581 * ALTER TABLE commands. Defaults on regular tables are
3582 * dumped as part of the CREATE TABLE if possible. To check
3583 * if it's safe, we mark the default as needing to appear
3584 * before the CREATE.
3586 if (tbinfo->relkind == RELKIND_VIEW)
3588 attrdefs[j].separate = true;
3589 /* needed in case pre-7.3 DB: */
3590 addObjectDependency(&attrdefs[j].dobj,
3591 tbinfo->dobj.dumpId);
3595 attrdefs[j].separate = false;
3596 addObjectDependency(&tbinfo->dobj,
3597 attrdefs[j].dobj.dumpId);
3600 if (adnum <= 0 || adnum > ntups)
3602 write_msg(NULL, "invalid adnum value %d for table \"%s\"\n",
3603 adnum, tbinfo->relname);
3606 tbinfo->attrdefs[adnum - 1] = &attrdefs[j];
3612 * Get info about table CHECK constraints
3614 if (tbinfo->ncheck > 0)
3616 ConstraintInfo *constrs;
3620 write_msg(NULL, "finding check constraints for table \"%s\"\n",
3623 resetPQExpBuffer(q);
3624 if (g_fout->remoteVersion >= 70400)
3626 appendPQExpBuffer(q, "SELECT tableoid, oid, conname, "
3627 "pg_catalog.pg_get_constraintdef(oid) AS consrc "
3628 "FROM pg_catalog.pg_constraint "
3629 "WHERE conrelid = '%u'::pg_catalog.oid "
3630 " AND contype = 'c' "
3632 tbinfo->dobj.catId.oid);
3634 else if (g_fout->remoteVersion >= 70300)
3636 /* no pg_get_constraintdef, must use consrc */
3637 appendPQExpBuffer(q, "SELECT tableoid, oid, conname, "
3638 "'CHECK (' || consrc || ')' AS consrc "
3639 "FROM pg_catalog.pg_constraint "
3640 "WHERE conrelid = '%u'::pg_catalog.oid "
3641 " AND contype = 'c' "
3643 tbinfo->dobj.catId.oid);
3645 else if (g_fout->remoteVersion >= 70200)
3647 /* 7.2 did not have OIDs in pg_relcheck */
3648 appendPQExpBuffer(q, "SELECT tableoid, 0 as oid, "
3649 "rcname AS conname, "
3650 "'CHECK (' || rcsrc || ')' AS consrc "
3652 "WHERE rcrelid = '%u'::oid "
3654 tbinfo->dobj.catId.oid);
3656 else if (g_fout->remoteVersion >= 70100)
3658 appendPQExpBuffer(q, "SELECT tableoid, oid, "
3659 "rcname AS conname, "
3660 "'CHECK (' || rcsrc || ')' AS consrc "
3662 "WHERE rcrelid = '%u'::oid "
3664 tbinfo->dobj.catId.oid);
3668 /* no tableoid in 7.0 */
3669 appendPQExpBuffer(q, "SELECT "
3670 "(SELECT oid FROM pg_class WHERE relname = 'pg_relcheck') AS tableoid, "
3671 "oid, rcname AS conname, "
3672 "'CHECK (' || rcsrc || ')' AS consrc "
3674 "WHERE rcrelid = '%u'::oid "
3676 tbinfo->dobj.catId.oid);
3678 res = PQexec(g_conn, q->data);
3679 check_sql_result(res, g_conn, q->data, PGRES_TUPLES_OK);
3681 numConstrs = PQntuples(res);
3682 if (numConstrs != tbinfo->ncheck)
3684 write_msg(NULL, "expected %d check constraints on table \"%s\" but found %d\n",
3685 tbinfo->ncheck, tbinfo->relname, numConstrs);
3686 write_msg(NULL, "(The system catalogs might be corrupted.)\n");
3690 constrs = (ConstraintInfo *) malloc(numConstrs * sizeof(ConstraintInfo));
3691 tbinfo->checkexprs = constrs;
3693 for (j = 0; j < numConstrs; j++)
3695 constrs[j].dobj.objType = DO_CONSTRAINT;
3696 constrs[j].dobj.catId.tableoid = atooid(PQgetvalue(res, j, 0));
3697 constrs[j].dobj.catId.oid = atooid(PQgetvalue(res, j, 1));
3698 AssignDumpId(&constrs[j].dobj);
3699 constrs[j].contable = tbinfo;
3700 constrs[j].condomain = NULL;
3701 constrs[j].contype = 'c';
3702 constrs[j].conname = strdup(PQgetvalue(res, j, 2));
3703 constrs[j].condef = strdup(PQgetvalue(res, j, 3));
3704 constrs[j].conindex = 0;
3705 constrs[j].coninherited = false;
3706 constrs[j].separate = false;
3707 addObjectDependency(&tbinfo->dobj,
3708 constrs[j].dobj.dumpId);
3710 * If the constraint is inherited, this will be detected
3711 * later. We also detect later if the constraint must be
3712 * split out from the table definition.
3719 * Check to see if any columns are serial columns. Our first
3720 * quick filter is that it must be integer or bigint with a
3721 * default. If so, we scan to see if we found a sequence linked
3722 * to this column. If we did, mark the column and sequence
3725 for (j = 0; j < ntups; j++)
3728 * Note assumption that format_type will show these types as
3729 * exactly "integer" and "bigint" regardless of schema path.
3730 * This is correct in 7.3 but needs to be watched.
3732 if (strcmp(tbinfo->atttypnames[j], "integer") != 0 &&
3733 strcmp(tbinfo->atttypnames[j], "bigint") != 0)
3735 if (tbinfo->attrdefs[j] == NULL)
3737 for (k = 0; k < numTables; k++)
3739 TableInfo *seqinfo = &tblinfo[k];
3741 if (OidIsValid(seqinfo->owning_tab) &&
3742 seqinfo->owning_tab == tbinfo->dobj.catId.oid &&
3743 seqinfo->owning_col == j + 1)
3746 * Found a match. Copy the table's interesting and
3747 * dumpable flags to the sequence.
3749 tbinfo->attisserial[j] = true;
3750 seqinfo->interesting = tbinfo->interesting;
3751 seqinfo->dump = tbinfo->dump;
3758 destroyPQExpBuffer(q);
3765 * This routine is used to dump any comments associated with the
3766 * object handed to this routine. The routine takes a constant character
3767 * string for the target part of the comment-creation command, plus
3768 * the namespace and owner of the object (for labeling the ArchiveEntry),
3769 * plus catalog ID and subid which are the lookup key for pg_description,
3770 * plus the dump ID for the object (for setting a dependency).
3771 * If a matching pg_description entry is found, it is dumped.
3774 dumpComment(Archive *fout, const char *target,
3775 const char *namespace, const char *owner,
3776 CatalogId catalogId, int subid, DumpId dumpId)
3782 /* Comments are SCHEMA not data */
3787 * Note we do NOT change source schema here; preserve the caller's
3791 /* Build query to find comment */
3793 query = createPQExpBuffer();
3795 if (fout->remoteVersion >= 70300)
3797 appendPQExpBuffer(query,
3798 "SELECT description FROM pg_catalog.pg_description "
3799 "WHERE classoid = '%u'::pg_catalog.oid and "
3800 "objoid = '%u'::pg_catalog.oid and objsubid = %d",
3801 catalogId.tableoid, catalogId.oid, subid);
3803 else if (fout->remoteVersion >= 70200)
3805 appendPQExpBuffer(query,
3806 "SELECT description FROM pg_description "
3807 "WHERE classoid = '%u'::oid and "
3808 "objoid = '%u'::oid and objsubid = %d",
3809 catalogId.tableoid, catalogId.oid, subid);
3813 /* Note: this will fail to find attribute comments in pre-7.2... */
3814 appendPQExpBuffer(query, "SELECT description FROM pg_description WHERE objoid = '%u'::oid", catalogId.oid);
3819 res = PQexec(g_conn, query->data);
3820 check_sql_result(res, g_conn, query->data, PGRES_TUPLES_OK);
3822 /* If a comment exists, build COMMENT ON statement */
3824 if (PQntuples(res) == 1)
3826 i_description = PQfnumber(res, "description");
3827 resetPQExpBuffer(query);
3828 appendPQExpBuffer(query, "COMMENT ON %s IS ", target);
3829 appendStringLiteral(query, PQgetvalue(res, 0, i_description), false);
3830 appendPQExpBuffer(query, ";\n");
3832 ArchiveEntry(fout, nilCatalogId, createDumpId(),
3833 target, namespace, owner,
3834 "COMMENT", query->data, "", NULL,
3840 destroyPQExpBuffer(query);
3844 * dumpTableComment --
3846 * As above, but dump comments for both the specified table (or view)
3847 * and its columns. For speed, we want to do this with only one query.
3850 dumpTableComment(Archive *fout, TableInfo *tbinfo,
3851 const char *reltypename)
3861 /* Comments are SCHEMA not data */
3866 * Note we do NOT change source schema here; preserve the caller's
3870 /* Build query to find comments */
3872 query = createPQExpBuffer();
3873 target = createPQExpBuffer();
3875 if (fout->remoteVersion >= 70300)
3877 appendPQExpBuffer(query, "SELECT description, objsubid FROM pg_catalog.pg_description "
3878 "WHERE classoid = '%u'::pg_catalog.oid and "
3879 "objoid = '%u'::pg_catalog.oid "
3880 "ORDER BY objoid, classoid, objsubid",
3881 tbinfo->dobj.catId.tableoid, tbinfo->dobj.catId.oid);
3883 else if (fout->remoteVersion >= 70200)
3885 appendPQExpBuffer(query, "SELECT description, objsubid FROM pg_description "
3886 "WHERE classoid = '%u'::oid and "
3887 "objoid = '%u'::oid "
3888 "ORDER BY objoid, classoid, objsubid",
3889 tbinfo->dobj.catId.tableoid, tbinfo->dobj.catId.oid);
3893 /* Note: this will fail to find attribute comments in pre-7.2... */
3894 appendPQExpBuffer(query, "SELECT description, 0 as objsubid FROM pg_description WHERE objoid = '%u'::oid",
3895 tbinfo->dobj.catId.oid);
3900 res = PQexec(g_conn, query->data);
3901 check_sql_result(res, g_conn, query->data, PGRES_TUPLES_OK);
3903 i_description = PQfnumber(res, "description");
3904 i_objsubid = PQfnumber(res, "objsubid");
3906 /* If comments exist, build COMMENT ON statements */
3908 ntups = PQntuples(res);
3909 for (i = 0; i < ntups; i++)
3911 const char *descr = PQgetvalue(res, i, i_description);
3912 int objsubid = atoi(PQgetvalue(res, i, i_objsubid));
3916 resetPQExpBuffer(target);
3917 appendPQExpBuffer(target, "%s %s", reltypename,
3918 fmtId(tbinfo->relname));
3920 resetPQExpBuffer(query);
3921 appendPQExpBuffer(query, "COMMENT ON %s IS ", target->data);
3922 appendStringLiteral(query, descr, false);
3923 appendPQExpBuffer(query, ";\n");
3925 ArchiveEntry(fout, nilCatalogId, createDumpId(),
3927 tbinfo->relnamespace->nspname, tbinfo->usename,
3928 "COMMENT", query->data, "", NULL,
3929 &(tbinfo->dobj.dumpId), 1,
3932 else if (objsubid > 0 && objsubid <= tbinfo->numatts)
3934 resetPQExpBuffer(target);
3935 appendPQExpBuffer(target, "COLUMN %s.",
3936 fmtId(tbinfo->relname));
3937 appendPQExpBuffer(target, "%s",
3938 fmtId(tbinfo->attnames[objsubid - 1]));
3940 resetPQExpBuffer(query);
3941 appendPQExpBuffer(query, "COMMENT ON %s IS ", target->data);
3942 appendStringLiteral(query, descr, false);
3943 appendPQExpBuffer(query, ";\n");
3945 ArchiveEntry(fout, nilCatalogId, createDumpId(),
3947 tbinfo->relnamespace->nspname, tbinfo->usename,
3948 "COMMENT", query->data, "", NULL,
3949 &(tbinfo->dobj.dumpId), 1,
3955 destroyPQExpBuffer(query);
3956 destroyPQExpBuffer(target);
3960 * dumpDumpableObject
3962 * This routine and its subsidiaries are responsible for creating
3963 * ArchiveEntries (TOC objects) for each object to be dumped.
3966 dumpDumpableObject(Archive *fout, DumpableObject *dobj)
3968 switch (dobj->objType)
3971 dumpNamespace(fout, (NamespaceInfo *) dobj);
3974 dumpType(fout, (TypeInfo *) dobj);
3977 dumpFunc(fout, (FuncInfo *) dobj);
3980 dumpAgg(fout, (AggInfo *) dobj);
3983 dumpOpr(fout, (OprInfo *) dobj);
3986 dumpOpclass(fout, (OpclassInfo *) dobj);
3989 dumpConversion(fout, (ConvInfo *) dobj);
3992 dumpTable(fout, (TableInfo *) dobj);
3995 dumpAttrDef(fout, (AttrDefInfo *) dobj);
3998 dumpIndex(fout, (IndxInfo *) dobj);
4001 dumpRule(fout, (RuleInfo *) dobj);
4004 dumpTrigger(fout, (TriggerInfo *) dobj);
4007 dumpConstraint(fout, (ConstraintInfo *) dobj);
4009 case DO_FK_CONSTRAINT:
4010 dumpConstraint(fout, (ConstraintInfo *) dobj);
4013 dumpProcLang(fout, (ProcLangInfo *) dobj);
4016 dumpCast(fout, (CastInfo *) dobj);
4019 dumpTableData(fout, (TableDataInfo *) dobj);
4026 * writes out to fout the queries to recreate a user-defined namespace
4029 dumpNamespace(Archive *fout, NamespaceInfo *nspinfo)
4035 /* skip if not to be dumped */
4036 if (!nspinfo->dump || dataOnly)
4039 /* don't dump dummy namespace from pre-7.3 source */
4040 if (strlen(nspinfo->nspname) == 0)
4043 q = createPQExpBuffer();
4044 delq = createPQExpBuffer();
4046 qnspname = strdup(fmtId(nspinfo->nspname));
4049 * If it's the PUBLIC namespace, suppress the CREATE SCHEMA record
4050 * for it, since we expect PUBLIC to exist already in the
4051 * destination database. But do emit ACL in case it's not standard,
4054 * Note that ownership is shown in the AUTHORIZATION clause,
4055 * while the archive entry is listed with empty owner (causing
4056 * it to be emitted with SET SESSION AUTHORIZATION DEFAULT).
4057 * This seems the best way of dealing with schemas owned by
4058 * users without CREATE SCHEMA privilege. Further hacking has
4059 * to be applied for --no-owner mode, though!
4061 if (strcmp(nspinfo->nspname, "public") != 0)
4063 appendPQExpBuffer(delq, "DROP SCHEMA %s;\n", qnspname);
4065 appendPQExpBuffer(q, "CREATE SCHEMA %s AUTHORIZATION %s;\n",
4066 qnspname, fmtId(nspinfo->usename));
4068 ArchiveEntry(fout, nspinfo->dobj.catId, nspinfo->dobj.dumpId,
4071 "SCHEMA", q->data, delq->data, NULL,
4072 nspinfo->dobj.dependencies, nspinfo->dobj.nDeps,
4076 /* Dump Schema Comments */
4077 resetPQExpBuffer(q);
4078 appendPQExpBuffer(q, "SCHEMA %s", qnspname);
4079 dumpComment(fout, q->data,
4080 NULL, nspinfo->usename,
4081 nspinfo->dobj.catId, 0, nspinfo->dobj.dumpId);
4083 dumpACL(fout, nspinfo->dobj.catId, nspinfo->dobj.dumpId, "SCHEMA",
4084 qnspname, nspinfo->nspname, NULL,
4085 nspinfo->usename, nspinfo->nspacl);
4089 destroyPQExpBuffer(q);
4090 destroyPQExpBuffer(delq);
4095 * writes out to fout the queries to recreate a user-defined type
4098 dumpType(Archive *fout, TypeInfo *tinfo)
4100 /* Dump only types in dumpable namespaces */
4101 if (!tinfo->typnamespace->dump || dataOnly)
4104 /* skip complex types, except for standalone composite types */
4105 if (OidIsValid(tinfo->typrelid) && tinfo->typrelkind != 'c')
4108 /* skip undefined placeholder types */
4109 if (!tinfo->isDefined)
4112 /* skip all array types that start w/ underscore */
4113 if ((tinfo->typname[0] == '_') &&
4114 OidIsValid(tinfo->typelem))
4117 /* Dump out in proper style */
4118 if (tinfo->typtype == 'b')
4119 dumpBaseType(fout, tinfo);
4120 else if (tinfo->typtype == 'd')
4121 dumpDomain(fout, tinfo);
4122 else if (tinfo->typtype == 'c')
4123 dumpCompositeType(fout, tinfo);
4128 * writes out to fout the queries to recreate a user-defined base type
4131 dumpBaseType(Archive *fout, TypeInfo *tinfo)
4133 PQExpBuffer q = createPQExpBuffer();
4134 PQExpBuffer delq = createPQExpBuffer();
4135 PQExpBuffer query = createPQExpBuffer();
4153 /* Set proper schema search path so regproc references list correctly */
4154 selectSourceSchema(tinfo->typnamespace->nspname);
4156 /* Fetch type-specific details */
4157 if (fout->remoteVersion >= 70400)
4159 appendPQExpBuffer(query, "SELECT typlen, "
4160 "typinput, typoutput, typreceive, typsend, "
4161 "typinput::pg_catalog.oid as typinputoid, "
4162 "typoutput::pg_catalog.oid as typoutputoid, "
4163 "typreceive::pg_catalog.oid as typreceiveoid, "
4164 "typsend::pg_catalog.oid as typsendoid, "
4165 "typdelim, typdefault, typbyval, typalign, "
4167 "FROM pg_catalog.pg_type "
4168 "WHERE oid = '%u'::pg_catalog.oid",
4169 tinfo->dobj.catId.oid);
4171 else if (fout->remoteVersion >= 70300)
4173 appendPQExpBuffer(query, "SELECT typlen, "
4174 "typinput, typoutput, "
4175 "'-' as typreceive, '-' as typsend, "
4176 "typinput::pg_catalog.oid as typinputoid, "
4177 "typoutput::pg_catalog.oid as typoutputoid, "
4178 "0 as typreceiveoid, 0 as typsendoid, "
4179 "typdelim, typdefault, typbyval, typalign, "
4181 "FROM pg_catalog.pg_type "
4182 "WHERE oid = '%u'::pg_catalog.oid",
4183 tinfo->dobj.catId.oid);
4185 else if (fout->remoteVersion >= 70100)
4188 * Note: although pre-7.3 catalogs contain typreceive and typsend,
4189 * ignore them because they are not right.
4191 appendPQExpBuffer(query, "SELECT typlen, "
4192 "typinput, typoutput, "
4193 "'-' as typreceive, '-' as typsend, "
4194 "typinput::oid as typinputoid, "
4195 "typoutput::oid as typoutputoid, "
4196 "0 as typreceiveoid, 0 as typsendoid, "
4197 "typdelim, typdefault, typbyval, typalign, "
4200 "WHERE oid = '%u'::oid",
4201 tinfo->dobj.catId.oid);
4205 appendPQExpBuffer(query, "SELECT typlen, "
4206 "typinput, typoutput, "
4207 "'-' as typreceive, '-' as typsend, "
4208 "typinput::oid as typinputoid, "
4209 "typoutput::oid as typoutputoid, "
4210 "0 as typreceiveoid, 0 as typsendoid, "
4211 "typdelim, typdefault, typbyval, typalign, "
4212 "'p'::char as typstorage "
4214 "WHERE oid = '%u'::oid",
4215 tinfo->dobj.catId.oid);
4218 res = PQexec(g_conn, query->data);
4219 check_sql_result(res, g_conn, query->data, PGRES_TUPLES_OK);
4221 /* Expecting a single result only */
4222 ntups = PQntuples(res);
4225 write_msg(NULL, "Got %d rows instead of one from: %s",
4226 ntups, query->data);
4230 typlen = PQgetvalue(res, 0, PQfnumber(res, "typlen"));
4231 typinput = PQgetvalue(res, 0, PQfnumber(res, "typinput"));
4232 typoutput = PQgetvalue(res, 0, PQfnumber(res, "typoutput"));
4233 typreceive = PQgetvalue(res, 0, PQfnumber(res, "typreceive"));
4234 typsend = PQgetvalue(res, 0, PQfnumber(res, "typsend"));
4235 typinputoid = atooid(PQgetvalue(res, 0, PQfnumber(res, "typinputoid")));
4236 typoutputoid = atooid(PQgetvalue(res, 0, PQfnumber(res, "typoutputoid")));
4237 typreceiveoid = atooid(PQgetvalue(res, 0, PQfnumber(res, "typreceiveoid")));
4238 typsendoid = atooid(PQgetvalue(res, 0, PQfnumber(res, "typsendoid")));
4239 typdelim = PQgetvalue(res, 0, PQfnumber(res, "typdelim"));
4240 if (PQgetisnull(res, 0, PQfnumber(res, "typdefault")))
4243 typdefault = PQgetvalue(res, 0, PQfnumber(res, "typdefault"));
4244 typbyval = PQgetvalue(res, 0, PQfnumber(res, "typbyval"));
4245 typalign = PQgetvalue(res, 0, PQfnumber(res, "typalign"));
4246 typstorage = PQgetvalue(res, 0, PQfnumber(res, "typstorage"));
4249 * DROP must be fully qualified in case same name appears in
4252 appendPQExpBuffer(delq, "DROP TYPE %s.",
4253 fmtId(tinfo->typnamespace->nspname));
4254 appendPQExpBuffer(delq, "%s CASCADE;\n",
4255 fmtId(tinfo->typname));
4257 appendPQExpBuffer(q,
4258 "CREATE TYPE %s (\n"
4259 " INTERNALLENGTH = %s",
4260 fmtId(tinfo->typname),
4261 (strcmp(typlen, "-1") == 0) ? "variable" : typlen);
4263 if (fout->remoteVersion >= 70300)
4265 /* regproc result is correctly quoted in 7.3 */
4266 appendPQExpBuffer(q, ",\n INPUT = %s", typinput);
4267 appendPQExpBuffer(q, ",\n OUTPUT = %s", typoutput);
4268 if (OidIsValid(typreceiveoid))
4269 appendPQExpBuffer(q, ",\n RECEIVE = %s", typreceive);
4270 if (OidIsValid(typsendoid))
4271 appendPQExpBuffer(q, ",\n SEND = %s", typsend);
4275 /* regproc delivers an unquoted name before 7.3 */
4276 /* cannot combine these because fmtId uses static result area */
4277 appendPQExpBuffer(q, ",\n INPUT = %s", fmtId(typinput));
4278 appendPQExpBuffer(q, ",\n OUTPUT = %s", fmtId(typoutput));
4279 /* no chance that receive/send need be printed */
4282 if (typdefault != NULL)
4284 appendPQExpBuffer(q, ",\n DEFAULT = ");
4285 appendStringLiteral(q, typdefault, true);
4292 /* reselect schema in case changed by function dump */
4293 selectSourceSchema(tinfo->typnamespace->nspname);
4294 elemType = getFormattedTypeName(tinfo->typelem, zeroAsOpaque);
4295 appendPQExpBuffer(q, ",\n ELEMENT = %s", elemType);
4299 if (typdelim && strcmp(typdelim, ",") != 0)
4301 appendPQExpBuffer(q, ",\n DELIMITER = ");
4302 appendStringLiteral(q, typdelim, true);
4305 if (strcmp(typalign, "c") == 0)
4306 appendPQExpBuffer(q, ",\n ALIGNMENT = char");
4307 else if (strcmp(typalign, "s") == 0)
4308 appendPQExpBuffer(q, ",\n ALIGNMENT = int2");
4309 else if (strcmp(typalign, "i") == 0)
4310 appendPQExpBuffer(q, ",\n ALIGNMENT = int4");
4311 else if (strcmp(typalign, "d") == 0)
4312 appendPQExpBuffer(q, ",\n ALIGNMENT = double");
4314 if (strcmp(typstorage, "p") == 0)
4315 appendPQExpBuffer(q, ",\n STORAGE = plain");
4316 else if (strcmp(typstorage, "e") == 0)
4317 appendPQExpBuffer(q, ",\n STORAGE = external");
4318 else if (strcmp(typstorage, "x") == 0)
4319 appendPQExpBuffer(q, ",\n STORAGE = extended");
4320 else if (strcmp(typstorage, "m") == 0)
4321 appendPQExpBuffer(q, ",\n STORAGE = main");
4323 if (strcmp(typbyval, "t") == 0)
4324 appendPQExpBuffer(q, ",\n PASSEDBYVALUE");
4326 appendPQExpBuffer(q, "\n);\n");
4328 ArchiveEntry(fout, tinfo->dobj.catId, tinfo->dobj.dumpId,
4330 tinfo->typnamespace->nspname,
4332 "TYPE", q->data, delq->data, NULL,
4333 tinfo->dobj.dependencies, tinfo->dobj.nDeps,
4336 /* Dump Type Comments */
4337 resetPQExpBuffer(q);
4339 appendPQExpBuffer(q, "TYPE %s", fmtId(tinfo->typname));
4340 dumpComment(fout, q->data,
4341 tinfo->typnamespace->nspname, tinfo->usename,
4342 tinfo->dobj.catId, 0, tinfo->dobj.dumpId);
4345 destroyPQExpBuffer(q);
4346 destroyPQExpBuffer(delq);
4347 destroyPQExpBuffer(query);
4352 * writes out to fout the queries to recreate a user-defined domain
4355 dumpDomain(Archive *fout, TypeInfo *tinfo)
4357 PQExpBuffer q = createPQExpBuffer();
4358 PQExpBuffer delq = createPQExpBuffer();
4359 PQExpBuffer query = createPQExpBuffer();
4367 /* Set proper schema search path so type references list correctly */
4368 selectSourceSchema(tinfo->typnamespace->nspname);
4370 /* Fetch domain specific details */
4371 /* We assume here that remoteVersion must be at least 70300 */
4372 appendPQExpBuffer(query, "SELECT typnotnull, "
4373 "pg_catalog.format_type(typbasetype, typtypmod) as typdefn, "
4375 "FROM pg_catalog.pg_type "
4376 "WHERE oid = '%u'::pg_catalog.oid",
4377 tinfo->dobj.catId.oid);
4379 res = PQexec(g_conn, query->data);
4380 check_sql_result(res, g_conn, query->data, PGRES_TUPLES_OK);
4382 /* Expecting a single result only */
4383 ntups = PQntuples(res);
4386 write_msg(NULL, "Got %d rows instead of one from: %s",
4387 ntups, query->data);
4391 typnotnull = PQgetvalue(res, 0, PQfnumber(res, "typnotnull"));
4392 typdefn = PQgetvalue(res, 0, PQfnumber(res, "typdefn"));
4393 if (PQgetisnull(res, 0, PQfnumber(res, "typdefault")))
4396 typdefault = PQgetvalue(res, 0, PQfnumber(res, "typdefault"));
4398 appendPQExpBuffer(q,
4399 "CREATE DOMAIN %s AS %s",
4400 fmtId(tinfo->typname),
4403 if (typnotnull[0] == 't')
4404 appendPQExpBuffer(q, " NOT NULL");
4407 appendPQExpBuffer(q, " DEFAULT %s", typdefault);
4412 * Add any CHECK constraints for the domain
4414 for (i = 0; i < tinfo->nDomChecks; i++)
4416 ConstraintInfo *domcheck = &(tinfo->domChecks[i]);
4418 if (!domcheck->separate)
4419 appendPQExpBuffer(q, "\n\tCONSTRAINT %s %s",
4420 fmtId(domcheck->conname), domcheck->condef);
4423 appendPQExpBuffer(q, ";\n");
4426 * DROP must be fully qualified in case same name appears in
4429 appendPQExpBuffer(delq, "DROP DOMAIN %s.",
4430 fmtId(tinfo->typnamespace->nspname));
4431 appendPQExpBuffer(delq, "%s;\n",
4432 fmtId(tinfo->typname));
4434 ArchiveEntry(fout, tinfo->dobj.catId, tinfo->dobj.dumpId,
4436 tinfo->typnamespace->nspname,
4438 "DOMAIN", q->data, delq->data, NULL,
4439 tinfo->dobj.dependencies, tinfo->dobj.nDeps,
4442 /* Dump Domain Comments */
4443 resetPQExpBuffer(q);
4445 appendPQExpBuffer(q, "DOMAIN %s", fmtId(tinfo->typname));
4446 dumpComment(fout, q->data,
4447 tinfo->typnamespace->nspname, tinfo->usename,
4448 tinfo->dobj.catId, 0, tinfo->dobj.dumpId);
4450 destroyPQExpBuffer(q);
4451 destroyPQExpBuffer(delq);
4452 destroyPQExpBuffer(query);
4457 * writes out to fout the queries to recreate a user-defined stand-alone
4461 dumpCompositeType(Archive *fout, TypeInfo *tinfo)
4463 PQExpBuffer q = createPQExpBuffer();
4464 PQExpBuffer delq = createPQExpBuffer();
4465 PQExpBuffer query = createPQExpBuffer();
4472 /* Set proper schema search path so type references list correctly */
4473 selectSourceSchema(tinfo->typnamespace->nspname);
4475 /* Fetch type specific details */
4476 /* We assume here that remoteVersion must be at least 70300 */
4478 appendPQExpBuffer(query, "SELECT a.attname, "
4479 "pg_catalog.format_type(a.atttypid, a.atttypmod) as atttypdefn "
4480 "FROM pg_catalog.pg_type t, pg_catalog.pg_attribute a "
4481 "WHERE t.oid = '%u'::pg_catalog.oid "
4482 "AND a.attrelid = t.typrelid "
4483 "AND NOT a.attisdropped "
4484 "ORDER BY a.attnum ",
4485 tinfo->dobj.catId.oid);
4487 res = PQexec(g_conn, query->data);
4488 check_sql_result(res, g_conn, query->data, PGRES_TUPLES_OK);
4490 /* Expecting at least a single result */
4491 ntups = PQntuples(res);
4494 write_msg(NULL, "query yielded no rows: %s\n", query->data);
4498 i_attname = PQfnumber(res, "attname");
4499 i_atttypdefn = PQfnumber(res, "atttypdefn");
4501 appendPQExpBuffer(q, "CREATE TYPE %s AS (",
4502 fmtId(tinfo->typname));
4504 for (i = 0; i < ntups; i++)
4509 attname = PQgetvalue(res, i, i_attname);
4510 atttypdefn = PQgetvalue(res, i, i_atttypdefn);
4512 appendPQExpBuffer(q, "\n\t%s %s", fmtId(attname), atttypdefn);
4514 appendPQExpBuffer(q, ",");
4516 appendPQExpBuffer(q, "\n);\n");
4519 * DROP must be fully qualified in case same name appears in
4522 appendPQExpBuffer(delq, "DROP TYPE %s.",
4523 fmtId(tinfo->typnamespace->nspname));
4524 appendPQExpBuffer(delq, "%s;\n",
4525 fmtId(tinfo->typname));
4527 ArchiveEntry(fout, tinfo->dobj.catId, tinfo->dobj.dumpId,
4529 tinfo->typnamespace->nspname,
4531 "TYPE", q->data, delq->data, NULL,
4532 tinfo->dobj.dependencies, tinfo->dobj.nDeps,
4536 /* Dump Type Comments */
4537 resetPQExpBuffer(q);
4539 appendPQExpBuffer(q, "TYPE %s", fmtId(tinfo->typname));
4540 dumpComment(fout, q->data,
4541 tinfo->typnamespace->nspname, tinfo->usename,
4542 tinfo->dobj.catId, 0, tinfo->dobj.dumpId);
4545 destroyPQExpBuffer(q);
4546 destroyPQExpBuffer(delq);
4547 destroyPQExpBuffer(query);
4552 * writes out to fout the queries to recreate a user-defined
4553 * procedural language
4556 dumpProcLang(Archive *fout, ProcLangInfo *plang)
4562 FuncInfo *validatorInfo = NULL;
4568 * Current theory is to dump PLs iff their underlying functions
4569 * will be dumped (are in a dumpable namespace, or have a
4570 * non-system OID in pre-7.3 databases). Actually, we treat the
4571 * PL itself as being in the underlying function's namespace,
4572 * though it isn't really. This avoids searchpath problems for
4573 * the HANDLER clause.
4575 * If the underlying function is in the pg_catalog namespace,
4576 * we won't have loaded it into finfo[] at all; therefore,
4577 * treat failure to find it in finfo[] as indicating we shouldn't
4578 * dump it, not as an error condition. Ditto for the validator.
4581 funcInfo = findFuncByOid(plang->lanplcallfoid);
4582 if (funcInfo == NULL)
4585 if (!funcInfo->pronamespace->dump)
4588 if (OidIsValid(plang->lanvalidator))
4590 validatorInfo = findFuncByOid(plang->lanvalidator);
4591 if (validatorInfo == NULL)
4595 defqry = createPQExpBuffer();
4596 delqry = createPQExpBuffer();
4598 qlanname = strdup(fmtId(plang->lanname));
4600 appendPQExpBuffer(delqry, "DROP PROCEDURAL LANGUAGE %s;\n",
4603 appendPQExpBuffer(defqry, "CREATE %sPROCEDURAL LANGUAGE %s",
4604 plang->lanpltrusted ? "TRUSTED " : "",
4606 appendPQExpBuffer(defqry, " HANDLER %s",
4607 fmtId(funcInfo->proname));
4608 if (OidIsValid(plang->lanvalidator))
4610 appendPQExpBuffer(defqry, " VALIDATOR ");
4611 /* Cope with possibility that validator is in different schema */
4612 if (validatorInfo->pronamespace != funcInfo->pronamespace)
4613 appendPQExpBuffer(defqry, "%s.",
4614 fmtId(validatorInfo->pronamespace->nspname));
4615 appendPQExpBuffer(defqry, "%s",
4616 fmtId(validatorInfo->proname));
4618 appendPQExpBuffer(defqry, ";\n");
4620 ArchiveEntry(fout, plang->dobj.catId, plang->dobj.dumpId,
4622 funcInfo->pronamespace->nspname, "",
4623 "PROCEDURAL LANGUAGE",
4624 defqry->data, delqry->data, NULL,
4625 plang->dobj.dependencies, plang->dobj.nDeps,
4628 /* Dump Proc Lang Comments */
4629 resetPQExpBuffer(defqry);
4631 appendPQExpBuffer(defqry, "LANGUAGE %s", qlanname);
4632 dumpComment(fout, defqry->data,
4634 plang->dobj.catId, 0, plang->dobj.dumpId);
4636 if (plang->lanpltrusted)
4637 dumpACL(fout, plang->dobj.catId, plang->dobj.dumpId, "LANGUAGE",
4638 qlanname, plang->lanname,
4639 funcInfo->pronamespace->nspname,
4640 NULL, plang->lanacl);
4644 destroyPQExpBuffer(defqry);
4645 destroyPQExpBuffer(delqry);
4649 * format_function_signature: generate function name and argument list
4651 * The argument type names are qualified if needed. The function name
4652 * is never qualified.
4655 format_function_signature(FuncInfo *finfo, bool honor_quotes)
4660 initPQExpBuffer(&fn);
4662 appendPQExpBuffer(&fn, "%s(", fmtId(finfo->proname));
4664 appendPQExpBuffer(&fn, "%s(", finfo->proname);
4665 for (j = 0; j < finfo->nargs; j++)
4669 typname = getFormattedTypeName(finfo->argtypes[j], zeroAsOpaque);
4670 appendPQExpBuffer(&fn, "%s%s",
4671 (j > 0) ? ", " : "",
4675 appendPQExpBuffer(&fn, ")");
4682 * dump out one function
4685 dumpFunc(Archive *fout, FuncInfo *finfo)
4704 /* Dump only funcs in dumpable namespaces */
4705 if (!finfo->pronamespace->dump || dataOnly)
4708 query = createPQExpBuffer();
4709 q = createPQExpBuffer();
4710 delqry = createPQExpBuffer();
4711 asPart = createPQExpBuffer();
4713 /* Set proper schema search path so type references list correctly */
4714 selectSourceSchema(finfo->pronamespace->nspname);
4716 /* Fetch function-specific details */
4717 if (g_fout->remoteVersion >= 70300)
4719 appendPQExpBuffer(query,
4720 "SELECT proretset, prosrc, probin, "
4721 "provolatile, proisstrict, prosecdef, "
4722 "(SELECT lanname FROM pg_catalog.pg_language WHERE oid = prolang) as lanname "
4723 "FROM pg_catalog.pg_proc "
4724 "WHERE oid = '%u'::pg_catalog.oid",
4725 finfo->dobj.catId.oid);
4727 else if (g_fout->remoteVersion >= 70100)
4729 appendPQExpBuffer(query,
4730 "SELECT proretset, prosrc, probin, "
4731 "case when proiscachable then 'i' else 'v' end as provolatile, "
4733 "'f'::boolean as prosecdef, "
4734 "(SELECT lanname FROM pg_language WHERE oid = prolang) as lanname "
4736 "WHERE oid = '%u'::oid",
4737 finfo->dobj.catId.oid);
4741 appendPQExpBuffer(query,
4742 "SELECT proretset, prosrc, probin, "
4743 "case when proiscachable then 'i' else 'v' end as provolatile, "
4744 "'f'::boolean as proisstrict, "
4745 "'f'::boolean as prosecdef, "
4746 "(SELECT lanname FROM pg_language WHERE oid = prolang) as lanname "
4748 "WHERE oid = '%u'::oid",
4749 finfo->dobj.catId.oid);
4752 res = PQexec(g_conn, query->data);
4753 check_sql_result(res, g_conn, query->data, PGRES_TUPLES_OK);
4755 /* Expecting a single result only */
4756 ntups = PQntuples(res);
4759 write_msg(NULL, "Got %d rows instead of one from: %s",
4760 ntups, query->data);
4764 proretset = PQgetvalue(res, 0, PQfnumber(res, "proretset"));
4765 prosrc = PQgetvalue(res, 0, PQfnumber(res, "prosrc"));
4766 probin = PQgetvalue(res, 0, PQfnumber(res, "probin"));
4767 provolatile = PQgetvalue(res, 0, PQfnumber(res, "provolatile"));
4768 proisstrict = PQgetvalue(res, 0, PQfnumber(res, "proisstrict"));
4769 prosecdef = PQgetvalue(res, 0, PQfnumber(res, "prosecdef"));
4770 lanname = PQgetvalue(res, 0, PQfnumber(res, "lanname"));
4773 * See backend/commands/define.c for details of how the 'AS' clause is
4776 if (strcmp(probin, "-") != 0)
4778 appendPQExpBuffer(asPart, "AS ");
4779 appendStringLiteral(asPart, probin, true);
4780 if (strcmp(prosrc, "-") != 0)
4782 appendPQExpBuffer(asPart, ", ");
4783 appendStringLiteral(asPart, prosrc, false);
4788 if (strcmp(prosrc, "-") != 0)
4790 appendPQExpBuffer(asPart, "AS ");
4791 appendStringLiteral(asPart, prosrc, false);
4795 funcsig = format_function_signature(finfo, true);
4796 funcsig_tag = format_function_signature(finfo, false);
4799 * DROP must be fully qualified in case same name appears in
4802 appendPQExpBuffer(delqry, "DROP FUNCTION %s.%s;\n",
4803 fmtId(finfo->pronamespace->nspname),
4806 rettypename = getFormattedTypeName(finfo->prorettype, zeroAsOpaque);
4808 appendPQExpBuffer(q, "CREATE FUNCTION %s ", funcsig);
4809 appendPQExpBuffer(q, "RETURNS %s%s\n %s\n LANGUAGE %s",
4810 (proretset[0] == 't') ? "SETOF " : "",
4817 if (provolatile[0] != PROVOLATILE_VOLATILE)
4819 if (provolatile[0] == PROVOLATILE_IMMUTABLE)
4820 appendPQExpBuffer(q, " IMMUTABLE");
4821 else if (provolatile[0] == PROVOLATILE_STABLE)
4822 appendPQExpBuffer(q, " STABLE");
4823 else if (provolatile[0] != PROVOLATILE_VOLATILE)
4825 write_msg(NULL, "unrecognized provolatile value for function \"%s\"\n",
4831 if (proisstrict[0] == 't')
4832 appendPQExpBuffer(q, " STRICT");
4834 if (prosecdef[0] == 't')
4835 appendPQExpBuffer(q, " SECURITY DEFINER");
4837 appendPQExpBuffer(q, ";\n");
4839 ArchiveEntry(fout, finfo->dobj.catId, finfo->dobj.dumpId,
4841 finfo->pronamespace->nspname,
4843 "FUNCTION", q->data, delqry->data, NULL,
4844 finfo->dobj.dependencies, finfo->dobj.nDeps,
4847 /* Dump Function Comments */
4848 resetPQExpBuffer(q);
4849 appendPQExpBuffer(q, "FUNCTION %s", funcsig);
4850 dumpComment(fout, q->data,
4851 finfo->pronamespace->nspname, finfo->usename,
4852 finfo->dobj.catId, 0, finfo->dobj.dumpId);
4854 dumpACL(fout, finfo->dobj.catId, finfo->dobj.dumpId, "FUNCTION",
4855 funcsig, funcsig_tag,
4856 finfo->pronamespace->nspname,
4857 finfo->usename, finfo->proacl);
4861 destroyPQExpBuffer(query);
4862 destroyPQExpBuffer(q);
4863 destroyPQExpBuffer(delqry);
4864 destroyPQExpBuffer(asPart);
4871 * Dump a user-defined cast
4874 dumpCast(Archive *fout, CastInfo *cast)
4878 PQExpBuffer castsig;
4879 FuncInfo *funcInfo = NULL;
4880 TypeInfo *sourceInfo;
4881 TypeInfo *targetInfo;
4886 if (OidIsValid(cast->castfunc))
4888 funcInfo = findFuncByOid(cast->castfunc);
4889 if (funcInfo == NULL)
4894 * As per discussion we dump casts if one or more of the underlying
4895 * objects (the conversion function and the two data types) are not
4896 * builtin AND if all of the non-builtin objects namespaces are
4897 * included in the dump. Builtin meaning, the namespace name does
4898 * not start with "pg_".
4900 sourceInfo = findTypeByOid(cast->castsource);
4901 targetInfo = findTypeByOid(cast->casttarget);
4903 if (sourceInfo == NULL || targetInfo == NULL)
4907 * Skip this cast if all objects are from pg_
4909 if ((funcInfo == NULL || strncmp(funcInfo->pronamespace->nspname, "pg_", 3) == 0) &&
4910 strncmp(sourceInfo->typnamespace->nspname, "pg_", 3) == 0 &&
4911 strncmp(targetInfo->typnamespace->nspname, "pg_", 3) == 0)
4915 * Skip cast if function isn't from pg_ and that namespace is
4919 strncmp(funcInfo->pronamespace->nspname, "pg_", 3) != 0 &&
4920 !funcInfo->pronamespace->dump)
4924 * Same for the Source type
4926 if (strncmp(sourceInfo->typnamespace->nspname, "pg_", 3) != 0 &&
4927 !sourceInfo->typnamespace->dump)
4931 * and the target type.
4933 if (strncmp(targetInfo->typnamespace->nspname, "pg_", 3) != 0 &&
4934 !targetInfo->typnamespace->dump)
4937 /* Make sure we are in proper schema (needed for getFormattedTypeName) */
4938 selectSourceSchema("pg_catalog");
4940 defqry = createPQExpBuffer();
4941 delqry = createPQExpBuffer();
4942 castsig = createPQExpBuffer();
4944 appendPQExpBuffer(delqry, "DROP CAST (%s AS %s);\n",
4945 getFormattedTypeName(cast->castsource, zeroAsNone),
4946 getFormattedTypeName(cast->casttarget, zeroAsNone));
4948 appendPQExpBuffer(defqry, "CREATE CAST (%s AS %s) ",
4949 getFormattedTypeName(cast->castsource, zeroAsNone),
4950 getFormattedTypeName(cast->casttarget, zeroAsNone));
4952 if (!OidIsValid(cast->castfunc))
4953 appendPQExpBuffer(defqry, "WITHOUT FUNCTION");
4955 appendPQExpBuffer(defqry, "WITH FUNCTION %s",
4956 format_function_signature(funcInfo, true));
4958 if (cast->castcontext == 'a')
4959 appendPQExpBuffer(defqry, " AS ASSIGNMENT");
4960 else if (cast->castcontext == 'i')
4961 appendPQExpBuffer(defqry, " AS IMPLICIT");
4962 appendPQExpBuffer(defqry, ";\n");
4964 appendPQExpBuffer(castsig, "CAST (%s AS %s)",
4965 getFormattedTypeName(cast->castsource, zeroAsNone),
4966 getFormattedTypeName(cast->casttarget, zeroAsNone));
4968 ArchiveEntry(fout, cast->dobj.catId, cast->dobj.dumpId,
4970 sourceInfo->typnamespace->nspname, "",
4971 "CAST", defqry->data, delqry->data, NULL,
4972 cast->dobj.dependencies, cast->dobj.nDeps,
4975 /* Dump Cast Comments */
4976 resetPQExpBuffer(defqry);
4977 appendPQExpBuffer(defqry, "CAST (%s AS %s)",
4978 getFormattedTypeName(cast->castsource, zeroAsNone),
4979 getFormattedTypeName(cast->casttarget, zeroAsNone));
4980 dumpComment(fout, defqry->data,
4982 cast->dobj.catId, 0, cast->dobj.dumpId);
4984 destroyPQExpBuffer(defqry);
4985 destroyPQExpBuffer(delqry);
4986 destroyPQExpBuffer(castsig);
4991 * write out a single operator definition
4994 dumpOpr(Archive *fout, OprInfo *oprinfo)
5000 PQExpBuffer details;
5031 /* Dump only operators in dumpable namespaces */
5032 if (!oprinfo->oprnamespace->dump || dataOnly)
5036 * some operators are invalid because they were the result of user
5037 * defining operators before commutators exist
5039 if (!OidIsValid(oprinfo->oprcode))
5042 query = createPQExpBuffer();
5043 q = createPQExpBuffer();
5044 delq = createPQExpBuffer();
5045 oprid = createPQExpBuffer();
5046 details = createPQExpBuffer();
5048 /* Make sure we are in proper schema so regoperator works correctly */
5049 selectSourceSchema(oprinfo->oprnamespace->nspname);
5051 if (g_fout->remoteVersion >= 70300)
5053 appendPQExpBuffer(query, "SELECT oprkind, "
5054 "oprcode::pg_catalog.regprocedure, "
5055 "oprleft::pg_catalog.regtype, "
5056 "oprright::pg_catalog.regtype, "
5057 "oprcom::pg_catalog.regoperator, "
5058 "oprnegate::pg_catalog.regoperator, "
5059 "oprrest::pg_catalog.regprocedure, "
5060 "oprjoin::pg_catalog.regprocedure, "
5062 "oprlsortop::pg_catalog.regoperator, "
5063 "oprrsortop::pg_catalog.regoperator, "
5064 "oprltcmpop::pg_catalog.regoperator, "
5065 "oprgtcmpop::pg_catalog.regoperator "
5066 "from pg_catalog.pg_operator "
5067 "where oid = '%u'::pg_catalog.oid",
5068 oprinfo->dobj.catId.oid);
5070 else if (g_fout->remoteVersion >= 70100)
5072 appendPQExpBuffer(query, "SELECT oprkind, oprcode, "
5073 "CASE WHEN oprleft = 0 THEN '-' "
5074 "ELSE format_type(oprleft, NULL) END as oprleft, "
5075 "CASE WHEN oprright = 0 THEN '-' "
5076 "ELSE format_type(oprright, NULL) END as oprright, "
5077 "oprcom, oprnegate, oprrest, oprjoin, "
5078 "oprcanhash, oprlsortop, oprrsortop, "
5079 "0 as oprltcmpop, 0 as oprgtcmpop "
5081 "where oid = '%u'::oid",
5082 oprinfo->dobj.catId.oid);
5086 appendPQExpBuffer(query, "SELECT oprkind, oprcode, "
5087 "CASE WHEN oprleft = 0 THEN '-'::name "
5088 "ELSE (select typname from pg_type where oid = oprleft) END as oprleft, "
5089 "CASE WHEN oprright = 0 THEN '-'::name "
5090 "ELSE (select typname from pg_type where oid = oprright) END as oprright, "
5091 "oprcom, oprnegate, oprrest, oprjoin, "
5092 "oprcanhash, oprlsortop, oprrsortop, "
5093 "0 as oprltcmpop, 0 as oprgtcmpop "
5095 "where oid = '%u'::oid",
5096 oprinfo->dobj.catId.oid);
5099 res = PQexec(g_conn, query->data);
5100 check_sql_result(res, g_conn, query->data, PGRES_TUPLES_OK);
5102 /* Expecting a single result only */
5103 ntups = PQntuples(res);
5106 write_msg(NULL, "Got %d rows instead of one from: %s",
5107 ntups, query->data);
5111 i_oprkind = PQfnumber(res, "oprkind");
5112 i_oprcode = PQfnumber(res, "oprcode");
5113 i_oprleft = PQfnumber(res, "oprleft");
5114 i_oprright = PQfnumber(res, "oprright");
5115 i_oprcom = PQfnumber(res, "oprcom");
5116 i_oprnegate = PQfnumber(res, "oprnegate");
5117 i_oprrest = PQfnumber(res, "oprrest");
5118 i_oprjoin = PQfnumber(res, "oprjoin");
5119 i_oprcanhash = PQfnumber(res, "oprcanhash");
5120 i_oprlsortop = PQfnumber(res, "oprlsortop");
5121 i_oprrsortop = PQfnumber(res, "oprrsortop");
5122 i_oprltcmpop = PQfnumber(res, "oprltcmpop");
5123 i_oprgtcmpop = PQfnumber(res, "oprgtcmpop");
5125 oprkind = PQgetvalue(res, 0, i_oprkind);
5126 oprcode = PQgetvalue(res, 0, i_oprcode);
5127 oprleft = PQgetvalue(res, 0, i_oprleft);
5128 oprright = PQgetvalue(res, 0, i_oprright);
5129 oprcom = PQgetvalue(res, 0, i_oprcom);
5130 oprnegate = PQgetvalue(res, 0, i_oprnegate);
5131 oprrest = PQgetvalue(res, 0, i_oprrest);
5132 oprjoin = PQgetvalue(res, 0, i_oprjoin);
5133 oprcanhash = PQgetvalue(res, 0, i_oprcanhash);
5134 oprlsortop = PQgetvalue(res, 0, i_oprlsortop);
5135 oprrsortop = PQgetvalue(res, 0, i_oprrsortop);
5136 oprltcmpop = PQgetvalue(res, 0, i_oprltcmpop);
5137 oprgtcmpop = PQgetvalue(res, 0, i_oprgtcmpop);
5139 appendPQExpBuffer(details, " PROCEDURE = %s",
5140 convertRegProcReference(oprcode));
5142 appendPQExpBuffer(oprid, "%s (",
5146 * right unary means there's a left arg and left unary means there's a
5149 if (strcmp(oprkind, "r") == 0 ||
5150 strcmp(oprkind, "b") == 0)
5152 if (g_fout->remoteVersion >= 70100)
5155 name = fmtId(oprleft);
5156 appendPQExpBuffer(details, ",\n LEFTARG = %s", name);
5157 appendPQExpBuffer(oprid, "%s", name);
5160 appendPQExpBuffer(oprid, "NONE");
5162 if (strcmp(oprkind, "l") == 0 ||
5163 strcmp(oprkind, "b") == 0)
5165 if (g_fout->remoteVersion >= 70100)
5168 name = fmtId(oprright);
5169 appendPQExpBuffer(details, ",\n RIGHTARG = %s", name);
5170 appendPQExpBuffer(oprid, ", %s)", name);
5173 appendPQExpBuffer(oprid, ", NONE)");
5175 name = convertOperatorReference(oprcom);
5177 appendPQExpBuffer(details, ",\n COMMUTATOR = %s", name);
5179 name = convertOperatorReference(oprnegate);
5181 appendPQExpBuffer(details, ",\n NEGATOR = %s", name);
5183 if (strcmp(oprcanhash, "t") == 0)
5184 appendPQExpBuffer(details, ",\n HASHES");
5186 name = convertRegProcReference(oprrest);
5188 appendPQExpBuffer(details, ",\n RESTRICT = %s", name);
5190 name = convertRegProcReference(oprjoin);
5192 appendPQExpBuffer(details, ",\n JOIN = %s", name);
5194 name = convertOperatorReference(oprlsortop);
5196 appendPQExpBuffer(details, ",\n SORT1 = %s", name);
5198 name = convertOperatorReference(oprrsortop);
5200 appendPQExpBuffer(details, ",\n SORT2 = %s", name);
5202 name = convertOperatorReference(oprltcmpop);
5204 appendPQExpBuffer(details, ",\n LTCMP = %s", name);
5206 name = convertOperatorReference(oprgtcmpop);
5208 appendPQExpBuffer(details, ",\n GTCMP = %s", name);
5211 * DROP must be fully qualified in case same name appears in
5214 appendPQExpBuffer(delq, "DROP OPERATOR %s.%s;\n",
5215 fmtId(oprinfo->oprnamespace->nspname),
5218 appendPQExpBuffer(q, "CREATE OPERATOR %s (\n%s\n);\n",
5219 oprinfo->oprname, details->data);
5221 ArchiveEntry(fout, oprinfo->dobj.catId, oprinfo->dobj.dumpId,
5223 oprinfo->oprnamespace->nspname, oprinfo->usename,
5224 "OPERATOR", q->data, delq->data, NULL,
5225 oprinfo->dobj.dependencies, oprinfo->dobj.nDeps,
5228 /* Dump Operator Comments */
5229 resetPQExpBuffer(q);
5230 appendPQExpBuffer(q, "OPERATOR %s", oprid->data);
5231 dumpComment(fout, q->data,
5232 oprinfo->oprnamespace->nspname, oprinfo->usename,
5233 oprinfo->dobj.catId, 0, oprinfo->dobj.dumpId);
5237 destroyPQExpBuffer(query);
5238 destroyPQExpBuffer(q);
5239 destroyPQExpBuffer(delq);
5240 destroyPQExpBuffer(oprid);
5241 destroyPQExpBuffer(details);
5245 * Convert a function reference obtained from pg_operator
5247 * Returns what to print, or NULL if function references is InvalidOid
5249 * In 7.3 the input is a REGPROCEDURE display; we have to strip the
5250 * argument-types part. In prior versions, the input is a REGPROC display.
5253 convertRegProcReference(const char *proc)
5255 /* In all cases "-" means a null reference */
5256 if (strcmp(proc, "-") == 0)
5259 if (g_fout->remoteVersion >= 70300)
5265 name = strdup(proc);
5266 /* find non-double-quoted left paren */
5268 for (paren = name; *paren; paren++)
5270 if (*paren == '(' && !inquote)
5281 /* REGPROC before 7.3 does not quote its result */
5286 * Convert an operator cross-reference obtained from pg_operator
5288 * Returns what to print, or NULL to print nothing
5290 * In 7.3 the input is a REGOPERATOR display; we have to strip the
5291 * argument-types part. In prior versions, the input is just a
5292 * numeric OID, which we search our operator list for.
5295 convertOperatorReference(const char *opr)
5299 /* In all cases "0" means a null reference */
5300 if (strcmp(opr, "0") == 0)
5303 if (g_fout->remoteVersion >= 70300)
5310 /* find non-double-quoted left paren */
5312 for (paren = name; *paren; paren++)
5314 if (*paren == '(' && !inquote)
5325 oprInfo = findOprByOid(atooid(opr));
5326 if (oprInfo == NULL)
5328 write_msg(NULL, "WARNING: could not find operator with OID %s\n",
5332 return oprInfo->oprname;
5337 * write out a single operator class definition
5340 dumpOpclass(Archive *fout, OpclassInfo *opcinfo)
5368 /* Dump only opclasses in dumpable namespaces */
5369 if (!opcinfo->opcnamespace->dump || dataOnly)
5373 * XXX currently we do not implement dumping of operator classes from
5374 * pre-7.3 databases. This could be done but it seems not worth the
5377 if (g_fout->remoteVersion < 70300)
5380 query = createPQExpBuffer();
5381 q = createPQExpBuffer();
5382 delq = createPQExpBuffer();
5384 /* Make sure we are in proper schema so regoperator works correctly */
5385 selectSourceSchema(opcinfo->opcnamespace->nspname);
5387 /* Get additional fields from the pg_opclass row */
5388 appendPQExpBuffer(query, "SELECT opcintype::pg_catalog.regtype, "
5389 "opckeytype::pg_catalog.regtype, "
5391 "(SELECT amname FROM pg_catalog.pg_am WHERE oid = opcamid) AS amname "
5392 "FROM pg_catalog.pg_opclass "
5393 "WHERE oid = '%u'::pg_catalog.oid",
5394 opcinfo->dobj.catId.oid);
5396 res = PQexec(g_conn, query->data);
5397 check_sql_result(res, g_conn, query->data, PGRES_TUPLES_OK);
5399 /* Expecting a single result only */
5400 ntups = PQntuples(res);
5403 write_msg(NULL, "Got %d rows instead of one from: %s",
5404 ntups, query->data);
5408 i_opcintype = PQfnumber(res, "opcintype");
5409 i_opckeytype = PQfnumber(res, "opckeytype");
5410 i_opcdefault = PQfnumber(res, "opcdefault");
5411 i_amname = PQfnumber(res, "amname");
5413 opcintype = PQgetvalue(res, 0, i_opcintype);
5414 opckeytype = PQgetvalue(res, 0, i_opckeytype);
5415 opcdefault = PQgetvalue(res, 0, i_opcdefault);
5416 /* amname will still be needed after we PQclear res */
5417 amname = strdup(PQgetvalue(res, 0, i_amname));
5420 * DROP must be fully qualified in case same name appears in
5423 appendPQExpBuffer(delq, "DROP OPERATOR CLASS %s",
5424 fmtId(opcinfo->opcnamespace->nspname));
5425 appendPQExpBuffer(delq, ".%s",
5426 fmtId(opcinfo->opcname));
5427 appendPQExpBuffer(delq, " USING %s;\n",
5430 /* Build the fixed portion of the CREATE command */
5431 appendPQExpBuffer(q, "CREATE OPERATOR CLASS %s\n ",
5432 fmtId(opcinfo->opcname));
5433 if (strcmp(opcdefault, "t") == 0)
5434 appendPQExpBuffer(q, "DEFAULT ");
5435 appendPQExpBuffer(q, "FOR TYPE %s USING %s AS\n ",
5441 if (strcmp(opckeytype, "-") != 0)
5443 appendPQExpBuffer(q, "STORAGE %s",
5451 * Now fetch and print the OPERATOR entries (pg_amop rows).
5453 resetPQExpBuffer(query);
5455 appendPQExpBuffer(query, "SELECT amopstrategy, amopreqcheck, "
5456 "amopopr::pg_catalog.regoperator "
5457 "FROM pg_catalog.pg_amop "
5458 "WHERE amopclaid = '%u'::pg_catalog.oid "
5459 "ORDER BY amopstrategy",
5460 opcinfo->dobj.catId.oid);
5462 res = PQexec(g_conn, query->data);
5463 check_sql_result(res, g_conn, query->data, PGRES_TUPLES_OK);
5465 ntups = PQntuples(res);
5467 i_amopstrategy = PQfnumber(res, "amopstrategy");
5468 i_amopreqcheck = PQfnumber(res, "amopreqcheck");
5469 i_amopopr = PQfnumber(res, "amopopr");
5471 for (i = 0; i < ntups; i++)
5473 amopstrategy = PQgetvalue(res, i, i_amopstrategy);
5474 amopreqcheck = PQgetvalue(res, i, i_amopreqcheck);
5475 amopopr = PQgetvalue(res, i, i_amopopr);
5478 appendPQExpBuffer(q, " ,\n ");
5480 appendPQExpBuffer(q, "OPERATOR %s %s",
5481 amopstrategy, amopopr);
5482 if (strcmp(amopreqcheck, "t") == 0)
5483 appendPQExpBuffer(q, " RECHECK");
5491 * Now fetch and print the FUNCTION entries (pg_amproc rows).
5493 resetPQExpBuffer(query);
5495 appendPQExpBuffer(query, "SELECT amprocnum, "
5496 "amproc::pg_catalog.regprocedure "
5497 "FROM pg_catalog.pg_amproc "
5498 "WHERE amopclaid = '%u'::pg_catalog.oid "
5499 "ORDER BY amprocnum",
5500 opcinfo->dobj.catId.oid);
5502 res = PQexec(g_conn, query->data);
5503 check_sql_result(res, g_conn, query->data, PGRES_TUPLES_OK);
5505 ntups = PQntuples(res);
5507 i_amprocnum = PQfnumber(res, "amprocnum");
5508 i_amproc = PQfnumber(res, "amproc");
5510 for (i = 0; i < ntups; i++)
5512 amprocnum = PQgetvalue(res, i, i_amprocnum);
5513 amproc = PQgetvalue(res, i, i_amproc);
5516 appendPQExpBuffer(q, " ,\n ");
5518 appendPQExpBuffer(q, "FUNCTION %s %s",
5526 appendPQExpBuffer(q, ";\n");
5528 ArchiveEntry(fout, opcinfo->dobj.catId, opcinfo->dobj.dumpId,
5530 opcinfo->opcnamespace->nspname, opcinfo->usename,
5531 "OPERATOR CLASS", q->data, delq->data, NULL,
5532 opcinfo->dobj.dependencies, opcinfo->dobj.nDeps,
5535 /* Dump Operator Class Comments */
5536 resetPQExpBuffer(q);
5537 appendPQExpBuffer(q, "OPERATOR CLASS %s",
5538 fmtId(opcinfo->opcname));
5539 appendPQExpBuffer(q, " USING %s",
5541 dumpComment(fout, q->data,
5542 NULL, opcinfo->usename,
5543 opcinfo->dobj.catId, 0, opcinfo->dobj.dumpId);
5546 destroyPQExpBuffer(query);
5547 destroyPQExpBuffer(q);
5548 destroyPQExpBuffer(delq);
5553 * write out a single conversion definition
5556 dumpConversion(Archive *fout, ConvInfo *convinfo)
5561 PQExpBuffer details;
5565 int i_conforencoding;
5566 int i_contoencoding;
5569 const char *conname;
5570 const char *conforencoding;
5571 const char *contoencoding;
5572 const char *conproc;
5575 /* Dump only conversions in dumpable namespaces */
5576 if (!convinfo->connamespace->dump || dataOnly)
5579 query = createPQExpBuffer();
5580 q = createPQExpBuffer();
5581 delq = createPQExpBuffer();
5582 details = createPQExpBuffer();
5584 /* Make sure we are in proper schema */
5585 selectSourceSchema(convinfo->connamespace->nspname);
5587 /* Get conversion-specific details */
5588 appendPQExpBuffer(query, "SELECT conname, "
5589 "pg_catalog.pg_encoding_to_char(conforencoding) AS conforencoding, "
5590 "pg_catalog.pg_encoding_to_char(contoencoding) AS contoencoding, "
5591 "conproc, condefault "
5592 "FROM pg_catalog.pg_conversion c "
5593 "WHERE c.oid = '%u'::pg_catalog.oid",
5594 convinfo->dobj.catId.oid);
5596 res = PQexec(g_conn, query->data);
5597 check_sql_result(res, g_conn, query->data, PGRES_TUPLES_OK);
5599 /* Expecting a single result only */
5600 ntups = PQntuples(res);
5603 write_msg(NULL, "Got %d rows instead of one from: %s",
5604 ntups, query->data);
5608 i_conname = PQfnumber(res, "conname");
5609 i_conforencoding = PQfnumber(res, "conforencoding");
5610 i_contoencoding = PQfnumber(res, "contoencoding");
5611 i_conproc = PQfnumber(res, "conproc");
5612 i_condefault = PQfnumber(res, "condefault");
5614 conname = PQgetvalue(res, 0, i_conname);
5615 conforencoding = PQgetvalue(res, 0, i_conforencoding);
5616 contoencoding = PQgetvalue(res, 0, i_contoencoding);
5617 conproc = PQgetvalue(res, 0, i_conproc);
5618 condefault = (PQgetvalue(res, 0, i_condefault)[0] == 't');
5621 * DROP must be fully qualified in case same name appears in
5624 appendPQExpBuffer(delq, "DROP CONVERSION %s",
5625 fmtId(convinfo->connamespace->nspname));
5626 appendPQExpBuffer(delq, ".%s;\n",
5627 fmtId(convinfo->conname));
5629 appendPQExpBuffer(q, "CREATE %sCONVERSION %s FOR ",
5630 (condefault) ? "DEFAULT " : "",
5631 fmtId(convinfo->conname));
5632 appendStringLiteral(q, conforencoding, true);
5633 appendPQExpBuffer(q, " TO ");
5634 appendStringLiteral(q, contoencoding, true);
5635 /* regproc is automatically quoted in 7.3 and above */
5636 appendPQExpBuffer(q, " FROM %s;\n", conproc);
5638 ArchiveEntry(fout, convinfo->dobj.catId, convinfo->dobj.dumpId,
5640 convinfo->connamespace->nspname, convinfo->usename,
5641 "CONVERSION", q->data, delq->data, NULL,
5642 convinfo->dobj.dependencies, convinfo->dobj.nDeps,
5645 /* Dump Conversion Comments */
5646 resetPQExpBuffer(q);
5647 appendPQExpBuffer(q, "CONVERSION %s", fmtId(convinfo->conname));
5648 dumpComment(fout, q->data,
5649 convinfo->connamespace->nspname, convinfo->usename,
5650 convinfo->dobj.catId, 0, convinfo->dobj.dumpId);
5654 destroyPQExpBuffer(query);
5655 destroyPQExpBuffer(q);
5656 destroyPQExpBuffer(delq);
5657 destroyPQExpBuffer(details);
5661 * format_aggregate_signature: generate aggregate name and argument list
5663 * The argument type names are qualified if needed. The aggregate name
5664 * is never qualified.
5667 format_aggregate_signature(AggInfo *agginfo, Archive *fout, bool honor_quotes)
5669 PQExpBufferData buf;
5671 initPQExpBuffer(&buf);
5673 appendPQExpBuffer(&buf, "%s",
5674 fmtId(agginfo->aggfn.proname));
5676 appendPQExpBuffer(&buf, "%s", agginfo->aggfn.proname);
5678 /* If using regtype or format_type, fmtbasetype is already quoted */
5679 if (fout->remoteVersion >= 70100)
5681 if (agginfo->anybasetype)
5682 appendPQExpBuffer(&buf, "(*)");
5684 appendPQExpBuffer(&buf, "(%s)", agginfo->fmtbasetype);
5688 if (agginfo->anybasetype)
5689 appendPQExpBuffer(&buf, "(*)");
5691 appendPQExpBuffer(&buf, "(%s)",
5692 fmtId(agginfo->fmtbasetype));
5700 * write out a single aggregate definition
5703 dumpAgg(Archive *fout, AggInfo *agginfo)
5708 PQExpBuffer details;
5720 const char *aggtransfn;
5721 const char *aggfinalfn;
5722 const char *aggtranstype;
5723 const char *agginitval;
5726 /* Dump only aggs in dumpable namespaces */
5727 if (!agginfo->aggfn.pronamespace->dump || dataOnly)
5730 query = createPQExpBuffer();
5731 q = createPQExpBuffer();
5732 delq = createPQExpBuffer();
5733 details = createPQExpBuffer();
5735 /* Make sure we are in proper schema */
5736 selectSourceSchema(agginfo->aggfn.pronamespace->nspname);
5738 /* Get aggregate-specific details */
5739 if (g_fout->remoteVersion >= 70300)
5741 appendPQExpBuffer(query, "SELECT aggtransfn, "
5742 "aggfinalfn, aggtranstype::pg_catalog.regtype, "
5744 "proargtypes[0] = 'pg_catalog.\"any\"'::pg_catalog.regtype as anybasetype, "
5745 "proargtypes[0]::pg_catalog.regtype as fmtbasetype, "
5746 "'t'::boolean as convertok "
5747 "from pg_catalog.pg_aggregate a, pg_catalog.pg_proc p "
5748 "where a.aggfnoid = p.oid "
5749 "and p.oid = '%u'::pg_catalog.oid",
5750 agginfo->aggfn.dobj.catId.oid);
5752 else if (g_fout->remoteVersion >= 70100)
5754 appendPQExpBuffer(query, "SELECT aggtransfn, aggfinalfn, "
5755 "format_type(aggtranstype, NULL) as aggtranstype, "
5757 "aggbasetype = 0 as anybasetype, "
5758 "CASE WHEN aggbasetype = 0 THEN '-' "
5759 "ELSE format_type(aggbasetype, NULL) END as fmtbasetype, "
5760 "'t'::boolean as convertok "
5761 "from pg_aggregate "
5762 "where oid = '%u'::oid",
5763 agginfo->aggfn.dobj.catId.oid);
5767 appendPQExpBuffer(query, "SELECT aggtransfn1 as aggtransfn, "
5769 "(select typname from pg_type where oid = aggtranstype1) as aggtranstype, "
5770 "agginitval1 as agginitval, "
5771 "aggbasetype = 0 as anybasetype, "
5772 "(select typname from pg_type where oid = aggbasetype) as fmtbasetype, "
5773 "(aggtransfn2 = 0 and aggtranstype2 = 0 and agginitval2 is null) as convertok "
5774 "from pg_aggregate "
5775 "where oid = '%u'::oid",
5776 agginfo->aggfn.dobj.catId.oid);
5779 res = PQexec(g_conn, query->data);
5780 check_sql_result(res, g_conn, query->data, PGRES_TUPLES_OK);
5782 /* Expecting a single result only */
5783 ntups = PQntuples(res);
5786 write_msg(NULL, "Got %d rows instead of one from: %s",
5787 ntups, query->data);
5791 i_aggtransfn = PQfnumber(res, "aggtransfn");
5792 i_aggfinalfn = PQfnumber(res, "aggfinalfn");
5793 i_aggtranstype = PQfnumber(res, "aggtranstype");
5794 i_agginitval = PQfnumber(res, "agginitval");
5795 i_anybasetype = PQfnumber(res, "anybasetype");
5796 i_fmtbasetype = PQfnumber(res, "fmtbasetype");
5797 i_convertok = PQfnumber(res, "convertok");
5799 aggtransfn = PQgetvalue(res, 0, i_aggtransfn);
5800 aggfinalfn = PQgetvalue(res, 0, i_aggfinalfn);
5801 aggtranstype = PQgetvalue(res, 0, i_aggtranstype);
5802 agginitval = PQgetvalue(res, 0, i_agginitval);
5803 /* we save anybasetype for format_aggregate_signature */
5804 agginfo->anybasetype = (PQgetvalue(res, 0, i_anybasetype)[0] == 't');
5805 /* we save fmtbasetype for format_aggregate_signature */
5806 agginfo->fmtbasetype = strdup(PQgetvalue(res, 0, i_fmtbasetype));
5807 convertok = (PQgetvalue(res, 0, i_convertok)[0] == 't');
5809 aggsig = format_aggregate_signature(agginfo, fout, true);
5810 aggsig_tag = format_aggregate_signature(agginfo, fout, false);
5814 write_msg(NULL, "WARNING: aggregate function %s could not be dumped correctly for this database version; ignored\n",
5819 if (g_fout->remoteVersion >= 70300)
5821 /* If using 7.3's regproc or regtype, data is already quoted */
5822 appendPQExpBuffer(details, " BASETYPE = %s,\n SFUNC = %s,\n STYPE = %s",
5823 agginfo->anybasetype ? "'any'" :
5824 agginfo->fmtbasetype,
5828 else if (g_fout->remoteVersion >= 70100)
5830 /* format_type quotes, regproc does not */
5831 appendPQExpBuffer(details, " BASETYPE = %s,\n SFUNC = %s,\n STYPE = %s",
5832 agginfo->anybasetype ? "'any'" :
5833 agginfo->fmtbasetype,
5839 /* need quotes all around */
5840 appendPQExpBuffer(details, " BASETYPE = %s,\n",
5841 agginfo->anybasetype ? "'any'" :
5842 fmtId(agginfo->fmtbasetype));
5843 appendPQExpBuffer(details, " SFUNC = %s,\n",
5845 appendPQExpBuffer(details, " STYPE = %s",
5846 fmtId(aggtranstype));
5849 if (!PQgetisnull(res, 0, i_agginitval))
5851 appendPQExpBuffer(details, ",\n INITCOND = ");
5852 appendStringLiteral(details, agginitval, true);
5855 if (strcmp(aggfinalfn, "-") != 0)
5857 appendPQExpBuffer(details, ",\n FINALFUNC = %s",
5862 * DROP must be fully qualified in case same name appears in
5865 appendPQExpBuffer(delq, "DROP AGGREGATE %s.%s;\n",
5866 fmtId(agginfo->aggfn.pronamespace->nspname),
5869 appendPQExpBuffer(q, "CREATE AGGREGATE %s (\n%s\n);\n",
5870 fmtId(agginfo->aggfn.proname),
5873 ArchiveEntry(fout, agginfo->aggfn.dobj.catId, agginfo->aggfn.dobj.dumpId,
5875 agginfo->aggfn.pronamespace->nspname, agginfo->aggfn.usename,
5876 "AGGREGATE", q->data, delq->data, NULL,
5877 agginfo->aggfn.dobj.dependencies, agginfo->aggfn.dobj.nDeps,
5880 /* Dump Aggregate Comments */
5881 resetPQExpBuffer(q);
5882 appendPQExpBuffer(q, "AGGREGATE %s", aggsig);
5883 dumpComment(fout, q->data,
5884 agginfo->aggfn.pronamespace->nspname, agginfo->aggfn.usename,
5885 agginfo->aggfn.dobj.catId, 0, agginfo->aggfn.dobj.dumpId);
5888 * Since there is no GRANT ON AGGREGATE syntax, we have to make the
5889 * ACL command look like a function's GRANT; in particular this affects
5890 * the syntax for aggregates on ANY.
5895 aggsig = format_function_signature(&agginfo->aggfn, true);
5896 aggsig_tag = format_function_signature(&agginfo->aggfn, false);
5898 dumpACL(fout, agginfo->aggfn.dobj.catId, agginfo->aggfn.dobj.dumpId,
5901 agginfo->aggfn.pronamespace->nspname,
5902 agginfo->aggfn.usename, agginfo->aggfn.proacl);
5909 destroyPQExpBuffer(query);
5910 destroyPQExpBuffer(q);
5911 destroyPQExpBuffer(delq);
5912 destroyPQExpBuffer(details);
5917 * Write out grant/revoke information
5919 * 'objCatId' is the catalog ID of the underlying object.
5920 * 'objDumpId' is the dump ID of the underlying object.
5921 * 'type' must be TABLE, FUNCTION, LANGUAGE, or SCHEMA.
5922 * 'name' is the formatted name of the object. Must be quoted etc. already.
5923 * 'tag' is the tag for the archive entry (typ. unquoted name of object).
5924 * 'nspname' is the namespace the object is in (NULL if none).
5925 * 'owner' is the owner, NULL if there is no owner (for languages).
5926 * 'acls' is the string read out of the fooacl system catalog field;
5927 * it will be parsed here.
5931 dumpACL(Archive *fout, CatalogId objCatId, DumpId objDumpId,
5932 const char *type, const char *name,
5933 const char *tag, const char *nspname, const char *owner,
5938 /* Do nothing if ACL dump is not enabled */
5939 if (dataOnly || aclsSkip)
5942 sql = createPQExpBuffer();
5944 if (!buildACLCommands(name, type, acls, owner, fout->remoteVersion, sql))
5946 write_msg(NULL, "could not parse ACL list (%s) for object \"%s\" (%s)\n",
5952 ArchiveEntry(fout, nilCatalogId, createDumpId(),
5955 "ACL", sql->data, "", NULL,
5959 destroyPQExpBuffer(sql);
5964 * write out to fout the declarations (not data) of a user-defined table
5967 dumpTable(Archive *fout, TableInfo *tbinfo)
5973 if (tbinfo->relkind == RELKIND_SEQUENCE)
5974 dumpSequence(fout, tbinfo);
5976 dumpTableSchema(fout, tbinfo);
5978 /* Handle the ACL here */
5979 namecopy = strdup(fmtId(tbinfo->relname));
5980 dumpACL(fout, tbinfo->dobj.catId, tbinfo->dobj.dumpId, "TABLE",
5981 namecopy, tbinfo->relname,
5982 tbinfo->relnamespace->nspname, tbinfo->usename,
5990 * write the declaration (not data) of one user-defined table or view
5993 dumpTableSchema(Archive *fout, TableInfo *tbinfo)
5995 PQExpBuffer query = createPQExpBuffer();
5996 PQExpBuffer q = createPQExpBuffer();
5997 PQExpBuffer delq = createPQExpBuffer();
6000 TableInfo **parents;
6001 int actual_atts; /* number of attrs in this CREATE statment */
6007 /* Make sure we are in proper schema */
6008 selectSourceSchema(tbinfo->relnamespace->nspname);
6010 /* Is it a table or a view? */
6011 if (tbinfo->relkind == RELKIND_VIEW)
6015 reltypename = "VIEW";
6017 /* Fetch the view definition */
6018 if (g_fout->remoteVersion >= 70300)
6020 /* Beginning in 7.3, viewname is not unique; rely on OID */
6021 appendPQExpBuffer(query,
6022 "SELECT pg_catalog.pg_get_viewdef('%u'::pg_catalog.oid) as viewdef",
6023 tbinfo->dobj.catId.oid);
6027 appendPQExpBuffer(query, "SELECT definition as viewdef "
6028 " from pg_views where viewname = ");
6029 appendStringLiteral(query, tbinfo->relname, true);
6030 appendPQExpBuffer(query, ";");
6033 res = PQexec(g_conn, query->data);
6034 check_sql_result(res, g_conn, query->data, PGRES_TUPLES_OK);
6036 if (PQntuples(res) != 1)
6038 if (PQntuples(res) < 1)
6039 write_msg(NULL, "query to obtain definition of view \"%s\" returned no data\n",
6042 write_msg(NULL, "query to obtain definition of view \"%s\" returned more than one definition\n",
6047 viewdef = PQgetvalue(res, 0, 0);
6049 if (strlen(viewdef) == 0)
6051 write_msg(NULL, "definition of view \"%s\" appears to be empty (length zero)\n",
6057 * DROP must be fully qualified in case same name appears in
6060 appendPQExpBuffer(delq, "DROP VIEW %s.",
6061 fmtId(tbinfo->relnamespace->nspname));
6062 appendPQExpBuffer(delq, "%s;\n",
6063 fmtId(tbinfo->relname));
6065 appendPQExpBuffer(q, "CREATE VIEW %s AS\n %s\n",
6066 fmtId(tbinfo->relname), viewdef);
6072 reltypename = "TABLE";
6073 numParents = tbinfo->numParents;
6074 parents = tbinfo->parents;
6077 * DROP must be fully qualified in case same name appears in
6080 appendPQExpBuffer(delq, "DROP TABLE %s.",
6081 fmtId(tbinfo->relnamespace->nspname));
6082 appendPQExpBuffer(delq, "%s;\n",
6083 fmtId(tbinfo->relname));
6085 appendPQExpBuffer(q, "CREATE TABLE %s (",
6086 fmtId(tbinfo->relname));
6088 for (j = 0; j < tbinfo->numatts; j++)
6090 /* Is this one of the table's own attrs, and not dropped ? */
6091 if (!tbinfo->inhAttrs[j] && !tbinfo->attisdropped[j])
6093 /* Format properly if not first attr */
6094 if (actual_atts > 0)
6095 appendPQExpBuffer(q, ",");
6096 appendPQExpBuffer(q, "\n ");
6098 /* Attribute name */
6099 appendPQExpBuffer(q, "%s ",
6100 fmtId(tbinfo->attnames[j]));
6102 /* Attribute type */
6103 if (g_fout->remoteVersion >= 70100)
6105 char *typname = tbinfo->atttypnames[j];
6107 if (tbinfo->attisserial[j])
6109 if (strcmp(typname, "integer") == 0)
6111 else if (strcmp(typname, "bigint") == 0)
6112 typname = "bigserial";
6114 appendPQExpBuffer(q, "%s", typname);
6118 /* If no format_type, fake it */
6119 appendPQExpBuffer(q, "%s",
6120 myFormatType(tbinfo->atttypnames[j],
6121 tbinfo->atttypmod[j]));
6125 * Default value --- suppress if inherited, serial,
6126 * or to be printed separately.
6128 if (tbinfo->attrdefs[j] != NULL &&
6129 !tbinfo->inhAttrDef[j] &&
6130 !tbinfo->attisserial[j] &&
6131 !tbinfo->attrdefs[j]->separate)
6132 appendPQExpBuffer(q, " DEFAULT %s",
6133 tbinfo->attrdefs[j]->adef_expr);
6136 * Not Null constraint --- suppress if inherited
6138 * Note: we could suppress this for serial columns since
6139 * SERIAL implies NOT NULL. We choose not to for forward
6140 * compatibility, since there has been some talk of making
6141 * SERIAL not imply NOT NULL, in which case the explicit
6142 * specification would be needed.
6144 if (tbinfo->notnull[j] && !tbinfo->inhNotNull[j])
6145 appendPQExpBuffer(q, " NOT NULL");
6152 * Add non-inherited CHECK constraints, if any.
6154 for (j = 0; j < tbinfo->ncheck; j++)
6156 ConstraintInfo *constr = &(tbinfo->checkexprs[j]);
6158 if (constr->coninherited || constr->separate)
6161 if (actual_atts > 0)
6162 appendPQExpBuffer(q, ",\n ");
6164 appendPQExpBuffer(q, "CONSTRAINT %s ",
6165 fmtId(constr->conname));
6166 appendPQExpBuffer(q, "%s", constr->condef);
6171 appendPQExpBuffer(q, "\n)");
6175 appendPQExpBuffer(q, "\nINHERITS (");
6176 for (k = 0; k < numParents; k++)
6178 TableInfo *parentRel = parents[k];
6181 appendPQExpBuffer(q, ", ");
6182 if (parentRel->relnamespace != tbinfo->relnamespace)
6183 appendPQExpBuffer(q, "%s.",
6184 fmtId(parentRel->relnamespace->nspname));
6185 appendPQExpBuffer(q, "%s",
6186 fmtId(parentRel->relname));
6188 appendPQExpBuffer(q, ")");
6191 appendPQExpBuffer(q, tbinfo->hasoids ? " WITH OIDS" : " WITHOUT OIDS");
6193 appendPQExpBuffer(q, ";\n");
6195 /* Loop dumping statistics and storage statements */
6196 for (j = 0; j < tbinfo->numatts; j++)
6199 * Dump per-column statistics information. We only issue an
6200 * ALTER TABLE statement if the attstattarget entry for this
6201 * column is non-negative (i.e. it's not the default value)
6203 if (tbinfo->attstattarget[j] >= 0 &&
6204 !tbinfo->attisdropped[j])
6206 appendPQExpBuffer(q, "ALTER TABLE ONLY %s ",
6207 fmtId(tbinfo->relname));
6208 appendPQExpBuffer(q, "ALTER COLUMN %s ",
6209 fmtId(tbinfo->attnames[j]));
6210 appendPQExpBuffer(q, "SET STATISTICS %d;\n",
6211 tbinfo->attstattarget[j]);
6215 * Dump per-column storage information. The statement is only
6216 * dumped if the storage has been changed from the type's
6219 if (!tbinfo->attisdropped[j] && tbinfo->attstorage[j] != tbinfo->typstorage[j])
6221 switch (tbinfo->attstorage[j])
6227 storage = "EXTERNAL";
6233 storage = "EXTENDED";
6240 * Only dump the statement if it's a storage type we
6243 if (storage != NULL)
6245 appendPQExpBuffer(q, "ALTER TABLE ONLY %s ",
6246 fmtId(tbinfo->relname));
6247 appendPQExpBuffer(q, "ALTER COLUMN %s ",
6248 fmtId(tbinfo->attnames[j]));
6249 appendPQExpBuffer(q, "SET STORAGE %s;\n",
6256 ArchiveEntry(fout, tbinfo->dobj.catId, tbinfo->dobj.dumpId,
6258 tbinfo->relnamespace->nspname, tbinfo->usename,
6259 reltypename, q->data, delq->data, NULL,
6260 tbinfo->dobj.dependencies, tbinfo->dobj.nDeps,
6263 /* Dump Table Comments */
6264 dumpTableComment(fout, tbinfo, reltypename);
6266 destroyPQExpBuffer(query);
6267 destroyPQExpBuffer(q);
6268 destroyPQExpBuffer(delq);
6272 * dumpAttrDef --- dump an attribute's default-value declaration
6275 dumpAttrDef(Archive *fout, AttrDefInfo *adinfo)
6277 TableInfo *tbinfo = adinfo->adtable;
6278 int adnum = adinfo->adnum;
6282 /* Only print it if "separate" mode is selected */
6283 if (!tbinfo->dump || !adinfo->separate || dataOnly)
6286 /* Don't print inherited or serial defaults, either */
6287 if (tbinfo->inhAttrDef[adnum-1] || tbinfo->attisserial[adnum-1])
6290 q = createPQExpBuffer();
6291 delq = createPQExpBuffer();
6293 appendPQExpBuffer(q, "ALTER TABLE %s ",
6294 fmtId(tbinfo->relname));
6295 appendPQExpBuffer(q, "ALTER COLUMN %s SET DEFAULT %s;\n",
6296 fmtId(tbinfo->attnames[adnum - 1]),
6300 * DROP must be fully qualified in case same name appears
6303 appendPQExpBuffer(delq, "ALTER TABLE %s.",
6304 fmtId(tbinfo->relnamespace->nspname));
6305 appendPQExpBuffer(delq, "%s ",
6306 fmtId(tbinfo->relname));
6307 appendPQExpBuffer(delq, "ALTER COLUMN %s DROP DEFAULT;\n",
6308 fmtId(tbinfo->attnames[adnum - 1]));
6310 ArchiveEntry(fout, adinfo->dobj.catId, adinfo->dobj.dumpId,
6311 tbinfo->attnames[adnum - 1],
6312 tbinfo->relnamespace->nspname, tbinfo->usename,
6313 "DEFAULT", q->data, delq->data, NULL,
6314 adinfo->dobj.dependencies, adinfo->dobj.nDeps,
6317 destroyPQExpBuffer(q);
6318 destroyPQExpBuffer(delq);
6322 * getAttrName: extract the correct name for an attribute
6324 * The array tblInfo->attnames[] only provides names of user attributes;
6325 * if a system attribute number is supplied, we have to fake it.
6326 * We also do a little bit of bounds checking for safety's sake.
6329 getAttrName(int attrnum, TableInfo *tblInfo)
6331 if (attrnum > 0 && attrnum <= tblInfo->numatts)
6332 return tblInfo->attnames[attrnum - 1];
6335 case SelfItemPointerAttributeNumber:
6337 case ObjectIdAttributeNumber:
6339 case MinTransactionIdAttributeNumber:
6341 case MinCommandIdAttributeNumber:
6343 case MaxTransactionIdAttributeNumber:
6345 case MaxCommandIdAttributeNumber:
6347 case TableOidAttributeNumber:
6350 write_msg(NULL, "invalid column number %d for table \"%s\"\n",
6351 attrnum, tblInfo->relname);
6353 return NULL; /* keep compiler quiet */
6358 * write out to fout a user-defined index
6361 dumpIndex(Archive *fout, IndxInfo *indxinfo)
6363 TableInfo *tbinfo = indxinfo->indextable;
6370 q = createPQExpBuffer();
6371 delq = createPQExpBuffer();
6374 * If there's an associated constraint, don't dump the index per se,
6375 * but do dump any comment for it.
6377 if (indxinfo->indexconstraint == 0)
6379 /* Plain secondary index */
6380 appendPQExpBuffer(q, "%s;\n", indxinfo->indexdef);
6382 /* If the index is clustered, we need to record that. */
6383 if (indxinfo->indisclustered)
6385 appendPQExpBuffer(q, "\nALTER TABLE %s CLUSTER",
6386 fmtId(tbinfo->relname));
6387 appendPQExpBuffer(q, " ON %s;\n",
6388 fmtId(indxinfo->indexname));
6392 * DROP must be fully qualified in case same name appears
6395 appendPQExpBuffer(delq, "DROP INDEX %s.",
6396 fmtId(tbinfo->relnamespace->nspname));
6397 appendPQExpBuffer(delq, "%s;\n",
6398 fmtId(indxinfo->indexname));
6400 ArchiveEntry(fout, indxinfo->dobj.catId, indxinfo->dobj.dumpId,
6401 indxinfo->indexname,
6402 tbinfo->relnamespace->nspname,
6404 "INDEX", q->data, delq->data, NULL,
6405 indxinfo->dobj.dependencies, indxinfo->dobj.nDeps,
6409 /* Dump Index Comments */
6410 resetPQExpBuffer(q);
6411 appendPQExpBuffer(q, "INDEX %s",
6412 fmtId(indxinfo->indexname));
6413 dumpComment(fout, q->data,
6414 tbinfo->relnamespace->nspname,
6416 indxinfo->dobj.catId, 0, indxinfo->dobj.dumpId);
6418 destroyPQExpBuffer(q);
6419 destroyPQExpBuffer(delq);
6424 * write out to fout a user-defined constraint
6427 dumpConstraint(Archive *fout, ConstraintInfo *coninfo)
6429 TableInfo *tbinfo = coninfo->contable;
6435 if (tbinfo && !tbinfo->dump)
6438 q = createPQExpBuffer();
6439 delq = createPQExpBuffer();
6441 if (coninfo->contype == 'p' || coninfo->contype == 'u')
6443 /* Index-related constraint */
6447 indxinfo = (IndxInfo *) findObjectByDumpId(coninfo->conindex);
6449 if (indxinfo == NULL)
6451 write_msg(NULL, "missing index for constraint %s\n",
6456 appendPQExpBuffer(q, "ALTER TABLE ONLY %s\n",
6457 fmtId(tbinfo->relname));
6458 appendPQExpBuffer(q, " ADD CONSTRAINT %s %s (",
6459 fmtId(coninfo->conname),
6460 coninfo->contype == 'p' ? "PRIMARY KEY" : "UNIQUE");
6462 for (k = 0; k < indxinfo->indnkeys; k++)
6464 int indkey = (int) indxinfo->indkeys[k];
6465 const char *attname;
6467 if (indkey == InvalidAttrNumber)
6469 attname = getAttrName(indkey, tbinfo);
6471 appendPQExpBuffer(q, "%s%s",
6472 (k == 0) ? "" : ", ",
6476 appendPQExpBuffer(q, ");\n");
6478 /* If the index is clustered, we need to record that. */
6479 if (indxinfo->indisclustered)
6481 appendPQExpBuffer(q, "\nALTER TABLE %s CLUSTER",
6482 fmtId(tbinfo->relname));
6483 appendPQExpBuffer(q, " ON %s;\n",
6484 fmtId(indxinfo->indexname));
6488 * DROP must be fully qualified in case same name appears
6491 appendPQExpBuffer(delq, "ALTER TABLE ONLY %s.",
6492 fmtId(tbinfo->relnamespace->nspname));
6493 appendPQExpBuffer(delq, "%s ",
6494 fmtId(tbinfo->relname));
6495 appendPQExpBuffer(delq, "DROP CONSTRAINT %s;\n",
6496 fmtId(coninfo->conname));
6498 ArchiveEntry(fout, coninfo->dobj.catId, coninfo->dobj.dumpId,
6500 tbinfo->relnamespace->nspname,
6502 "CONSTRAINT", q->data, delq->data, NULL,
6503 coninfo->dobj.dependencies, coninfo->dobj.nDeps,
6506 else if (coninfo->contype == 'f')
6509 * XXX Potentially wrap in a 'SET CONSTRAINTS OFF' block so that
6510 * the current table data is not processed
6512 appendPQExpBuffer(q, "ALTER TABLE ONLY %s\n",
6513 fmtId(tbinfo->relname));
6514 appendPQExpBuffer(q, " ADD CONSTRAINT %s %s;\n",
6515 fmtId(coninfo->conname),
6519 * DROP must be fully qualified in case same name appears in
6522 appendPQExpBuffer(delq, "ALTER TABLE ONLY %s.",
6523 fmtId(tbinfo->relnamespace->nspname));
6524 appendPQExpBuffer(delq, "%s ",
6525 fmtId(tbinfo->relname));
6526 appendPQExpBuffer(delq, "DROP CONSTRAINT %s;\n",
6527 fmtId(coninfo->conname));
6529 ArchiveEntry(fout, coninfo->dobj.catId, coninfo->dobj.dumpId,
6531 tbinfo->relnamespace->nspname,
6533 "FK CONSTRAINT", q->data, delq->data, NULL,
6534 coninfo->dobj.dependencies, coninfo->dobj.nDeps,
6537 else if (coninfo->contype == 'c' && tbinfo)
6539 /* CHECK constraint on a table */
6541 /* Ignore if not to be dumped separately */
6542 if (coninfo->separate)
6544 /* not ONLY since we want it to propagate to children */
6545 appendPQExpBuffer(q, "ALTER TABLE %s\n",
6546 fmtId(tbinfo->relname));
6547 appendPQExpBuffer(q, " ADD CONSTRAINT %s %s;\n",
6548 fmtId(coninfo->conname),
6552 * DROP must be fully qualified in case same name appears in
6555 appendPQExpBuffer(delq, "ALTER TABLE %s.",
6556 fmtId(tbinfo->relnamespace->nspname));
6557 appendPQExpBuffer(delq, "%s ",
6558 fmtId(tbinfo->relname));
6559 appendPQExpBuffer(delq, "DROP CONSTRAINT %s;\n",
6560 fmtId(coninfo->conname));
6562 ArchiveEntry(fout, coninfo->dobj.catId, coninfo->dobj.dumpId,
6564 tbinfo->relnamespace->nspname,
6566 "CHECK CONSTRAINT", q->data, delq->data, NULL,
6567 coninfo->dobj.dependencies, coninfo->dobj.nDeps,
6571 else if (coninfo->contype == 'c' && tbinfo == NULL)
6573 /* CHECK constraint on a domain */
6574 TypeInfo *tinfo = coninfo->condomain;
6576 /* Ignore if not to be dumped separately, or if not dumping domain */
6577 if (coninfo->separate && tinfo->typnamespace->dump)
6579 appendPQExpBuffer(q, "ALTER DOMAIN %s\n",
6580 fmtId(tinfo->typname));
6581 appendPQExpBuffer(q, " ADD CONSTRAINT %s %s;\n",
6582 fmtId(coninfo->conname),
6586 * DROP must be fully qualified in case same name appears in
6589 appendPQExpBuffer(delq, "ALTER DOMAIN %s.",
6590 fmtId(tinfo->typnamespace->nspname));
6591 appendPQExpBuffer(delq, "%s ",
6592 fmtId(tinfo->typname));
6593 appendPQExpBuffer(delq, "DROP CONSTRAINT %s;\n",
6594 fmtId(coninfo->conname));
6596 ArchiveEntry(fout, coninfo->dobj.catId, coninfo->dobj.dumpId,
6598 tinfo->typnamespace->nspname,
6600 "CHECK CONSTRAINT", q->data, delq->data, NULL,
6601 coninfo->dobj.dependencies, coninfo->dobj.nDeps,
6607 write_msg(NULL, "unexpected constraint type\n");
6611 /* Dump Constraint Comments --- only works for table constraints */
6614 resetPQExpBuffer(q);
6615 appendPQExpBuffer(q, "CONSTRAINT %s ",
6616 fmtId(coninfo->conname));
6617 appendPQExpBuffer(q, "ON %s",
6618 fmtId(tbinfo->relname));
6619 dumpComment(fout, q->data,
6620 tbinfo->relnamespace->nspname,
6622 coninfo->dobj.catId, 0, coninfo->dobj.dumpId);
6625 destroyPQExpBuffer(q);
6626 destroyPQExpBuffer(delq);
6631 * find the maximum oid and generate a COPY statement to set it
6635 setMaxOid(Archive *fout)
6641 do_sql_command(g_conn,
6642 "CREATE TEMPORARY TABLE pgdump_oid (dummy integer)");
6643 res = PQexec(g_conn, "INSERT INTO pgdump_oid VALUES (0)");
6644 check_sql_result(res, g_conn, "INSERT INTO pgdump_oid VALUES (0)",
6646 max_oid = PQoidValue(res);
6649 write_msg(NULL, "inserted invalid OID\n");
6653 do_sql_command(g_conn, "DROP TABLE pgdump_oid;");
6655 write_msg(NULL, "maximum system OID is %u\n", max_oid);
6656 snprintf(sql, sizeof(sql),
6657 "CREATE TEMPORARY TABLE pgdump_oid (dummy integer);\n"
6658 "COPY pgdump_oid WITH OIDS FROM stdin;\n"
6661 "DROP TABLE pgdump_oid;\n",
6664 ArchiveEntry(fout, nilCatalogId, createDumpId(),
6665 "Max OID", NULL, "",
6666 "<Init>", sql, "", NULL,
6672 * findLastBuiltInOid -
6673 * find the last built in oid
6675 * For 7.1 and 7.2, we do this by retrieving datlastsysoid from the
6676 * pg_database entry for the current database
6679 findLastBuiltinOid_V71(const char *dbname)
6684 PQExpBuffer query = createPQExpBuffer();
6686 resetPQExpBuffer(query);
6687 appendPQExpBuffer(query, "SELECT datlastsysoid from pg_database where datname = ");
6688 appendStringLiteral(query, dbname, true);
6690 res = PQexec(g_conn, query->data);
6691 check_sql_result(res, g_conn, query->data, PGRES_TUPLES_OK);
6693 ntups = PQntuples(res);
6696 write_msg(NULL, "missing pg_database entry for this database\n");
6701 write_msg(NULL, "found more than one pg_database entry for this database\n");
6704 last_oid = atooid(PQgetvalue(res, 0, PQfnumber(res, "datlastsysoid")));
6706 destroyPQExpBuffer(query);
6711 * findLastBuiltInOid -
6712 * find the last built in oid
6714 * For 7.0, we do this by assuming that the last thing that initdb does is to
6715 * create the pg_indexes view. This sucks in general, but seeing that 7.0.x
6716 * initdb won't be changing anymore, it'll do.
6719 findLastBuiltinOid_V70(void)
6725 res = PQexec(g_conn,
6726 "SELECT oid FROM pg_class WHERE relname = 'pg_indexes'");
6727 check_sql_result(res, g_conn,
6728 "SELECT oid FROM pg_class WHERE relname = 'pg_indexes'",
6730 ntups = PQntuples(res);
6733 write_msg(NULL, "could not find entry for pg_indexes in pg_class\n");
6738 write_msg(NULL, "found more than one entry for pg_indexes in pg_class\n");
6741 last_oid = atooid(PQgetvalue(res, 0, PQfnumber(res, "oid")));
6747 dumpSequence(Archive *fout, TableInfo *tbinfo)
6759 PQExpBuffer query = createPQExpBuffer();
6760 PQExpBuffer delqry = createPQExpBuffer();
6762 /* Make sure we are in proper schema */
6763 selectSourceSchema(tbinfo->relnamespace->nspname);
6765 snprintf(bufm, sizeof(bufm), INT64_FORMAT, SEQ_MINVALUE);
6766 snprintf(bufx, sizeof(bufx), INT64_FORMAT, SEQ_MAXVALUE);
6768 appendPQExpBuffer(query,
6769 "SELECT sequence_name, last_value, increment_by, "
6770 "CASE WHEN increment_by > 0 AND max_value = %s THEN NULL "
6771 " WHEN increment_by < 0 AND max_value = -1 THEN NULL "
6773 "END AS max_value, "
6774 "CASE WHEN increment_by > 0 AND min_value = 1 THEN NULL "
6775 " WHEN increment_by < 0 AND min_value = %s THEN NULL "
6777 "END AS min_value, "
6778 "cache_value, is_cycled, is_called from %s",
6780 fmtId(tbinfo->relname));
6782 res = PQexec(g_conn, query->data);
6783 check_sql_result(res, g_conn, query->data, PGRES_TUPLES_OK);
6785 if (PQntuples(res) != 1)
6787 write_msg(NULL, "query to get data of sequence \"%s\" returned %d rows (expected 1)\n",
6788 tbinfo->relname, PQntuples(res));
6792 /* Disable this check: it fails if sequence has been renamed */
6794 if (strcmp(PQgetvalue(res, 0, 0), tbinfo->relname) != 0)
6796 write_msg(NULL, "query to get data of sequence \"%s\" returned name \"%s\"\n",
6797 tbinfo->relname, PQgetvalue(res, 0, 0));
6802 last = PQgetvalue(res, 0, 1);
6803 incby = PQgetvalue(res, 0, 2);
6804 if (!PQgetisnull(res, 0, 3))
6805 maxv = PQgetvalue(res, 0, 3);
6806 if (!PQgetisnull(res, 0, 4))
6807 minv = PQgetvalue(res, 0, 4);
6808 cache = PQgetvalue(res, 0, 5);
6809 cycled = (strcmp(PQgetvalue(res, 0, 6), "t") == 0);
6810 called = (strcmp(PQgetvalue(res, 0, 7), "t") == 0);
6813 * The logic we use for restoring sequences is as follows:
6815 * Add a basic CREATE SEQUENCE statement (use last_val for start if
6816 * called is false, else use min_val for start_val). Skip this if the
6817 * sequence came from a SERIAL column.
6819 * Add a 'SETVAL(seq, last_val, iscalled)' at restore-time iff we load
6820 * data. We do this for serial sequences too.
6823 if (!dataOnly && !OidIsValid(tbinfo->owning_tab))
6825 resetPQExpBuffer(delqry);
6828 * DROP must be fully qualified in case same name appears in
6831 appendPQExpBuffer(delqry, "DROP SEQUENCE %s.",
6832 fmtId(tbinfo->relnamespace->nspname));
6833 appendPQExpBuffer(delqry, "%s;\n",
6834 fmtId(tbinfo->relname));
6836 resetPQExpBuffer(query);
6837 appendPQExpBuffer(query,
6838 "CREATE SEQUENCE %s\n",
6839 fmtId(tbinfo->relname));
6842 appendPQExpBuffer(query, " START WITH %s\n", last);
6844 appendPQExpBuffer(query, " INCREMENT BY %s\n", incby);
6847 appendPQExpBuffer(query, " MAXVALUE %s\n", maxv);
6849 appendPQExpBuffer(query, " NO MAXVALUE\n");
6852 appendPQExpBuffer(query, " MINVALUE %s\n", minv);
6854 appendPQExpBuffer(query, " NO MINVALUE\n");
6856 appendPQExpBuffer(query,
6858 cache, (cycled ? "\n CYCLE" : ""));
6860 ArchiveEntry(fout, tbinfo->dobj.catId, tbinfo->dobj.dumpId,
6862 tbinfo->relnamespace->nspname, tbinfo->usename,
6863 "SEQUENCE", query->data, delqry->data, NULL,
6864 tbinfo->dobj.dependencies, tbinfo->dobj.nDeps,
6870 resetPQExpBuffer(query);
6871 appendPQExpBuffer(query, "SELECT pg_catalog.setval(");
6872 appendStringLiteral(query, fmtId(tbinfo->relname), true);
6873 appendPQExpBuffer(query, ", %s, %s);\n",
6874 last, (called ? "true" : "false"));
6876 ArchiveEntry(fout, nilCatalogId, createDumpId(),
6878 tbinfo->relnamespace->nspname, tbinfo->usename,
6879 "SEQUENCE SET", query->data, "", NULL,
6880 &(tbinfo->dobj.dumpId), 1,
6886 /* Dump Sequence Comments */
6887 resetPQExpBuffer(query);
6888 appendPQExpBuffer(query, "SEQUENCE %s", fmtId(tbinfo->relname));
6889 dumpComment(fout, query->data,
6890 tbinfo->relnamespace->nspname, tbinfo->usename,
6891 tbinfo->dobj.catId, 0, tbinfo->dobj.dumpId);
6896 destroyPQExpBuffer(query);
6897 destroyPQExpBuffer(delqry);
6901 dumpTrigger(Archive *fout, TriggerInfo *tginfo)
6903 TableInfo *tbinfo = tginfo->tgtable;
6912 query = createPQExpBuffer();
6913 delqry = createPQExpBuffer();
6916 * DROP must be fully qualified in case same name appears in
6919 appendPQExpBuffer(delqry, "DROP TRIGGER %s ",
6920 fmtId(tginfo->tgname));
6921 appendPQExpBuffer(delqry, "ON %s.",
6922 fmtId(tbinfo->relnamespace->nspname));
6923 appendPQExpBuffer(delqry, "%s;\n",
6924 fmtId(tbinfo->relname));
6926 if (tginfo->tgisconstraint)
6928 appendPQExpBuffer(query, "CREATE CONSTRAINT TRIGGER ");
6929 appendPQExpBuffer(query, fmtId(tginfo->tgconstrname));
6933 appendPQExpBuffer(query, "CREATE TRIGGER ");
6934 appendPQExpBuffer(query, fmtId(tginfo->tgname));
6936 appendPQExpBuffer(query, "\n ");
6940 if (TRIGGER_FOR_BEFORE(tginfo->tgtype))
6941 appendPQExpBuffer(query, "BEFORE");
6943 appendPQExpBuffer(query, "AFTER");
6944 if (TRIGGER_FOR_INSERT(tginfo->tgtype))
6946 appendPQExpBuffer(query, " INSERT");
6949 if (TRIGGER_FOR_DELETE(tginfo->tgtype))
6952 appendPQExpBuffer(query, " OR DELETE");
6954 appendPQExpBuffer(query, " DELETE");
6957 if (TRIGGER_FOR_UPDATE(tginfo->tgtype))
6960 appendPQExpBuffer(query, " OR UPDATE");
6962 appendPQExpBuffer(query, " UPDATE");
6964 appendPQExpBuffer(query, " ON %s\n",
6965 fmtId(tbinfo->relname));
6967 if (tginfo->tgisconstraint)
6969 if (OidIsValid(tginfo->tgconstrrelid))
6971 /* If we are using regclass, name is already quoted */
6972 if (g_fout->remoteVersion >= 70300)
6973 appendPQExpBuffer(query, " FROM %s\n ",
6974 tginfo->tgconstrrelname);
6976 appendPQExpBuffer(query, " FROM %s\n ",
6977 fmtId(tginfo->tgconstrrelname));
6979 if (!tginfo->tgdeferrable)
6980 appendPQExpBuffer(query, "NOT ");
6981 appendPQExpBuffer(query, "DEFERRABLE INITIALLY ");
6982 if (tginfo->tginitdeferred)
6983 appendPQExpBuffer(query, "DEFERRED\n");
6985 appendPQExpBuffer(query, "IMMEDIATE\n");
6988 if (TRIGGER_FOR_ROW(tginfo->tgtype))
6989 appendPQExpBuffer(query, " FOR EACH ROW\n ");
6991 appendPQExpBuffer(query, " FOR EACH STATEMENT\n ");
6993 /* In 7.3, result of regproc is already quoted */
6994 if (g_fout->remoteVersion >= 70300)
6995 appendPQExpBuffer(query, "EXECUTE PROCEDURE %s(",
6998 appendPQExpBuffer(query, "EXECUTE PROCEDURE %s(",
6999 fmtId(tginfo->tgfname));
7002 for (findx = 0; findx < tginfo->tgnargs; findx++)
7008 p = strchr(p, '\\');
7011 write_msg(NULL, "invalid argument string (%s) for trigger \"%s\" on table \"%s\"\n",
7023 if (p[0] == '0' && p[1] == '0' && p[2] == '0')
7027 appendPQExpBufferChar(query, '\'');
7031 appendPQExpBufferChar(query, '\\');
7032 appendPQExpBufferChar(query, *s++);
7034 appendPQExpBufferChar(query, '\'');
7035 appendPQExpBuffer(query,
7036 (findx < tginfo->tgnargs - 1) ? ", " : "");
7039 appendPQExpBuffer(query, ");\n");
7041 ArchiveEntry(fout, tginfo->dobj.catId, tginfo->dobj.dumpId,
7043 tbinfo->relnamespace->nspname,
7045 "TRIGGER", query->data, delqry->data, NULL,
7046 tginfo->dobj.dependencies, tginfo->dobj.nDeps,
7049 resetPQExpBuffer(query);
7050 appendPQExpBuffer(query, "TRIGGER %s ",
7051 fmtId(tginfo->tgname));
7052 appendPQExpBuffer(query, "ON %s",
7053 fmtId(tbinfo->relname));
7055 dumpComment(fout, query->data,
7056 tbinfo->relnamespace->nspname, tbinfo->usename,
7057 tginfo->dobj.catId, 0, tginfo->dobj.dumpId);
7059 destroyPQExpBuffer(query);
7060 destroyPQExpBuffer(delqry);
7068 dumpRule(Archive *fout, RuleInfo *rinfo)
7070 TableInfo *tbinfo = rinfo->ruletable;
7077 * Ignore rules for not-to-be-dumped tables
7079 if (tbinfo == NULL || !tbinfo->dump || dataOnly)
7083 * If it is an ON SELECT rule, we do not need to dump it because
7084 * it will be handled via CREATE VIEW for the table.
7086 if (rinfo->ev_type == '1' && rinfo->is_instead)
7090 * Make sure we are in proper schema.
7092 selectSourceSchema(tbinfo->relnamespace->nspname);
7094 query = createPQExpBuffer();
7095 cmd = createPQExpBuffer();
7096 delcmd = createPQExpBuffer();
7098 if (g_fout->remoteVersion >= 70300)
7100 appendPQExpBuffer(query,
7101 "SELECT pg_catalog.pg_get_ruledef('%u'::pg_catalog.oid) AS definition",
7102 rinfo->dobj.catId.oid);
7106 /* Rule name was unique before 7.3 ... */
7107 appendPQExpBuffer(query,
7108 "SELECT pg_get_ruledef('%s') AS definition",
7112 res = PQexec(g_conn, query->data);
7113 check_sql_result(res, g_conn, query->data, PGRES_TUPLES_OK);
7115 if (PQntuples(res) != 1)
7117 write_msg(NULL, "query to get rule \"%s\" for table \"%s\" failed: wrong number of rows returned",
7118 rinfo->rulename, tbinfo->relname);
7122 printfPQExpBuffer(cmd, "%s\n", PQgetvalue(res, 0, 0));
7125 * DROP must be fully qualified in case same name appears in
7128 appendPQExpBuffer(delcmd, "DROP RULE %s ",
7129 fmtId(rinfo->rulename));
7130 appendPQExpBuffer(delcmd, "ON %s.",
7131 fmtId(tbinfo->relnamespace->nspname));
7132 appendPQExpBuffer(delcmd, "%s;\n",
7133 fmtId(tbinfo->relname));
7135 ArchiveEntry(fout, rinfo->dobj.catId, rinfo->dobj.dumpId,
7137 tbinfo->relnamespace->nspname,
7139 "RULE", cmd->data, delcmd->data, NULL,
7140 rinfo->dobj.dependencies, rinfo->dobj.nDeps,
7143 /* Dump rule comments */
7144 resetPQExpBuffer(query);
7145 appendPQExpBuffer(query, "RULE %s",
7146 fmtId(rinfo->rulename));
7147 appendPQExpBuffer(query, " ON %s",
7148 fmtId(tbinfo->relname));
7149 dumpComment(fout, query->data,
7150 tbinfo->relnamespace->nspname,
7152 rinfo->dobj.catId, 0, rinfo->dobj.dumpId);
7156 destroyPQExpBuffer(query);
7157 destroyPQExpBuffer(cmd);
7158 destroyPQExpBuffer(delcmd);
7162 * getDependencies --- obtain available dependency data
7165 getDependencies(void)
7176 DumpableObject *dobj,
7179 /* No dependency info available before 7.3 */
7180 if (g_fout->remoteVersion < 70300)
7184 write_msg(NULL, "fetching dependency data\n");
7186 /* Make sure we are in proper schema */
7187 selectSourceSchema("pg_catalog");
7189 query = createPQExpBuffer();
7191 appendPQExpBuffer(query, "SELECT "
7192 "classid, objid, refclassid, refobjid, deptype "
7194 "WHERE deptype != 'p' "
7197 res = PQexec(g_conn, query->data);
7198 check_sql_result(res, g_conn, query->data, PGRES_TUPLES_OK);
7200 ntups = PQntuples(res);
7202 i_classid = PQfnumber(res, "classid");
7203 i_objid = PQfnumber(res, "objid");
7204 i_refclassid = PQfnumber(res, "refclassid");
7205 i_refobjid = PQfnumber(res, "refobjid");
7206 i_deptype = PQfnumber(res, "deptype");
7209 * Since we ordered the SELECT by referencing ID, we can expect that
7210 * multiple entries for the same object will appear together; this
7211 * saves on searches.
7215 for (i = 0; i < ntups; i++)
7221 objId.tableoid = atooid(PQgetvalue(res, i, i_classid));
7222 objId.oid = atooid(PQgetvalue(res, i, i_objid));
7223 refobjId.tableoid = atooid(PQgetvalue(res, i, i_refclassid));
7224 refobjId.oid = atooid(PQgetvalue(res, i, i_refobjid));
7225 deptype = *(PQgetvalue(res, i, i_deptype));
7228 dobj->catId.tableoid != objId.tableoid ||
7229 dobj->catId.oid != objId.oid)
7230 dobj = findObjectByCatalogId(objId);
7233 * Failure to find objects mentioned in pg_depend is not unexpected,
7234 * since for example we don't collect info about TOAST tables.
7239 fprintf(stderr, "no referencing object %u %u\n",
7240 objId.tableoid, objId.oid);
7245 refdobj = findObjectByCatalogId(refobjId);
7247 if (refdobj == NULL)
7250 fprintf(stderr, "no referenced object %u %u\n",
7251 refobjId.tableoid, refobjId.oid);
7256 addObjectDependency(dobj, refdobj->dumpId);
7261 destroyPQExpBuffer(query);
7266 * selectSourceSchema - make the specified schema the active search path
7267 * in the source database.
7269 * NB: pg_catalog is explicitly searched after the specified schema;
7270 * so user names are only qualified if they are cross-schema references,
7271 * and system names are only qualified if they conflict with a user name
7272 * in the current schema.
7274 * Whenever the selected schema is not pg_catalog, be careful to qualify
7275 * references to system catalogs and types in our emitted commands!
7278 selectSourceSchema(const char *schemaName)
7280 static char *curSchemaName = NULL;
7283 /* Not relevant if fetching from pre-7.3 DB */
7284 if (g_fout->remoteVersion < 70300)
7286 /* Ignore null schema names */
7287 if (schemaName == NULL || *schemaName == '\0')
7289 /* Optimize away repeated selection of same schema */
7290 if (curSchemaName && strcmp(curSchemaName, schemaName) == 0)
7293 query = createPQExpBuffer();
7294 appendPQExpBuffer(query, "SET search_path = %s",
7296 if (strcmp(schemaName, "pg_catalog") != 0)
7297 appendPQExpBuffer(query, ", pg_catalog");
7299 do_sql_command(g_conn, query->data);
7301 destroyPQExpBuffer(query);
7303 free(curSchemaName);
7304 curSchemaName = strdup(schemaName);
7308 * getFormattedTypeName - retrieve a nicely-formatted type name for the
7311 * NB: in 7.3 and up the result may depend on the currently-selected
7312 * schema; this is why we don't try to cache the names.
7315 getFormattedTypeName(Oid oid, OidOptions opts)
7324 if ((opts & zeroAsOpaque) != 0)
7325 return strdup(g_opaque_type);
7326 else if ((opts & zeroAsAny) != 0)
7327 return strdup("'any'");
7328 else if ((opts & zeroAsStar) != 0)
7330 else if ((opts & zeroAsNone) != 0)
7331 return strdup("NONE");
7334 query = createPQExpBuffer();
7335 if (g_fout->remoteVersion >= 70300)
7337 appendPQExpBuffer(query, "SELECT pg_catalog.format_type('%u'::pg_catalog.oid, NULL)",
7340 else if (g_fout->remoteVersion >= 70100)
7342 appendPQExpBuffer(query, "SELECT format_type('%u'::oid, NULL)",
7347 appendPQExpBuffer(query, "SELECT typname "
7349 "WHERE oid = '%u'::oid",
7353 res = PQexec(g_conn, query->data);
7354 check_sql_result(res, g_conn, query->data, PGRES_TUPLES_OK);
7356 /* Expecting a single result only */
7357 ntups = PQntuples(res);
7360 write_msg(NULL, "query yielded %d rows instead of one: %s\n",
7361 ntups, query->data);
7365 if (g_fout->remoteVersion >= 70100)
7367 /* already quoted */
7368 result = strdup(PQgetvalue(res, 0, 0));
7372 /* may need to quote it */
7373 result = strdup(fmtId(PQgetvalue(res, 0, 0)));
7377 destroyPQExpBuffer(query);
7383 * myFormatType --- local implementation of format_type for use with 7.0.
7386 myFormatType(const char *typname, int32 typmod)
7389 PQExpBuffer buf = createPQExpBuffer();
7391 /* Show lengths on bpchar and varchar */
7392 if (!strcmp(typname, "bpchar"))
7394 int len = (typmod - VARHDRSZ);
7396 appendPQExpBuffer(buf, "character");
7398 appendPQExpBuffer(buf, "(%d)",
7401 else if (!strcmp(typname, "varchar"))
7403 appendPQExpBuffer(buf, "character varying");
7405 appendPQExpBuffer(buf, "(%d)",
7408 else if (!strcmp(typname, "numeric"))
7410 appendPQExpBuffer(buf, "numeric");
7417 tmp_typmod = typmod - VARHDRSZ;
7418 precision = (tmp_typmod >> 16) & 0xffff;
7419 scale = tmp_typmod & 0xffff;
7420 appendPQExpBuffer(buf, "(%d,%d)",
7426 * char is an internal single-byte data type; Let's make sure we force
7427 * it through with quotes. - thomas 1998-12-13
7429 else if (strcmp(typname, "char") == 0)
7430 appendPQExpBuffer(buf, "\"char\"");
7432 appendPQExpBuffer(buf, "%s", fmtId(typname));
7434 result = strdup(buf->data);
7435 destroyPQExpBuffer(buf);
7441 * fmtQualifiedId - convert a qualified name to the proper format for
7442 * the source database.
7444 * Like fmtId, use the result before calling again.
7447 fmtQualifiedId(const char *schema, const char *id)
7449 static PQExpBuffer id_return = NULL;
7451 if (id_return) /* first time through? */
7452 resetPQExpBuffer(id_return);
7454 id_return = createPQExpBuffer();
7456 /* Suppress schema name if fetching from pre-7.3 DB */
7457 if (g_fout->remoteVersion >= 70300 && schema && *schema)
7459 appendPQExpBuffer(id_return, "%s.",
7462 appendPQExpBuffer(id_return, "%s",
7465 return id_return->data;
7469 * Return a column list clause for the given relation.
7471 * Special case: if there are no undropped columns in the relation, return
7472 * "", not an invalid "()" column list.
7475 fmtCopyColumnList(const TableInfo *ti)
7477 static PQExpBuffer q = NULL;
7478 int numatts = ti->numatts;
7479 char **attnames = ti->attnames;
7480 bool *attisdropped = ti->attisdropped;
7484 if (q) /* first time through? */
7485 resetPQExpBuffer(q);
7487 q = createPQExpBuffer();
7489 appendPQExpBuffer(q, "(");
7491 for (i = 0; i < numatts; i++)
7493 if (attisdropped[i])
7496 appendPQExpBuffer(q, ", ");
7497 appendPQExpBuffer(q, "%s", fmtId(attnames[i]));
7502 return ""; /* no undropped columns */
7504 appendPQExpBuffer(q, ")");
7509 * Convenience subroutine to execute a SQL command and check for
7510 * COMMAND_OK status.
7513 do_sql_command(PGconn *conn, const char *query)
7517 res = PQexec(conn, query);
7518 check_sql_result(res, conn, query, PGRES_COMMAND_OK);
7523 * Convenience subroutine to verify a SQL command succeeded,
7524 * and exit with a useful error message if not.
7527 check_sql_result(PGresult *res, PGconn *conn, const char *query,
7528 ExecStatusType expected)
7532 if (res && PQresultStatus(res) == expected)
7535 write_msg(NULL, "SQL command failed\n");
7537 err = PQresultErrorMessage(res);
7539 err = PQerrorMessage(conn);
7540 write_msg(NULL, "Error message from server: %s", err);
7541 write_msg(NULL, "The command was: %s\n", query);