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.366 2004/03/02 21:14:44 tgl 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, char **argnames,
140 static const char *convertRegProcReference(const char *proc);
141 static const char *convertOperatorReference(const char *opr);
142 static Oid findLastBuiltinOid_V71(const char *);
143 static Oid findLastBuiltinOid_V70(void);
144 static void setMaxOid(Archive *fout);
145 static void selectSourceSchema(const char *schemaName);
146 static char *getFormattedTypeName(Oid oid, OidOptions opts);
147 static char *myFormatType(const char *typname, int32 typmod);
148 static const char *fmtQualifiedId(const char *schema, const char *id);
149 static int dumpBlobs(Archive *AH, void *arg);
150 static void dumpDatabase(Archive *AH);
151 static void dumpEncoding(Archive *AH);
152 static const char *getAttrName(int attrnum, TableInfo *tblInfo);
153 static const char *fmtCopyColumnList(const TableInfo *ti);
154 static void do_sql_command(PGconn *conn, const char *query);
155 static void check_sql_result(PGresult *res, PGconn *conn, const char *query,
156 ExecStatusType expected);
160 main(int argc, char **argv)
163 const char *filename = NULL;
164 const char *format = "p";
165 const char *dbname = NULL;
166 const char *pghost = NULL;
167 const char *pgport = NULL;
168 const char *username = NULL;
172 DumpableObject **dobjs;
175 bool force_password = false;
176 int compressLevel = -1;
177 bool ignore_version = false;
180 int outputCreate = 0;
182 int outputNoOwner = 0;
183 static int use_setsessauth = 0;
184 static int disable_triggers = 0;
185 char *outputSuperuser = NULL;
187 RestoreOptions *ropt;
189 static struct option long_options[] = {
190 {"data-only", no_argument, NULL, 'a'},
191 {"blobs", no_argument, NULL, 'b'},
192 {"clean", no_argument, NULL, 'c'},
193 {"create", no_argument, NULL, 'C'},
194 {"file", required_argument, NULL, 'f'},
195 {"format", required_argument, NULL, 'F'},
196 {"inserts", no_argument, NULL, 'd'},
197 {"attribute-inserts", no_argument, NULL, 'D'},
198 {"column-inserts", no_argument, NULL, 'D'},
199 {"host", required_argument, NULL, 'h'},
200 {"ignore-version", no_argument, NULL, 'i'},
201 {"no-reconnect", no_argument, NULL, 'R'},
202 {"oids", no_argument, NULL, 'o'},
203 {"no-owner", no_argument, NULL, 'O'},
204 {"port", required_argument, NULL, 'p'},
205 {"schema", required_argument, NULL, 'n'},
206 {"schema-only", no_argument, NULL, 's'},
207 {"superuser", required_argument, NULL, 'S'},
208 {"table", required_argument, NULL, 't'},
209 {"password", no_argument, NULL, 'W'},
210 {"username", required_argument, NULL, 'U'},
211 {"verbose", no_argument, NULL, 'v'},
212 {"no-privileges", no_argument, NULL, 'x'},
213 {"no-acl", no_argument, NULL, 'x'},
214 {"compress", required_argument, NULL, 'Z'},
215 {"help", no_argument, NULL, '?'},
216 {"version", no_argument, NULL, 'V'},
219 * the following options don't have an equivalent short option
220 * letter, but are available as '-X long-name'
222 {"use-set-session-authorization", no_argument, &use_setsessauth, 1},
223 {"disable-triggers", no_argument, &disable_triggers, 1},
230 setlocale(LC_ALL, "");
231 bindtextdomain("pg_dump", LOCALEDIR);
232 textdomain("pg_dump");
237 strcpy(g_comment_start, "-- ");
238 g_comment_end[0] = '\0';
239 strcpy(g_opaque_type, "opaque");
241 dataOnly = schemaOnly = dumpInserts = attrNames = false;
243 progname = get_progname(argv[0]);
245 /* Set default options based on progname */
246 if (strcmp(progname, "pg_backup") == 0)
254 if (strcmp(argv[1], "--help") == 0 || strcmp(argv[1], "-?") == 0)
259 if (strcmp(argv[1], "--version") == 0 || strcmp(argv[1], "-V") == 0)
261 puts("pg_dump (PostgreSQL) " PG_VERSION);
266 while ((c = getopt_long(argc, argv, "abcCdDf:F:h:in:oOp:RsS:t:uU:vWxX:Z:",
267 long_options, &optindex)) != -1)
271 case 'a': /* Dump data only */
275 case 'b': /* Dump blobs */
279 case 'c': /* clean (i.e., drop) schema prior to
284 case 'C': /* Create DB */
288 case 'd': /* dump data as proper insert strings */
292 case 'D': /* dump data as proper insert strings with
306 case 'h': /* server host */
310 case 'i': /* ignore database version mismatch */
311 ignore_version = true;
314 case 'n': /* Dump data for this schema only */
315 selectSchemaName = strdup(optarg);
318 case 'o': /* Dump oids */
322 case 'O': /* Don't reconnect to match owner */
326 case 'p': /* server port */
331 /* no-op, still accepted for backwards compatibility */
334 case 's': /* dump schema only */
338 case 'S': /* Username for superuser in plain text
340 outputSuperuser = strdup(optarg);
343 case 't': /* Dump data for this table only */
344 selectTableName = strdup(optarg);
348 force_password = true;
349 username = simple_prompt("User name: ", 100, true);
356 case 'v': /* verbose */
361 force_password = true;
364 case 'x': /* skip ACL dump */
369 * Option letters were getting scarce, so I invented this
370 * new scheme: '-X feature' turns on some feature. Compare
371 * to the -f option in GCC. You should also add an
372 * equivalent GNU-style option --feature. Features that
373 * require arguments should use '-X feature=foo'.
376 if (strcmp(optarg, "use-set-session-authorization") == 0)
377 /* no-op, still allowed for compatibility */ ;
378 else if (strcmp(optarg, "disable-triggers") == 0)
379 disable_triggers = 1;
383 _("%s: invalid -X option -- %s\n"),
385 fprintf(stderr, _("Try \"%s --help\" for more information.\n"), progname);
390 case 'Z': /* Compression Level */
391 compressLevel = atoi(optarg);
393 /* This covers the long options equivalent to -X xxx. */
399 fprintf(stderr, _("Try \"%s --help\" for more information.\n"), progname);
404 if (optind < (argc - 1))
406 fprintf(stderr, _("%s: too many command-line arguments (first is \"%s\")\n"),
407 progname, argv[optind + 1]);
408 fprintf(stderr, _("Try \"%s --help\" for more information.\n"),
413 /* Get database name from command line */
415 dbname = argv[optind];
417 if (dataOnly && schemaOnly)
419 write_msg(NULL, "options \"schema only\" (-s) and \"data only\" (-a) cannot be used together\n");
423 if (dataOnly && outputClean)
425 write_msg(NULL, "options \"clean\" (-c) and \"data only\" (-a) cannot be used together\n");
429 if (outputBlobs && selectTableName != NULL)
431 write_msg(NULL, "large-object output not supported for a single table\n");
432 write_msg(NULL, "use a full dump instead\n");
436 if (outputBlobs && selectSchemaName != NULL)
438 write_msg(NULL, "large-object output not supported for a single schema\n");
439 write_msg(NULL, "use a full dump instead\n");
443 if (dumpInserts == true && oids == true)
445 write_msg(NULL, "INSERT (-d, -D) and OID (-o) options cannot be used together\n");
446 write_msg(NULL, "(The INSERT command cannot set OIDs.)\n");
450 if (outputBlobs == true && (format[0] == 'p' || format[0] == 'P'))
452 write_msg(NULL, "large-object output is not supported for plain-text dump files\n");
453 write_msg(NULL, "(Use a different output format.)\n");
457 /* open the output file */
462 g_fout = CreateArchive(filename, archCustom, compressLevel);
467 g_fout = CreateArchive(filename, archFiles, compressLevel);
473 g_fout = CreateArchive(filename, archNull, 0);
478 g_fout = CreateArchive(filename, archTar, compressLevel);
482 write_msg(NULL, "invalid output format \"%s\" specified\n", format);
488 write_msg(NULL, "could not open output file \"%s\" for writing\n", filename);
492 /* Let the archiver know how noisy to be */
493 g_fout->verbose = g_verbose;
495 g_fout->minRemoteVersion = 70000; /* we can handle back to 7.0 */
496 g_fout->maxRemoteVersion = parse_version(PG_VERSION);
497 if (g_fout->maxRemoteVersion < 0)
499 write_msg(NULL, "could not parse version string \"%s\"\n", PG_VERSION);
504 * Open the database using the Archiver, so it knows about it. Errors
507 g_conn = ConnectDatabase(g_fout, dbname, pghost, pgport,
508 username, force_password, ignore_version);
511 * Start serializable transaction to dump consistent data.
513 do_sql_command(g_conn, "BEGIN");
515 do_sql_command(g_conn, "SET TRANSACTION ISOLATION LEVEL SERIALIZABLE");
517 /* Set the datestyle to ISO to ensure the dump's portability */
518 do_sql_command(g_conn, "SET DATESTYLE = ISO");
521 * If supported, set extra_float_digits so that we can dump float data
522 * exactly (given correctly implemented float I/O code, anyway)
524 if (g_fout->remoteVersion >= 70400)
525 do_sql_command(g_conn, "SET extra_float_digits TO 2");
527 /* Find the last built-in OID, if needed */
528 if (g_fout->remoteVersion < 70300)
530 if (g_fout->remoteVersion >= 70100)
531 g_last_builtin_oid = findLastBuiltinOid_V71(PQdb(g_conn));
533 g_last_builtin_oid = findLastBuiltinOid_V70();
535 write_msg(NULL, "last built-in OID is %u\n", g_last_builtin_oid);
539 * Now scan the database and create DumpableObject structs for all the
540 * objects we intend to dump.
542 tblinfo = getSchemaData(&numTables, schemaOnly, dataOnly);
545 getTableData(tblinfo, numTables, oids);
548 * Collect dependency data to assist in ordering the objects.
553 * Sort the objects into a safe dump order (no forward references).
555 getDumpableObjects(&dobjs, &numObjs);
557 sortDumpableObjectsByType(dobjs, numObjs);
558 sortDumpableObjects(dobjs, numObjs);
561 * Create archive TOC entries for all the objects to be dumped,
565 /* First the special encoding entry. */
566 dumpEncoding(g_fout);
568 /* The database item is always second. */
570 dumpDatabase(g_fout);
572 /* Max OID is next. */
576 /* Now the rearrangeable objects. */
577 for (i = 0; i < numObjs; i++)
579 dumpDumpableObject(g_fout, dobjs[i]);
582 /* BLOBs are always last (XXX is this right?) */
584 ArchiveEntry(g_fout, nilCatalogId, createDumpId(),
586 "BLOBS", "", "", NULL,
591 * And finally we can do the actual output.
595 ropt = NewRestoreOptions();
596 ropt->filename = (char *) filename;
597 ropt->dropSchema = outputClean;
598 ropt->aclsSkip = aclsSkip;
599 ropt->superuser = outputSuperuser;
600 ropt->create = outputCreate;
601 ropt->noOwner = outputNoOwner;
602 ropt->disable_triggers = disable_triggers;
604 if (compressLevel == -1)
605 ropt->compression = 0;
607 ropt->compression = compressLevel;
609 ropt->suppressDumpWarnings = true; /* We've already shown
612 RestoreArchive(g_fout, ropt);
615 CloseArchive(g_fout);
624 help(const char *progname)
626 printf(_("%s dumps a database as a text file or to other formats.\n\n"), progname);
627 printf(_("Usage:\n"));
628 printf(_(" %s [OPTION]... [DBNAME]\n"), progname);
630 printf(_("\nGeneral options:\n"));
631 printf(_(" -f, --file=FILENAME output file name\n"));
632 printf(_(" -F, --format=c|t|p output file format (custom, tar, plain text)\n"));
633 printf(_(" -i, --ignore-version proceed even when server version mismatches\n"
634 " pg_dump version\n"));
635 printf(_(" -v, --verbose verbose mode\n"));
636 printf(_(" -Z, --compress=0-9 compression level for compressed formats\n"));
637 printf(_(" --help show this help, then exit\n"));
638 printf(_(" --version output version information, then exit\n"));
640 printf(_("\nOptions controlling the output content:\n"));
641 printf(_(" -a, --data-only dump only the data, not the schema\n"));
642 printf(_(" -b, --blobs include large objects in dump\n"));
643 printf(_(" -c, --clean clean (drop) schema prior to create\n"));
644 printf(_(" -C, --create include commands to create database in dump\n"));
645 printf(_(" -d, --inserts dump data as INSERT, rather than COPY, commands\n"));
646 printf(_(" -D, --column-inserts dump data as INSERT commands with column names\n"));
647 printf(_(" -n, --schema=SCHEMA dump the named schema only\n"));
648 printf(_(" -o, --oids include OIDs in dump\n"));
649 printf(_(" -O, --no-owner do not output commands to set object ownership\n"
650 " in plain text format\n"));
651 printf(_(" -s, --schema-only dump only the schema, no data\n"));
652 printf(_(" -S, --superuser=NAME specify the superuser user name to use in\n"
653 " plain text format\n"));
654 printf(_(" -t, --table=TABLE dump the named table only\n"));
655 printf(_(" -x, --no-privileges do not dump privileges (grant/revoke)\n"));
656 printf(_(" -X disable-triggers, --disable-triggers\n"
657 " disable triggers during data-only restore\n"));
659 printf(_("\nConnection options:\n"));
660 printf(_(" -h, --host=HOSTNAME database server host or socket directory\n"));
661 printf(_(" -p, --port=PORT database server port number\n"));
662 printf(_(" -U, --username=NAME connect as specified database user\n"));
663 printf(_(" -W, --password force password prompt (should happen automatically)\n"));
665 printf(_("\nIf no database name is supplied, then the PGDATABASE environment\n"
666 "variable value is used.\n\n"));
667 printf(_("Report bugs to <pgsql-bugs@postgresql.org>.\n"));
675 write_msg(NULL, "*** aborted because of error\n");
680 * selectDumpableNamespace: policy-setting subroutine
681 * Mark a namespace as to be dumped or not
684 selectDumpableNamespace(NamespaceInfo *nsinfo)
687 * If a specific table is being dumped, do not dump any complete
688 * namespaces. If a specific namespace is being dumped, dump just
689 * that namespace. Otherwise, dump all non-system namespaces.
691 if (selectTableName != NULL)
692 nsinfo->dump = false;
693 else if (selectSchemaName != NULL)
695 if (strcmp(nsinfo->nspname, selectSchemaName) == 0)
698 nsinfo->dump = false;
700 else if (strncmp(nsinfo->nspname, "pg_", 3) == 0 ||
701 strcmp(nsinfo->nspname, "information_schema") == 0)
702 nsinfo->dump = false;
708 * selectDumpableTable: policy-setting subroutine
709 * Mark a table as to be dumped or not
712 selectDumpableTable(TableInfo *tbinfo)
715 * Always dump if dumping parent namespace; else, if a particular
716 * tablename has been specified, dump matching table name; else, do
719 tbinfo->dump = false;
720 if (tbinfo->relnamespace->dump)
722 else if (selectTableName != NULL &&
723 strcmp(tbinfo->relname, selectTableName) == 0)
725 /* If both -s and -t specified, must match both to dump */
726 if (selectSchemaName == NULL)
728 else if (strcmp(tbinfo->relnamespace->nspname, selectSchemaName) == 0)
734 * Dump a table's contents for loading using the COPY command
735 * - this routine is called by the Archiver when it wants the table
739 #define COPYBUFSIZ 8192
742 dumpTableData_copy(Archive *fout, void *dcontext)
744 TableDataInfo *tdinfo = (TableDataInfo *) dcontext;
745 TableInfo *tbinfo = tdinfo->tdtable;
746 const char *classname = tbinfo->relname;
747 const bool hasoids = tbinfo->hasoids;
748 const bool oids = tdinfo->oids;
749 PQExpBuffer q = createPQExpBuffer();
753 char copybuf[COPYBUFSIZ];
754 const char *column_list;
757 write_msg(NULL, "dumping contents of table %s\n", classname);
760 * Make sure we are in proper schema. We will qualify the table name
761 * below anyway (in case its name conflicts with a pg_catalog table);
762 * but this ensures reproducible results in case the table contains
763 * regproc, regclass, etc columns.
765 selectSourceSchema(tbinfo->relnamespace->nspname);
768 * If possible, specify the column list explicitly so that we have no
769 * possibility of retrieving data in the wrong column order. (The
770 * default column ordering of COPY will not be what we want in certain
771 * corner cases involving ADD COLUMN and inheritance.)
773 if (g_fout->remoteVersion >= 70300)
774 column_list = fmtCopyColumnList(tbinfo);
776 column_list = ""; /* can't select columns in COPY */
780 appendPQExpBuffer(q, "COPY %s %s WITH OIDS TO stdout;",
781 fmtQualifiedId(tbinfo->relnamespace->nspname,
787 appendPQExpBuffer(q, "COPY %s %s TO stdout;",
788 fmtQualifiedId(tbinfo->relnamespace->nspname,
792 res = PQexec(g_conn, q->data);
793 check_sql_result(res, g_conn, q->data, PGRES_COPY_OUT);
799 ret = PQgetline(g_conn, copybuf, COPYBUFSIZ);
801 if (copybuf[0] == '\\' &&
805 copydone = true; /* don't print this... */
809 archputs(copybuf, fout);
816 archputs("\n", fout);
826 * There was considerable discussion in late July, 2000 regarding
827 * slowing down pg_dump when backing up large tables. Users with
828 * both slow & fast (muti-processor) machines experienced
829 * performance degradation when doing a backup.
831 * Initial attempts based on sleeping for a number of ms for each ms
832 * of work were deemed too complex, then a simple 'sleep in each
833 * loop' implementation was suggested. The latter failed because
834 * the loop was too tight. Finally, the following was implemented:
836 * If throttle is non-zero, then See how long since the last sleep.
837 * Work out how long to sleep (based on ratio). If sleep is more
838 * than 100ms, then sleep reset timer EndIf EndIf
840 * where the throttle value was the number of ms to sleep per ms of
841 * work. The calculation was done in each loop.
843 * Most of the hard work is done in the backend, and this solution
844 * still did not work particularly well: on slow machines, the
845 * ratio was 50:1, and on medium paced machines, 1:1, and on fast
846 * multi-processor machines, it had little or no effect, for
847 * reasons that were unclear.
849 * Further discussion ensued, and the proposal was dropped.
851 * For those people who want this feature, it can be implemented
852 * using gettimeofday in each loop, calculating the time since
853 * last sleep, multiplying that by the sleep ratio, then if the
854 * result is more than a preset 'minimum sleep time' (say 100ms),
855 * call the 'select' function to sleep for a subsecond period ie.
857 * select(0, NULL, NULL, NULL, &tvi);
859 * This will return after the interval specified in the structure
860 * tvi. Finally, call gettimeofday again to save the 'last sleep
864 archprintf(fout, "\\.\n\n\n");
866 ret = PQendcopy(g_conn);
869 write_msg(NULL, "SQL command to dump the contents of table \"%s\" failed: PQendcopy() failed.\n", classname);
870 write_msg(NULL, "Error message from server: %s", PQerrorMessage(g_conn));
871 write_msg(NULL, "The command was: %s\n", q->data);
876 destroyPQExpBuffer(q);
881 dumpTableData_insert(Archive *fout, void *dcontext)
883 TableDataInfo *tdinfo = (TableDataInfo *) dcontext;
884 TableInfo *tbinfo = tdinfo->tdtable;
885 const char *classname = tbinfo->relname;
886 PQExpBuffer q = createPQExpBuffer();
893 * Make sure we are in proper schema. We will qualify the table name
894 * below anyway (in case its name conflicts with a pg_catalog table);
895 * but this ensures reproducible results in case the table contains
896 * regproc, regclass, etc columns.
898 selectSourceSchema(tbinfo->relnamespace->nspname);
900 if (fout->remoteVersion >= 70100)
902 appendPQExpBuffer(q, "DECLARE _pg_dump_cursor CURSOR FOR "
903 "SELECT * FROM ONLY %s",
904 fmtQualifiedId(tbinfo->relnamespace->nspname,
909 appendPQExpBuffer(q, "DECLARE _pg_dump_cursor CURSOR FOR "
911 fmtQualifiedId(tbinfo->relnamespace->nspname,
915 res = PQexec(g_conn, q->data);
916 check_sql_result(res, g_conn, q->data, PGRES_COMMAND_OK);
922 res = PQexec(g_conn, "FETCH 100 FROM _pg_dump_cursor");
923 check_sql_result(res, g_conn, "FETCH 100 FROM _pg_dump_cursor",
925 nfields = PQnfields(res);
926 for (tuple = 0; tuple < PQntuples(res); tuple++)
928 archprintf(fout, "INSERT INTO %s ", fmtId(classname));
931 /* corner case for zero-column table */
932 archprintf(fout, "DEFAULT VALUES;\n");
935 if (attrNames == true)
938 appendPQExpBuffer(q, "(");
939 for (field = 0; field < nfields; field++)
942 appendPQExpBuffer(q, ", ");
943 appendPQExpBuffer(q, fmtId(PQfname(res, field)));
945 appendPQExpBuffer(q, ") ");
946 archprintf(fout, "%s", q->data);
948 archprintf(fout, "VALUES (");
949 for (field = 0; field < nfields; field++)
952 archprintf(fout, ", ");
953 if (PQgetisnull(res, tuple, field))
955 archprintf(fout, "NULL");
959 /* XXX This code is partially duplicated in ruleutils.c */
960 switch (PQftype(res, field))
971 * These types are printed without quotes
972 * unless they contain values that aren't
973 * accepted by the scanner unquoted (e.g.,
974 * 'NaN'). Note that strtod() and friends
975 * might accept NaN, so we can't use that to
978 * In reality we only need to defend against
979 * infinity and NaN, so we need not get too
980 * crazy about pattern matching here.
982 const char *s = PQgetvalue(res, tuple, field);
984 if (strspn(s, "0123456789 +-eE.") == strlen(s))
985 archprintf(fout, "%s", s);
987 archprintf(fout, "'%s'", s);
993 archprintf(fout, "B'%s'",
994 PQgetvalue(res, tuple, field));
998 if (strcmp(PQgetvalue(res, tuple, field), "t") == 0)
999 archprintf(fout, "true");
1001 archprintf(fout, "false");
1005 /* All other types are printed as string literals. */
1006 resetPQExpBuffer(q);
1007 appendStringLiteral(q, PQgetvalue(res, tuple, field), false);
1008 archprintf(fout, "%s", q->data);
1012 archprintf(fout, ");\n");
1014 } while (PQntuples(res) > 0);
1018 archprintf(fout, "\n\n");
1020 do_sql_command(g_conn, "CLOSE _pg_dump_cursor");
1022 destroyPQExpBuffer(q);
1029 * dump the contents of a single table
1031 * Actually, this just makes an ArchiveEntry for the table contents.
1034 dumpTableData(Archive *fout, TableDataInfo *tdinfo)
1036 TableInfo *tbinfo = tdinfo->tdtable;
1037 PQExpBuffer copyBuf = createPQExpBuffer();
1038 DataDumperPtr dumpFn;
1043 /* Dump/restore using COPY */
1044 dumpFn = dumpTableData_copy;
1045 /* must use 2 steps here 'cause fmtId is nonreentrant */
1046 appendPQExpBuffer(copyBuf, "COPY %s ",
1047 fmtId(tbinfo->relname));
1048 appendPQExpBuffer(copyBuf, "%s %sFROM stdin;\n",
1049 fmtCopyColumnList(tbinfo),
1050 (tdinfo->oids && tbinfo->hasoids) ? "WITH OIDS " : "");
1051 copyStmt = copyBuf->data;
1055 /* Restore using INSERT */
1056 dumpFn = dumpTableData_insert;
1060 ArchiveEntry(fout, tdinfo->dobj.catId, tdinfo->dobj.dumpId,
1062 tbinfo->relnamespace->nspname,
1064 "TABLE DATA", "", "", copyStmt,
1065 tdinfo->dobj.dependencies, tdinfo->dobj.nDeps,
1068 destroyPQExpBuffer(copyBuf);
1073 * set up dumpable objects representing the contents of tables
1076 getTableData(TableInfo *tblinfo, int numTables, bool oids)
1080 for (i = 0; i < numTables; i++)
1082 /* Skip VIEWs (no data to dump) */
1083 if (tblinfo[i].relkind == RELKIND_VIEW)
1085 /* Skip SEQUENCEs (handled elsewhere) */
1086 if (tblinfo[i].relkind == RELKIND_SEQUENCE)
1089 if (tblinfo[i].dump)
1091 TableDataInfo *tdinfo;
1093 tdinfo = (TableDataInfo *) malloc(sizeof(TableDataInfo));
1095 tdinfo->dobj.objType = DO_TABLE_DATA;
1097 * Note: use tableoid 0 so that this object won't be mistaken
1098 * for something that pg_depend entries apply to.
1100 tdinfo->dobj.catId.tableoid = 0;
1101 tdinfo->dobj.catId.oid = tblinfo[i].dobj.catId.oid;
1102 AssignDumpId(&tdinfo->dobj);
1103 tdinfo->tdtable = &(tblinfo[i]);
1104 tdinfo->oids = oids;
1105 addObjectDependency(&tdinfo->dobj, tblinfo[i].dobj.dumpId);
1113 * dump the database definition
1116 dumpDatabase(Archive *AH)
1118 PQExpBuffer dbQry = createPQExpBuffer();
1119 PQExpBuffer delQry = createPQExpBuffer();
1120 PQExpBuffer creaQry = createPQExpBuffer();
1130 const char *datname,
1135 datname = PQdb(g_conn);
1138 write_msg(NULL, "saving database definition\n");
1140 /* Make sure we are in proper schema */
1141 selectSourceSchema("pg_catalog");
1143 /* Get the database owner and parameters from pg_database */
1144 if (g_fout->remoteVersion >= 70100)
1146 appendPQExpBuffer(dbQry, "SELECT tableoid, oid, "
1147 "(SELECT usename FROM pg_user WHERE usesysid = datdba) as dba, "
1148 "pg_encoding_to_char(encoding) as encoding, "
1151 "WHERE datname = ");
1152 appendStringLiteral(dbQry, datname, true);
1157 * In 7.0, datpath is either the same as datname, or the user-given
1158 * location with "/" and the datname appended. We must strip this
1159 * junk off to produce a correct LOCATION value.
1161 appendPQExpBuffer(dbQry, "SELECT "
1162 "(SELECT oid FROM pg_class WHERE relname = 'pg_database') AS tableoid, "
1164 "(SELECT usename FROM pg_user WHERE usesysid = datdba) as dba, "
1165 "pg_encoding_to_char(encoding) as encoding, "
1166 "CASE WHEN length(datpath) > length(datname) THEN "
1167 "substr(datpath,1,length(datpath)-length(datname)-1) "
1168 "ELSE '' END as datpath "
1170 "WHERE datname = ");
1171 appendStringLiteral(dbQry, datname, true);
1174 res = PQexec(g_conn, dbQry->data);
1175 check_sql_result(res, g_conn, dbQry->data, PGRES_TUPLES_OK);
1177 ntups = PQntuples(res);
1181 write_msg(NULL, "missing pg_database entry for database \"%s\"\n",
1188 write_msg(NULL, "query returned more than one (%d) pg_database entry for database \"%s\"\n",
1193 i_tableoid = PQfnumber(res, "tableoid");
1194 i_oid = PQfnumber(res, "oid");
1195 i_dba = PQfnumber(res, "dba");
1196 i_encoding = PQfnumber(res, "encoding");
1197 i_datpath = PQfnumber(res, "datpath");
1199 dbCatId.tableoid = atooid(PQgetvalue(res, 0, i_tableoid));
1200 dbCatId.oid = atooid(PQgetvalue(res, 0, i_oid));
1201 dba = PQgetvalue(res, 0, i_dba);
1202 encoding = PQgetvalue(res, 0, i_encoding);
1203 datpath = PQgetvalue(res, 0, i_datpath);
1205 appendPQExpBuffer(creaQry, "CREATE DATABASE %s WITH TEMPLATE = template0",
1207 if (strlen(datpath) > 0)
1209 appendPQExpBuffer(creaQry, " LOCATION = ");
1210 appendStringLiteral(creaQry, datpath, true);
1212 if (strlen(encoding) > 0)
1214 appendPQExpBuffer(creaQry, " ENCODING = ");
1215 appendStringLiteral(creaQry, encoding, true);
1217 appendPQExpBuffer(creaQry, ";\n");
1219 appendPQExpBuffer(delQry, "DROP DATABASE %s;\n",
1222 dbDumpId = createDumpId();
1225 dbCatId, /* catalog ID */
1226 dbDumpId, /* dump ID */
1228 NULL, /* Namespace */
1230 "DATABASE", /* Desc */
1231 creaQry->data, /* Create */
1232 delQry->data, /* Del */
1237 NULL); /* Dumper Arg */
1239 /* Dump DB comment if any */
1240 resetPQExpBuffer(dbQry);
1241 appendPQExpBuffer(dbQry, "DATABASE %s", fmtId(datname));
1242 dumpComment(AH, dbQry->data, NULL, "",
1243 dbCatId, 0, dbDumpId);
1247 destroyPQExpBuffer(dbQry);
1248 destroyPQExpBuffer(delQry);
1249 destroyPQExpBuffer(creaQry);
1254 * dumpEncoding: put the correct encoding into the archive
1257 dumpEncoding(Archive *AH)
1262 /* Can't read the encoding from pre-7.3 servers (SHOW isn't a query) */
1263 if (AH->remoteVersion < 70300)
1267 write_msg(NULL, "saving encoding\n");
1269 qry = createPQExpBuffer();
1271 appendPQExpBuffer(qry, "SHOW client_encoding");
1273 res = PQexec(g_conn, qry->data);
1275 check_sql_result(res, g_conn, qry->data, PGRES_TUPLES_OK);
1277 resetPQExpBuffer(qry);
1279 appendPQExpBuffer(qry, "SET client_encoding = ");
1280 appendStringLiteral(qry, PQgetvalue(res, 0, 0), true);
1281 appendPQExpBuffer(qry, ";\n");
1283 ArchiveEntry(AH, nilCatalogId, createDumpId(),
1284 "ENCODING", NULL, "",
1285 "ENCODING", qry->data, "", NULL,
1291 destroyPQExpBuffer(qry);
1301 #define loBufSize 16384
1302 #define loFetchSize 1000
1305 dumpBlobs(Archive *AH, void *arg)
1307 PQExpBuffer oidQry = createPQExpBuffer();
1308 PQExpBuffer oidFetchQry = createPQExpBuffer();
1312 char buf[loBufSize];
1317 write_msg(NULL, "saving large objects\n");
1319 /* Make sure we are in proper schema */
1320 selectSourceSchema("pg_catalog");
1322 /* Cursor to get all BLOB tables */
1323 if (AH->remoteVersion >= 70100)
1324 appendPQExpBuffer(oidQry, "Declare blobOid Cursor for SELECT DISTINCT loid FROM pg_largeobject");
1326 appendPQExpBuffer(oidQry, "Declare blobOid Cursor for SELECT oid from pg_class where relkind = 'l'");
1328 res = PQexec(g_conn, oidQry->data);
1329 check_sql_result(res, g_conn, oidQry->data, PGRES_COMMAND_OK);
1331 /* Fetch for cursor */
1332 appendPQExpBuffer(oidFetchQry, "Fetch %d in blobOid", loFetchSize);
1339 res = PQexec(g_conn, oidFetchQry->data);
1340 check_sql_result(res, g_conn, oidFetchQry->data, PGRES_TUPLES_OK);
1342 /* Process the tuples, if any */
1343 for (i = 0; i < PQntuples(res); i++)
1345 blobOid = atooid(PQgetvalue(res, i, 0));
1347 loFd = lo_open(g_conn, blobOid, INV_READ);
1350 write_msg(NULL, "dumpBlobs(): could not open large object: %s",
1351 PQerrorMessage(g_conn));
1355 StartBlob(AH, blobOid);
1357 /* Now read it in chunks, sending data to archive */
1360 cnt = lo_read(g_conn, loFd, buf, loBufSize);
1363 write_msg(NULL, "dumpBlobs(): error reading large object: %s",
1364 PQerrorMessage(g_conn));
1368 WriteData(AH, buf, cnt);
1372 lo_close(g_conn, loFd);
1374 EndBlob(AH, blobOid);
1377 } while (PQntuples(res) > 0);
1379 destroyPQExpBuffer(oidQry);
1380 destroyPQExpBuffer(oidFetchQry);
1387 * read all namespaces in the system catalogs and return them in the
1388 * NamespaceInfo* structure
1390 * numNamespaces is set to the number of namespaces read in
1393 getNamespaces(int *numNamespaces)
1399 NamespaceInfo *nsinfo;
1407 * Before 7.3, there are no real namespaces; create two dummy entries,
1408 * one for user stuff and one for system stuff.
1410 if (g_fout->remoteVersion < 70300)
1412 nsinfo = (NamespaceInfo *) malloc(2 * sizeof(NamespaceInfo));
1414 nsinfo[0].dobj.objType = DO_NAMESPACE;
1415 nsinfo[0].dobj.catId.tableoid = 0;
1416 nsinfo[0].dobj.catId.oid = 0;
1417 AssignDumpId(&nsinfo[0].dobj);
1418 nsinfo[0].nspname = strdup("");
1419 nsinfo[0].usename = strdup("");
1420 nsinfo[0].nspacl = strdup("");
1422 selectDumpableNamespace(&nsinfo[0]);
1424 nsinfo[1].dobj.objType = DO_NAMESPACE;
1425 nsinfo[1].dobj.catId.tableoid = 0;
1426 nsinfo[1].dobj.catId.oid = 1;
1427 AssignDumpId(&nsinfo[1].dobj);
1428 nsinfo[1].nspname = strdup("pg_catalog");
1429 nsinfo[1].usename = strdup("");
1430 nsinfo[1].nspacl = strdup("");
1432 selectDumpableNamespace(&nsinfo[1]);
1434 g_namespaces = nsinfo;
1435 g_numNamespaces = *numNamespaces = 2;
1440 query = createPQExpBuffer();
1442 /* Make sure we are in proper schema */
1443 selectSourceSchema("pg_catalog");
1446 * we fetch all namespaces including system ones, so that every object
1447 * we read in can be linked to a containing namespace.
1449 appendPQExpBuffer(query, "SELECT tableoid, oid, nspname, "
1450 "(select usename from pg_user where nspowner = usesysid) as usename, "
1452 "FROM pg_namespace");
1454 res = PQexec(g_conn, query->data);
1455 check_sql_result(res, g_conn, query->data, PGRES_TUPLES_OK);
1457 ntups = PQntuples(res);
1459 nsinfo = (NamespaceInfo *) malloc(ntups * sizeof(NamespaceInfo));
1461 i_tableoid = PQfnumber(res, "tableoid");
1462 i_oid = PQfnumber(res, "oid");
1463 i_nspname = PQfnumber(res, "nspname");
1464 i_usename = PQfnumber(res, "usename");
1465 i_nspacl = PQfnumber(res, "nspacl");
1467 for (i = 0; i < ntups; i++)
1469 nsinfo[i].dobj.objType = DO_NAMESPACE;
1470 nsinfo[i].dobj.catId.tableoid = atooid(PQgetvalue(res, i, i_tableoid));
1471 nsinfo[i].dobj.catId.oid = atooid(PQgetvalue(res, i, i_oid));
1472 AssignDumpId(&nsinfo[i].dobj);
1473 nsinfo[i].nspname = strdup(PQgetvalue(res, i, i_nspname));
1474 nsinfo[i].usename = strdup(PQgetvalue(res, i, i_usename));
1475 nsinfo[i].nspacl = strdup(PQgetvalue(res, i, i_nspacl));
1477 /* Decide whether to dump this namespace */
1478 selectDumpableNamespace(&nsinfo[i]);
1480 if (strlen(nsinfo[i].usename) == 0)
1481 write_msg(NULL, "WARNING: owner of schema \"%s\" appears to be invalid\n",
1486 * If the user attempted to dump a specific namespace, check to ensure
1487 * that the specified namespace actually exists.
1489 if (selectSchemaName)
1491 for (i = 0; i < ntups; i++)
1492 if (strcmp(nsinfo[i].nspname, selectSchemaName) == 0)
1495 /* Didn't find a match */
1498 write_msg(NULL, "specified schema \"%s\" does not exist\n",
1505 destroyPQExpBuffer(query);
1507 g_namespaces = nsinfo;
1508 g_numNamespaces = *numNamespaces = ntups;
1515 * given a namespace OID and an object OID, look up the info read by
1518 * NB: for pre-7.3 source database, we use object OID to guess whether it's
1519 * a system object or not. In 7.3 and later there is no guessing.
1521 static NamespaceInfo *
1522 findNamespace(Oid nsoid, Oid objoid)
1526 if (g_fout->remoteVersion >= 70300)
1528 for (i = 0; i < g_numNamespaces; i++)
1530 NamespaceInfo *nsinfo = &g_namespaces[i];
1532 if (nsoid == nsinfo->dobj.catId.oid)
1535 write_msg(NULL, "schema with OID %u does not exist\n", nsoid);
1540 /* This code depends on the layout set up by getNamespaces. */
1541 if (objoid > g_last_builtin_oid)
1542 i = 0; /* user object */
1544 i = 1; /* system object */
1545 return &g_namespaces[i];
1548 return NULL; /* keep compiler quiet */
1553 * read all types in the system catalogs and return them in the
1554 * TypeInfo* structure
1556 * numTypes is set to the number of types read in
1558 * NB: this must run after getFuncs() because we assume we can do
1562 getTypes(int *numTypes)
1567 PQExpBuffer query = createPQExpBuffer();
1583 * we include even the built-in types because those may be used as
1584 * array elements by user-defined types
1586 * we filter out the built-in types when we dump out the types
1588 * same approach for undefined (shell) types
1591 /* Make sure we are in proper schema */
1592 selectSourceSchema("pg_catalog");
1594 if (g_fout->remoteVersion >= 70300)
1596 appendPQExpBuffer(query, "SELECT tableoid, oid, typname, "
1598 "(select usename from pg_user where typowner = usesysid) as usename, "
1599 "typinput::oid as typinput, "
1600 "typoutput::oid as typoutput, typelem, typrelid, "
1601 "CASE WHEN typrelid = 0 THEN ' '::\"char\" "
1602 "ELSE (SELECT relkind FROM pg_class WHERE oid = typrelid) END as typrelkind, "
1603 "typtype, typisdefined "
1606 else if (g_fout->remoteVersion >= 70100)
1608 appendPQExpBuffer(query, "SELECT tableoid, oid, typname, "
1609 "0::oid as typnamespace, "
1610 "(select usename from pg_user where typowner = usesysid) as usename, "
1611 "typinput::oid as typinput, "
1612 "typoutput::oid as typoutput, typelem, typrelid, "
1613 "CASE WHEN typrelid = 0 THEN ' '::\"char\" "
1614 "ELSE (SELECT relkind FROM pg_class WHERE oid = typrelid) END as typrelkind, "
1615 "typtype, typisdefined "
1620 appendPQExpBuffer(query, "SELECT "
1621 "(SELECT oid FROM pg_class WHERE relname = 'pg_type') AS tableoid, "
1623 "0::oid as typnamespace, "
1624 "(select usename from pg_user where typowner = usesysid) as usename, "
1625 "typinput::oid as typinput, "
1626 "typoutput::oid as typoutput, typelem, typrelid, "
1627 "CASE WHEN typrelid = 0 THEN ' '::\"char\" "
1628 "ELSE (SELECT relkind FROM pg_class WHERE oid = typrelid) END as typrelkind, "
1629 "typtype, typisdefined "
1633 res = PQexec(g_conn, query->data);
1634 check_sql_result(res, g_conn, query->data, PGRES_TUPLES_OK);
1636 ntups = PQntuples(res);
1638 tinfo = (TypeInfo *) malloc(ntups * sizeof(TypeInfo));
1640 i_tableoid = PQfnumber(res, "tableoid");
1641 i_oid = PQfnumber(res, "oid");
1642 i_typname = PQfnumber(res, "typname");
1643 i_typnamespace = PQfnumber(res, "typnamespace");
1644 i_usename = PQfnumber(res, "usename");
1645 i_typinput = PQfnumber(res, "typinput");
1646 i_typoutput = PQfnumber(res, "typoutput");
1647 i_typelem = PQfnumber(res, "typelem");
1648 i_typrelid = PQfnumber(res, "typrelid");
1649 i_typrelkind = PQfnumber(res, "typrelkind");
1650 i_typtype = PQfnumber(res, "typtype");
1651 i_typisdefined = PQfnumber(res, "typisdefined");
1653 for (i = 0; i < ntups; i++)
1658 tinfo[i].dobj.objType = DO_TYPE;
1659 tinfo[i].dobj.catId.tableoid = atooid(PQgetvalue(res, i, i_tableoid));
1660 tinfo[i].dobj.catId.oid = atooid(PQgetvalue(res, i, i_oid));
1661 AssignDumpId(&tinfo[i].dobj);
1662 tinfo[i].typname = strdup(PQgetvalue(res, i, i_typname));
1663 tinfo[i].typnamespace = findNamespace(atooid(PQgetvalue(res, i, i_typnamespace)),
1664 tinfo[i].dobj.catId.oid);
1665 tinfo[i].usename = strdup(PQgetvalue(res, i, i_usename));
1666 tinfo[i].typinput = atooid(PQgetvalue(res, i, i_typinput));
1667 typoutput = atooid(PQgetvalue(res, i, i_typoutput));
1668 tinfo[i].typelem = atooid(PQgetvalue(res, i, i_typelem));
1669 tinfo[i].typrelid = atooid(PQgetvalue(res, i, i_typrelid));
1670 tinfo[i].typrelkind = *PQgetvalue(res, i, i_typrelkind);
1671 tinfo[i].typtype = *PQgetvalue(res, i, i_typtype);
1674 * check for user-defined array types, omit system generated ones
1676 if (OidIsValid(tinfo[i].typelem) &&
1677 tinfo[i].typname[0] != '_')
1678 tinfo[i].isArray = true;
1680 tinfo[i].isArray = false;
1682 if (strcmp(PQgetvalue(res, i, i_typisdefined), "t") == 0)
1683 tinfo[i].isDefined = true;
1685 tinfo[i].isDefined = false;
1688 * If it's a domain, fetch info about its constraints, if any
1690 tinfo[i].nDomChecks = 0;
1691 tinfo[i].domChecks = NULL;
1692 if (tinfo[i].typtype == 'd')
1693 getDomainConstraints(&(tinfo[i]));
1696 * Make sure there are dependencies from the type to its input and
1697 * output functions. (We don't worry about typsend, typreceive, or
1698 * typanalyze since those are only valid in 7.4 and later, wherein
1699 * the standard dependency mechanism will pick them up.)
1701 funcInfo = findFuncByOid(tinfo[i].typinput);
1703 addObjectDependency(&tinfo[i].dobj,
1704 funcInfo->dobj.dumpId);
1705 funcInfo = findFuncByOid(typoutput);
1707 addObjectDependency(&tinfo[i].dobj,
1708 funcInfo->dobj.dumpId);
1710 if (strlen(tinfo[i].usename) == 0 && tinfo[i].isDefined)
1711 write_msg(NULL, "WARNING: owner of data type \"%s\" appears to be invalid\n",
1719 destroyPQExpBuffer(query);
1726 * read all operators in the system catalogs and return them in the
1727 * OprInfo* structure
1729 * numOprs is set to the number of operators read in
1732 getOperators(int *numOprs)
1737 PQExpBuffer query = createPQExpBuffer();
1747 * find all operators, including builtin operators; we filter out
1748 * system-defined operators at dump-out time.
1751 /* Make sure we are in proper schema */
1752 selectSourceSchema("pg_catalog");
1754 if (g_fout->remoteVersion >= 70300)
1756 appendPQExpBuffer(query, "SELECT tableoid, oid, oprname, "
1758 "(select usename from pg_user where oprowner = usesysid) as usename, "
1759 "oprcode::oid as oprcode "
1760 "FROM pg_operator");
1762 else if (g_fout->remoteVersion >= 70100)
1764 appendPQExpBuffer(query, "SELECT tableoid, oid, oprname, "
1765 "0::oid as oprnamespace, "
1766 "(select usename from pg_user where oprowner = usesysid) as usename, "
1767 "oprcode::oid as oprcode "
1768 "FROM pg_operator");
1772 appendPQExpBuffer(query, "SELECT "
1773 "(SELECT oid FROM pg_class WHERE relname = 'pg_operator') AS tableoid, "
1775 "0::oid as oprnamespace, "
1776 "(select usename from pg_user where oprowner = usesysid) as usename, "
1777 "oprcode::oid as oprcode "
1778 "FROM pg_operator");
1781 res = PQexec(g_conn, query->data);
1782 check_sql_result(res, g_conn, query->data, PGRES_TUPLES_OK);
1784 ntups = PQntuples(res);
1787 oprinfo = (OprInfo *) malloc(ntups * sizeof(OprInfo));
1789 i_tableoid = PQfnumber(res, "tableoid");
1790 i_oid = PQfnumber(res, "oid");
1791 i_oprname = PQfnumber(res, "oprname");
1792 i_oprnamespace = PQfnumber(res, "oprnamespace");
1793 i_usename = PQfnumber(res, "usename");
1794 i_oprcode = PQfnumber(res, "oprcode");
1796 for (i = 0; i < ntups; i++)
1798 oprinfo[i].dobj.objType = DO_OPERATOR;
1799 oprinfo[i].dobj.catId.tableoid = atooid(PQgetvalue(res, i, i_tableoid));
1800 oprinfo[i].dobj.catId.oid = atooid(PQgetvalue(res, i, i_oid));
1801 AssignDumpId(&oprinfo[i].dobj);
1802 oprinfo[i].oprname = strdup(PQgetvalue(res, i, i_oprname));
1803 oprinfo[i].oprnamespace = findNamespace(atooid(PQgetvalue(res, i, i_oprnamespace)),
1804 oprinfo[i].dobj.catId.oid);
1805 oprinfo[i].usename = strdup(PQgetvalue(res, i, i_usename));
1806 oprinfo[i].oprcode = atooid(PQgetvalue(res, i, i_oprcode));
1808 if (strlen(oprinfo[i].usename) == 0)
1809 write_msg(NULL, "WARNING: owner of operator \"%s\" appears to be invalid\n",
1810 oprinfo[i].oprname);
1815 destroyPQExpBuffer(query);
1822 * read all conversions in the system catalogs and return them in the
1823 * ConvInfo* structure
1825 * numConversions is set to the number of conversions read in
1828 getConversions(int *numConversions)
1833 PQExpBuffer query = createPQExpBuffer();
1841 /* Conversions didn't exist pre-7.3 */
1842 if (g_fout->remoteVersion < 70300) {
1843 *numConversions = 0;
1848 * find all conversions, including builtin conversions; we filter out
1849 * system-defined conversions at dump-out time.
1852 /* Make sure we are in proper schema */
1853 selectSourceSchema("pg_catalog");
1855 appendPQExpBuffer(query, "SELECT tableoid, oid, conname, "
1857 "(select usename from pg_user where conowner = usesysid) as usename "
1858 "FROM pg_conversion");
1860 res = PQexec(g_conn, query->data);
1861 check_sql_result(res, g_conn, query->data, PGRES_TUPLES_OK);
1863 ntups = PQntuples(res);
1864 *numConversions = ntups;
1866 convinfo = (ConvInfo *) malloc(ntups * sizeof(ConvInfo));
1868 i_tableoid = PQfnumber(res, "tableoid");
1869 i_oid = PQfnumber(res, "oid");
1870 i_conname = PQfnumber(res, "conname");
1871 i_connamespace = PQfnumber(res, "connamespace");
1872 i_usename = PQfnumber(res, "usename");
1874 for (i = 0; i < ntups; i++)
1876 convinfo[i].dobj.objType = DO_CONVERSION;
1877 convinfo[i].dobj.catId.tableoid = atooid(PQgetvalue(res, i, i_tableoid));
1878 convinfo[i].dobj.catId.oid = atooid(PQgetvalue(res, i, i_oid));
1879 AssignDumpId(&convinfo[i].dobj);
1880 convinfo[i].conname = strdup(PQgetvalue(res, i, i_conname));
1881 convinfo[i].connamespace = findNamespace(atooid(PQgetvalue(res, i, i_connamespace)),
1882 convinfo[i].dobj.catId.oid);
1883 convinfo[i].usename = strdup(PQgetvalue(res, i, i_usename));
1888 destroyPQExpBuffer(query);
1895 * read all opclasses in the system catalogs and return them in the
1896 * OpclassInfo* structure
1898 * numOpclasses is set to the number of opclasses read in
1901 getOpclasses(int *numOpclasses)
1906 PQExpBuffer query = createPQExpBuffer();
1907 OpclassInfo *opcinfo;
1915 * find all opclasses, including builtin opclasses; we filter out
1916 * system-defined opclasses at dump-out time.
1919 /* Make sure we are in proper schema */
1920 selectSourceSchema("pg_catalog");
1922 if (g_fout->remoteVersion >= 70300)
1924 appendPQExpBuffer(query, "SELECT tableoid, oid, opcname, "
1926 "(select usename from pg_user where opcowner = usesysid) as usename "
1929 else if (g_fout->remoteVersion >= 70100)
1931 appendPQExpBuffer(query, "SELECT tableoid, oid, opcname, "
1932 "0::oid as opcnamespace, "
1933 "''::name as usename "
1938 appendPQExpBuffer(query, "SELECT "
1939 "(SELECT oid FROM pg_class WHERE relname = 'pg_opclass') AS tableoid, "
1941 "0::oid as opcnamespace, "
1942 "''::name as usename "
1946 res = PQexec(g_conn, query->data);
1947 check_sql_result(res, g_conn, query->data, PGRES_TUPLES_OK);
1949 ntups = PQntuples(res);
1950 *numOpclasses = ntups;
1952 opcinfo = (OpclassInfo *) malloc(ntups * sizeof(OpclassInfo));
1954 i_tableoid = PQfnumber(res, "tableoid");
1955 i_oid = PQfnumber(res, "oid");
1956 i_opcname = PQfnumber(res, "opcname");
1957 i_opcnamespace = PQfnumber(res, "opcnamespace");
1958 i_usename = PQfnumber(res, "usename");
1960 for (i = 0; i < ntups; i++)
1962 opcinfo[i].dobj.objType = DO_OPCLASS;
1963 opcinfo[i].dobj.catId.tableoid = atooid(PQgetvalue(res, i, i_tableoid));
1964 opcinfo[i].dobj.catId.oid = atooid(PQgetvalue(res, i, i_oid));
1965 AssignDumpId(&opcinfo[i].dobj);
1966 opcinfo[i].opcname = strdup(PQgetvalue(res, i, i_opcname));
1967 opcinfo[i].opcnamespace = findNamespace(atooid(PQgetvalue(res, i, i_opcnamespace)),
1968 opcinfo[i].dobj.catId.oid);
1969 opcinfo[i].usename = strdup(PQgetvalue(res, i, i_usename));
1971 if (g_fout->remoteVersion >= 70300)
1973 if (strlen(opcinfo[i].usename) == 0)
1974 write_msg(NULL, "WARNING: owner of operator class \"%s\" appears to be invalid\n",
1975 opcinfo[i].opcname);
1981 destroyPQExpBuffer(query);
1988 * read all the user-defined aggregates in the system catalogs and
1989 * return them in the AggInfo* structure
1991 * numAggs is set to the number of aggregates read in
1994 getAggregates(int *numAggs)
1999 PQExpBuffer query = createPQExpBuffer();
2009 /* Make sure we are in proper schema */
2010 selectSourceSchema("pg_catalog");
2012 /* find all user-defined aggregates */
2014 if (g_fout->remoteVersion >= 70300)
2016 appendPQExpBuffer(query, "SELECT tableoid, oid, proname as aggname, "
2017 "pronamespace as aggnamespace, "
2018 "proargtypes[0] as aggbasetype, "
2019 "(select usename from pg_user where proowner = usesysid) as usename, "
2023 "AND pronamespace != "
2024 "(select oid from pg_namespace where nspname = 'pg_catalog')");
2026 else if (g_fout->remoteVersion >= 70100)
2028 appendPQExpBuffer(query, "SELECT tableoid, oid, aggname, "
2029 "0::oid as aggnamespace, "
2031 "(select usename from pg_user where aggowner = usesysid) as usename, "
2033 "FROM pg_aggregate "
2034 "where oid > '%u'::oid",
2035 g_last_builtin_oid);
2039 appendPQExpBuffer(query, "SELECT "
2040 "(SELECT oid FROM pg_class WHERE relname = 'pg_aggregate') AS tableoid, "
2042 "0::oid as aggnamespace, "
2044 "(select usename from pg_user where aggowner = usesysid) as usename, "
2046 "FROM pg_aggregate "
2047 "where oid > '%u'::oid",
2048 g_last_builtin_oid);
2051 res = PQexec(g_conn, query->data);
2052 check_sql_result(res, g_conn, query->data, PGRES_TUPLES_OK);
2054 ntups = PQntuples(res);
2057 agginfo = (AggInfo *) malloc(ntups * sizeof(AggInfo));
2059 i_tableoid = PQfnumber(res, "tableoid");
2060 i_oid = PQfnumber(res, "oid");
2061 i_aggname = PQfnumber(res, "aggname");
2062 i_aggnamespace = PQfnumber(res, "aggnamespace");
2063 i_aggbasetype = PQfnumber(res, "aggbasetype");
2064 i_usename = PQfnumber(res, "usename");
2065 i_aggacl = PQfnumber(res, "aggacl");
2067 for (i = 0; i < ntups; i++)
2069 agginfo[i].aggfn.dobj.objType = DO_AGG;
2070 agginfo[i].aggfn.dobj.catId.tableoid = atooid(PQgetvalue(res, i, i_tableoid));
2071 agginfo[i].aggfn.dobj.catId.oid = atooid(PQgetvalue(res, i, i_oid));
2072 AssignDumpId(&agginfo[i].aggfn.dobj);
2073 agginfo[i].aggfn.proname = strdup(PQgetvalue(res, i, i_aggname));
2074 agginfo[i].aggfn.pronamespace = findNamespace(atooid(PQgetvalue(res, i, i_aggnamespace)),
2075 agginfo[i].aggfn.dobj.catId.oid);
2076 agginfo[i].aggfn.usename = strdup(PQgetvalue(res, i, i_usename));
2077 if (strlen(agginfo[i].aggfn.usename) == 0)
2078 write_msg(NULL, "WARNING: owner of aggregate function \"%s\" appears to be invalid\n",
2079 agginfo[i].aggfn.proname);
2080 agginfo[i].aggfn.lang = InvalidOid; /* not currently interesting */
2081 agginfo[i].aggfn.nargs = 1;
2082 agginfo[i].aggfn.argtypes = (Oid *) malloc(sizeof(Oid));
2083 agginfo[i].aggfn.argtypes[0] = atooid(PQgetvalue(res, i, i_aggbasetype));
2084 agginfo[i].aggfn.prorettype = InvalidOid; /* not saved */
2085 agginfo[i].aggfn.proacl = strdup(PQgetvalue(res, i, i_aggacl));
2086 agginfo[i].anybasetype = false; /* computed when it's dumped */
2087 agginfo[i].fmtbasetype = NULL; /* computed when it's dumped */
2092 destroyPQExpBuffer(query);
2099 * read all the user-defined functions in the system catalogs and
2100 * return them in the FuncInfo* structure
2102 * numFuncs is set to the number of functions read in
2105 getFuncs(int *numFuncs)
2110 PQExpBuffer query = createPQExpBuffer();
2123 /* Make sure we are in proper schema */
2124 selectSourceSchema("pg_catalog");
2126 /* find all user-defined funcs */
2128 if (g_fout->remoteVersion >= 70300)
2130 appendPQExpBuffer(query,
2131 "SELECT tableoid, oid, proname, prolang, "
2132 "pronargs, proargtypes, prorettype, proacl, "
2134 "(select usename from pg_user where proowner = usesysid) as usename "
2136 "WHERE NOT proisagg "
2137 "AND pronamespace != "
2138 "(select oid from pg_namespace where nspname = 'pg_catalog')");
2140 else if (g_fout->remoteVersion >= 70100)
2142 appendPQExpBuffer(query,
2143 "SELECT tableoid, oid, proname, prolang, "
2144 "pronargs, proargtypes, prorettype, "
2145 "'{=X}' as proacl, "
2146 "0::oid as pronamespace, "
2147 "(select usename from pg_user where proowner = usesysid) as usename "
2149 "where pg_proc.oid > '%u'::oid",
2150 g_last_builtin_oid);
2154 appendPQExpBuffer(query,
2156 "(SELECT oid FROM pg_class WHERE relname = 'pg_proc') AS tableoid, "
2157 "oid, proname, prolang, "
2158 "pronargs, proargtypes, prorettype, "
2159 "'{=X}' as proacl, "
2160 "0::oid as pronamespace, "
2161 "(select usename from pg_user where proowner = usesysid) as usename "
2163 "where pg_proc.oid > '%u'::oid",
2164 g_last_builtin_oid);
2167 res = PQexec(g_conn, query->data);
2168 check_sql_result(res, g_conn, query->data, PGRES_TUPLES_OK);
2170 ntups = PQntuples(res);
2174 finfo = (FuncInfo *) calloc(ntups, sizeof(FuncInfo));
2176 i_tableoid = PQfnumber(res, "tableoid");
2177 i_oid = PQfnumber(res, "oid");
2178 i_proname = PQfnumber(res, "proname");
2179 i_pronamespace = PQfnumber(res, "pronamespace");
2180 i_usename = PQfnumber(res, "usename");
2181 i_prolang = PQfnumber(res, "prolang");
2182 i_pronargs = PQfnumber(res, "pronargs");
2183 i_proargtypes = PQfnumber(res, "proargtypes");
2184 i_prorettype = PQfnumber(res, "prorettype");
2185 i_proacl = PQfnumber(res, "proacl");
2187 for (i = 0; i < ntups; i++)
2189 finfo[i].dobj.objType = DO_FUNC;
2190 finfo[i].dobj.catId.tableoid = atooid(PQgetvalue(res, i, i_tableoid));
2191 finfo[i].dobj.catId.oid = atooid(PQgetvalue(res, i, i_oid));
2192 AssignDumpId(&finfo[i].dobj);
2193 finfo[i].proname = strdup(PQgetvalue(res, i, i_proname));
2194 finfo[i].pronamespace = findNamespace(atooid(PQgetvalue(res, i, i_pronamespace)),
2195 finfo[i].dobj.catId.oid);
2196 finfo[i].usename = strdup(PQgetvalue(res, i, i_usename));
2197 finfo[i].lang = atooid(PQgetvalue(res, i, i_prolang));
2198 finfo[i].prorettype = atooid(PQgetvalue(res, i, i_prorettype));
2199 finfo[i].proacl = strdup(PQgetvalue(res, i, i_proacl));
2200 finfo[i].nargs = atoi(PQgetvalue(res, i, i_pronargs));
2201 if (finfo[i].nargs == 0)
2202 finfo[i].argtypes = NULL;
2205 finfo[i].argtypes = (Oid *) malloc(finfo[i].nargs * sizeof(Oid));
2206 parseOidArray(PQgetvalue(res, i, i_proargtypes),
2207 finfo[i].argtypes, finfo[i].nargs);
2210 if (strlen(finfo[i].usename) == 0)
2211 write_msg(NULL, "WARNING: owner of function \"%s\" appears to be invalid\n",
2217 destroyPQExpBuffer(query);
2224 * read all the user-defined tables (no indexes, no catalogs)
2225 * in the system catalogs return them in the TableInfo* structure
2227 * numTables is set to the number of tables read in
2230 getTables(int *numTables)
2235 PQExpBuffer query = createPQExpBuffer();
2236 PQExpBuffer delqry = createPQExpBuffer();
2237 PQExpBuffer lockquery = createPQExpBuffer();
2254 /* Make sure we are in proper schema */
2255 selectSourceSchema("pg_catalog");
2258 * Find all the tables (including views and sequences).
2260 * We include system catalogs, so that we can work if a user table is
2261 * defined to inherit from a system catalog (pretty weird, but...)
2263 * We ignore tables that are not type 'r' (ordinary relation) or 'S'
2264 * (sequence) or 'v' (view).
2266 * Note: in this phase we should collect only a minimal amount of
2267 * information about each table, basically just enough to decide if it
2268 * is interesting. We must fetch all tables in this phase because
2269 * otherwise we cannot correctly identify inherited columns, serial
2273 if (g_fout->remoteVersion >= 70300)
2276 * Left join to pick up dependency info linking sequences to their
2277 * serial column, if any
2279 appendPQExpBuffer(query,
2280 "SELECT c.tableoid, c.oid, relname, "
2281 "relacl, relkind, relnamespace, "
2282 "(select usename from pg_user where relowner = usesysid) as usename, "
2283 "relchecks, reltriggers, "
2284 "relhasindex, relhasrules, relhasoids, "
2285 "d.refobjid as owning_tab, "
2286 "d.refobjsubid as owning_col "
2288 "left join pg_depend d on "
2289 "(c.relkind = '%c' and "
2290 "d.classid = c.tableoid and d.objid = c.oid and "
2291 "d.objsubid = 0 and "
2292 "d.refclassid = c.tableoid and d.deptype = 'i') "
2293 "where relkind in ('%c', '%c', '%c') "
2296 RELKIND_RELATION, RELKIND_SEQUENCE, RELKIND_VIEW);
2298 else if (g_fout->remoteVersion >= 70200)
2300 appendPQExpBuffer(query,
2301 "SELECT tableoid, oid, relname, relacl, relkind, "
2302 "0::oid as relnamespace, "
2303 "(select usename from pg_user where relowner = usesysid) as usename, "
2304 "relchecks, reltriggers, "
2305 "relhasindex, relhasrules, relhasoids, "
2306 "NULL::oid as owning_tab, "
2307 "NULL::int4 as owning_col "
2309 "where relkind in ('%c', '%c', '%c') "
2311 RELKIND_RELATION, RELKIND_SEQUENCE, RELKIND_VIEW);
2313 else if (g_fout->remoteVersion >= 70100)
2315 /* all tables have oids in 7.1 */
2316 appendPQExpBuffer(query,
2317 "SELECT tableoid, oid, relname, relacl, relkind, "
2318 "0::oid as relnamespace, "
2319 "(select usename from pg_user where relowner = usesysid) as usename, "
2320 "relchecks, reltriggers, "
2321 "relhasindex, relhasrules, "
2322 "'t'::bool as relhasoids, "
2323 "NULL::oid as owning_tab, "
2324 "NULL::int4 as owning_col "
2326 "where relkind in ('%c', '%c', '%c') "
2328 RELKIND_RELATION, RELKIND_SEQUENCE, RELKIND_VIEW);
2333 * Before 7.1, view relkind was not set to 'v', so we must check
2334 * if we have a view by looking for a rule in pg_rewrite.
2336 appendPQExpBuffer(query,
2338 "(SELECT oid FROM pg_class WHERE relname = 'pg_class') AS tableoid, "
2339 "oid, relname, relacl, "
2340 "CASE WHEN relhasrules and relkind = 'r' "
2341 " and EXISTS(SELECT rulename FROM pg_rewrite r WHERE "
2342 " r.ev_class = c.oid AND r.ev_type = '1') "
2343 "THEN '%c'::\"char\" "
2344 "ELSE relkind END AS relkind,"
2345 "0::oid as relnamespace, "
2346 "(select usename from pg_user where relowner = usesysid) as usename, "
2347 "relchecks, reltriggers, "
2348 "relhasindex, relhasrules, "
2349 "'t'::bool as relhasoids, "
2350 "NULL::oid as owning_tab, "
2351 "NULL::int4 as owning_col "
2353 "where relkind in ('%c', '%c') "
2356 RELKIND_RELATION, RELKIND_SEQUENCE);
2359 res = PQexec(g_conn, query->data);
2360 check_sql_result(res, g_conn, query->data, PGRES_TUPLES_OK);
2362 ntups = PQntuples(res);
2367 * Extract data from result and lock dumpable tables. We do the
2368 * locking before anything else, to minimize the window wherein a
2369 * table could disappear under us.
2371 * Note that we have to save info about all tables here, even when
2372 * dumping only one, because we don't yet know which tables might be
2373 * inheritance ancestors of the target table.
2375 tblinfo = (TableInfo *) calloc(ntups, sizeof(TableInfo));
2377 i_reltableoid = PQfnumber(res, "tableoid");
2378 i_reloid = PQfnumber(res, "oid");
2379 i_relname = PQfnumber(res, "relname");
2380 i_relnamespace = PQfnumber(res, "relnamespace");
2381 i_relacl = PQfnumber(res, "relacl");
2382 i_relkind = PQfnumber(res, "relkind");
2383 i_usename = PQfnumber(res, "usename");
2384 i_relchecks = PQfnumber(res, "relchecks");
2385 i_reltriggers = PQfnumber(res, "reltriggers");
2386 i_relhasindex = PQfnumber(res, "relhasindex");
2387 i_relhasrules = PQfnumber(res, "relhasrules");
2388 i_relhasoids = PQfnumber(res, "relhasoids");
2389 i_owning_tab = PQfnumber(res, "owning_tab");
2390 i_owning_col = PQfnumber(res, "owning_col");
2392 for (i = 0; i < ntups; i++)
2394 tblinfo[i].dobj.objType = DO_TABLE;
2395 tblinfo[i].dobj.catId.tableoid = atooid(PQgetvalue(res, i, i_reltableoid));
2396 tblinfo[i].dobj.catId.oid = atooid(PQgetvalue(res, i, i_reloid));
2397 AssignDumpId(&tblinfo[i].dobj);
2398 tblinfo[i].relname = strdup(PQgetvalue(res, i, i_relname));
2399 tblinfo[i].relnamespace = findNamespace(atooid(PQgetvalue(res, i, i_relnamespace)),
2400 tblinfo[i].dobj.catId.oid);
2401 tblinfo[i].usename = strdup(PQgetvalue(res, i, i_usename));
2402 tblinfo[i].relacl = strdup(PQgetvalue(res, i, i_relacl));
2403 tblinfo[i].relkind = *(PQgetvalue(res, i, i_relkind));
2404 tblinfo[i].hasindex = (strcmp(PQgetvalue(res, i, i_relhasindex), "t") == 0);
2405 tblinfo[i].hasrules = (strcmp(PQgetvalue(res, i, i_relhasrules), "t") == 0);
2406 tblinfo[i].hasoids = (strcmp(PQgetvalue(res, i, i_relhasoids), "t") == 0);
2407 tblinfo[i].ncheck = atoi(PQgetvalue(res, i, i_relchecks));
2408 tblinfo[i].ntrig = atoi(PQgetvalue(res, i, i_reltriggers));
2409 if (PQgetisnull(res, i, i_owning_tab))
2411 tblinfo[i].owning_tab = InvalidOid;
2412 tblinfo[i].owning_col = 0;
2416 tblinfo[i].owning_tab = atooid(PQgetvalue(res, i, i_owning_tab));
2417 tblinfo[i].owning_col = atoi(PQgetvalue(res, i, i_owning_col));
2420 /* other fields were zeroed above */
2423 * Decide whether we want to dump this table. Sequences owned by
2424 * serial columns are never dumpable on their own; we will
2425 * transpose their owning table's dump flag to them below.
2427 if (OidIsValid(tblinfo[i].owning_tab))
2428 tblinfo[i].dump = false;
2430 selectDumpableTable(&tblinfo[i]);
2431 tblinfo[i].interesting = tblinfo[i].dump;
2434 * Read-lock target tables to make sure they aren't DROPPED or
2435 * altered in schema before we get around to dumping them.
2437 * Note that we don't explicitly lock parents of the target tables;
2438 * we assume our lock on the child is enough to prevent schema
2439 * alterations to parent tables.
2441 * NOTE: it'd be kinda nice to lock views and sequences too, not only
2442 * plain tables, but the backend doesn't presently allow that.
2444 if (tblinfo[i].dump && tblinfo[i].relkind == RELKIND_RELATION)
2446 resetPQExpBuffer(lockquery);
2447 appendPQExpBuffer(lockquery,
2448 "LOCK TABLE %s IN ACCESS SHARE MODE",
2449 fmtQualifiedId(tblinfo[i].relnamespace->nspname,
2450 tblinfo[i].relname));
2451 do_sql_command(g_conn, lockquery->data);
2454 /* Emit notice if join for owner failed */
2455 if (strlen(tblinfo[i].usename) == 0)
2456 write_msg(NULL, "WARNING: owner of table \"%s\" appears to be invalid\n",
2457 tblinfo[i].relname);
2461 * If the user is attempting to dump a specific table, check to ensure
2462 * that the specified table actually exists. (This is a bit simplistic
2463 * since we don't fully check the combination of -n and -t switches.)
2465 if (selectTableName)
2467 for (i = 0; i < ntups; i++)
2468 if (strcmp(tblinfo[i].relname, selectTableName) == 0)
2471 /* Didn't find a match */
2474 write_msg(NULL, "specified table \"%s\" does not exist\n",
2481 destroyPQExpBuffer(query);
2482 destroyPQExpBuffer(delqry);
2483 destroyPQExpBuffer(lockquery);
2490 * read all the inheritance information
2491 * from the system catalogs return them in the InhInfo* structure
2493 * numInherits is set to the number of pairs read in
2496 getInherits(int *numInherits)
2501 PQExpBuffer query = createPQExpBuffer();
2507 /* Make sure we are in proper schema */
2508 selectSourceSchema("pg_catalog");
2510 /* find all the inheritance information */
2512 appendPQExpBuffer(query, "SELECT inhrelid, inhparent from pg_inherits");
2514 res = PQexec(g_conn, query->data);
2515 check_sql_result(res, g_conn, query->data, PGRES_TUPLES_OK);
2517 ntups = PQntuples(res);
2519 *numInherits = ntups;
2521 inhinfo = (InhInfo *) malloc(ntups * sizeof(InhInfo));
2523 i_inhrelid = PQfnumber(res, "inhrelid");
2524 i_inhparent = PQfnumber(res, "inhparent");
2526 for (i = 0; i < ntups; i++)
2528 inhinfo[i].inhrelid = atooid(PQgetvalue(res, i, i_inhrelid));
2529 inhinfo[i].inhparent = atooid(PQgetvalue(res, i, i_inhparent));
2534 destroyPQExpBuffer(query);
2541 * get information about every index on a dumpable table
2543 * Note: index data is not returned directly to the caller, but it
2544 * does get entered into the DumpableObject tables.
2547 getIndexes(TableInfo tblinfo[], int numTables)
2551 PQExpBuffer query = createPQExpBuffer();
2554 ConstraintInfo *constrinfo;
2568 for (i = 0; i < numTables; i++)
2570 TableInfo *tbinfo = &tblinfo[i];
2572 /* Only plain tables have indexes */
2573 if (tbinfo->relkind != RELKIND_RELATION || !tbinfo->hasindex)
2580 write_msg(NULL, "reading indexes for table \"%s\"\n",
2583 /* Make sure we are in proper schema so indexdef is right */
2584 selectSourceSchema(tbinfo->relnamespace->nspname);
2587 * The point of the messy-looking outer join is to find a
2588 * constraint that is related by an internal dependency link to
2589 * the index. If we find one, create a CONSTRAINT entry linked
2590 * to the INDEX entry. We assume an index won't have more than
2591 * one internal dependency.
2593 resetPQExpBuffer(query);
2594 if (g_fout->remoteVersion >= 70300)
2596 appendPQExpBuffer(query,
2597 "SELECT t.tableoid, t.oid, "
2598 "t.relname as indexname, "
2599 "pg_catalog.pg_get_indexdef(i.indexrelid) as indexdef, "
2600 "t.relnatts as indnkeys, "
2601 "i.indkey, i.indisclustered, "
2602 "c.contype, c.conname, "
2603 "c.tableoid as contableoid, "
2605 "FROM pg_catalog.pg_index i "
2606 "JOIN pg_catalog.pg_class t ON (t.oid = i.indexrelid) "
2607 "LEFT JOIN pg_catalog.pg_depend d "
2608 "ON (d.classid = t.tableoid "
2609 "AND d.objid = t.oid "
2610 "AND d.deptype = 'i') "
2611 "LEFT JOIN pg_catalog.pg_constraint c "
2612 "ON (d.refclassid = c.tableoid "
2613 "AND d.refobjid = c.oid) "
2614 "WHERE i.indrelid = '%u'::pg_catalog.oid "
2615 "ORDER BY indexname",
2616 tbinfo->dobj.catId.oid);
2618 else if (g_fout->remoteVersion >= 70100)
2620 appendPQExpBuffer(query,
2621 "SELECT t.tableoid, t.oid, "
2622 "t.relname as indexname, "
2623 "pg_get_indexdef(i.indexrelid) as indexdef, "
2624 "t.relnatts as indnkeys, "
2625 "i.indkey, false as indisclustered, "
2626 "CASE WHEN i.indisprimary THEN 'p'::char "
2627 "ELSE '0'::char END as contype, "
2628 "t.relname as conname, "
2629 "0::oid as contableoid, "
2631 "FROM pg_index i, pg_class t "
2632 "WHERE t.oid = i.indexrelid "
2633 "AND i.indrelid = '%u'::oid "
2634 "ORDER BY indexname",
2635 tbinfo->dobj.catId.oid);
2639 appendPQExpBuffer(query,
2641 "(SELECT oid FROM pg_class WHERE relname = 'pg_class') AS tableoid, "
2643 "t.relname as indexname, "
2644 "pg_get_indexdef(i.indexrelid) as indexdef, "
2645 "t.relnatts as indnkeys, "
2646 "i.indkey, false as indisclustered, "
2647 "CASE WHEN i.indisprimary THEN 'p'::char "
2648 "ELSE '0'::char END as contype, "
2649 "t.relname as conname, "
2650 "0::oid as contableoid, "
2652 "FROM pg_index i, pg_class t "
2653 "WHERE t.oid = i.indexrelid "
2654 "AND i.indrelid = '%u'::oid "
2655 "ORDER BY indexname",
2656 tbinfo->dobj.catId.oid);
2659 res = PQexec(g_conn, query->data);
2660 check_sql_result(res, g_conn, query->data, PGRES_TUPLES_OK);
2662 ntups = PQntuples(res);
2664 i_tableoid = PQfnumber(res, "tableoid");
2665 i_oid = PQfnumber(res, "oid");
2666 i_indexname = PQfnumber(res, "indexname");
2667 i_indexdef = PQfnumber(res, "indexdef");
2668 i_indnkeys = PQfnumber(res, "indnkeys");
2669 i_indkey = PQfnumber(res, "indkey");
2670 i_indisclustered = PQfnumber(res, "indisclustered");
2671 i_contype = PQfnumber(res, "contype");
2672 i_conname = PQfnumber(res, "conname");
2673 i_contableoid = PQfnumber(res, "contableoid");
2674 i_conoid = PQfnumber(res, "conoid");
2676 indxinfo = (IndxInfo *) malloc(ntups * sizeof(IndxInfo));
2677 constrinfo = (ConstraintInfo *) malloc(ntups * sizeof(ConstraintInfo));
2679 for (j = 0; j < ntups; j++)
2683 indxinfo[j].dobj.objType = DO_INDEX;
2684 indxinfo[j].dobj.catId.tableoid = atooid(PQgetvalue(res, j, i_tableoid));
2685 indxinfo[j].dobj.catId.oid = atooid(PQgetvalue(res, j, i_oid));
2686 AssignDumpId(&indxinfo[j].dobj);
2687 indxinfo[j].indexname = strdup(PQgetvalue(res, j, i_indexname));
2688 indxinfo[j].indextable = tbinfo;
2689 indxinfo[j].indexdef = strdup(PQgetvalue(res, j, i_indexdef));
2690 indxinfo[j].indnkeys = atoi(PQgetvalue(res, j, i_indnkeys));
2692 * In pre-7.4 releases, indkeys may contain more entries than
2693 * indnkeys says (since indnkeys will be 1 for a functional
2694 * index). We don't actually care about this case since we don't
2695 * examine indkeys except for indexes associated with PRIMARY
2696 * and UNIQUE constraints, which are never functional indexes.
2697 * But we have to allocate enough space to keep parseOidArray
2700 indxinfo[j].indkeys = (Oid *) malloc(INDEX_MAX_KEYS * sizeof(Oid));
2701 parseOidArray(PQgetvalue(res, j, i_indkey),
2702 indxinfo[j].indkeys, INDEX_MAX_KEYS);
2703 indxinfo[j].indisclustered = (PQgetvalue(res, j, i_indisclustered)[0] == 't');
2704 contype = *(PQgetvalue(res, j, i_contype));
2706 if (contype == 'p' || contype == 'u')
2709 * If we found a constraint matching the index, create an
2712 * In a pre-7.3 database, we take this path iff the index was
2713 * marked indisprimary.
2715 constrinfo[j].dobj.objType = DO_CONSTRAINT;
2716 constrinfo[j].dobj.catId.tableoid = atooid(PQgetvalue(res, j, i_contableoid));
2717 constrinfo[j].dobj.catId.oid = atooid(PQgetvalue(res, j, i_conoid));
2718 AssignDumpId(&constrinfo[j].dobj);
2720 constrinfo[j].conname = strdup(PQgetvalue(res, j, i_conname));
2721 constrinfo[j].contable = tbinfo;
2722 constrinfo[j].condomain = NULL;
2723 constrinfo[j].contype = contype;
2724 constrinfo[j].condef = NULL;
2725 constrinfo[j].conindex = indxinfo[j].dobj.dumpId;
2726 constrinfo[j].coninherited = false;
2727 constrinfo[j].separate = true;
2729 indxinfo[j].indexconstraint = constrinfo[j].dobj.dumpId;
2731 /* If pre-7.3 DB, better make sure table comes first */
2732 addObjectDependency(&constrinfo[j].dobj,
2733 tbinfo->dobj.dumpId);
2737 /* Plain secondary index */
2738 indxinfo[j].indexconstraint = 0;
2745 destroyPQExpBuffer(query);
2751 * Get info about constraints on dumpable tables.
2753 * Currently handles foreign keys only.
2754 * Unique and primary key constraints are handled with indexes,
2755 * while check constraints are processed in getTableAttrs().
2758 getConstraints(TableInfo tblinfo[], int numTables)
2762 ConstraintInfo *constrinfo;
2771 /* pg_constraint was created in 7.3, so nothing to do if older */
2772 if (g_fout->remoteVersion < 70300)
2775 query = createPQExpBuffer();
2777 for (i = 0; i < numTables; i++)
2779 TableInfo *tbinfo = &tblinfo[i];
2781 if (tbinfo->ntrig == 0 || !tbinfo->dump)
2785 write_msg(NULL, "reading foreign key constraints for table \"%s\"\n",
2789 * select table schema to ensure constraint expr is qualified if
2792 selectSourceSchema(tbinfo->relnamespace->nspname);
2794 resetPQExpBuffer(query);
2795 appendPQExpBuffer(query,
2796 "SELECT tableoid, oid, conname, "
2797 "pg_catalog.pg_get_constraintdef(oid) as condef "
2798 "FROM pg_catalog.pg_constraint "
2799 "WHERE conrelid = '%u'::pg_catalog.oid "
2800 "AND contype = 'f'",
2801 tbinfo->dobj.catId.oid);
2802 res = PQexec(g_conn, query->data);
2803 check_sql_result(res, g_conn, query->data, PGRES_TUPLES_OK);
2805 ntups = PQntuples(res);
2807 i_contableoid = PQfnumber(res, "tableoid");
2808 i_conoid = PQfnumber(res, "oid");
2809 i_conname = PQfnumber(res, "conname");
2810 i_condef = PQfnumber(res, "condef");
2812 constrinfo = (ConstraintInfo *) malloc(ntups * sizeof(ConstraintInfo));
2814 for (j = 0; j < ntups; j++)
2816 constrinfo[j].dobj.objType = DO_FK_CONSTRAINT;
2817 constrinfo[j].dobj.catId.tableoid = atooid(PQgetvalue(res, j, i_contableoid));
2818 constrinfo[j].dobj.catId.oid = atooid(PQgetvalue(res, j, i_conoid));
2819 AssignDumpId(&constrinfo[j].dobj);
2820 constrinfo[j].conname = strdup(PQgetvalue(res, j, i_conname));
2821 constrinfo[j].contable = tbinfo;
2822 constrinfo[j].condomain = NULL;
2823 constrinfo[j].contype = 'f';
2824 constrinfo[j].condef = strdup(PQgetvalue(res, j, i_condef));
2825 constrinfo[j].conindex = 0;
2826 constrinfo[j].coninherited = false;
2827 constrinfo[j].separate = true;
2833 destroyPQExpBuffer(query);
2837 * getDomainConstraints
2839 * Get info about constraints on a domain.
2842 getDomainConstraints(TypeInfo *tinfo)
2845 ConstraintInfo *constrinfo;
2854 /* pg_constraint was created in 7.3, so nothing to do if older */
2855 if (g_fout->remoteVersion < 70300)
2859 * select appropriate schema to ensure names in constraint are properly
2862 selectSourceSchema(tinfo->typnamespace->nspname);
2864 query = createPQExpBuffer();
2866 if (g_fout->remoteVersion >= 70400)
2867 appendPQExpBuffer(query, "SELECT tableoid, oid, conname, "
2868 "pg_catalog.pg_get_constraintdef(oid) AS consrc "
2869 "FROM pg_catalog.pg_constraint "
2870 "WHERE contypid = '%u'::pg_catalog.oid "
2872 tinfo->dobj.catId.oid);
2874 appendPQExpBuffer(query, "SELECT tableoid, oid, conname, "
2875 "'CHECK (' || consrc || ')' AS consrc "
2876 "FROM pg_catalog.pg_constraint "
2877 "WHERE contypid = '%u'::pg_catalog.oid "
2879 tinfo->dobj.catId.oid);
2881 res = PQexec(g_conn, query->data);
2882 check_sql_result(res, g_conn, query->data, PGRES_TUPLES_OK);
2884 ntups = PQntuples(res);
2886 i_tableoid = PQfnumber(res, "tableoid");
2887 i_oid = PQfnumber(res, "oid");
2888 i_conname = PQfnumber(res, "conname");
2889 i_consrc = PQfnumber(res, "consrc");
2891 constrinfo = (ConstraintInfo *) malloc(ntups * sizeof(ConstraintInfo));
2893 tinfo->nDomChecks = ntups;
2894 tinfo->domChecks = constrinfo;
2896 for (i = 0; i < ntups; i++)
2898 constrinfo[i].dobj.objType = DO_CONSTRAINT;
2899 constrinfo[i].dobj.catId.tableoid = atooid(PQgetvalue(res, i, i_tableoid));
2900 constrinfo[i].dobj.catId.oid = atooid(PQgetvalue(res, i, i_oid));
2901 AssignDumpId(&constrinfo[i].dobj);
2902 constrinfo[i].conname = strdup(PQgetvalue(res, i, i_conname));
2903 constrinfo[i].contable = NULL;
2904 constrinfo[i].condomain = tinfo;
2905 constrinfo[i].contype = 'c';
2906 constrinfo[i].condef = strdup(PQgetvalue(res, i, i_consrc));
2907 constrinfo[i].conindex = 0;
2908 constrinfo[i].coninherited = false;
2909 constrinfo[i].separate = false;
2911 * Make the domain depend on the constraint, ensuring it won't
2912 * be output till any constraint dependencies are OK.
2914 addObjectDependency(&tinfo->dobj,
2915 constrinfo[i].dobj.dumpId);
2920 destroyPQExpBuffer(query);
2925 * get basic information about every rule in the system
2927 * numRules is set to the number of rules read in
2930 getRules(int *numRules)
2935 PQExpBuffer query = createPQExpBuffer();
2944 /* Make sure we are in proper schema */
2945 selectSourceSchema("pg_catalog");
2947 if (g_fout->remoteVersion >= 70100)
2949 appendPQExpBuffer(query, "SELECT "
2950 "tableoid, oid, rulename, "
2951 "ev_class as ruletable, ev_type, is_instead "
2957 appendPQExpBuffer(query, "SELECT "
2958 "(SELECT oid FROM pg_class WHERE relname = 'pg_rewrite') AS tableoid, "
2960 "ev_class as ruletable, ev_type, is_instead "
2965 res = PQexec(g_conn, query->data);
2966 check_sql_result(res, g_conn, query->data, PGRES_TUPLES_OK);
2968 ntups = PQntuples(res);
2972 ruleinfo = (RuleInfo *) malloc(ntups * sizeof(RuleInfo));
2974 i_tableoid = PQfnumber(res, "tableoid");
2975 i_oid = PQfnumber(res, "oid");
2976 i_rulename = PQfnumber(res, "rulename");
2977 i_ruletable = PQfnumber(res, "ruletable");
2978 i_ev_type = PQfnumber(res, "ev_type");
2979 i_is_instead = PQfnumber(res, "is_instead");
2981 for (i = 0; i < ntups; i++)
2985 ruleinfo[i].dobj.objType = DO_RULE;
2986 ruleinfo[i].dobj.catId.tableoid = atooid(PQgetvalue(res, i, i_tableoid));
2987 ruleinfo[i].dobj.catId.oid = atooid(PQgetvalue(res, i, i_oid));
2988 AssignDumpId(&ruleinfo[i].dobj);
2989 ruleinfo[i].rulename = strdup(PQgetvalue(res, i, i_rulename));
2990 ruletableoid = atooid(PQgetvalue(res, i, i_ruletable));
2991 ruleinfo[i].ruletable = findTableByOid(ruletableoid);
2992 ruleinfo[i].ev_type = *(PQgetvalue(res, i, i_ev_type));
2993 ruleinfo[i].is_instead = *(PQgetvalue(res, i, i_is_instead)) == 't';
2994 if (ruleinfo[i].ruletable)
2997 * If the table is a view, force its ON SELECT rule to be sorted
2998 * before the view itself --- this ensures that any dependencies
2999 * for the rule affect the table's positioning. Other rules
3000 * are forced to appear after their table.
3002 if (ruleinfo[i].ruletable->relkind == RELKIND_VIEW &&
3003 ruleinfo[i].ev_type == '1' && ruleinfo[i].is_instead)
3004 addObjectDependency(&ruleinfo[i].ruletable->dobj,
3005 ruleinfo[i].dobj.dumpId);
3007 addObjectDependency(&ruleinfo[i].dobj,
3008 ruleinfo[i].ruletable->dobj.dumpId);
3014 destroyPQExpBuffer(query);
3021 * get information about every trigger on a dumpable table
3023 * Note: trigger data is not returned directly to the caller, but it
3024 * does get entered into the DumpableObject tables.
3027 getTriggers(TableInfo tblinfo[], int numTables)
3031 PQExpBuffer query = createPQExpBuffer();
3033 TriggerInfo *tginfo;
3049 for (i = 0; i < numTables; i++)
3051 TableInfo *tbinfo = &tblinfo[i];
3053 if (tbinfo->ntrig == 0 || !tbinfo->dump)
3057 write_msg(NULL, "reading triggers for table \"%s\"\n",
3061 * select table schema to ensure regproc name is qualified if
3064 selectSourceSchema(tbinfo->relnamespace->nspname);
3066 resetPQExpBuffer(query);
3067 if (g_fout->remoteVersion >= 70300)
3070 * We ignore triggers that are tied to a foreign-key
3073 appendPQExpBuffer(query,
3075 "tgfoid::pg_catalog.regproc as tgfname, "
3076 "tgtype, tgnargs, tgargs, "
3077 "tgisconstraint, tgconstrname, tgdeferrable, "
3078 "tgconstrrelid, tginitdeferred, tableoid, oid, "
3079 "tgconstrrelid::pg_catalog.regclass as tgconstrrelname "
3080 "from pg_catalog.pg_trigger t "
3081 "where tgrelid = '%u'::pg_catalog.oid "
3082 "and (not tgisconstraint "
3084 " (SELECT 1 FROM pg_catalog.pg_depend d "
3085 " JOIN pg_catalog.pg_constraint c ON (d.refclassid = c.tableoid AND d.refobjid = c.oid) "
3086 " WHERE d.classid = t.tableoid AND d.objid = t.oid AND d.deptype = 'i' AND c.contype = 'f'))",
3087 tbinfo->dobj.catId.oid);
3089 else if (g_fout->remoteVersion >= 70100)
3091 appendPQExpBuffer(query,
3092 "SELECT tgname, tgfoid::regproc as tgfname, "
3093 "tgtype, tgnargs, tgargs, "
3094 "tgisconstraint, tgconstrname, tgdeferrable, "
3095 "tgconstrrelid, tginitdeferred, tableoid, oid, "
3096 "(select relname from pg_class where oid = tgconstrrelid) "
3097 " as tgconstrrelname "
3099 "where tgrelid = '%u'::oid",
3100 tbinfo->dobj.catId.oid);
3104 appendPQExpBuffer(query,
3105 "SELECT tgname, tgfoid::regproc as tgfname, "
3106 "tgtype, tgnargs, tgargs, "
3107 "tgisconstraint, tgconstrname, tgdeferrable, "
3108 "tgconstrrelid, tginitdeferred, "
3109 "(SELECT oid FROM pg_class WHERE relname = 'pg_trigger') AS tableoid, "
3112 "(select relname from pg_class where oid = tgconstrrelid) "
3113 " as tgconstrrelname "
3115 "where tgrelid = '%u'::oid",
3116 tbinfo->dobj.catId.oid);
3118 res = PQexec(g_conn, query->data);
3119 check_sql_result(res, g_conn, query->data, PGRES_TUPLES_OK);
3121 ntups = PQntuples(res);
3124 * We may have less triggers than recorded due to having ignored
3125 * foreign-key triggers
3127 if (ntups > tbinfo->ntrig)
3129 write_msg(NULL, "expected %d triggers on table \"%s\" but found %d\n",
3130 tbinfo->ntrig, tbinfo->relname, ntups);
3133 i_tableoid = PQfnumber(res, "tableoid");
3134 i_oid = PQfnumber(res, "oid");
3135 i_tgname = PQfnumber(res, "tgname");
3136 i_tgfname = PQfnumber(res, "tgfname");
3137 i_tgtype = PQfnumber(res, "tgtype");
3138 i_tgnargs = PQfnumber(res, "tgnargs");
3139 i_tgargs = PQfnumber(res, "tgargs");
3140 i_tgisconstraint = PQfnumber(res, "tgisconstraint");
3141 i_tgconstrname = PQfnumber(res, "tgconstrname");
3142 i_tgconstrrelid = PQfnumber(res, "tgconstrrelid");
3143 i_tgconstrrelname = PQfnumber(res, "tgconstrrelname");
3144 i_tgdeferrable = PQfnumber(res, "tgdeferrable");
3145 i_tginitdeferred = PQfnumber(res, "tginitdeferred");
3147 tginfo = (TriggerInfo *) malloc(ntups * sizeof(TriggerInfo));
3149 for (j = 0; j < ntups; j++)
3151 tginfo[j].dobj.objType = DO_TRIGGER;
3152 tginfo[j].dobj.catId.tableoid = atooid(PQgetvalue(res, j, i_tableoid));
3153 tginfo[j].dobj.catId.oid = atooid(PQgetvalue(res, j, i_oid));
3154 AssignDumpId(&tginfo[j].dobj);
3155 tginfo[j].tgtable = tbinfo;
3156 tginfo[j].tgname = strdup(PQgetvalue(res, j, i_tgname));
3157 tginfo[j].tgfname = strdup(PQgetvalue(res, j, i_tgfname));
3158 tginfo[j].tgtype = atoi(PQgetvalue(res, j, i_tgtype));
3159 tginfo[j].tgnargs = atoi(PQgetvalue(res, j, i_tgnargs));
3160 tginfo[j].tgargs = strdup(PQgetvalue(res, j, i_tgargs));
3161 tginfo[j].tgisconstraint = *(PQgetvalue(res, j, i_tgisconstraint)) == 't';
3162 tginfo[j].tgdeferrable = *(PQgetvalue(res, j, i_tgdeferrable)) == 't';
3163 tginfo[j].tginitdeferred = *(PQgetvalue(res, j, i_tginitdeferred)) == 't';
3165 if (tginfo[j].tgisconstraint)
3167 tginfo[j].tgconstrname = strdup(PQgetvalue(res, j, i_tgconstrname));
3168 tginfo[j].tgconstrrelid = atooid(PQgetvalue(res, j, i_tgconstrrelid));
3169 if (OidIsValid(tginfo[j].tgconstrrelid))
3171 if (PQgetisnull(res, j, i_tgconstrrelname))
3173 write_msg(NULL, "query produced null referenced table name for foreign key trigger \"%s\" on table \"%s\" (OID of table: %u)\n",
3174 tginfo[j].tgname, tbinfo->relname,
3175 tginfo[j].tgconstrrelid);
3178 tginfo[j].tgconstrrelname = strdup(PQgetvalue(res, j, i_tgconstrrelname));
3181 tginfo[j].tgconstrrelname = NULL;
3185 tginfo[j].tgconstrname = NULL;
3186 tginfo[j].tgconstrrelid = InvalidOid;
3187 tginfo[j].tgconstrrelname = NULL;
3194 destroyPQExpBuffer(query);
3199 * get basic information about every procedural language in the system
3201 * numProcLangs is set to the number of langs read in
3203 * NB: this must run after getFuncs() because we assume we can do
3207 getProcLangs(int *numProcLangs)
3212 PQExpBuffer query = createPQExpBuffer();
3213 ProcLangInfo *planginfo;
3218 int i_lanplcallfoid;
3219 int i_lanvalidator = -1;
3222 /* Make sure we are in proper schema */
3223 selectSourceSchema("pg_catalog");
3225 if (g_fout->remoteVersion >= 70100)
3227 appendPQExpBuffer(query, "SELECT tableoid, oid, * FROM pg_language "
3233 appendPQExpBuffer(query, "SELECT "
3234 "(SELECT oid FROM pg_class WHERE relname = 'pg_language') AS tableoid, "
3235 "oid, * FROM pg_language "
3240 res = PQexec(g_conn, query->data);
3241 check_sql_result(res, g_conn, query->data, PGRES_TUPLES_OK);
3243 ntups = PQntuples(res);
3245 *numProcLangs = ntups;
3247 planginfo = (ProcLangInfo *) malloc(ntups * sizeof(ProcLangInfo));
3249 i_tableoid = PQfnumber(res, "tableoid");
3250 i_oid = PQfnumber(res, "oid");
3251 i_lanname = PQfnumber(res, "lanname");
3252 i_lanpltrusted = PQfnumber(res, "lanpltrusted");
3253 i_lanplcallfoid = PQfnumber(res, "lanplcallfoid");
3254 if (g_fout->remoteVersion >= 70300)
3256 i_lanvalidator = PQfnumber(res, "lanvalidator");
3257 i_lanacl = PQfnumber(res, "lanacl");
3260 for (i = 0; i < ntups; i++)
3262 planginfo[i].dobj.objType = DO_PROCLANG;
3263 planginfo[i].dobj.catId.tableoid = atooid(PQgetvalue(res, i, i_tableoid));
3264 planginfo[i].dobj.catId.oid = atooid(PQgetvalue(res, i, i_oid));
3265 AssignDumpId(&planginfo[i].dobj);
3267 planginfo[i].lanname = strdup(PQgetvalue(res, i, i_lanname));
3268 planginfo[i].lanpltrusted = *(PQgetvalue(res, i, i_lanpltrusted)) == 't';
3269 planginfo[i].lanplcallfoid = atooid(PQgetvalue(res, i, i_lanplcallfoid));
3270 if (g_fout->remoteVersion >= 70300)
3272 planginfo[i].lanvalidator = atooid(PQgetvalue(res, i, i_lanvalidator));
3273 planginfo[i].lanacl = strdup(PQgetvalue(res, i, i_lanacl));
3279 planginfo[i].lanvalidator = InvalidOid;
3280 planginfo[i].lanacl = strdup("{=U}");
3282 * We need to make a dependency to ensure the function will
3283 * be dumped first. (In 7.3 and later the regular dependency
3284 * mechanism will handle this for us.)
3286 funcInfo = findFuncByOid(planginfo[i].lanplcallfoid);
3288 addObjectDependency(&planginfo[i].dobj,
3289 funcInfo->dobj.dumpId);
3295 destroyPQExpBuffer(query);
3302 * get basic information about every cast in the system
3304 * numCasts is set to the number of casts read in
3307 getCasts(int *numCasts)
3312 PQExpBuffer query = createPQExpBuffer();
3321 /* Make sure we are in proper schema */
3322 selectSourceSchema("pg_catalog");
3324 if (g_fout->remoteVersion >= 70300)
3326 appendPQExpBuffer(query, "SELECT tableoid, oid, "
3327 "castsource, casttarget, castfunc, castcontext "
3328 "FROM pg_cast ORDER BY 3,4");
3332 appendPQExpBuffer(query, "SELECT 0 as tableoid, p.oid, "
3333 "t1.oid as castsource, t2.oid as casttarget, "
3334 "p.oid as castfunc, 'e' as castcontext "
3335 "FROM pg_type t1, pg_type t2, pg_proc p "
3336 "WHERE p.pronargs = 1 AND "
3337 "p.proargtypes[0] = t1.oid AND "
3338 "p.prorettype = t2.oid AND p.proname = t2.typname "
3342 res = PQexec(g_conn, query->data);
3343 check_sql_result(res, g_conn, query->data, PGRES_TUPLES_OK);
3345 ntups = PQntuples(res);
3349 castinfo = (CastInfo *) malloc(ntups * sizeof(CastInfo));
3351 i_tableoid = PQfnumber(res, "tableoid");
3352 i_oid = PQfnumber(res, "oid");
3353 i_castsource = PQfnumber(res, "castsource");
3354 i_casttarget = PQfnumber(res, "casttarget");
3355 i_castfunc = PQfnumber(res, "castfunc");
3356 i_castcontext = PQfnumber(res, "castcontext");
3358 for (i = 0; i < ntups; i++)
3360 castinfo[i].dobj.objType = DO_CAST;
3361 castinfo[i].dobj.catId.tableoid = atooid(PQgetvalue(res, i, i_tableoid));
3362 castinfo[i].dobj.catId.oid = atooid(PQgetvalue(res, i, i_oid));
3363 AssignDumpId(&castinfo[i].dobj);
3364 castinfo[i].castsource = atooid(PQgetvalue(res, i, i_castsource));
3365 castinfo[i].casttarget = atooid(PQgetvalue(res, i, i_casttarget));
3366 castinfo[i].castfunc = atooid(PQgetvalue(res, i, i_castfunc));
3367 castinfo[i].castcontext = *(PQgetvalue(res, i, i_castcontext));
3369 if (OidIsValid(castinfo[i].castfunc))
3372 * We need to make a dependency to ensure the function will
3373 * be dumped first. (In 7.3 and later the regular dependency
3374 * mechanism will handle this for us.)
3378 funcInfo = findFuncByOid(castinfo[i].castfunc);
3380 addObjectDependency(&castinfo[i].dobj,
3381 funcInfo->dobj.dumpId);
3387 destroyPQExpBuffer(query);
3394 * for each interesting table, read info about its attributes
3395 * (names, types, default values, CHECK constraints, etc)
3397 * This is implemented in a very inefficient way right now, looping
3398 * through the tblinfo and doing a join per table to find the attrs and their
3399 * types. However, because we want type names and so forth to be named
3400 * relative to the schema of each table, we couldn't do it in just one
3401 * query. (Maybe one query per schema?)
3406 getTableAttrs(TableInfo *tblinfo, int numTables)
3411 PQExpBuffer q = createPQExpBuffer();
3416 int i_attstattarget;
3427 for (i = 0; i < numTables; i++)
3429 TableInfo *tbinfo = &tblinfo[i];
3431 /* Don't bother to collect info for sequences */
3432 if (tbinfo->relkind == RELKIND_SEQUENCE)
3435 /* Don't bother with uninteresting tables, either */
3436 if (!tbinfo->interesting)
3440 * Make sure we are in proper schema for this table; this allows
3441 * correct retrieval of formatted type names and default exprs
3443 selectSourceSchema(tbinfo->relnamespace->nspname);
3445 /* find all the user attributes and their types */
3448 * we must read the attribute names in attribute number order!
3449 * because we will use the attnum to index into the attnames array
3450 * later. We actually ask to order by "attrelid, attnum" because
3451 * (at least up to 7.3) the planner is not smart enough to realize
3452 * it needn't re-sort the output of an indexscan on
3453 * pg_attribute_relid_attnum_index.
3456 write_msg(NULL, "finding the columns and types of table \"%s\"\n",
3459 resetPQExpBuffer(q);
3461 if (g_fout->remoteVersion >= 70300)
3463 /* need left join here to not fail on dropped columns ... */
3464 appendPQExpBuffer(q, "SELECT a.attnum, a.attname, a.atttypmod, a.attstattarget, a.attstorage, t.typstorage, "
3465 "a.attnotnull, a.atthasdef, a.attisdropped, a.attislocal, "
3466 "pg_catalog.format_type(t.oid,a.atttypmod) as atttypname "
3467 "from pg_catalog.pg_attribute a left join pg_catalog.pg_type t "
3468 "on a.atttypid = t.oid "
3469 "where a.attrelid = '%u'::pg_catalog.oid "
3470 "and a.attnum > 0::pg_catalog.int2 "
3471 "order by a.attrelid, a.attnum",
3472 tbinfo->dobj.catId.oid);
3474 else if (g_fout->remoteVersion >= 70100)
3477 * attstattarget doesn't exist in 7.1. It does exist in 7.2,
3478 * but we don't dump it because we can't tell whether it's
3479 * been explicitly set or was just a default.
3481 appendPQExpBuffer(q, "SELECT a.attnum, a.attname, a.atttypmod, -1 as attstattarget, a.attstorage, t.typstorage, "
3482 "a.attnotnull, a.atthasdef, false as attisdropped, false as attislocal, "
3483 "format_type(t.oid,a.atttypmod) as atttypname "
3484 "from pg_attribute a left join pg_type t "
3485 "on a.atttypid = t.oid "
3486 "where a.attrelid = '%u'::oid "
3487 "and a.attnum > 0::int2 "
3488 "order by a.attrelid, a.attnum",
3489 tbinfo->dobj.catId.oid);
3493 /* format_type not available before 7.1 */
3494 appendPQExpBuffer(q, "SELECT attnum, attname, atttypmod, -1 as attstattarget, attstorage, attstorage as typstorage, "
3495 "attnotnull, atthasdef, false as attisdropped, false as attislocal, "
3496 "(select typname from pg_type where oid = atttypid) as atttypname "
3497 "from pg_attribute a "
3498 "where attrelid = '%u'::oid "
3499 "and attnum > 0::int2 "
3500 "order by attrelid, attnum",
3501 tbinfo->dobj.catId.oid);
3504 res = PQexec(g_conn, q->data);
3505 check_sql_result(res, g_conn, q->data, PGRES_TUPLES_OK);
3507 ntups = PQntuples(res);
3509 i_attnum = PQfnumber(res, "attnum");
3510 i_attname = PQfnumber(res, "attname");
3511 i_atttypname = PQfnumber(res, "atttypname");
3512 i_atttypmod = PQfnumber(res, "atttypmod");
3513 i_attstattarget = PQfnumber(res, "attstattarget");
3514 i_attstorage = PQfnumber(res, "attstorage");
3515 i_typstorage = PQfnumber(res, "typstorage");
3516 i_attnotnull = PQfnumber(res, "attnotnull");
3517 i_atthasdef = PQfnumber(res, "atthasdef");
3518 i_attisdropped = PQfnumber(res, "attisdropped");
3519 i_attislocal = PQfnumber(res, "attislocal");
3521 tbinfo->numatts = ntups;
3522 tbinfo->attnames = (char **) malloc(ntups * sizeof(char *));
3523 tbinfo->atttypnames = (char **) malloc(ntups * sizeof(char *));
3524 tbinfo->atttypmod = (int *) malloc(ntups * sizeof(int));
3525 tbinfo->attstattarget = (int *) malloc(ntups * sizeof(int));
3526 tbinfo->attstorage = (char *) malloc(ntups * sizeof(char));
3527 tbinfo->typstorage = (char *) malloc(ntups * sizeof(char));
3528 tbinfo->attisdropped = (bool *) malloc(ntups * sizeof(bool));
3529 tbinfo->attislocal = (bool *) malloc(ntups * sizeof(bool));
3530 tbinfo->attisserial = (bool *) malloc(ntups * sizeof(bool));
3531 tbinfo->notnull = (bool *) malloc(ntups * sizeof(bool));
3532 tbinfo->attrdefs = (AttrDefInfo **) malloc(ntups * sizeof(AttrDefInfo *));
3533 tbinfo->inhAttrs = (bool *) malloc(ntups * sizeof(bool));
3534 tbinfo->inhAttrDef = (bool *) malloc(ntups * sizeof(bool));
3535 tbinfo->inhNotNull = (bool *) malloc(ntups * sizeof(bool));
3536 hasdefaults = false;
3538 for (j = 0; j < ntups; j++)
3540 if (j + 1 != atoi(PQgetvalue(res, j, i_attnum)))
3542 write_msg(NULL, "invalid column numbering in table \"%s\"\n",
3546 tbinfo->attnames[j] = strdup(PQgetvalue(res, j, i_attname));
3547 tbinfo->atttypnames[j] = strdup(PQgetvalue(res, j, i_atttypname));
3548 tbinfo->atttypmod[j] = atoi(PQgetvalue(res, j, i_atttypmod));
3549 tbinfo->attstattarget[j] = atoi(PQgetvalue(res, j, i_attstattarget));
3550 tbinfo->attstorage[j] = *(PQgetvalue(res, j, i_attstorage));
3551 tbinfo->typstorage[j] = *(PQgetvalue(res, j, i_typstorage));
3552 tbinfo->attisdropped[j] = (PQgetvalue(res, j, i_attisdropped)[0] == 't');
3553 tbinfo->attislocal[j] = (PQgetvalue(res, j, i_attislocal)[0] == 't');
3554 tbinfo->attisserial[j] = false; /* fix below */
3555 tbinfo->notnull[j] = (PQgetvalue(res, j, i_attnotnull)[0] == 't');
3556 tbinfo->attrdefs[j] = NULL; /* fix below */
3557 if (PQgetvalue(res, j, i_atthasdef)[0] == 't')
3559 /* these flags will be set in flagInhAttrs() */
3560 tbinfo->inhAttrs[j] = false;
3561 tbinfo->inhAttrDef[j] = false;
3562 tbinfo->inhNotNull[j] = false;
3568 * Get info about column defaults
3572 AttrDefInfo *attrdefs;
3576 write_msg(NULL, "finding default expressions of table \"%s\"\n",
3579 resetPQExpBuffer(q);
3580 if (g_fout->remoteVersion >= 70300)
3582 appendPQExpBuffer(q, "SELECT tableoid, oid, adnum, "
3583 "pg_catalog.pg_get_expr(adbin, adrelid) AS adsrc "
3584 "FROM pg_catalog.pg_attrdef "
3585 "WHERE adrelid = '%u'::pg_catalog.oid",
3586 tbinfo->dobj.catId.oid);
3588 else if (g_fout->remoteVersion >= 70200)
3590 /* 7.2 did not have OIDs in pg_attrdef */
3591 appendPQExpBuffer(q, "SELECT tableoid, 0 as oid, adnum, "
3592 "pg_get_expr(adbin, adrelid) AS adsrc "
3594 "WHERE adrelid = '%u'::oid",
3595 tbinfo->dobj.catId.oid);
3597 else if (g_fout->remoteVersion >= 70100)
3599 /* no pg_get_expr, so must rely on adsrc */
3600 appendPQExpBuffer(q, "SELECT tableoid, oid, adnum, adsrc "
3602 "WHERE adrelid = '%u'::oid",
3603 tbinfo->dobj.catId.oid);
3607 /* no pg_get_expr, no tableoid either */
3608 appendPQExpBuffer(q, "SELECT "
3609 "(SELECT oid FROM pg_class WHERE relname = 'pg_attrdef') AS tableoid, "
3610 "oid, adnum, adsrc "
3612 "WHERE adrelid = '%u'::oid",
3613 tbinfo->dobj.catId.oid);
3615 res = PQexec(g_conn, q->data);
3616 check_sql_result(res, g_conn, q->data, PGRES_TUPLES_OK);
3618 numDefaults = PQntuples(res);
3619 attrdefs = (AttrDefInfo *) malloc(numDefaults * sizeof(AttrDefInfo));
3621 for (j = 0; j < numDefaults; j++)
3625 attrdefs[j].dobj.objType = DO_ATTRDEF;
3626 attrdefs[j].dobj.catId.tableoid = atooid(PQgetvalue(res, j, 0));
3627 attrdefs[j].dobj.catId.oid = atooid(PQgetvalue(res, j, 1));
3628 AssignDumpId(&attrdefs[j].dobj);
3629 attrdefs[j].adtable = tbinfo;
3630 attrdefs[j].adnum = adnum = atoi(PQgetvalue(res, j, 2));
3631 attrdefs[j].adef_expr = strdup(PQgetvalue(res, j, 3));
3634 * Defaults on a VIEW must always be dumped as separate
3635 * ALTER TABLE commands. Defaults on regular tables are
3636 * dumped as part of the CREATE TABLE if possible. To check
3637 * if it's safe, we mark the default as needing to appear
3638 * before the CREATE.
3640 if (tbinfo->relkind == RELKIND_VIEW)
3642 attrdefs[j].separate = true;
3643 /* needed in case pre-7.3 DB: */
3644 addObjectDependency(&attrdefs[j].dobj,
3645 tbinfo->dobj.dumpId);
3649 attrdefs[j].separate = false;
3650 addObjectDependency(&tbinfo->dobj,
3651 attrdefs[j].dobj.dumpId);
3654 if (adnum <= 0 || adnum > ntups)
3656 write_msg(NULL, "invalid adnum value %d for table \"%s\"\n",
3657 adnum, tbinfo->relname);
3660 tbinfo->attrdefs[adnum - 1] = &attrdefs[j];
3666 * Get info about table CHECK constraints
3668 if (tbinfo->ncheck > 0)
3670 ConstraintInfo *constrs;
3674 write_msg(NULL, "finding check constraints for table \"%s\"\n",
3677 resetPQExpBuffer(q);
3678 if (g_fout->remoteVersion >= 70400)
3680 appendPQExpBuffer(q, "SELECT tableoid, oid, conname, "
3681 "pg_catalog.pg_get_constraintdef(oid) AS consrc "
3682 "FROM pg_catalog.pg_constraint "
3683 "WHERE conrelid = '%u'::pg_catalog.oid "
3684 " AND contype = 'c' "
3686 tbinfo->dobj.catId.oid);
3688 else if (g_fout->remoteVersion >= 70300)
3690 /* no pg_get_constraintdef, must use consrc */
3691 appendPQExpBuffer(q, "SELECT tableoid, oid, conname, "
3692 "'CHECK (' || consrc || ')' AS consrc "
3693 "FROM pg_catalog.pg_constraint "
3694 "WHERE conrelid = '%u'::pg_catalog.oid "
3695 " AND contype = 'c' "
3697 tbinfo->dobj.catId.oid);
3699 else if (g_fout->remoteVersion >= 70200)
3701 /* 7.2 did not have OIDs in pg_relcheck */
3702 appendPQExpBuffer(q, "SELECT tableoid, 0 as oid, "
3703 "rcname AS conname, "
3704 "'CHECK (' || rcsrc || ')' AS consrc "
3706 "WHERE rcrelid = '%u'::oid "
3708 tbinfo->dobj.catId.oid);
3710 else if (g_fout->remoteVersion >= 70100)
3712 appendPQExpBuffer(q, "SELECT tableoid, oid, "
3713 "rcname AS conname, "
3714 "'CHECK (' || rcsrc || ')' AS consrc "
3716 "WHERE rcrelid = '%u'::oid "
3718 tbinfo->dobj.catId.oid);
3722 /* no tableoid in 7.0 */
3723 appendPQExpBuffer(q, "SELECT "
3724 "(SELECT oid FROM pg_class WHERE relname = 'pg_relcheck') AS tableoid, "
3725 "oid, rcname AS conname, "
3726 "'CHECK (' || rcsrc || ')' AS consrc "
3728 "WHERE rcrelid = '%u'::oid "
3730 tbinfo->dobj.catId.oid);
3732 res = PQexec(g_conn, q->data);
3733 check_sql_result(res, g_conn, q->data, PGRES_TUPLES_OK);
3735 numConstrs = PQntuples(res);
3736 if (numConstrs != tbinfo->ncheck)
3738 write_msg(NULL, "expected %d check constraints on table \"%s\" but found %d\n",
3739 tbinfo->ncheck, tbinfo->relname, numConstrs);
3740 write_msg(NULL, "(The system catalogs might be corrupted.)\n");
3744 constrs = (ConstraintInfo *) malloc(numConstrs * sizeof(ConstraintInfo));
3745 tbinfo->checkexprs = constrs;
3747 for (j = 0; j < numConstrs; j++)
3749 constrs[j].dobj.objType = DO_CONSTRAINT;
3750 constrs[j].dobj.catId.tableoid = atooid(PQgetvalue(res, j, 0));
3751 constrs[j].dobj.catId.oid = atooid(PQgetvalue(res, j, 1));
3752 AssignDumpId(&constrs[j].dobj);
3753 constrs[j].contable = tbinfo;
3754 constrs[j].condomain = NULL;
3755 constrs[j].contype = 'c';
3756 constrs[j].conname = strdup(PQgetvalue(res, j, 2));
3757 constrs[j].condef = strdup(PQgetvalue(res, j, 3));
3758 constrs[j].conindex = 0;
3759 constrs[j].coninherited = false;
3760 constrs[j].separate = false;
3761 addObjectDependency(&tbinfo->dobj,
3762 constrs[j].dobj.dumpId);
3764 * If the constraint is inherited, this will be detected
3765 * later. We also detect later if the constraint must be
3766 * split out from the table definition.
3773 * Check to see if any columns are serial columns. Our first
3774 * quick filter is that it must be integer or bigint with a
3775 * default. If so, we scan to see if we found a sequence linked
3776 * to this column. If we did, mark the column and sequence
3779 for (j = 0; j < ntups; j++)
3782 * Note assumption that format_type will show these types as
3783 * exactly "integer" and "bigint" regardless of schema path.
3784 * This is correct in 7.3 but needs to be watched.
3786 if (strcmp(tbinfo->atttypnames[j], "integer") != 0 &&
3787 strcmp(tbinfo->atttypnames[j], "bigint") != 0)
3789 if (tbinfo->attrdefs[j] == NULL)
3791 for (k = 0; k < numTables; k++)
3793 TableInfo *seqinfo = &tblinfo[k];
3795 if (OidIsValid(seqinfo->owning_tab) &&
3796 seqinfo->owning_tab == tbinfo->dobj.catId.oid &&
3797 seqinfo->owning_col == j + 1)
3800 * Found a match. Copy the table's interesting and
3801 * dumpable flags to the sequence.
3803 tbinfo->attisserial[j] = true;
3804 seqinfo->interesting = tbinfo->interesting;
3805 seqinfo->dump = tbinfo->dump;
3812 destroyPQExpBuffer(q);
3819 * This routine is used to dump any comments associated with the
3820 * object handed to this routine. The routine takes a constant character
3821 * string for the target part of the comment-creation command, plus
3822 * the namespace and owner of the object (for labeling the ArchiveEntry),
3823 * plus catalog ID and subid which are the lookup key for pg_description,
3824 * plus the dump ID for the object (for setting a dependency).
3825 * If a matching pg_description entry is found, it is dumped.
3828 dumpComment(Archive *fout, const char *target,
3829 const char *namespace, const char *owner,
3830 CatalogId catalogId, int subid, DumpId dumpId)
3836 /* Comments are SCHEMA not data */
3841 * Note we do NOT change source schema here; preserve the caller's
3845 /* Build query to find comment */
3847 query = createPQExpBuffer();
3849 if (fout->remoteVersion >= 70300)
3851 appendPQExpBuffer(query,
3852 "SELECT description FROM pg_catalog.pg_description "
3853 "WHERE classoid = '%u'::pg_catalog.oid and "
3854 "objoid = '%u'::pg_catalog.oid and objsubid = %d",
3855 catalogId.tableoid, catalogId.oid, subid);
3857 else if (fout->remoteVersion >= 70200)
3859 appendPQExpBuffer(query,
3860 "SELECT description FROM pg_description "
3861 "WHERE classoid = '%u'::oid and "
3862 "objoid = '%u'::oid and objsubid = %d",
3863 catalogId.tableoid, catalogId.oid, subid);
3867 /* Note: this will fail to find attribute comments in pre-7.2... */
3868 appendPQExpBuffer(query, "SELECT description FROM pg_description WHERE objoid = '%u'::oid", catalogId.oid);
3873 res = PQexec(g_conn, query->data);
3874 check_sql_result(res, g_conn, query->data, PGRES_TUPLES_OK);
3876 /* If a comment exists, build COMMENT ON statement */
3878 if (PQntuples(res) == 1)
3880 i_description = PQfnumber(res, "description");
3881 resetPQExpBuffer(query);
3882 appendPQExpBuffer(query, "COMMENT ON %s IS ", target);
3883 appendStringLiteral(query, PQgetvalue(res, 0, i_description), false);
3884 appendPQExpBuffer(query, ";\n");
3886 ArchiveEntry(fout, nilCatalogId, createDumpId(),
3887 target, namespace, owner,
3888 "COMMENT", query->data, "", NULL,
3894 destroyPQExpBuffer(query);
3898 * dumpTableComment --
3900 * As above, but dump comments for both the specified table (or view)
3901 * and its columns. For speed, we want to do this with only one query.
3904 dumpTableComment(Archive *fout, TableInfo *tbinfo,
3905 const char *reltypename)
3915 /* Comments are SCHEMA not data */
3920 * Note we do NOT change source schema here; preserve the caller's
3924 /* Build query to find comments */
3926 query = createPQExpBuffer();
3927 target = createPQExpBuffer();
3929 if (fout->remoteVersion >= 70300)
3931 appendPQExpBuffer(query, "SELECT description, objsubid FROM pg_catalog.pg_description "
3932 "WHERE classoid = '%u'::pg_catalog.oid and "
3933 "objoid = '%u'::pg_catalog.oid "
3934 "ORDER BY objoid, classoid, objsubid",
3935 tbinfo->dobj.catId.tableoid, tbinfo->dobj.catId.oid);
3937 else if (fout->remoteVersion >= 70200)
3939 appendPQExpBuffer(query, "SELECT description, objsubid FROM pg_description "
3940 "WHERE classoid = '%u'::oid and "
3941 "objoid = '%u'::oid "
3942 "ORDER BY objoid, classoid, objsubid",
3943 tbinfo->dobj.catId.tableoid, tbinfo->dobj.catId.oid);
3947 /* Note: this will fail to find attribute comments in pre-7.2... */
3948 appendPQExpBuffer(query, "SELECT description, 0 as objsubid FROM pg_description WHERE objoid = '%u'::oid",
3949 tbinfo->dobj.catId.oid);
3954 res = PQexec(g_conn, query->data);
3955 check_sql_result(res, g_conn, query->data, PGRES_TUPLES_OK);
3957 i_description = PQfnumber(res, "description");
3958 i_objsubid = PQfnumber(res, "objsubid");
3960 /* If comments exist, build COMMENT ON statements */
3962 ntups = PQntuples(res);
3963 for (i = 0; i < ntups; i++)
3965 const char *descr = PQgetvalue(res, i, i_description);
3966 int objsubid = atoi(PQgetvalue(res, i, i_objsubid));
3970 resetPQExpBuffer(target);
3971 appendPQExpBuffer(target, "%s %s", reltypename,
3972 fmtId(tbinfo->relname));
3974 resetPQExpBuffer(query);
3975 appendPQExpBuffer(query, "COMMENT ON %s IS ", target->data);
3976 appendStringLiteral(query, descr, false);
3977 appendPQExpBuffer(query, ";\n");
3979 ArchiveEntry(fout, nilCatalogId, createDumpId(),
3981 tbinfo->relnamespace->nspname, tbinfo->usename,
3982 "COMMENT", query->data, "", NULL,
3983 &(tbinfo->dobj.dumpId), 1,
3986 else if (objsubid > 0 && objsubid <= tbinfo->numatts)
3988 resetPQExpBuffer(target);
3989 appendPQExpBuffer(target, "COLUMN %s.",
3990 fmtId(tbinfo->relname));
3991 appendPQExpBuffer(target, "%s",
3992 fmtId(tbinfo->attnames[objsubid - 1]));
3994 resetPQExpBuffer(query);
3995 appendPQExpBuffer(query, "COMMENT ON %s IS ", target->data);
3996 appendStringLiteral(query, descr, false);
3997 appendPQExpBuffer(query, ";\n");
3999 ArchiveEntry(fout, nilCatalogId, createDumpId(),
4001 tbinfo->relnamespace->nspname, tbinfo->usename,
4002 "COMMENT", query->data, "", NULL,
4003 &(tbinfo->dobj.dumpId), 1,
4009 destroyPQExpBuffer(query);
4010 destroyPQExpBuffer(target);
4014 * dumpDumpableObject
4016 * This routine and its subsidiaries are responsible for creating
4017 * ArchiveEntries (TOC objects) for each object to be dumped.
4020 dumpDumpableObject(Archive *fout, DumpableObject *dobj)
4022 switch (dobj->objType)
4025 dumpNamespace(fout, (NamespaceInfo *) dobj);
4028 dumpType(fout, (TypeInfo *) dobj);
4031 dumpFunc(fout, (FuncInfo *) dobj);
4034 dumpAgg(fout, (AggInfo *) dobj);
4037 dumpOpr(fout, (OprInfo *) dobj);
4040 dumpOpclass(fout, (OpclassInfo *) dobj);
4043 dumpConversion(fout, (ConvInfo *) dobj);
4046 dumpTable(fout, (TableInfo *) dobj);
4049 dumpAttrDef(fout, (AttrDefInfo *) dobj);
4052 dumpIndex(fout, (IndxInfo *) dobj);
4055 dumpRule(fout, (RuleInfo *) dobj);
4058 dumpTrigger(fout, (TriggerInfo *) dobj);
4061 dumpConstraint(fout, (ConstraintInfo *) dobj);
4063 case DO_FK_CONSTRAINT:
4064 dumpConstraint(fout, (ConstraintInfo *) dobj);
4067 dumpProcLang(fout, (ProcLangInfo *) dobj);
4070 dumpCast(fout, (CastInfo *) dobj);
4073 dumpTableData(fout, (TableDataInfo *) dobj);
4080 * writes out to fout the queries to recreate a user-defined namespace
4083 dumpNamespace(Archive *fout, NamespaceInfo *nspinfo)
4089 /* skip if not to be dumped */
4090 if (!nspinfo->dump || dataOnly)
4093 /* don't dump dummy namespace from pre-7.3 source */
4094 if (strlen(nspinfo->nspname) == 0)
4097 q = createPQExpBuffer();
4098 delq = createPQExpBuffer();
4100 qnspname = strdup(fmtId(nspinfo->nspname));
4103 * If it's the PUBLIC namespace, suppress the CREATE SCHEMA record
4104 * for it, since we expect PUBLIC to exist already in the
4105 * destination database. But do emit ACL in case it's not standard,
4108 * Note that ownership is shown in the AUTHORIZATION clause,
4109 * while the archive entry is listed with empty owner (causing
4110 * it to be emitted with SET SESSION AUTHORIZATION DEFAULT).
4111 * This seems the best way of dealing with schemas owned by
4112 * users without CREATE SCHEMA privilege. Further hacking has
4113 * to be applied for --no-owner mode, though!
4115 if (strcmp(nspinfo->nspname, "public") != 0)
4117 appendPQExpBuffer(delq, "DROP SCHEMA %s;\n", qnspname);
4119 appendPQExpBuffer(q, "CREATE SCHEMA %s AUTHORIZATION %s;\n",
4120 qnspname, fmtId(nspinfo->usename));
4122 ArchiveEntry(fout, nspinfo->dobj.catId, nspinfo->dobj.dumpId,
4125 "SCHEMA", q->data, delq->data, NULL,
4126 nspinfo->dobj.dependencies, nspinfo->dobj.nDeps,
4130 /* Dump Schema Comments */
4131 resetPQExpBuffer(q);
4132 appendPQExpBuffer(q, "SCHEMA %s", qnspname);
4133 dumpComment(fout, q->data,
4134 NULL, nspinfo->usename,
4135 nspinfo->dobj.catId, 0, nspinfo->dobj.dumpId);
4137 dumpACL(fout, nspinfo->dobj.catId, nspinfo->dobj.dumpId, "SCHEMA",
4138 qnspname, nspinfo->nspname, NULL,
4139 nspinfo->usename, nspinfo->nspacl);
4143 destroyPQExpBuffer(q);
4144 destroyPQExpBuffer(delq);
4149 * writes out to fout the queries to recreate a user-defined type
4152 dumpType(Archive *fout, TypeInfo *tinfo)
4154 /* Dump only types in dumpable namespaces */
4155 if (!tinfo->typnamespace->dump || dataOnly)
4158 /* skip complex types, except for standalone composite types */
4159 if (OidIsValid(tinfo->typrelid) && tinfo->typrelkind != 'c')
4162 /* skip undefined placeholder types */
4163 if (!tinfo->isDefined)
4166 /* skip all array types that start w/ underscore */
4167 if ((tinfo->typname[0] == '_') &&
4168 OidIsValid(tinfo->typelem))
4171 /* Dump out in proper style */
4172 if (tinfo->typtype == 'b')
4173 dumpBaseType(fout, tinfo);
4174 else if (tinfo->typtype == 'd')
4175 dumpDomain(fout, tinfo);
4176 else if (tinfo->typtype == 'c')
4177 dumpCompositeType(fout, tinfo);
4182 * writes out to fout the queries to recreate a user-defined base type
4185 dumpBaseType(Archive *fout, TypeInfo *tinfo)
4187 PQExpBuffer q = createPQExpBuffer();
4188 PQExpBuffer delq = createPQExpBuffer();
4189 PQExpBuffer query = createPQExpBuffer();
4209 /* Set proper schema search path so regproc references list correctly */
4210 selectSourceSchema(tinfo->typnamespace->nspname);
4212 /* Fetch type-specific details */
4213 if (fout->remoteVersion >= 70500)
4215 appendPQExpBuffer(query, "SELECT typlen, "
4216 "typinput, typoutput, typreceive, typsend, "
4218 "typinput::pg_catalog.oid as typinputoid, "
4219 "typoutput::pg_catalog.oid as typoutputoid, "
4220 "typreceive::pg_catalog.oid as typreceiveoid, "
4221 "typsend::pg_catalog.oid as typsendoid, "
4222 "typanalyze::pg_catalog.oid as typanalyzeoid, "
4223 "typdelim, typdefault, typbyval, typalign, "
4225 "FROM pg_catalog.pg_type "
4226 "WHERE oid = '%u'::pg_catalog.oid",
4227 tinfo->dobj.catId.oid);
4229 else if (fout->remoteVersion >= 70400)
4231 appendPQExpBuffer(query, "SELECT typlen, "
4232 "typinput, typoutput, typreceive, typsend, "
4233 "'-' as typanalyze, "
4234 "typinput::pg_catalog.oid as typinputoid, "
4235 "typoutput::pg_catalog.oid as typoutputoid, "
4236 "typreceive::pg_catalog.oid as typreceiveoid, "
4237 "typsend::pg_catalog.oid as typsendoid, "
4238 "0 as typanalyzeoid, "
4239 "typdelim, typdefault, typbyval, typalign, "
4241 "FROM pg_catalog.pg_type "
4242 "WHERE oid = '%u'::pg_catalog.oid",
4243 tinfo->dobj.catId.oid);
4245 else if (fout->remoteVersion >= 70300)
4247 appendPQExpBuffer(query, "SELECT typlen, "
4248 "typinput, typoutput, "
4249 "'-' as typreceive, '-' as typsend, "
4250 "'-' as typanalyze, "
4251 "typinput::pg_catalog.oid as typinputoid, "
4252 "typoutput::pg_catalog.oid as typoutputoid, "
4253 "0 as typreceiveoid, 0 as typsendoid, "
4254 "0 as typanalyzeoid, "
4255 "typdelim, typdefault, typbyval, typalign, "
4257 "FROM pg_catalog.pg_type "
4258 "WHERE oid = '%u'::pg_catalog.oid",
4259 tinfo->dobj.catId.oid);
4261 else if (fout->remoteVersion >= 70100)
4264 * Note: although pre-7.3 catalogs contain typreceive and typsend,
4265 * ignore them because they are not right.
4267 appendPQExpBuffer(query, "SELECT typlen, "
4268 "typinput, typoutput, "
4269 "'-' as typreceive, '-' as typsend, "
4270 "'-' as typanalyze, "
4271 "typinput::oid as typinputoid, "
4272 "typoutput::oid as typoutputoid, "
4273 "0 as typreceiveoid, 0 as typsendoid, "
4274 "0 as typanalyzeoid, "
4275 "typdelim, typdefault, typbyval, typalign, "
4278 "WHERE oid = '%u'::oid",
4279 tinfo->dobj.catId.oid);
4283 appendPQExpBuffer(query, "SELECT typlen, "
4284 "typinput, typoutput, "
4285 "'-' as typreceive, '-' as typsend, "
4286 "'-' as typanalyze, "
4287 "typinput::oid as typinputoid, "
4288 "typoutput::oid as typoutputoid, "
4289 "0 as typreceiveoid, 0 as typsendoid, "
4290 "0 as typanalyzeoid, "
4291 "typdelim, typdefault, typbyval, typalign, "
4292 "'p'::char as typstorage "
4294 "WHERE oid = '%u'::oid",
4295 tinfo->dobj.catId.oid);
4298 res = PQexec(g_conn, query->data);
4299 check_sql_result(res, g_conn, query->data, PGRES_TUPLES_OK);
4301 /* Expecting a single result only */
4302 ntups = PQntuples(res);
4305 write_msg(NULL, "Got %d rows instead of one from: %s",
4306 ntups, query->data);
4310 typlen = PQgetvalue(res, 0, PQfnumber(res, "typlen"));
4311 typinput = PQgetvalue(res, 0, PQfnumber(res, "typinput"));
4312 typoutput = PQgetvalue(res, 0, PQfnumber(res, "typoutput"));
4313 typreceive = PQgetvalue(res, 0, PQfnumber(res, "typreceive"));
4314 typsend = PQgetvalue(res, 0, PQfnumber(res, "typsend"));
4315 typanalyze = PQgetvalue(res, 0, PQfnumber(res, "typanalyze"));
4316 typinputoid = atooid(PQgetvalue(res, 0, PQfnumber(res, "typinputoid")));
4317 typoutputoid = atooid(PQgetvalue(res, 0, PQfnumber(res, "typoutputoid")));
4318 typreceiveoid = atooid(PQgetvalue(res, 0, PQfnumber(res, "typreceiveoid")));
4319 typsendoid = atooid(PQgetvalue(res, 0, PQfnumber(res, "typsendoid")));
4320 typanalyzeoid = atooid(PQgetvalue(res, 0, PQfnumber(res, "typanalyzeoid")));
4321 typdelim = PQgetvalue(res, 0, PQfnumber(res, "typdelim"));
4322 if (PQgetisnull(res, 0, PQfnumber(res, "typdefault")))
4325 typdefault = PQgetvalue(res, 0, PQfnumber(res, "typdefault"));
4326 typbyval = PQgetvalue(res, 0, PQfnumber(res, "typbyval"));
4327 typalign = PQgetvalue(res, 0, PQfnumber(res, "typalign"));
4328 typstorage = PQgetvalue(res, 0, PQfnumber(res, "typstorage"));
4331 * DROP must be fully qualified in case same name appears in
4334 appendPQExpBuffer(delq, "DROP TYPE %s.",
4335 fmtId(tinfo->typnamespace->nspname));
4336 appendPQExpBuffer(delq, "%s CASCADE;\n",
4337 fmtId(tinfo->typname));
4339 appendPQExpBuffer(q,
4340 "CREATE TYPE %s (\n"
4341 " INTERNALLENGTH = %s",
4342 fmtId(tinfo->typname),
4343 (strcmp(typlen, "-1") == 0) ? "variable" : typlen);
4345 if (fout->remoteVersion >= 70300)
4347 /* regproc result is correctly quoted as of 7.3 */
4348 appendPQExpBuffer(q, ",\n INPUT = %s", typinput);
4349 appendPQExpBuffer(q, ",\n OUTPUT = %s", typoutput);
4350 if (OidIsValid(typreceiveoid))
4351 appendPQExpBuffer(q, ",\n RECEIVE = %s", typreceive);
4352 if (OidIsValid(typsendoid))
4353 appendPQExpBuffer(q, ",\n SEND = %s", typsend);
4354 if (OidIsValid(typanalyzeoid))
4355 appendPQExpBuffer(q, ",\n ANALYZE = %s", typanalyze);
4359 /* regproc delivers an unquoted name before 7.3 */
4360 /* cannot combine these because fmtId uses static result area */
4361 appendPQExpBuffer(q, ",\n INPUT = %s", fmtId(typinput));
4362 appendPQExpBuffer(q, ",\n OUTPUT = %s", fmtId(typoutput));
4363 /* no chance that receive/send/analyze need be printed */
4366 if (typdefault != NULL)
4368 appendPQExpBuffer(q, ",\n DEFAULT = ");
4369 appendStringLiteral(q, typdefault, true);
4376 /* reselect schema in case changed by function dump */
4377 selectSourceSchema(tinfo->typnamespace->nspname);
4378 elemType = getFormattedTypeName(tinfo->typelem, zeroAsOpaque);
4379 appendPQExpBuffer(q, ",\n ELEMENT = %s", elemType);
4383 if (typdelim && strcmp(typdelim, ",") != 0)
4385 appendPQExpBuffer(q, ",\n DELIMITER = ");
4386 appendStringLiteral(q, typdelim, true);
4389 if (strcmp(typalign, "c") == 0)
4390 appendPQExpBuffer(q, ",\n ALIGNMENT = char");
4391 else if (strcmp(typalign, "s") == 0)
4392 appendPQExpBuffer(q, ",\n ALIGNMENT = int2");
4393 else if (strcmp(typalign, "i") == 0)
4394 appendPQExpBuffer(q, ",\n ALIGNMENT = int4");
4395 else if (strcmp(typalign, "d") == 0)
4396 appendPQExpBuffer(q, ",\n ALIGNMENT = double");
4398 if (strcmp(typstorage, "p") == 0)
4399 appendPQExpBuffer(q, ",\n STORAGE = plain");
4400 else if (strcmp(typstorage, "e") == 0)
4401 appendPQExpBuffer(q, ",\n STORAGE = external");
4402 else if (strcmp(typstorage, "x") == 0)
4403 appendPQExpBuffer(q, ",\n STORAGE = extended");
4404 else if (strcmp(typstorage, "m") == 0)
4405 appendPQExpBuffer(q, ",\n STORAGE = main");
4407 if (strcmp(typbyval, "t") == 0)
4408 appendPQExpBuffer(q, ",\n PASSEDBYVALUE");
4410 appendPQExpBuffer(q, "\n);\n");
4412 ArchiveEntry(fout, tinfo->dobj.catId, tinfo->dobj.dumpId,
4414 tinfo->typnamespace->nspname,
4416 "TYPE", q->data, delq->data, NULL,
4417 tinfo->dobj.dependencies, tinfo->dobj.nDeps,
4420 /* Dump Type Comments */
4421 resetPQExpBuffer(q);
4423 appendPQExpBuffer(q, "TYPE %s", fmtId(tinfo->typname));
4424 dumpComment(fout, q->data,
4425 tinfo->typnamespace->nspname, tinfo->usename,
4426 tinfo->dobj.catId, 0, tinfo->dobj.dumpId);
4429 destroyPQExpBuffer(q);
4430 destroyPQExpBuffer(delq);
4431 destroyPQExpBuffer(query);
4436 * writes out to fout the queries to recreate a user-defined domain
4439 dumpDomain(Archive *fout, TypeInfo *tinfo)
4441 PQExpBuffer q = createPQExpBuffer();
4442 PQExpBuffer delq = createPQExpBuffer();
4443 PQExpBuffer query = createPQExpBuffer();
4451 /* Set proper schema search path so type references list correctly */
4452 selectSourceSchema(tinfo->typnamespace->nspname);
4454 /* Fetch domain specific details */
4455 /* We assume here that remoteVersion must be at least 70300 */
4456 appendPQExpBuffer(query, "SELECT typnotnull, "
4457 "pg_catalog.format_type(typbasetype, typtypmod) as typdefn, "
4459 "FROM pg_catalog.pg_type "
4460 "WHERE oid = '%u'::pg_catalog.oid",
4461 tinfo->dobj.catId.oid);
4463 res = PQexec(g_conn, query->data);
4464 check_sql_result(res, g_conn, query->data, PGRES_TUPLES_OK);
4466 /* Expecting a single result only */
4467 ntups = PQntuples(res);
4470 write_msg(NULL, "Got %d rows instead of one from: %s",
4471 ntups, query->data);
4475 typnotnull = PQgetvalue(res, 0, PQfnumber(res, "typnotnull"));
4476 typdefn = PQgetvalue(res, 0, PQfnumber(res, "typdefn"));
4477 if (PQgetisnull(res, 0, PQfnumber(res, "typdefault")))
4480 typdefault = PQgetvalue(res, 0, PQfnumber(res, "typdefault"));
4482 appendPQExpBuffer(q,
4483 "CREATE DOMAIN %s AS %s",
4484 fmtId(tinfo->typname),
4487 if (typnotnull[0] == 't')
4488 appendPQExpBuffer(q, " NOT NULL");
4491 appendPQExpBuffer(q, " DEFAULT %s", typdefault);
4496 * Add any CHECK constraints for the domain
4498 for (i = 0; i < tinfo->nDomChecks; i++)
4500 ConstraintInfo *domcheck = &(tinfo->domChecks[i]);
4502 if (!domcheck->separate)
4503 appendPQExpBuffer(q, "\n\tCONSTRAINT %s %s",
4504 fmtId(domcheck->conname), domcheck->condef);
4507 appendPQExpBuffer(q, ";\n");
4510 * DROP must be fully qualified in case same name appears in
4513 appendPQExpBuffer(delq, "DROP DOMAIN %s.",
4514 fmtId(tinfo->typnamespace->nspname));
4515 appendPQExpBuffer(delq, "%s;\n",
4516 fmtId(tinfo->typname));
4518 ArchiveEntry(fout, tinfo->dobj.catId, tinfo->dobj.dumpId,
4520 tinfo->typnamespace->nspname,
4522 "DOMAIN", q->data, delq->data, NULL,
4523 tinfo->dobj.dependencies, tinfo->dobj.nDeps,
4526 /* Dump Domain Comments */
4527 resetPQExpBuffer(q);
4529 appendPQExpBuffer(q, "DOMAIN %s", fmtId(tinfo->typname));
4530 dumpComment(fout, q->data,
4531 tinfo->typnamespace->nspname, tinfo->usename,
4532 tinfo->dobj.catId, 0, tinfo->dobj.dumpId);
4534 destroyPQExpBuffer(q);
4535 destroyPQExpBuffer(delq);
4536 destroyPQExpBuffer(query);
4541 * writes out to fout the queries to recreate a user-defined stand-alone
4545 dumpCompositeType(Archive *fout, TypeInfo *tinfo)
4547 PQExpBuffer q = createPQExpBuffer();
4548 PQExpBuffer delq = createPQExpBuffer();
4549 PQExpBuffer query = createPQExpBuffer();
4556 /* Set proper schema search path so type references list correctly */
4557 selectSourceSchema(tinfo->typnamespace->nspname);
4559 /* Fetch type specific details */
4560 /* We assume here that remoteVersion must be at least 70300 */
4562 appendPQExpBuffer(query, "SELECT a.attname, "
4563 "pg_catalog.format_type(a.atttypid, a.atttypmod) as atttypdefn "
4564 "FROM pg_catalog.pg_type t, pg_catalog.pg_attribute a "
4565 "WHERE t.oid = '%u'::pg_catalog.oid "
4566 "AND a.attrelid = t.typrelid "
4567 "AND NOT a.attisdropped "
4568 "ORDER BY a.attnum ",
4569 tinfo->dobj.catId.oid);
4571 res = PQexec(g_conn, query->data);
4572 check_sql_result(res, g_conn, query->data, PGRES_TUPLES_OK);
4574 /* Expecting at least a single result */
4575 ntups = PQntuples(res);
4578 write_msg(NULL, "query yielded no rows: %s\n", query->data);
4582 i_attname = PQfnumber(res, "attname");
4583 i_atttypdefn = PQfnumber(res, "atttypdefn");
4585 appendPQExpBuffer(q, "CREATE TYPE %s AS (",
4586 fmtId(tinfo->typname));
4588 for (i = 0; i < ntups; i++)
4593 attname = PQgetvalue(res, i, i_attname);
4594 atttypdefn = PQgetvalue(res, i, i_atttypdefn);
4596 appendPQExpBuffer(q, "\n\t%s %s", fmtId(attname), atttypdefn);
4598 appendPQExpBuffer(q, ",");
4600 appendPQExpBuffer(q, "\n);\n");
4603 * DROP must be fully qualified in case same name appears in
4606 appendPQExpBuffer(delq, "DROP TYPE %s.",
4607 fmtId(tinfo->typnamespace->nspname));
4608 appendPQExpBuffer(delq, "%s;\n",
4609 fmtId(tinfo->typname));
4611 ArchiveEntry(fout, tinfo->dobj.catId, tinfo->dobj.dumpId,
4613 tinfo->typnamespace->nspname,
4615 "TYPE", q->data, delq->data, NULL,
4616 tinfo->dobj.dependencies, tinfo->dobj.nDeps,
4620 /* Dump Type Comments */
4621 resetPQExpBuffer(q);
4623 appendPQExpBuffer(q, "TYPE %s", fmtId(tinfo->typname));
4624 dumpComment(fout, q->data,
4625 tinfo->typnamespace->nspname, tinfo->usename,
4626 tinfo->dobj.catId, 0, tinfo->dobj.dumpId);
4629 destroyPQExpBuffer(q);
4630 destroyPQExpBuffer(delq);
4631 destroyPQExpBuffer(query);
4636 * writes out to fout the queries to recreate a user-defined
4637 * procedural language
4640 dumpProcLang(Archive *fout, ProcLangInfo *plang)
4646 FuncInfo *validatorInfo = NULL;
4652 * Current theory is to dump PLs iff their underlying functions
4653 * will be dumped (are in a dumpable namespace, or have a
4654 * non-system OID in pre-7.3 databases). Actually, we treat the
4655 * PL itself as being in the underlying function's namespace,
4656 * though it isn't really. This avoids searchpath problems for
4657 * the HANDLER clause.
4659 * If the underlying function is in the pg_catalog namespace,
4660 * we won't have loaded it into finfo[] at all; therefore,
4661 * treat failure to find it in finfo[] as indicating we shouldn't
4662 * dump it, not as an error condition. Ditto for the validator.
4665 funcInfo = findFuncByOid(plang->lanplcallfoid);
4666 if (funcInfo == NULL)
4669 if (!funcInfo->pronamespace->dump)
4672 if (OidIsValid(plang->lanvalidator))
4674 validatorInfo = findFuncByOid(plang->lanvalidator);
4675 if (validatorInfo == NULL)
4679 defqry = createPQExpBuffer();
4680 delqry = createPQExpBuffer();
4682 qlanname = strdup(fmtId(plang->lanname));
4684 appendPQExpBuffer(delqry, "DROP PROCEDURAL LANGUAGE %s;\n",
4687 appendPQExpBuffer(defqry, "CREATE %sPROCEDURAL LANGUAGE %s",
4688 plang->lanpltrusted ? "TRUSTED " : "",
4690 appendPQExpBuffer(defqry, " HANDLER %s",
4691 fmtId(funcInfo->proname));
4692 if (OidIsValid(plang->lanvalidator))
4694 appendPQExpBuffer(defqry, " VALIDATOR ");
4695 /* Cope with possibility that validator is in different schema */
4696 if (validatorInfo->pronamespace != funcInfo->pronamespace)
4697 appendPQExpBuffer(defqry, "%s.",
4698 fmtId(validatorInfo->pronamespace->nspname));
4699 appendPQExpBuffer(defqry, "%s",
4700 fmtId(validatorInfo->proname));
4702 appendPQExpBuffer(defqry, ";\n");
4704 ArchiveEntry(fout, plang->dobj.catId, plang->dobj.dumpId,
4706 funcInfo->pronamespace->nspname, "",
4707 "PROCEDURAL LANGUAGE",
4708 defqry->data, delqry->data, NULL,
4709 plang->dobj.dependencies, plang->dobj.nDeps,
4712 /* Dump Proc Lang Comments */
4713 resetPQExpBuffer(defqry);
4715 appendPQExpBuffer(defqry, "LANGUAGE %s", qlanname);
4716 dumpComment(fout, defqry->data,
4718 plang->dobj.catId, 0, plang->dobj.dumpId);
4720 if (plang->lanpltrusted)
4721 dumpACL(fout, plang->dobj.catId, plang->dobj.dumpId, "LANGUAGE",
4722 qlanname, plang->lanname,
4723 funcInfo->pronamespace->nspname,
4724 NULL, plang->lanacl);
4728 destroyPQExpBuffer(defqry);
4729 destroyPQExpBuffer(delqry);
4733 * format_function_signature: generate function name and argument list
4735 * The argument type names are qualified if needed. The function name
4736 * is never qualified.
4738 * argnames may be NULL if no names are available.
4741 format_function_signature(FuncInfo *finfo, char **argnames,
4747 initPQExpBuffer(&fn);
4749 appendPQExpBuffer(&fn, "%s(", fmtId(finfo->proname));
4751 appendPQExpBuffer(&fn, "%s(", finfo->proname);
4752 for (j = 0; j < finfo->nargs; j++)
4757 typname = getFormattedTypeName(finfo->argtypes[j], zeroAsOpaque);
4759 argname = argnames ? argnames[j] : (char *) NULL;
4760 if (argname && argname[0] == '\0')
4763 appendPQExpBuffer(&fn, "%s%s%s%s",
4764 (j > 0) ? ", " : "",
4765 argname ? fmtId(argname) : "",
4770 appendPQExpBuffer(&fn, ")");
4777 * dump out one function
4780 dumpFunc(Archive *fout, FuncInfo *finfo)
4799 char **argnamearray = NULL;
4801 /* Dump only funcs in dumpable namespaces */
4802 if (!finfo->pronamespace->dump || dataOnly)
4805 query = createPQExpBuffer();
4806 q = createPQExpBuffer();
4807 delqry = createPQExpBuffer();
4808 asPart = createPQExpBuffer();
4810 /* Set proper schema search path so type references list correctly */
4811 selectSourceSchema(finfo->pronamespace->nspname);
4813 /* Fetch function-specific details */
4814 if (g_fout->remoteVersion >= 70500)
4816 appendPQExpBuffer(query,
4817 "SELECT proretset, prosrc, probin, "
4819 "provolatile, proisstrict, prosecdef, "
4820 "(SELECT lanname FROM pg_catalog.pg_language WHERE oid = prolang) as lanname "
4821 "FROM pg_catalog.pg_proc "
4822 "WHERE oid = '%u'::pg_catalog.oid",
4823 finfo->dobj.catId.oid);
4825 else if (g_fout->remoteVersion >= 70300)
4827 appendPQExpBuffer(query,
4828 "SELECT proretset, prosrc, probin, "
4829 "null::text as proargnames, "
4830 "provolatile, proisstrict, prosecdef, "
4831 "(SELECT lanname FROM pg_catalog.pg_language WHERE oid = prolang) as lanname "
4832 "FROM pg_catalog.pg_proc "
4833 "WHERE oid = '%u'::pg_catalog.oid",
4834 finfo->dobj.catId.oid);
4836 else if (g_fout->remoteVersion >= 70100)
4838 appendPQExpBuffer(query,
4839 "SELECT proretset, prosrc, probin, "
4840 "null::text as proargnames, "
4841 "case when proiscachable then 'i' else 'v' end as provolatile, "
4843 "'f'::boolean as prosecdef, "
4844 "(SELECT lanname FROM pg_language WHERE oid = prolang) as lanname "
4846 "WHERE oid = '%u'::oid",
4847 finfo->dobj.catId.oid);
4851 appendPQExpBuffer(query,
4852 "SELECT proretset, prosrc, probin, "
4853 "null::text as proargnames, "
4854 "case when proiscachable then 'i' else 'v' end as provolatile, "
4855 "'f'::boolean as proisstrict, "
4856 "'f'::boolean as prosecdef, "
4857 "(SELECT lanname FROM pg_language WHERE oid = prolang) as lanname "
4859 "WHERE oid = '%u'::oid",
4860 finfo->dobj.catId.oid);
4863 res = PQexec(g_conn, query->data);
4864 check_sql_result(res, g_conn, query->data, PGRES_TUPLES_OK);
4866 /* Expecting a single result only */
4867 ntups = PQntuples(res);
4870 write_msg(NULL, "Got %d rows instead of one from: %s",
4871 ntups, query->data);
4875 proretset = PQgetvalue(res, 0, PQfnumber(res, "proretset"));
4876 prosrc = PQgetvalue(res, 0, PQfnumber(res, "prosrc"));
4877 probin = PQgetvalue(res, 0, PQfnumber(res, "probin"));
4878 proargnames = PQgetvalue(res, 0, PQfnumber(res, "proargnames"));
4879 provolatile = PQgetvalue(res, 0, PQfnumber(res, "provolatile"));
4880 proisstrict = PQgetvalue(res, 0, PQfnumber(res, "proisstrict"));
4881 prosecdef = PQgetvalue(res, 0, PQfnumber(res, "prosecdef"));
4882 lanname = PQgetvalue(res, 0, PQfnumber(res, "lanname"));
4885 * See backend/commands/define.c for details of how the 'AS' clause is
4888 if (strcmp(probin, "-") != 0)
4890 appendPQExpBuffer(asPart, "AS ");
4891 appendStringLiteral(asPart, probin, true);
4892 if (strcmp(prosrc, "-") != 0)
4894 appendPQExpBuffer(asPart, ", ");
4895 appendStringLiteral(asPart, prosrc, false);
4900 if (strcmp(prosrc, "-") != 0)
4902 appendPQExpBuffer(asPart, "AS ");
4903 appendStringLiteral(asPart, prosrc, false);
4907 if (proargnames && *proargnames)
4911 if (!parsePGArray(proargnames, &argnamearray, &nitems) ||
4912 nitems != finfo->nargs)
4914 write_msg(NULL, "WARNING: could not parse proargnames array\n");
4917 argnamearray = NULL;
4921 funcsig = format_function_signature(finfo, argnamearray, true);
4922 funcsig_tag = format_function_signature(finfo, NULL, false);
4925 * DROP must be fully qualified in case same name appears in
4928 appendPQExpBuffer(delqry, "DROP FUNCTION %s.%s;\n",
4929 fmtId(finfo->pronamespace->nspname),
4932 rettypename = getFormattedTypeName(finfo->prorettype, zeroAsOpaque);
4934 appendPQExpBuffer(q, "CREATE FUNCTION %s ", funcsig);
4935 appendPQExpBuffer(q, "RETURNS %s%s\n %s\n LANGUAGE %s",
4936 (proretset[0] == 't') ? "SETOF " : "",
4943 if (provolatile[0] != PROVOLATILE_VOLATILE)
4945 if (provolatile[0] == PROVOLATILE_IMMUTABLE)
4946 appendPQExpBuffer(q, " IMMUTABLE");
4947 else if (provolatile[0] == PROVOLATILE_STABLE)
4948 appendPQExpBuffer(q, " STABLE");
4949 else if (provolatile[0] != PROVOLATILE_VOLATILE)
4951 write_msg(NULL, "unrecognized provolatile value for function \"%s\"\n",
4957 if (proisstrict[0] == 't')
4958 appendPQExpBuffer(q, " STRICT");
4960 if (prosecdef[0] == 't')
4961 appendPQExpBuffer(q, " SECURITY DEFINER");
4963 appendPQExpBuffer(q, ";\n");
4965 ArchiveEntry(fout, finfo->dobj.catId, finfo->dobj.dumpId,
4967 finfo->pronamespace->nspname,
4969 "FUNCTION", q->data, delqry->data, NULL,
4970 finfo->dobj.dependencies, finfo->dobj.nDeps,
4973 /* Dump Function Comments */
4974 resetPQExpBuffer(q);
4975 appendPQExpBuffer(q, "FUNCTION %s", funcsig);
4976 dumpComment(fout, q->data,
4977 finfo->pronamespace->nspname, finfo->usename,
4978 finfo->dobj.catId, 0, finfo->dobj.dumpId);
4980 dumpACL(fout, finfo->dobj.catId, finfo->dobj.dumpId, "FUNCTION",
4981 funcsig, funcsig_tag,
4982 finfo->pronamespace->nspname,
4983 finfo->usename, finfo->proacl);
4987 destroyPQExpBuffer(query);
4988 destroyPQExpBuffer(q);
4989 destroyPQExpBuffer(delqry);
4990 destroyPQExpBuffer(asPart);
4999 * Dump a user-defined cast
5002 dumpCast(Archive *fout, CastInfo *cast)
5006 PQExpBuffer castsig;
5007 FuncInfo *funcInfo = NULL;
5008 TypeInfo *sourceInfo;
5009 TypeInfo *targetInfo;
5014 if (OidIsValid(cast->castfunc))
5016 funcInfo = findFuncByOid(cast->castfunc);
5017 if (funcInfo == NULL)
5022 * As per discussion we dump casts if one or more of the underlying
5023 * objects (the conversion function and the two data types) are not
5024 * builtin AND if all of the non-builtin objects namespaces are
5025 * included in the dump. Builtin meaning, the namespace name does
5026 * not start with "pg_".
5028 sourceInfo = findTypeByOid(cast->castsource);
5029 targetInfo = findTypeByOid(cast->casttarget);
5031 if (sourceInfo == NULL || targetInfo == NULL)
5035 * Skip this cast if all objects are from pg_
5037 if ((funcInfo == NULL || strncmp(funcInfo->pronamespace->nspname, "pg_", 3) == 0) &&
5038 strncmp(sourceInfo->typnamespace->nspname, "pg_", 3) == 0 &&
5039 strncmp(targetInfo->typnamespace->nspname, "pg_", 3) == 0)
5043 * Skip cast if function isn't from pg_ and that namespace is
5047 strncmp(funcInfo->pronamespace->nspname, "pg_", 3) != 0 &&
5048 !funcInfo->pronamespace->dump)
5052 * Same for the Source type
5054 if (strncmp(sourceInfo->typnamespace->nspname, "pg_", 3) != 0 &&
5055 !sourceInfo->typnamespace->dump)
5059 * and the target type.
5061 if (strncmp(targetInfo->typnamespace->nspname, "pg_", 3) != 0 &&
5062 !targetInfo->typnamespace->dump)
5065 /* Make sure we are in proper schema (needed for getFormattedTypeName) */
5066 selectSourceSchema("pg_catalog");
5068 defqry = createPQExpBuffer();
5069 delqry = createPQExpBuffer();
5070 castsig = createPQExpBuffer();
5072 appendPQExpBuffer(delqry, "DROP CAST (%s AS %s);\n",
5073 getFormattedTypeName(cast->castsource, zeroAsNone),
5074 getFormattedTypeName(cast->casttarget, zeroAsNone));
5076 appendPQExpBuffer(defqry, "CREATE CAST (%s AS %s) ",
5077 getFormattedTypeName(cast->castsource, zeroAsNone),
5078 getFormattedTypeName(cast->casttarget, zeroAsNone));
5080 if (!OidIsValid(cast->castfunc))
5081 appendPQExpBuffer(defqry, "WITHOUT FUNCTION");
5085 * Always qualify the function name, in case it is not in pg_catalog
5086 * schema (format_function_signature won't qualify it).
5088 appendPQExpBuffer(defqry, "WITH FUNCTION %s.",
5089 fmtId(funcInfo->pronamespace->nspname));
5090 appendPQExpBuffer(defqry, "%s",
5091 format_function_signature(funcInfo, NULL, true));
5094 if (cast->castcontext == 'a')
5095 appendPQExpBuffer(defqry, " AS ASSIGNMENT");
5096 else if (cast->castcontext == 'i')
5097 appendPQExpBuffer(defqry, " AS IMPLICIT");
5098 appendPQExpBuffer(defqry, ";\n");
5100 appendPQExpBuffer(castsig, "CAST (%s AS %s)",
5101 getFormattedTypeName(cast->castsource, zeroAsNone),
5102 getFormattedTypeName(cast->casttarget, zeroAsNone));
5104 ArchiveEntry(fout, cast->dobj.catId, cast->dobj.dumpId,
5106 sourceInfo->typnamespace->nspname, "",
5107 "CAST", defqry->data, delqry->data, NULL,
5108 cast->dobj.dependencies, cast->dobj.nDeps,
5111 /* Dump Cast Comments */
5112 resetPQExpBuffer(defqry);
5113 appendPQExpBuffer(defqry, "CAST (%s AS %s)",
5114 getFormattedTypeName(cast->castsource, zeroAsNone),
5115 getFormattedTypeName(cast->casttarget, zeroAsNone));
5116 dumpComment(fout, defqry->data,
5118 cast->dobj.catId, 0, cast->dobj.dumpId);
5120 destroyPQExpBuffer(defqry);
5121 destroyPQExpBuffer(delqry);
5122 destroyPQExpBuffer(castsig);
5127 * write out a single operator definition
5130 dumpOpr(Archive *fout, OprInfo *oprinfo)
5136 PQExpBuffer details;
5167 /* Dump only operators in dumpable namespaces */
5168 if (!oprinfo->oprnamespace->dump || dataOnly)
5172 * some operators are invalid because they were the result of user
5173 * defining operators before commutators exist
5175 if (!OidIsValid(oprinfo->oprcode))
5178 query = createPQExpBuffer();
5179 q = createPQExpBuffer();
5180 delq = createPQExpBuffer();
5181 oprid = createPQExpBuffer();
5182 details = createPQExpBuffer();
5184 /* Make sure we are in proper schema so regoperator works correctly */
5185 selectSourceSchema(oprinfo->oprnamespace->nspname);
5187 if (g_fout->remoteVersion >= 70300)
5189 appendPQExpBuffer(query, "SELECT oprkind, "
5190 "oprcode::pg_catalog.regprocedure, "
5191 "oprleft::pg_catalog.regtype, "
5192 "oprright::pg_catalog.regtype, "
5193 "oprcom::pg_catalog.regoperator, "
5194 "oprnegate::pg_catalog.regoperator, "
5195 "oprrest::pg_catalog.regprocedure, "
5196 "oprjoin::pg_catalog.regprocedure, "
5198 "oprlsortop::pg_catalog.regoperator, "
5199 "oprrsortop::pg_catalog.regoperator, "
5200 "oprltcmpop::pg_catalog.regoperator, "
5201 "oprgtcmpop::pg_catalog.regoperator "
5202 "from pg_catalog.pg_operator "
5203 "where oid = '%u'::pg_catalog.oid",
5204 oprinfo->dobj.catId.oid);
5206 else if (g_fout->remoteVersion >= 70100)
5208 appendPQExpBuffer(query, "SELECT oprkind, oprcode, "
5209 "CASE WHEN oprleft = 0 THEN '-' "
5210 "ELSE format_type(oprleft, NULL) END as oprleft, "
5211 "CASE WHEN oprright = 0 THEN '-' "
5212 "ELSE format_type(oprright, NULL) END as oprright, "
5213 "oprcom, oprnegate, oprrest, oprjoin, "
5214 "oprcanhash, oprlsortop, oprrsortop, "
5215 "0 as oprltcmpop, 0 as oprgtcmpop "
5217 "where oid = '%u'::oid",
5218 oprinfo->dobj.catId.oid);
5222 appendPQExpBuffer(query, "SELECT oprkind, oprcode, "
5223 "CASE WHEN oprleft = 0 THEN '-'::name "
5224 "ELSE (select typname from pg_type where oid = oprleft) END as oprleft, "
5225 "CASE WHEN oprright = 0 THEN '-'::name "
5226 "ELSE (select typname from pg_type where oid = oprright) END as oprright, "
5227 "oprcom, oprnegate, oprrest, oprjoin, "
5228 "oprcanhash, oprlsortop, oprrsortop, "
5229 "0 as oprltcmpop, 0 as oprgtcmpop "
5231 "where oid = '%u'::oid",
5232 oprinfo->dobj.catId.oid);
5235 res = PQexec(g_conn, query->data);
5236 check_sql_result(res, g_conn, query->data, PGRES_TUPLES_OK);
5238 /* Expecting a single result only */
5239 ntups = PQntuples(res);
5242 write_msg(NULL, "Got %d rows instead of one from: %s",
5243 ntups, query->data);
5247 i_oprkind = PQfnumber(res, "oprkind");
5248 i_oprcode = PQfnumber(res, "oprcode");
5249 i_oprleft = PQfnumber(res, "oprleft");
5250 i_oprright = PQfnumber(res, "oprright");
5251 i_oprcom = PQfnumber(res, "oprcom");
5252 i_oprnegate = PQfnumber(res, "oprnegate");
5253 i_oprrest = PQfnumber(res, "oprrest");
5254 i_oprjoin = PQfnumber(res, "oprjoin");
5255 i_oprcanhash = PQfnumber(res, "oprcanhash");
5256 i_oprlsortop = PQfnumber(res, "oprlsortop");
5257 i_oprrsortop = PQfnumber(res, "oprrsortop");
5258 i_oprltcmpop = PQfnumber(res, "oprltcmpop");
5259 i_oprgtcmpop = PQfnumber(res, "oprgtcmpop");
5261 oprkind = PQgetvalue(res, 0, i_oprkind);
5262 oprcode = PQgetvalue(res, 0, i_oprcode);
5263 oprleft = PQgetvalue(res, 0, i_oprleft);
5264 oprright = PQgetvalue(res, 0, i_oprright);
5265 oprcom = PQgetvalue(res, 0, i_oprcom);
5266 oprnegate = PQgetvalue(res, 0, i_oprnegate);
5267 oprrest = PQgetvalue(res, 0, i_oprrest);
5268 oprjoin = PQgetvalue(res, 0, i_oprjoin);
5269 oprcanhash = PQgetvalue(res, 0, i_oprcanhash);
5270 oprlsortop = PQgetvalue(res, 0, i_oprlsortop);
5271 oprrsortop = PQgetvalue(res, 0, i_oprrsortop);
5272 oprltcmpop = PQgetvalue(res, 0, i_oprltcmpop);
5273 oprgtcmpop = PQgetvalue(res, 0, i_oprgtcmpop);
5275 appendPQExpBuffer(details, " PROCEDURE = %s",
5276 convertRegProcReference(oprcode));
5278 appendPQExpBuffer(oprid, "%s (",
5282 * right unary means there's a left arg and left unary means there's a
5285 if (strcmp(oprkind, "r") == 0 ||
5286 strcmp(oprkind, "b") == 0)
5288 if (g_fout->remoteVersion >= 70100)
5291 name = fmtId(oprleft);
5292 appendPQExpBuffer(details, ",\n LEFTARG = %s", name);
5293 appendPQExpBuffer(oprid, "%s", name);
5296 appendPQExpBuffer(oprid, "NONE");
5298 if (strcmp(oprkind, "l") == 0 ||
5299 strcmp(oprkind, "b") == 0)
5301 if (g_fout->remoteVersion >= 70100)
5304 name = fmtId(oprright);
5305 appendPQExpBuffer(details, ",\n RIGHTARG = %s", name);
5306 appendPQExpBuffer(oprid, ", %s)", name);
5309 appendPQExpBuffer(oprid, ", NONE)");
5311 name = convertOperatorReference(oprcom);
5313 appendPQExpBuffer(details, ",\n COMMUTATOR = %s", name);
5315 name = convertOperatorReference(oprnegate);
5317 appendPQExpBuffer(details, ",\n NEGATOR = %s", name);
5319 if (strcmp(oprcanhash, "t") == 0)
5320 appendPQExpBuffer(details, ",\n HASHES");
5322 name = convertRegProcReference(oprrest);
5324 appendPQExpBuffer(details, ",\n RESTRICT = %s", name);
5326 name = convertRegProcReference(oprjoin);
5328 appendPQExpBuffer(details, ",\n JOIN = %s", name);
5330 name = convertOperatorReference(oprlsortop);
5332 appendPQExpBuffer(details, ",\n SORT1 = %s", name);
5334 name = convertOperatorReference(oprrsortop);
5336 appendPQExpBuffer(details, ",\n SORT2 = %s", name);
5338 name = convertOperatorReference(oprltcmpop);
5340 appendPQExpBuffer(details, ",\n LTCMP = %s", name);
5342 name = convertOperatorReference(oprgtcmpop);
5344 appendPQExpBuffer(details, ",\n GTCMP = %s", name);
5347 * DROP must be fully qualified in case same name appears in
5350 appendPQExpBuffer(delq, "DROP OPERATOR %s.%s;\n",
5351 fmtId(oprinfo->oprnamespace->nspname),
5354 appendPQExpBuffer(q, "CREATE OPERATOR %s (\n%s\n);\n",
5355 oprinfo->oprname, details->data);
5357 ArchiveEntry(fout, oprinfo->dobj.catId, oprinfo->dobj.dumpId,
5359 oprinfo->oprnamespace->nspname, oprinfo->usename,
5360 "OPERATOR", q->data, delq->data, NULL,
5361 oprinfo->dobj.dependencies, oprinfo->dobj.nDeps,
5364 /* Dump Operator Comments */
5365 resetPQExpBuffer(q);
5366 appendPQExpBuffer(q, "OPERATOR %s", oprid->data);
5367 dumpComment(fout, q->data,
5368 oprinfo->oprnamespace->nspname, oprinfo->usename,
5369 oprinfo->dobj.catId, 0, oprinfo->dobj.dumpId);
5373 destroyPQExpBuffer(query);
5374 destroyPQExpBuffer(q);
5375 destroyPQExpBuffer(delq);
5376 destroyPQExpBuffer(oprid);
5377 destroyPQExpBuffer(details);
5381 * Convert a function reference obtained from pg_operator
5383 * Returns what to print, or NULL if function references is InvalidOid
5385 * In 7.3 the input is a REGPROCEDURE display; we have to strip the
5386 * argument-types part. In prior versions, the input is a REGPROC display.
5389 convertRegProcReference(const char *proc)
5391 /* In all cases "-" means a null reference */
5392 if (strcmp(proc, "-") == 0)
5395 if (g_fout->remoteVersion >= 70300)
5401 name = strdup(proc);
5402 /* find non-double-quoted left paren */
5404 for (paren = name; *paren; paren++)
5406 if (*paren == '(' && !inquote)
5417 /* REGPROC before 7.3 does not quote its result */
5422 * Convert an operator cross-reference obtained from pg_operator
5424 * Returns what to print, or NULL to print nothing
5426 * In 7.3 the input is a REGOPERATOR display; we have to strip the
5427 * argument-types part. In prior versions, the input is just a
5428 * numeric OID, which we search our operator list for.
5431 convertOperatorReference(const char *opr)
5435 /* In all cases "0" means a null reference */
5436 if (strcmp(opr, "0") == 0)
5439 if (g_fout->remoteVersion >= 70300)
5446 /* find non-double-quoted left paren */
5448 for (paren = name; *paren; paren++)
5450 if (*paren == '(' && !inquote)
5461 oprInfo = findOprByOid(atooid(opr));
5462 if (oprInfo == NULL)
5464 write_msg(NULL, "WARNING: could not find operator with OID %s\n",
5468 return oprInfo->oprname;
5473 * write out a single operator class definition
5476 dumpOpclass(Archive *fout, OpclassInfo *opcinfo)
5504 /* Dump only opclasses in dumpable namespaces */
5505 if (!opcinfo->opcnamespace->dump || dataOnly)
5509 * XXX currently we do not implement dumping of operator classes from
5510 * pre-7.3 databases. This could be done but it seems not worth the
5513 if (g_fout->remoteVersion < 70300)
5516 query = createPQExpBuffer();
5517 q = createPQExpBuffer();
5518 delq = createPQExpBuffer();
5520 /* Make sure we are in proper schema so regoperator works correctly */
5521 selectSourceSchema(opcinfo->opcnamespace->nspname);
5523 /* Get additional fields from the pg_opclass row */
5524 appendPQExpBuffer(query, "SELECT opcintype::pg_catalog.regtype, "
5525 "opckeytype::pg_catalog.regtype, "
5527 "(SELECT amname FROM pg_catalog.pg_am WHERE oid = opcamid) AS amname "
5528 "FROM pg_catalog.pg_opclass "
5529 "WHERE oid = '%u'::pg_catalog.oid",
5530 opcinfo->dobj.catId.oid);
5532 res = PQexec(g_conn, query->data);
5533 check_sql_result(res, g_conn, query->data, PGRES_TUPLES_OK);
5535 /* Expecting a single result only */
5536 ntups = PQntuples(res);
5539 write_msg(NULL, "Got %d rows instead of one from: %s",
5540 ntups, query->data);
5544 i_opcintype = PQfnumber(res, "opcintype");
5545 i_opckeytype = PQfnumber(res, "opckeytype");
5546 i_opcdefault = PQfnumber(res, "opcdefault");
5547 i_amname = PQfnumber(res, "amname");
5549 opcintype = PQgetvalue(res, 0, i_opcintype);
5550 opckeytype = PQgetvalue(res, 0, i_opckeytype);
5551 opcdefault = PQgetvalue(res, 0, i_opcdefault);
5552 /* amname will still be needed after we PQclear res */
5553 amname = strdup(PQgetvalue(res, 0, i_amname));
5556 * DROP must be fully qualified in case same name appears in
5559 appendPQExpBuffer(delq, "DROP OPERATOR CLASS %s",
5560 fmtId(opcinfo->opcnamespace->nspname));
5561 appendPQExpBuffer(delq, ".%s",
5562 fmtId(opcinfo->opcname));
5563 appendPQExpBuffer(delq, " USING %s;\n",
5566 /* Build the fixed portion of the CREATE command */
5567 appendPQExpBuffer(q, "CREATE OPERATOR CLASS %s\n ",
5568 fmtId(opcinfo->opcname));
5569 if (strcmp(opcdefault, "t") == 0)
5570 appendPQExpBuffer(q, "DEFAULT ");
5571 appendPQExpBuffer(q, "FOR TYPE %s USING %s AS\n ",
5577 if (strcmp(opckeytype, "-") != 0)
5579 appendPQExpBuffer(q, "STORAGE %s",
5587 * Now fetch and print the OPERATOR entries (pg_amop rows).
5589 resetPQExpBuffer(query);
5591 appendPQExpBuffer(query, "SELECT amopstrategy, amopreqcheck, "
5592 "amopopr::pg_catalog.regoperator "
5593 "FROM pg_catalog.pg_amop "
5594 "WHERE amopclaid = '%u'::pg_catalog.oid "
5595 "ORDER BY amopstrategy",
5596 opcinfo->dobj.catId.oid);
5598 res = PQexec(g_conn, query->data);
5599 check_sql_result(res, g_conn, query->data, PGRES_TUPLES_OK);
5601 ntups = PQntuples(res);
5603 i_amopstrategy = PQfnumber(res, "amopstrategy");
5604 i_amopreqcheck = PQfnumber(res, "amopreqcheck");
5605 i_amopopr = PQfnumber(res, "amopopr");
5607 for (i = 0; i < ntups; i++)
5609 amopstrategy = PQgetvalue(res, i, i_amopstrategy);
5610 amopreqcheck = PQgetvalue(res, i, i_amopreqcheck);
5611 amopopr = PQgetvalue(res, i, i_amopopr);
5614 appendPQExpBuffer(q, " ,\n ");
5616 appendPQExpBuffer(q, "OPERATOR %s %s",
5617 amopstrategy, amopopr);
5618 if (strcmp(amopreqcheck, "t") == 0)
5619 appendPQExpBuffer(q, " RECHECK");
5627 * Now fetch and print the FUNCTION entries (pg_amproc rows).
5629 resetPQExpBuffer(query);
5631 appendPQExpBuffer(query, "SELECT amprocnum, "
5632 "amproc::pg_catalog.regprocedure "
5633 "FROM pg_catalog.pg_amproc "
5634 "WHERE amopclaid = '%u'::pg_catalog.oid "
5635 "ORDER BY amprocnum",
5636 opcinfo->dobj.catId.oid);
5638 res = PQexec(g_conn, query->data);
5639 check_sql_result(res, g_conn, query->data, PGRES_TUPLES_OK);
5641 ntups = PQntuples(res);
5643 i_amprocnum = PQfnumber(res, "amprocnum");
5644 i_amproc = PQfnumber(res, "amproc");
5646 for (i = 0; i < ntups; i++)
5648 amprocnum = PQgetvalue(res, i, i_amprocnum);
5649 amproc = PQgetvalue(res, i, i_amproc);
5652 appendPQExpBuffer(q, " ,\n ");
5654 appendPQExpBuffer(q, "FUNCTION %s %s",
5662 appendPQExpBuffer(q, ";\n");
5664 ArchiveEntry(fout, opcinfo->dobj.catId, opcinfo->dobj.dumpId,
5666 opcinfo->opcnamespace->nspname, opcinfo->usename,
5667 "OPERATOR CLASS", q->data, delq->data, NULL,
5668 opcinfo->dobj.dependencies, opcinfo->dobj.nDeps,
5671 /* Dump Operator Class Comments */
5672 resetPQExpBuffer(q);
5673 appendPQExpBuffer(q, "OPERATOR CLASS %s",
5674 fmtId(opcinfo->opcname));
5675 appendPQExpBuffer(q, " USING %s",
5677 dumpComment(fout, q->data,
5678 NULL, opcinfo->usename,
5679 opcinfo->dobj.catId, 0, opcinfo->dobj.dumpId);
5682 destroyPQExpBuffer(query);
5683 destroyPQExpBuffer(q);
5684 destroyPQExpBuffer(delq);
5689 * write out a single conversion definition
5692 dumpConversion(Archive *fout, ConvInfo *convinfo)
5697 PQExpBuffer details;
5701 int i_conforencoding;
5702 int i_contoencoding;
5705 const char *conname;
5706 const char *conforencoding;
5707 const char *contoencoding;
5708 const char *conproc;
5711 /* Dump only conversions in dumpable namespaces */
5712 if (!convinfo->connamespace->dump || dataOnly)
5715 query = createPQExpBuffer();
5716 q = createPQExpBuffer();
5717 delq = createPQExpBuffer();
5718 details = createPQExpBuffer();
5720 /* Make sure we are in proper schema */
5721 selectSourceSchema(convinfo->connamespace->nspname);
5723 /* Get conversion-specific details */
5724 appendPQExpBuffer(query, "SELECT conname, "
5725 "pg_catalog.pg_encoding_to_char(conforencoding) AS conforencoding, "
5726 "pg_catalog.pg_encoding_to_char(contoencoding) AS contoencoding, "
5727 "conproc, condefault "
5728 "FROM pg_catalog.pg_conversion c "
5729 "WHERE c.oid = '%u'::pg_catalog.oid",
5730 convinfo->dobj.catId.oid);
5732 res = PQexec(g_conn, query->data);
5733 check_sql_result(res, g_conn, query->data, PGRES_TUPLES_OK);
5735 /* Expecting a single result only */
5736 ntups = PQntuples(res);
5739 write_msg(NULL, "Got %d rows instead of one from: %s",
5740 ntups, query->data);
5744 i_conname = PQfnumber(res, "conname");
5745 i_conforencoding = PQfnumber(res, "conforencoding");
5746 i_contoencoding = PQfnumber(res, "contoencoding");
5747 i_conproc = PQfnumber(res, "conproc");
5748 i_condefault = PQfnumber(res, "condefault");
5750 conname = PQgetvalue(res, 0, i_conname);
5751 conforencoding = PQgetvalue(res, 0, i_conforencoding);
5752 contoencoding = PQgetvalue(res, 0, i_contoencoding);
5753 conproc = PQgetvalue(res, 0, i_conproc);
5754 condefault = (PQgetvalue(res, 0, i_condefault)[0] == 't');
5757 * DROP must be fully qualified in case same name appears in
5760 appendPQExpBuffer(delq, "DROP CONVERSION %s",
5761 fmtId(convinfo->connamespace->nspname));
5762 appendPQExpBuffer(delq, ".%s;\n",
5763 fmtId(convinfo->conname));
5765 appendPQExpBuffer(q, "CREATE %sCONVERSION %s FOR ",
5766 (condefault) ? "DEFAULT " : "",
5767 fmtId(convinfo->conname));
5768 appendStringLiteral(q, conforencoding, true);
5769 appendPQExpBuffer(q, " TO ");
5770 appendStringLiteral(q, contoencoding, true);
5771 /* regproc is automatically quoted in 7.3 and above */
5772 appendPQExpBuffer(q, " FROM %s;\n", conproc);
5774 ArchiveEntry(fout, convinfo->dobj.catId, convinfo->dobj.dumpId,
5776 convinfo->connamespace->nspname, convinfo->usename,
5777 "CONVERSION", q->data, delq->data, NULL,
5778 convinfo->dobj.dependencies, convinfo->dobj.nDeps,
5781 /* Dump Conversion Comments */
5782 resetPQExpBuffer(q);
5783 appendPQExpBuffer(q, "CONVERSION %s", fmtId(convinfo->conname));
5784 dumpComment(fout, q->data,
5785 convinfo->connamespace->nspname, convinfo->usename,
5786 convinfo->dobj.catId, 0, convinfo->dobj.dumpId);
5790 destroyPQExpBuffer(query);
5791 destroyPQExpBuffer(q);
5792 destroyPQExpBuffer(delq);
5793 destroyPQExpBuffer(details);
5797 * format_aggregate_signature: generate aggregate name and argument list
5799 * The argument type names are qualified if needed. The aggregate name
5800 * is never qualified.
5803 format_aggregate_signature(AggInfo *agginfo, Archive *fout, bool honor_quotes)
5805 PQExpBufferData buf;
5807 initPQExpBuffer(&buf);
5809 appendPQExpBuffer(&buf, "%s",
5810 fmtId(agginfo->aggfn.proname));
5812 appendPQExpBuffer(&buf, "%s", agginfo->aggfn.proname);
5814 /* If using regtype or format_type, fmtbasetype is already quoted */
5815 if (fout->remoteVersion >= 70100)
5817 if (agginfo->anybasetype)
5818 appendPQExpBuffer(&buf, "(*)");
5820 appendPQExpBuffer(&buf, "(%s)", agginfo->fmtbasetype);
5824 if (agginfo->anybasetype)
5825 appendPQExpBuffer(&buf, "(*)");
5827 appendPQExpBuffer(&buf, "(%s)",
5828 fmtId(agginfo->fmtbasetype));
5836 * write out a single aggregate definition
5839 dumpAgg(Archive *fout, AggInfo *agginfo)
5844 PQExpBuffer details;
5856 const char *aggtransfn;
5857 const char *aggfinalfn;
5858 const char *aggtranstype;
5859 const char *agginitval;
5862 /* Dump only aggs in dumpable namespaces */
5863 if (!agginfo->aggfn.pronamespace->dump || dataOnly)
5866 query = createPQExpBuffer();
5867 q = createPQExpBuffer();
5868 delq = createPQExpBuffer();
5869 details = createPQExpBuffer();
5871 /* Make sure we are in proper schema */
5872 selectSourceSchema(agginfo->aggfn.pronamespace->nspname);
5874 /* Get aggregate-specific details */
5875 if (g_fout->remoteVersion >= 70300)
5877 appendPQExpBuffer(query, "SELECT aggtransfn, "
5878 "aggfinalfn, aggtranstype::pg_catalog.regtype, "
5880 "proargtypes[0] = 'pg_catalog.\"any\"'::pg_catalog.regtype as anybasetype, "
5881 "proargtypes[0]::pg_catalog.regtype as fmtbasetype, "
5882 "'t'::boolean as convertok "
5883 "from pg_catalog.pg_aggregate a, pg_catalog.pg_proc p "
5884 "where a.aggfnoid = p.oid "
5885 "and p.oid = '%u'::pg_catalog.oid",
5886 agginfo->aggfn.dobj.catId.oid);
5888 else if (g_fout->remoteVersion >= 70100)
5890 appendPQExpBuffer(query, "SELECT aggtransfn, aggfinalfn, "
5891 "format_type(aggtranstype, NULL) as aggtranstype, "
5893 "aggbasetype = 0 as anybasetype, "
5894 "CASE WHEN aggbasetype = 0 THEN '-' "
5895 "ELSE format_type(aggbasetype, NULL) END as fmtbasetype, "
5896 "'t'::boolean as convertok "
5897 "from pg_aggregate "
5898 "where oid = '%u'::oid",
5899 agginfo->aggfn.dobj.catId.oid);
5903 appendPQExpBuffer(query, "SELECT aggtransfn1 as aggtransfn, "
5905 "(select typname from pg_type where oid = aggtranstype1) as aggtranstype, "
5906 "agginitval1 as agginitval, "
5907 "aggbasetype = 0 as anybasetype, "
5908 "(select typname from pg_type where oid = aggbasetype) as fmtbasetype, "
5909 "(aggtransfn2 = 0 and aggtranstype2 = 0 and agginitval2 is null) as convertok "
5910 "from pg_aggregate "
5911 "where oid = '%u'::oid",
5912 agginfo->aggfn.dobj.catId.oid);
5915 res = PQexec(g_conn, query->data);
5916 check_sql_result(res, g_conn, query->data, PGRES_TUPLES_OK);
5918 /* Expecting a single result only */
5919 ntups = PQntuples(res);
5922 write_msg(NULL, "Got %d rows instead of one from: %s",
5923 ntups, query->data);
5927 i_aggtransfn = PQfnumber(res, "aggtransfn");
5928 i_aggfinalfn = PQfnumber(res, "aggfinalfn");
5929 i_aggtranstype = PQfnumber(res, "aggtranstype");
5930 i_agginitval = PQfnumber(res, "agginitval");
5931 i_anybasetype = PQfnumber(res, "anybasetype");
5932 i_fmtbasetype = PQfnumber(res, "fmtbasetype");
5933 i_convertok = PQfnumber(res, "convertok");
5935 aggtransfn = PQgetvalue(res, 0, i_aggtransfn);
5936 aggfinalfn = PQgetvalue(res, 0, i_aggfinalfn);
5937 aggtranstype = PQgetvalue(res, 0, i_aggtranstype);
5938 agginitval = PQgetvalue(res, 0, i_agginitval);
5939 /* we save anybasetype for format_aggregate_signature */
5940 agginfo->anybasetype = (PQgetvalue(res, 0, i_anybasetype)[0] == 't');
5941 /* we save fmtbasetype for format_aggregate_signature */
5942 agginfo->fmtbasetype = strdup(PQgetvalue(res, 0, i_fmtbasetype));
5943 convertok = (PQgetvalue(res, 0, i_convertok)[0] == 't');
5945 aggsig = format_aggregate_signature(agginfo, fout, true);
5946 aggsig_tag = format_aggregate_signature(agginfo, fout, false);
5950 write_msg(NULL, "WARNING: aggregate function %s could not be dumped correctly for this database version; ignored\n",
5955 if (g_fout->remoteVersion >= 70300)
5957 /* If using 7.3's regproc or regtype, data is already quoted */
5958 appendPQExpBuffer(details, " BASETYPE = %s,\n SFUNC = %s,\n STYPE = %s",
5959 agginfo->anybasetype ? "'any'" :
5960 agginfo->fmtbasetype,
5964 else if (g_fout->remoteVersion >= 70100)
5966 /* format_type quotes, regproc does not */
5967 appendPQExpBuffer(details, " BASETYPE = %s,\n SFUNC = %s,\n STYPE = %s",
5968 agginfo->anybasetype ? "'any'" :
5969 agginfo->fmtbasetype,
5975 /* need quotes all around */
5976 appendPQExpBuffer(details, " BASETYPE = %s,\n",
5977 agginfo->anybasetype ? "'any'" :
5978 fmtId(agginfo->fmtbasetype));
5979 appendPQExpBuffer(details, " SFUNC = %s,\n",
5981 appendPQExpBuffer(details, " STYPE = %s",
5982 fmtId(aggtranstype));
5985 if (!PQgetisnull(res, 0, i_agginitval))
5987 appendPQExpBuffer(details, ",\n INITCOND = ");
5988 appendStringLiteral(details, agginitval, true);
5991 if (strcmp(aggfinalfn, "-") != 0)
5993 appendPQExpBuffer(details, ",\n FINALFUNC = %s",
5998 * DROP must be fully qualified in case same name appears in
6001 appendPQExpBuffer(delq, "DROP AGGREGATE %s.%s;\n",
6002 fmtId(agginfo->aggfn.pronamespace->nspname),
6005 appendPQExpBuffer(q, "CREATE AGGREGATE %s (\n%s\n);\n",
6006 fmtId(agginfo->aggfn.proname),
6009 ArchiveEntry(fout, agginfo->aggfn.dobj.catId, agginfo->aggfn.dobj.dumpId,
6011 agginfo->aggfn.pronamespace->nspname, agginfo->aggfn.usename,
6012 "AGGREGATE", q->data, delq->data, NULL,
6013 agginfo->aggfn.dobj.dependencies, agginfo->aggfn.dobj.nDeps,
6016 /* Dump Aggregate Comments */
6017 resetPQExpBuffer(q);
6018 appendPQExpBuffer(q, "AGGREGATE %s", aggsig);
6019 dumpComment(fout, q->data,
6020 agginfo->aggfn.pronamespace->nspname, agginfo->aggfn.usename,
6021 agginfo->aggfn.dobj.catId, 0, agginfo->aggfn.dobj.dumpId);
6024 * Since there is no GRANT ON AGGREGATE syntax, we have to make the
6025 * ACL command look like a function's GRANT; in particular this affects
6026 * the syntax for aggregates on ANY.
6031 aggsig = format_function_signature(&agginfo->aggfn, NULL, true);
6032 aggsig_tag = format_function_signature(&agginfo->aggfn, NULL, false);
6034 dumpACL(fout, agginfo->aggfn.dobj.catId, agginfo->aggfn.dobj.dumpId,
6037 agginfo->aggfn.pronamespace->nspname,
6038 agginfo->aggfn.usename, agginfo->aggfn.proacl);
6045 destroyPQExpBuffer(query);
6046 destroyPQExpBuffer(q);
6047 destroyPQExpBuffer(delq);
6048 destroyPQExpBuffer(details);
6053 * Write out grant/revoke information
6055 * 'objCatId' is the catalog ID of the underlying object.
6056 * 'objDumpId' is the dump ID of the underlying object.
6057 * 'type' must be TABLE, FUNCTION, LANGUAGE, or SCHEMA.
6058 * 'name' is the formatted name of the object. Must be quoted etc. already.
6059 * 'tag' is the tag for the archive entry (typ. unquoted name of object).
6060 * 'nspname' is the namespace the object is in (NULL if none).
6061 * 'owner' is the owner, NULL if there is no owner (for languages).
6062 * 'acls' is the string read out of the fooacl system catalog field;
6063 * it will be parsed here.
6067 dumpACL(Archive *fout, CatalogId objCatId, DumpId objDumpId,
6068 const char *type, const char *name,
6069 const char *tag, const char *nspname, const char *owner,
6074 /* Do nothing if ACL dump is not enabled */
6075 if (dataOnly || aclsSkip)
6078 sql = createPQExpBuffer();
6080 if (!buildACLCommands(name, type, acls, owner, fout->remoteVersion, sql))
6082 write_msg(NULL, "could not parse ACL list (%s) for object \"%s\" (%s)\n",
6088 ArchiveEntry(fout, nilCatalogId, createDumpId(),
6091 "ACL", sql->data, "", NULL,
6095 destroyPQExpBuffer(sql);
6100 * write out to fout the declarations (not data) of a user-defined table
6103 dumpTable(Archive *fout, TableInfo *tbinfo)
6109 if (tbinfo->relkind == RELKIND_SEQUENCE)
6110 dumpSequence(fout, tbinfo);
6112 dumpTableSchema(fout, tbinfo);
6114 /* Handle the ACL here */
6115 namecopy = strdup(fmtId(tbinfo->relname));
6116 dumpACL(fout, tbinfo->dobj.catId, tbinfo->dobj.dumpId, "TABLE",
6117 namecopy, tbinfo->relname,
6118 tbinfo->relnamespace->nspname, tbinfo->usename,
6126 * write the declaration (not data) of one user-defined table or view
6129 dumpTableSchema(Archive *fout, TableInfo *tbinfo)
6131 PQExpBuffer query = createPQExpBuffer();
6132 PQExpBuffer q = createPQExpBuffer();
6133 PQExpBuffer delq = createPQExpBuffer();
6136 TableInfo **parents;
6137 int actual_atts; /* number of attrs in this CREATE statment */
6143 /* Make sure we are in proper schema */
6144 selectSourceSchema(tbinfo->relnamespace->nspname);
6146 /* Is it a table or a view? */
6147 if (tbinfo->relkind == RELKIND_VIEW)
6151 reltypename = "VIEW";
6153 /* Fetch the view definition */
6154 if (g_fout->remoteVersion >= 70300)
6156 /* Beginning in 7.3, viewname is not unique; rely on OID */
6157 appendPQExpBuffer(query,
6158 "SELECT pg_catalog.pg_get_viewdef('%u'::pg_catalog.oid) as viewdef",
6159 tbinfo->dobj.catId.oid);
6163 appendPQExpBuffer(query, "SELECT definition as viewdef "
6164 " from pg_views where viewname = ");
6165 appendStringLiteral(query, tbinfo->relname, true);
6166 appendPQExpBuffer(query, ";");
6169 res = PQexec(g_conn, query->data);
6170 check_sql_result(res, g_conn, query->data, PGRES_TUPLES_OK);
6172 if (PQntuples(res) != 1)
6174 if (PQntuples(res) < 1)
6175 write_msg(NULL, "query to obtain definition of view \"%s\" returned no data\n",
6178 write_msg(NULL, "query to obtain definition of view \"%s\" returned more than one definition\n",
6183 viewdef = PQgetvalue(res, 0, 0);
6185 if (strlen(viewdef) == 0)
6187 write_msg(NULL, "definition of view \"%s\" appears to be empty (length zero)\n",
6193 * DROP must be fully qualified in case same name appears in
6196 appendPQExpBuffer(delq, "DROP VIEW %s.",
6197 fmtId(tbinfo->relnamespace->nspname));
6198 appendPQExpBuffer(delq, "%s;\n",
6199 fmtId(tbinfo->relname));
6201 appendPQExpBuffer(q, "CREATE VIEW %s AS\n %s\n",
6202 fmtId(tbinfo->relname), viewdef);
6208 reltypename = "TABLE";
6209 numParents = tbinfo->numParents;
6210 parents = tbinfo->parents;
6213 * DROP must be fully qualified in case same name appears in
6216 appendPQExpBuffer(delq, "DROP TABLE %s.",
6217 fmtId(tbinfo->relnamespace->nspname));
6218 appendPQExpBuffer(delq, "%s;\n",
6219 fmtId(tbinfo->relname));
6221 appendPQExpBuffer(q, "CREATE TABLE %s (",
6222 fmtId(tbinfo->relname));
6224 for (j = 0; j < tbinfo->numatts; j++)
6226 /* Is this one of the table's own attrs, and not dropped ? */
6227 if (!tbinfo->inhAttrs[j] && !tbinfo->attisdropped[j])
6229 /* Format properly if not first attr */
6230 if (actual_atts > 0)
6231 appendPQExpBuffer(q, ",");
6232 appendPQExpBuffer(q, "\n ");
6234 /* Attribute name */
6235 appendPQExpBuffer(q, "%s ",
6236 fmtId(tbinfo->attnames[j]));
6238 /* Attribute type */
6239 if (g_fout->remoteVersion >= 70100)
6241 char *typname = tbinfo->atttypnames[j];
6243 if (tbinfo->attisserial[j])
6245 if (strcmp(typname, "integer") == 0)
6247 else if (strcmp(typname, "bigint") == 0)
6248 typname = "bigserial";
6250 appendPQExpBuffer(q, "%s", typname);
6254 /* If no format_type, fake it */
6255 appendPQExpBuffer(q, "%s",
6256 myFormatType(tbinfo->atttypnames[j],
6257 tbinfo->atttypmod[j]));
6261 * Default value --- suppress if inherited, serial,
6262 * or to be printed separately.
6264 if (tbinfo->attrdefs[j] != NULL &&
6265 !tbinfo->inhAttrDef[j] &&
6266 !tbinfo->attisserial[j] &&
6267 !tbinfo->attrdefs[j]->separate)
6268 appendPQExpBuffer(q, " DEFAULT %s",
6269 tbinfo->attrdefs[j]->adef_expr);
6272 * Not Null constraint --- suppress if inherited
6274 * Note: we could suppress this for serial columns since
6275 * SERIAL implies NOT NULL. We choose not to for forward
6276 * compatibility, since there has been some talk of making
6277 * SERIAL not imply NOT NULL, in which case the explicit
6278 * specification would be needed.
6280 if (tbinfo->notnull[j] && !tbinfo->inhNotNull[j])
6281 appendPQExpBuffer(q, " NOT NULL");
6288 * Add non-inherited CHECK constraints, if any.
6290 for (j = 0; j < tbinfo->ncheck; j++)
6292 ConstraintInfo *constr = &(tbinfo->checkexprs[j]);
6294 if (constr->coninherited || constr->separate)
6297 if (actual_atts > 0)
6298 appendPQExpBuffer(q, ",\n ");
6300 appendPQExpBuffer(q, "CONSTRAINT %s ",
6301 fmtId(constr->conname));
6302 appendPQExpBuffer(q, "%s", constr->condef);
6307 appendPQExpBuffer(q, "\n)");
6311 appendPQExpBuffer(q, "\nINHERITS (");
6312 for (k = 0; k < numParents; k++)
6314 TableInfo *parentRel = parents[k];
6317 appendPQExpBuffer(q, ", ");
6318 if (parentRel->relnamespace != tbinfo->relnamespace)
6319 appendPQExpBuffer(q, "%s.",
6320 fmtId(parentRel->relnamespace->nspname));
6321 appendPQExpBuffer(q, "%s",
6322 fmtId(parentRel->relname));
6324 appendPQExpBuffer(q, ")");
6327 appendPQExpBuffer(q, tbinfo->hasoids ? " WITH OIDS" : " WITHOUT OIDS");
6329 appendPQExpBuffer(q, ";\n");
6331 /* Loop dumping statistics and storage statements */
6332 for (j = 0; j < tbinfo->numatts; j++)
6335 * Dump per-column statistics information. We only issue an
6336 * ALTER TABLE statement if the attstattarget entry for this
6337 * column is non-negative (i.e. it's not the default value)
6339 if (tbinfo->attstattarget[j] >= 0 &&
6340 !tbinfo->attisdropped[j])
6342 appendPQExpBuffer(q, "ALTER TABLE ONLY %s ",
6343 fmtId(tbinfo->relname));
6344 appendPQExpBuffer(q, "ALTER COLUMN %s ",
6345 fmtId(tbinfo->attnames[j]));
6346 appendPQExpBuffer(q, "SET STATISTICS %d;\n",
6347 tbinfo->attstattarget[j]);
6351 * Dump per-column storage information. The statement is only
6352 * dumped if the storage has been changed from the type's
6355 if (!tbinfo->attisdropped[j] && tbinfo->attstorage[j] != tbinfo->typstorage[j])
6357 switch (tbinfo->attstorage[j])
6363 storage = "EXTERNAL";
6369 storage = "EXTENDED";
6376 * Only dump the statement if it's a storage type we
6379 if (storage != NULL)
6381 appendPQExpBuffer(q, "ALTER TABLE ONLY %s ",
6382 fmtId(tbinfo->relname));
6383 appendPQExpBuffer(q, "ALTER COLUMN %s ",
6384 fmtId(tbinfo->attnames[j]));
6385 appendPQExpBuffer(q, "SET STORAGE %s;\n",
6392 ArchiveEntry(fout, tbinfo->dobj.catId, tbinfo->dobj.dumpId,
6394 tbinfo->relnamespace->nspname, tbinfo->usename,
6395 reltypename, q->data, delq->data, NULL,
6396 tbinfo->dobj.dependencies, tbinfo->dobj.nDeps,
6399 /* Dump Table Comments */
6400 dumpTableComment(fout, tbinfo, reltypename);
6402 destroyPQExpBuffer(query);
6403 destroyPQExpBuffer(q);
6404 destroyPQExpBuffer(delq);
6408 * dumpAttrDef --- dump an attribute's default-value declaration
6411 dumpAttrDef(Archive *fout, AttrDefInfo *adinfo)
6413 TableInfo *tbinfo = adinfo->adtable;
6414 int adnum = adinfo->adnum;
6418 /* Only print it if "separate" mode is selected */
6419 if (!tbinfo->dump || !adinfo->separate || dataOnly)
6422 /* Don't print inherited or serial defaults, either */
6423 if (tbinfo->inhAttrDef[adnum-1] || tbinfo->attisserial[adnum-1])
6426 q = createPQExpBuffer();
6427 delq = createPQExpBuffer();
6429 appendPQExpBuffer(q, "ALTER TABLE %s ",
6430 fmtId(tbinfo->relname));
6431 appendPQExpBuffer(q, "ALTER COLUMN %s SET DEFAULT %s;\n",
6432 fmtId(tbinfo->attnames[adnum - 1]),
6436 * DROP must be fully qualified in case same name appears
6439 appendPQExpBuffer(delq, "ALTER TABLE %s.",
6440 fmtId(tbinfo->relnamespace->nspname));
6441 appendPQExpBuffer(delq, "%s ",
6442 fmtId(tbinfo->relname));
6443 appendPQExpBuffer(delq, "ALTER COLUMN %s DROP DEFAULT;\n",
6444 fmtId(tbinfo->attnames[adnum - 1]));
6446 ArchiveEntry(fout, adinfo->dobj.catId, adinfo->dobj.dumpId,
6447 tbinfo->attnames[adnum - 1],
6448 tbinfo->relnamespace->nspname, tbinfo->usename,
6449 "DEFAULT", q->data, delq->data, NULL,
6450 adinfo->dobj.dependencies, adinfo->dobj.nDeps,
6453 destroyPQExpBuffer(q);
6454 destroyPQExpBuffer(delq);
6458 * getAttrName: extract the correct name for an attribute
6460 * The array tblInfo->attnames[] only provides names of user attributes;
6461 * if a system attribute number is supplied, we have to fake it.
6462 * We also do a little bit of bounds checking for safety's sake.
6465 getAttrName(int attrnum, TableInfo *tblInfo)
6467 if (attrnum > 0 && attrnum <= tblInfo->numatts)
6468 return tblInfo->attnames[attrnum - 1];
6471 case SelfItemPointerAttributeNumber:
6473 case ObjectIdAttributeNumber:
6475 case MinTransactionIdAttributeNumber:
6477 case MinCommandIdAttributeNumber:
6479 case MaxTransactionIdAttributeNumber:
6481 case MaxCommandIdAttributeNumber:
6483 case TableOidAttributeNumber:
6486 write_msg(NULL, "invalid column number %d for table \"%s\"\n",
6487 attrnum, tblInfo->relname);
6489 return NULL; /* keep compiler quiet */
6494 * write out to fout a user-defined index
6497 dumpIndex(Archive *fout, IndxInfo *indxinfo)
6499 TableInfo *tbinfo = indxinfo->indextable;
6506 q = createPQExpBuffer();
6507 delq = createPQExpBuffer();
6510 * If there's an associated constraint, don't dump the index per se,
6511 * but do dump any comment for it.
6513 if (indxinfo->indexconstraint == 0)
6515 /* Plain secondary index */
6516 appendPQExpBuffer(q, "%s;\n", indxinfo->indexdef);
6518 /* If the index is clustered, we need to record that. */
6519 if (indxinfo->indisclustered)
6521 appendPQExpBuffer(q, "\nALTER TABLE %s CLUSTER",
6522 fmtId(tbinfo->relname));
6523 appendPQExpBuffer(q, " ON %s;\n",
6524 fmtId(indxinfo->indexname));
6528 * DROP must be fully qualified in case same name appears
6531 appendPQExpBuffer(delq, "DROP INDEX %s.",
6532 fmtId(tbinfo->relnamespace->nspname));
6533 appendPQExpBuffer(delq, "%s;\n",
6534 fmtId(indxinfo->indexname));
6536 ArchiveEntry(fout, indxinfo->dobj.catId, indxinfo->dobj.dumpId,
6537 indxinfo->indexname,
6538 tbinfo->relnamespace->nspname,
6540 "INDEX", q->data, delq->data, NULL,
6541 indxinfo->dobj.dependencies, indxinfo->dobj.nDeps,
6545 /* Dump Index Comments */
6546 resetPQExpBuffer(q);
6547 appendPQExpBuffer(q, "INDEX %s",
6548 fmtId(indxinfo->indexname));
6549 dumpComment(fout, q->data,
6550 tbinfo->relnamespace->nspname,
6552 indxinfo->dobj.catId, 0, indxinfo->dobj.dumpId);
6554 destroyPQExpBuffer(q);
6555 destroyPQExpBuffer(delq);
6560 * write out to fout a user-defined constraint
6563 dumpConstraint(Archive *fout, ConstraintInfo *coninfo)
6565 TableInfo *tbinfo = coninfo->contable;
6571 if (tbinfo && !tbinfo->dump)
6574 q = createPQExpBuffer();
6575 delq = createPQExpBuffer();
6577 if (coninfo->contype == 'p' || coninfo->contype == 'u')
6579 /* Index-related constraint */
6583 indxinfo = (IndxInfo *) findObjectByDumpId(coninfo->conindex);
6585 if (indxinfo == NULL)
6587 write_msg(NULL, "missing index for constraint %s\n",
6592 appendPQExpBuffer(q, "ALTER TABLE ONLY %s\n",
6593 fmtId(tbinfo->relname));
6594 appendPQExpBuffer(q, " ADD CONSTRAINT %s %s (",
6595 fmtId(coninfo->conname),
6596 coninfo->contype == 'p' ? "PRIMARY KEY" : "UNIQUE");
6598 for (k = 0; k < indxinfo->indnkeys; k++)
6600 int indkey = (int) indxinfo->indkeys[k];
6601 const char *attname;
6603 if (indkey == InvalidAttrNumber)
6605 attname = getAttrName(indkey, tbinfo);
6607 appendPQExpBuffer(q, "%s%s",
6608 (k == 0) ? "" : ", ",
6612 appendPQExpBuffer(q, ");\n");
6614 /* If the index is clustered, we need to record that. */
6615 if (indxinfo->indisclustered)
6617 appendPQExpBuffer(q, "\nALTER TABLE %s CLUSTER",
6618 fmtId(tbinfo->relname));
6619 appendPQExpBuffer(q, " ON %s;\n",
6620 fmtId(indxinfo->indexname));
6624 * DROP must be fully qualified in case same name appears
6627 appendPQExpBuffer(delq, "ALTER TABLE ONLY %s.",
6628 fmtId(tbinfo->relnamespace->nspname));
6629 appendPQExpBuffer(delq, "%s ",
6630 fmtId(tbinfo->relname));
6631 appendPQExpBuffer(delq, "DROP CONSTRAINT %s;\n",
6632 fmtId(coninfo->conname));
6634 ArchiveEntry(fout, coninfo->dobj.catId, coninfo->dobj.dumpId,
6636 tbinfo->relnamespace->nspname,
6638 "CONSTRAINT", q->data, delq->data, NULL,
6639 coninfo->dobj.dependencies, coninfo->dobj.nDeps,
6642 else if (coninfo->contype == 'f')
6645 * XXX Potentially wrap in a 'SET CONSTRAINTS OFF' block so that
6646 * the current table data is not processed
6648 appendPQExpBuffer(q, "ALTER TABLE ONLY %s\n",
6649 fmtId(tbinfo->relname));
6650 appendPQExpBuffer(q, " ADD CONSTRAINT %s %s;\n",
6651 fmtId(coninfo->conname),
6655 * DROP must be fully qualified in case same name appears in
6658 appendPQExpBuffer(delq, "ALTER TABLE ONLY %s.",
6659 fmtId(tbinfo->relnamespace->nspname));
6660 appendPQExpBuffer(delq, "%s ",
6661 fmtId(tbinfo->relname));
6662 appendPQExpBuffer(delq, "DROP CONSTRAINT %s;\n",
6663 fmtId(coninfo->conname));
6665 ArchiveEntry(fout, coninfo->dobj.catId, coninfo->dobj.dumpId,
6667 tbinfo->relnamespace->nspname,
6669 "FK CONSTRAINT", q->data, delq->data, NULL,
6670 coninfo->dobj.dependencies, coninfo->dobj.nDeps,
6673 else if (coninfo->contype == 'c' && tbinfo)
6675 /* CHECK constraint on a table */
6677 /* Ignore if not to be dumped separately */
6678 if (coninfo->separate)
6680 /* not ONLY since we want it to propagate to children */
6681 appendPQExpBuffer(q, "ALTER TABLE %s\n",
6682 fmtId(tbinfo->relname));
6683 appendPQExpBuffer(q, " ADD CONSTRAINT %s %s;\n",
6684 fmtId(coninfo->conname),
6688 * DROP must be fully qualified in case same name appears in
6691 appendPQExpBuffer(delq, "ALTER TABLE %s.",
6692 fmtId(tbinfo->relnamespace->nspname));
6693 appendPQExpBuffer(delq, "%s ",
6694 fmtId(tbinfo->relname));
6695 appendPQExpBuffer(delq, "DROP CONSTRAINT %s;\n",
6696 fmtId(coninfo->conname));
6698 ArchiveEntry(fout, coninfo->dobj.catId, coninfo->dobj.dumpId,
6700 tbinfo->relnamespace->nspname,
6702 "CHECK CONSTRAINT", q->data, delq->data, NULL,
6703 coninfo->dobj.dependencies, coninfo->dobj.nDeps,
6707 else if (coninfo->contype == 'c' && tbinfo == NULL)
6709 /* CHECK constraint on a domain */
6710 TypeInfo *tinfo = coninfo->condomain;
6712 /* Ignore if not to be dumped separately, or if not dumping domain */
6713 if (coninfo->separate && tinfo->typnamespace->dump)
6715 appendPQExpBuffer(q, "ALTER DOMAIN %s\n",
6716 fmtId(tinfo->typname));
6717 appendPQExpBuffer(q, " ADD CONSTRAINT %s %s;\n",
6718 fmtId(coninfo->conname),
6722 * DROP must be fully qualified in case same name appears in
6725 appendPQExpBuffer(delq, "ALTER DOMAIN %s.",
6726 fmtId(tinfo->typnamespace->nspname));
6727 appendPQExpBuffer(delq, "%s ",
6728 fmtId(tinfo->typname));
6729 appendPQExpBuffer(delq, "DROP CONSTRAINT %s;\n",
6730 fmtId(coninfo->conname));
6732 ArchiveEntry(fout, coninfo->dobj.catId, coninfo->dobj.dumpId,
6734 tinfo->typnamespace->nspname,
6736 "CHECK CONSTRAINT", q->data, delq->data, NULL,
6737 coninfo->dobj.dependencies, coninfo->dobj.nDeps,
6743 write_msg(NULL, "unexpected constraint type\n");
6747 /* Dump Constraint Comments --- only works for table constraints */
6750 resetPQExpBuffer(q);
6751 appendPQExpBuffer(q, "CONSTRAINT %s ",
6752 fmtId(coninfo->conname));
6753 appendPQExpBuffer(q, "ON %s",
6754 fmtId(tbinfo->relname));
6755 dumpComment(fout, q->data,
6756 tbinfo->relnamespace->nspname,
6758 coninfo->dobj.catId, 0, coninfo->dobj.dumpId);
6761 destroyPQExpBuffer(q);
6762 destroyPQExpBuffer(delq);
6767 * find the maximum oid and generate a COPY statement to set it
6771 setMaxOid(Archive *fout)
6777 do_sql_command(g_conn,
6778 "CREATE TEMPORARY TABLE pgdump_oid (dummy integer)");
6779 res = PQexec(g_conn, "INSERT INTO pgdump_oid VALUES (0)");
6780 check_sql_result(res, g_conn, "INSERT INTO pgdump_oid VALUES (0)",
6782 max_oid = PQoidValue(res);
6785 write_msg(NULL, "inserted invalid OID\n");
6789 do_sql_command(g_conn, "DROP TABLE pgdump_oid;");
6791 write_msg(NULL, "maximum system OID is %u\n", max_oid);
6792 snprintf(sql, sizeof(sql),
6793 "CREATE TEMPORARY TABLE pgdump_oid (dummy integer);\n"
6794 "COPY pgdump_oid WITH OIDS FROM stdin;\n"
6797 "DROP TABLE pgdump_oid;\n",
6800 ArchiveEntry(fout, nilCatalogId, createDumpId(),
6801 "Max OID", NULL, "",
6802 "<Init>", sql, "", NULL,
6808 * findLastBuiltInOid -
6809 * find the last built in oid
6811 * For 7.1 and 7.2, we do this by retrieving datlastsysoid from the
6812 * pg_database entry for the current database
6815 findLastBuiltinOid_V71(const char *dbname)
6820 PQExpBuffer query = createPQExpBuffer();
6822 resetPQExpBuffer(query);
6823 appendPQExpBuffer(query, "SELECT datlastsysoid from pg_database where datname = ");
6824 appendStringLiteral(query, dbname, true);
6826 res = PQexec(g_conn, query->data);
6827 check_sql_result(res, g_conn, query->data, PGRES_TUPLES_OK);
6829 ntups = PQntuples(res);
6832 write_msg(NULL, "missing pg_database entry for this database\n");
6837 write_msg(NULL, "found more than one pg_database entry for this database\n");
6840 last_oid = atooid(PQgetvalue(res, 0, PQfnumber(res, "datlastsysoid")));
6842 destroyPQExpBuffer(query);
6847 * findLastBuiltInOid -
6848 * find the last built in oid
6850 * For 7.0, we do this by assuming that the last thing that initdb does is to
6851 * create the pg_indexes view. This sucks in general, but seeing that 7.0.x
6852 * initdb won't be changing anymore, it'll do.
6855 findLastBuiltinOid_V70(void)
6861 res = PQexec(g_conn,
6862 "SELECT oid FROM pg_class WHERE relname = 'pg_indexes'");
6863 check_sql_result(res, g_conn,
6864 "SELECT oid FROM pg_class WHERE relname = 'pg_indexes'",
6866 ntups = PQntuples(res);
6869 write_msg(NULL, "could not find entry for pg_indexes in pg_class\n");
6874 write_msg(NULL, "found more than one entry for pg_indexes in pg_class\n");
6877 last_oid = atooid(PQgetvalue(res, 0, PQfnumber(res, "oid")));
6883 dumpSequence(Archive *fout, TableInfo *tbinfo)
6895 PQExpBuffer query = createPQExpBuffer();
6896 PQExpBuffer delqry = createPQExpBuffer();
6898 /* Make sure we are in proper schema */
6899 selectSourceSchema(tbinfo->relnamespace->nspname);
6901 snprintf(bufm, sizeof(bufm), INT64_FORMAT, SEQ_MINVALUE);
6902 snprintf(bufx, sizeof(bufx), INT64_FORMAT, SEQ_MAXVALUE);
6904 appendPQExpBuffer(query,
6905 "SELECT sequence_name, last_value, increment_by, "
6906 "CASE WHEN increment_by > 0 AND max_value = %s THEN NULL "
6907 " WHEN increment_by < 0 AND max_value = -1 THEN NULL "
6909 "END AS max_value, "
6910 "CASE WHEN increment_by > 0 AND min_value = 1 THEN NULL "
6911 " WHEN increment_by < 0 AND min_value = %s THEN NULL "
6913 "END AS min_value, "
6914 "cache_value, is_cycled, is_called from %s",
6916 fmtId(tbinfo->relname));
6918 res = PQexec(g_conn, query->data);
6919 check_sql_result(res, g_conn, query->data, PGRES_TUPLES_OK);
6921 if (PQntuples(res) != 1)
6923 write_msg(NULL, "query to get data of sequence \"%s\" returned %d rows (expected 1)\n",
6924 tbinfo->relname, PQntuples(res));
6928 /* Disable this check: it fails if sequence has been renamed */
6930 if (strcmp(PQgetvalue(res, 0, 0), tbinfo->relname) != 0)
6932 write_msg(NULL, "query to get data of sequence \"%s\" returned name \"%s\"\n",
6933 tbinfo->relname, PQgetvalue(res, 0, 0));
6938 last = PQgetvalue(res, 0, 1);
6939 incby = PQgetvalue(res, 0, 2);
6940 if (!PQgetisnull(res, 0, 3))
6941 maxv = PQgetvalue(res, 0, 3);
6942 if (!PQgetisnull(res, 0, 4))
6943 minv = PQgetvalue(res, 0, 4);
6944 cache = PQgetvalue(res, 0, 5);
6945 cycled = (strcmp(PQgetvalue(res, 0, 6), "t") == 0);
6946 called = (strcmp(PQgetvalue(res, 0, 7), "t") == 0);
6949 * The logic we use for restoring sequences is as follows:
6951 * Add a basic CREATE SEQUENCE statement (use last_val for start if
6952 * called is false, else use min_val for start_val). Skip this if the
6953 * sequence came from a SERIAL column.
6955 * Add a 'SETVAL(seq, last_val, iscalled)' at restore-time iff we load
6956 * data. We do this for serial sequences too.
6959 if (!dataOnly && !OidIsValid(tbinfo->owning_tab))
6961 resetPQExpBuffer(delqry);
6964 * DROP must be fully qualified in case same name appears in
6967 appendPQExpBuffer(delqry, "DROP SEQUENCE %s.",
6968 fmtId(tbinfo->relnamespace->nspname));
6969 appendPQExpBuffer(delqry, "%s;\n",
6970 fmtId(tbinfo->relname));
6972 resetPQExpBuffer(query);
6973 appendPQExpBuffer(query,
6974 "CREATE SEQUENCE %s\n",
6975 fmtId(tbinfo->relname));
6978 appendPQExpBuffer(query, " START WITH %s\n", last);
6980 appendPQExpBuffer(query, " INCREMENT BY %s\n", incby);
6983 appendPQExpBuffer(query, " MAXVALUE %s\n", maxv);
6985 appendPQExpBuffer(query, " NO MAXVALUE\n");
6988 appendPQExpBuffer(query, " MINVALUE %s\n", minv);
6990 appendPQExpBuffer(query, " NO MINVALUE\n");
6992 appendPQExpBuffer(query,
6994 cache, (cycled ? "\n CYCLE" : ""));
6996 ArchiveEntry(fout, tbinfo->dobj.catId, tbinfo->dobj.dumpId,
6998 tbinfo->relnamespace->nspname, tbinfo->usename,
6999 "SEQUENCE", query->data, delqry->data, NULL,
7000 tbinfo->dobj.dependencies, tbinfo->dobj.nDeps,
7006 resetPQExpBuffer(query);
7007 appendPQExpBuffer(query, "SELECT pg_catalog.setval(");
7008 appendStringLiteral(query, fmtId(tbinfo->relname), true);
7009 appendPQExpBuffer(query, ", %s, %s);\n",
7010 last, (called ? "true" : "false"));
7012 ArchiveEntry(fout, nilCatalogId, createDumpId(),
7014 tbinfo->relnamespace->nspname, tbinfo->usename,
7015 "SEQUENCE SET", query->data, "", NULL,
7016 &(tbinfo->dobj.dumpId), 1,
7022 /* Dump Sequence Comments */
7023 resetPQExpBuffer(query);
7024 appendPQExpBuffer(query, "SEQUENCE %s", fmtId(tbinfo->relname));
7025 dumpComment(fout, query->data,
7026 tbinfo->relnamespace->nspname, tbinfo->usename,
7027 tbinfo->dobj.catId, 0, tbinfo->dobj.dumpId);
7032 destroyPQExpBuffer(query);
7033 destroyPQExpBuffer(delqry);
7037 dumpTrigger(Archive *fout, TriggerInfo *tginfo)
7039 TableInfo *tbinfo = tginfo->tgtable;
7048 query = createPQExpBuffer();
7049 delqry = createPQExpBuffer();
7052 * DROP must be fully qualified in case same name appears in
7055 appendPQExpBuffer(delqry, "DROP TRIGGER %s ",
7056 fmtId(tginfo->tgname));
7057 appendPQExpBuffer(delqry, "ON %s.",
7058 fmtId(tbinfo->relnamespace->nspname));
7059 appendPQExpBuffer(delqry, "%s;\n",
7060 fmtId(tbinfo->relname));
7062 if (tginfo->tgisconstraint)
7064 appendPQExpBuffer(query, "CREATE CONSTRAINT TRIGGER ");
7065 appendPQExpBuffer(query, fmtId(tginfo->tgconstrname));
7069 appendPQExpBuffer(query, "CREATE TRIGGER ");
7070 appendPQExpBuffer(query, fmtId(tginfo->tgname));
7072 appendPQExpBuffer(query, "\n ");
7076 if (TRIGGER_FOR_BEFORE(tginfo->tgtype))
7077 appendPQExpBuffer(query, "BEFORE");
7079 appendPQExpBuffer(query, "AFTER");
7080 if (TRIGGER_FOR_INSERT(tginfo->tgtype))
7082 appendPQExpBuffer(query, " INSERT");
7085 if (TRIGGER_FOR_DELETE(tginfo->tgtype))
7088 appendPQExpBuffer(query, " OR DELETE");
7090 appendPQExpBuffer(query, " DELETE");
7093 if (TRIGGER_FOR_UPDATE(tginfo->tgtype))
7096 appendPQExpBuffer(query, " OR UPDATE");
7098 appendPQExpBuffer(query, " UPDATE");
7100 appendPQExpBuffer(query, " ON %s\n",
7101 fmtId(tbinfo->relname));
7103 if (tginfo->tgisconstraint)
7105 if (OidIsValid(tginfo->tgconstrrelid))
7107 /* If we are using regclass, name is already quoted */
7108 if (g_fout->remoteVersion >= 70300)
7109 appendPQExpBuffer(query, " FROM %s\n ",
7110 tginfo->tgconstrrelname);
7112 appendPQExpBuffer(query, " FROM %s\n ",
7113 fmtId(tginfo->tgconstrrelname));
7115 if (!tginfo->tgdeferrable)
7116 appendPQExpBuffer(query, "NOT ");
7117 appendPQExpBuffer(query, "DEFERRABLE INITIALLY ");
7118 if (tginfo->tginitdeferred)
7119 appendPQExpBuffer(query, "DEFERRED\n");
7121 appendPQExpBuffer(query, "IMMEDIATE\n");
7124 if (TRIGGER_FOR_ROW(tginfo->tgtype))
7125 appendPQExpBuffer(query, " FOR EACH ROW\n ");
7127 appendPQExpBuffer(query, " FOR EACH STATEMENT\n ");
7129 /* In 7.3, result of regproc is already quoted */
7130 if (g_fout->remoteVersion >= 70300)
7131 appendPQExpBuffer(query, "EXECUTE PROCEDURE %s(",
7134 appendPQExpBuffer(query, "EXECUTE PROCEDURE %s(",
7135 fmtId(tginfo->tgfname));
7138 for (findx = 0; findx < tginfo->tgnargs; findx++)
7144 p = strchr(p, '\\');
7147 write_msg(NULL, "invalid argument string (%s) for trigger \"%s\" on table \"%s\"\n",
7159 if (p[0] == '0' && p[1] == '0' && p[2] == '0')
7163 appendPQExpBufferChar(query, '\'');
7167 appendPQExpBufferChar(query, '\\');
7168 appendPQExpBufferChar(query, *s++);
7170 appendPQExpBufferChar(query, '\'');
7171 appendPQExpBuffer(query,
7172 (findx < tginfo->tgnargs - 1) ? ", " : "");
7175 appendPQExpBuffer(query, ");\n");
7177 ArchiveEntry(fout, tginfo->dobj.catId, tginfo->dobj.dumpId,
7179 tbinfo->relnamespace->nspname,
7181 "TRIGGER", query->data, delqry->data, NULL,
7182 tginfo->dobj.dependencies, tginfo->dobj.nDeps,
7185 resetPQExpBuffer(query);
7186 appendPQExpBuffer(query, "TRIGGER %s ",
7187 fmtId(tginfo->tgname));
7188 appendPQExpBuffer(query, "ON %s",
7189 fmtId(tbinfo->relname));
7191 dumpComment(fout, query->data,
7192 tbinfo->relnamespace->nspname, tbinfo->usename,
7193 tginfo->dobj.catId, 0, tginfo->dobj.dumpId);
7195 destroyPQExpBuffer(query);
7196 destroyPQExpBuffer(delqry);
7204 dumpRule(Archive *fout, RuleInfo *rinfo)
7206 TableInfo *tbinfo = rinfo->ruletable;
7213 * Ignore rules for not-to-be-dumped tables
7215 if (tbinfo == NULL || !tbinfo->dump || dataOnly)
7219 * If it is an ON SELECT rule, we do not need to dump it because
7220 * it will be handled via CREATE VIEW for the table.
7222 if (rinfo->ev_type == '1' && rinfo->is_instead)
7226 * Make sure we are in proper schema.
7228 selectSourceSchema(tbinfo->relnamespace->nspname);
7230 query = createPQExpBuffer();
7231 cmd = createPQExpBuffer();
7232 delcmd = createPQExpBuffer();
7234 if (g_fout->remoteVersion >= 70300)
7236 appendPQExpBuffer(query,
7237 "SELECT pg_catalog.pg_get_ruledef('%u'::pg_catalog.oid) AS definition",
7238 rinfo->dobj.catId.oid);
7242 /* Rule name was unique before 7.3 ... */
7243 appendPQExpBuffer(query,
7244 "SELECT pg_get_ruledef('%s') AS definition",
7248 res = PQexec(g_conn, query->data);
7249 check_sql_result(res, g_conn, query->data, PGRES_TUPLES_OK);
7251 if (PQntuples(res) != 1)
7253 write_msg(NULL, "query to get rule \"%s\" for table \"%s\" failed: wrong number of rows returned",
7254 rinfo->rulename, tbinfo->relname);
7258 printfPQExpBuffer(cmd, "%s\n", PQgetvalue(res, 0, 0));
7261 * DROP must be fully qualified in case same name appears in
7264 appendPQExpBuffer(delcmd, "DROP RULE %s ",
7265 fmtId(rinfo->rulename));
7266 appendPQExpBuffer(delcmd, "ON %s.",
7267 fmtId(tbinfo->relnamespace->nspname));
7268 appendPQExpBuffer(delcmd, "%s;\n",
7269 fmtId(tbinfo->relname));
7271 ArchiveEntry(fout, rinfo->dobj.catId, rinfo->dobj.dumpId,
7273 tbinfo->relnamespace->nspname,
7275 "RULE", cmd->data, delcmd->data, NULL,
7276 rinfo->dobj.dependencies, rinfo->dobj.nDeps,
7279 /* Dump rule comments */
7280 resetPQExpBuffer(query);
7281 appendPQExpBuffer(query, "RULE %s",
7282 fmtId(rinfo->rulename));
7283 appendPQExpBuffer(query, " ON %s",
7284 fmtId(tbinfo->relname));
7285 dumpComment(fout, query->data,
7286 tbinfo->relnamespace->nspname,
7288 rinfo->dobj.catId, 0, rinfo->dobj.dumpId);
7292 destroyPQExpBuffer(query);
7293 destroyPQExpBuffer(cmd);
7294 destroyPQExpBuffer(delcmd);
7298 * getDependencies --- obtain available dependency data
7301 getDependencies(void)
7312 DumpableObject *dobj,
7315 /* No dependency info available before 7.3 */
7316 if (g_fout->remoteVersion < 70300)
7320 write_msg(NULL, "fetching dependency data\n");
7322 /* Make sure we are in proper schema */
7323 selectSourceSchema("pg_catalog");
7325 query = createPQExpBuffer();
7327 appendPQExpBuffer(query, "SELECT "
7328 "classid, objid, refclassid, refobjid, deptype "
7330 "WHERE deptype != 'p' "
7333 res = PQexec(g_conn, query->data);
7334 check_sql_result(res, g_conn, query->data, PGRES_TUPLES_OK);
7336 ntups = PQntuples(res);
7338 i_classid = PQfnumber(res, "classid");
7339 i_objid = PQfnumber(res, "objid");
7340 i_refclassid = PQfnumber(res, "refclassid");
7341 i_refobjid = PQfnumber(res, "refobjid");
7342 i_deptype = PQfnumber(res, "deptype");
7345 * Since we ordered the SELECT by referencing ID, we can expect that
7346 * multiple entries for the same object will appear together; this
7347 * saves on searches.
7351 for (i = 0; i < ntups; i++)
7357 objId.tableoid = atooid(PQgetvalue(res, i, i_classid));
7358 objId.oid = atooid(PQgetvalue(res, i, i_objid));
7359 refobjId.tableoid = atooid(PQgetvalue(res, i, i_refclassid));
7360 refobjId.oid = atooid(PQgetvalue(res, i, i_refobjid));
7361 deptype = *(PQgetvalue(res, i, i_deptype));
7364 dobj->catId.tableoid != objId.tableoid ||
7365 dobj->catId.oid != objId.oid)
7366 dobj = findObjectByCatalogId(objId);
7369 * Failure to find objects mentioned in pg_depend is not unexpected,
7370 * since for example we don't collect info about TOAST tables.
7375 fprintf(stderr, "no referencing object %u %u\n",
7376 objId.tableoid, objId.oid);
7381 refdobj = findObjectByCatalogId(refobjId);
7383 if (refdobj == NULL)
7386 fprintf(stderr, "no referenced object %u %u\n",
7387 refobjId.tableoid, refobjId.oid);
7392 addObjectDependency(dobj, refdobj->dumpId);
7397 destroyPQExpBuffer(query);
7402 * selectSourceSchema - make the specified schema the active search path
7403 * in the source database.
7405 * NB: pg_catalog is explicitly searched after the specified schema;
7406 * so user names are only qualified if they are cross-schema references,
7407 * and system names are only qualified if they conflict with a user name
7408 * in the current schema.
7410 * Whenever the selected schema is not pg_catalog, be careful to qualify
7411 * references to system catalogs and types in our emitted commands!
7414 selectSourceSchema(const char *schemaName)
7416 static char *curSchemaName = NULL;
7419 /* Not relevant if fetching from pre-7.3 DB */
7420 if (g_fout->remoteVersion < 70300)
7422 /* Ignore null schema names */
7423 if (schemaName == NULL || *schemaName == '\0')
7425 /* Optimize away repeated selection of same schema */
7426 if (curSchemaName && strcmp(curSchemaName, schemaName) == 0)
7429 query = createPQExpBuffer();
7430 appendPQExpBuffer(query, "SET search_path = %s",
7432 if (strcmp(schemaName, "pg_catalog") != 0)
7433 appendPQExpBuffer(query, ", pg_catalog");
7435 do_sql_command(g_conn, query->data);
7437 destroyPQExpBuffer(query);
7439 free(curSchemaName);
7440 curSchemaName = strdup(schemaName);
7444 * getFormattedTypeName - retrieve a nicely-formatted type name for the
7447 * NB: in 7.3 and up the result may depend on the currently-selected
7448 * schema; this is why we don't try to cache the names.
7451 getFormattedTypeName(Oid oid, OidOptions opts)
7460 if ((opts & zeroAsOpaque) != 0)
7461 return strdup(g_opaque_type);
7462 else if ((opts & zeroAsAny) != 0)
7463 return strdup("'any'");
7464 else if ((opts & zeroAsStar) != 0)
7466 else if ((opts & zeroAsNone) != 0)
7467 return strdup("NONE");
7470 query = createPQExpBuffer();
7471 if (g_fout->remoteVersion >= 70300)
7473 appendPQExpBuffer(query, "SELECT pg_catalog.format_type('%u'::pg_catalog.oid, NULL)",
7476 else if (g_fout->remoteVersion >= 70100)
7478 appendPQExpBuffer(query, "SELECT format_type('%u'::oid, NULL)",
7483 appendPQExpBuffer(query, "SELECT typname "
7485 "WHERE oid = '%u'::oid",
7489 res = PQexec(g_conn, query->data);
7490 check_sql_result(res, g_conn, query->data, PGRES_TUPLES_OK);
7492 /* Expecting a single result only */
7493 ntups = PQntuples(res);
7496 write_msg(NULL, "query yielded %d rows instead of one: %s\n",
7497 ntups, query->data);
7501 if (g_fout->remoteVersion >= 70100)
7503 /* already quoted */
7504 result = strdup(PQgetvalue(res, 0, 0));
7508 /* may need to quote it */
7509 result = strdup(fmtId(PQgetvalue(res, 0, 0)));
7513 destroyPQExpBuffer(query);
7519 * myFormatType --- local implementation of format_type for use with 7.0.
7522 myFormatType(const char *typname, int32 typmod)
7525 PQExpBuffer buf = createPQExpBuffer();
7527 /* Show lengths on bpchar and varchar */
7528 if (!strcmp(typname, "bpchar"))
7530 int len = (typmod - VARHDRSZ);
7532 appendPQExpBuffer(buf, "character");
7534 appendPQExpBuffer(buf, "(%d)",
7537 else if (!strcmp(typname, "varchar"))
7539 appendPQExpBuffer(buf, "character varying");
7541 appendPQExpBuffer(buf, "(%d)",
7544 else if (!strcmp(typname, "numeric"))
7546 appendPQExpBuffer(buf, "numeric");
7553 tmp_typmod = typmod - VARHDRSZ;
7554 precision = (tmp_typmod >> 16) & 0xffff;
7555 scale = tmp_typmod & 0xffff;
7556 appendPQExpBuffer(buf, "(%d,%d)",
7562 * char is an internal single-byte data type; Let's make sure we force
7563 * it through with quotes. - thomas 1998-12-13
7565 else if (strcmp(typname, "char") == 0)
7566 appendPQExpBuffer(buf, "\"char\"");
7568 appendPQExpBuffer(buf, "%s", fmtId(typname));
7570 result = strdup(buf->data);
7571 destroyPQExpBuffer(buf);
7577 * fmtQualifiedId - convert a qualified name to the proper format for
7578 * the source database.
7580 * Like fmtId, use the result before calling again.
7583 fmtQualifiedId(const char *schema, const char *id)
7585 static PQExpBuffer id_return = NULL;
7587 if (id_return) /* first time through? */
7588 resetPQExpBuffer(id_return);
7590 id_return = createPQExpBuffer();
7592 /* Suppress schema name if fetching from pre-7.3 DB */
7593 if (g_fout->remoteVersion >= 70300 && schema && *schema)
7595 appendPQExpBuffer(id_return, "%s.",
7598 appendPQExpBuffer(id_return, "%s",
7601 return id_return->data;
7605 * Return a column list clause for the given relation.
7607 * Special case: if there are no undropped columns in the relation, return
7608 * "", not an invalid "()" column list.
7611 fmtCopyColumnList(const TableInfo *ti)
7613 static PQExpBuffer q = NULL;
7614 int numatts = ti->numatts;
7615 char **attnames = ti->attnames;
7616 bool *attisdropped = ti->attisdropped;
7620 if (q) /* first time through? */
7621 resetPQExpBuffer(q);
7623 q = createPQExpBuffer();
7625 appendPQExpBuffer(q, "(");
7627 for (i = 0; i < numatts; i++)
7629 if (attisdropped[i])
7632 appendPQExpBuffer(q, ", ");
7633 appendPQExpBuffer(q, "%s", fmtId(attnames[i]));
7638 return ""; /* no undropped columns */
7640 appendPQExpBuffer(q, ")");
7645 * Convenience subroutine to execute a SQL command and check for
7646 * COMMAND_OK status.
7649 do_sql_command(PGconn *conn, const char *query)
7653 res = PQexec(conn, query);
7654 check_sql_result(res, conn, query, PGRES_COMMAND_OK);
7659 * Convenience subroutine to verify a SQL command succeeded,
7660 * and exit with a useful error message if not.
7663 check_sql_result(PGresult *res, PGconn *conn, const char *query,
7664 ExecStatusType expected)
7668 if (res && PQresultStatus(res) == expected)
7671 write_msg(NULL, "SQL command failed\n");
7673 err = PQresultErrorMessage(res);
7675 err = PQerrorMessage(conn);
7676 write_msg(NULL, "Error message from server: %s", err);
7677 write_msg(NULL, "The command was: %s\n", query);