1 /*-------------------------------------------------------------------------
4 * pg_dump is a utility for dumping out a postgres database
7 * Portions Copyright (c) 1996-2004, 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.392 2004/11/06 19:36:02 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 ...
41 #include "getopt_long.h"
47 #include "access/attnum.h"
48 #include "access/htup.h"
49 #include "catalog/pg_class.h"
50 #include "catalog/pg_proc.h"
51 #include "catalog/pg_trigger.h"
52 #include "catalog/pg_type.h"
54 #include "commands/sequence.h"
57 #include "libpq/libpq-fs.h"
60 #include "pg_backup.h"
61 #include "pg_backup_archiver.h"
62 #include "dumputils.h"
64 #define _(x) gettext((x))
73 const char *descr; /* comment for an object */
74 Oid classoid; /* object class (catalog OID) */
75 Oid objoid; /* object OID */
76 int objsubid; /* subobject (table column #) */
81 bool g_verbose; /* User wants verbose narration of our
83 Archive *g_fout; /* the script file */
84 PGconn *g_conn; /* the database connection */
86 /* various user-settable parameters */
87 bool dumpInserts; /* dump data using proper insert strings */
88 bool attrNames; /* put attr names into insert strings */
93 /* obsolete as of 7.3: */
94 static Oid g_last_builtin_oid; /* value of the last builtin oid */
96 static char *selectTableName = NULL; /* name of a single table to dump */
97 static char *selectSchemaName = NULL; /* name of a single schema to dump */
99 char g_opaque_type[10]; /* name for the opaque type */
101 /* placeholders for the delimiters for comments */
102 char g_comment_start[10];
103 char g_comment_end[10];
105 static const CatalogId nilCatalogId = {0, 0};
107 /* these are to avoid passing around info for findNamespace() */
108 static NamespaceInfo *g_namespaces;
109 static int g_numNamespaces;
111 /* flag to turn on/off dollar quoting */
112 static int disable_dollar_quoting = 0;
115 static void help(const char *progname);
116 static NamespaceInfo *findNamespace(Oid nsoid, Oid objoid);
117 static void dumpTableData(Archive *fout, TableDataInfo *tdinfo);
118 static void dumpComment(Archive *fout, const char *target,
119 const char *namespace, const char *owner,
120 CatalogId catalogId, int subid, DumpId dumpId);
121 static int findComments(Archive *fout, Oid classoid, Oid objoid,
122 CommentItem **items);
123 static int collectComments(Archive *fout, CommentItem **items);
124 static void dumpDumpableObject(Archive *fout, DumpableObject *dobj);
125 static void dumpNamespace(Archive *fout, NamespaceInfo *nspinfo);
126 static void dumpType(Archive *fout, TypeInfo *tinfo);
127 static void dumpBaseType(Archive *fout, TypeInfo *tinfo);
128 static void dumpDomain(Archive *fout, TypeInfo *tinfo);
129 static void dumpCompositeType(Archive *fout, TypeInfo *tinfo);
130 static void dumpProcLang(Archive *fout, ProcLangInfo *plang);
131 static void dumpFunc(Archive *fout, FuncInfo *finfo);
132 static void dumpCast(Archive *fout, CastInfo *cast);
133 static void dumpOpr(Archive *fout, OprInfo *oprinfo);
134 static void dumpOpclass(Archive *fout, OpclassInfo *opcinfo);
135 static void dumpConversion(Archive *fout, ConvInfo *convinfo);
136 static void dumpRule(Archive *fout, RuleInfo *rinfo);
137 static void dumpAgg(Archive *fout, AggInfo *agginfo);
138 static void dumpTrigger(Archive *fout, TriggerInfo *tginfo);
139 static void dumpTable(Archive *fout, TableInfo *tbinfo);
140 static void dumpTableSchema(Archive *fout, TableInfo *tbinfo);
141 static void dumpAttrDef(Archive *fout, AttrDefInfo *adinfo);
142 static void dumpSequence(Archive *fout, TableInfo *tbinfo);
143 static void dumpIndex(Archive *fout, IndxInfo *indxinfo);
144 static void dumpConstraint(Archive *fout, ConstraintInfo *coninfo);
146 static void dumpACL(Archive *fout, CatalogId objCatId, DumpId objDumpId,
147 const char *type, const char *name,
148 const char *tag, const char *nspname, const char *owner,
151 static void getDependencies(void);
152 static void getDomainConstraints(TypeInfo *tinfo);
153 static void getTableData(TableInfo *tblinfo, int numTables, bool oids);
154 static char *format_function_signature(FuncInfo *finfo, char **argnames,
156 static const char *convertRegProcReference(const char *proc);
157 static const char *convertOperatorReference(const char *opr);
158 static Oid findLastBuiltinOid_V71(const char *);
159 static Oid findLastBuiltinOid_V70(void);
160 static void setMaxOid(Archive *fout);
161 static void selectSourceSchema(const char *schemaName);
162 static char *getFormattedTypeName(Oid oid, OidOptions opts);
163 static char *myFormatType(const char *typname, int32 typmod);
164 static const char *fmtQualifiedId(const char *schema, const char *id);
165 static int dumpBlobs(Archive *AH, void *arg);
166 static void dumpDatabase(Archive *AH);
167 static void dumpTimestamp(Archive *AH, char *msg);
168 static void dumpEncoding(Archive *AH);
169 static const char *getAttrName(int attrnum, TableInfo *tblInfo);
170 static const char *fmtCopyColumnList(const TableInfo *ti);
171 static void do_sql_command(PGconn *conn, const char *query);
172 static void check_sql_result(PGresult *res, PGconn *conn, const char *query,
173 ExecStatusType expected);
177 main(int argc, char **argv)
180 const char *filename = NULL;
181 const char *format = "p";
182 const char *dbname = NULL;
183 const char *pghost = NULL;
184 const char *pgport = NULL;
185 const char *username = NULL;
189 DumpableObject **dobjs;
192 bool force_password = false;
193 int compressLevel = -1;
194 bool ignore_version = false;
197 int outputCreate = 0;
199 int outputNoOwner = 0;
200 static int use_setsessauth = 0;
201 static int disable_triggers = 0;
202 char *outputSuperuser = NULL;
204 RestoreOptions *ropt;
206 static struct option long_options[] = {
207 {"data-only", no_argument, NULL, 'a'},
208 {"blobs", no_argument, NULL, 'b'},
209 {"clean", no_argument, NULL, 'c'},
210 {"create", no_argument, NULL, 'C'},
211 {"file", required_argument, NULL, 'f'},
212 {"format", required_argument, NULL, 'F'},
213 {"inserts", no_argument, NULL, 'd'},
214 {"attribute-inserts", no_argument, NULL, 'D'},
215 {"column-inserts", no_argument, NULL, 'D'},
216 {"host", required_argument, NULL, 'h'},
217 {"ignore-version", no_argument, NULL, 'i'},
218 {"no-reconnect", no_argument, NULL, 'R'},
219 {"oids", no_argument, NULL, 'o'},
220 {"no-owner", no_argument, NULL, 'O'},
221 {"port", required_argument, NULL, 'p'},
222 {"schema", required_argument, NULL, 'n'},
223 {"schema-only", no_argument, NULL, 's'},
224 {"superuser", required_argument, NULL, 'S'},
225 {"table", required_argument, NULL, 't'},
226 {"password", no_argument, NULL, 'W'},
227 {"username", required_argument, NULL, 'U'},
228 {"verbose", no_argument, NULL, 'v'},
229 {"no-privileges", no_argument, NULL, 'x'},
230 {"no-acl", no_argument, NULL, 'x'},
231 {"compress", required_argument, NULL, 'Z'},
232 {"help", no_argument, NULL, '?'},
233 {"version", no_argument, NULL, 'V'},
236 * the following options don't have an equivalent short option
237 * letter, but are available as '-X long-name'
239 {"disable-dollar-quoting", no_argument, &disable_dollar_quoting, 1},
240 {"disable-triggers", no_argument, &disable_triggers, 1},
241 {"use-set-session-authorization", no_argument, &use_setsessauth, 1},
247 set_pglocale_pgservice(argv[0], "pg_dump");
251 strcpy(g_comment_start, "-- ");
252 g_comment_end[0] = '\0';
253 strcpy(g_opaque_type, "opaque");
255 dataOnly = schemaOnly = dumpInserts = attrNames = false;
257 progname = get_progname(argv[0]);
259 /* Set default options based on progname */
260 if (strcmp(progname, "pg_backup") == 0)
268 if (strcmp(argv[1], "--help") == 0 || strcmp(argv[1], "-?") == 0)
273 if (strcmp(argv[1], "--version") == 0 || strcmp(argv[1], "-V") == 0)
275 puts("pg_dump (PostgreSQL) " PG_VERSION);
280 while ((c = getopt_long(argc, argv, "abcCdDf:F:h:in:oOp:RsS:t:uU:vWxX:Z:",
281 long_options, &optindex)) != -1)
285 case 'a': /* Dump data only */
289 case 'b': /* Dump blobs */
293 case 'c': /* clean (i.e., drop) schema prior to
298 case 'C': /* Create DB */
302 case 'd': /* dump data as proper insert strings */
306 case 'D': /* dump data as proper insert strings with
320 case 'h': /* server host */
324 case 'i': /* ignore database version mismatch */
325 ignore_version = true;
328 case 'n': /* Dump data for this schema only */
329 selectSchemaName = strdup(optarg);
332 case 'o': /* Dump oids */
336 case 'O': /* Don't reconnect to match owner */
340 case 'p': /* server port */
345 /* no-op, still accepted for backwards compatibility */
348 case 's': /* dump schema only */
352 case 'S': /* Username for superuser in plain text
354 outputSuperuser = strdup(optarg);
357 case 't': /* Dump data for this table only */
358 selectTableName = strdup(optarg);
362 force_password = true;
363 username = simple_prompt("User name: ", 100, true);
370 case 'v': /* verbose */
375 force_password = true;
378 case 'x': /* skip ACL dump */
383 * Option letters were getting scarce, so I invented this
384 * new scheme: '-X feature' turns on some feature. Compare
385 * to the -f option in GCC. You should also add an
386 * equivalent GNU-style option --feature. Features that
387 * require arguments should use '-X feature=foo'.
390 if (strcmp(optarg, "disable-dollar-quoting") == 0)
391 disable_dollar_quoting = 1;
392 else if (strcmp(optarg, "disable-triggers") == 0)
393 disable_triggers = 1;
394 else if (strcmp(optarg, "use-set-session-authorization") == 0)
399 _("%s: invalid -X option -- %s\n"),
401 fprintf(stderr, _("Try \"%s --help\" for more information.\n"), progname);
406 case 'Z': /* Compression Level */
407 compressLevel = atoi(optarg);
409 /* This covers the long options equivalent to -X xxx. */
415 fprintf(stderr, _("Try \"%s --help\" for more information.\n"), progname);
420 if (optind < (argc - 1))
422 fprintf(stderr, _("%s: too many command-line arguments (first is \"%s\")\n"),
423 progname, argv[optind + 1]);
424 fprintf(stderr, _("Try \"%s --help\" for more information.\n"),
429 /* Get database name from command line */
431 dbname = argv[optind];
433 if (dataOnly && schemaOnly)
435 write_msg(NULL, "options \"schema only\" (-s) and \"data only\" (-a) cannot be used together\n");
439 if (dataOnly && outputClean)
441 write_msg(NULL, "options \"clean\" (-c) and \"data only\" (-a) cannot be used together\n");
445 if (outputBlobs && selectTableName != NULL)
447 write_msg(NULL, "large-object output not supported for a single table\n");
448 write_msg(NULL, "use a full dump instead\n");
452 if (outputBlobs && selectSchemaName != NULL)
454 write_msg(NULL, "large-object output not supported for a single schema\n");
455 write_msg(NULL, "use a full dump instead\n");
459 if (dumpInserts == true && oids == true)
461 write_msg(NULL, "INSERT (-d, -D) and OID (-o) options cannot be used together\n");
462 write_msg(NULL, "(The INSERT command cannot set OIDs.)\n");
466 if (outputBlobs == true && (format[0] == 'p' || format[0] == 'P'))
468 write_msg(NULL, "large-object output is not supported for plain-text dump files\n");
469 write_msg(NULL, "(Use a different output format.)\n");
473 /* open the output file */
478 g_fout = CreateArchive(filename, archCustom, compressLevel);
483 g_fout = CreateArchive(filename, archFiles, compressLevel);
489 g_fout = CreateArchive(filename, archNull, 0);
494 g_fout = CreateArchive(filename, archTar, compressLevel);
498 write_msg(NULL, "invalid output format \"%s\" specified\n", format);
504 write_msg(NULL, "could not open output file \"%s\" for writing\n", filename);
508 /* Let the archiver know how noisy to be */
509 g_fout->verbose = g_verbose;
511 g_fout->minRemoteVersion = 70000; /* we can handle back to 7.0 */
512 g_fout->maxRemoteVersion = parse_version(PG_VERSION);
513 if (g_fout->maxRemoteVersion < 0)
515 write_msg(NULL, "could not parse version string \"%s\"\n", PG_VERSION);
520 * Open the database using the Archiver, so it knows about it. Errors
523 g_conn = ConnectDatabase(g_fout, dbname, pghost, pgport,
524 username, force_password, ignore_version);
527 * Start serializable transaction to dump consistent data.
529 do_sql_command(g_conn, "BEGIN");
531 do_sql_command(g_conn, "SET TRANSACTION ISOLATION LEVEL SERIALIZABLE");
533 /* Set the datestyle to ISO to ensure the dump's portability */
534 do_sql_command(g_conn, "SET DATESTYLE = ISO");
537 * If supported, set extra_float_digits so that we can dump float data
538 * exactly (given correctly implemented float I/O code, anyway)
540 if (g_fout->remoteVersion >= 70400)
541 do_sql_command(g_conn, "SET extra_float_digits TO 2");
543 /* Find the last built-in OID, if needed */
544 if (g_fout->remoteVersion < 70300)
546 if (g_fout->remoteVersion >= 70100)
547 g_last_builtin_oid = findLastBuiltinOid_V71(PQdb(g_conn));
549 g_last_builtin_oid = findLastBuiltinOid_V70();
551 write_msg(NULL, "last built-in OID is %u\n", g_last_builtin_oid);
555 * Now scan the database and create DumpableObject structs for all the
556 * objects we intend to dump.
558 tblinfo = getSchemaData(&numTables, schemaOnly, dataOnly);
561 getTableData(tblinfo, numTables, oids);
565 /* This is just a placeholder to allow correct sorting of blobs */
566 DumpableObject *blobobj;
568 blobobj = (DumpableObject *) malloc(sizeof(DumpableObject));
569 blobobj->objType = DO_BLOBS;
570 blobobj->catId = nilCatalogId;
571 AssignDumpId(blobobj);
572 blobobj->name = strdup("BLOBS");
576 * Collect dependency data to assist in ordering the objects.
581 * Sort the objects into a safe dump order (no forward references).
583 * In 7.3 or later, we can rely on dependency information to help us
584 * determine a safe order, so the initial sort is mostly for cosmetic
585 * purposes: we sort by name to ensure that logically identical
586 * schemas will dump identically. Before 7.3 we don't have
587 * dependencies and we use OID ordering as an (unreliable) guide to
590 getDumpableObjects(&dobjs, &numObjs);
592 if (g_fout->remoteVersion >= 70300)
593 sortDumpableObjectsByTypeName(dobjs, numObjs);
595 sortDumpableObjectsByTypeOid(dobjs, numObjs);
597 sortDumpableObjects(dobjs, numObjs);
600 * Create archive TOC entries for all the objects to be dumped, in a
605 dumpTimestamp(g_fout, "Started on");
607 /* First the special encoding entry. */
608 dumpEncoding(g_fout);
610 /* The database item is always second. */
612 dumpDatabase(g_fout);
614 /* Max OID is next. */
618 /* Now the rearrangeable objects. */
619 for (i = 0; i < numObjs; i++)
620 dumpDumpableObject(g_fout, dobjs[i]);
623 dumpTimestamp(g_fout, "Completed on");
626 * And finally we can do the actual output.
630 ropt = NewRestoreOptions();
631 ropt->filename = (char *) filename;
632 ropt->dropSchema = outputClean;
633 ropt->aclsSkip = aclsSkip;
634 ropt->superuser = outputSuperuser;
635 ropt->create = outputCreate;
636 ropt->noOwner = outputNoOwner;
637 ropt->disable_triggers = disable_triggers;
638 ropt->use_setsessauth = use_setsessauth;
640 if (compressLevel == -1)
641 ropt->compression = 0;
643 ropt->compression = compressLevel;
645 ropt->suppressDumpWarnings = true; /* We've already shown
648 RestoreArchive(g_fout, ropt);
651 CloseArchive(g_fout);
660 help(const char *progname)
662 printf(_("%s dumps a database as a text file or to other formats.\n\n"), progname);
663 printf(_("Usage:\n"));
664 printf(_(" %s [OPTION]... [DBNAME]\n"), progname);
666 printf(_("\nGeneral options:\n"));
667 printf(_(" -f, --file=FILENAME output file name\n"));
668 printf(_(" -F, --format=c|t|p output file format (custom, tar, plain text)\n"));
669 printf(_(" -i, --ignore-version proceed even when server version mismatches\n"
670 " pg_dump version\n"));
671 printf(_(" -v, --verbose verbose mode\n"));
672 printf(_(" -Z, --compress=0-9 compression level for compressed formats\n"));
673 printf(_(" --help show this help, then exit\n"));
674 printf(_(" --version output version information, then exit\n"));
676 printf(_("\nOptions controlling the output content:\n"));
677 printf(_(" -a, --data-only dump only the data, not the schema\n"));
678 printf(_(" -b, --blobs include large objects in dump\n"));
679 printf(_(" -c, --clean clean (drop) schema prior to create\n"));
680 printf(_(" -C, --create include commands to create database in dump\n"));
681 printf(_(" -d, --inserts dump data as INSERT, rather than COPY, commands\n"));
682 printf(_(" -D, --column-inserts dump data as INSERT commands with column names\n"));
683 printf(_(" -n, --schema=SCHEMA dump the named schema only\n"));
684 printf(_(" -o, --oids include OIDs in dump\n"));
685 printf(_(" -O, --no-owner do not output commands to set object ownership\n"
686 " in plain text format\n"));
687 printf(_(" -s, --schema-only dump only the schema, no data\n"));
688 printf(_(" -S, --superuser=NAME specify the superuser user name to use in\n"
689 " plain text format\n"));
690 printf(_(" -t, --table=TABLE dump the named table only\n"));
691 printf(_(" -x, --no-privileges do not dump privileges (grant/revoke)\n"));
692 printf(_(" -X disable-dollar-quoting, --disable-dollar-quoting\n"
693 " disable dollar quoting, use SQL standard quoting\n"));
694 printf(_(" -X disable-triggers, --disable-triggers\n"
695 " disable triggers during data-only restore\n"));
696 printf(_(" -X use-set-session-authorization, --use-set-session-authorization\n"
697 " use SESSION AUTHORIZATION commands instead of\n"
698 " OWNER TO commands\n"));
700 printf(_("\nConnection options:\n"));
701 printf(_(" -h, --host=HOSTNAME database server host or socket directory\n"));
702 printf(_(" -p, --port=PORT database server port number\n"));
703 printf(_(" -U, --username=NAME connect as specified database user\n"));
704 printf(_(" -W, --password force password prompt (should happen automatically)\n"));
706 printf(_("\nIf no database name is supplied, then the PGDATABASE environment\n"
707 "variable value is used.\n\n"));
708 printf(_("Report bugs to <pgsql-bugs@postgresql.org>.\n"));
716 write_msg(NULL, "*** aborted because of error\n");
721 * selectDumpableNamespace: policy-setting subroutine
722 * Mark a namespace as to be dumped or not
725 selectDumpableNamespace(NamespaceInfo *nsinfo)
728 * If a specific table is being dumped, do not dump any complete
729 * namespaces. If a specific namespace is being dumped, dump just
730 * that namespace. Otherwise, dump all non-system namespaces.
732 if (selectTableName != NULL)
733 nsinfo->dump = false;
734 else if (selectSchemaName != NULL)
736 if (strcmp(nsinfo->dobj.name, selectSchemaName) == 0)
739 nsinfo->dump = false;
741 else if (strncmp(nsinfo->dobj.name, "pg_", 3) == 0 ||
742 strcmp(nsinfo->dobj.name, "information_schema") == 0)
743 nsinfo->dump = false;
749 * selectDumpableTable: policy-setting subroutine
750 * Mark a table as to be dumped or not
753 selectDumpableTable(TableInfo *tbinfo)
756 * Always dump if dumping parent namespace; else, if a particular
757 * tablename has been specified, dump matching table name; else, do
760 tbinfo->dump = false;
761 if (tbinfo->dobj.namespace->dump)
763 else if (selectTableName != NULL &&
764 strcmp(tbinfo->dobj.name, selectTableName) == 0)
766 /* If both -s and -t specified, must match both to dump */
767 if (selectSchemaName == NULL)
769 else if (strcmp(tbinfo->dobj.namespace->dobj.name, selectSchemaName) == 0)
775 * Dump a table's contents for loading using the COPY command
776 * - this routine is called by the Archiver when it wants the table
780 #define COPYBUFSIZ 8192
783 dumpTableData_copy(Archive *fout, void *dcontext)
785 TableDataInfo *tdinfo = (TableDataInfo *) dcontext;
786 TableInfo *tbinfo = tdinfo->tdtable;
787 const char *classname = tbinfo->dobj.name;
788 const bool hasoids = tbinfo->hasoids;
789 const bool oids = tdinfo->oids;
790 PQExpBuffer q = createPQExpBuffer();
794 char copybuf[COPYBUFSIZ];
795 const char *column_list;
798 write_msg(NULL, "dumping contents of table %s\n", classname);
801 * Make sure we are in proper schema. We will qualify the table name
802 * below anyway (in case its name conflicts with a pg_catalog table);
803 * but this ensures reproducible results in case the table contains
804 * regproc, regclass, etc columns.
806 selectSourceSchema(tbinfo->dobj.namespace->dobj.name);
809 * If possible, specify the column list explicitly so that we have no
810 * possibility of retrieving data in the wrong column order. (The
811 * default column ordering of COPY will not be what we want in certain
812 * corner cases involving ADD COLUMN and inheritance.)
814 if (g_fout->remoteVersion >= 70300)
815 column_list = fmtCopyColumnList(tbinfo);
817 column_list = ""; /* can't select columns in COPY */
821 appendPQExpBuffer(q, "COPY %s %s WITH OIDS TO stdout;",
822 fmtQualifiedId(tbinfo->dobj.namespace->dobj.name,
828 appendPQExpBuffer(q, "COPY %s %s TO stdout;",
829 fmtQualifiedId(tbinfo->dobj.namespace->dobj.name,
833 res = PQexec(g_conn, q->data);
834 check_sql_result(res, g_conn, q->data, PGRES_COPY_OUT);
840 ret = PQgetline(g_conn, copybuf, COPYBUFSIZ);
842 if (copybuf[0] == '\\' &&
846 copydone = true; /* don't print this... */
850 archputs(copybuf, fout);
857 archputs("\n", fout);
867 * There was considerable discussion in late July, 2000 regarding
868 * slowing down pg_dump when backing up large tables. Users with
869 * both slow & fast (muti-processor) machines experienced
870 * performance degradation when doing a backup.
872 * Initial attempts based on sleeping for a number of ms for each ms
873 * of work were deemed too complex, then a simple 'sleep in each
874 * loop' implementation was suggested. The latter failed because
875 * the loop was too tight. Finally, the following was implemented:
877 * If throttle is non-zero, then See how long since the last sleep.
878 * Work out how long to sleep (based on ratio). If sleep is more
879 * than 100ms, then sleep reset timer EndIf EndIf
881 * where the throttle value was the number of ms to sleep per ms of
882 * work. The calculation was done in each loop.
884 * Most of the hard work is done in the backend, and this solution
885 * still did not work particularly well: on slow machines, the
886 * ratio was 50:1, and on medium paced machines, 1:1, and on fast
887 * multi-processor machines, it had little or no effect, for
888 * reasons that were unclear.
890 * Further discussion ensued, and the proposal was dropped.
892 * For those people who want this feature, it can be implemented
893 * using gettimeofday in each loop, calculating the time since
894 * last sleep, multiplying that by the sleep ratio, then if the
895 * result is more than a preset 'minimum sleep time' (say 100ms),
896 * call the 'select' function to sleep for a subsecond period ie.
898 * select(0, NULL, NULL, NULL, &tvi);
900 * This will return after the interval specified in the structure
901 * tvi. Finally, call gettimeofday again to save the 'last sleep
905 archprintf(fout, "\\.\n\n\n");
907 ret = PQendcopy(g_conn);
910 write_msg(NULL, "SQL command to dump the contents of table \"%s\" failed: PQendcopy() failed.\n", classname);
911 write_msg(NULL, "Error message from server: %s", PQerrorMessage(g_conn));
912 write_msg(NULL, "The command was: %s\n", q->data);
917 destroyPQExpBuffer(q);
922 dumpTableData_insert(Archive *fout, void *dcontext)
924 TableDataInfo *tdinfo = (TableDataInfo *) dcontext;
925 TableInfo *tbinfo = tdinfo->tdtable;
926 const char *classname = tbinfo->dobj.name;
927 PQExpBuffer q = createPQExpBuffer();
934 * Make sure we are in proper schema. We will qualify the table name
935 * below anyway (in case its name conflicts with a pg_catalog table);
936 * but this ensures reproducible results in case the table contains
937 * regproc, regclass, etc columns.
939 selectSourceSchema(tbinfo->dobj.namespace->dobj.name);
941 if (fout->remoteVersion >= 70100)
943 appendPQExpBuffer(q, "DECLARE _pg_dump_cursor CURSOR FOR "
944 "SELECT * FROM ONLY %s",
945 fmtQualifiedId(tbinfo->dobj.namespace->dobj.name,
950 appendPQExpBuffer(q, "DECLARE _pg_dump_cursor CURSOR FOR "
952 fmtQualifiedId(tbinfo->dobj.namespace->dobj.name,
956 res = PQexec(g_conn, q->data);
957 check_sql_result(res, g_conn, q->data, PGRES_COMMAND_OK);
963 res = PQexec(g_conn, "FETCH 100 FROM _pg_dump_cursor");
964 check_sql_result(res, g_conn, "FETCH 100 FROM _pg_dump_cursor",
966 nfields = PQnfields(res);
967 for (tuple = 0; tuple < PQntuples(res); tuple++)
969 archprintf(fout, "INSERT INTO %s ", fmtId(classname));
972 /* corner case for zero-column table */
973 archprintf(fout, "DEFAULT VALUES;\n");
976 if (attrNames == true)
979 appendPQExpBuffer(q, "(");
980 for (field = 0; field < nfields; field++)
983 appendPQExpBuffer(q, ", ");
984 appendPQExpBuffer(q, fmtId(PQfname(res, field)));
986 appendPQExpBuffer(q, ") ");
987 archprintf(fout, "%s", q->data);
989 archprintf(fout, "VALUES (");
990 for (field = 0; field < nfields; field++)
993 archprintf(fout, ", ");
994 if (PQgetisnull(res, tuple, field))
996 archprintf(fout, "NULL");
1000 /* XXX This code is partially duplicated in ruleutils.c */
1001 switch (PQftype(res, field))
1012 * These types are printed without quotes
1013 * unless they contain values that aren't
1014 * accepted by the scanner unquoted (e.g.,
1015 * 'NaN'). Note that strtod() and friends
1016 * might accept NaN, so we can't use that to
1019 * In reality we only need to defend against
1020 * infinity and NaN, so we need not get too
1021 * crazy about pattern matching here.
1023 const char *s = PQgetvalue(res, tuple, field);
1025 if (strspn(s, "0123456789 +-eE.") == strlen(s))
1026 archprintf(fout, "%s", s);
1028 archprintf(fout, "'%s'", s);
1034 archprintf(fout, "B'%s'",
1035 PQgetvalue(res, tuple, field));
1039 if (strcmp(PQgetvalue(res, tuple, field), "t") == 0)
1040 archprintf(fout, "true");
1042 archprintf(fout, "false");
1046 /* All other types are printed as string literals. */
1047 resetPQExpBuffer(q);
1048 appendStringLiteral(q, PQgetvalue(res, tuple, field), false);
1049 archprintf(fout, "%s", q->data);
1053 archprintf(fout, ");\n");
1055 } while (PQntuples(res) > 0);
1059 archprintf(fout, "\n\n");
1061 do_sql_command(g_conn, "CLOSE _pg_dump_cursor");
1063 destroyPQExpBuffer(q);
1070 * dump the contents of a single table
1072 * Actually, this just makes an ArchiveEntry for the table contents.
1075 dumpTableData(Archive *fout, TableDataInfo *tdinfo)
1077 TableInfo *tbinfo = tdinfo->tdtable;
1078 PQExpBuffer copyBuf = createPQExpBuffer();
1079 DataDumperPtr dumpFn;
1084 /* Dump/restore using COPY */
1085 dumpFn = dumpTableData_copy;
1086 /* must use 2 steps here 'cause fmtId is nonreentrant */
1087 appendPQExpBuffer(copyBuf, "COPY %s ",
1088 fmtId(tbinfo->dobj.name));
1089 appendPQExpBuffer(copyBuf, "%s %sFROM stdin;\n",
1090 fmtCopyColumnList(tbinfo),
1091 (tdinfo->oids && tbinfo->hasoids) ? "WITH OIDS " : "");
1092 copyStmt = copyBuf->data;
1096 /* Restore using INSERT */
1097 dumpFn = dumpTableData_insert;
1101 ArchiveEntry(fout, tdinfo->dobj.catId, tdinfo->dobj.dumpId,
1103 tbinfo->dobj.namespace->dobj.name,
1105 tbinfo->usename, false,
1106 "TABLE DATA", "", "", copyStmt,
1107 tdinfo->dobj.dependencies, tdinfo->dobj.nDeps,
1110 destroyPQExpBuffer(copyBuf);
1115 * set up dumpable objects representing the contents of tables
1118 getTableData(TableInfo *tblinfo, int numTables, bool oids)
1122 for (i = 0; i < numTables; i++)
1124 /* Skip VIEWs (no data to dump) */
1125 if (tblinfo[i].relkind == RELKIND_VIEW)
1127 /* Skip SEQUENCEs (handled elsewhere) */
1128 if (tblinfo[i].relkind == RELKIND_SEQUENCE)
1131 if (tblinfo[i].dump)
1133 TableDataInfo *tdinfo;
1135 tdinfo = (TableDataInfo *) malloc(sizeof(TableDataInfo));
1137 tdinfo->dobj.objType = DO_TABLE_DATA;
1140 * Note: use tableoid 0 so that this object won't be mistaken
1141 * for something that pg_depend entries apply to.
1143 tdinfo->dobj.catId.tableoid = 0;
1144 tdinfo->dobj.catId.oid = tblinfo[i].dobj.catId.oid;
1145 AssignDumpId(&tdinfo->dobj);
1146 tdinfo->dobj.name = tblinfo[i].dobj.name;
1147 tdinfo->dobj.namespace = tblinfo[i].dobj.namespace;
1148 tdinfo->tdtable = &(tblinfo[i]);
1149 tdinfo->oids = oids;
1150 addObjectDependency(&tdinfo->dobj, tblinfo[i].dobj.dumpId);
1158 * dump the database definition
1161 dumpDatabase(Archive *AH)
1163 PQExpBuffer dbQry = createPQExpBuffer();
1164 PQExpBuffer delQry = createPQExpBuffer();
1165 PQExpBuffer creaQry = createPQExpBuffer();
1175 const char *datname,
1180 datname = PQdb(g_conn);
1183 write_msg(NULL, "saving database definition\n");
1185 /* Make sure we are in proper schema */
1186 selectSourceSchema("pg_catalog");
1188 /* Get the database owner and parameters from pg_database */
1189 if (g_fout->remoteVersion >= 80000)
1191 appendPQExpBuffer(dbQry, "SELECT tableoid, oid, "
1192 "(SELECT usename FROM pg_user WHERE usesysid = datdba) as dba, "
1193 "pg_encoding_to_char(encoding) as encoding, "
1194 "(SELECT spcname FROM pg_tablespace t WHERE t.oid = dattablespace) as tablespace "
1196 "WHERE datname = ");
1197 appendStringLiteral(dbQry, datname, true);
1199 else if (g_fout->remoteVersion >= 70100)
1201 appendPQExpBuffer(dbQry, "SELECT tableoid, oid, "
1202 "(SELECT usename FROM pg_user WHERE usesysid = datdba) as dba, "
1203 "pg_encoding_to_char(encoding) as encoding, "
1204 "NULL as tablespace "
1206 "WHERE datname = ");
1207 appendStringLiteral(dbQry, datname, true);
1211 appendPQExpBuffer(dbQry, "SELECT "
1212 "(SELECT oid FROM pg_class WHERE relname = 'pg_database') AS tableoid, "
1214 "(SELECT usename FROM pg_user WHERE usesysid = datdba) as dba, "
1215 "pg_encoding_to_char(encoding) as encoding, "
1216 "NULL as tablespace "
1218 "WHERE datname = ");
1219 appendStringLiteral(dbQry, datname, true);
1222 res = PQexec(g_conn, dbQry->data);
1223 check_sql_result(res, g_conn, dbQry->data, PGRES_TUPLES_OK);
1225 ntups = PQntuples(res);
1229 write_msg(NULL, "missing pg_database entry for database \"%s\"\n",
1236 write_msg(NULL, "query returned more than one (%d) pg_database entry for database \"%s\"\n",
1241 i_tableoid = PQfnumber(res, "tableoid");
1242 i_oid = PQfnumber(res, "oid");
1243 i_dba = PQfnumber(res, "dba");
1244 i_encoding = PQfnumber(res, "encoding");
1245 i_tablespace = PQfnumber(res, "tablespace");
1247 dbCatId.tableoid = atooid(PQgetvalue(res, 0, i_tableoid));
1248 dbCatId.oid = atooid(PQgetvalue(res, 0, i_oid));
1249 dba = PQgetvalue(res, 0, i_dba);
1250 encoding = PQgetvalue(res, 0, i_encoding);
1251 tablespace = PQgetvalue(res, 0, i_tablespace);
1253 appendPQExpBuffer(creaQry, "CREATE DATABASE %s WITH TEMPLATE = template0",
1255 if (strlen(encoding) > 0)
1257 appendPQExpBuffer(creaQry, " ENCODING = ");
1258 appendStringLiteral(creaQry, encoding, true);
1260 if (strlen(tablespace) > 0 && strcmp(tablespace, "pg_default") != 0)
1261 appendPQExpBuffer(creaQry, " TABLESPACE = %s",
1263 appendPQExpBuffer(creaQry, ";\n");
1265 appendPQExpBuffer(delQry, "DROP DATABASE %s;\n",
1268 dbDumpId = createDumpId();
1271 dbCatId, /* catalog ID */
1272 dbDumpId, /* dump ID */
1274 NULL, /* Namespace */
1275 NULL, /* Tablespace */
1277 false, /* with oids */
1278 "DATABASE", /* Desc */
1279 creaQry->data, /* Create */
1280 delQry->data, /* Del */
1285 NULL); /* Dumper Arg */
1287 /* Dump DB comment if any */
1288 resetPQExpBuffer(dbQry);
1289 appendPQExpBuffer(dbQry, "DATABASE %s", fmtId(datname));
1290 dumpComment(AH, dbQry->data, NULL, "",
1291 dbCatId, 0, dbDumpId);
1295 destroyPQExpBuffer(dbQry);
1296 destroyPQExpBuffer(delQry);
1297 destroyPQExpBuffer(creaQry);
1305 dumpTimestamp(Archive *AH, char *msg)
1308 time_t now = time(NULL);
1310 if (strftime(buf, 256, "%Y-%m-%d %H:%M:%S %Z", localtime(&now)) != 0)
1312 PQExpBuffer qry = createPQExpBuffer();
1314 appendPQExpBuffer(qry, "-- ");
1315 appendPQExpBuffer(qry, msg);
1316 appendPQExpBuffer(qry, " ");
1317 appendPQExpBuffer(qry, buf);
1318 appendPQExpBuffer(qry, "\n");
1320 ArchiveEntry(AH, nilCatalogId, createDumpId(),
1321 "DUMP TIMESTAMP", NULL, NULL, "",
1322 false, "DUMP TIMESTAMP", qry->data, "", NULL,
1325 destroyPQExpBuffer(qry);
1331 * dumpEncoding: put the correct encoding into the archive
1334 dumpEncoding(Archive *AH)
1339 /* Can't read the encoding from pre-7.3 servers (SHOW isn't a query) */
1340 if (AH->remoteVersion < 70300)
1344 write_msg(NULL, "saving encoding\n");
1346 qry = createPQExpBuffer();
1348 appendPQExpBuffer(qry, "SHOW client_encoding");
1350 res = PQexec(g_conn, qry->data);
1352 check_sql_result(res, g_conn, qry->data, PGRES_TUPLES_OK);
1354 resetPQExpBuffer(qry);
1356 appendPQExpBuffer(qry, "SET client_encoding = ");
1357 appendStringLiteral(qry, PQgetvalue(res, 0, 0), true);
1358 appendPQExpBuffer(qry, ";\n");
1360 ArchiveEntry(AH, nilCatalogId, createDumpId(),
1361 "ENCODING", NULL, NULL, "",
1362 false, "ENCODING", qry->data, "", NULL,
1368 destroyPQExpBuffer(qry);
1378 #define loBufSize 16384
1379 #define loFetchSize 1000
1382 dumpBlobs(Archive *AH, void *arg)
1384 PQExpBuffer oidQry = createPQExpBuffer();
1385 PQExpBuffer oidFetchQry = createPQExpBuffer();
1389 char buf[loBufSize];
1394 write_msg(NULL, "saving large objects\n");
1396 /* Make sure we are in proper schema */
1397 selectSourceSchema("pg_catalog");
1399 /* Cursor to get all BLOB tables */
1400 if (AH->remoteVersion >= 70100)
1401 appendPQExpBuffer(oidQry, "DECLARE bloboid CURSOR FOR SELECT DISTINCT loid FROM pg_largeobject");
1403 appendPQExpBuffer(oidQry, "DECLARE bloboid CURSOR FOR SELECT oid FROM pg_class WHERE relkind = 'l'");
1405 res = PQexec(g_conn, oidQry->data);
1406 check_sql_result(res, g_conn, oidQry->data, PGRES_COMMAND_OK);
1408 /* Fetch for cursor */
1409 appendPQExpBuffer(oidFetchQry, "FETCH %d IN bloboid", loFetchSize);
1416 res = PQexec(g_conn, oidFetchQry->data);
1417 check_sql_result(res, g_conn, oidFetchQry->data, PGRES_TUPLES_OK);
1419 /* Process the tuples, if any */
1420 for (i = 0; i < PQntuples(res); i++)
1422 blobOid = atooid(PQgetvalue(res, i, 0));
1424 loFd = lo_open(g_conn, blobOid, INV_READ);
1427 write_msg(NULL, "dumpBlobs(): could not open large object: %s",
1428 PQerrorMessage(g_conn));
1432 StartBlob(AH, blobOid);
1434 /* Now read it in chunks, sending data to archive */
1437 cnt = lo_read(g_conn, loFd, buf, loBufSize);
1440 write_msg(NULL, "dumpBlobs(): error reading large object: %s",
1441 PQerrorMessage(g_conn));
1445 WriteData(AH, buf, cnt);
1449 lo_close(g_conn, loFd);
1451 EndBlob(AH, blobOid);
1454 } while (PQntuples(res) > 0);
1456 destroyPQExpBuffer(oidQry);
1457 destroyPQExpBuffer(oidFetchQry);
1464 * read all namespaces in the system catalogs and return them in the
1465 * NamespaceInfo* structure
1467 * numNamespaces is set to the number of namespaces read in
1470 getNamespaces(int *numNamespaces)
1476 NamespaceInfo *nsinfo;
1484 * Before 7.3, there are no real namespaces; create two dummy entries,
1485 * one for user stuff and one for system stuff.
1487 if (g_fout->remoteVersion < 70300)
1489 nsinfo = (NamespaceInfo *) malloc(2 * sizeof(NamespaceInfo));
1491 nsinfo[0].dobj.objType = DO_NAMESPACE;
1492 nsinfo[0].dobj.catId.tableoid = 0;
1493 nsinfo[0].dobj.catId.oid = 0;
1494 AssignDumpId(&nsinfo[0].dobj);
1495 nsinfo[0].dobj.name = strdup("public");
1496 nsinfo[0].usename = strdup("");
1497 nsinfo[0].nspacl = strdup("");
1499 selectDumpableNamespace(&nsinfo[0]);
1501 nsinfo[1].dobj.objType = DO_NAMESPACE;
1502 nsinfo[1].dobj.catId.tableoid = 0;
1503 nsinfo[1].dobj.catId.oid = 1;
1504 AssignDumpId(&nsinfo[1].dobj);
1505 nsinfo[1].dobj.name = strdup("pg_catalog");
1506 nsinfo[1].usename = strdup("");
1507 nsinfo[1].nspacl = strdup("");
1509 selectDumpableNamespace(&nsinfo[1]);
1511 g_namespaces = nsinfo;
1512 g_numNamespaces = *numNamespaces = 2;
1517 query = createPQExpBuffer();
1519 /* Make sure we are in proper schema */
1520 selectSourceSchema("pg_catalog");
1523 * we fetch all namespaces including system ones, so that every object
1524 * we read in can be linked to a containing namespace.
1526 appendPQExpBuffer(query, "SELECT tableoid, oid, nspname, "
1527 "(select usename from pg_user where nspowner = usesysid) as usename, "
1528 "nspacl FROM pg_namespace");
1530 res = PQexec(g_conn, query->data);
1531 check_sql_result(res, g_conn, query->data, PGRES_TUPLES_OK);
1533 ntups = PQntuples(res);
1535 nsinfo = (NamespaceInfo *) malloc(ntups * sizeof(NamespaceInfo));
1537 i_tableoid = PQfnumber(res, "tableoid");
1538 i_oid = PQfnumber(res, "oid");
1539 i_nspname = PQfnumber(res, "nspname");
1540 i_usename = PQfnumber(res, "usename");
1541 i_nspacl = PQfnumber(res, "nspacl");
1543 for (i = 0; i < ntups; i++)
1545 nsinfo[i].dobj.objType = DO_NAMESPACE;
1546 nsinfo[i].dobj.catId.tableoid = atooid(PQgetvalue(res, i, i_tableoid));
1547 nsinfo[i].dobj.catId.oid = atooid(PQgetvalue(res, i, i_oid));
1548 AssignDumpId(&nsinfo[i].dobj);
1549 nsinfo[i].dobj.name = strdup(PQgetvalue(res, i, i_nspname));
1550 nsinfo[i].usename = strdup(PQgetvalue(res, i, i_usename));
1551 nsinfo[i].nspacl = strdup(PQgetvalue(res, i, i_nspacl));
1553 /* Decide whether to dump this namespace */
1554 selectDumpableNamespace(&nsinfo[i]);
1556 if (strlen(nsinfo[i].usename) == 0)
1557 write_msg(NULL, "WARNING: owner of schema \"%s\" appears to be invalid\n",
1558 nsinfo[i].dobj.name);
1562 * If the user attempted to dump a specific namespace, check to ensure
1563 * that the specified namespace actually exists.
1565 if (selectSchemaName)
1567 for (i = 0; i < ntups; i++)
1568 if (strcmp(nsinfo[i].dobj.name, selectSchemaName) == 0)
1571 /* Didn't find a match */
1574 write_msg(NULL, "specified schema \"%s\" does not exist\n",
1581 destroyPQExpBuffer(query);
1583 g_namespaces = nsinfo;
1584 g_numNamespaces = *numNamespaces = ntups;
1591 * given a namespace OID and an object OID, look up the info read by
1594 * NB: for pre-7.3 source database, we use object OID to guess whether it's
1595 * a system object or not. In 7.3 and later there is no guessing.
1597 static NamespaceInfo *
1598 findNamespace(Oid nsoid, Oid objoid)
1602 if (g_fout->remoteVersion >= 70300)
1604 for (i = 0; i < g_numNamespaces; i++)
1606 NamespaceInfo *nsinfo = &g_namespaces[i];
1608 if (nsoid == nsinfo->dobj.catId.oid)
1611 write_msg(NULL, "schema with OID %u does not exist\n", nsoid);
1616 /* This code depends on the layout set up by getNamespaces. */
1617 if (objoid > g_last_builtin_oid)
1618 i = 0; /* user object */
1620 i = 1; /* system object */
1621 return &g_namespaces[i];
1624 return NULL; /* keep compiler quiet */
1629 * read all types in the system catalogs and return them in the
1630 * TypeInfo* structure
1632 * numTypes is set to the number of types read in
1634 * NB: this must run after getFuncs() because we assume we can do
1638 getTypes(int *numTypes)
1643 PQExpBuffer query = createPQExpBuffer();
1659 * we include even the built-in types because those may be used as
1660 * array elements by user-defined types
1662 * we filter out the built-in types when we dump out the types
1664 * same approach for undefined (shell) types
1667 /* Make sure we are in proper schema */
1668 selectSourceSchema("pg_catalog");
1670 if (g_fout->remoteVersion >= 70300)
1672 appendPQExpBuffer(query, "SELECT tableoid, oid, typname, "
1674 "(select usename from pg_user where typowner = usesysid) as usename, "
1675 "typinput::oid as typinput, "
1676 "typoutput::oid as typoutput, typelem, typrelid, "
1677 "CASE WHEN typrelid = 0 THEN ' '::\"char\" "
1678 "ELSE (SELECT relkind FROM pg_class WHERE oid = typrelid) END as typrelkind, "
1679 "typtype, typisdefined "
1682 else if (g_fout->remoteVersion >= 70100)
1684 appendPQExpBuffer(query, "SELECT tableoid, oid, typname, "
1685 "0::oid as typnamespace, "
1686 "(select usename from pg_user where typowner = usesysid) as usename, "
1687 "typinput::oid as typinput, "
1688 "typoutput::oid as typoutput, typelem, typrelid, "
1689 "CASE WHEN typrelid = 0 THEN ' '::\"char\" "
1690 "ELSE (SELECT relkind FROM pg_class WHERE oid = typrelid) END as typrelkind, "
1691 "typtype, typisdefined "
1696 appendPQExpBuffer(query, "SELECT "
1697 "(SELECT oid FROM pg_class WHERE relname = 'pg_type') AS tableoid, "
1699 "0::oid as typnamespace, "
1700 "(select usename from pg_user where typowner = usesysid) as usename, "
1701 "typinput::oid as typinput, "
1702 "typoutput::oid as typoutput, typelem, typrelid, "
1703 "CASE WHEN typrelid = 0 THEN ' '::\"char\" "
1704 "ELSE (SELECT relkind FROM pg_class WHERE oid = typrelid) END as typrelkind, "
1705 "typtype, typisdefined "
1709 res = PQexec(g_conn, query->data);
1710 check_sql_result(res, g_conn, query->data, PGRES_TUPLES_OK);
1712 ntups = PQntuples(res);
1714 tinfo = (TypeInfo *) malloc(ntups * sizeof(TypeInfo));
1716 i_tableoid = PQfnumber(res, "tableoid");
1717 i_oid = PQfnumber(res, "oid");
1718 i_typname = PQfnumber(res, "typname");
1719 i_typnamespace = PQfnumber(res, "typnamespace");
1720 i_usename = PQfnumber(res, "usename");
1721 i_typinput = PQfnumber(res, "typinput");
1722 i_typoutput = PQfnumber(res, "typoutput");
1723 i_typelem = PQfnumber(res, "typelem");
1724 i_typrelid = PQfnumber(res, "typrelid");
1725 i_typrelkind = PQfnumber(res, "typrelkind");
1726 i_typtype = PQfnumber(res, "typtype");
1727 i_typisdefined = PQfnumber(res, "typisdefined");
1729 for (i = 0; i < ntups; i++)
1734 tinfo[i].dobj.objType = DO_TYPE;
1735 tinfo[i].dobj.catId.tableoid = atooid(PQgetvalue(res, i, i_tableoid));
1736 tinfo[i].dobj.catId.oid = atooid(PQgetvalue(res, i, i_oid));
1737 AssignDumpId(&tinfo[i].dobj);
1738 tinfo[i].dobj.name = strdup(PQgetvalue(res, i, i_typname));
1739 tinfo[i].dobj.namespace = findNamespace(atooid(PQgetvalue(res, i, i_typnamespace)),
1740 tinfo[i].dobj.catId.oid);
1741 tinfo[i].usename = strdup(PQgetvalue(res, i, i_usename));
1742 tinfo[i].typinput = atooid(PQgetvalue(res, i, i_typinput));
1743 typoutput = atooid(PQgetvalue(res, i, i_typoutput));
1744 tinfo[i].typelem = atooid(PQgetvalue(res, i, i_typelem));
1745 tinfo[i].typrelid = atooid(PQgetvalue(res, i, i_typrelid));
1746 tinfo[i].typrelkind = *PQgetvalue(res, i, i_typrelkind);
1747 tinfo[i].typtype = *PQgetvalue(res, i, i_typtype);
1750 * If it's a table's rowtype, use special type code to facilitate
1751 * sorting into the desired order. (We don't want to consider it
1752 * an ordinary type because that would bring the table up into the
1753 * datatype part of the dump order.)
1755 if (OidIsValid(tinfo[i].typrelid) && tinfo[i].typrelkind != 'c')
1756 tinfo[i].dobj.objType = DO_TABLE_TYPE;
1759 * check for user-defined array types, omit system generated ones
1761 if (OidIsValid(tinfo[i].typelem) &&
1762 tinfo[i].dobj.name[0] != '_')
1763 tinfo[i].isArray = true;
1765 tinfo[i].isArray = false;
1767 if (strcmp(PQgetvalue(res, i, i_typisdefined), "t") == 0)
1768 tinfo[i].isDefined = true;
1770 tinfo[i].isDefined = false;
1773 * If it's a domain, fetch info about its constraints, if any
1775 tinfo[i].nDomChecks = 0;
1776 tinfo[i].domChecks = NULL;
1777 if (tinfo[i].typtype == 'd')
1778 getDomainConstraints(&(tinfo[i]));
1781 * Make sure there are dependencies from the type to its input and
1782 * output functions. (We don't worry about typsend, typreceive,
1783 * or typanalyze since those are only valid in 7.4 and later,
1784 * wherein the standard dependency mechanism will pick them up.)
1786 funcInfo = findFuncByOid(tinfo[i].typinput);
1788 addObjectDependency(&tinfo[i].dobj,
1789 funcInfo->dobj.dumpId);
1790 funcInfo = findFuncByOid(typoutput);
1792 addObjectDependency(&tinfo[i].dobj,
1793 funcInfo->dobj.dumpId);
1795 if (strlen(tinfo[i].usename) == 0 && tinfo[i].isDefined)
1796 write_msg(NULL, "WARNING: owner of data type \"%s\" appears to be invalid\n",
1797 tinfo[i].dobj.name);
1804 destroyPQExpBuffer(query);
1811 * read all operators in the system catalogs and return them in the
1812 * OprInfo* structure
1814 * numOprs is set to the number of operators read in
1817 getOperators(int *numOprs)
1822 PQExpBuffer query = createPQExpBuffer();
1832 * find all operators, including builtin operators; we filter out
1833 * system-defined operators at dump-out time.
1836 /* Make sure we are in proper schema */
1837 selectSourceSchema("pg_catalog");
1839 if (g_fout->remoteVersion >= 70300)
1841 appendPQExpBuffer(query, "SELECT tableoid, oid, oprname, "
1843 "(select usename from pg_user where oprowner = usesysid) as usename, "
1844 "oprcode::oid as oprcode "
1845 "FROM pg_operator");
1847 else if (g_fout->remoteVersion >= 70100)
1849 appendPQExpBuffer(query, "SELECT tableoid, oid, oprname, "
1850 "0::oid as oprnamespace, "
1851 "(select usename from pg_user where oprowner = usesysid) as usename, "
1852 "oprcode::oid as oprcode "
1853 "FROM pg_operator");
1857 appendPQExpBuffer(query, "SELECT "
1858 "(SELECT oid FROM pg_class WHERE relname = 'pg_operator') AS tableoid, "
1860 "0::oid as oprnamespace, "
1861 "(select usename from pg_user where oprowner = usesysid) as usename, "
1862 "oprcode::oid as oprcode "
1863 "FROM pg_operator");
1866 res = PQexec(g_conn, query->data);
1867 check_sql_result(res, g_conn, query->data, PGRES_TUPLES_OK);
1869 ntups = PQntuples(res);
1872 oprinfo = (OprInfo *) malloc(ntups * sizeof(OprInfo));
1874 i_tableoid = PQfnumber(res, "tableoid");
1875 i_oid = PQfnumber(res, "oid");
1876 i_oprname = PQfnumber(res, "oprname");
1877 i_oprnamespace = PQfnumber(res, "oprnamespace");
1878 i_usename = PQfnumber(res, "usename");
1879 i_oprcode = PQfnumber(res, "oprcode");
1881 for (i = 0; i < ntups; i++)
1883 oprinfo[i].dobj.objType = DO_OPERATOR;
1884 oprinfo[i].dobj.catId.tableoid = atooid(PQgetvalue(res, i, i_tableoid));
1885 oprinfo[i].dobj.catId.oid = atooid(PQgetvalue(res, i, i_oid));
1886 AssignDumpId(&oprinfo[i].dobj);
1887 oprinfo[i].dobj.name = strdup(PQgetvalue(res, i, i_oprname));
1888 oprinfo[i].dobj.namespace = findNamespace(atooid(PQgetvalue(res, i, i_oprnamespace)),
1889 oprinfo[i].dobj.catId.oid);
1890 oprinfo[i].usename = strdup(PQgetvalue(res, i, i_usename));
1891 oprinfo[i].oprcode = atooid(PQgetvalue(res, i, i_oprcode));
1893 if (strlen(oprinfo[i].usename) == 0)
1894 write_msg(NULL, "WARNING: owner of operator \"%s\" appears to be invalid\n",
1895 oprinfo[i].dobj.name);
1900 destroyPQExpBuffer(query);
1907 * read all conversions in the system catalogs and return them in the
1908 * ConvInfo* structure
1910 * numConversions is set to the number of conversions read in
1913 getConversions(int *numConversions)
1918 PQExpBuffer query = createPQExpBuffer();
1926 /* Conversions didn't exist pre-7.3 */
1927 if (g_fout->remoteVersion < 70300)
1929 *numConversions = 0;
1934 * find all conversions, including builtin conversions; we filter out
1935 * system-defined conversions at dump-out time.
1938 /* Make sure we are in proper schema */
1939 selectSourceSchema("pg_catalog");
1941 appendPQExpBuffer(query, "SELECT tableoid, oid, conname, "
1943 "(select usename from pg_user where conowner = usesysid) as usename "
1944 "FROM pg_conversion");
1946 res = PQexec(g_conn, query->data);
1947 check_sql_result(res, g_conn, query->data, PGRES_TUPLES_OK);
1949 ntups = PQntuples(res);
1950 *numConversions = ntups;
1952 convinfo = (ConvInfo *) malloc(ntups * sizeof(ConvInfo));
1954 i_tableoid = PQfnumber(res, "tableoid");
1955 i_oid = PQfnumber(res, "oid");
1956 i_conname = PQfnumber(res, "conname");
1957 i_connamespace = PQfnumber(res, "connamespace");
1958 i_usename = PQfnumber(res, "usename");
1960 for (i = 0; i < ntups; i++)
1962 convinfo[i].dobj.objType = DO_CONVERSION;
1963 convinfo[i].dobj.catId.tableoid = atooid(PQgetvalue(res, i, i_tableoid));
1964 convinfo[i].dobj.catId.oid = atooid(PQgetvalue(res, i, i_oid));
1965 AssignDumpId(&convinfo[i].dobj);
1966 convinfo[i].dobj.name = strdup(PQgetvalue(res, i, i_conname));
1967 convinfo[i].dobj.namespace = findNamespace(atooid(PQgetvalue(res, i, i_connamespace)),
1968 convinfo[i].dobj.catId.oid);
1969 convinfo[i].usename = strdup(PQgetvalue(res, i, i_usename));
1974 destroyPQExpBuffer(query);
1981 * read all opclasses in the system catalogs and return them in the
1982 * OpclassInfo* structure
1984 * numOpclasses is set to the number of opclasses read in
1987 getOpclasses(int *numOpclasses)
1992 PQExpBuffer query = createPQExpBuffer();
1993 OpclassInfo *opcinfo;
2001 * find all opclasses, including builtin opclasses; we filter out
2002 * system-defined opclasses at dump-out time.
2005 /* Make sure we are in proper schema */
2006 selectSourceSchema("pg_catalog");
2008 if (g_fout->remoteVersion >= 70300)
2010 appendPQExpBuffer(query, "SELECT tableoid, oid, opcname, "
2012 "(select usename from pg_user where opcowner = usesysid) as usename "
2015 else if (g_fout->remoteVersion >= 70100)
2017 appendPQExpBuffer(query, "SELECT tableoid, oid, opcname, "
2018 "0::oid as opcnamespace, "
2019 "''::name as usename "
2024 appendPQExpBuffer(query, "SELECT "
2025 "(SELECT oid FROM pg_class WHERE relname = 'pg_opclass') AS tableoid, "
2027 "0::oid as opcnamespace, "
2028 "''::name as usename "
2032 res = PQexec(g_conn, query->data);
2033 check_sql_result(res, g_conn, query->data, PGRES_TUPLES_OK);
2035 ntups = PQntuples(res);
2036 *numOpclasses = ntups;
2038 opcinfo = (OpclassInfo *) malloc(ntups * sizeof(OpclassInfo));
2040 i_tableoid = PQfnumber(res, "tableoid");
2041 i_oid = PQfnumber(res, "oid");
2042 i_opcname = PQfnumber(res, "opcname");
2043 i_opcnamespace = PQfnumber(res, "opcnamespace");
2044 i_usename = PQfnumber(res, "usename");
2046 for (i = 0; i < ntups; i++)
2048 opcinfo[i].dobj.objType = DO_OPCLASS;
2049 opcinfo[i].dobj.catId.tableoid = atooid(PQgetvalue(res, i, i_tableoid));
2050 opcinfo[i].dobj.catId.oid = atooid(PQgetvalue(res, i, i_oid));
2051 AssignDumpId(&opcinfo[i].dobj);
2052 opcinfo[i].dobj.name = strdup(PQgetvalue(res, i, i_opcname));
2053 opcinfo[i].dobj.namespace = findNamespace(atooid(PQgetvalue(res, i, i_opcnamespace)),
2054 opcinfo[i].dobj.catId.oid);
2055 opcinfo[i].usename = strdup(PQgetvalue(res, i, i_usename));
2057 if (g_fout->remoteVersion >= 70300)
2059 if (strlen(opcinfo[i].usename) == 0)
2060 write_msg(NULL, "WARNING: owner of operator class \"%s\" appears to be invalid\n",
2061 opcinfo[i].dobj.name);
2067 destroyPQExpBuffer(query);
2074 * read all the user-defined aggregates in the system catalogs and
2075 * return them in the AggInfo* structure
2077 * numAggs is set to the number of aggregates read in
2080 getAggregates(int *numAggs)
2085 PQExpBuffer query = createPQExpBuffer();
2095 /* Make sure we are in proper schema */
2096 selectSourceSchema("pg_catalog");
2098 /* find all user-defined aggregates */
2100 if (g_fout->remoteVersion >= 70300)
2102 appendPQExpBuffer(query, "SELECT tableoid, oid, proname as aggname, "
2103 "pronamespace as aggnamespace, "
2104 "proargtypes[0] as aggbasetype, "
2105 "(select usename from pg_user where proowner = usesysid) as usename, "
2109 "AND pronamespace != "
2110 "(select oid from pg_namespace where nspname = 'pg_catalog')");
2112 else if (g_fout->remoteVersion >= 70100)
2114 appendPQExpBuffer(query, "SELECT tableoid, oid, aggname, "
2115 "0::oid as aggnamespace, "
2117 "(select usename from pg_user where aggowner = usesysid) as usename, "
2119 "FROM pg_aggregate "
2120 "where oid > '%u'::oid",
2121 g_last_builtin_oid);
2125 appendPQExpBuffer(query, "SELECT "
2126 "(SELECT oid FROM pg_class WHERE relname = 'pg_aggregate') AS tableoid, "
2128 "0::oid as aggnamespace, "
2130 "(select usename from pg_user where aggowner = usesysid) as usename, "
2132 "FROM pg_aggregate "
2133 "where oid > '%u'::oid",
2134 g_last_builtin_oid);
2137 res = PQexec(g_conn, query->data);
2138 check_sql_result(res, g_conn, query->data, PGRES_TUPLES_OK);
2140 ntups = PQntuples(res);
2143 agginfo = (AggInfo *) malloc(ntups * sizeof(AggInfo));
2145 i_tableoid = PQfnumber(res, "tableoid");
2146 i_oid = PQfnumber(res, "oid");
2147 i_aggname = PQfnumber(res, "aggname");
2148 i_aggnamespace = PQfnumber(res, "aggnamespace");
2149 i_aggbasetype = PQfnumber(res, "aggbasetype");
2150 i_usename = PQfnumber(res, "usename");
2151 i_aggacl = PQfnumber(res, "aggacl");
2153 for (i = 0; i < ntups; i++)
2155 agginfo[i].aggfn.dobj.objType = DO_AGG;
2156 agginfo[i].aggfn.dobj.catId.tableoid = atooid(PQgetvalue(res, i, i_tableoid));
2157 agginfo[i].aggfn.dobj.catId.oid = atooid(PQgetvalue(res, i, i_oid));
2158 AssignDumpId(&agginfo[i].aggfn.dobj);
2159 agginfo[i].aggfn.dobj.name = strdup(PQgetvalue(res, i, i_aggname));
2160 agginfo[i].aggfn.dobj.namespace = findNamespace(atooid(PQgetvalue(res, i, i_aggnamespace)),
2161 agginfo[i].aggfn.dobj.catId.oid);
2162 agginfo[i].aggfn.usename = strdup(PQgetvalue(res, i, i_usename));
2163 if (strlen(agginfo[i].aggfn.usename) == 0)
2164 write_msg(NULL, "WARNING: owner of aggregate function \"%s\" appears to be invalid\n",
2165 agginfo[i].aggfn.dobj.name);
2166 agginfo[i].aggfn.lang = InvalidOid; /* not currently
2168 agginfo[i].aggfn.nargs = 1;
2169 agginfo[i].aggfn.argtypes = (Oid *) malloc(sizeof(Oid));
2170 agginfo[i].aggfn.argtypes[0] = atooid(PQgetvalue(res, i, i_aggbasetype));
2171 agginfo[i].aggfn.prorettype = InvalidOid; /* not saved */
2172 agginfo[i].aggfn.proacl = strdup(PQgetvalue(res, i, i_aggacl));
2173 agginfo[i].anybasetype = false; /* computed when it's dumped */
2174 agginfo[i].fmtbasetype = NULL; /* computed when it's dumped */
2179 destroyPQExpBuffer(query);
2186 * read all the user-defined functions in the system catalogs and
2187 * return them in the FuncInfo* structure
2189 * numFuncs is set to the number of functions read in
2192 getFuncs(int *numFuncs)
2197 PQExpBuffer query = createPQExpBuffer();
2210 /* Make sure we are in proper schema */
2211 selectSourceSchema("pg_catalog");
2213 /* find all user-defined funcs */
2215 if (g_fout->remoteVersion >= 70300)
2217 appendPQExpBuffer(query,
2218 "SELECT tableoid, oid, proname, prolang, "
2219 "pronargs, proargtypes, prorettype, proacl, "
2221 "(select usename from pg_user where proowner = usesysid) as usename "
2223 "WHERE NOT proisagg "
2224 "AND pronamespace != "
2225 "(select oid from pg_namespace where nspname = 'pg_catalog')");
2227 else if (g_fout->remoteVersion >= 70100)
2229 appendPQExpBuffer(query,
2230 "SELECT tableoid, oid, proname, prolang, "
2231 "pronargs, proargtypes, prorettype, "
2232 "'{=X}' as proacl, "
2233 "0::oid as pronamespace, "
2234 "(select usename from pg_user where proowner = usesysid) as usename "
2236 "where pg_proc.oid > '%u'::oid",
2237 g_last_builtin_oid);
2241 appendPQExpBuffer(query,
2243 "(SELECT oid FROM pg_class WHERE relname = 'pg_proc') AS tableoid, "
2244 "oid, proname, prolang, "
2245 "pronargs, proargtypes, prorettype, "
2246 "'{=X}' as proacl, "
2247 "0::oid as pronamespace, "
2248 "(select usename from pg_user where proowner = usesysid) as usename "
2250 "where pg_proc.oid > '%u'::oid",
2251 g_last_builtin_oid);
2254 res = PQexec(g_conn, query->data);
2255 check_sql_result(res, g_conn, query->data, PGRES_TUPLES_OK);
2257 ntups = PQntuples(res);
2261 finfo = (FuncInfo *) calloc(ntups, sizeof(FuncInfo));
2263 i_tableoid = PQfnumber(res, "tableoid");
2264 i_oid = PQfnumber(res, "oid");
2265 i_proname = PQfnumber(res, "proname");
2266 i_pronamespace = PQfnumber(res, "pronamespace");
2267 i_usename = PQfnumber(res, "usename");
2268 i_prolang = PQfnumber(res, "prolang");
2269 i_pronargs = PQfnumber(res, "pronargs");
2270 i_proargtypes = PQfnumber(res, "proargtypes");
2271 i_prorettype = PQfnumber(res, "prorettype");
2272 i_proacl = PQfnumber(res, "proacl");
2274 for (i = 0; i < ntups; i++)
2276 finfo[i].dobj.objType = DO_FUNC;
2277 finfo[i].dobj.catId.tableoid = atooid(PQgetvalue(res, i, i_tableoid));
2278 finfo[i].dobj.catId.oid = atooid(PQgetvalue(res, i, i_oid));
2279 AssignDumpId(&finfo[i].dobj);
2280 finfo[i].dobj.name = strdup(PQgetvalue(res, i, i_proname));
2281 finfo[i].dobj.namespace = findNamespace(atooid(PQgetvalue(res, i, i_pronamespace)),
2282 finfo[i].dobj.catId.oid);
2283 finfo[i].usename = strdup(PQgetvalue(res, i, i_usename));
2284 finfo[i].lang = atooid(PQgetvalue(res, i, i_prolang));
2285 finfo[i].prorettype = atooid(PQgetvalue(res, i, i_prorettype));
2286 finfo[i].proacl = strdup(PQgetvalue(res, i, i_proacl));
2287 finfo[i].nargs = atoi(PQgetvalue(res, i, i_pronargs));
2288 if (finfo[i].nargs == 0)
2289 finfo[i].argtypes = NULL;
2292 finfo[i].argtypes = (Oid *) malloc(finfo[i].nargs * sizeof(Oid));
2293 parseOidArray(PQgetvalue(res, i, i_proargtypes),
2294 finfo[i].argtypes, finfo[i].nargs);
2297 if (strlen(finfo[i].usename) == 0)
2298 write_msg(NULL, "WARNING: owner of function \"%s\" appears to be invalid\n",
2299 finfo[i].dobj.name);
2304 destroyPQExpBuffer(query);
2311 * read all the user-defined tables (no indexes, no catalogs)
2312 * in the system catalogs return them in the TableInfo* structure
2314 * numTables is set to the number of tables read in
2317 getTables(int *numTables)
2322 PQExpBuffer query = createPQExpBuffer();
2323 PQExpBuffer delqry = createPQExpBuffer();
2324 PQExpBuffer lockquery = createPQExpBuffer();
2340 int i_reltablespace;
2342 /* Make sure we are in proper schema */
2343 selectSourceSchema("pg_catalog");
2346 * Find all the tables (including views and sequences).
2348 * We include system catalogs, so that we can work if a user table is
2349 * defined to inherit from a system catalog (pretty weird, but...)
2351 * We ignore tables that are not type 'r' (ordinary relation) or 'S'
2352 * (sequence) or 'v' (view).
2354 * Note: in this phase we should collect only a minimal amount of
2355 * information about each table, basically just enough to decide if it
2356 * is interesting. We must fetch all tables in this phase because
2357 * otherwise we cannot correctly identify inherited columns, serial
2361 if (g_fout->remoteVersion >= 80000)
2364 * Left join to pick up dependency info linking sequences to their
2365 * serial column, if any
2367 appendPQExpBuffer(query,
2368 "SELECT c.tableoid, c.oid, relname, "
2369 "relacl, relkind, relnamespace, "
2370 "(select usename from pg_user where relowner = usesysid) as usename, "
2371 "relchecks, reltriggers, "
2372 "relhasindex, relhasrules, relhasoids, "
2373 "d.refobjid as owning_tab, "
2374 "d.refobjsubid as owning_col, "
2375 "(SELECT spcname FROM pg_tablespace t WHERE t.oid = c.reltablespace) AS reltablespace "
2377 "left join pg_depend d on "
2378 "(c.relkind = '%c' and "
2379 "d.classid = c.tableoid and d.objid = c.oid and "
2380 "d.objsubid = 0 and "
2381 "d.refclassid = c.tableoid and d.deptype = 'i') "
2382 "where relkind in ('%c', '%c', '%c') "
2385 RELKIND_RELATION, RELKIND_SEQUENCE, RELKIND_VIEW);
2387 else if (g_fout->remoteVersion >= 70300)
2390 * Left join to pick up dependency info linking sequences to their
2391 * serial column, if any
2393 appendPQExpBuffer(query,
2394 "SELECT c.tableoid, c.oid, relname, "
2395 "relacl, relkind, relnamespace, "
2396 "(select usename from pg_user where relowner = usesysid) as usename, "
2397 "relchecks, reltriggers, "
2398 "relhasindex, relhasrules, relhasoids, "
2399 "d.refobjid as owning_tab, "
2400 "d.refobjsubid as owning_col, "
2401 "NULL as reltablespace "
2403 "left join pg_depend d on "
2404 "(c.relkind = '%c' and "
2405 "d.classid = c.tableoid and d.objid = c.oid and "
2406 "d.objsubid = 0 and "
2407 "d.refclassid = c.tableoid and d.deptype = 'i') "
2408 "where relkind in ('%c', '%c', '%c') "
2411 RELKIND_RELATION, RELKIND_SEQUENCE, RELKIND_VIEW);
2413 else if (g_fout->remoteVersion >= 70200)
2415 appendPQExpBuffer(query,
2416 "SELECT tableoid, oid, relname, relacl, relkind, "
2417 "0::oid as relnamespace, "
2418 "(select usename from pg_user where relowner = usesysid) as usename, "
2419 "relchecks, reltriggers, "
2420 "relhasindex, relhasrules, relhasoids, "
2421 "NULL::oid as owning_tab, "
2422 "NULL::int4 as owning_col, "
2423 "NULL as reltablespace "
2425 "where relkind in ('%c', '%c', '%c') "
2427 RELKIND_RELATION, RELKIND_SEQUENCE, RELKIND_VIEW);
2429 else if (g_fout->remoteVersion >= 70100)
2431 /* all tables have oids in 7.1 */
2432 appendPQExpBuffer(query,
2433 "SELECT tableoid, oid, relname, relacl, relkind, "
2434 "0::oid as relnamespace, "
2435 "(select usename from pg_user where relowner = usesysid) as usename, "
2436 "relchecks, reltriggers, "
2437 "relhasindex, relhasrules, "
2438 "'t'::bool as relhasoids, "
2439 "NULL::oid as owning_tab, "
2440 "NULL::int4 as owning_col, "
2441 "NULL as reltablespace "
2443 "where relkind in ('%c', '%c', '%c') "
2445 RELKIND_RELATION, RELKIND_SEQUENCE, RELKIND_VIEW);
2450 * Before 7.1, view relkind was not set to 'v', so we must check
2451 * if we have a view by looking for a rule in pg_rewrite.
2453 appendPQExpBuffer(query,
2455 "(SELECT oid FROM pg_class WHERE relname = 'pg_class') AS tableoid, "
2456 "oid, relname, relacl, "
2457 "CASE WHEN relhasrules and relkind = 'r' "
2458 " and EXISTS(SELECT rulename FROM pg_rewrite r WHERE "
2459 " r.ev_class = c.oid AND r.ev_type = '1') "
2460 "THEN '%c'::\"char\" "
2461 "ELSE relkind END AS relkind,"
2462 "0::oid as relnamespace, "
2463 "(select usename from pg_user where relowner = usesysid) as usename, "
2464 "relchecks, reltriggers, "
2465 "relhasindex, relhasrules, "
2466 "'t'::bool as relhasoids, "
2467 "NULL::oid as owning_tab, "
2468 "NULL::int4 as owning_col, "
2469 "NULL as reltablespace "
2471 "where relkind in ('%c', '%c') "
2474 RELKIND_RELATION, RELKIND_SEQUENCE);
2477 res = PQexec(g_conn, query->data);
2478 check_sql_result(res, g_conn, query->data, PGRES_TUPLES_OK);
2480 ntups = PQntuples(res);
2485 * Extract data from result and lock dumpable tables. We do the
2486 * locking before anything else, to minimize the window wherein a
2487 * table could disappear under us.
2489 * Note that we have to save info about all tables here, even when
2490 * dumping only one, because we don't yet know which tables might be
2491 * inheritance ancestors of the target table.
2493 tblinfo = (TableInfo *) calloc(ntups, sizeof(TableInfo));
2495 i_reltableoid = PQfnumber(res, "tableoid");
2496 i_reloid = PQfnumber(res, "oid");
2497 i_relname = PQfnumber(res, "relname");
2498 i_relnamespace = PQfnumber(res, "relnamespace");
2499 i_relacl = PQfnumber(res, "relacl");
2500 i_relkind = PQfnumber(res, "relkind");
2501 i_usename = PQfnumber(res, "usename");
2502 i_relchecks = PQfnumber(res, "relchecks");
2503 i_reltriggers = PQfnumber(res, "reltriggers");
2504 i_relhasindex = PQfnumber(res, "relhasindex");
2505 i_relhasrules = PQfnumber(res, "relhasrules");
2506 i_relhasoids = PQfnumber(res, "relhasoids");
2507 i_owning_tab = PQfnumber(res, "owning_tab");
2508 i_owning_col = PQfnumber(res, "owning_col");
2509 i_reltablespace = PQfnumber(res, "reltablespace");
2511 for (i = 0; i < ntups; i++)
2513 tblinfo[i].dobj.objType = DO_TABLE;
2514 tblinfo[i].dobj.catId.tableoid = atooid(PQgetvalue(res, i, i_reltableoid));
2515 tblinfo[i].dobj.catId.oid = atooid(PQgetvalue(res, i, i_reloid));
2516 AssignDumpId(&tblinfo[i].dobj);
2517 tblinfo[i].dobj.name = strdup(PQgetvalue(res, i, i_relname));
2518 tblinfo[i].dobj.namespace = findNamespace(atooid(PQgetvalue(res, i, i_relnamespace)),
2519 tblinfo[i].dobj.catId.oid);
2520 tblinfo[i].usename = strdup(PQgetvalue(res, i, i_usename));
2521 tblinfo[i].relacl = strdup(PQgetvalue(res, i, i_relacl));
2522 tblinfo[i].relkind = *(PQgetvalue(res, i, i_relkind));
2523 tblinfo[i].hasindex = (strcmp(PQgetvalue(res, i, i_relhasindex), "t") == 0);
2524 tblinfo[i].hasrules = (strcmp(PQgetvalue(res, i, i_relhasrules), "t") == 0);
2525 tblinfo[i].hasoids = (strcmp(PQgetvalue(res, i, i_relhasoids), "t") == 0);
2526 tblinfo[i].ncheck = atoi(PQgetvalue(res, i, i_relchecks));
2527 tblinfo[i].ntrig = atoi(PQgetvalue(res, i, i_reltriggers));
2528 if (PQgetisnull(res, i, i_owning_tab))
2530 tblinfo[i].owning_tab = InvalidOid;
2531 tblinfo[i].owning_col = 0;
2535 tblinfo[i].owning_tab = atooid(PQgetvalue(res, i, i_owning_tab));
2536 tblinfo[i].owning_col = atoi(PQgetvalue(res, i, i_owning_col));
2538 tblinfo[i].reltablespace = strdup(PQgetvalue(res, i, i_reltablespace));
2540 /* other fields were zeroed above */
2543 * Decide whether we want to dump this table. Sequences owned by
2544 * serial columns are never dumpable on their own; we will
2545 * transpose their owning table's dump flag to them below.
2547 if (OidIsValid(tblinfo[i].owning_tab))
2548 tblinfo[i].dump = false;
2550 selectDumpableTable(&tblinfo[i]);
2551 tblinfo[i].interesting = tblinfo[i].dump;
2554 * Read-lock target tables to make sure they aren't DROPPED or
2555 * altered in schema before we get around to dumping them.
2557 * Note that we don't explicitly lock parents of the target tables;
2558 * we assume our lock on the child is enough to prevent schema
2559 * alterations to parent tables.
2561 * NOTE: it'd be kinda nice to lock views and sequences too, not only
2562 * plain tables, but the backend doesn't presently allow that.
2564 if (tblinfo[i].dump && tblinfo[i].relkind == RELKIND_RELATION)
2566 resetPQExpBuffer(lockquery);
2567 appendPQExpBuffer(lockquery,
2568 "LOCK TABLE %s IN ACCESS SHARE MODE",
2569 fmtQualifiedId(tblinfo[i].dobj.namespace->dobj.name,
2570 tblinfo[i].dobj.name));
2571 do_sql_command(g_conn, lockquery->data);
2574 /* Emit notice if join for owner failed */
2575 if (strlen(tblinfo[i].usename) == 0)
2576 write_msg(NULL, "WARNING: owner of table \"%s\" appears to be invalid\n",
2577 tblinfo[i].dobj.name);
2581 * If the user is attempting to dump a specific table, check to ensure
2582 * that the specified table actually exists. (This is a bit
2583 * simplistic since we don't fully check the combination of -n and -t
2586 if (selectTableName)
2588 for (i = 0; i < ntups; i++)
2589 if (strcmp(tblinfo[i].dobj.name, selectTableName) == 0)
2592 /* Didn't find a match */
2595 write_msg(NULL, "specified table \"%s\" does not exist\n",
2602 destroyPQExpBuffer(query);
2603 destroyPQExpBuffer(delqry);
2604 destroyPQExpBuffer(lockquery);
2611 * read all the inheritance information
2612 * from the system catalogs return them in the InhInfo* structure
2614 * numInherits is set to the number of pairs read in
2617 getInherits(int *numInherits)
2622 PQExpBuffer query = createPQExpBuffer();
2628 /* Make sure we are in proper schema */
2629 selectSourceSchema("pg_catalog");
2631 /* find all the inheritance information */
2633 appendPQExpBuffer(query, "SELECT inhrelid, inhparent from pg_inherits");
2635 res = PQexec(g_conn, query->data);
2636 check_sql_result(res, g_conn, query->data, PGRES_TUPLES_OK);
2638 ntups = PQntuples(res);
2640 *numInherits = ntups;
2642 inhinfo = (InhInfo *) malloc(ntups * sizeof(InhInfo));
2644 i_inhrelid = PQfnumber(res, "inhrelid");
2645 i_inhparent = PQfnumber(res, "inhparent");
2647 for (i = 0; i < ntups; i++)
2649 inhinfo[i].inhrelid = atooid(PQgetvalue(res, i, i_inhrelid));
2650 inhinfo[i].inhparent = atooid(PQgetvalue(res, i, i_inhparent));
2655 destroyPQExpBuffer(query);
2662 * get information about every index on a dumpable table
2664 * Note: index data is not returned directly to the caller, but it
2665 * does get entered into the DumpableObject tables.
2668 getIndexes(TableInfo tblinfo[], int numTables)
2672 PQExpBuffer query = createPQExpBuffer();
2675 ConstraintInfo *constrinfo;
2690 for (i = 0; i < numTables; i++)
2692 TableInfo *tbinfo = &tblinfo[i];
2694 /* Only plain tables have indexes */
2695 if (tbinfo->relkind != RELKIND_RELATION || !tbinfo->hasindex)
2702 write_msg(NULL, "reading indexes for table \"%s\"\n",
2705 /* Make sure we are in proper schema so indexdef is right */
2706 selectSourceSchema(tbinfo->dobj.namespace->dobj.name);
2709 * The point of the messy-looking outer join is to find a
2710 * constraint that is related by an internal dependency link to
2711 * the index. If we find one, create a CONSTRAINT entry linked to
2712 * the INDEX entry. We assume an index won't have more than one
2713 * internal dependency.
2715 resetPQExpBuffer(query);
2716 if (g_fout->remoteVersion >= 80000)
2718 appendPQExpBuffer(query,
2719 "SELECT t.tableoid, t.oid, "
2720 "t.relname as indexname, "
2721 "pg_catalog.pg_get_indexdef(i.indexrelid) as indexdef, "
2722 "t.relnatts as indnkeys, "
2723 "i.indkey, i.indisclustered, "
2724 "c.contype, c.conname, "
2725 "c.tableoid as contableoid, "
2727 "(SELECT spcname FROM pg_catalog.pg_tablespace s WHERE s.oid = t.reltablespace) as tablespace "
2728 "FROM pg_catalog.pg_index i "
2729 "JOIN pg_catalog.pg_class t ON (t.oid = i.indexrelid) "
2730 "LEFT JOIN pg_catalog.pg_depend d "
2731 "ON (d.classid = t.tableoid "
2732 "AND d.objid = t.oid "
2733 "AND d.deptype = 'i') "
2734 "LEFT JOIN pg_catalog.pg_constraint c "
2735 "ON (d.refclassid = c.tableoid "
2736 "AND d.refobjid = c.oid) "
2737 "WHERE i.indrelid = '%u'::pg_catalog.oid "
2738 "ORDER BY indexname",
2739 tbinfo->dobj.catId.oid);
2741 else if (g_fout->remoteVersion >= 70300)
2743 appendPQExpBuffer(query,
2744 "SELECT t.tableoid, t.oid, "
2745 "t.relname as indexname, "
2746 "pg_catalog.pg_get_indexdef(i.indexrelid) as indexdef, "
2747 "t.relnatts as indnkeys, "
2748 "i.indkey, i.indisclustered, "
2749 "c.contype, c.conname, "
2750 "c.tableoid as contableoid, "
2752 "NULL as tablespace "
2753 "FROM pg_catalog.pg_index i "
2754 "JOIN pg_catalog.pg_class t ON (t.oid = i.indexrelid) "
2755 "LEFT JOIN pg_catalog.pg_depend d "
2756 "ON (d.classid = t.tableoid "
2757 "AND d.objid = t.oid "
2758 "AND d.deptype = 'i') "
2759 "LEFT JOIN pg_catalog.pg_constraint c "
2760 "ON (d.refclassid = c.tableoid "
2761 "AND d.refobjid = c.oid) "
2762 "WHERE i.indrelid = '%u'::pg_catalog.oid "
2763 "ORDER BY indexname",
2764 tbinfo->dobj.catId.oid);
2766 else if (g_fout->remoteVersion >= 70100)
2768 appendPQExpBuffer(query,
2769 "SELECT t.tableoid, t.oid, "
2770 "t.relname as indexname, "
2771 "pg_get_indexdef(i.indexrelid) as indexdef, "
2772 "t.relnatts as indnkeys, "
2773 "i.indkey, false as indisclustered, "
2774 "CASE WHEN i.indisprimary THEN 'p'::char "
2775 "ELSE '0'::char END as contype, "
2776 "t.relname as conname, "
2777 "0::oid as contableoid, "
2779 "NULL as tablespace "
2780 "FROM pg_index i, pg_class t "
2781 "WHERE t.oid = i.indexrelid "
2782 "AND i.indrelid = '%u'::oid "
2783 "ORDER BY indexname",
2784 tbinfo->dobj.catId.oid);
2788 appendPQExpBuffer(query,
2790 "(SELECT oid FROM pg_class WHERE relname = 'pg_class') AS tableoid, "
2792 "t.relname as indexname, "
2793 "pg_get_indexdef(i.indexrelid) as indexdef, "
2794 "t.relnatts as indnkeys, "
2795 "i.indkey, false as indisclustered, "
2796 "CASE WHEN i.indisprimary THEN 'p'::char "
2797 "ELSE '0'::char END as contype, "
2798 "t.relname as conname, "
2799 "0::oid as contableoid, "
2801 "NULL as tablespace "
2802 "FROM pg_index i, pg_class t "
2803 "WHERE t.oid = i.indexrelid "
2804 "AND i.indrelid = '%u'::oid "
2805 "ORDER BY indexname",
2806 tbinfo->dobj.catId.oid);
2809 res = PQexec(g_conn, query->data);
2810 check_sql_result(res, g_conn, query->data, PGRES_TUPLES_OK);
2812 ntups = PQntuples(res);
2814 i_tableoid = PQfnumber(res, "tableoid");
2815 i_oid = PQfnumber(res, "oid");
2816 i_indexname = PQfnumber(res, "indexname");
2817 i_indexdef = PQfnumber(res, "indexdef");
2818 i_indnkeys = PQfnumber(res, "indnkeys");
2819 i_indkey = PQfnumber(res, "indkey");
2820 i_indisclustered = PQfnumber(res, "indisclustered");
2821 i_contype = PQfnumber(res, "contype");
2822 i_conname = PQfnumber(res, "conname");
2823 i_contableoid = PQfnumber(res, "contableoid");
2824 i_conoid = PQfnumber(res, "conoid");
2825 i_tablespace = PQfnumber(res, "tablespace");
2827 indxinfo = (IndxInfo *) malloc(ntups * sizeof(IndxInfo));
2828 constrinfo = (ConstraintInfo *) malloc(ntups * sizeof(ConstraintInfo));
2830 for (j = 0; j < ntups; j++)
2834 indxinfo[j].dobj.objType = DO_INDEX;
2835 indxinfo[j].dobj.catId.tableoid = atooid(PQgetvalue(res, j, i_tableoid));
2836 indxinfo[j].dobj.catId.oid = atooid(PQgetvalue(res, j, i_oid));
2837 AssignDumpId(&indxinfo[j].dobj);
2838 indxinfo[j].dobj.name = strdup(PQgetvalue(res, j, i_indexname));
2839 indxinfo[j].dobj.namespace = tbinfo->dobj.namespace;
2840 indxinfo[j].indextable = tbinfo;
2841 indxinfo[j].indexdef = strdup(PQgetvalue(res, j, i_indexdef));
2842 indxinfo[j].indnkeys = atoi(PQgetvalue(res, j, i_indnkeys));
2843 indxinfo[j].tablespace = strdup(PQgetvalue(res, j, i_tablespace));
2846 * In pre-7.4 releases, indkeys may contain more entries than
2847 * indnkeys says (since indnkeys will be 1 for a functional
2848 * index). We don't actually care about this case since we
2849 * don't examine indkeys except for indexes associated with
2850 * PRIMARY and UNIQUE constraints, which are never functional
2851 * indexes. But we have to allocate enough space to keep
2852 * parseOidArray from complaining.
2854 indxinfo[j].indkeys = (Oid *) malloc(INDEX_MAX_KEYS * sizeof(Oid));
2855 parseOidArray(PQgetvalue(res, j, i_indkey),
2856 indxinfo[j].indkeys, INDEX_MAX_KEYS);
2857 indxinfo[j].indisclustered = (PQgetvalue(res, j, i_indisclustered)[0] == 't');
2858 contype = *(PQgetvalue(res, j, i_contype));
2860 if (contype == 'p' || contype == 'u')
2863 * If we found a constraint matching the index, create an
2866 * In a pre-7.3 database, we take this path iff the index was
2867 * marked indisprimary.
2869 constrinfo[j].dobj.objType = DO_CONSTRAINT;
2870 constrinfo[j].dobj.catId.tableoid = atooid(PQgetvalue(res, j, i_contableoid));
2871 constrinfo[j].dobj.catId.oid = atooid(PQgetvalue(res, j, i_conoid));
2872 AssignDumpId(&constrinfo[j].dobj);
2873 constrinfo[j].dobj.name = strdup(PQgetvalue(res, j, i_conname));
2874 constrinfo[j].dobj.namespace = tbinfo->dobj.namespace;
2875 constrinfo[j].contable = tbinfo;
2876 constrinfo[j].condomain = NULL;
2877 constrinfo[j].contype = contype;
2878 constrinfo[j].condef = NULL;
2879 constrinfo[j].conindex = indxinfo[j].dobj.dumpId;
2880 constrinfo[j].coninherited = false;
2881 constrinfo[j].separate = true;
2883 indxinfo[j].indexconstraint = constrinfo[j].dobj.dumpId;
2885 /* If pre-7.3 DB, better make sure table comes first */
2886 addObjectDependency(&constrinfo[j].dobj,
2887 tbinfo->dobj.dumpId);
2891 /* Plain secondary index */
2892 indxinfo[j].indexconstraint = 0;
2899 destroyPQExpBuffer(query);
2905 * Get info about constraints on dumpable tables.
2907 * Currently handles foreign keys only.
2908 * Unique and primary key constraints are handled with indexes,
2909 * while check constraints are processed in getTableAttrs().
2912 getConstraints(TableInfo tblinfo[], int numTables)
2916 ConstraintInfo *constrinfo;
2925 /* pg_constraint was created in 7.3, so nothing to do if older */
2926 if (g_fout->remoteVersion < 70300)
2929 query = createPQExpBuffer();
2931 for (i = 0; i < numTables; i++)
2933 TableInfo *tbinfo = &tblinfo[i];
2935 if (tbinfo->ntrig == 0 || !tbinfo->dump)
2939 write_msg(NULL, "reading foreign key constraints for table \"%s\"\n",
2943 * select table schema to ensure constraint expr is qualified if
2946 selectSourceSchema(tbinfo->dobj.namespace->dobj.name);
2948 resetPQExpBuffer(query);
2949 appendPQExpBuffer(query,
2950 "SELECT tableoid, oid, conname, "
2951 "pg_catalog.pg_get_constraintdef(oid) as condef "
2952 "FROM pg_catalog.pg_constraint "
2953 "WHERE conrelid = '%u'::pg_catalog.oid "
2954 "AND contype = 'f'",
2955 tbinfo->dobj.catId.oid);
2956 res = PQexec(g_conn, query->data);
2957 check_sql_result(res, g_conn, query->data, PGRES_TUPLES_OK);
2959 ntups = PQntuples(res);
2961 i_contableoid = PQfnumber(res, "tableoid");
2962 i_conoid = PQfnumber(res, "oid");
2963 i_conname = PQfnumber(res, "conname");
2964 i_condef = PQfnumber(res, "condef");
2966 constrinfo = (ConstraintInfo *) malloc(ntups * sizeof(ConstraintInfo));
2968 for (j = 0; j < ntups; j++)
2970 constrinfo[j].dobj.objType = DO_FK_CONSTRAINT;
2971 constrinfo[j].dobj.catId.tableoid = atooid(PQgetvalue(res, j, i_contableoid));
2972 constrinfo[j].dobj.catId.oid = atooid(PQgetvalue(res, j, i_conoid));
2973 AssignDumpId(&constrinfo[j].dobj);
2974 constrinfo[j].dobj.name = strdup(PQgetvalue(res, j, i_conname));
2975 constrinfo[j].dobj.namespace = tbinfo->dobj.namespace;
2976 constrinfo[j].contable = tbinfo;
2977 constrinfo[j].condomain = NULL;
2978 constrinfo[j].contype = 'f';
2979 constrinfo[j].condef = strdup(PQgetvalue(res, j, i_condef));
2980 constrinfo[j].conindex = 0;
2981 constrinfo[j].coninherited = false;
2982 constrinfo[j].separate = true;
2988 destroyPQExpBuffer(query);
2992 * getDomainConstraints
2994 * Get info about constraints on a domain.
2997 getDomainConstraints(TypeInfo *tinfo)
3000 ConstraintInfo *constrinfo;
3009 /* pg_constraint was created in 7.3, so nothing to do if older */
3010 if (g_fout->remoteVersion < 70300)
3014 * select appropriate schema to ensure names in constraint are
3015 * properly qualified
3017 selectSourceSchema(tinfo->dobj.namespace->dobj.name);
3019 query = createPQExpBuffer();
3021 if (g_fout->remoteVersion >= 70400)
3022 appendPQExpBuffer(query, "SELECT tableoid, oid, conname, "
3023 "pg_catalog.pg_get_constraintdef(oid) AS consrc "
3024 "FROM pg_catalog.pg_constraint "
3025 "WHERE contypid = '%u'::pg_catalog.oid "
3027 tinfo->dobj.catId.oid);
3029 appendPQExpBuffer(query, "SELECT tableoid, oid, conname, "
3030 "'CHECK (' || consrc || ')' AS consrc "
3031 "FROM pg_catalog.pg_constraint "
3032 "WHERE contypid = '%u'::pg_catalog.oid "
3034 tinfo->dobj.catId.oid);
3036 res = PQexec(g_conn, query->data);
3037 check_sql_result(res, g_conn, query->data, PGRES_TUPLES_OK);
3039 ntups = PQntuples(res);
3041 i_tableoid = PQfnumber(res, "tableoid");
3042 i_oid = PQfnumber(res, "oid");
3043 i_conname = PQfnumber(res, "conname");
3044 i_consrc = PQfnumber(res, "consrc");
3046 constrinfo = (ConstraintInfo *) malloc(ntups * sizeof(ConstraintInfo));
3048 tinfo->nDomChecks = ntups;
3049 tinfo->domChecks = constrinfo;
3051 for (i = 0; i < ntups; i++)
3053 constrinfo[i].dobj.objType = DO_CONSTRAINT;
3054 constrinfo[i].dobj.catId.tableoid = atooid(PQgetvalue(res, i, i_tableoid));
3055 constrinfo[i].dobj.catId.oid = atooid(PQgetvalue(res, i, i_oid));
3056 AssignDumpId(&constrinfo[i].dobj);
3057 constrinfo[i].dobj.name = strdup(PQgetvalue(res, i, i_conname));
3058 constrinfo[i].dobj.namespace = tinfo->dobj.namespace;
3059 constrinfo[i].contable = NULL;
3060 constrinfo[i].condomain = tinfo;
3061 constrinfo[i].contype = 'c';
3062 constrinfo[i].condef = strdup(PQgetvalue(res, i, i_consrc));
3063 constrinfo[i].conindex = 0;
3064 constrinfo[i].coninherited = false;
3065 constrinfo[i].separate = false;
3068 * Make the domain depend on the constraint, ensuring it won't be
3069 * output till any constraint dependencies are OK.
3071 addObjectDependency(&tinfo->dobj,
3072 constrinfo[i].dobj.dumpId);
3077 destroyPQExpBuffer(query);
3082 * get basic information about every rule in the system
3084 * numRules is set to the number of rules read in
3087 getRules(int *numRules)
3092 PQExpBuffer query = createPQExpBuffer();
3101 /* Make sure we are in proper schema */
3102 selectSourceSchema("pg_catalog");
3104 if (g_fout->remoteVersion >= 70100)
3106 appendPQExpBuffer(query, "SELECT "
3107 "tableoid, oid, rulename, "
3108 "ev_class as ruletable, ev_type, is_instead "
3114 appendPQExpBuffer(query, "SELECT "
3115 "(SELECT oid FROM pg_class WHERE relname = 'pg_rewrite') AS tableoid, "
3117 "ev_class as ruletable, ev_type, is_instead "
3122 res = PQexec(g_conn, query->data);
3123 check_sql_result(res, g_conn, query->data, PGRES_TUPLES_OK);
3125 ntups = PQntuples(res);
3129 ruleinfo = (RuleInfo *) malloc(ntups * sizeof(RuleInfo));
3131 i_tableoid = PQfnumber(res, "tableoid");
3132 i_oid = PQfnumber(res, "oid");
3133 i_rulename = PQfnumber(res, "rulename");
3134 i_ruletable = PQfnumber(res, "ruletable");
3135 i_ev_type = PQfnumber(res, "ev_type");
3136 i_is_instead = PQfnumber(res, "is_instead");
3138 for (i = 0; i < ntups; i++)
3142 ruleinfo[i].dobj.objType = DO_RULE;
3143 ruleinfo[i].dobj.catId.tableoid = atooid(PQgetvalue(res, i, i_tableoid));
3144 ruleinfo[i].dobj.catId.oid = atooid(PQgetvalue(res, i, i_oid));
3145 AssignDumpId(&ruleinfo[i].dobj);
3146 ruleinfo[i].dobj.name = strdup(PQgetvalue(res, i, i_rulename));
3147 ruletableoid = atooid(PQgetvalue(res, i, i_ruletable));
3148 ruleinfo[i].ruletable = findTableByOid(ruletableoid);
3149 ruleinfo[i].dobj.namespace = ruleinfo[i].ruletable->dobj.namespace;
3150 ruleinfo[i].ev_type = *(PQgetvalue(res, i, i_ev_type));
3151 ruleinfo[i].is_instead = *(PQgetvalue(res, i, i_is_instead)) == 't';
3152 if (ruleinfo[i].ruletable)
3155 * If the table is a view, force its ON SELECT rule to be
3156 * sorted before the view itself --- this ensures that any
3157 * dependencies for the rule affect the table's positioning.
3158 * Other rules are forced to appear after their table.
3160 if (ruleinfo[i].ruletable->relkind == RELKIND_VIEW &&
3161 ruleinfo[i].ev_type == '1' && ruleinfo[i].is_instead)
3162 addObjectDependency(&ruleinfo[i].ruletable->dobj,
3163 ruleinfo[i].dobj.dumpId);
3165 addObjectDependency(&ruleinfo[i].dobj,
3166 ruleinfo[i].ruletable->dobj.dumpId);
3172 destroyPQExpBuffer(query);
3179 * get information about every trigger on a dumpable table
3181 * Note: trigger data is not returned directly to the caller, but it
3182 * does get entered into the DumpableObject tables.
3185 getTriggers(TableInfo tblinfo[], int numTables)
3189 PQExpBuffer query = createPQExpBuffer();
3191 TriggerInfo *tginfo;
3207 for (i = 0; i < numTables; i++)
3209 TableInfo *tbinfo = &tblinfo[i];
3211 if (tbinfo->ntrig == 0 || !tbinfo->dump)
3215 write_msg(NULL, "reading triggers for table \"%s\"\n",
3219 * select table schema to ensure regproc name is qualified if
3222 selectSourceSchema(tbinfo->dobj.namespace->dobj.name);
3224 resetPQExpBuffer(query);
3225 if (g_fout->remoteVersion >= 70300)
3228 * We ignore triggers that are tied to a foreign-key
3231 appendPQExpBuffer(query,
3233 "tgfoid::pg_catalog.regproc as tgfname, "
3234 "tgtype, tgnargs, tgargs, "
3235 "tgisconstraint, tgconstrname, tgdeferrable, "
3236 "tgconstrrelid, tginitdeferred, tableoid, oid, "
3237 "tgconstrrelid::pg_catalog.regclass as tgconstrrelname "
3238 "from pg_catalog.pg_trigger t "
3239 "where tgrelid = '%u'::pg_catalog.oid "
3240 "and (not tgisconstraint "
3242 " (SELECT 1 FROM pg_catalog.pg_depend d "
3243 " JOIN pg_catalog.pg_constraint c ON (d.refclassid = c.tableoid AND d.refobjid = c.oid) "
3244 " WHERE d.classid = t.tableoid AND d.objid = t.oid AND d.deptype = 'i' AND c.contype = 'f'))",
3245 tbinfo->dobj.catId.oid);
3247 else if (g_fout->remoteVersion >= 70100)
3249 appendPQExpBuffer(query,
3250 "SELECT tgname, tgfoid::regproc as tgfname, "
3251 "tgtype, tgnargs, tgargs, "
3252 "tgisconstraint, tgconstrname, tgdeferrable, "
3253 "tgconstrrelid, tginitdeferred, tableoid, oid, "
3254 "(select relname from pg_class where oid = tgconstrrelid) "
3255 " as tgconstrrelname "
3257 "where tgrelid = '%u'::oid",
3258 tbinfo->dobj.catId.oid);
3262 appendPQExpBuffer(query,
3263 "SELECT tgname, tgfoid::regproc as tgfname, "
3264 "tgtype, tgnargs, tgargs, "
3265 "tgisconstraint, tgconstrname, tgdeferrable, "
3266 "tgconstrrelid, tginitdeferred, "
3267 "(SELECT oid FROM pg_class WHERE relname = 'pg_trigger') AS tableoid, "
3270 "(select relname from pg_class where oid = tgconstrrelid) "
3271 " as tgconstrrelname "
3273 "where tgrelid = '%u'::oid",
3274 tbinfo->dobj.catId.oid);
3276 res = PQexec(g_conn, query->data);
3277 check_sql_result(res, g_conn, query->data, PGRES_TUPLES_OK);
3279 ntups = PQntuples(res);
3282 * We may have less triggers than recorded due to having ignored
3283 * foreign-key triggers
3285 if (ntups > tbinfo->ntrig)
3287 write_msg(NULL, "expected %d triggers on table \"%s\" but found %d\n",
3288 tbinfo->ntrig, tbinfo->dobj.name, ntups);
3291 i_tableoid = PQfnumber(res, "tableoid");
3292 i_oid = PQfnumber(res, "oid");
3293 i_tgname = PQfnumber(res, "tgname");
3294 i_tgfname = PQfnumber(res, "tgfname");
3295 i_tgtype = PQfnumber(res, "tgtype");
3296 i_tgnargs = PQfnumber(res, "tgnargs");
3297 i_tgargs = PQfnumber(res, "tgargs");
3298 i_tgisconstraint = PQfnumber(res, "tgisconstraint");
3299 i_tgconstrname = PQfnumber(res, "tgconstrname");
3300 i_tgconstrrelid = PQfnumber(res, "tgconstrrelid");
3301 i_tgconstrrelname = PQfnumber(res, "tgconstrrelname");
3302 i_tgdeferrable = PQfnumber(res, "tgdeferrable");
3303 i_tginitdeferred = PQfnumber(res, "tginitdeferred");
3305 tginfo = (TriggerInfo *) malloc(ntups * sizeof(TriggerInfo));
3307 for (j = 0; j < ntups; j++)
3309 tginfo[j].dobj.objType = DO_TRIGGER;
3310 tginfo[j].dobj.catId.tableoid = atooid(PQgetvalue(res, j, i_tableoid));
3311 tginfo[j].dobj.catId.oid = atooid(PQgetvalue(res, j, i_oid));
3312 AssignDumpId(&tginfo[j].dobj);
3313 tginfo[j].dobj.name = strdup(PQgetvalue(res, j, i_tgname));
3314 tginfo[j].dobj.namespace = tbinfo->dobj.namespace;
3315 tginfo[j].tgtable = tbinfo;
3316 tginfo[j].tgfname = strdup(PQgetvalue(res, j, i_tgfname));
3317 tginfo[j].tgtype = atoi(PQgetvalue(res, j, i_tgtype));
3318 tginfo[j].tgnargs = atoi(PQgetvalue(res, j, i_tgnargs));
3319 tginfo[j].tgargs = strdup(PQgetvalue(res, j, i_tgargs));
3320 tginfo[j].tgisconstraint = *(PQgetvalue(res, j, i_tgisconstraint)) == 't';
3321 tginfo[j].tgdeferrable = *(PQgetvalue(res, j, i_tgdeferrable)) == 't';
3322 tginfo[j].tginitdeferred = *(PQgetvalue(res, j, i_tginitdeferred)) == 't';
3324 if (tginfo[j].tgisconstraint)
3326 tginfo[j].tgconstrname = strdup(PQgetvalue(res, j, i_tgconstrname));
3327 tginfo[j].tgconstrrelid = atooid(PQgetvalue(res, j, i_tgconstrrelid));
3328 if (OidIsValid(tginfo[j].tgconstrrelid))
3330 if (PQgetisnull(res, j, i_tgconstrrelname))
3332 write_msg(NULL, "query produced null referenced table name for foreign key trigger \"%s\" on table \"%s\" (OID of table: %u)\n",
3333 tginfo[j].dobj.name, tbinfo->dobj.name,
3334 tginfo[j].tgconstrrelid);
3337 tginfo[j].tgconstrrelname = strdup(PQgetvalue(res, j, i_tgconstrrelname));
3340 tginfo[j].tgconstrrelname = NULL;
3344 tginfo[j].tgconstrname = NULL;
3345 tginfo[j].tgconstrrelid = InvalidOid;
3346 tginfo[j].tgconstrrelname = NULL;
3353 destroyPQExpBuffer(query);
3358 * get basic information about every procedural language in the system
3360 * numProcLangs is set to the number of langs read in
3362 * NB: this must run after getFuncs() because we assume we can do
3366 getProcLangs(int *numProcLangs)
3371 PQExpBuffer query = createPQExpBuffer();
3372 ProcLangInfo *planginfo;
3377 int i_lanplcallfoid;
3378 int i_lanvalidator = -1;
3381 /* Make sure we are in proper schema */
3382 selectSourceSchema("pg_catalog");
3384 if (g_fout->remoteVersion >= 70100)
3386 appendPQExpBuffer(query, "SELECT tableoid, oid, * FROM pg_language "
3392 appendPQExpBuffer(query, "SELECT "
3393 "(SELECT oid FROM pg_class WHERE relname = 'pg_language') AS tableoid, "
3394 "oid, * FROM pg_language "
3399 res = PQexec(g_conn, query->data);
3400 check_sql_result(res, g_conn, query->data, PGRES_TUPLES_OK);
3402 ntups = PQntuples(res);
3404 *numProcLangs = ntups;
3406 planginfo = (ProcLangInfo *) malloc(ntups * sizeof(ProcLangInfo));
3408 i_tableoid = PQfnumber(res, "tableoid");
3409 i_oid = PQfnumber(res, "oid");
3410 i_lanname = PQfnumber(res, "lanname");
3411 i_lanpltrusted = PQfnumber(res, "lanpltrusted");
3412 i_lanplcallfoid = PQfnumber(res, "lanplcallfoid");
3413 if (g_fout->remoteVersion >= 70300)
3415 i_lanvalidator = PQfnumber(res, "lanvalidator");
3416 i_lanacl = PQfnumber(res, "lanacl");
3419 for (i = 0; i < ntups; i++)
3421 planginfo[i].dobj.objType = DO_PROCLANG;
3422 planginfo[i].dobj.catId.tableoid = atooid(PQgetvalue(res, i, i_tableoid));
3423 planginfo[i].dobj.catId.oid = atooid(PQgetvalue(res, i, i_oid));
3424 AssignDumpId(&planginfo[i].dobj);
3426 planginfo[i].dobj.name = strdup(PQgetvalue(res, i, i_lanname));
3427 planginfo[i].lanpltrusted = *(PQgetvalue(res, i, i_lanpltrusted)) == 't';
3428 planginfo[i].lanplcallfoid = atooid(PQgetvalue(res, i, i_lanplcallfoid));
3429 if (g_fout->remoteVersion >= 70300)
3431 planginfo[i].lanvalidator = atooid(PQgetvalue(res, i, i_lanvalidator));
3432 planginfo[i].lanacl = strdup(PQgetvalue(res, i, i_lanacl));
3438 planginfo[i].lanvalidator = InvalidOid;
3439 planginfo[i].lanacl = strdup("{=U}");
3442 * We need to make a dependency to ensure the function will be
3443 * dumped first. (In 7.3 and later the regular dependency
3444 * mechanism will handle this for us.)
3446 funcInfo = findFuncByOid(planginfo[i].lanplcallfoid);
3448 addObjectDependency(&planginfo[i].dobj,
3449 funcInfo->dobj.dumpId);
3455 destroyPQExpBuffer(query);
3462 * get basic information about every cast in the system
3464 * numCasts is set to the number of casts read in
3467 getCasts(int *numCasts)
3472 PQExpBuffer query = createPQExpBuffer();
3481 /* Make sure we are in proper schema */
3482 selectSourceSchema("pg_catalog");
3484 if (g_fout->remoteVersion >= 70300)
3486 appendPQExpBuffer(query, "SELECT tableoid, oid, "
3487 "castsource, casttarget, castfunc, castcontext "
3488 "FROM pg_cast ORDER BY 3,4");
3492 appendPQExpBuffer(query, "SELECT 0 as tableoid, p.oid, "
3493 "t1.oid as castsource, t2.oid as casttarget, "
3494 "p.oid as castfunc, 'e' as castcontext "
3495 "FROM pg_type t1, pg_type t2, pg_proc p "
3496 "WHERE p.pronargs = 1 AND "
3497 "p.proargtypes[0] = t1.oid AND "
3498 "p.prorettype = t2.oid AND p.proname = t2.typname "
3502 res = PQexec(g_conn, query->data);
3503 check_sql_result(res, g_conn, query->data, PGRES_TUPLES_OK);
3505 ntups = PQntuples(res);
3509 castinfo = (CastInfo *) malloc(ntups * sizeof(CastInfo));
3511 i_tableoid = PQfnumber(res, "tableoid");
3512 i_oid = PQfnumber(res, "oid");
3513 i_castsource = PQfnumber(res, "castsource");
3514 i_casttarget = PQfnumber(res, "casttarget");
3515 i_castfunc = PQfnumber(res, "castfunc");
3516 i_castcontext = PQfnumber(res, "castcontext");
3518 for (i = 0; i < ntups; i++)
3520 PQExpBufferData namebuf;
3521 TypeInfo *sTypeInfo;
3522 TypeInfo *tTypeInfo;
3524 castinfo[i].dobj.objType = DO_CAST;
3525 castinfo[i].dobj.catId.tableoid = atooid(PQgetvalue(res, i, i_tableoid));
3526 castinfo[i].dobj.catId.oid = atooid(PQgetvalue(res, i, i_oid));
3527 AssignDumpId(&castinfo[i].dobj);
3528 castinfo[i].castsource = atooid(PQgetvalue(res, i, i_castsource));
3529 castinfo[i].casttarget = atooid(PQgetvalue(res, i, i_casttarget));
3530 castinfo[i].castfunc = atooid(PQgetvalue(res, i, i_castfunc));
3531 castinfo[i].castcontext = *(PQgetvalue(res, i, i_castcontext));
3534 * Try to name cast as concatenation of typnames. This is only
3535 * used for purposes of sorting. If we fail to find either type,
3536 * the name will be an empty string.
3538 initPQExpBuffer(&namebuf);
3539 sTypeInfo = findTypeByOid(castinfo[i].castsource);
3540 tTypeInfo = findTypeByOid(castinfo[i].casttarget);
3541 if (sTypeInfo && tTypeInfo)
3542 appendPQExpBuffer(&namebuf, "%s %s",
3543 sTypeInfo->dobj.name, tTypeInfo->dobj.name);
3544 castinfo[i].dobj.name = namebuf.data;
3546 if (OidIsValid(castinfo[i].castfunc))
3549 * We need to make a dependency to ensure the function will be
3550 * dumped first. (In 7.3 and later the regular dependency
3551 * mechanism will handle this for us.)
3555 funcInfo = findFuncByOid(castinfo[i].castfunc);
3557 addObjectDependency(&castinfo[i].dobj,
3558 funcInfo->dobj.dumpId);
3564 destroyPQExpBuffer(query);
3571 * for each interesting table, read info about its attributes
3572 * (names, types, default values, CHECK constraints, etc)
3574 * This is implemented in a very inefficient way right now, looping
3575 * through the tblinfo and doing a join per table to find the attrs and their
3576 * types. However, because we want type names and so forth to be named
3577 * relative to the schema of each table, we couldn't do it in just one
3578 * query. (Maybe one query per schema?)
3583 getTableAttrs(TableInfo *tblinfo, int numTables)
3588 PQExpBuffer q = createPQExpBuffer();
3593 int i_attstattarget;
3604 for (i = 0; i < numTables; i++)
3606 TableInfo *tbinfo = &tblinfo[i];
3608 /* Don't bother to collect info for sequences */
3609 if (tbinfo->relkind == RELKIND_SEQUENCE)
3612 /* Don't bother with uninteresting tables, either */
3613 if (!tbinfo->interesting)
3617 * Make sure we are in proper schema for this table; this allows
3618 * correct retrieval of formatted type names and default exprs
3620 selectSourceSchema(tbinfo->dobj.namespace->dobj.name);
3622 /* find all the user attributes and their types */
3625 * we must read the attribute names in attribute number order!
3626 * because we will use the attnum to index into the attnames array
3627 * later. We actually ask to order by "attrelid, attnum" because
3628 * (at least up to 7.3) the planner is not smart enough to realize
3629 * it needn't re-sort the output of an indexscan on
3630 * pg_attribute_relid_attnum_index.
3633 write_msg(NULL, "finding the columns and types of table \"%s\"\n",
3636 resetPQExpBuffer(q);
3638 if (g_fout->remoteVersion >= 70300)
3640 /* need left join here to not fail on dropped columns ... */
3641 appendPQExpBuffer(q, "SELECT a.attnum, a.attname, a.atttypmod, a.attstattarget, a.attstorage, t.typstorage, "
3642 "a.attnotnull, a.atthasdef, a.attisdropped, a.attislocal, "
3643 "pg_catalog.format_type(t.oid,a.atttypmod) as atttypname "
3644 "from pg_catalog.pg_attribute a left join pg_catalog.pg_type t "
3645 "on a.atttypid = t.oid "
3646 "where a.attrelid = '%u'::pg_catalog.oid "
3647 "and a.attnum > 0::pg_catalog.int2 "
3648 "order by a.attrelid, a.attnum",
3649 tbinfo->dobj.catId.oid);
3651 else if (g_fout->remoteVersion >= 70100)
3654 * attstattarget doesn't exist in 7.1. It does exist in 7.2,
3655 * but we don't dump it because we can't tell whether it's
3656 * been explicitly set or was just a default.
3658 appendPQExpBuffer(q, "SELECT a.attnum, a.attname, a.atttypmod, -1 as attstattarget, a.attstorage, t.typstorage, "
3659 "a.attnotnull, a.atthasdef, false as attisdropped, false as attislocal, "
3660 "format_type(t.oid,a.atttypmod) as atttypname "
3661 "from pg_attribute a left join pg_type t "
3662 "on a.atttypid = t.oid "
3663 "where a.attrelid = '%u'::oid "
3664 "and a.attnum > 0::int2 "
3665 "order by a.attrelid, a.attnum",
3666 tbinfo->dobj.catId.oid);
3670 /* format_type not available before 7.1 */
3671 appendPQExpBuffer(q, "SELECT attnum, attname, atttypmod, -1 as attstattarget, attstorage, attstorage as typstorage, "
3672 "attnotnull, atthasdef, false as attisdropped, false as attislocal, "
3673 "(select typname from pg_type where oid = atttypid) as atttypname "
3674 "from pg_attribute a "
3675 "where attrelid = '%u'::oid "
3676 "and attnum > 0::int2 "
3677 "order by attrelid, attnum",
3678 tbinfo->dobj.catId.oid);
3681 res = PQexec(g_conn, q->data);
3682 check_sql_result(res, g_conn, q->data, PGRES_TUPLES_OK);
3684 ntups = PQntuples(res);
3686 i_attnum = PQfnumber(res, "attnum");
3687 i_attname = PQfnumber(res, "attname");
3688 i_atttypname = PQfnumber(res, "atttypname");
3689 i_atttypmod = PQfnumber(res, "atttypmod");
3690 i_attstattarget = PQfnumber(res, "attstattarget");
3691 i_attstorage = PQfnumber(res, "attstorage");
3692 i_typstorage = PQfnumber(res, "typstorage");
3693 i_attnotnull = PQfnumber(res, "attnotnull");
3694 i_atthasdef = PQfnumber(res, "atthasdef");
3695 i_attisdropped = PQfnumber(res, "attisdropped");
3696 i_attislocal = PQfnumber(res, "attislocal");
3698 tbinfo->numatts = ntups;
3699 tbinfo->attnames = (char **) malloc(ntups * sizeof(char *));
3700 tbinfo->atttypnames = (char **) malloc(ntups * sizeof(char *));
3701 tbinfo->atttypmod = (int *) malloc(ntups * sizeof(int));
3702 tbinfo->attstattarget = (int *) malloc(ntups * sizeof(int));
3703 tbinfo->attstorage = (char *) malloc(ntups * sizeof(char));
3704 tbinfo->typstorage = (char *) malloc(ntups * sizeof(char));
3705 tbinfo->attisdropped = (bool *) malloc(ntups * sizeof(bool));
3706 tbinfo->attislocal = (bool *) malloc(ntups * sizeof(bool));
3707 tbinfo->attisserial = (bool *) malloc(ntups * sizeof(bool));
3708 tbinfo->notnull = (bool *) malloc(ntups * sizeof(bool));
3709 tbinfo->attrdefs = (AttrDefInfo **) malloc(ntups * sizeof(AttrDefInfo *));
3710 tbinfo->inhAttrs = (bool *) malloc(ntups * sizeof(bool));
3711 tbinfo->inhAttrDef = (bool *) malloc(ntups * sizeof(bool));
3712 tbinfo->inhNotNull = (bool *) malloc(ntups * sizeof(bool));
3713 hasdefaults = false;
3715 for (j = 0; j < ntups; j++)
3717 if (j + 1 != atoi(PQgetvalue(res, j, i_attnum)))
3719 write_msg(NULL, "invalid column numbering in table \"%s\"\n",
3723 tbinfo->attnames[j] = strdup(PQgetvalue(res, j, i_attname));
3724 tbinfo->atttypnames[j] = strdup(PQgetvalue(res, j, i_atttypname));
3725 tbinfo->atttypmod[j] = atoi(PQgetvalue(res, j, i_atttypmod));
3726 tbinfo->attstattarget[j] = atoi(PQgetvalue(res, j, i_attstattarget));
3727 tbinfo->attstorage[j] = *(PQgetvalue(res, j, i_attstorage));
3728 tbinfo->typstorage[j] = *(PQgetvalue(res, j, i_typstorage));
3729 tbinfo->attisdropped[j] = (PQgetvalue(res, j, i_attisdropped)[0] == 't');
3730 tbinfo->attislocal[j] = (PQgetvalue(res, j, i_attislocal)[0] == 't');
3731 tbinfo->attisserial[j] = false; /* fix below */
3732 tbinfo->notnull[j] = (PQgetvalue(res, j, i_attnotnull)[0] == 't');
3733 tbinfo->attrdefs[j] = NULL; /* fix below */
3734 if (PQgetvalue(res, j, i_atthasdef)[0] == 't')
3736 /* these flags will be set in flagInhAttrs() */
3737 tbinfo->inhAttrs[j] = false;
3738 tbinfo->inhAttrDef[j] = false;
3739 tbinfo->inhNotNull[j] = false;
3745 * Get info about column defaults
3749 AttrDefInfo *attrdefs;
3753 write_msg(NULL, "finding default expressions of table \"%s\"\n",
3756 resetPQExpBuffer(q);
3757 if (g_fout->remoteVersion >= 70300)
3759 appendPQExpBuffer(q, "SELECT tableoid, oid, adnum, "
3760 "pg_catalog.pg_get_expr(adbin, adrelid) AS adsrc "
3761 "FROM pg_catalog.pg_attrdef "
3762 "WHERE adrelid = '%u'::pg_catalog.oid",
3763 tbinfo->dobj.catId.oid);
3765 else if (g_fout->remoteVersion >= 70200)
3767 /* 7.2 did not have OIDs in pg_attrdef */
3768 appendPQExpBuffer(q, "SELECT tableoid, 0 as oid, adnum, "
3769 "pg_get_expr(adbin, adrelid) AS adsrc "
3771 "WHERE adrelid = '%u'::oid",
3772 tbinfo->dobj.catId.oid);
3774 else if (g_fout->remoteVersion >= 70100)
3776 /* no pg_get_expr, so must rely on adsrc */
3777 appendPQExpBuffer(q, "SELECT tableoid, oid, adnum, adsrc "
3779 "WHERE adrelid = '%u'::oid",
3780 tbinfo->dobj.catId.oid);
3784 /* no pg_get_expr, no tableoid either */
3785 appendPQExpBuffer(q, "SELECT "
3786 "(SELECT oid FROM pg_class WHERE relname = 'pg_attrdef') AS tableoid, "
3787 "oid, adnum, adsrc "
3789 "WHERE adrelid = '%u'::oid",
3790 tbinfo->dobj.catId.oid);
3792 res = PQexec(g_conn, q->data);
3793 check_sql_result(res, g_conn, q->data, PGRES_TUPLES_OK);
3795 numDefaults = PQntuples(res);
3796 attrdefs = (AttrDefInfo *) malloc(numDefaults * sizeof(AttrDefInfo));
3798 for (j = 0; j < numDefaults; j++)
3802 attrdefs[j].dobj.objType = DO_ATTRDEF;
3803 attrdefs[j].dobj.catId.tableoid = atooid(PQgetvalue(res, j, 0));
3804 attrdefs[j].dobj.catId.oid = atooid(PQgetvalue(res, j, 1));
3805 AssignDumpId(&attrdefs[j].dobj);
3806 attrdefs[j].adtable = tbinfo;
3807 attrdefs[j].adnum = adnum = atoi(PQgetvalue(res, j, 2));
3808 attrdefs[j].adef_expr = strdup(PQgetvalue(res, j, 3));
3810 attrdefs[j].dobj.name = strdup(tbinfo->dobj.name);
3811 attrdefs[j].dobj.namespace = tbinfo->dobj.namespace;
3814 * Defaults on a VIEW must always be dumped as separate
3815 * ALTER TABLE commands. Defaults on regular tables are
3816 * dumped as part of the CREATE TABLE if possible. To
3817 * check if it's safe, we mark the default as needing to
3818 * appear before the CREATE.
3820 if (tbinfo->relkind == RELKIND_VIEW)
3822 attrdefs[j].separate = true;
3823 /* needed in case pre-7.3 DB: */
3824 addObjectDependency(&attrdefs[j].dobj,
3825 tbinfo->dobj.dumpId);
3829 attrdefs[j].separate = false;
3830 addObjectDependency(&tbinfo->dobj,
3831 attrdefs[j].dobj.dumpId);
3834 if (adnum <= 0 || adnum > ntups)
3836 write_msg(NULL, "invalid adnum value %d for table \"%s\"\n",
3837 adnum, tbinfo->dobj.name);
3840 tbinfo->attrdefs[adnum - 1] = &attrdefs[j];
3846 * Get info about table CHECK constraints
3848 if (tbinfo->ncheck > 0)
3850 ConstraintInfo *constrs;
3854 write_msg(NULL, "finding check constraints for table \"%s\"\n",
3857 resetPQExpBuffer(q);
3858 if (g_fout->remoteVersion >= 70400)
3860 appendPQExpBuffer(q, "SELECT tableoid, oid, conname, "
3861 "pg_catalog.pg_get_constraintdef(oid) AS consrc "
3862 "FROM pg_catalog.pg_constraint "
3863 "WHERE conrelid = '%u'::pg_catalog.oid "
3864 " AND contype = 'c' "
3866 tbinfo->dobj.catId.oid);
3868 else if (g_fout->remoteVersion >= 70300)
3870 /* no pg_get_constraintdef, must use consrc */
3871 appendPQExpBuffer(q, "SELECT tableoid, oid, conname, "
3872 "'CHECK (' || consrc || ')' AS consrc "
3873 "FROM pg_catalog.pg_constraint "
3874 "WHERE conrelid = '%u'::pg_catalog.oid "
3875 " AND contype = 'c' "
3877 tbinfo->dobj.catId.oid);
3879 else if (g_fout->remoteVersion >= 70200)
3881 /* 7.2 did not have OIDs in pg_relcheck */
3882 appendPQExpBuffer(q, "SELECT tableoid, 0 as oid, "
3883 "rcname AS conname, "
3884 "'CHECK (' || rcsrc || ')' AS consrc "
3886 "WHERE rcrelid = '%u'::oid "
3888 tbinfo->dobj.catId.oid);
3890 else if (g_fout->remoteVersion >= 70100)
3892 appendPQExpBuffer(q, "SELECT tableoid, oid, "
3893 "rcname AS conname, "
3894 "'CHECK (' || rcsrc || ')' AS consrc "
3896 "WHERE rcrelid = '%u'::oid "
3898 tbinfo->dobj.catId.oid);
3902 /* no tableoid in 7.0 */
3903 appendPQExpBuffer(q, "SELECT "
3904 "(SELECT oid FROM pg_class WHERE relname = 'pg_relcheck') AS tableoid, "
3905 "oid, rcname AS conname, "
3906 "'CHECK (' || rcsrc || ')' AS consrc "
3908 "WHERE rcrelid = '%u'::oid "
3910 tbinfo->dobj.catId.oid);
3912 res = PQexec(g_conn, q->data);
3913 check_sql_result(res, g_conn, q->data, PGRES_TUPLES_OK);
3915 numConstrs = PQntuples(res);
3916 if (numConstrs != tbinfo->ncheck)
3918 write_msg(NULL, "expected %d check constraints on table \"%s\" but found %d\n",
3919 tbinfo->ncheck, tbinfo->dobj.name, numConstrs);
3920 write_msg(NULL, "(The system catalogs might be corrupted.)\n");
3924 constrs = (ConstraintInfo *) malloc(numConstrs * sizeof(ConstraintInfo));
3925 tbinfo->checkexprs = constrs;
3927 for (j = 0; j < numConstrs; j++)
3929 constrs[j].dobj.objType = DO_CONSTRAINT;
3930 constrs[j].dobj.catId.tableoid = atooid(PQgetvalue(res, j, 0));
3931 constrs[j].dobj.catId.oid = atooid(PQgetvalue(res, j, 1));
3932 AssignDumpId(&constrs[j].dobj);
3933 constrs[j].dobj.name = strdup(PQgetvalue(res, j, 2));
3934 constrs[j].dobj.namespace = tbinfo->dobj.namespace;
3935 constrs[j].contable = tbinfo;
3936 constrs[j].condomain = NULL;
3937 constrs[j].contype = 'c';
3938 constrs[j].condef = strdup(PQgetvalue(res, j, 3));
3939 constrs[j].conindex = 0;
3940 constrs[j].coninherited = false;
3941 constrs[j].separate = false;
3942 addObjectDependency(&tbinfo->dobj,
3943 constrs[j].dobj.dumpId);
3946 * If the constraint is inherited, this will be detected
3947 * later. We also detect later if the constraint must be
3948 * split out from the table definition.
3955 * Check to see if any columns are serial columns. Our first
3956 * quick filter is that it must be integer or bigint with a
3957 * default. If so, we scan to see if we found a sequence linked
3958 * to this column. If we did, mark the column and sequence
3961 for (j = 0; j < ntups; j++)
3964 * Note assumption that format_type will show these types as
3965 * exactly "integer" and "bigint" regardless of schema path.
3966 * This is correct in 7.3 but needs to be watched.
3968 if (strcmp(tbinfo->atttypnames[j], "integer") != 0 &&
3969 strcmp(tbinfo->atttypnames[j], "bigint") != 0)
3971 if (tbinfo->attrdefs[j] == NULL)
3973 for (k = 0; k < numTables; k++)
3975 TableInfo *seqinfo = &tblinfo[k];
3977 if (OidIsValid(seqinfo->owning_tab) &&
3978 seqinfo->owning_tab == tbinfo->dobj.catId.oid &&
3979 seqinfo->owning_col == j + 1)
3982 * Found a match. Copy the table's interesting and
3983 * dumpable flags to the sequence.
3985 tbinfo->attisserial[j] = true;
3986 seqinfo->interesting = tbinfo->interesting;
3987 seqinfo->dump = tbinfo->dump;
3994 destroyPQExpBuffer(q);
4001 * This routine is used to dump any comments associated with the
4002 * object handed to this routine. The routine takes a constant character
4003 * string for the target part of the comment-creation command, plus
4004 * the namespace and owner of the object (for labeling the ArchiveEntry),
4005 * plus catalog ID and subid which are the lookup key for pg_description,
4006 * plus the dump ID for the object (for setting a dependency).
4007 * If a matching pg_description entry is found, it is dumped.
4010 dumpComment(Archive *fout, const char *target,
4011 const char *namespace, const char *owner,
4012 CatalogId catalogId, int subid, DumpId dumpId)
4014 CommentItem *comments;
4017 /* Comments are SCHEMA not data */
4021 /* Search for comments associated with catalogId, using table */
4022 ncomments = findComments(fout, catalogId.tableoid, catalogId.oid,
4025 /* Is there one matching the subid? */
4026 while (ncomments > 0)
4028 if (comments->objsubid == subid)
4034 /* If a comment exists, build COMMENT ON statement */
4037 PQExpBuffer query = createPQExpBuffer();
4039 appendPQExpBuffer(query, "COMMENT ON %s IS ", target);
4040 appendStringLiteral(query, comments->descr, false);
4041 appendPQExpBuffer(query, ";\n");
4043 ArchiveEntry(fout, nilCatalogId, createDumpId(),
4044 target, namespace, NULL, owner, false,
4045 "COMMENT", query->data, "", NULL,
4049 destroyPQExpBuffer(query);
4054 * dumpTableComment --
4056 * As above, but dump comments for both the specified table (or view)
4060 dumpTableComment(Archive *fout, TableInfo *tbinfo,
4061 const char *reltypename)
4063 CommentItem *comments;
4068 /* Comments are SCHEMA not data */
4072 /* Search for comments associated with relation, using table */
4073 ncomments = findComments(fout,
4074 tbinfo->dobj.catId.tableoid,
4075 tbinfo->dobj.catId.oid,
4078 /* If comments exist, build COMMENT ON statements */
4082 query = createPQExpBuffer();
4083 target = createPQExpBuffer();
4085 while (ncomments > 0)
4087 const char *descr = comments->descr;
4088 int objsubid = comments->objsubid;
4092 resetPQExpBuffer(target);
4093 appendPQExpBuffer(target, "%s %s", reltypename,
4094 fmtId(tbinfo->dobj.name));
4096 resetPQExpBuffer(query);
4097 appendPQExpBuffer(query, "COMMENT ON %s IS ", target->data);
4098 appendStringLiteral(query, descr, false);
4099 appendPQExpBuffer(query, ";\n");
4101 ArchiveEntry(fout, nilCatalogId, createDumpId(),
4103 tbinfo->dobj.namespace->dobj.name,
4106 false, "COMMENT", query->data, "", NULL,
4107 &(tbinfo->dobj.dumpId), 1,
4110 else if (objsubid > 0 && objsubid <= tbinfo->numatts)
4112 resetPQExpBuffer(target);
4113 appendPQExpBuffer(target, "COLUMN %s.",
4114 fmtId(tbinfo->dobj.name));
4115 appendPQExpBuffer(target, "%s",
4116 fmtId(tbinfo->attnames[objsubid - 1]));
4118 resetPQExpBuffer(query);
4119 appendPQExpBuffer(query, "COMMENT ON %s IS ", target->data);
4120 appendStringLiteral(query, descr, false);
4121 appendPQExpBuffer(query, ";\n");
4123 ArchiveEntry(fout, nilCatalogId, createDumpId(),
4125 tbinfo->dobj.namespace->dobj.name,
4128 false, "COMMENT", query->data, "", NULL,
4129 &(tbinfo->dobj.dumpId), 1,
4137 destroyPQExpBuffer(query);
4138 destroyPQExpBuffer(target);
4144 * Find the comment(s), if any, associated with the given object. All the
4145 * objsubid values associated with the given classoid/objoid are found with
4149 findComments(Archive *fout, Oid classoid, Oid objoid,
4150 CommentItem **items)
4152 /* static storage for table of comments */
4153 static CommentItem *comments = NULL;
4154 static int ncomments = -1;
4156 CommentItem *middle = NULL;
4161 /* Get comments if we didn't already */
4163 ncomments = collectComments(fout, &comments);
4166 * Pre-7.2, pg_description does not contain classoid, so
4167 * collectComments just stores a zero. If there's a collision on
4168 * object OID, well, you get duplicate comments.
4170 if (fout->remoteVersion < 70200)
4174 * Do binary search to find some item matching the object.
4177 high = &comments[ncomments - 1];
4180 middle = low + (high - low) / 2;
4182 if (classoid < middle->classoid)
4184 else if (classoid > middle->classoid)
4186 else if (objoid < middle->objoid)
4188 else if (objoid > middle->objoid)
4191 break; /* found a match */
4194 if (low > high) /* no matches */
4201 * Now determine how many items match the object. The search loop
4202 * invariant still holds: only items between low and high inclusive
4206 while (middle > low)
4208 if (classoid != middle[-1].classoid ||
4209 objoid != middle[-1].objoid)
4218 while (middle <= high)
4220 if (classoid != middle->classoid ||
4221 objoid != middle->objoid)
4231 * collectComments --
4233 * Construct a table of all comments available for database objects.
4234 * We used to do per-object queries for the comments, but it's much faster
4235 * to pull them all over at once, and on most databases the memory cost
4238 * The table is sorted by classoid/objid/objsubid for speed in lookup.
4241 collectComments(Archive *fout, CommentItem **items)
4251 CommentItem *comments;
4254 * Note we do NOT change source schema here; preserve the caller's
4258 query = createPQExpBuffer();
4260 if (fout->remoteVersion >= 70300)
4262 appendPQExpBuffer(query, "SELECT description, classoid, objoid, objsubid "
4263 "FROM pg_catalog.pg_description "
4264 "ORDER BY classoid, objoid, objsubid");
4266 else if (fout->remoteVersion >= 70200)
4268 appendPQExpBuffer(query, "SELECT description, classoid, objoid, objsubid "
4269 "FROM pg_description "
4270 "ORDER BY classoid, objoid, objsubid");
4274 /* Note: this will fail to find attribute comments in pre-7.2... */
4275 appendPQExpBuffer(query, "SELECT description, 0 as classoid, objoid, 0 as objsubid "
4276 "FROM pg_description "
4280 res = PQexec(g_conn, query->data);
4281 check_sql_result(res, g_conn, query->data, PGRES_TUPLES_OK);
4283 /* Construct lookup table containing OIDs in numeric form */
4285 i_description = PQfnumber(res, "description");
4286 i_classoid = PQfnumber(res, "classoid");
4287 i_objoid = PQfnumber(res, "objoid");
4288 i_objsubid = PQfnumber(res, "objsubid");
4290 ntups = PQntuples(res);
4292 comments = (CommentItem *) malloc(ntups * sizeof(CommentItem));
4294 for (i = 0; i < ntups; i++)
4296 comments[i].descr = PQgetvalue(res, i, i_description);
4297 comments[i].classoid = atooid(PQgetvalue(res, i, i_classoid));
4298 comments[i].objoid = atooid(PQgetvalue(res, i, i_objoid));
4299 comments[i].objsubid = atoi(PQgetvalue(res, i, i_objsubid));
4302 /* Do NOT free the PGresult since we are keeping pointers into it */
4303 destroyPQExpBuffer(query);
4310 * dumpDumpableObject
4312 * This routine and its subsidiaries are responsible for creating
4313 * ArchiveEntries (TOC objects) for each object to be dumped.
4316 dumpDumpableObject(Archive *fout, DumpableObject *dobj)
4318 switch (dobj->objType)
4321 dumpNamespace(fout, (NamespaceInfo *) dobj);
4324 dumpType(fout, (TypeInfo *) dobj);
4327 dumpFunc(fout, (FuncInfo *) dobj);
4330 dumpAgg(fout, (AggInfo *) dobj);
4333 dumpOpr(fout, (OprInfo *) dobj);
4336 dumpOpclass(fout, (OpclassInfo *) dobj);
4339 dumpConversion(fout, (ConvInfo *) dobj);
4342 dumpTable(fout, (TableInfo *) dobj);
4345 dumpAttrDef(fout, (AttrDefInfo *) dobj);
4348 dumpIndex(fout, (IndxInfo *) dobj);
4351 dumpRule(fout, (RuleInfo *) dobj);
4354 dumpTrigger(fout, (TriggerInfo *) dobj);
4357 dumpConstraint(fout, (ConstraintInfo *) dobj);
4359 case DO_FK_CONSTRAINT:
4360 dumpConstraint(fout, (ConstraintInfo *) dobj);
4363 dumpProcLang(fout, (ProcLangInfo *) dobj);
4366 dumpCast(fout, (CastInfo *) dobj);
4369 dumpTableData(fout, (TableDataInfo *) dobj);
4372 /* table rowtypes are never dumped separately */
4375 ArchiveEntry(fout, dobj->catId, dobj->dumpId,
4376 dobj->name, NULL, NULL, "",
4377 false, "BLOBS", "", "", NULL,
4386 * writes out to fout the queries to recreate a user-defined namespace
4389 dumpNamespace(Archive *fout, NamespaceInfo *nspinfo)
4395 /* skip if not to be dumped */
4396 if (!nspinfo->dump || dataOnly)
4399 /* don't dump dummy namespace from pre-7.3 source */
4400 if (strlen(nspinfo->dobj.name) == 0)
4403 q = createPQExpBuffer();
4404 delq = createPQExpBuffer();
4406 qnspname = strdup(fmtId(nspinfo->dobj.name));
4409 * Note that ownership is shown in the AUTHORIZATION clause, while the
4410 * archive entry is listed with empty owner (causing it to be emitted
4411 * with SET SESSION AUTHORIZATION DEFAULT). This seems the best way of
4412 * dealing with schemas owned by users without CREATE SCHEMA
4413 * privilege. Further hacking has to be applied for --no-owner mode,
4416 appendPQExpBuffer(delq, "DROP SCHEMA %s;\n", qnspname);
4418 appendPQExpBuffer(q, "CREATE SCHEMA %s AUTHORIZATION %s;\n",
4419 qnspname, fmtId(nspinfo->usename));
4421 ArchiveEntry(fout, nspinfo->dobj.catId, nspinfo->dobj.dumpId,
4424 strcmp(nspinfo->dobj.name, "public") == 0 ? nspinfo->usename : "",
4425 false, "SCHEMA", q->data, delq->data, NULL,
4426 nspinfo->dobj.dependencies, nspinfo->dobj.nDeps,
4429 /* Dump Schema Comments */
4430 resetPQExpBuffer(q);
4431 appendPQExpBuffer(q, "SCHEMA %s", qnspname);
4432 dumpComment(fout, q->data,
4433 NULL, nspinfo->usename,
4434 nspinfo->dobj.catId, 0, nspinfo->dobj.dumpId);
4436 dumpACL(fout, nspinfo->dobj.catId, nspinfo->dobj.dumpId, "SCHEMA",
4437 qnspname, nspinfo->dobj.name, NULL,
4438 nspinfo->usename, nspinfo->nspacl);
4442 destroyPQExpBuffer(q);
4443 destroyPQExpBuffer(delq);
4448 * writes out to fout the queries to recreate a user-defined type
4451 dumpType(Archive *fout, TypeInfo *tinfo)
4453 /* Dump only types in dumpable namespaces */
4454 if (!tinfo->dobj.namespace->dump || dataOnly)
4457 /* skip complex types, except for standalone composite types */
4458 /* (note: this test should now be unnecessary) */
4459 if (OidIsValid(tinfo->typrelid) && tinfo->typrelkind != 'c')
4462 /* skip undefined placeholder types */
4463 if (!tinfo->isDefined)
4466 /* skip all array types that start w/ underscore */
4467 if ((tinfo->dobj.name[0] == '_') &&
4468 OidIsValid(tinfo->typelem))
4471 /* Dump out in proper style */
4472 if (tinfo->typtype == 'b')
4473 dumpBaseType(fout, tinfo);
4474 else if (tinfo->typtype == 'd')
4475 dumpDomain(fout, tinfo);
4476 else if (tinfo->typtype == 'c')
4477 dumpCompositeType(fout, tinfo);
4482 * writes out to fout the queries to recreate a user-defined base type
4485 dumpBaseType(Archive *fout, TypeInfo *tinfo)
4487 PQExpBuffer q = createPQExpBuffer();
4488 PQExpBuffer delq = createPQExpBuffer();
4489 PQExpBuffer query = createPQExpBuffer();
4509 /* Set proper schema search path so regproc references list correctly */
4510 selectSourceSchema(tinfo->dobj.namespace->dobj.name);
4512 /* Fetch type-specific details */
4513 if (fout->remoteVersion >= 80000)
4515 appendPQExpBuffer(query, "SELECT typlen, "
4516 "typinput, typoutput, typreceive, typsend, "
4518 "typinput::pg_catalog.oid as typinputoid, "
4519 "typoutput::pg_catalog.oid as typoutputoid, "
4520 "typreceive::pg_catalog.oid as typreceiveoid, "
4521 "typsend::pg_catalog.oid as typsendoid, "
4522 "typanalyze::pg_catalog.oid as typanalyzeoid, "
4523 "typdelim, typdefault, typbyval, typalign, "
4525 "FROM pg_catalog.pg_type "
4526 "WHERE oid = '%u'::pg_catalog.oid",
4527 tinfo->dobj.catId.oid);
4529 else if (fout->remoteVersion >= 70400)
4531 appendPQExpBuffer(query, "SELECT typlen, "
4532 "typinput, typoutput, typreceive, typsend, "
4533 "'-' as typanalyze, "
4534 "typinput::pg_catalog.oid as typinputoid, "
4535 "typoutput::pg_catalog.oid as typoutputoid, "
4536 "typreceive::pg_catalog.oid as typreceiveoid, "
4537 "typsend::pg_catalog.oid as typsendoid, "
4538 "0 as typanalyzeoid, "
4539 "typdelim, typdefault, typbyval, typalign, "
4541 "FROM pg_catalog.pg_type "
4542 "WHERE oid = '%u'::pg_catalog.oid",
4543 tinfo->dobj.catId.oid);
4545 else if (fout->remoteVersion >= 70300)
4547 appendPQExpBuffer(query, "SELECT typlen, "
4548 "typinput, typoutput, "
4549 "'-' as typreceive, '-' as typsend, "
4550 "'-' as typanalyze, "
4551 "typinput::pg_catalog.oid as typinputoid, "
4552 "typoutput::pg_catalog.oid as typoutputoid, "
4553 "0 as typreceiveoid, 0 as typsendoid, "
4554 "0 as typanalyzeoid, "
4555 "typdelim, typdefault, typbyval, typalign, "
4557 "FROM pg_catalog.pg_type "
4558 "WHERE oid = '%u'::pg_catalog.oid",
4559 tinfo->dobj.catId.oid);
4561 else if (fout->remoteVersion >= 70100)
4564 * Note: although pre-7.3 catalogs contain typreceive and typsend,
4565 * ignore them because they are not right.
4567 appendPQExpBuffer(query, "SELECT typlen, "
4568 "typinput, typoutput, "
4569 "'-' as typreceive, '-' as typsend, "
4570 "'-' as typanalyze, "
4571 "typinput::oid as typinputoid, "
4572 "typoutput::oid as typoutputoid, "
4573 "0 as typreceiveoid, 0 as typsendoid, "
4574 "0 as typanalyzeoid, "
4575 "typdelim, typdefault, typbyval, typalign, "
4578 "WHERE oid = '%u'::oid",
4579 tinfo->dobj.catId.oid);
4583 appendPQExpBuffer(query, "SELECT typlen, "
4584 "typinput, typoutput, "
4585 "'-' as typreceive, '-' as typsend, "
4586 "'-' as typanalyze, "
4587 "typinput::oid as typinputoid, "
4588 "typoutput::oid as typoutputoid, "
4589 "0 as typreceiveoid, 0 as typsendoid, "
4590 "0 as typanalyzeoid, "
4591 "typdelim, typdefault, typbyval, typalign, "
4592 "'p'::char as typstorage "
4594 "WHERE oid = '%u'::oid",
4595 tinfo->dobj.catId.oid);
4598 res = PQexec(g_conn, query->data);
4599 check_sql_result(res, g_conn, query->data, PGRES_TUPLES_OK);
4601 /* Expecting a single result only */
4602 ntups = PQntuples(res);
4605 write_msg(NULL, "Got %d rows instead of one from: %s",
4606 ntups, query->data);
4610 typlen = PQgetvalue(res, 0, PQfnumber(res, "typlen"));
4611 typinput = PQgetvalue(res, 0, PQfnumber(res, "typinput"));
4612 typoutput = PQgetvalue(res, 0, PQfnumber(res, "typoutput"));
4613 typreceive = PQgetvalue(res, 0, PQfnumber(res, "typreceive"));
4614 typsend = PQgetvalue(res, 0, PQfnumber(res, "typsend"));
4615 typanalyze = PQgetvalue(res, 0, PQfnumber(res, "typanalyze"));
4616 typinputoid = atooid(PQgetvalue(res, 0, PQfnumber(res, "typinputoid")));
4617 typoutputoid = atooid(PQgetvalue(res, 0, PQfnumber(res, "typoutputoid")));
4618 typreceiveoid = atooid(PQgetvalue(res, 0, PQfnumber(res, "typreceiveoid")));
4619 typsendoid = atooid(PQgetvalue(res, 0, PQfnumber(res, "typsendoid")));
4620 typanalyzeoid = atooid(PQgetvalue(res, 0, PQfnumber(res, "typanalyzeoid")));
4621 typdelim = PQgetvalue(res, 0, PQfnumber(res, "typdelim"));
4622 if (PQgetisnull(res, 0, PQfnumber(res, "typdefault")))
4625 typdefault = PQgetvalue(res, 0, PQfnumber(res, "typdefault"));
4626 typbyval = PQgetvalue(res, 0, PQfnumber(res, "typbyval"));
4627 typalign = PQgetvalue(res, 0, PQfnumber(res, "typalign"));
4628 typstorage = PQgetvalue(res, 0, PQfnumber(res, "typstorage"));
4631 * DROP must be fully qualified in case same name appears in
4634 appendPQExpBuffer(delq, "DROP TYPE %s.",
4635 fmtId(tinfo->dobj.namespace->dobj.name));
4636 appendPQExpBuffer(delq, "%s CASCADE;\n",
4637 fmtId(tinfo->dobj.name));
4639 appendPQExpBuffer(q,
4640 "CREATE TYPE %s (\n"
4641 " INTERNALLENGTH = %s",
4642 fmtId(tinfo->dobj.name),
4643 (strcmp(typlen, "-1") == 0) ? "variable" : typlen);
4645 if (fout->remoteVersion >= 70300)
4647 /* regproc result is correctly quoted as of 7.3 */
4648 appendPQExpBuffer(q, ",\n INPUT = %s", typinput);
4649 appendPQExpBuffer(q, ",\n OUTPUT = %s", typoutput);
4650 if (OidIsValid(typreceiveoid))
4651 appendPQExpBuffer(q, ",\n RECEIVE = %s", typreceive);
4652 if (OidIsValid(typsendoid))
4653 appendPQExpBuffer(q, ",\n SEND = %s", typsend);
4654 if (OidIsValid(typanalyzeoid))
4655 appendPQExpBuffer(q, ",\n ANALYZE = %s", typanalyze);
4659 /* regproc delivers an unquoted name before 7.3 */
4660 /* cannot combine these because fmtId uses static result area */
4661 appendPQExpBuffer(q, ",\n INPUT = %s", fmtId(typinput));
4662 appendPQExpBuffer(q, ",\n OUTPUT = %s", fmtId(typoutput));
4663 /* no chance that receive/send/analyze need be printed */
4666 if (typdefault != NULL)
4668 appendPQExpBuffer(q, ",\n DEFAULT = ");
4669 appendStringLiteral(q, typdefault, true);
4676 /* reselect schema in case changed by function dump */
4677 selectSourceSchema(tinfo->dobj.namespace->dobj.name);
4678 elemType = getFormattedTypeName(tinfo->typelem, zeroAsOpaque);
4679 appendPQExpBuffer(q, ",\n ELEMENT = %s", elemType);
4683 if (typdelim && strcmp(typdelim, ",") != 0)
4685 appendPQExpBuffer(q, ",\n DELIMITER = ");
4686 appendStringLiteral(q, typdelim, true);
4689 if (strcmp(typalign, "c") == 0)
4690 appendPQExpBuffer(q, ",\n ALIGNMENT = char");
4691 else if (strcmp(typalign, "s") == 0)
4692 appendPQExpBuffer(q, ",\n ALIGNMENT = int2");
4693 else if (strcmp(typalign, "i") == 0)
4694 appendPQExpBuffer(q, ",\n ALIGNMENT = int4");
4695 else if (strcmp(typalign, "d") == 0)
4696 appendPQExpBuffer(q, ",\n ALIGNMENT = double");
4698 if (strcmp(typstorage, "p") == 0)
4699 appendPQExpBuffer(q, ",\n STORAGE = plain");
4700 else if (strcmp(typstorage, "e") == 0)
4701 appendPQExpBuffer(q, ",\n STORAGE = external");
4702 else if (strcmp(typstorage, "x") == 0)
4703 appendPQExpBuffer(q, ",\n STORAGE = extended");
4704 else if (strcmp(typstorage, "m") == 0)
4705 appendPQExpBuffer(q, ",\n STORAGE = main");
4707 if (strcmp(typbyval, "t") == 0)
4708 appendPQExpBuffer(q, ",\n PASSEDBYVALUE");
4710 appendPQExpBuffer(q, "\n);\n");
4712 ArchiveEntry(fout, tinfo->dobj.catId, tinfo->dobj.dumpId,
4714 tinfo->dobj.namespace->dobj.name,
4716 tinfo->usename, false,
4717 "TYPE", q->data, delq->data, NULL,
4718 tinfo->dobj.dependencies, tinfo->dobj.nDeps,
4721 /* Dump Type Comments */
4722 resetPQExpBuffer(q);
4724 appendPQExpBuffer(q, "TYPE %s", fmtId(tinfo->dobj.name));
4725 dumpComment(fout, q->data,
4726 tinfo->dobj.namespace->dobj.name, tinfo->usename,
4727 tinfo->dobj.catId, 0, tinfo->dobj.dumpId);
4730 destroyPQExpBuffer(q);
4731 destroyPQExpBuffer(delq);
4732 destroyPQExpBuffer(query);
4737 * writes out to fout the queries to recreate a user-defined domain
4740 dumpDomain(Archive *fout, TypeInfo *tinfo)
4742 PQExpBuffer q = createPQExpBuffer();
4743 PQExpBuffer delq = createPQExpBuffer();
4744 PQExpBuffer query = createPQExpBuffer();
4752 /* Set proper schema search path so type references list correctly */
4753 selectSourceSchema(tinfo->dobj.namespace->dobj.name);
4755 /* Fetch domain specific details */
4756 /* We assume here that remoteVersion must be at least 70300 */
4757 appendPQExpBuffer(query, "SELECT typnotnull, "
4758 "pg_catalog.format_type(typbasetype, typtypmod) as typdefn, "
4760 "FROM pg_catalog.pg_type "
4761 "WHERE oid = '%u'::pg_catalog.oid",
4762 tinfo->dobj.catId.oid);
4764 res = PQexec(g_conn, query->data);
4765 check_sql_result(res, g_conn, query->data, PGRES_TUPLES_OK);
4767 /* Expecting a single result only */
4768 ntups = PQntuples(res);
4771 write_msg(NULL, "Got %d rows instead of one from: %s",
4772 ntups, query->data);
4776 typnotnull = PQgetvalue(res, 0, PQfnumber(res, "typnotnull"));
4777 typdefn = PQgetvalue(res, 0, PQfnumber(res, "typdefn"));
4778 if (PQgetisnull(res, 0, PQfnumber(res, "typdefault")))
4781 typdefault = PQgetvalue(res, 0, PQfnumber(res, "typdefault"));
4783 appendPQExpBuffer(q,
4784 "CREATE DOMAIN %s AS %s",
4785 fmtId(tinfo->dobj.name),
4788 if (typnotnull[0] == 't')
4789 appendPQExpBuffer(q, " NOT NULL");
4792 appendPQExpBuffer(q, " DEFAULT %s", typdefault);
4797 * Add any CHECK constraints for the domain
4799 for (i = 0; i < tinfo->nDomChecks; i++)
4801 ConstraintInfo *domcheck = &(tinfo->domChecks[i]);
4803 if (!domcheck->separate)
4804 appendPQExpBuffer(q, "\n\tCONSTRAINT %s %s",
4805 fmtId(domcheck->dobj.name), domcheck->condef);
4808 appendPQExpBuffer(q, ";\n");
4811 * DROP must be fully qualified in case same name appears in
4814 appendPQExpBuffer(delq, "DROP DOMAIN %s.",
4815 fmtId(tinfo->dobj.namespace->dobj.name));
4816 appendPQExpBuffer(delq, "%s;\n",
4817 fmtId(tinfo->dobj.name));
4819 ArchiveEntry(fout, tinfo->dobj.catId, tinfo->dobj.dumpId,
4821 tinfo->dobj.namespace->dobj.name,
4823 tinfo->usename, false,
4824 "DOMAIN", q->data, delq->data, NULL,
4825 tinfo->dobj.dependencies, tinfo->dobj.nDeps,
4828 /* Dump Domain Comments */
4829 resetPQExpBuffer(q);
4831 appendPQExpBuffer(q, "DOMAIN %s", fmtId(tinfo->dobj.name));
4832 dumpComment(fout, q->data,
4833 tinfo->dobj.namespace->dobj.name, tinfo->usename,
4834 tinfo->dobj.catId, 0, tinfo->dobj.dumpId);
4836 destroyPQExpBuffer(q);
4837 destroyPQExpBuffer(delq);
4838 destroyPQExpBuffer(query);
4843 * writes out to fout the queries to recreate a user-defined stand-alone
4847 dumpCompositeType(Archive *fout, TypeInfo *tinfo)
4849 PQExpBuffer q = createPQExpBuffer();
4850 PQExpBuffer delq = createPQExpBuffer();
4851 PQExpBuffer query = createPQExpBuffer();
4858 /* Set proper schema search path so type references list correctly */
4859 selectSourceSchema(tinfo->dobj.namespace->dobj.name);
4861 /* Fetch type specific details */
4862 /* We assume here that remoteVersion must be at least 70300 */
4864 appendPQExpBuffer(query, "SELECT a.attname, "
4865 "pg_catalog.format_type(a.atttypid, a.atttypmod) as atttypdefn "
4866 "FROM pg_catalog.pg_type t, pg_catalog.pg_attribute a "
4867 "WHERE t.oid = '%u'::pg_catalog.oid "
4868 "AND a.attrelid = t.typrelid "
4869 "AND NOT a.attisdropped "
4870 "ORDER BY a.attnum ",
4871 tinfo->dobj.catId.oid);
4873 res = PQexec(g_conn, query->data);
4874 check_sql_result(res, g_conn, query->data, PGRES_TUPLES_OK);
4876 /* Expecting at least a single result */
4877 ntups = PQntuples(res);
4880 write_msg(NULL, "query yielded no rows: %s\n", query->data);
4884 i_attname = PQfnumber(res, "attname");
4885 i_atttypdefn = PQfnumber(res, "atttypdefn");
4887 appendPQExpBuffer(q, "CREATE TYPE %s AS (",
4888 fmtId(tinfo->dobj.name));
4890 for (i = 0; i < ntups; i++)
4895 attname = PQgetvalue(res, i, i_attname);
4896 atttypdefn = PQgetvalue(res, i, i_atttypdefn);
4898 appendPQExpBuffer(q, "\n\t%s %s", fmtId(attname), atttypdefn);
4900 appendPQExpBuffer(q, ",");
4902 appendPQExpBuffer(q, "\n);\n");
4905 * DROP must be fully qualified in case same name appears in
4908 appendPQExpBuffer(delq, "DROP TYPE %s.",
4909 fmtId(tinfo->dobj.namespace->dobj.name));
4910 appendPQExpBuffer(delq, "%s;\n",
4911 fmtId(tinfo->dobj.name));
4913 ArchiveEntry(fout, tinfo->dobj.catId, tinfo->dobj.dumpId,
4915 tinfo->dobj.namespace->dobj.name,
4917 tinfo->usename, false,
4918 "TYPE", q->data, delq->data, NULL,
4919 tinfo->dobj.dependencies, tinfo->dobj.nDeps,
4923 /* Dump Type Comments */
4924 resetPQExpBuffer(q);
4926 appendPQExpBuffer(q, "TYPE %s", fmtId(tinfo->dobj.name));
4927 dumpComment(fout, q->data,
4928 tinfo->dobj.namespace->dobj.name, tinfo->usename,
4929 tinfo->dobj.catId, 0, tinfo->dobj.dumpId);
4932 destroyPQExpBuffer(q);
4933 destroyPQExpBuffer(delq);
4934 destroyPQExpBuffer(query);
4939 * writes out to fout the queries to recreate a user-defined
4940 * procedural language
4943 dumpProcLang(Archive *fout, ProcLangInfo *plang)
4949 FuncInfo *validatorInfo = NULL;
4955 * Current theory is to dump PLs iff their underlying functions will
4956 * be dumped (are in a dumpable namespace, or have a non-system OID in
4957 * pre-7.3 databases). Actually, we treat the PL itself as being in
4958 * the underlying function's namespace, though it isn't really. This
4959 * avoids searchpath problems for the HANDLER clause.
4961 * If the underlying function is in the pg_catalog namespace, we won't
4962 * have loaded it into finfo[] at all; therefore, treat failure to
4963 * find it in finfo[] as indicating we shouldn't dump it, not as an
4964 * error condition. Ditto for the validator.
4967 funcInfo = findFuncByOid(plang->lanplcallfoid);
4968 if (funcInfo == NULL)
4971 if (!funcInfo->dobj.namespace->dump)
4974 if (OidIsValid(plang->lanvalidator))
4976 validatorInfo = findFuncByOid(plang->lanvalidator);
4977 if (validatorInfo == NULL)
4981 defqry = createPQExpBuffer();
4982 delqry = createPQExpBuffer();
4984 qlanname = strdup(fmtId(plang->dobj.name));
4986 appendPQExpBuffer(delqry, "DROP PROCEDURAL LANGUAGE %s;\n",
4989 appendPQExpBuffer(defqry, "CREATE %sPROCEDURAL LANGUAGE %s",
4990 plang->lanpltrusted ? "TRUSTED " : "",
4992 appendPQExpBuffer(defqry, " HANDLER %s",
4993 fmtId(funcInfo->dobj.name));
4994 if (OidIsValid(plang->lanvalidator))
4996 appendPQExpBuffer(defqry, " VALIDATOR ");
4997 /* Cope with possibility that validator is in different schema */
4998 if (validatorInfo->dobj.namespace != funcInfo->dobj.namespace)
4999 appendPQExpBuffer(defqry, "%s.",
5000 fmtId(validatorInfo->dobj.namespace->dobj.name));
5001 appendPQExpBuffer(defqry, "%s",
5002 fmtId(validatorInfo->dobj.name));
5004 appendPQExpBuffer(defqry, ";\n");
5006 ArchiveEntry(fout, plang->dobj.catId, plang->dobj.dumpId,
5008 funcInfo->dobj.namespace->dobj.name, NULL, "",
5009 false, "PROCEDURAL LANGUAGE",
5010 defqry->data, delqry->data, NULL,
5011 plang->dobj.dependencies, plang->dobj.nDeps,
5014 /* Dump Proc Lang Comments */
5015 resetPQExpBuffer(defqry);
5017 appendPQExpBuffer(defqry, "LANGUAGE %s", qlanname);
5018 dumpComment(fout, defqry->data,
5020 plang->dobj.catId, 0, plang->dobj.dumpId);
5022 if (plang->lanpltrusted)
5023 dumpACL(fout, plang->dobj.catId, plang->dobj.dumpId, "LANGUAGE",
5024 qlanname, plang->dobj.name,
5025 funcInfo->dobj.namespace->dobj.name,
5026 NULL, plang->lanacl);
5030 destroyPQExpBuffer(defqry);
5031 destroyPQExpBuffer(delqry);
5035 * format_function_signature: generate function name and argument list
5037 * The argument type names are qualified if needed. The function name
5038 * is never qualified.
5040 * argnames may be NULL if no names are available.
5043 format_function_signature(FuncInfo *finfo, char **argnames,
5049 initPQExpBuffer(&fn);
5051 appendPQExpBuffer(&fn, "%s(", fmtId(finfo->dobj.name));
5053 appendPQExpBuffer(&fn, "%s(", finfo->dobj.name);
5054 for (j = 0; j < finfo->nargs; j++)
5059 typname = getFormattedTypeName(finfo->argtypes[j], zeroAsOpaque);
5061 argname = argnames ? argnames[j] : (char *) NULL;
5062 if (argname && argname[0] == '\0')
5065 appendPQExpBuffer(&fn, "%s%s%s%s",
5066 (j > 0) ? ", " : "",
5067 argname ? fmtId(argname) : "",
5072 appendPQExpBuffer(&fn, ")");
5079 * dump out one function
5082 dumpFunc(Archive *fout, FuncInfo *finfo)
5101 char **argnamearray = NULL;
5103 /* Dump only funcs in dumpable namespaces */
5104 if (!finfo->dobj.namespace->dump || dataOnly)
5107 query = createPQExpBuffer();
5108 q = createPQExpBuffer();
5109 delqry = createPQExpBuffer();
5110 asPart = createPQExpBuffer();
5112 /* Set proper schema search path so type references list correctly */
5113 selectSourceSchema(finfo->dobj.namespace->dobj.name);
5115 /* Fetch function-specific details */
5116 if (g_fout->remoteVersion >= 80000)
5118 appendPQExpBuffer(query,
5119 "SELECT proretset, prosrc, probin, "
5121 "provolatile, proisstrict, prosecdef, "
5122 "(SELECT lanname FROM pg_catalog.pg_language WHERE oid = prolang) as lanname "
5123 "FROM pg_catalog.pg_proc "
5124 "WHERE oid = '%u'::pg_catalog.oid",
5125 finfo->dobj.catId.oid);
5127 else if (g_fout->remoteVersion >= 70300)
5129 appendPQExpBuffer(query,
5130 "SELECT proretset, prosrc, probin, "
5131 "null::text as proargnames, "
5132 "provolatile, proisstrict, prosecdef, "
5133 "(SELECT lanname FROM pg_catalog.pg_language WHERE oid = prolang) as lanname "
5134 "FROM pg_catalog.pg_proc "
5135 "WHERE oid = '%u'::pg_catalog.oid",
5136 finfo->dobj.catId.oid);
5138 else if (g_fout->remoteVersion >= 70100)
5140 appendPQExpBuffer(query,
5141 "SELECT proretset, prosrc, probin, "
5142 "null::text as proargnames, "
5143 "case when proiscachable then 'i' else 'v' end as provolatile, "
5145 "'f'::boolean as prosecdef, "
5146 "(SELECT lanname FROM pg_language WHERE oid = prolang) as lanname "
5148 "WHERE oid = '%u'::oid",
5149 finfo->dobj.catId.oid);
5153 appendPQExpBuffer(query,
5154 "SELECT proretset, prosrc, probin, "
5155 "null::text as proargnames, "
5156 "case when proiscachable then 'i' else 'v' end as provolatile, "
5157 "'f'::boolean as proisstrict, "
5158 "'f'::boolean as prosecdef, "
5159 "(SELECT lanname FROM pg_language WHERE oid = prolang) as lanname "
5161 "WHERE oid = '%u'::oid",
5162 finfo->dobj.catId.oid);
5165 res = PQexec(g_conn, query->data);
5166 check_sql_result(res, g_conn, query->data, PGRES_TUPLES_OK);
5168 /* Expecting a single result only */
5169 ntups = PQntuples(res);
5172 write_msg(NULL, "Got %d rows instead of one from: %s",
5173 ntups, query->data);
5177 proretset = PQgetvalue(res, 0, PQfnumber(res, "proretset"));
5178 prosrc = PQgetvalue(res, 0, PQfnumber(res, "prosrc"));
5179 probin = PQgetvalue(res, 0, PQfnumber(res, "probin"));
5180 proargnames = PQgetvalue(res, 0, PQfnumber(res, "proargnames"));
5181 provolatile = PQgetvalue(res, 0, PQfnumber(res, "provolatile"));
5182 proisstrict = PQgetvalue(res, 0, PQfnumber(res, "proisstrict"));
5183 prosecdef = PQgetvalue(res, 0, PQfnumber(res, "prosecdef"));
5184 lanname = PQgetvalue(res, 0, PQfnumber(res, "lanname"));
5187 * See backend/commands/define.c for details of how the 'AS' clause is
5190 if (strcmp(probin, "-") != 0)
5192 appendPQExpBuffer(asPart, "AS ");
5193 appendStringLiteral(asPart, probin, true);
5194 if (strcmp(prosrc, "-") != 0)
5196 appendPQExpBuffer(asPart, ", ");
5199 * where we have bin, use dollar quoting if allowed and src
5200 * contains quote or backslash; else use regular quoting.
5202 if (disable_dollar_quoting)
5203 appendStringLiteral(asPart, prosrc, false);
5205 appendStringLiteralDQOpt(asPart, prosrc, false, NULL);
5210 if (strcmp(prosrc, "-") != 0)
5212 appendPQExpBuffer(asPart, "AS ");
5213 /* with no bin, dollar quote src unconditionally if allowed */
5214 if (disable_dollar_quoting)
5215 appendStringLiteral(asPart, prosrc, false);
5217 appendStringLiteralDQ(asPart, prosrc, NULL);
5221 if (proargnames && *proargnames)
5225 if (!parsePGArray(proargnames, &argnamearray, &nitems) ||
5226 nitems != finfo->nargs)
5228 write_msg(NULL, "WARNING: could not parse proargnames array\n");
5231 argnamearray = NULL;
5235 funcsig = format_function_signature(finfo, argnamearray, true);
5236 funcsig_tag = format_function_signature(finfo, NULL, false);
5239 * DROP must be fully qualified in case same name appears in
5242 appendPQExpBuffer(delqry, "DROP FUNCTION %s.%s;\n",
5243 fmtId(finfo->dobj.namespace->dobj.name),
5246 rettypename = getFormattedTypeName(finfo->prorettype, zeroAsOpaque);
5248 appendPQExpBuffer(q, "CREATE FUNCTION %s ", funcsig);
5249 appendPQExpBuffer(q, "RETURNS %s%s\n %s\n LANGUAGE %s",
5250 (proretset[0] == 't') ? "SETOF " : "",
5257 if (provolatile[0] != PROVOLATILE_VOLATILE)
5259 if (provolatile[0] == PROVOLATILE_IMMUTABLE)
5260 appendPQExpBuffer(q, " IMMUTABLE");
5261 else if (provolatile[0] == PROVOLATILE_STABLE)
5262 appendPQExpBuffer(q, " STABLE");
5263 else if (provolatile[0] != PROVOLATILE_VOLATILE)
5265 write_msg(NULL, "unrecognized provolatile value for function \"%s\"\n",
5271 if (proisstrict[0] == 't')
5272 appendPQExpBuffer(q, " STRICT");
5274 if (prosecdef[0] == 't')
5275 appendPQExpBuffer(q, " SECURITY DEFINER");
5277 appendPQExpBuffer(q, ";\n");
5279 ArchiveEntry(fout, finfo->dobj.catId, finfo->dobj.dumpId,
5281 finfo->dobj.namespace->dobj.name,
5283 finfo->usename, false,
5284 "FUNCTION", q->data, delqry->data, NULL,
5285 finfo->dobj.dependencies, finfo->dobj.nDeps,
5288 /* Dump Function Comments */
5289 resetPQExpBuffer(q);
5290 appendPQExpBuffer(q, "FUNCTION %s", funcsig);
5291 dumpComment(fout, q->data,
5292 finfo->dobj.namespace->dobj.name, finfo->usename,
5293 finfo->dobj.catId, 0, finfo->dobj.dumpId);
5295 dumpACL(fout, finfo->dobj.catId, finfo->dobj.dumpId, "FUNCTION",
5296 funcsig, funcsig_tag,
5297 finfo->dobj.namespace->dobj.name,
5298 finfo->usename, finfo->proacl);
5302 destroyPQExpBuffer(query);
5303 destroyPQExpBuffer(q);
5304 destroyPQExpBuffer(delqry);
5305 destroyPQExpBuffer(asPart);
5314 * Dump a user-defined cast
5317 dumpCast(Archive *fout, CastInfo *cast)
5321 PQExpBuffer castsig;
5322 FuncInfo *funcInfo = NULL;
5323 TypeInfo *sourceInfo;
5324 TypeInfo *targetInfo;
5329 if (OidIsValid(cast->castfunc))
5331 funcInfo = findFuncByOid(cast->castfunc);
5332 if (funcInfo == NULL)
5337 * As per discussion we dump casts if one or more of the underlying
5338 * objects (the conversion function and the two data types) are not
5339 * builtin AND if all of the non-builtin objects namespaces are
5340 * included in the dump. Builtin meaning, the namespace name does not
5343 sourceInfo = findTypeByOid(cast->castsource);
5344 targetInfo = findTypeByOid(cast->casttarget);
5346 if (sourceInfo == NULL || targetInfo == NULL)
5350 * Skip this cast if all objects are from pg_
5352 if ((funcInfo == NULL ||
5353 strncmp(funcInfo->dobj.namespace->dobj.name, "pg_", 3) == 0) &&
5354 strncmp(sourceInfo->dobj.namespace->dobj.name, "pg_", 3) == 0 &&
5355 strncmp(targetInfo->dobj.namespace->dobj.name, "pg_", 3) == 0)
5359 * Skip cast if function isn't from pg_ and that namespace is not
5363 strncmp(funcInfo->dobj.namespace->dobj.name, "pg_", 3) != 0 &&
5364 !funcInfo->dobj.namespace->dump)
5368 * Same for the Source type
5370 if (strncmp(sourceInfo->dobj.namespace->dobj.name, "pg_", 3) != 0 &&
5371 !sourceInfo->dobj.namespace->dump)
5375 * and the target type.
5377 if (strncmp(targetInfo->dobj.namespace->dobj.name, "pg_", 3) != 0 &&
5378 !targetInfo->dobj.namespace->dump)
5381 /* Make sure we are in proper schema (needed for getFormattedTypeName) */
5382 selectSourceSchema("pg_catalog");
5384 defqry = createPQExpBuffer();
5385 delqry = createPQExpBuffer();
5386 castsig = createPQExpBuffer();
5388 appendPQExpBuffer(delqry, "DROP CAST (%s AS %s);\n",
5389 getFormattedTypeName(cast->castsource, zeroAsNone),
5390 getFormattedTypeName(cast->casttarget, zeroAsNone));
5392 appendPQExpBuffer(defqry, "CREATE CAST (%s AS %s) ",
5393 getFormattedTypeName(cast->castsource, zeroAsNone),
5394 getFormattedTypeName(cast->casttarget, zeroAsNone));
5396 if (!OidIsValid(cast->castfunc))
5397 appendPQExpBuffer(defqry, "WITHOUT FUNCTION");
5401 * Always qualify the function name, in case it is not in
5402 * pg_catalog schema (format_function_signature won't qualify it).
5404 appendPQExpBuffer(defqry, "WITH FUNCTION %s.",
5405 fmtId(funcInfo->dobj.namespace->dobj.name));
5406 appendPQExpBuffer(defqry, "%s",
5407 format_function_signature(funcInfo, NULL, true));
5410 if (cast->castcontext == 'a')
5411 appendPQExpBuffer(defqry, " AS ASSIGNMENT");
5412 else if (cast->castcontext == 'i')
5413 appendPQExpBuffer(defqry, " AS IMPLICIT");
5414 appendPQExpBuffer(defqry, ";\n");
5416 appendPQExpBuffer(castsig, "CAST (%s AS %s)",
5417 getFormattedTypeName(cast->castsource, zeroAsNone),
5418 getFormattedTypeName(cast->casttarget, zeroAsNone));
5420 ArchiveEntry(fout, cast->dobj.catId, cast->dobj.dumpId,
5422 "pg_catalog", NULL, "",
5423 false, "CAST", defqry->data, delqry->data, NULL,
5424 cast->dobj.dependencies, cast->dobj.nDeps,
5427 /* Dump Cast Comments */
5428 resetPQExpBuffer(defqry);
5429 appendPQExpBuffer(defqry, "CAST (%s AS %s)",
5430 getFormattedTypeName(cast->castsource, zeroAsNone),
5431 getFormattedTypeName(cast->casttarget, zeroAsNone));
5432 dumpComment(fout, defqry->data,
5434 cast->dobj.catId, 0, cast->dobj.dumpId);
5436 destroyPQExpBuffer(defqry);
5437 destroyPQExpBuffer(delqry);
5438 destroyPQExpBuffer(castsig);
5443 * write out a single operator definition
5446 dumpOpr(Archive *fout, OprInfo *oprinfo)
5452 PQExpBuffer details;
5483 /* Dump only operators in dumpable namespaces */
5484 if (!oprinfo->dobj.namespace->dump || dataOnly)
5488 * some operators are invalid because they were the result of user
5489 * defining operators before commutators exist
5491 if (!OidIsValid(oprinfo->oprcode))
5494 query = createPQExpBuffer();
5495 q = createPQExpBuffer();
5496 delq = createPQExpBuffer();
5497 oprid = createPQExpBuffer();
5498 details = createPQExpBuffer();
5500 /* Make sure we are in proper schema so regoperator works correctly */
5501 selectSourceSchema(oprinfo->dobj.namespace->dobj.name);
5503 if (g_fout->remoteVersion >= 70300)
5505 appendPQExpBuffer(query, "SELECT oprkind, "
5506 "oprcode::pg_catalog.regprocedure, "
5507 "oprleft::pg_catalog.regtype, "
5508 "oprright::pg_catalog.regtype, "
5509 "oprcom::pg_catalog.regoperator, "
5510 "oprnegate::pg_catalog.regoperator, "
5511 "oprrest::pg_catalog.regprocedure, "
5512 "oprjoin::pg_catalog.regprocedure, "
5514 "oprlsortop::pg_catalog.regoperator, "
5515 "oprrsortop::pg_catalog.regoperator, "
5516 "oprltcmpop::pg_catalog.regoperator, "
5517 "oprgtcmpop::pg_catalog.regoperator "
5518 "from pg_catalog.pg_operator "
5519 "where oid = '%u'::pg_catalog.oid",
5520 oprinfo->dobj.catId.oid);
5522 else if (g_fout->remoteVersion >= 70100)
5524 appendPQExpBuffer(query, "SELECT oprkind, oprcode, "
5525 "CASE WHEN oprleft = 0 THEN '-' "
5526 "ELSE format_type(oprleft, NULL) END as oprleft, "
5527 "CASE WHEN oprright = 0 THEN '-' "
5528 "ELSE format_type(oprright, NULL) END as oprright, "
5529 "oprcom, oprnegate, oprrest, oprjoin, "
5530 "oprcanhash, oprlsortop, oprrsortop, "
5531 "0 as oprltcmpop, 0 as oprgtcmpop "
5533 "where oid = '%u'::oid",
5534 oprinfo->dobj.catId.oid);
5538 appendPQExpBuffer(query, "SELECT oprkind, oprcode, "
5539 "CASE WHEN oprleft = 0 THEN '-'::name "
5540 "ELSE (select typname from pg_type where oid = oprleft) END as oprleft, "
5541 "CASE WHEN oprright = 0 THEN '-'::name "
5542 "ELSE (select typname from pg_type where oid = oprright) END as oprright, "
5543 "oprcom, oprnegate, oprrest, oprjoin, "
5544 "oprcanhash, oprlsortop, oprrsortop, "
5545 "0 as oprltcmpop, 0 as oprgtcmpop "
5547 "where oid = '%u'::oid",
5548 oprinfo->dobj.catId.oid);
5551 res = PQexec(g_conn, query->data);
5552 check_sql_result(res, g_conn, query->data, PGRES_TUPLES_OK);
5554 /* Expecting a single result only */
5555 ntups = PQntuples(res);
5558 write_msg(NULL, "Got %d rows instead of one from: %s",
5559 ntups, query->data);
5563 i_oprkind = PQfnumber(res, "oprkind");
5564 i_oprcode = PQfnumber(res, "oprcode");
5565 i_oprleft = PQfnumber(res, "oprleft");
5566 i_oprright = PQfnumber(res, "oprright");
5567 i_oprcom = PQfnumber(res, "oprcom");
5568 i_oprnegate = PQfnumber(res, "oprnegate");
5569 i_oprrest = PQfnumber(res, "oprrest");
5570 i_oprjoin = PQfnumber(res, "oprjoin");
5571 i_oprcanhash = PQfnumber(res, "oprcanhash");
5572 i_oprlsortop = PQfnumber(res, "oprlsortop");
5573 i_oprrsortop = PQfnumber(res, "oprrsortop");
5574 i_oprltcmpop = PQfnumber(res, "oprltcmpop");
5575 i_oprgtcmpop = PQfnumber(res, "oprgtcmpop");
5577 oprkind = PQgetvalue(res, 0, i_oprkind);
5578 oprcode = PQgetvalue(res, 0, i_oprcode);
5579 oprleft = PQgetvalue(res, 0, i_oprleft);
5580 oprright = PQgetvalue(res, 0, i_oprright);
5581 oprcom = PQgetvalue(res, 0, i_oprcom);
5582 oprnegate = PQgetvalue(res, 0, i_oprnegate);
5583 oprrest = PQgetvalue(res, 0, i_oprrest);
5584 oprjoin = PQgetvalue(res, 0, i_oprjoin);
5585 oprcanhash = PQgetvalue(res, 0, i_oprcanhash);
5586 oprlsortop = PQgetvalue(res, 0, i_oprlsortop);
5587 oprrsortop = PQgetvalue(res, 0, i_oprrsortop);
5588 oprltcmpop = PQgetvalue(res, 0, i_oprltcmpop);
5589 oprgtcmpop = PQgetvalue(res, 0, i_oprgtcmpop);
5591 appendPQExpBuffer(details, " PROCEDURE = %s",
5592 convertRegProcReference(oprcode));
5594 appendPQExpBuffer(oprid, "%s (",
5595 oprinfo->dobj.name);
5598 * right unary means there's a left arg and left unary means there's a
5601 if (strcmp(oprkind, "r") == 0 ||
5602 strcmp(oprkind, "b") == 0)
5604 if (g_fout->remoteVersion >= 70100)
5607 name = fmtId(oprleft);
5608 appendPQExpBuffer(details, ",\n LEFTARG = %s", name);
5609 appendPQExpBuffer(oprid, "%s", name);
5612 appendPQExpBuffer(oprid, "NONE");
5614 if (strcmp(oprkind, "l") == 0 ||
5615 strcmp(oprkind, "b") == 0)
5617 if (g_fout->remoteVersion >= 70100)
5620 name = fmtId(oprright);
5621 appendPQExpBuffer(details, ",\n RIGHTARG = %s", name);
5622 appendPQExpBuffer(oprid, ", %s)", name);
5625 appendPQExpBuffer(oprid, ", NONE)");
5627 name = convertOperatorReference(oprcom);
5629 appendPQExpBuffer(details, ",\n COMMUTATOR = %s", name);
5631 name = convertOperatorReference(oprnegate);
5633 appendPQExpBuffer(details, ",\n NEGATOR = %s", name);
5635 if (strcmp(oprcanhash, "t") == 0)
5636 appendPQExpBuffer(details, ",\n HASHES");
5638 name = convertRegProcReference(oprrest);
5640 appendPQExpBuffer(details, ",\n RESTRICT = %s", name);
5642 name = convertRegProcReference(oprjoin);
5644 appendPQExpBuffer(details, ",\n JOIN = %s", name);
5646 name = convertOperatorReference(oprlsortop);
5648 appendPQExpBuffer(details, ",\n SORT1 = %s", name);
5650 name = convertOperatorReference(oprrsortop);
5652 appendPQExpBuffer(details, ",\n SORT2 = %s", name);
5654 name = convertOperatorReference(oprltcmpop);
5656 appendPQExpBuffer(details, ",\n LTCMP = %s", name);
5658 name = convertOperatorReference(oprgtcmpop);
5660 appendPQExpBuffer(details, ",\n GTCMP = %s", name);
5663 * DROP must be fully qualified in case same name appears in
5666 appendPQExpBuffer(delq, "DROP OPERATOR %s.%s;\n",
5667 fmtId(oprinfo->dobj.namespace->dobj.name),
5670 appendPQExpBuffer(q, "CREATE OPERATOR %s (\n%s\n);\n",
5671 oprinfo->dobj.name, details->data);
5673 ArchiveEntry(fout, oprinfo->dobj.catId, oprinfo->dobj.dumpId,
5675 oprinfo->dobj.namespace->dobj.name,
5678 false, "OPERATOR", q->data, delq->data, NULL,
5679 oprinfo->dobj.dependencies, oprinfo->dobj.nDeps,
5682 /* Dump Operator Comments */
5683 resetPQExpBuffer(q);
5684 appendPQExpBuffer(q, "OPERATOR %s", oprid->data);
5685 dumpComment(fout, q->data,
5686 oprinfo->dobj.namespace->dobj.name, oprinfo->usename,
5687 oprinfo->dobj.catId, 0, oprinfo->dobj.dumpId);
5691 destroyPQExpBuffer(query);
5692 destroyPQExpBuffer(q);
5693 destroyPQExpBuffer(delq);
5694 destroyPQExpBuffer(oprid);
5695 destroyPQExpBuffer(details);
5699 * Convert a function reference obtained from pg_operator
5701 * Returns what to print, or NULL if function references is InvalidOid
5703 * In 7.3 the input is a REGPROCEDURE display; we have to strip the
5704 * argument-types part. In prior versions, the input is a REGPROC display.
5707 convertRegProcReference(const char *proc)
5709 /* In all cases "-" means a null reference */
5710 if (strcmp(proc, "-") == 0)
5713 if (g_fout->remoteVersion >= 70300)
5719 name = strdup(proc);
5720 /* find non-double-quoted left paren */
5722 for (paren = name; *paren; paren++)
5724 if (*paren == '(' && !inquote)
5735 /* REGPROC before 7.3 does not quote its result */
5740 * Convert an operator cross-reference obtained from pg_operator
5742 * Returns what to print, or NULL to print nothing
5744 * In 7.3 the input is a REGOPERATOR display; we have to strip the
5745 * argument-types part. In prior versions, the input is just a
5746 * numeric OID, which we search our operator list for.
5749 convertOperatorReference(const char *opr)
5753 /* In all cases "0" means a null reference */
5754 if (strcmp(opr, "0") == 0)
5757 if (g_fout->remoteVersion >= 70300)
5764 /* find non-double-quoted left paren */
5766 for (paren = name; *paren; paren++)
5768 if (*paren == '(' && !inquote)
5779 oprInfo = findOprByOid(atooid(opr));
5780 if (oprInfo == NULL)
5782 write_msg(NULL, "WARNING: could not find operator with OID %s\n",
5786 return oprInfo->dobj.name;
5791 * write out a single operator class definition
5794 dumpOpclass(Archive *fout, OpclassInfo *opcinfo)
5822 /* Dump only opclasses in dumpable namespaces */
5823 if (!opcinfo->dobj.namespace->dump || dataOnly)
5827 * XXX currently we do not implement dumping of operator classes from
5828 * pre-7.3 databases. This could be done but it seems not worth the
5831 if (g_fout->remoteVersion < 70300)
5834 query = createPQExpBuffer();
5835 q = createPQExpBuffer();
5836 delq = createPQExpBuffer();
5838 /* Make sure we are in proper schema so regoperator works correctly */
5839 selectSourceSchema(opcinfo->dobj.namespace->dobj.name);
5841 /* Get additional fields from the pg_opclass row */
5842 appendPQExpBuffer(query, "SELECT opcintype::pg_catalog.regtype, "
5843 "opckeytype::pg_catalog.regtype, "
5845 "(SELECT amname FROM pg_catalog.pg_am WHERE oid = opcamid) AS amname "
5846 "FROM pg_catalog.pg_opclass "
5847 "WHERE oid = '%u'::pg_catalog.oid",
5848 opcinfo->dobj.catId.oid);
5850 res = PQexec(g_conn, query->data);
5851 check_sql_result(res, g_conn, query->data, PGRES_TUPLES_OK);
5853 /* Expecting a single result only */
5854 ntups = PQntuples(res);
5857 write_msg(NULL, "Got %d rows instead of one from: %s",
5858 ntups, query->data);
5862 i_opcintype = PQfnumber(res, "opcintype");
5863 i_opckeytype = PQfnumber(res, "opckeytype");
5864 i_opcdefault = PQfnumber(res, "opcdefault");
5865 i_amname = PQfnumber(res, "amname");
5867 opcintype = PQgetvalue(res, 0, i_opcintype);
5868 opckeytype = PQgetvalue(res, 0, i_opckeytype);
5869 opcdefault = PQgetvalue(res, 0, i_opcdefault);
5870 /* amname will still be needed after we PQclear res */
5871 amname = strdup(PQgetvalue(res, 0, i_amname));
5874 * DROP must be fully qualified in case same name appears in
5877 appendPQExpBuffer(delq, "DROP OPERATOR CLASS %s",
5878 fmtId(opcinfo->dobj.namespace->dobj.name));
5879 appendPQExpBuffer(delq, ".%s",
5880 fmtId(opcinfo->dobj.name));
5881 appendPQExpBuffer(delq, " USING %s;\n",
5884 /* Build the fixed portion of the CREATE command */
5885 appendPQExpBuffer(q, "CREATE OPERATOR CLASS %s\n ",
5886 fmtId(opcinfo->dobj.name));
5887 if (strcmp(opcdefault, "t") == 0)
5888 appendPQExpBuffer(q, "DEFAULT ");
5889 appendPQExpBuffer(q, "FOR TYPE %s USING %s AS\n ",
5895 if (strcmp(opckeytype, "-") != 0)
5897 appendPQExpBuffer(q, "STORAGE %s",
5905 * Now fetch and print the OPERATOR entries (pg_amop rows).
5907 resetPQExpBuffer(query);
5909 appendPQExpBuffer(query, "SELECT amopstrategy, amopreqcheck, "
5910 "amopopr::pg_catalog.regoperator "
5911 "FROM pg_catalog.pg_amop "
5912 "WHERE amopclaid = '%u'::pg_catalog.oid "
5913 "ORDER BY amopstrategy",
5914 opcinfo->dobj.catId.oid);
5916 res = PQexec(g_conn, query->data);
5917 check_sql_result(res, g_conn, query->data, PGRES_TUPLES_OK);
5919 ntups = PQntuples(res);
5921 i_amopstrategy = PQfnumber(res, "amopstrategy");
5922 i_amopreqcheck = PQfnumber(res, "amopreqcheck");
5923 i_amopopr = PQfnumber(res, "amopopr");
5925 for (i = 0; i < ntups; i++)
5927 amopstrategy = PQgetvalue(res, i, i_amopstrategy);
5928 amopreqcheck = PQgetvalue(res, i, i_amopreqcheck);
5929 amopopr = PQgetvalue(res, i, i_amopopr);
5932 appendPQExpBuffer(q, " ,\n ");
5934 appendPQExpBuffer(q, "OPERATOR %s %s",
5935 amopstrategy, amopopr);
5936 if (strcmp(amopreqcheck, "t") == 0)
5937 appendPQExpBuffer(q, " RECHECK");
5945 * Now fetch and print the FUNCTION entries (pg_amproc rows).
5947 resetPQExpBuffer(query);
5949 appendPQExpBuffer(query, "SELECT amprocnum, "
5950 "amproc::pg_catalog.regprocedure "
5951 "FROM pg_catalog.pg_amproc "
5952 "WHERE amopclaid = '%u'::pg_catalog.oid "
5953 "ORDER BY amprocnum",
5954 opcinfo->dobj.catId.oid);
5956 res = PQexec(g_conn, query->data);
5957 check_sql_result(res, g_conn, query->data, PGRES_TUPLES_OK);
5959 ntups = PQntuples(res);
5961 i_amprocnum = PQfnumber(res, "amprocnum");
5962 i_amproc = PQfnumber(res, "amproc");
5964 for (i = 0; i < ntups; i++)
5966 amprocnum = PQgetvalue(res, i, i_amprocnum);
5967 amproc = PQgetvalue(res, i, i_amproc);
5970 appendPQExpBuffer(q, " ,\n ");
5972 appendPQExpBuffer(q, "FUNCTION %s %s",
5980 appendPQExpBuffer(q, ";\n");
5982 ArchiveEntry(fout, opcinfo->dobj.catId, opcinfo->dobj.dumpId,
5984 opcinfo->dobj.namespace->dobj.name,
5987 false, "OPERATOR CLASS", q->data, delq->data, NULL,
5988 opcinfo->dobj.dependencies, opcinfo->dobj.nDeps,
5991 /* Dump Operator Class Comments */
5992 resetPQExpBuffer(q);
5993 appendPQExpBuffer(q, "OPERATOR CLASS %s",
5994 fmtId(opcinfo->dobj.name));
5995 appendPQExpBuffer(q, " USING %s",
5997 dumpComment(fout, q->data,
5998 NULL, opcinfo->usename,
5999 opcinfo->dobj.catId, 0, opcinfo->dobj.dumpId);
6002 destroyPQExpBuffer(query);
6003 destroyPQExpBuffer(q);
6004 destroyPQExpBuffer(delq);
6009 * write out a single conversion definition
6012 dumpConversion(Archive *fout, ConvInfo *convinfo)
6017 PQExpBuffer details;
6021 int i_conforencoding;
6022 int i_contoencoding;
6025 const char *conname;
6026 const char *conforencoding;
6027 const char *contoencoding;
6028 const char *conproc;
6031 /* Dump only conversions in dumpable namespaces */
6032 if (!convinfo->dobj.namespace->dump || dataOnly)
6035 query = createPQExpBuffer();
6036 q = createPQExpBuffer();
6037 delq = createPQExpBuffer();
6038 details = createPQExpBuffer();
6040 /* Make sure we are in proper schema */
6041 selectSourceSchema(convinfo->dobj.namespace->dobj.name);
6043 /* Get conversion-specific details */
6044 appendPQExpBuffer(query, "SELECT conname, "
6045 "pg_catalog.pg_encoding_to_char(conforencoding) AS conforencoding, "
6046 "pg_catalog.pg_encoding_to_char(contoencoding) AS contoencoding, "
6047 "conproc, condefault "
6048 "FROM pg_catalog.pg_conversion c "
6049 "WHERE c.oid = '%u'::pg_catalog.oid",
6050 convinfo->dobj.catId.oid);
6052 res = PQexec(g_conn, query->data);
6053 check_sql_result(res, g_conn, query->data, PGRES_TUPLES_OK);
6055 /* Expecting a single result only */
6056 ntups = PQntuples(res);
6059 write_msg(NULL, "Got %d rows instead of one from: %s",
6060 ntups, query->data);
6064 i_conname = PQfnumber(res, "conname");
6065 i_conforencoding = PQfnumber(res, "conforencoding");
6066 i_contoencoding = PQfnumber(res, "contoencoding");
6067 i_conproc = PQfnumber(res, "conproc");
6068 i_condefault = PQfnumber(res, "condefault");
6070 conname = PQgetvalue(res, 0, i_conname);
6071 conforencoding = PQgetvalue(res, 0, i_conforencoding);
6072 contoencoding = PQgetvalue(res, 0, i_contoencoding);
6073 conproc = PQgetvalue(res, 0, i_conproc);
6074 condefault = (PQgetvalue(res, 0, i_condefault)[0] == 't');
6077 * DROP must be fully qualified in case same name appears in
6080 appendPQExpBuffer(delq, "DROP CONVERSION %s",
6081 fmtId(convinfo->dobj.namespace->dobj.name));
6082 appendPQExpBuffer(delq, ".%s;\n",
6083 fmtId(convinfo->dobj.name));
6085 appendPQExpBuffer(q, "CREATE %sCONVERSION %s FOR ",
6086 (condefault) ? "DEFAULT " : "",
6087 fmtId(convinfo->dobj.name));
6088 appendStringLiteral(q, conforencoding, true);
6089 appendPQExpBuffer(q, " TO ");
6090 appendStringLiteral(q, contoencoding, true);
6091 /* regproc is automatically quoted in 7.3 and above */
6092 appendPQExpBuffer(q, " FROM %s;\n", conproc);
6094 ArchiveEntry(fout, convinfo->dobj.catId, convinfo->dobj.dumpId,
6095 convinfo->dobj.name,
6096 convinfo->dobj.namespace->dobj.name,
6099 false, "CONVERSION", q->data, delq->data, NULL,
6100 convinfo->dobj.dependencies, convinfo->dobj.nDeps,
6103 /* Dump Conversion Comments */
6104 resetPQExpBuffer(q);
6105 appendPQExpBuffer(q, "CONVERSION %s", fmtId(convinfo->dobj.name));
6106 dumpComment(fout, q->data,
6107 convinfo->dobj.namespace->dobj.name, convinfo->usename,
6108 convinfo->dobj.catId, 0, convinfo->dobj.dumpId);
6112 destroyPQExpBuffer(query);
6113 destroyPQExpBuffer(q);
6114 destroyPQExpBuffer(delq);
6115 destroyPQExpBuffer(details);
6119 * format_aggregate_signature: generate aggregate name and argument list
6121 * The argument type names are qualified if needed. The aggregate name
6122 * is never qualified.
6125 format_aggregate_signature(AggInfo *agginfo, Archive *fout, bool honor_quotes)
6127 PQExpBufferData buf;
6129 initPQExpBuffer(&buf);
6131 appendPQExpBuffer(&buf, "%s",
6132 fmtId(agginfo->aggfn.dobj.name));
6134 appendPQExpBuffer(&buf, "%s", agginfo->aggfn.dobj.name);
6136 /* If using regtype or format_type, fmtbasetype is already quoted */
6137 if (fout->remoteVersion >= 70100)
6139 if (agginfo->anybasetype)
6140 appendPQExpBuffer(&buf, "(*)");
6142 appendPQExpBuffer(&buf, "(%s)", agginfo->fmtbasetype);
6146 if (agginfo->anybasetype)
6147 appendPQExpBuffer(&buf, "(*)");
6149 appendPQExpBuffer(&buf, "(%s)",
6150 fmtId(agginfo->fmtbasetype));
6158 * write out a single aggregate definition
6161 dumpAgg(Archive *fout, AggInfo *agginfo)
6166 PQExpBuffer details;
6178 const char *aggtransfn;
6179 const char *aggfinalfn;
6180 const char *aggtranstype;
6181 const char *agginitval;
6184 /* Dump only aggs in dumpable namespaces */
6185 if (!agginfo->aggfn.dobj.namespace->dump || dataOnly)
6188 query = createPQExpBuffer();
6189 q = createPQExpBuffer();
6190 delq = createPQExpBuffer();
6191 details = createPQExpBuffer();
6193 /* Make sure we are in proper schema */
6194 selectSourceSchema(agginfo->aggfn.dobj.namespace->dobj.name);
6196 /* Get aggregate-specific details */
6197 if (g_fout->remoteVersion >= 70300)
6199 appendPQExpBuffer(query, "SELECT aggtransfn, "
6200 "aggfinalfn, aggtranstype::pg_catalog.regtype, "
6202 "proargtypes[0] = 'pg_catalog.\"any\"'::pg_catalog.regtype as anybasetype, "
6203 "proargtypes[0]::pg_catalog.regtype as fmtbasetype, "
6204 "'t'::boolean as convertok "
6205 "from pg_catalog.pg_aggregate a, pg_catalog.pg_proc p "
6206 "where a.aggfnoid = p.oid "
6207 "and p.oid = '%u'::pg_catalog.oid",
6208 agginfo->aggfn.dobj.catId.oid);
6210 else if (g_fout->remoteVersion >= 70100)
6212 appendPQExpBuffer(query, "SELECT aggtransfn, aggfinalfn, "
6213 "format_type(aggtranstype, NULL) as aggtranstype, "
6215 "aggbasetype = 0 as anybasetype, "
6216 "CASE WHEN aggbasetype = 0 THEN '-' "
6217 "ELSE format_type(aggbasetype, NULL) END as fmtbasetype, "
6218 "'t'::boolean as convertok "
6219 "from pg_aggregate "
6220 "where oid = '%u'::oid",
6221 agginfo->aggfn.dobj.catId.oid);
6225 appendPQExpBuffer(query, "SELECT aggtransfn1 as aggtransfn, "
6227 "(select typname from pg_type where oid = aggtranstype1) as aggtranstype, "
6228 "agginitval1 as agginitval, "
6229 "aggbasetype = 0 as anybasetype, "
6230 "(select typname from pg_type where oid = aggbasetype) as fmtbasetype, "
6231 "(aggtransfn2 = 0 and aggtranstype2 = 0 and agginitval2 is null) as convertok "
6232 "from pg_aggregate "
6233 "where oid = '%u'::oid",
6234 agginfo->aggfn.dobj.catId.oid);
6237 res = PQexec(g_conn, query->data);
6238 check_sql_result(res, g_conn, query->data, PGRES_TUPLES_OK);
6240 /* Expecting a single result only */
6241 ntups = PQntuples(res);
6244 write_msg(NULL, "Got %d rows instead of one from: %s",
6245 ntups, query->data);
6249 i_aggtransfn = PQfnumber(res, "aggtransfn");
6250 i_aggfinalfn = PQfnumber(res, "aggfinalfn");
6251 i_aggtranstype = PQfnumber(res, "aggtranstype");
6252 i_agginitval = PQfnumber(res, "agginitval");
6253 i_anybasetype = PQfnumber(res, "anybasetype");
6254 i_fmtbasetype = PQfnumber(res, "fmtbasetype");
6255 i_convertok = PQfnumber(res, "convertok");
6257 aggtransfn = PQgetvalue(res, 0, i_aggtransfn);
6258 aggfinalfn = PQgetvalue(res, 0, i_aggfinalfn);
6259 aggtranstype = PQgetvalue(res, 0, i_aggtranstype);
6260 agginitval = PQgetvalue(res, 0, i_agginitval);
6261 /* we save anybasetype for format_aggregate_signature */
6262 agginfo->anybasetype = (PQgetvalue(res, 0, i_anybasetype)[0] == 't');
6263 /* we save fmtbasetype for format_aggregate_signature */
6264 agginfo->fmtbasetype = strdup(PQgetvalue(res, 0, i_fmtbasetype));
6265 convertok = (PQgetvalue(res, 0, i_convertok)[0] == 't');
6267 aggsig = format_aggregate_signature(agginfo, fout, true);
6268 aggsig_tag = format_aggregate_signature(agginfo, fout, false);
6272 write_msg(NULL, "WARNING: aggregate function %s could not be dumped correctly for this database version; ignored\n",
6277 if (g_fout->remoteVersion >= 70300)
6279 /* If using 7.3's regproc or regtype, data is already quoted */
6280 appendPQExpBuffer(details, " BASETYPE = %s,\n SFUNC = %s,\n STYPE = %s",
6281 agginfo->anybasetype ? "'any'" :
6282 agginfo->fmtbasetype,
6286 else if (g_fout->remoteVersion >= 70100)
6288 /* format_type quotes, regproc does not */
6289 appendPQExpBuffer(details, " BASETYPE = %s,\n SFUNC = %s,\n STYPE = %s",
6290 agginfo->anybasetype ? "'any'" :
6291 agginfo->fmtbasetype,
6297 /* need quotes all around */
6298 appendPQExpBuffer(details, " BASETYPE = %s,\n",
6299 agginfo->anybasetype ? "'any'" :
6300 fmtId(agginfo->fmtbasetype));
6301 appendPQExpBuffer(details, " SFUNC = %s,\n",
6303 appendPQExpBuffer(details, " STYPE = %s",
6304 fmtId(aggtranstype));
6307 if (!PQgetisnull(res, 0, i_agginitval))
6309 appendPQExpBuffer(details, ",\n INITCOND = ");
6310 appendStringLiteral(details, agginitval, true);
6313 if (strcmp(aggfinalfn, "-") != 0)
6315 appendPQExpBuffer(details, ",\n FINALFUNC = %s",
6320 * DROP must be fully qualified in case same name appears in
6323 appendPQExpBuffer(delq, "DROP AGGREGATE %s.%s;\n",
6324 fmtId(agginfo->aggfn.dobj.namespace->dobj.name),
6327 appendPQExpBuffer(q, "CREATE AGGREGATE %s (\n%s\n);\n",
6328 fmtId(agginfo->aggfn.dobj.name),
6331 ArchiveEntry(fout, agginfo->aggfn.dobj.catId, agginfo->aggfn.dobj.dumpId,
6333 agginfo->aggfn.dobj.namespace->dobj.name,
6335 agginfo->aggfn.usename,
6336 false, "AGGREGATE", q->data, delq->data, NULL,
6337 agginfo->aggfn.dobj.dependencies, agginfo->aggfn.dobj.nDeps,
6340 /* Dump Aggregate Comments */
6341 resetPQExpBuffer(q);
6342 appendPQExpBuffer(q, "AGGREGATE %s", aggsig);
6343 dumpComment(fout, q->data,
6344 agginfo->aggfn.dobj.namespace->dobj.name, agginfo->aggfn.usename,
6345 agginfo->aggfn.dobj.catId, 0, agginfo->aggfn.dobj.dumpId);
6348 * Since there is no GRANT ON AGGREGATE syntax, we have to make the
6349 * ACL command look like a function's GRANT; in particular this
6350 * affects the syntax for aggregates on ANY.
6355 aggsig = format_function_signature(&agginfo->aggfn, NULL, true);
6356 aggsig_tag = format_function_signature(&agginfo->aggfn, NULL, false);
6358 dumpACL(fout, agginfo->aggfn.dobj.catId, agginfo->aggfn.dobj.dumpId,
6361 agginfo->aggfn.dobj.namespace->dobj.name,
6362 agginfo->aggfn.usename, agginfo->aggfn.proacl);
6369 destroyPQExpBuffer(query);
6370 destroyPQExpBuffer(q);
6371 destroyPQExpBuffer(delq);
6372 destroyPQExpBuffer(details);
6377 * Write out grant/revoke information
6379 * 'objCatId' is the catalog ID of the underlying object.
6380 * 'objDumpId' is the dump ID of the underlying object.
6381 * 'type' must be TABLE, FUNCTION, LANGUAGE, or SCHEMA.
6382 * 'name' is the formatted name of the object. Must be quoted etc. already.
6383 * 'tag' is the tag for the archive entry (typ. unquoted name of object).
6384 * 'nspname' is the namespace the object is in (NULL if none).
6385 * 'owner' is the owner, NULL if there is no owner (for languages).
6386 * 'acls' is the string read out of the fooacl system catalog field;
6387 * it will be parsed here.
6391 dumpACL(Archive *fout, CatalogId objCatId, DumpId objDumpId,
6392 const char *type, const char *name,
6393 const char *tag, const char *nspname, const char *owner,
6398 /* Do nothing if ACL dump is not enabled */
6399 if (dataOnly || aclsSkip)
6402 sql = createPQExpBuffer();
6404 if (!buildACLCommands(name, type, acls, owner, fout->remoteVersion, sql))
6406 write_msg(NULL, "could not parse ACL list (%s) for object \"%s\" (%s)\n",
6412 ArchiveEntry(fout, nilCatalogId, createDumpId(),
6416 false, "ACL", sql->data, "", NULL,
6420 destroyPQExpBuffer(sql);
6425 * write out to fout the declarations (not data) of a user-defined table
6428 dumpTable(Archive *fout, TableInfo *tbinfo)
6434 if (tbinfo->relkind == RELKIND_SEQUENCE)
6435 dumpSequence(fout, tbinfo);
6437 dumpTableSchema(fout, tbinfo);
6439 /* Handle the ACL here */
6440 namecopy = strdup(fmtId(tbinfo->dobj.name));
6441 dumpACL(fout, tbinfo->dobj.catId, tbinfo->dobj.dumpId, "TABLE",
6442 namecopy, tbinfo->dobj.name,
6443 tbinfo->dobj.namespace->dobj.name, tbinfo->usename,
6451 * write the declaration (not data) of one user-defined table or view
6454 dumpTableSchema(Archive *fout, TableInfo *tbinfo)
6456 PQExpBuffer query = createPQExpBuffer();
6457 PQExpBuffer q = createPQExpBuffer();
6458 PQExpBuffer delq = createPQExpBuffer();
6461 TableInfo **parents;
6462 int actual_atts; /* number of attrs in this CREATE statment */
6468 /* Make sure we are in proper schema */
6469 selectSourceSchema(tbinfo->dobj.namespace->dobj.name);
6471 /* Is it a table or a view? */
6472 if (tbinfo->relkind == RELKIND_VIEW)
6476 reltypename = "VIEW";
6478 /* Fetch the view definition */
6479 if (g_fout->remoteVersion >= 70300)
6481 /* Beginning in 7.3, viewname is not unique; rely on OID */
6482 appendPQExpBuffer(query,
6483 "SELECT pg_catalog.pg_get_viewdef('%u'::pg_catalog.oid) as viewdef",
6484 tbinfo->dobj.catId.oid);
6488 appendPQExpBuffer(query, "SELECT definition as viewdef "
6489 " from pg_views where viewname = ");
6490 appendStringLiteral(query, tbinfo->dobj.name, true);
6491 appendPQExpBuffer(query, ";");
6494 res = PQexec(g_conn, query->data);
6495 check_sql_result(res, g_conn, query->data, PGRES_TUPLES_OK);
6497 if (PQntuples(res) != 1)
6499 if (PQntuples(res) < 1)
6500 write_msg(NULL, "query to obtain definition of view \"%s\" returned no data\n",
6503 write_msg(NULL, "query to obtain definition of view \"%s\" returned more than one definition\n",
6508 viewdef = PQgetvalue(res, 0, 0);
6510 if (strlen(viewdef) == 0)
6512 write_msg(NULL, "definition of view \"%s\" appears to be empty (length zero)\n",
6518 * DROP must be fully qualified in case same name appears in
6521 appendPQExpBuffer(delq, "DROP VIEW %s.",
6522 fmtId(tbinfo->dobj.namespace->dobj.name));
6523 appendPQExpBuffer(delq, "%s;\n",
6524 fmtId(tbinfo->dobj.name));
6526 appendPQExpBuffer(q, "CREATE VIEW %s AS\n %s\n",
6527 fmtId(tbinfo->dobj.name), viewdef);
6533 reltypename = "TABLE";
6534 numParents = tbinfo->numParents;
6535 parents = tbinfo->parents;
6538 * DROP must be fully qualified in case same name appears in
6541 appendPQExpBuffer(delq, "DROP TABLE %s.",
6542 fmtId(tbinfo->dobj.namespace->dobj.name));
6543 appendPQExpBuffer(delq, "%s;\n",
6544 fmtId(tbinfo->dobj.name));
6546 appendPQExpBuffer(q, "CREATE TABLE %s (",
6547 fmtId(tbinfo->dobj.name));
6549 for (j = 0; j < tbinfo->numatts; j++)
6551 /* Is this one of the table's own attrs, and not dropped ? */
6552 if (!tbinfo->inhAttrs[j] && !tbinfo->attisdropped[j])
6554 /* Format properly if not first attr */
6555 if (actual_atts > 0)
6556 appendPQExpBuffer(q, ",");
6557 appendPQExpBuffer(q, "\n ");
6559 /* Attribute name */
6560 appendPQExpBuffer(q, "%s ",
6561 fmtId(tbinfo->attnames[j]));
6563 /* Attribute type */
6564 if (g_fout->remoteVersion >= 70100)
6566 char *typname = tbinfo->atttypnames[j];
6568 if (tbinfo->attisserial[j])
6570 if (strcmp(typname, "integer") == 0)
6572 else if (strcmp(typname, "bigint") == 0)
6573 typname = "bigserial";
6575 appendPQExpBuffer(q, "%s", typname);
6579 /* If no format_type, fake it */
6580 appendPQExpBuffer(q, "%s",
6581 myFormatType(tbinfo->atttypnames[j],
6582 tbinfo->atttypmod[j]));
6586 * Default value --- suppress if inherited, serial, or to
6587 * be printed separately.
6589 if (tbinfo->attrdefs[j] != NULL &&
6590 !tbinfo->inhAttrDef[j] &&
6591 !tbinfo->attisserial[j] &&
6592 !tbinfo->attrdefs[j]->separate)
6593 appendPQExpBuffer(q, " DEFAULT %s",
6594 tbinfo->attrdefs[j]->adef_expr);
6597 * Not Null constraint --- suppress if inherited
6599 * Note: we could suppress this for serial columns since
6600 * SERIAL implies NOT NULL. We choose not to for forward
6601 * compatibility, since there has been some talk of making
6602 * SERIAL not imply NOT NULL, in which case the explicit
6603 * specification would be needed.
6605 if (tbinfo->notnull[j] && !tbinfo->inhNotNull[j])
6606 appendPQExpBuffer(q, " NOT NULL");
6613 * Add non-inherited CHECK constraints, if any.
6615 for (j = 0; j < tbinfo->ncheck; j++)
6617 ConstraintInfo *constr = &(tbinfo->checkexprs[j]);
6619 if (constr->coninherited || constr->separate)
6622 if (actual_atts > 0)
6623 appendPQExpBuffer(q, ",\n ");
6625 appendPQExpBuffer(q, "CONSTRAINT %s ",
6626 fmtId(constr->dobj.name));
6627 appendPQExpBuffer(q, "%s", constr->condef);
6632 appendPQExpBuffer(q, "\n)");
6636 appendPQExpBuffer(q, "\nINHERITS (");
6637 for (k = 0; k < numParents; k++)
6639 TableInfo *parentRel = parents[k];
6642 appendPQExpBuffer(q, ", ");
6643 if (parentRel->dobj.namespace != tbinfo->dobj.namespace)
6644 appendPQExpBuffer(q, "%s.",
6645 fmtId(parentRel->dobj.namespace->dobj.name));
6646 appendPQExpBuffer(q, "%s",
6647 fmtId(parentRel->dobj.name));
6649 appendPQExpBuffer(q, ")");
6652 appendPQExpBuffer(q, ";\n");
6654 /* Loop dumping statistics and storage statements */
6655 for (j = 0; j < tbinfo->numatts; j++)
6658 * Dump per-column statistics information. We only issue an
6659 * ALTER TABLE statement if the attstattarget entry for this
6660 * column is non-negative (i.e. it's not the default value)
6662 if (tbinfo->attstattarget[j] >= 0 &&
6663 !tbinfo->attisdropped[j])
6665 appendPQExpBuffer(q, "ALTER TABLE ONLY %s ",
6666 fmtId(tbinfo->dobj.name));
6667 appendPQExpBuffer(q, "ALTER COLUMN %s ",
6668 fmtId(tbinfo->attnames[j]));
6669 appendPQExpBuffer(q, "SET STATISTICS %d;\n",
6670 tbinfo->attstattarget[j]);
6674 * Dump per-column storage information. The statement is only
6675 * dumped if the storage has been changed from the type's
6678 if (!tbinfo->attisdropped[j] && tbinfo->attstorage[j] != tbinfo->typstorage[j])
6680 switch (tbinfo->attstorage[j])
6686 storage = "EXTERNAL";
6692 storage = "EXTENDED";
6699 * Only dump the statement if it's a storage type we
6702 if (storage != NULL)
6704 appendPQExpBuffer(q, "ALTER TABLE ONLY %s ",
6705 fmtId(tbinfo->dobj.name));
6706 appendPQExpBuffer(q, "ALTER COLUMN %s ",
6707 fmtId(tbinfo->attnames[j]));
6708 appendPQExpBuffer(q, "SET STORAGE %s;\n",
6715 ArchiveEntry(fout, tbinfo->dobj.catId, tbinfo->dobj.dumpId,
6717 tbinfo->dobj.namespace->dobj.name,
6718 (tbinfo->relkind == RELKIND_VIEW) ? NULL : tbinfo->reltablespace,
6720 (strcmp(reltypename, "TABLE") == 0) ? tbinfo->hasoids : false,
6721 reltypename, q->data, delq->data, NULL,
6722 tbinfo->dobj.dependencies, tbinfo->dobj.nDeps,
6725 /* Dump Table Comments */
6726 dumpTableComment(fout, tbinfo, reltypename);
6728 destroyPQExpBuffer(query);
6729 destroyPQExpBuffer(q);
6730 destroyPQExpBuffer(delq);
6734 * dumpAttrDef --- dump an attribute's default-value declaration
6737 dumpAttrDef(Archive *fout, AttrDefInfo *adinfo)
6739 TableInfo *tbinfo = adinfo->adtable;
6740 int adnum = adinfo->adnum;
6744 /* Only print it if "separate" mode is selected */
6745 if (!tbinfo->dump || !adinfo->separate || dataOnly)
6748 /* Don't print inherited or serial defaults, either */
6749 if (tbinfo->inhAttrDef[adnum - 1] || tbinfo->attisserial[adnum - 1])
6752 q = createPQExpBuffer();
6753 delq = createPQExpBuffer();
6755 appendPQExpBuffer(q, "ALTER TABLE %s ",
6756 fmtId(tbinfo->dobj.name));
6757 appendPQExpBuffer(q, "ALTER COLUMN %s SET DEFAULT %s;\n",
6758 fmtId(tbinfo->attnames[adnum - 1]),
6762 * DROP must be fully qualified in case same name appears in
6765 appendPQExpBuffer(delq, "ALTER TABLE %s.",
6766 fmtId(tbinfo->dobj.namespace->dobj.name));
6767 appendPQExpBuffer(delq, "%s ",
6768 fmtId(tbinfo->dobj.name));
6769 appendPQExpBuffer(delq, "ALTER COLUMN %s DROP DEFAULT;\n",
6770 fmtId(tbinfo->attnames[adnum - 1]));
6772 ArchiveEntry(fout, adinfo->dobj.catId, adinfo->dobj.dumpId,
6773 tbinfo->attnames[adnum - 1],
6774 tbinfo->dobj.namespace->dobj.name,
6777 false, "DEFAULT", q->data, delq->data, NULL,
6778 adinfo->dobj.dependencies, adinfo->dobj.nDeps,
6781 destroyPQExpBuffer(q);
6782 destroyPQExpBuffer(delq);
6786 * getAttrName: extract the correct name for an attribute
6788 * The array tblInfo->attnames[] only provides names of user attributes;
6789 * if a system attribute number is supplied, we have to fake it.
6790 * We also do a little bit of bounds checking for safety's sake.
6793 getAttrName(int attrnum, TableInfo *tblInfo)
6795 if (attrnum > 0 && attrnum <= tblInfo->numatts)
6796 return tblInfo->attnames[attrnum - 1];
6799 case SelfItemPointerAttributeNumber:
6801 case ObjectIdAttributeNumber:
6803 case MinTransactionIdAttributeNumber:
6805 case MinCommandIdAttributeNumber:
6807 case MaxTransactionIdAttributeNumber:
6809 case MaxCommandIdAttributeNumber:
6811 case TableOidAttributeNumber:
6814 write_msg(NULL, "invalid column number %d for table \"%s\"\n",
6815 attrnum, tblInfo->dobj.name);
6817 return NULL; /* keep compiler quiet */
6822 * write out to fout a user-defined index
6825 dumpIndex(Archive *fout, IndxInfo *indxinfo)
6827 TableInfo *tbinfo = indxinfo->indextable;
6834 q = createPQExpBuffer();
6835 delq = createPQExpBuffer();
6838 * If there's an associated constraint, don't dump the index per se,
6839 * but do dump any comment for it.
6841 if (indxinfo->indexconstraint == 0)
6843 /* Plain secondary index */
6844 appendPQExpBuffer(q, "%s;\n", indxinfo->indexdef);
6846 /* If the index is clustered, we need to record that. */
6847 if (indxinfo->indisclustered)
6849 appendPQExpBuffer(q, "\nALTER TABLE %s CLUSTER",
6850 fmtId(tbinfo->dobj.name));
6851 appendPQExpBuffer(q, " ON %s;\n",
6852 fmtId(indxinfo->dobj.name));
6856 * DROP must be fully qualified in case same name appears in
6859 appendPQExpBuffer(delq, "DROP INDEX %s.",
6860 fmtId(tbinfo->dobj.namespace->dobj.name));
6861 appendPQExpBuffer(delq, "%s;\n",
6862 fmtId(indxinfo->dobj.name));
6864 ArchiveEntry(fout, indxinfo->dobj.catId, indxinfo->dobj.dumpId,
6865 indxinfo->dobj.name,
6866 tbinfo->dobj.namespace->dobj.name,
6867 tbinfo->reltablespace,
6868 tbinfo->usename, false,
6869 "INDEX", q->data, delq->data, NULL,
6870 indxinfo->dobj.dependencies, indxinfo->dobj.nDeps,
6874 /* Dump Index Comments */
6875 resetPQExpBuffer(q);
6876 appendPQExpBuffer(q, "INDEX %s",
6877 fmtId(indxinfo->dobj.name));
6878 dumpComment(fout, q->data,
6879 tbinfo->dobj.namespace->dobj.name,
6881 indxinfo->dobj.catId, 0, indxinfo->dobj.dumpId);
6883 destroyPQExpBuffer(q);
6884 destroyPQExpBuffer(delq);
6889 * write out to fout a user-defined constraint
6892 dumpConstraint(Archive *fout, ConstraintInfo *coninfo)
6894 TableInfo *tbinfo = coninfo->contable;
6900 if (tbinfo && !tbinfo->dump)
6903 q = createPQExpBuffer();
6904 delq = createPQExpBuffer();
6906 if (coninfo->contype == 'p' || coninfo->contype == 'u')
6908 /* Index-related constraint */
6912 indxinfo = (IndxInfo *) findObjectByDumpId(coninfo->conindex);
6914 if (indxinfo == NULL)
6916 write_msg(NULL, "missing index for constraint \"%s\"\n",
6917 coninfo->dobj.name);
6921 appendPQExpBuffer(q, "ALTER TABLE ONLY %s\n",
6922 fmtId(tbinfo->dobj.name));
6923 appendPQExpBuffer(q, " ADD CONSTRAINT %s %s (",
6924 fmtId(coninfo->dobj.name),
6925 coninfo->contype == 'p' ? "PRIMARY KEY" : "UNIQUE");
6927 for (k = 0; k < indxinfo->indnkeys; k++)
6929 int indkey = (int) indxinfo->indkeys[k];
6930 const char *attname;
6932 if (indkey == InvalidAttrNumber)
6934 attname = getAttrName(indkey, tbinfo);
6936 appendPQExpBuffer(q, "%s%s",
6937 (k == 0) ? "" : ", ",
6941 appendPQExpBuffer(q, ");\n");
6943 /* If the index is clustered, we need to record that. */
6944 if (indxinfo->indisclustered)
6946 appendPQExpBuffer(q, "\nALTER TABLE %s CLUSTER",
6947 fmtId(tbinfo->dobj.name));
6948 appendPQExpBuffer(q, " ON %s;\n",
6949 fmtId(indxinfo->dobj.name));
6953 * DROP must be fully qualified in case same name appears in
6956 appendPQExpBuffer(delq, "ALTER TABLE ONLY %s.",
6957 fmtId(tbinfo->dobj.namespace->dobj.name));
6958 appendPQExpBuffer(delq, "%s ",
6959 fmtId(tbinfo->dobj.name));
6960 appendPQExpBuffer(delq, "DROP CONSTRAINT %s;\n",
6961 fmtId(coninfo->dobj.name));
6963 ArchiveEntry(fout, coninfo->dobj.catId, coninfo->dobj.dumpId,
6965 tbinfo->dobj.namespace->dobj.name,
6966 indxinfo->tablespace,
6967 tbinfo->usename, false,
6968 "CONSTRAINT", q->data, delq->data, NULL,
6969 coninfo->dobj.dependencies, coninfo->dobj.nDeps,
6972 else if (coninfo->contype == 'f')
6975 * XXX Potentially wrap in a 'SET CONSTRAINTS OFF' block so that
6976 * the current table data is not processed
6978 appendPQExpBuffer(q, "ALTER TABLE ONLY %s\n",
6979 fmtId(tbinfo->dobj.name));
6980 appendPQExpBuffer(q, " ADD CONSTRAINT %s %s;\n",
6981 fmtId(coninfo->dobj.name),
6985 * DROP must be fully qualified in case same name appears in
6988 appendPQExpBuffer(delq, "ALTER TABLE ONLY %s.",
6989 fmtId(tbinfo->dobj.namespace->dobj.name));
6990 appendPQExpBuffer(delq, "%s ",
6991 fmtId(tbinfo->dobj.name));
6992 appendPQExpBuffer(delq, "DROP CONSTRAINT %s;\n",
6993 fmtId(coninfo->dobj.name));
6995 ArchiveEntry(fout, coninfo->dobj.catId, coninfo->dobj.dumpId,
6997 tbinfo->dobj.namespace->dobj.name,
6999 tbinfo->usename, false,
7000 "FK CONSTRAINT", q->data, delq->data, NULL,
7001 coninfo->dobj.dependencies, coninfo->dobj.nDeps,
7004 else if (coninfo->contype == 'c' && tbinfo)
7006 /* CHECK constraint on a table */
7008 /* Ignore if not to be dumped separately */
7009 if (coninfo->separate)
7011 /* not ONLY since we want it to propagate to children */
7012 appendPQExpBuffer(q, "ALTER TABLE %s\n",
7013 fmtId(tbinfo->dobj.name));
7014 appendPQExpBuffer(q, " ADD CONSTRAINT %s %s;\n",
7015 fmtId(coninfo->dobj.name),
7019 * DROP must be fully qualified in case same name appears in
7022 appendPQExpBuffer(delq, "ALTER TABLE %s.",
7023 fmtId(tbinfo->dobj.namespace->dobj.name));
7024 appendPQExpBuffer(delq, "%s ",
7025 fmtId(tbinfo->dobj.name));
7026 appendPQExpBuffer(delq, "DROP CONSTRAINT %s;\n",
7027 fmtId(coninfo->dobj.name));
7029 ArchiveEntry(fout, coninfo->dobj.catId, coninfo->dobj.dumpId,
7031 tbinfo->dobj.namespace->dobj.name,
7033 tbinfo->usename, false,
7034 "CHECK CONSTRAINT", q->data, delq->data, NULL,
7035 coninfo->dobj.dependencies, coninfo->dobj.nDeps,
7039 else if (coninfo->contype == 'c' && tbinfo == NULL)
7041 /* CHECK constraint on a domain */
7042 TypeInfo *tinfo = coninfo->condomain;
7044 /* Ignore if not to be dumped separately, or if not dumping domain */
7045 if (coninfo->separate && tinfo->dobj.namespace->dump)
7047 appendPQExpBuffer(q, "ALTER DOMAIN %s\n",
7048 fmtId(tinfo->dobj.name));
7049 appendPQExpBuffer(q, " ADD CONSTRAINT %s %s;\n",
7050 fmtId(coninfo->dobj.name),
7054 * DROP must be fully qualified in case same name appears in
7057 appendPQExpBuffer(delq, "ALTER DOMAIN %s.",
7058 fmtId(tinfo->dobj.namespace->dobj.name));
7059 appendPQExpBuffer(delq, "%s ",
7060 fmtId(tinfo->dobj.name));
7061 appendPQExpBuffer(delq, "DROP CONSTRAINT %s;\n",
7062 fmtId(coninfo->dobj.name));
7064 ArchiveEntry(fout, coninfo->dobj.catId, coninfo->dobj.dumpId,
7066 tinfo->dobj.namespace->dobj.name,
7068 tinfo->usename, false,
7069 "CHECK CONSTRAINT", q->data, delq->data, NULL,
7070 coninfo->dobj.dependencies, coninfo->dobj.nDeps,
7076 write_msg(NULL, "unrecognized constraint type: %c\n", coninfo->contype);
7080 /* Dump Constraint Comments --- only works for table constraints */
7083 resetPQExpBuffer(q);
7084 appendPQExpBuffer(q, "CONSTRAINT %s ",
7085 fmtId(coninfo->dobj.name));
7086 appendPQExpBuffer(q, "ON %s",
7087 fmtId(tbinfo->dobj.name));
7088 dumpComment(fout, q->data,
7089 tbinfo->dobj.namespace->dobj.name,
7091 coninfo->dobj.catId, 0, coninfo->dobj.dumpId);
7094 destroyPQExpBuffer(q);
7095 destroyPQExpBuffer(delq);
7100 * find the maximum oid and generate a COPY statement to set it
7104 setMaxOid(Archive *fout)
7110 do_sql_command(g_conn,
7111 "CREATE TEMPORARY TABLE pgdump_oid (dummy integer)");
7112 res = PQexec(g_conn, "INSERT INTO pgdump_oid VALUES (0)");
7113 check_sql_result(res, g_conn, "INSERT INTO pgdump_oid VALUES (0)",
7115 max_oid = PQoidValue(res);
7118 write_msg(NULL, "inserted invalid OID\n");
7122 do_sql_command(g_conn, "DROP TABLE pgdump_oid;");
7124 write_msg(NULL, "maximum system OID is %u\n", max_oid);
7125 snprintf(sql, sizeof(sql),
7126 "CREATE TEMPORARY TABLE pgdump_oid (dummy integer);\n"
7127 "COPY pgdump_oid WITH OIDS FROM stdin;\n"
7130 "DROP TABLE pgdump_oid;\n",
7133 ArchiveEntry(fout, nilCatalogId, createDumpId(),
7134 "Max OID", NULL, NULL, "",
7135 false, "<Init>", sql, "", NULL,
7141 * findLastBuiltInOid -
7142 * find the last built in oid
7144 * For 7.1 and 7.2, we do this by retrieving datlastsysoid from the
7145 * pg_database entry for the current database
7148 findLastBuiltinOid_V71(const char *dbname)
7153 PQExpBuffer query = createPQExpBuffer();
7155 resetPQExpBuffer(query);
7156 appendPQExpBuffer(query, "SELECT datlastsysoid from pg_database where datname = ");
7157 appendStringLiteral(query, dbname, true);
7159 res = PQexec(g_conn, query->data);
7160 check_sql_result(res, g_conn, query->data, PGRES_TUPLES_OK);
7162 ntups = PQntuples(res);
7165 write_msg(NULL, "missing pg_database entry for this database\n");
7170 write_msg(NULL, "found more than one pg_database entry for this database\n");
7173 last_oid = atooid(PQgetvalue(res, 0, PQfnumber(res, "datlastsysoid")));
7175 destroyPQExpBuffer(query);
7180 * findLastBuiltInOid -
7181 * find the last built in oid
7183 * For 7.0, we do this by assuming that the last thing that initdb does is to
7184 * create the pg_indexes view. This sucks in general, but seeing that 7.0.x
7185 * initdb won't be changing anymore, it'll do.
7188 findLastBuiltinOid_V70(void)
7194 res = PQexec(g_conn,
7195 "SELECT oid FROM pg_class WHERE relname = 'pg_indexes'");
7196 check_sql_result(res, g_conn,
7197 "SELECT oid FROM pg_class WHERE relname = 'pg_indexes'",
7199 ntups = PQntuples(res);
7202 write_msg(NULL, "could not find entry for pg_indexes in pg_class\n");
7207 write_msg(NULL, "found more than one entry for pg_indexes in pg_class\n");
7210 last_oid = atooid(PQgetvalue(res, 0, PQfnumber(res, "oid")));
7216 dumpSequence(Archive *fout, TableInfo *tbinfo)
7228 PQExpBuffer query = createPQExpBuffer();
7229 PQExpBuffer delqry = createPQExpBuffer();
7231 /* Make sure we are in proper schema */
7232 selectSourceSchema(tbinfo->dobj.namespace->dobj.name);
7234 snprintf(bufm, sizeof(bufm), INT64_FORMAT, SEQ_MINVALUE);
7235 snprintf(bufx, sizeof(bufx), INT64_FORMAT, SEQ_MAXVALUE);
7237 appendPQExpBuffer(query,
7238 "SELECT sequence_name, last_value, increment_by, "
7239 "CASE WHEN increment_by > 0 AND max_value = %s THEN NULL "
7240 " WHEN increment_by < 0 AND max_value = -1 THEN NULL "
7242 "END AS max_value, "
7243 "CASE WHEN increment_by > 0 AND min_value = 1 THEN NULL "
7244 " WHEN increment_by < 0 AND min_value = %s THEN NULL "
7246 "END AS min_value, "
7247 "cache_value, is_cycled, is_called from %s",
7249 fmtId(tbinfo->dobj.name));
7251 res = PQexec(g_conn, query->data);
7252 check_sql_result(res, g_conn, query->data, PGRES_TUPLES_OK);
7254 if (PQntuples(res) != 1)
7256 write_msg(NULL, "query to get data of sequence \"%s\" returned %d rows (expected 1)\n",
7257 tbinfo->dobj.name, PQntuples(res));
7261 /* Disable this check: it fails if sequence has been renamed */
7263 if (strcmp(PQgetvalue(res, 0, 0), tbinfo->dobj.name) != 0)
7265 write_msg(NULL, "query to get data of sequence \"%s\" returned name \"%s\"\n",
7266 tbinfo->dobj.name, PQgetvalue(res, 0, 0));
7271 last = PQgetvalue(res, 0, 1);
7272 incby = PQgetvalue(res, 0, 2);
7273 if (!PQgetisnull(res, 0, 3))
7274 maxv = PQgetvalue(res, 0, 3);
7275 if (!PQgetisnull(res, 0, 4))
7276 minv = PQgetvalue(res, 0, 4);
7277 cache = PQgetvalue(res, 0, 5);
7278 cycled = (strcmp(PQgetvalue(res, 0, 6), "t") == 0);
7279 called = (strcmp(PQgetvalue(res, 0, 7), "t") == 0);
7282 * The logic we use for restoring sequences is as follows:
7284 * Add a basic CREATE SEQUENCE statement (use last_val for start if
7285 * called is false, else use min_val for start_val). Skip this if the
7286 * sequence came from a SERIAL column.
7288 * Add a 'SETVAL(seq, last_val, iscalled)' at restore-time iff we load
7289 * data. We do this for serial sequences too.
7292 if (!dataOnly && !OidIsValid(tbinfo->owning_tab))
7294 resetPQExpBuffer(delqry);
7297 * DROP must be fully qualified in case same name appears in
7300 appendPQExpBuffer(delqry, "DROP SEQUENCE %s.",
7301 fmtId(tbinfo->dobj.namespace->dobj.name));
7302 appendPQExpBuffer(delqry, "%s;\n",
7303 fmtId(tbinfo->dobj.name));
7305 resetPQExpBuffer(query);
7306 appendPQExpBuffer(query,
7307 "CREATE SEQUENCE %s\n",
7308 fmtId(tbinfo->dobj.name));
7311 appendPQExpBuffer(query, " START WITH %s\n", last);
7313 appendPQExpBuffer(query, " INCREMENT BY %s\n", incby);
7316 appendPQExpBuffer(query, " MAXVALUE %s\n", maxv);
7318 appendPQExpBuffer(query, " NO MAXVALUE\n");
7321 appendPQExpBuffer(query, " MINVALUE %s\n", minv);
7323 appendPQExpBuffer(query, " NO MINVALUE\n");
7325 appendPQExpBuffer(query,
7327 cache, (cycled ? "\n CYCLE" : ""));
7329 appendPQExpBuffer(query, ";\n");
7331 ArchiveEntry(fout, tbinfo->dobj.catId, tbinfo->dobj.dumpId,
7333 tbinfo->dobj.namespace->dobj.name,
7336 false, "SEQUENCE", query->data, delqry->data, NULL,
7337 tbinfo->dobj.dependencies, tbinfo->dobj.nDeps,
7343 TableInfo *owning_tab;
7345 resetPQExpBuffer(query);
7346 appendPQExpBuffer(query, "SELECT pg_catalog.setval(");
7349 * If this is a SERIAL sequence, then use the
7350 * pg_get_serial_sequence function to avoid hard-coding the
7351 * sequence name. Note that this implicitly assumes that the
7352 * sequence and its owning table are in the same schema, because
7353 * we don't schema-qualify the reference.
7355 if (OidIsValid(tbinfo->owning_tab) &&
7356 (owning_tab = findTableByOid(tbinfo->owning_tab)) != NULL)
7358 appendPQExpBuffer(query, "pg_catalog.pg_get_serial_sequence(");
7359 appendStringLiteral(query, fmtId(owning_tab->dobj.name), true);
7360 appendPQExpBuffer(query, ", ");
7361 appendStringLiteral(query, owning_tab->attnames[tbinfo->owning_col - 1], true);
7362 appendPQExpBuffer(query, ")");
7365 appendStringLiteral(query, fmtId(tbinfo->dobj.name), true);
7366 appendPQExpBuffer(query, ", %s, %s);\n",
7367 last, (called ? "true" : "false"));
7369 ArchiveEntry(fout, nilCatalogId, createDumpId(),
7371 tbinfo->dobj.namespace->dobj.name,
7374 false, "SEQUENCE SET", query->data, "", NULL,
7375 &(tbinfo->dobj.dumpId), 1,
7381 /* Dump Sequence Comments */
7382 resetPQExpBuffer(query);
7383 appendPQExpBuffer(query, "SEQUENCE %s", fmtId(tbinfo->dobj.name));
7384 dumpComment(fout, query->data,
7385 tbinfo->dobj.namespace->dobj.name, tbinfo->usename,
7386 tbinfo->dobj.catId, 0, tbinfo->dobj.dumpId);
7391 destroyPQExpBuffer(query);
7392 destroyPQExpBuffer(delqry);
7396 dumpTrigger(Archive *fout, TriggerInfo *tginfo)
7398 TableInfo *tbinfo = tginfo->tgtable;
7407 query = createPQExpBuffer();
7408 delqry = createPQExpBuffer();
7411 * DROP must be fully qualified in case same name appears in
7414 appendPQExpBuffer(delqry, "DROP TRIGGER %s ",
7415 fmtId(tginfo->dobj.name));
7416 appendPQExpBuffer(delqry, "ON %s.",
7417 fmtId(tbinfo->dobj.namespace->dobj.name));
7418 appendPQExpBuffer(delqry, "%s;\n",
7419 fmtId(tbinfo->dobj.name));
7421 if (tginfo->tgisconstraint)
7423 appendPQExpBuffer(query, "CREATE CONSTRAINT TRIGGER ");
7424 appendPQExpBuffer(query, fmtId(tginfo->tgconstrname));
7428 appendPQExpBuffer(query, "CREATE TRIGGER ");
7429 appendPQExpBuffer(query, fmtId(tginfo->dobj.name));
7431 appendPQExpBuffer(query, "\n ");
7435 if (TRIGGER_FOR_BEFORE(tginfo->tgtype))
7436 appendPQExpBuffer(query, "BEFORE");
7438 appendPQExpBuffer(query, "AFTER");
7439 if (TRIGGER_FOR_INSERT(tginfo->tgtype))
7441 appendPQExpBuffer(query, " INSERT");
7444 if (TRIGGER_FOR_DELETE(tginfo->tgtype))
7447 appendPQExpBuffer(query, " OR DELETE");
7449 appendPQExpBuffer(query, " DELETE");
7452 if (TRIGGER_FOR_UPDATE(tginfo->tgtype))
7455 appendPQExpBuffer(query, " OR UPDATE");
7457 appendPQExpBuffer(query, " UPDATE");
7459 appendPQExpBuffer(query, " ON %s\n",
7460 fmtId(tbinfo->dobj.name));
7462 if (tginfo->tgisconstraint)
7464 if (OidIsValid(tginfo->tgconstrrelid))
7466 /* If we are using regclass, name is already quoted */
7467 if (g_fout->remoteVersion >= 70300)
7468 appendPQExpBuffer(query, " FROM %s\n ",
7469 tginfo->tgconstrrelname);
7471 appendPQExpBuffer(query, " FROM %s\n ",
7472 fmtId(tginfo->tgconstrrelname));
7474 if (!tginfo->tgdeferrable)
7475 appendPQExpBuffer(query, "NOT ");
7476 appendPQExpBuffer(query, "DEFERRABLE INITIALLY ");
7477 if (tginfo->tginitdeferred)
7478 appendPQExpBuffer(query, "DEFERRED\n");
7480 appendPQExpBuffer(query, "IMMEDIATE\n");
7483 if (TRIGGER_FOR_ROW(tginfo->tgtype))
7484 appendPQExpBuffer(query, " FOR EACH ROW\n ");
7486 appendPQExpBuffer(query, " FOR EACH STATEMENT\n ");
7488 /* In 7.3, result of regproc is already quoted */
7489 if (g_fout->remoteVersion >= 70300)
7490 appendPQExpBuffer(query, "EXECUTE PROCEDURE %s(",
7493 appendPQExpBuffer(query, "EXECUTE PROCEDURE %s(",
7494 fmtId(tginfo->tgfname));
7497 for (findx = 0; findx < tginfo->tgnargs; findx++)
7503 p = strchr(p, '\\');
7506 write_msg(NULL, "invalid argument string (%s) for trigger \"%s\" on table \"%s\"\n",
7518 if (p[0] == '0' && p[1] == '0' && p[2] == '0')
7522 appendPQExpBufferChar(query, '\'');
7526 appendPQExpBufferChar(query, '\\');
7527 appendPQExpBufferChar(query, *s++);
7529 appendPQExpBufferChar(query, '\'');
7530 appendPQExpBuffer(query,
7531 (findx < tginfo->tgnargs - 1) ? ", " : "");
7534 appendPQExpBuffer(query, ");\n");
7536 ArchiveEntry(fout, tginfo->dobj.catId, tginfo->dobj.dumpId,
7538 tbinfo->dobj.namespace->dobj.name,
7540 tbinfo->usename, false,
7541 "TRIGGER", query->data, delqry->data, NULL,
7542 tginfo->dobj.dependencies, tginfo->dobj.nDeps,
7545 resetPQExpBuffer(query);
7546 appendPQExpBuffer(query, "TRIGGER %s ",
7547 fmtId(tginfo->dobj.name));
7548 appendPQExpBuffer(query, "ON %s",
7549 fmtId(tbinfo->dobj.name));
7551 dumpComment(fout, query->data,
7552 tbinfo->dobj.namespace->dobj.name, tbinfo->usename,
7553 tginfo->dobj.catId, 0, tginfo->dobj.dumpId);
7555 destroyPQExpBuffer(query);
7556 destroyPQExpBuffer(delqry);
7564 dumpRule(Archive *fout, RuleInfo *rinfo)
7566 TableInfo *tbinfo = rinfo->ruletable;
7573 * Ignore rules for not-to-be-dumped tables
7575 if (tbinfo == NULL || !tbinfo->dump || dataOnly)
7579 * If it is an ON SELECT rule, we do not need to dump it because it
7580 * will be handled via CREATE VIEW for the table.
7582 if (rinfo->ev_type == '1' && rinfo->is_instead)
7586 * Make sure we are in proper schema.
7588 selectSourceSchema(tbinfo->dobj.namespace->dobj.name);
7590 query = createPQExpBuffer();
7591 cmd = createPQExpBuffer();
7592 delcmd = createPQExpBuffer();
7594 if (g_fout->remoteVersion >= 70300)
7596 appendPQExpBuffer(query,
7597 "SELECT pg_catalog.pg_get_ruledef('%u'::pg_catalog.oid) AS definition",
7598 rinfo->dobj.catId.oid);
7602 /* Rule name was unique before 7.3 ... */
7603 appendPQExpBuffer(query,
7604 "SELECT pg_get_ruledef('%s') AS definition",
7608 res = PQexec(g_conn, query->data);
7609 check_sql_result(res, g_conn, query->data, PGRES_TUPLES_OK);
7611 if (PQntuples(res) != 1)
7613 write_msg(NULL, "query to get rule \"%s\" for table \"%s\" failed: wrong number of rows returned",
7614 rinfo->dobj.name, tbinfo->dobj.name);
7618 printfPQExpBuffer(cmd, "%s\n", PQgetvalue(res, 0, 0));
7621 * DROP must be fully qualified in case same name appears in
7624 appendPQExpBuffer(delcmd, "DROP RULE %s ",
7625 fmtId(rinfo->dobj.name));
7626 appendPQExpBuffer(delcmd, "ON %s.",
7627 fmtId(tbinfo->dobj.namespace->dobj.name));
7628 appendPQExpBuffer(delcmd, "%s;\n",
7629 fmtId(tbinfo->dobj.name));
7631 ArchiveEntry(fout, rinfo->dobj.catId, rinfo->dobj.dumpId,
7633 tbinfo->dobj.namespace->dobj.name,
7635 tbinfo->usename, false,
7636 "RULE", cmd->data, delcmd->data, NULL,
7637 rinfo->dobj.dependencies, rinfo->dobj.nDeps,
7640 /* Dump rule comments */
7641 resetPQExpBuffer(query);
7642 appendPQExpBuffer(query, "RULE %s",
7643 fmtId(rinfo->dobj.name));
7644 appendPQExpBuffer(query, " ON %s",
7645 fmtId(tbinfo->dobj.name));
7646 dumpComment(fout, query->data,
7647 tbinfo->dobj.namespace->dobj.name,
7649 rinfo->dobj.catId, 0, rinfo->dobj.dumpId);
7653 destroyPQExpBuffer(query);
7654 destroyPQExpBuffer(cmd);
7655 destroyPQExpBuffer(delcmd);
7659 * getDependencies --- obtain available dependency data
7662 getDependencies(void)
7673 DumpableObject *dobj,
7676 /* No dependency info available before 7.3 */
7677 if (g_fout->remoteVersion < 70300)
7681 write_msg(NULL, "reading dependency data\n");
7683 /* Make sure we are in proper schema */
7684 selectSourceSchema("pg_catalog");
7686 query = createPQExpBuffer();
7688 appendPQExpBuffer(query, "SELECT "
7689 "classid, objid, refclassid, refobjid, deptype "
7691 "WHERE deptype != 'p' "
7694 res = PQexec(g_conn, query->data);
7695 check_sql_result(res, g_conn, query->data, PGRES_TUPLES_OK);
7697 ntups = PQntuples(res);
7699 i_classid = PQfnumber(res, "classid");
7700 i_objid = PQfnumber(res, "objid");
7701 i_refclassid = PQfnumber(res, "refclassid");
7702 i_refobjid = PQfnumber(res, "refobjid");
7703 i_deptype = PQfnumber(res, "deptype");
7706 * Since we ordered the SELECT by referencing ID, we can expect that
7707 * multiple entries for the same object will appear together; this
7708 * saves on searches.
7712 for (i = 0; i < ntups; i++)
7718 objId.tableoid = atooid(PQgetvalue(res, i, i_classid));
7719 objId.oid = atooid(PQgetvalue(res, i, i_objid));
7720 refobjId.tableoid = atooid(PQgetvalue(res, i, i_refclassid));
7721 refobjId.oid = atooid(PQgetvalue(res, i, i_refobjid));
7722 deptype = *(PQgetvalue(res, i, i_deptype));
7725 dobj->catId.tableoid != objId.tableoid ||
7726 dobj->catId.oid != objId.oid)
7727 dobj = findObjectByCatalogId(objId);
7730 * Failure to find objects mentioned in pg_depend is not
7731 * unexpected, since for example we don't collect info about TOAST
7737 fprintf(stderr, "no referencing object %u %u\n",
7738 objId.tableoid, objId.oid);
7743 refdobj = findObjectByCatalogId(refobjId);
7745 if (refdobj == NULL)
7748 fprintf(stderr, "no referenced object %u %u\n",
7749 refobjId.tableoid, refobjId.oid);
7754 addObjectDependency(dobj, refdobj->dumpId);
7759 destroyPQExpBuffer(query);
7764 * selectSourceSchema - make the specified schema the active search path
7765 * in the source database.
7767 * NB: pg_catalog is explicitly searched after the specified schema;
7768 * so user names are only qualified if they are cross-schema references,
7769 * and system names are only qualified if they conflict with a user name
7770 * in the current schema.
7772 * Whenever the selected schema is not pg_catalog, be careful to qualify
7773 * references to system catalogs and types in our emitted commands!
7776 selectSourceSchema(const char *schemaName)
7778 static char *curSchemaName = NULL;
7781 /* Not relevant if fetching from pre-7.3 DB */
7782 if (g_fout->remoteVersion < 70300)
7784 /* Ignore null schema names */
7785 if (schemaName == NULL || *schemaName == '\0')
7787 /* Optimize away repeated selection of same schema */
7788 if (curSchemaName && strcmp(curSchemaName, schemaName) == 0)
7791 query = createPQExpBuffer();
7792 appendPQExpBuffer(query, "SET search_path = %s",
7794 if (strcmp(schemaName, "pg_catalog") != 0)
7795 appendPQExpBuffer(query, ", pg_catalog");
7797 do_sql_command(g_conn, query->data);
7799 destroyPQExpBuffer(query);
7801 free(curSchemaName);
7802 curSchemaName = strdup(schemaName);
7806 * getFormattedTypeName - retrieve a nicely-formatted type name for the
7809 * NB: in 7.3 and up the result may depend on the currently-selected
7810 * schema; this is why we don't try to cache the names.
7813 getFormattedTypeName(Oid oid, OidOptions opts)
7822 if ((opts & zeroAsOpaque) != 0)
7823 return strdup(g_opaque_type);
7824 else if ((opts & zeroAsAny) != 0)
7825 return strdup("'any'");
7826 else if ((opts & zeroAsStar) != 0)
7828 else if ((opts & zeroAsNone) != 0)
7829 return strdup("NONE");
7832 query = createPQExpBuffer();
7833 if (g_fout->remoteVersion >= 70300)
7835 appendPQExpBuffer(query, "SELECT pg_catalog.format_type('%u'::pg_catalog.oid, NULL)",
7838 else if (g_fout->remoteVersion >= 70100)
7840 appendPQExpBuffer(query, "SELECT format_type('%u'::oid, NULL)",
7845 appendPQExpBuffer(query, "SELECT typname "
7847 "WHERE oid = '%u'::oid",
7851 res = PQexec(g_conn, query->data);
7852 check_sql_result(res, g_conn, query->data, PGRES_TUPLES_OK);
7854 /* Expecting a single result only */
7855 ntups = PQntuples(res);
7858 write_msg(NULL, "query yielded %d rows instead of one: %s\n",
7859 ntups, query->data);
7863 if (g_fout->remoteVersion >= 70100)
7865 /* already quoted */
7866 result = strdup(PQgetvalue(res, 0, 0));
7870 /* may need to quote it */
7871 result = strdup(fmtId(PQgetvalue(res, 0, 0)));
7875 destroyPQExpBuffer(query);
7881 * myFormatType --- local implementation of format_type for use with 7.0.
7884 myFormatType(const char *typname, int32 typmod)
7887 bool isarray = false;
7888 PQExpBuffer buf = createPQExpBuffer();
7890 /* Handle array types */
7891 if (typname[0] == '_')
7897 /* Show lengths on bpchar and varchar */
7898 if (!strcmp(typname, "bpchar"))
7900 int len = (typmod - VARHDRSZ);
7902 appendPQExpBuffer(buf, "character");
7904 appendPQExpBuffer(buf, "(%d)",
7907 else if (!strcmp(typname, "varchar"))
7909 appendPQExpBuffer(buf, "character varying");
7911 appendPQExpBuffer(buf, "(%d)",
7914 else if (!strcmp(typname, "numeric"))
7916 appendPQExpBuffer(buf, "numeric");
7923 tmp_typmod = typmod - VARHDRSZ;
7924 precision = (tmp_typmod >> 16) & 0xffff;
7925 scale = tmp_typmod & 0xffff;
7926 appendPQExpBuffer(buf, "(%d,%d)",
7932 * char is an internal single-byte data type; Let's make sure we force
7933 * it through with quotes. - thomas 1998-12-13
7935 else if (strcmp(typname, "char") == 0)
7936 appendPQExpBuffer(buf, "\"char\"");
7938 appendPQExpBuffer(buf, "%s", fmtId(typname));
7940 /* Append array qualifier for array types */
7942 appendPQExpBuffer(buf, "[]");
7944 result = strdup(buf->data);
7945 destroyPQExpBuffer(buf);
7951 * fmtQualifiedId - convert a qualified name to the proper format for
7952 * the source database.
7954 * Like fmtId, use the result before calling again.
7957 fmtQualifiedId(const char *schema, const char *id)
7959 static PQExpBuffer id_return = NULL;
7961 if (id_return) /* first time through? */
7962 resetPQExpBuffer(id_return);
7964 id_return = createPQExpBuffer();
7966 /* Suppress schema name if fetching from pre-7.3 DB */
7967 if (g_fout->remoteVersion >= 70300 && schema && *schema)
7969 appendPQExpBuffer(id_return, "%s.",
7972 appendPQExpBuffer(id_return, "%s",
7975 return id_return->data;
7979 * Return a column list clause for the given relation.
7981 * Special case: if there are no undropped columns in the relation, return
7982 * "", not an invalid "()" column list.
7985 fmtCopyColumnList(const TableInfo *ti)
7987 static PQExpBuffer q = NULL;
7988 int numatts = ti->numatts;
7989 char **attnames = ti->attnames;
7990 bool *attisdropped = ti->attisdropped;
7994 if (q) /* first time through? */
7995 resetPQExpBuffer(q);
7997 q = createPQExpBuffer();
7999 appendPQExpBuffer(q, "(");
8001 for (i = 0; i < numatts; i++)
8003 if (attisdropped[i])
8006 appendPQExpBuffer(q, ", ");
8007 appendPQExpBuffer(q, "%s", fmtId(attnames[i]));
8012 return ""; /* no undropped columns */
8014 appendPQExpBuffer(q, ")");
8019 * Convenience subroutine to execute a SQL command and check for
8020 * COMMAND_OK status.
8023 do_sql_command(PGconn *conn, const char *query)
8027 res = PQexec(conn, query);
8028 check_sql_result(res, conn, query, PGRES_COMMAND_OK);
8033 * Convenience subroutine to verify a SQL command succeeded,
8034 * and exit with a useful error message if not.
8037 check_sql_result(PGresult *res, PGconn *conn, const char *query,
8038 ExecStatusType expected)
8042 if (res && PQresultStatus(res) == expected)
8045 write_msg(NULL, "SQL command failed\n");
8047 err = PQresultErrorMessage(res);
8049 err = PQerrorMessage(conn);
8050 write_msg(NULL, "Error message from server: %s", err);
8051 write_msg(NULL, "The command was: %s\n", query);