1 /*-------------------------------------------------------------------------
4 * pg_dump is a utility for dumping out a postgres database
7 * Portions Copyright (c) 1996-2003, PostgreSQL Global Development Group
8 * Portions Copyright (c) 1994, Regents of the University of California
10 * pg_dump will read the system catalogs in a database and dump out a
11 * script that reproduces the schema in terms of SQL that is understood
15 * $PostgreSQL: pgsql/src/bin/pg_dump/pg_dump.c,v 1.364 2004/02/12 23:41:03 tgl Exp $
17 *-------------------------------------------------------------------------
21 * Although this is not a backend module, we must include postgres.h anyway
22 * so that we can include a bunch of backend include files. pg_dump has
23 * never pretended to be very independent of the backend anyhow ...
40 #include "getopt_long.h"
46 #include "access/attnum.h"
47 #include "access/htup.h"
48 #include "catalog/pg_class.h"
49 #include "catalog/pg_proc.h"
50 #include "catalog/pg_trigger.h"
51 #include "catalog/pg_type.h"
53 #include "commands/sequence.h"
56 #include "libpq/libpq-fs.h"
59 #include "pg_backup.h"
60 #include "pg_backup_archiver.h"
61 #include "dumputils.h"
63 #define _(x) gettext((x))
71 bool g_verbose; /* User wants verbose narration of our
73 Archive *g_fout; /* the script file */
74 PGconn *g_conn; /* the database connection */
76 /* various user-settable parameters */
77 bool dumpInserts; /* dump data using proper insert strings */
78 bool attrNames; /* put attr names into insert strings */
83 /* obsolete as of 7.3: */
84 static Oid g_last_builtin_oid; /* value of the last builtin oid */
86 static char *selectTableName = NULL; /* name of a single table to dump */
87 static char *selectSchemaName = NULL; /* name of a single schema to dump */
89 char g_opaque_type[10]; /* name for the opaque type */
91 /* placeholders for the delimiters for comments */
92 char g_comment_start[10];
93 char g_comment_end[10];
95 static const CatalogId nilCatalogId = { 0, 0 };
97 /* these are to avoid passing around info for findNamespace() */
98 static NamespaceInfo *g_namespaces;
99 static int g_numNamespaces;
102 static void help(const char *progname);
103 static NamespaceInfo *findNamespace(Oid nsoid, Oid objoid);
104 static void dumpTableData(Archive *fout, TableDataInfo *tdinfo);
105 static void dumpComment(Archive *fout, const char *target,
106 const char *namespace, const char *owner,
107 CatalogId catalogId, int subid, DumpId dumpId);
108 static void dumpDumpableObject(Archive *fout, DumpableObject *dobj);
109 static void dumpNamespace(Archive *fout, NamespaceInfo *nspinfo);
110 static void dumpType(Archive *fout, TypeInfo *tinfo);
111 static void dumpBaseType(Archive *fout, TypeInfo *tinfo);
112 static void dumpDomain(Archive *fout, TypeInfo *tinfo);
113 static void dumpCompositeType(Archive *fout, TypeInfo *tinfo);
114 static void dumpProcLang(Archive *fout, ProcLangInfo *plang);
115 static void dumpFunc(Archive *fout, FuncInfo *finfo);
116 static void dumpCast(Archive *fout, CastInfo *cast);
117 static void dumpOpr(Archive *fout, OprInfo *oprinfo);
118 static void dumpOpclass(Archive *fout, OpclassInfo *opcinfo);
119 static void dumpConversion(Archive *fout, ConvInfo *convinfo);
120 static void dumpRule(Archive *fout, RuleInfo *rinfo);
121 static void dumpAgg(Archive *fout, AggInfo *agginfo);
122 static void dumpTrigger(Archive *fout, TriggerInfo *tginfo);
123 static void dumpTable(Archive *fout, TableInfo *tbinfo);
124 static void dumpTableSchema(Archive *fout, TableInfo *tbinfo);
125 static void dumpAttrDef(Archive *fout, AttrDefInfo *adinfo);
126 static void dumpSequence(Archive *fout, TableInfo *tbinfo);
127 static void dumpIndex(Archive *fout, IndxInfo *indxinfo);
128 static void dumpConstraint(Archive *fout, ConstraintInfo *coninfo);
130 static void dumpACL(Archive *fout, CatalogId objCatId, DumpId objDumpId,
131 const char *type, const char *name,
132 const char *tag, const char *nspname, const char *owner,
135 static void getDependencies(void);
136 static void getDomainConstraints(TypeInfo *tinfo);
137 static void getTableData(TableInfo *tblinfo, int numTables, bool oids);
138 static char *format_function_signature(FuncInfo *finfo, char **argnames,
140 static const char *convertRegProcReference(const char *proc);
141 static const char *convertOperatorReference(const char *opr);
142 static Oid findLastBuiltinOid_V71(const char *);
143 static Oid findLastBuiltinOid_V70(void);
144 static void setMaxOid(Archive *fout);
145 static void selectSourceSchema(const char *schemaName);
146 static char *getFormattedTypeName(Oid oid, OidOptions opts);
147 static char *myFormatType(const char *typname, int32 typmod);
148 static const char *fmtQualifiedId(const char *schema, const char *id);
149 static int dumpBlobs(Archive *AH, void *arg);
150 static void dumpDatabase(Archive *AH);
151 static const char *getAttrName(int attrnum, TableInfo *tblInfo);
152 static const char *fmtCopyColumnList(const TableInfo *ti);
153 static void do_sql_command(PGconn *conn, const char *query);
154 static void check_sql_result(PGresult *res, PGconn *conn, const char *query,
155 ExecStatusType expected);
159 main(int argc, char **argv)
162 const char *filename = NULL;
163 const char *format = "p";
164 const char *dbname = NULL;
165 const char *pghost = NULL;
166 const char *pgport = NULL;
167 const char *username = NULL;
171 DumpableObject **dobjs;
174 bool force_password = false;
175 int compressLevel = -1;
176 bool ignore_version = false;
179 int outputCreate = 0;
181 int outputNoOwner = 0;
182 static int use_setsessauth = 0;
183 static int disable_triggers = 0;
184 char *outputSuperuser = NULL;
186 RestoreOptions *ropt;
188 static struct option long_options[] = {
189 {"data-only", no_argument, NULL, 'a'},
190 {"blobs", no_argument, NULL, 'b'},
191 {"clean", no_argument, NULL, 'c'},
192 {"create", no_argument, NULL, 'C'},
193 {"file", required_argument, NULL, 'f'},
194 {"format", required_argument, NULL, 'F'},
195 {"inserts", no_argument, NULL, 'd'},
196 {"attribute-inserts", no_argument, NULL, 'D'},
197 {"column-inserts", no_argument, NULL, 'D'},
198 {"host", required_argument, NULL, 'h'},
199 {"ignore-version", no_argument, NULL, 'i'},
200 {"no-reconnect", no_argument, NULL, 'R'},
201 {"oids", no_argument, NULL, 'o'},
202 {"no-owner", no_argument, NULL, 'O'},
203 {"port", required_argument, NULL, 'p'},
204 {"schema", required_argument, NULL, 'n'},
205 {"schema-only", no_argument, NULL, 's'},
206 {"superuser", required_argument, NULL, 'S'},
207 {"table", required_argument, NULL, 't'},
208 {"password", no_argument, NULL, 'W'},
209 {"username", required_argument, NULL, 'U'},
210 {"verbose", no_argument, NULL, 'v'},
211 {"no-privileges", no_argument, NULL, 'x'},
212 {"no-acl", no_argument, NULL, 'x'},
213 {"compress", required_argument, NULL, 'Z'},
214 {"help", no_argument, NULL, '?'},
215 {"version", no_argument, NULL, 'V'},
218 * the following options don't have an equivalent short option
219 * letter, but are available as '-X long-name'
221 {"use-set-session-authorization", no_argument, &use_setsessauth, 1},
222 {"disable-triggers", no_argument, &disable_triggers, 1},
229 setlocale(LC_ALL, "");
230 bindtextdomain("pg_dump", LOCALEDIR);
231 textdomain("pg_dump");
236 strcpy(g_comment_start, "-- ");
237 g_comment_end[0] = '\0';
238 strcpy(g_opaque_type, "opaque");
240 dataOnly = schemaOnly = dumpInserts = attrNames = false;
242 progname = get_progname(argv[0]);
244 /* Set default options based on progname */
245 if (strcmp(progname, "pg_backup") == 0)
253 if (strcmp(argv[1], "--help") == 0 || strcmp(argv[1], "-?") == 0)
258 if (strcmp(argv[1], "--version") == 0 || strcmp(argv[1], "-V") == 0)
260 puts("pg_dump (PostgreSQL) " PG_VERSION);
265 while ((c = getopt_long(argc, argv, "abcCdDf:F:h:in:oOp:RsS:t:uU:vWxX:Z:",
266 long_options, &optindex)) != -1)
270 case 'a': /* Dump data only */
274 case 'b': /* Dump blobs */
278 case 'c': /* clean (i.e., drop) schema prior to
283 case 'C': /* Create DB */
287 case 'd': /* dump data as proper insert strings */
291 case 'D': /* dump data as proper insert strings with
305 case 'h': /* server host */
309 case 'i': /* ignore database version mismatch */
310 ignore_version = true;
313 case 'n': /* Dump data for this schema only */
314 selectSchemaName = strdup(optarg);
317 case 'o': /* Dump oids */
321 case 'O': /* Don't reconnect to match owner */
325 case 'p': /* server port */
330 /* no-op, still accepted for backwards compatibility */
333 case 's': /* dump schema only */
337 case 'S': /* Username for superuser in plain text
339 outputSuperuser = strdup(optarg);
342 case 't': /* Dump data for this table only */
343 selectTableName = strdup(optarg);
347 force_password = true;
348 username = simple_prompt("User name: ", 100, true);
355 case 'v': /* verbose */
360 force_password = true;
363 case 'x': /* skip ACL dump */
368 * Option letters were getting scarce, so I invented this
369 * new scheme: '-X feature' turns on some feature. Compare
370 * to the -f option in GCC. You should also add an
371 * equivalent GNU-style option --feature. Features that
372 * require arguments should use '-X feature=foo'.
375 if (strcmp(optarg, "use-set-session-authorization") == 0)
376 /* no-op, still allowed for compatibility */ ;
377 else if (strcmp(optarg, "disable-triggers") == 0)
378 disable_triggers = 1;
382 _("%s: invalid -X option -- %s\n"),
384 fprintf(stderr, _("Try \"%s --help\" for more information.\n"), progname);
389 case 'Z': /* Compression Level */
390 compressLevel = atoi(optarg);
392 /* This covers the long options equivalent to -X xxx. */
398 fprintf(stderr, _("Try \"%s --help\" for more information.\n"), progname);
403 if (optind < (argc - 1))
405 fprintf(stderr, _("%s: too many command-line arguments (first is \"%s\")\n"),
406 progname, argv[optind + 1]);
407 fprintf(stderr, _("Try \"%s --help\" for more information.\n"),
412 /* Get database name from command line */
414 dbname = argv[optind];
416 if (dataOnly && schemaOnly)
418 write_msg(NULL, "options \"schema only\" (-s) and \"data only\" (-a) cannot be used together\n");
422 if (dataOnly && outputClean)
424 write_msg(NULL, "options \"clean\" (-c) and \"data only\" (-a) cannot be used together\n");
428 if (outputBlobs && selectTableName != NULL)
430 write_msg(NULL, "large-object output not supported for a single table\n");
431 write_msg(NULL, "use a full dump instead\n");
435 if (outputBlobs && selectSchemaName != NULL)
437 write_msg(NULL, "large-object output not supported for a single schema\n");
438 write_msg(NULL, "use a full dump instead\n");
442 if (dumpInserts == true && oids == true)
444 write_msg(NULL, "INSERT (-d, -D) and OID (-o) options cannot be used together\n");
445 write_msg(NULL, "(The INSERT command cannot set OIDs.)\n");
449 if (outputBlobs == true && (format[0] == 'p' || format[0] == 'P'))
451 write_msg(NULL, "large-object output is not supported for plain-text dump files\n");
452 write_msg(NULL, "(Use a different output format.)\n");
456 /* open the output file */
461 g_fout = CreateArchive(filename, archCustom, compressLevel);
466 g_fout = CreateArchive(filename, archFiles, compressLevel);
472 g_fout = CreateArchive(filename, archNull, 0);
477 g_fout = CreateArchive(filename, archTar, compressLevel);
481 write_msg(NULL, "invalid output format \"%s\" specified\n", format);
487 write_msg(NULL, "could not open output file \"%s\" for writing\n", filename);
491 /* Let the archiver know how noisy to be */
492 g_fout->verbose = g_verbose;
494 g_fout->minRemoteVersion = 70000; /* we can handle back to 7.0 */
495 g_fout->maxRemoteVersion = parse_version(PG_VERSION);
496 if (g_fout->maxRemoteVersion < 0)
498 write_msg(NULL, "could not parse version string \"%s\"\n", PG_VERSION);
503 * Open the database using the Archiver, so it knows about it. Errors
506 g_conn = ConnectDatabase(g_fout, dbname, pghost, pgport,
507 username, force_password, ignore_version);
510 * Start serializable transaction to dump consistent data.
512 do_sql_command(g_conn, "BEGIN");
514 do_sql_command(g_conn, "SET TRANSACTION ISOLATION LEVEL SERIALIZABLE");
516 /* Set the datestyle to ISO to ensure the dump's portability */
517 do_sql_command(g_conn, "SET DATESTYLE = ISO");
520 * If supported, set extra_float_digits so that we can dump float data
521 * exactly (given correctly implemented float I/O code, anyway)
523 if (g_fout->remoteVersion >= 70400)
524 do_sql_command(g_conn, "SET extra_float_digits TO 2");
526 /* Find the last built-in OID, if needed */
527 if (g_fout->remoteVersion < 70300)
529 if (g_fout->remoteVersion >= 70100)
530 g_last_builtin_oid = findLastBuiltinOid_V71(PQdb(g_conn));
532 g_last_builtin_oid = findLastBuiltinOid_V70();
534 write_msg(NULL, "last built-in OID is %u\n", g_last_builtin_oid);
538 * Now scan the database and create DumpableObject structs for all the
539 * objects we intend to dump.
541 tblinfo = getSchemaData(&numTables, schemaOnly, dataOnly);
544 getTableData(tblinfo, numTables, oids);
547 * Collect dependency data to assist in ordering the objects.
552 * Sort the objects into a safe dump order (no forward references).
554 getDumpableObjects(&dobjs, &numObjs);
556 sortDumpableObjectsByType(dobjs, numObjs);
557 sortDumpableObjects(dobjs, numObjs);
560 * Create archive TOC entries for all the objects to be dumped,
564 /* The database item is always first. */
566 dumpDatabase(g_fout);
568 /* Max OID is second. */
572 /* Now the rearrangeable objects. */
573 for (i = 0; i < numObjs; i++)
575 dumpDumpableObject(g_fout, dobjs[i]);
578 /* BLOBs are always last. */
580 ArchiveEntry(g_fout, nilCatalogId, createDumpId(),
582 "BLOBS", "", "", NULL,
587 * And finally we can do the actual output.
591 ropt = NewRestoreOptions();
592 ropt->filename = (char *) filename;
593 ropt->dropSchema = outputClean;
594 ropt->aclsSkip = aclsSkip;
595 ropt->superuser = outputSuperuser;
596 ropt->create = outputCreate;
597 ropt->noOwner = outputNoOwner;
598 ropt->disable_triggers = disable_triggers;
600 if (compressLevel == -1)
601 ropt->compression = 0;
603 ropt->compression = compressLevel;
605 ropt->suppressDumpWarnings = true; /* We've already shown
608 RestoreArchive(g_fout, ropt);
611 CloseArchive(g_fout);
620 help(const char *progname)
622 printf(_("%s dumps a database as a text file or to other formats.\n\n"), progname);
623 printf(_("Usage:\n"));
624 printf(_(" %s [OPTION]... [DBNAME]\n"), progname);
626 printf(_("\nGeneral options:\n"));
627 printf(_(" -f, --file=FILENAME output file name\n"));
628 printf(_(" -F, --format=c|t|p output file format (custom, tar, plain text)\n"));
629 printf(_(" -i, --ignore-version proceed even when server version mismatches\n"
630 " pg_dump version\n"));
631 printf(_(" -v, --verbose verbose mode\n"));
632 printf(_(" -Z, --compress=0-9 compression level for compressed formats\n"));
633 printf(_(" --help show this help, then exit\n"));
634 printf(_(" --version output version information, then exit\n"));
636 printf(_("\nOptions controlling the output content:\n"));
637 printf(_(" -a, --data-only dump only the data, not the schema\n"));
638 printf(_(" -b, --blobs include large objects in dump\n"));
639 printf(_(" -c, --clean clean (drop) schema prior to create\n"));
640 printf(_(" -C, --create include commands to create database in dump\n"));
641 printf(_(" -d, --inserts dump data as INSERT, rather than COPY, commands\n"));
642 printf(_(" -D, --column-inserts dump data as INSERT commands with column names\n"));
643 printf(_(" -n, --schema=SCHEMA dump the named schema only\n"));
644 printf(_(" -o, --oids include OIDs in dump\n"));
645 printf(_(" -O, --no-owner do not output commands to set object ownership\n"
646 " in plain text format\n"));
647 printf(_(" -s, --schema-only dump only the schema, no data\n"));
648 printf(_(" -S, --superuser=NAME specify the superuser user name to use in\n"
649 " plain text format\n"));
650 printf(_(" -t, --table=TABLE dump the named table only\n"));
651 printf(_(" -x, --no-privileges do not dump privileges (grant/revoke)\n"));
652 printf(_(" -X disable-triggers, --disable-triggers\n"
653 " disable triggers during data-only restore\n"));
655 printf(_("\nConnection options:\n"));
656 printf(_(" -h, --host=HOSTNAME database server host or socket directory\n"));
657 printf(_(" -p, --port=PORT database server port number\n"));
658 printf(_(" -U, --username=NAME connect as specified database user\n"));
659 printf(_(" -W, --password force password prompt (should happen automatically)\n"));
661 printf(_("\nIf no database name is supplied, then the PGDATABASE environment\n"
662 "variable value is used.\n\n"));
663 printf(_("Report bugs to <pgsql-bugs@postgresql.org>.\n"));
671 write_msg(NULL, "*** aborted because of error\n");
676 * selectDumpableNamespace: policy-setting subroutine
677 * Mark a namespace as to be dumped or not
680 selectDumpableNamespace(NamespaceInfo *nsinfo)
683 * If a specific table is being dumped, do not dump any complete
684 * namespaces. If a specific namespace is being dumped, dump just
685 * that namespace. Otherwise, dump all non-system namespaces.
687 if (selectTableName != NULL)
688 nsinfo->dump = false;
689 else if (selectSchemaName != NULL)
691 if (strcmp(nsinfo->nspname, selectSchemaName) == 0)
694 nsinfo->dump = false;
696 else if (strncmp(nsinfo->nspname, "pg_", 3) == 0 ||
697 strcmp(nsinfo->nspname, "information_schema") == 0)
698 nsinfo->dump = false;
704 * selectDumpableTable: policy-setting subroutine
705 * Mark a table as to be dumped or not
708 selectDumpableTable(TableInfo *tbinfo)
711 * Always dump if dumping parent namespace; else, if a particular
712 * tablename has been specified, dump matching table name; else, do
715 tbinfo->dump = false;
716 if (tbinfo->relnamespace->dump)
718 else if (selectTableName != NULL &&
719 strcmp(tbinfo->relname, selectTableName) == 0)
721 /* If both -s and -t specified, must match both to dump */
722 if (selectSchemaName == NULL)
724 else if (strcmp(tbinfo->relnamespace->nspname, selectSchemaName) == 0)
730 * Dump a table's contents for loading using the COPY command
731 * - this routine is called by the Archiver when it wants the table
735 #define COPYBUFSIZ 8192
738 dumpTableData_copy(Archive *fout, void *dcontext)
740 TableDataInfo *tdinfo = (TableDataInfo *) dcontext;
741 TableInfo *tbinfo = tdinfo->tdtable;
742 const char *classname = tbinfo->relname;
743 const bool hasoids = tbinfo->hasoids;
744 const bool oids = tdinfo->oids;
745 PQExpBuffer q = createPQExpBuffer();
749 char copybuf[COPYBUFSIZ];
750 const char *column_list;
753 write_msg(NULL, "dumping contents of table %s\n", classname);
756 * Make sure we are in proper schema. We will qualify the table name
757 * below anyway (in case its name conflicts with a pg_catalog table);
758 * but this ensures reproducible results in case the table contains
759 * regproc, regclass, etc columns.
761 selectSourceSchema(tbinfo->relnamespace->nspname);
764 * If possible, specify the column list explicitly so that we have no
765 * possibility of retrieving data in the wrong column order. (The
766 * default column ordering of COPY will not be what we want in certain
767 * corner cases involving ADD COLUMN and inheritance.)
769 if (g_fout->remoteVersion >= 70300)
770 column_list = fmtCopyColumnList(tbinfo);
772 column_list = ""; /* can't select columns in COPY */
776 appendPQExpBuffer(q, "COPY %s %s WITH OIDS TO stdout;",
777 fmtQualifiedId(tbinfo->relnamespace->nspname,
783 appendPQExpBuffer(q, "COPY %s %s TO stdout;",
784 fmtQualifiedId(tbinfo->relnamespace->nspname,
788 res = PQexec(g_conn, q->data);
789 check_sql_result(res, g_conn, q->data, PGRES_COPY_OUT);
795 ret = PQgetline(g_conn, copybuf, COPYBUFSIZ);
797 if (copybuf[0] == '\\' &&
801 copydone = true; /* don't print this... */
805 archputs(copybuf, fout);
812 archputs("\n", fout);
822 * There was considerable discussion in late July, 2000 regarding
823 * slowing down pg_dump when backing up large tables. Users with
824 * both slow & fast (muti-processor) machines experienced
825 * performance degradation when doing a backup.
827 * Initial attempts based on sleeping for a number of ms for each ms
828 * of work were deemed too complex, then a simple 'sleep in each
829 * loop' implementation was suggested. The latter failed because
830 * the loop was too tight. Finally, the following was implemented:
832 * If throttle is non-zero, then See how long since the last sleep.
833 * Work out how long to sleep (based on ratio). If sleep is more
834 * than 100ms, then sleep reset timer EndIf EndIf
836 * where the throttle value was the number of ms to sleep per ms of
837 * work. The calculation was done in each loop.
839 * Most of the hard work is done in the backend, and this solution
840 * still did not work particularly well: on slow machines, the
841 * ratio was 50:1, and on medium paced machines, 1:1, and on fast
842 * multi-processor machines, it had little or no effect, for
843 * reasons that were unclear.
845 * Further discussion ensued, and the proposal was dropped.
847 * For those people who want this feature, it can be implemented
848 * using gettimeofday in each loop, calculating the time since
849 * last sleep, multiplying that by the sleep ratio, then if the
850 * result is more than a preset 'minimum sleep time' (say 100ms),
851 * call the 'select' function to sleep for a subsecond period ie.
853 * select(0, NULL, NULL, NULL, &tvi);
855 * This will return after the interval specified in the structure
856 * tvi. Finally, call gettimeofday again to save the 'last sleep
860 archprintf(fout, "\\.\n\n\n");
862 ret = PQendcopy(g_conn);
865 write_msg(NULL, "SQL command to dump the contents of table \"%s\" failed: PQendcopy() failed.\n", classname);
866 write_msg(NULL, "Error message from server: %s", PQerrorMessage(g_conn));
867 write_msg(NULL, "The command was: %s\n", q->data);
872 destroyPQExpBuffer(q);
877 dumpTableData_insert(Archive *fout, void *dcontext)
879 TableDataInfo *tdinfo = (TableDataInfo *) dcontext;
880 TableInfo *tbinfo = tdinfo->tdtable;
881 const char *classname = tbinfo->relname;
882 PQExpBuffer q = createPQExpBuffer();
889 * Make sure we are in proper schema. We will qualify the table name
890 * below anyway (in case its name conflicts with a pg_catalog table);
891 * but this ensures reproducible results in case the table contains
892 * regproc, regclass, etc columns.
894 selectSourceSchema(tbinfo->relnamespace->nspname);
896 if (fout->remoteVersion >= 70100)
898 appendPQExpBuffer(q, "DECLARE _pg_dump_cursor CURSOR FOR "
899 "SELECT * FROM ONLY %s",
900 fmtQualifiedId(tbinfo->relnamespace->nspname,
905 appendPQExpBuffer(q, "DECLARE _pg_dump_cursor CURSOR FOR "
907 fmtQualifiedId(tbinfo->relnamespace->nspname,
911 res = PQexec(g_conn, q->data);
912 check_sql_result(res, g_conn, q->data, PGRES_COMMAND_OK);
918 res = PQexec(g_conn, "FETCH 100 FROM _pg_dump_cursor");
919 check_sql_result(res, g_conn, "FETCH 100 FROM _pg_dump_cursor",
921 nfields = PQnfields(res);
922 for (tuple = 0; tuple < PQntuples(res); tuple++)
924 archprintf(fout, "INSERT INTO %s ", fmtId(classname));
927 /* corner case for zero-column table */
928 archprintf(fout, "DEFAULT VALUES;\n");
931 if (attrNames == true)
934 appendPQExpBuffer(q, "(");
935 for (field = 0; field < nfields; field++)
938 appendPQExpBuffer(q, ", ");
939 appendPQExpBuffer(q, fmtId(PQfname(res, field)));
941 appendPQExpBuffer(q, ") ");
942 archprintf(fout, "%s", q->data);
944 archprintf(fout, "VALUES (");
945 for (field = 0; field < nfields; field++)
948 archprintf(fout, ", ");
949 if (PQgetisnull(res, tuple, field))
951 archprintf(fout, "NULL");
955 /* XXX This code is partially duplicated in ruleutils.c */
956 switch (PQftype(res, field))
967 * These types are printed without quotes
968 * unless they contain values that aren't
969 * accepted by the scanner unquoted (e.g.,
970 * 'NaN'). Note that strtod() and friends
971 * might accept NaN, so we can't use that to
974 * In reality we only need to defend against
975 * infinity and NaN, so we need not get too
976 * crazy about pattern matching here.
978 const char *s = PQgetvalue(res, tuple, field);
980 if (strspn(s, "0123456789 +-eE.") == strlen(s))
981 archprintf(fout, "%s", s);
983 archprintf(fout, "'%s'", s);
989 archprintf(fout, "B'%s'",
990 PQgetvalue(res, tuple, field));
994 if (strcmp(PQgetvalue(res, tuple, field), "t") == 0)
995 archprintf(fout, "true");
997 archprintf(fout, "false");
1001 /* All other types are printed as string literals. */
1002 resetPQExpBuffer(q);
1003 appendStringLiteral(q, PQgetvalue(res, tuple, field), false);
1004 archprintf(fout, "%s", q->data);
1008 archprintf(fout, ");\n");
1010 } while (PQntuples(res) > 0);
1014 archprintf(fout, "\n\n");
1016 do_sql_command(g_conn, "CLOSE _pg_dump_cursor");
1018 destroyPQExpBuffer(q);
1025 * dump the contents of a single table
1027 * Actually, this just makes an ArchiveEntry for the table contents.
1030 dumpTableData(Archive *fout, TableDataInfo *tdinfo)
1032 TableInfo *tbinfo = tdinfo->tdtable;
1033 PQExpBuffer copyBuf = createPQExpBuffer();
1034 DataDumperPtr dumpFn;
1039 /* Dump/restore using COPY */
1040 dumpFn = dumpTableData_copy;
1041 /* must use 2 steps here 'cause fmtId is nonreentrant */
1042 appendPQExpBuffer(copyBuf, "COPY %s ",
1043 fmtId(tbinfo->relname));
1044 appendPQExpBuffer(copyBuf, "%s %sFROM stdin;\n",
1045 fmtCopyColumnList(tbinfo),
1046 (tdinfo->oids && tbinfo->hasoids) ? "WITH OIDS " : "");
1047 copyStmt = copyBuf->data;
1051 /* Restore using INSERT */
1052 dumpFn = dumpTableData_insert;
1056 ArchiveEntry(fout, tdinfo->dobj.catId, tdinfo->dobj.dumpId,
1058 tbinfo->relnamespace->nspname,
1060 "TABLE DATA", "", "", copyStmt,
1061 tdinfo->dobj.dependencies, tdinfo->dobj.nDeps,
1064 destroyPQExpBuffer(copyBuf);
1069 * set up dumpable objects representing the contents of tables
1072 getTableData(TableInfo *tblinfo, int numTables, bool oids)
1076 for (i = 0; i < numTables; i++)
1078 /* Skip VIEWs (no data to dump) */
1079 if (tblinfo[i].relkind == RELKIND_VIEW)
1081 /* Skip SEQUENCEs (handled elsewhere) */
1082 if (tblinfo[i].relkind == RELKIND_SEQUENCE)
1085 if (tblinfo[i].dump)
1087 TableDataInfo *tdinfo;
1089 tdinfo = (TableDataInfo *) malloc(sizeof(TableDataInfo));
1091 tdinfo->dobj.objType = DO_TABLE_DATA;
1093 * Note: use tableoid 0 so that this object won't be mistaken
1094 * for something that pg_depend entries apply to.
1096 tdinfo->dobj.catId.tableoid = 0;
1097 tdinfo->dobj.catId.oid = tblinfo[i].dobj.catId.oid;
1098 AssignDumpId(&tdinfo->dobj);
1099 tdinfo->tdtable = &(tblinfo[i]);
1100 tdinfo->oids = oids;
1101 addObjectDependency(&tdinfo->dobj, tblinfo[i].dobj.dumpId);
1109 * dump the database definition
1112 dumpDatabase(Archive *AH)
1114 PQExpBuffer dbQry = createPQExpBuffer();
1115 PQExpBuffer delQry = createPQExpBuffer();
1116 PQExpBuffer creaQry = createPQExpBuffer();
1126 const char *datname,
1131 datname = PQdb(g_conn);
1134 write_msg(NULL, "saving database definition\n");
1136 /* Make sure we are in proper schema */
1137 selectSourceSchema("pg_catalog");
1139 /* Get the database owner and parameters from pg_database */
1140 if (g_fout->remoteVersion >= 70100)
1142 appendPQExpBuffer(dbQry, "SELECT tableoid, oid, "
1143 "(SELECT usename FROM pg_user WHERE usesysid = datdba) as dba, "
1144 "pg_encoding_to_char(encoding) as encoding, "
1147 "WHERE datname = ");
1148 appendStringLiteral(dbQry, datname, true);
1153 * In 7.0, datpath is either the same as datname, or the user-given
1154 * location with "/" and the datname appended. We must strip this
1155 * junk off to produce a correct LOCATION value.
1157 appendPQExpBuffer(dbQry, "SELECT "
1158 "(SELECT oid FROM pg_class WHERE relname = 'pg_database') AS tableoid, "
1160 "(SELECT usename FROM pg_user WHERE usesysid = datdba) as dba, "
1161 "pg_encoding_to_char(encoding) as encoding, "
1162 "CASE WHEN length(datpath) > length(datname) THEN "
1163 "substr(datpath,1,length(datpath)-length(datname)-1) "
1164 "ELSE '' END as datpath "
1166 "WHERE datname = ");
1167 appendStringLiteral(dbQry, datname, true);
1170 res = PQexec(g_conn, dbQry->data);
1171 check_sql_result(res, g_conn, dbQry->data, PGRES_TUPLES_OK);
1173 ntups = PQntuples(res);
1177 write_msg(NULL, "missing pg_database entry for database \"%s\"\n",
1184 write_msg(NULL, "query returned more than one (%d) pg_database entry for database \"%s\"\n",
1189 i_tableoid = PQfnumber(res, "tableoid");
1190 i_oid = PQfnumber(res, "oid");
1191 i_dba = PQfnumber(res, "dba");
1192 i_encoding = PQfnumber(res, "encoding");
1193 i_datpath = PQfnumber(res, "datpath");
1195 dbCatId.tableoid = atooid(PQgetvalue(res, 0, i_tableoid));
1196 dbCatId.oid = atooid(PQgetvalue(res, 0, i_oid));
1197 dba = PQgetvalue(res, 0, i_dba);
1198 encoding = PQgetvalue(res, 0, i_encoding);
1199 datpath = PQgetvalue(res, 0, i_datpath);
1201 appendPQExpBuffer(creaQry, "CREATE DATABASE %s WITH TEMPLATE = template0",
1203 if (strlen(datpath) > 0)
1205 appendPQExpBuffer(creaQry, " LOCATION = ");
1206 appendStringLiteral(creaQry, datpath, true);
1208 if (strlen(encoding) > 0)
1210 appendPQExpBuffer(creaQry, " ENCODING = ");
1211 appendStringLiteral(creaQry, encoding, true);
1213 appendPQExpBuffer(creaQry, ";\n");
1215 appendPQExpBuffer(delQry, "DROP DATABASE %s;\n",
1218 dbDumpId = createDumpId();
1221 dbCatId, /* catalog ID */
1222 dbDumpId, /* dump ID */
1224 NULL, /* Namespace */
1226 "DATABASE", /* Desc */
1227 creaQry->data, /* Create */
1228 delQry->data, /* Del */
1233 NULL); /* Dumper Arg */
1235 /* Dump DB comment if any */
1236 resetPQExpBuffer(dbQry);
1237 appendPQExpBuffer(dbQry, "DATABASE %s", fmtId(datname));
1238 dumpComment(AH, dbQry->data, NULL, "",
1239 dbCatId, 0, dbDumpId);
1243 destroyPQExpBuffer(dbQry);
1244 destroyPQExpBuffer(delQry);
1245 destroyPQExpBuffer(creaQry);
1255 #define loBufSize 16384
1256 #define loFetchSize 1000
1259 dumpBlobs(Archive *AH, void *arg)
1261 PQExpBuffer oidQry = createPQExpBuffer();
1262 PQExpBuffer oidFetchQry = createPQExpBuffer();
1266 char buf[loBufSize];
1271 write_msg(NULL, "saving large objects\n");
1273 /* Make sure we are in proper schema */
1274 selectSourceSchema("pg_catalog");
1276 /* Cursor to get all BLOB tables */
1277 if (AH->remoteVersion >= 70100)
1278 appendPQExpBuffer(oidQry, "Declare blobOid Cursor for SELECT DISTINCT loid FROM pg_largeobject");
1280 appendPQExpBuffer(oidQry, "Declare blobOid Cursor for SELECT oid from pg_class where relkind = 'l'");
1282 res = PQexec(g_conn, oidQry->data);
1283 check_sql_result(res, g_conn, oidQry->data, PGRES_COMMAND_OK);
1285 /* Fetch for cursor */
1286 appendPQExpBuffer(oidFetchQry, "Fetch %d in blobOid", loFetchSize);
1293 res = PQexec(g_conn, oidFetchQry->data);
1294 check_sql_result(res, g_conn, oidFetchQry->data, PGRES_TUPLES_OK);
1296 /* Process the tuples, if any */
1297 for (i = 0; i < PQntuples(res); i++)
1299 blobOid = atooid(PQgetvalue(res, i, 0));
1301 loFd = lo_open(g_conn, blobOid, INV_READ);
1304 write_msg(NULL, "dumpBlobs(): could not open large object: %s",
1305 PQerrorMessage(g_conn));
1309 StartBlob(AH, blobOid);
1311 /* Now read it in chunks, sending data to archive */
1314 cnt = lo_read(g_conn, loFd, buf, loBufSize);
1317 write_msg(NULL, "dumpBlobs(): error reading large object: %s",
1318 PQerrorMessage(g_conn));
1322 WriteData(AH, buf, cnt);
1326 lo_close(g_conn, loFd);
1328 EndBlob(AH, blobOid);
1331 } while (PQntuples(res) > 0);
1333 destroyPQExpBuffer(oidQry);
1334 destroyPQExpBuffer(oidFetchQry);
1341 * read all namespaces in the system catalogs and return them in the
1342 * NamespaceInfo* structure
1344 * numNamespaces is set to the number of namespaces read in
1347 getNamespaces(int *numNamespaces)
1353 NamespaceInfo *nsinfo;
1361 * Before 7.3, there are no real namespaces; create two dummy entries,
1362 * one for user stuff and one for system stuff.
1364 if (g_fout->remoteVersion < 70300)
1366 nsinfo = (NamespaceInfo *) malloc(2 * sizeof(NamespaceInfo));
1368 nsinfo[0].dobj.objType = DO_NAMESPACE;
1369 nsinfo[0].dobj.catId.tableoid = 0;
1370 nsinfo[0].dobj.catId.oid = 0;
1371 AssignDumpId(&nsinfo[0].dobj);
1372 nsinfo[0].nspname = strdup("");
1373 nsinfo[0].usename = strdup("");
1374 nsinfo[0].nspacl = strdup("");
1376 selectDumpableNamespace(&nsinfo[0]);
1378 nsinfo[1].dobj.objType = DO_NAMESPACE;
1379 nsinfo[1].dobj.catId.tableoid = 0;
1380 nsinfo[1].dobj.catId.oid = 1;
1381 AssignDumpId(&nsinfo[1].dobj);
1382 nsinfo[1].nspname = strdup("pg_catalog");
1383 nsinfo[1].usename = strdup("");
1384 nsinfo[1].nspacl = strdup("");
1386 selectDumpableNamespace(&nsinfo[1]);
1388 g_namespaces = nsinfo;
1389 g_numNamespaces = *numNamespaces = 2;
1394 query = createPQExpBuffer();
1396 /* Make sure we are in proper schema */
1397 selectSourceSchema("pg_catalog");
1400 * we fetch all namespaces including system ones, so that every object
1401 * we read in can be linked to a containing namespace.
1403 appendPQExpBuffer(query, "SELECT tableoid, oid, nspname, "
1404 "(select usename from pg_user where nspowner = usesysid) as usename, "
1406 "FROM pg_namespace");
1408 res = PQexec(g_conn, query->data);
1409 check_sql_result(res, g_conn, query->data, PGRES_TUPLES_OK);
1411 ntups = PQntuples(res);
1413 nsinfo = (NamespaceInfo *) malloc(ntups * sizeof(NamespaceInfo));
1415 i_tableoid = PQfnumber(res, "tableoid");
1416 i_oid = PQfnumber(res, "oid");
1417 i_nspname = PQfnumber(res, "nspname");
1418 i_usename = PQfnumber(res, "usename");
1419 i_nspacl = PQfnumber(res, "nspacl");
1421 for (i = 0; i < ntups; i++)
1423 nsinfo[i].dobj.objType = DO_NAMESPACE;
1424 nsinfo[i].dobj.catId.tableoid = atooid(PQgetvalue(res, i, i_tableoid));
1425 nsinfo[i].dobj.catId.oid = atooid(PQgetvalue(res, i, i_oid));
1426 AssignDumpId(&nsinfo[i].dobj);
1427 nsinfo[i].nspname = strdup(PQgetvalue(res, i, i_nspname));
1428 nsinfo[i].usename = strdup(PQgetvalue(res, i, i_usename));
1429 nsinfo[i].nspacl = strdup(PQgetvalue(res, i, i_nspacl));
1431 /* Decide whether to dump this namespace */
1432 selectDumpableNamespace(&nsinfo[i]);
1434 if (strlen(nsinfo[i].usename) == 0)
1435 write_msg(NULL, "WARNING: owner of schema \"%s\" appears to be invalid\n",
1440 * If the user attempted to dump a specific namespace, check to ensure
1441 * that the specified namespace actually exists.
1443 if (selectSchemaName)
1445 for (i = 0; i < ntups; i++)
1446 if (strcmp(nsinfo[i].nspname, selectSchemaName) == 0)
1449 /* Didn't find a match */
1452 write_msg(NULL, "specified schema \"%s\" does not exist\n",
1459 destroyPQExpBuffer(query);
1461 g_namespaces = nsinfo;
1462 g_numNamespaces = *numNamespaces = ntups;
1469 * given a namespace OID and an object OID, look up the info read by
1472 * NB: for pre-7.3 source database, we use object OID to guess whether it's
1473 * a system object or not. In 7.3 and later there is no guessing.
1475 static NamespaceInfo *
1476 findNamespace(Oid nsoid, Oid objoid)
1480 if (g_fout->remoteVersion >= 70300)
1482 for (i = 0; i < g_numNamespaces; i++)
1484 NamespaceInfo *nsinfo = &g_namespaces[i];
1486 if (nsoid == nsinfo->dobj.catId.oid)
1489 write_msg(NULL, "schema with OID %u does not exist\n", nsoid);
1494 /* This code depends on the layout set up by getNamespaces. */
1495 if (objoid > g_last_builtin_oid)
1496 i = 0; /* user object */
1498 i = 1; /* system object */
1499 return &g_namespaces[i];
1502 return NULL; /* keep compiler quiet */
1507 * read all types in the system catalogs and return them in the
1508 * TypeInfo* structure
1510 * numTypes is set to the number of types read in
1512 * NB: this must run after getFuncs() because we assume we can do
1516 getTypes(int *numTypes)
1521 PQExpBuffer query = createPQExpBuffer();
1537 * we include even the built-in types because those may be used as
1538 * array elements by user-defined types
1540 * we filter out the built-in types when we dump out the types
1542 * same approach for undefined (shell) types
1545 /* Make sure we are in proper schema */
1546 selectSourceSchema("pg_catalog");
1548 if (g_fout->remoteVersion >= 70300)
1550 appendPQExpBuffer(query, "SELECT tableoid, oid, typname, "
1552 "(select usename from pg_user where typowner = usesysid) as usename, "
1553 "typinput::oid as typinput, "
1554 "typoutput::oid as typoutput, typelem, typrelid, "
1555 "CASE WHEN typrelid = 0 THEN ' '::\"char\" "
1556 "ELSE (SELECT relkind FROM pg_class WHERE oid = typrelid) END as typrelkind, "
1557 "typtype, typisdefined "
1560 else if (g_fout->remoteVersion >= 70100)
1562 appendPQExpBuffer(query, "SELECT tableoid, oid, typname, "
1563 "0::oid as typnamespace, "
1564 "(select usename from pg_user where typowner = usesysid) as usename, "
1565 "typinput::oid as typinput, "
1566 "typoutput::oid as typoutput, typelem, typrelid, "
1567 "CASE WHEN typrelid = 0 THEN ' '::\"char\" "
1568 "ELSE (SELECT relkind FROM pg_class WHERE oid = typrelid) END as typrelkind, "
1569 "typtype, typisdefined "
1574 appendPQExpBuffer(query, "SELECT "
1575 "(SELECT oid FROM pg_class WHERE relname = 'pg_type') AS tableoid, "
1577 "0::oid as typnamespace, "
1578 "(select usename from pg_user where typowner = usesysid) as usename, "
1579 "typinput::oid as typinput, "
1580 "typoutput::oid as typoutput, typelem, typrelid, "
1581 "CASE WHEN typrelid = 0 THEN ' '::\"char\" "
1582 "ELSE (SELECT relkind FROM pg_class WHERE oid = typrelid) END as typrelkind, "
1583 "typtype, typisdefined "
1587 res = PQexec(g_conn, query->data);
1588 check_sql_result(res, g_conn, query->data, PGRES_TUPLES_OK);
1590 ntups = PQntuples(res);
1592 tinfo = (TypeInfo *) malloc(ntups * sizeof(TypeInfo));
1594 i_tableoid = PQfnumber(res, "tableoid");
1595 i_oid = PQfnumber(res, "oid");
1596 i_typname = PQfnumber(res, "typname");
1597 i_typnamespace = PQfnumber(res, "typnamespace");
1598 i_usename = PQfnumber(res, "usename");
1599 i_typinput = PQfnumber(res, "typinput");
1600 i_typoutput = PQfnumber(res, "typoutput");
1601 i_typelem = PQfnumber(res, "typelem");
1602 i_typrelid = PQfnumber(res, "typrelid");
1603 i_typrelkind = PQfnumber(res, "typrelkind");
1604 i_typtype = PQfnumber(res, "typtype");
1605 i_typisdefined = PQfnumber(res, "typisdefined");
1607 for (i = 0; i < ntups; i++)
1612 tinfo[i].dobj.objType = DO_TYPE;
1613 tinfo[i].dobj.catId.tableoid = atooid(PQgetvalue(res, i, i_tableoid));
1614 tinfo[i].dobj.catId.oid = atooid(PQgetvalue(res, i, i_oid));
1615 AssignDumpId(&tinfo[i].dobj);
1616 tinfo[i].typname = strdup(PQgetvalue(res, i, i_typname));
1617 tinfo[i].typnamespace = findNamespace(atooid(PQgetvalue(res, i, i_typnamespace)),
1618 tinfo[i].dobj.catId.oid);
1619 tinfo[i].usename = strdup(PQgetvalue(res, i, i_usename));
1620 tinfo[i].typinput = atooid(PQgetvalue(res, i, i_typinput));
1621 typoutput = atooid(PQgetvalue(res, i, i_typoutput));
1622 tinfo[i].typelem = atooid(PQgetvalue(res, i, i_typelem));
1623 tinfo[i].typrelid = atooid(PQgetvalue(res, i, i_typrelid));
1624 tinfo[i].typrelkind = *PQgetvalue(res, i, i_typrelkind);
1625 tinfo[i].typtype = *PQgetvalue(res, i, i_typtype);
1628 * check for user-defined array types, omit system generated ones
1630 if (OidIsValid(tinfo[i].typelem) &&
1631 tinfo[i].typname[0] != '_')
1632 tinfo[i].isArray = true;
1634 tinfo[i].isArray = false;
1636 if (strcmp(PQgetvalue(res, i, i_typisdefined), "t") == 0)
1637 tinfo[i].isDefined = true;
1639 tinfo[i].isDefined = false;
1642 * If it's a domain, fetch info about its constraints, if any
1644 tinfo[i].nDomChecks = 0;
1645 tinfo[i].domChecks = NULL;
1646 if (tinfo[i].typtype == 'd')
1647 getDomainConstraints(&(tinfo[i]));
1650 * Make sure there are dependencies from the type to its input and
1651 * output functions. (We don't worry about typsend, typreceive, or
1652 * typanalyze since those are only valid in 7.4 and later, wherein
1653 * the standard dependency mechanism will pick them up.)
1655 funcInfo = findFuncByOid(tinfo[i].typinput);
1657 addObjectDependency(&tinfo[i].dobj,
1658 funcInfo->dobj.dumpId);
1659 funcInfo = findFuncByOid(typoutput);
1661 addObjectDependency(&tinfo[i].dobj,
1662 funcInfo->dobj.dumpId);
1664 if (strlen(tinfo[i].usename) == 0 && tinfo[i].isDefined)
1665 write_msg(NULL, "WARNING: owner of data type \"%s\" appears to be invalid\n",
1673 destroyPQExpBuffer(query);
1680 * read all operators in the system catalogs and return them in the
1681 * OprInfo* structure
1683 * numOprs is set to the number of operators read in
1686 getOperators(int *numOprs)
1691 PQExpBuffer query = createPQExpBuffer();
1701 * find all operators, including builtin operators; we filter out
1702 * system-defined operators at dump-out time.
1705 /* Make sure we are in proper schema */
1706 selectSourceSchema("pg_catalog");
1708 if (g_fout->remoteVersion >= 70300)
1710 appendPQExpBuffer(query, "SELECT tableoid, oid, oprname, "
1712 "(select usename from pg_user where oprowner = usesysid) as usename, "
1713 "oprcode::oid as oprcode "
1714 "FROM pg_operator");
1716 else if (g_fout->remoteVersion >= 70100)
1718 appendPQExpBuffer(query, "SELECT tableoid, oid, oprname, "
1719 "0::oid as oprnamespace, "
1720 "(select usename from pg_user where oprowner = usesysid) as usename, "
1721 "oprcode::oid as oprcode "
1722 "FROM pg_operator");
1726 appendPQExpBuffer(query, "SELECT "
1727 "(SELECT oid FROM pg_class WHERE relname = 'pg_operator') AS tableoid, "
1729 "0::oid as oprnamespace, "
1730 "(select usename from pg_user where oprowner = usesysid) as usename, "
1731 "oprcode::oid as oprcode "
1732 "FROM pg_operator");
1735 res = PQexec(g_conn, query->data);
1736 check_sql_result(res, g_conn, query->data, PGRES_TUPLES_OK);
1738 ntups = PQntuples(res);
1741 oprinfo = (OprInfo *) malloc(ntups * sizeof(OprInfo));
1743 i_tableoid = PQfnumber(res, "tableoid");
1744 i_oid = PQfnumber(res, "oid");
1745 i_oprname = PQfnumber(res, "oprname");
1746 i_oprnamespace = PQfnumber(res, "oprnamespace");
1747 i_usename = PQfnumber(res, "usename");
1748 i_oprcode = PQfnumber(res, "oprcode");
1750 for (i = 0; i < ntups; i++)
1752 oprinfo[i].dobj.objType = DO_OPERATOR;
1753 oprinfo[i].dobj.catId.tableoid = atooid(PQgetvalue(res, i, i_tableoid));
1754 oprinfo[i].dobj.catId.oid = atooid(PQgetvalue(res, i, i_oid));
1755 AssignDumpId(&oprinfo[i].dobj);
1756 oprinfo[i].oprname = strdup(PQgetvalue(res, i, i_oprname));
1757 oprinfo[i].oprnamespace = findNamespace(atooid(PQgetvalue(res, i, i_oprnamespace)),
1758 oprinfo[i].dobj.catId.oid);
1759 oprinfo[i].usename = strdup(PQgetvalue(res, i, i_usename));
1760 oprinfo[i].oprcode = atooid(PQgetvalue(res, i, i_oprcode));
1762 if (strlen(oprinfo[i].usename) == 0)
1763 write_msg(NULL, "WARNING: owner of operator \"%s\" appears to be invalid\n",
1764 oprinfo[i].oprname);
1769 destroyPQExpBuffer(query);
1776 * read all conversions in the system catalogs and return them in the
1777 * ConvInfo* structure
1779 * numConversions is set to the number of conversions read in
1782 getConversions(int *numConversions)
1787 PQExpBuffer query = createPQExpBuffer();
1795 /* Conversions didn't exist pre-7.3 */
1796 if (g_fout->remoteVersion < 70300) {
1797 *numConversions = 0;
1802 * find all conversions, including builtin conversions; we filter out
1803 * system-defined conversions at dump-out time.
1806 /* Make sure we are in proper schema */
1807 selectSourceSchema("pg_catalog");
1809 appendPQExpBuffer(query, "SELECT tableoid, oid, conname, "
1811 "(select usename from pg_user where conowner = usesysid) as usename "
1812 "FROM pg_conversion");
1814 res = PQexec(g_conn, query->data);
1815 check_sql_result(res, g_conn, query->data, PGRES_TUPLES_OK);
1817 ntups = PQntuples(res);
1818 *numConversions = ntups;
1820 convinfo = (ConvInfo *) malloc(ntups * sizeof(ConvInfo));
1822 i_tableoid = PQfnumber(res, "tableoid");
1823 i_oid = PQfnumber(res, "oid");
1824 i_conname = PQfnumber(res, "conname");
1825 i_connamespace = PQfnumber(res, "connamespace");
1826 i_usename = PQfnumber(res, "usename");
1828 for (i = 0; i < ntups; i++)
1830 convinfo[i].dobj.objType = DO_CONVERSION;
1831 convinfo[i].dobj.catId.tableoid = atooid(PQgetvalue(res, i, i_tableoid));
1832 convinfo[i].dobj.catId.oid = atooid(PQgetvalue(res, i, i_oid));
1833 AssignDumpId(&convinfo[i].dobj);
1834 convinfo[i].conname = strdup(PQgetvalue(res, i, i_conname));
1835 convinfo[i].connamespace = findNamespace(atooid(PQgetvalue(res, i, i_connamespace)),
1836 convinfo[i].dobj.catId.oid);
1837 convinfo[i].usename = strdup(PQgetvalue(res, i, i_usename));
1842 destroyPQExpBuffer(query);
1849 * read all opclasses in the system catalogs and return them in the
1850 * OpclassInfo* structure
1852 * numOpclasses is set to the number of opclasses read in
1855 getOpclasses(int *numOpclasses)
1860 PQExpBuffer query = createPQExpBuffer();
1861 OpclassInfo *opcinfo;
1869 * find all opclasses, including builtin opclasses; we filter out
1870 * system-defined opclasses at dump-out time.
1873 /* Make sure we are in proper schema */
1874 selectSourceSchema("pg_catalog");
1876 if (g_fout->remoteVersion >= 70300)
1878 appendPQExpBuffer(query, "SELECT tableoid, oid, opcname, "
1880 "(select usename from pg_user where opcowner = usesysid) as usename "
1883 else if (g_fout->remoteVersion >= 70100)
1885 appendPQExpBuffer(query, "SELECT tableoid, oid, opcname, "
1886 "0::oid as opcnamespace, "
1887 "''::name as usename "
1892 appendPQExpBuffer(query, "SELECT "
1893 "(SELECT oid FROM pg_class WHERE relname = 'pg_opclass') AS tableoid, "
1895 "0::oid as opcnamespace, "
1896 "''::name as usename "
1900 res = PQexec(g_conn, query->data);
1901 check_sql_result(res, g_conn, query->data, PGRES_TUPLES_OK);
1903 ntups = PQntuples(res);
1904 *numOpclasses = ntups;
1906 opcinfo = (OpclassInfo *) malloc(ntups * sizeof(OpclassInfo));
1908 i_tableoid = PQfnumber(res, "tableoid");
1909 i_oid = PQfnumber(res, "oid");
1910 i_opcname = PQfnumber(res, "opcname");
1911 i_opcnamespace = PQfnumber(res, "opcnamespace");
1912 i_usename = PQfnumber(res, "usename");
1914 for (i = 0; i < ntups; i++)
1916 opcinfo[i].dobj.objType = DO_OPCLASS;
1917 opcinfo[i].dobj.catId.tableoid = atooid(PQgetvalue(res, i, i_tableoid));
1918 opcinfo[i].dobj.catId.oid = atooid(PQgetvalue(res, i, i_oid));
1919 AssignDumpId(&opcinfo[i].dobj);
1920 opcinfo[i].opcname = strdup(PQgetvalue(res, i, i_opcname));
1921 opcinfo[i].opcnamespace = findNamespace(atooid(PQgetvalue(res, i, i_opcnamespace)),
1922 opcinfo[i].dobj.catId.oid);
1923 opcinfo[i].usename = strdup(PQgetvalue(res, i, i_usename));
1925 if (g_fout->remoteVersion >= 70300)
1927 if (strlen(opcinfo[i].usename) == 0)
1928 write_msg(NULL, "WARNING: owner of operator class \"%s\" appears to be invalid\n",
1929 opcinfo[i].opcname);
1935 destroyPQExpBuffer(query);
1942 * read all the user-defined aggregates in the system catalogs and
1943 * return them in the AggInfo* structure
1945 * numAggs is set to the number of aggregates read in
1948 getAggregates(int *numAggs)
1953 PQExpBuffer query = createPQExpBuffer();
1963 /* Make sure we are in proper schema */
1964 selectSourceSchema("pg_catalog");
1966 /* find all user-defined aggregates */
1968 if (g_fout->remoteVersion >= 70300)
1970 appendPQExpBuffer(query, "SELECT tableoid, oid, proname as aggname, "
1971 "pronamespace as aggnamespace, "
1972 "proargtypes[0] as aggbasetype, "
1973 "(select usename from pg_user where proowner = usesysid) as usename, "
1977 "AND pronamespace != "
1978 "(select oid from pg_namespace where nspname = 'pg_catalog')");
1980 else if (g_fout->remoteVersion >= 70100)
1982 appendPQExpBuffer(query, "SELECT tableoid, oid, aggname, "
1983 "0::oid as aggnamespace, "
1985 "(select usename from pg_user where aggowner = usesysid) as usename, "
1987 "FROM pg_aggregate "
1988 "where oid > '%u'::oid",
1989 g_last_builtin_oid);
1993 appendPQExpBuffer(query, "SELECT "
1994 "(SELECT oid FROM pg_class WHERE relname = 'pg_aggregate') AS tableoid, "
1996 "0::oid as aggnamespace, "
1998 "(select usename from pg_user where aggowner = usesysid) as usename, "
2000 "FROM pg_aggregate "
2001 "where oid > '%u'::oid",
2002 g_last_builtin_oid);
2005 res = PQexec(g_conn, query->data);
2006 check_sql_result(res, g_conn, query->data, PGRES_TUPLES_OK);
2008 ntups = PQntuples(res);
2011 agginfo = (AggInfo *) malloc(ntups * sizeof(AggInfo));
2013 i_tableoid = PQfnumber(res, "tableoid");
2014 i_oid = PQfnumber(res, "oid");
2015 i_aggname = PQfnumber(res, "aggname");
2016 i_aggnamespace = PQfnumber(res, "aggnamespace");
2017 i_aggbasetype = PQfnumber(res, "aggbasetype");
2018 i_usename = PQfnumber(res, "usename");
2019 i_aggacl = PQfnumber(res, "aggacl");
2021 for (i = 0; i < ntups; i++)
2023 agginfo[i].aggfn.dobj.objType = DO_AGG;
2024 agginfo[i].aggfn.dobj.catId.tableoid = atooid(PQgetvalue(res, i, i_tableoid));
2025 agginfo[i].aggfn.dobj.catId.oid = atooid(PQgetvalue(res, i, i_oid));
2026 AssignDumpId(&agginfo[i].aggfn.dobj);
2027 agginfo[i].aggfn.proname = strdup(PQgetvalue(res, i, i_aggname));
2028 agginfo[i].aggfn.pronamespace = findNamespace(atooid(PQgetvalue(res, i, i_aggnamespace)),
2029 agginfo[i].aggfn.dobj.catId.oid);
2030 agginfo[i].aggfn.usename = strdup(PQgetvalue(res, i, i_usename));
2031 if (strlen(agginfo[i].aggfn.usename) == 0)
2032 write_msg(NULL, "WARNING: owner of aggregate function \"%s\" appears to be invalid\n",
2033 agginfo[i].aggfn.proname);
2034 agginfo[i].aggfn.lang = InvalidOid; /* not currently interesting */
2035 agginfo[i].aggfn.nargs = 1;
2036 agginfo[i].aggfn.argtypes = (Oid *) malloc(sizeof(Oid));
2037 agginfo[i].aggfn.argtypes[0] = atooid(PQgetvalue(res, i, i_aggbasetype));
2038 agginfo[i].aggfn.prorettype = InvalidOid; /* not saved */
2039 agginfo[i].aggfn.proacl = strdup(PQgetvalue(res, i, i_aggacl));
2040 agginfo[i].anybasetype = false; /* computed when it's dumped */
2041 agginfo[i].fmtbasetype = NULL; /* computed when it's dumped */
2046 destroyPQExpBuffer(query);
2053 * read all the user-defined functions in the system catalogs and
2054 * return them in the FuncInfo* structure
2056 * numFuncs is set to the number of functions read in
2059 getFuncs(int *numFuncs)
2064 PQExpBuffer query = createPQExpBuffer();
2077 /* Make sure we are in proper schema */
2078 selectSourceSchema("pg_catalog");
2080 /* find all user-defined funcs */
2082 if (g_fout->remoteVersion >= 70300)
2084 appendPQExpBuffer(query,
2085 "SELECT tableoid, oid, proname, prolang, "
2086 "pronargs, proargtypes, prorettype, proacl, "
2088 "(select usename from pg_user where proowner = usesysid) as usename "
2090 "WHERE NOT proisagg "
2091 "AND pronamespace != "
2092 "(select oid from pg_namespace where nspname = 'pg_catalog')");
2094 else if (g_fout->remoteVersion >= 70100)
2096 appendPQExpBuffer(query,
2097 "SELECT tableoid, oid, proname, prolang, "
2098 "pronargs, proargtypes, prorettype, "
2099 "'{=X}' as proacl, "
2100 "0::oid as pronamespace, "
2101 "(select usename from pg_user where proowner = usesysid) as usename "
2103 "where pg_proc.oid > '%u'::oid",
2104 g_last_builtin_oid);
2108 appendPQExpBuffer(query,
2110 "(SELECT oid FROM pg_class WHERE relname = 'pg_proc') AS tableoid, "
2111 "oid, proname, prolang, "
2112 "pronargs, proargtypes, prorettype, "
2113 "'{=X}' as proacl, "
2114 "0::oid as pronamespace, "
2115 "(select usename from pg_user where proowner = usesysid) as usename "
2117 "where pg_proc.oid > '%u'::oid",
2118 g_last_builtin_oid);
2121 res = PQexec(g_conn, query->data);
2122 check_sql_result(res, g_conn, query->data, PGRES_TUPLES_OK);
2124 ntups = PQntuples(res);
2128 finfo = (FuncInfo *) calloc(ntups, sizeof(FuncInfo));
2130 i_tableoid = PQfnumber(res, "tableoid");
2131 i_oid = PQfnumber(res, "oid");
2132 i_proname = PQfnumber(res, "proname");
2133 i_pronamespace = PQfnumber(res, "pronamespace");
2134 i_usename = PQfnumber(res, "usename");
2135 i_prolang = PQfnumber(res, "prolang");
2136 i_pronargs = PQfnumber(res, "pronargs");
2137 i_proargtypes = PQfnumber(res, "proargtypes");
2138 i_prorettype = PQfnumber(res, "prorettype");
2139 i_proacl = PQfnumber(res, "proacl");
2141 for (i = 0; i < ntups; i++)
2143 finfo[i].dobj.objType = DO_FUNC;
2144 finfo[i].dobj.catId.tableoid = atooid(PQgetvalue(res, i, i_tableoid));
2145 finfo[i].dobj.catId.oid = atooid(PQgetvalue(res, i, i_oid));
2146 AssignDumpId(&finfo[i].dobj);
2147 finfo[i].proname = strdup(PQgetvalue(res, i, i_proname));
2148 finfo[i].pronamespace = findNamespace(atooid(PQgetvalue(res, i, i_pronamespace)),
2149 finfo[i].dobj.catId.oid);
2150 finfo[i].usename = strdup(PQgetvalue(res, i, i_usename));
2151 finfo[i].lang = atooid(PQgetvalue(res, i, i_prolang));
2152 finfo[i].prorettype = atooid(PQgetvalue(res, i, i_prorettype));
2153 finfo[i].proacl = strdup(PQgetvalue(res, i, i_proacl));
2154 finfo[i].nargs = atoi(PQgetvalue(res, i, i_pronargs));
2155 if (finfo[i].nargs == 0)
2156 finfo[i].argtypes = NULL;
2159 finfo[i].argtypes = (Oid *) malloc(finfo[i].nargs * sizeof(Oid));
2160 parseOidArray(PQgetvalue(res, i, i_proargtypes),
2161 finfo[i].argtypes, finfo[i].nargs);
2164 if (strlen(finfo[i].usename) == 0)
2165 write_msg(NULL, "WARNING: owner of function \"%s\" appears to be invalid\n",
2171 destroyPQExpBuffer(query);
2178 * read all the user-defined tables (no indexes, no catalogs)
2179 * in the system catalogs return them in the TableInfo* structure
2181 * numTables is set to the number of tables read in
2184 getTables(int *numTables)
2189 PQExpBuffer query = createPQExpBuffer();
2190 PQExpBuffer delqry = createPQExpBuffer();
2191 PQExpBuffer lockquery = createPQExpBuffer();
2208 /* Make sure we are in proper schema */
2209 selectSourceSchema("pg_catalog");
2212 * Find all the tables (including views and sequences).
2214 * We include system catalogs, so that we can work if a user table is
2215 * defined to inherit from a system catalog (pretty weird, but...)
2217 * We ignore tables that are not type 'r' (ordinary relation) or 'S'
2218 * (sequence) or 'v' (view).
2220 * Note: in this phase we should collect only a minimal amount of
2221 * information about each table, basically just enough to decide if it
2222 * is interesting. We must fetch all tables in this phase because
2223 * otherwise we cannot correctly identify inherited columns, serial
2227 if (g_fout->remoteVersion >= 70300)
2230 * Left join to pick up dependency info linking sequences to their
2231 * serial column, if any
2233 appendPQExpBuffer(query,
2234 "SELECT c.tableoid, c.oid, relname, "
2235 "relacl, relkind, relnamespace, "
2236 "(select usename from pg_user where relowner = usesysid) as usename, "
2237 "relchecks, reltriggers, "
2238 "relhasindex, relhasrules, relhasoids, "
2239 "d.refobjid as owning_tab, "
2240 "d.refobjsubid as owning_col "
2242 "left join pg_depend d on "
2243 "(c.relkind = '%c' and "
2244 "d.classid = c.tableoid and d.objid = c.oid and "
2245 "d.objsubid = 0 and "
2246 "d.refclassid = c.tableoid and d.deptype = 'i') "
2247 "where relkind in ('%c', '%c', '%c') "
2250 RELKIND_RELATION, RELKIND_SEQUENCE, RELKIND_VIEW);
2252 else if (g_fout->remoteVersion >= 70200)
2254 appendPQExpBuffer(query,
2255 "SELECT tableoid, oid, relname, relacl, relkind, "
2256 "0::oid as relnamespace, "
2257 "(select usename from pg_user where relowner = usesysid) as usename, "
2258 "relchecks, reltriggers, "
2259 "relhasindex, relhasrules, relhasoids, "
2260 "NULL::oid as owning_tab, "
2261 "NULL::int4 as owning_col "
2263 "where relkind in ('%c', '%c', '%c') "
2265 RELKIND_RELATION, RELKIND_SEQUENCE, RELKIND_VIEW);
2267 else if (g_fout->remoteVersion >= 70100)
2269 /* all tables have oids in 7.1 */
2270 appendPQExpBuffer(query,
2271 "SELECT tableoid, oid, relname, relacl, relkind, "
2272 "0::oid as relnamespace, "
2273 "(select usename from pg_user where relowner = usesysid) as usename, "
2274 "relchecks, reltriggers, "
2275 "relhasindex, relhasrules, "
2276 "'t'::bool as relhasoids, "
2277 "NULL::oid as owning_tab, "
2278 "NULL::int4 as owning_col "
2280 "where relkind in ('%c', '%c', '%c') "
2282 RELKIND_RELATION, RELKIND_SEQUENCE, RELKIND_VIEW);
2287 * Before 7.1, view relkind was not set to 'v', so we must check
2288 * if we have a view by looking for a rule in pg_rewrite.
2290 appendPQExpBuffer(query,
2292 "(SELECT oid FROM pg_class WHERE relname = 'pg_class') AS tableoid, "
2293 "oid, relname, relacl, "
2294 "CASE WHEN relhasrules and relkind = 'r' "
2295 " and EXISTS(SELECT rulename FROM pg_rewrite r WHERE "
2296 " r.ev_class = c.oid AND r.ev_type = '1') "
2297 "THEN '%c'::\"char\" "
2298 "ELSE relkind END AS relkind,"
2299 "0::oid as relnamespace, "
2300 "(select usename from pg_user where relowner = usesysid) as usename, "
2301 "relchecks, reltriggers, "
2302 "relhasindex, relhasrules, "
2303 "'t'::bool as relhasoids, "
2304 "NULL::oid as owning_tab, "
2305 "NULL::int4 as owning_col "
2307 "where relkind in ('%c', '%c') "
2310 RELKIND_RELATION, RELKIND_SEQUENCE);
2313 res = PQexec(g_conn, query->data);
2314 check_sql_result(res, g_conn, query->data, PGRES_TUPLES_OK);
2316 ntups = PQntuples(res);
2321 * Extract data from result and lock dumpable tables. We do the
2322 * locking before anything else, to minimize the window wherein a
2323 * table could disappear under us.
2325 * Note that we have to save info about all tables here, even when
2326 * dumping only one, because we don't yet know which tables might be
2327 * inheritance ancestors of the target table.
2329 tblinfo = (TableInfo *) calloc(ntups, sizeof(TableInfo));
2331 i_reltableoid = PQfnumber(res, "tableoid");
2332 i_reloid = PQfnumber(res, "oid");
2333 i_relname = PQfnumber(res, "relname");
2334 i_relnamespace = PQfnumber(res, "relnamespace");
2335 i_relacl = PQfnumber(res, "relacl");
2336 i_relkind = PQfnumber(res, "relkind");
2337 i_usename = PQfnumber(res, "usename");
2338 i_relchecks = PQfnumber(res, "relchecks");
2339 i_reltriggers = PQfnumber(res, "reltriggers");
2340 i_relhasindex = PQfnumber(res, "relhasindex");
2341 i_relhasrules = PQfnumber(res, "relhasrules");
2342 i_relhasoids = PQfnumber(res, "relhasoids");
2343 i_owning_tab = PQfnumber(res, "owning_tab");
2344 i_owning_col = PQfnumber(res, "owning_col");
2346 for (i = 0; i < ntups; i++)
2348 tblinfo[i].dobj.objType = DO_TABLE;
2349 tblinfo[i].dobj.catId.tableoid = atooid(PQgetvalue(res, i, i_reltableoid));
2350 tblinfo[i].dobj.catId.oid = atooid(PQgetvalue(res, i, i_reloid));
2351 AssignDumpId(&tblinfo[i].dobj);
2352 tblinfo[i].relname = strdup(PQgetvalue(res, i, i_relname));
2353 tblinfo[i].relnamespace = findNamespace(atooid(PQgetvalue(res, i, i_relnamespace)),
2354 tblinfo[i].dobj.catId.oid);
2355 tblinfo[i].usename = strdup(PQgetvalue(res, i, i_usename));
2356 tblinfo[i].relacl = strdup(PQgetvalue(res, i, i_relacl));
2357 tblinfo[i].relkind = *(PQgetvalue(res, i, i_relkind));
2358 tblinfo[i].hasindex = (strcmp(PQgetvalue(res, i, i_relhasindex), "t") == 0);
2359 tblinfo[i].hasrules = (strcmp(PQgetvalue(res, i, i_relhasrules), "t") == 0);
2360 tblinfo[i].hasoids = (strcmp(PQgetvalue(res, i, i_relhasoids), "t") == 0);
2361 tblinfo[i].ncheck = atoi(PQgetvalue(res, i, i_relchecks));
2362 tblinfo[i].ntrig = atoi(PQgetvalue(res, i, i_reltriggers));
2363 if (PQgetisnull(res, i, i_owning_tab))
2365 tblinfo[i].owning_tab = InvalidOid;
2366 tblinfo[i].owning_col = 0;
2370 tblinfo[i].owning_tab = atooid(PQgetvalue(res, i, i_owning_tab));
2371 tblinfo[i].owning_col = atoi(PQgetvalue(res, i, i_owning_col));
2374 /* other fields were zeroed above */
2377 * Decide whether we want to dump this table. Sequences owned by
2378 * serial columns are never dumpable on their own; we will
2379 * transpose their owning table's dump flag to them below.
2381 if (OidIsValid(tblinfo[i].owning_tab))
2382 tblinfo[i].dump = false;
2384 selectDumpableTable(&tblinfo[i]);
2385 tblinfo[i].interesting = tblinfo[i].dump;
2388 * Read-lock target tables to make sure they aren't DROPPED or
2389 * altered in schema before we get around to dumping them.
2391 * Note that we don't explicitly lock parents of the target tables;
2392 * we assume our lock on the child is enough to prevent schema
2393 * alterations to parent tables.
2395 * NOTE: it'd be kinda nice to lock views and sequences too, not only
2396 * plain tables, but the backend doesn't presently allow that.
2398 if (tblinfo[i].dump && tblinfo[i].relkind == RELKIND_RELATION)
2400 resetPQExpBuffer(lockquery);
2401 appendPQExpBuffer(lockquery,
2402 "LOCK TABLE %s IN ACCESS SHARE MODE",
2403 fmtQualifiedId(tblinfo[i].relnamespace->nspname,
2404 tblinfo[i].relname));
2405 do_sql_command(g_conn, lockquery->data);
2408 /* Emit notice if join for owner failed */
2409 if (strlen(tblinfo[i].usename) == 0)
2410 write_msg(NULL, "WARNING: owner of table \"%s\" appears to be invalid\n",
2411 tblinfo[i].relname);
2415 * If the user is attempting to dump a specific table, check to ensure
2416 * that the specified table actually exists. (This is a bit simplistic
2417 * since we don't fully check the combination of -n and -t switches.)
2419 if (selectTableName)
2421 for (i = 0; i < ntups; i++)
2422 if (strcmp(tblinfo[i].relname, selectTableName) == 0)
2425 /* Didn't find a match */
2428 write_msg(NULL, "specified table \"%s\" does not exist\n",
2435 destroyPQExpBuffer(query);
2436 destroyPQExpBuffer(delqry);
2437 destroyPQExpBuffer(lockquery);
2444 * read all the inheritance information
2445 * from the system catalogs return them in the InhInfo* structure
2447 * numInherits is set to the number of pairs read in
2450 getInherits(int *numInherits)
2455 PQExpBuffer query = createPQExpBuffer();
2461 /* Make sure we are in proper schema */
2462 selectSourceSchema("pg_catalog");
2464 /* find all the inheritance information */
2466 appendPQExpBuffer(query, "SELECT inhrelid, inhparent from pg_inherits");
2468 res = PQexec(g_conn, query->data);
2469 check_sql_result(res, g_conn, query->data, PGRES_TUPLES_OK);
2471 ntups = PQntuples(res);
2473 *numInherits = ntups;
2475 inhinfo = (InhInfo *) malloc(ntups * sizeof(InhInfo));
2477 i_inhrelid = PQfnumber(res, "inhrelid");
2478 i_inhparent = PQfnumber(res, "inhparent");
2480 for (i = 0; i < ntups; i++)
2482 inhinfo[i].inhrelid = atooid(PQgetvalue(res, i, i_inhrelid));
2483 inhinfo[i].inhparent = atooid(PQgetvalue(res, i, i_inhparent));
2488 destroyPQExpBuffer(query);
2495 * get information about every index on a dumpable table
2497 * Note: index data is not returned directly to the caller, but it
2498 * does get entered into the DumpableObject tables.
2501 getIndexes(TableInfo tblinfo[], int numTables)
2505 PQExpBuffer query = createPQExpBuffer();
2508 ConstraintInfo *constrinfo;
2522 for (i = 0; i < numTables; i++)
2524 TableInfo *tbinfo = &tblinfo[i];
2526 /* Only plain tables have indexes */
2527 if (tbinfo->relkind != RELKIND_RELATION || !tbinfo->hasindex)
2534 write_msg(NULL, "reading indexes for table \"%s\"\n",
2537 /* Make sure we are in proper schema so indexdef is right */
2538 selectSourceSchema(tbinfo->relnamespace->nspname);
2541 * The point of the messy-looking outer join is to find a
2542 * constraint that is related by an internal dependency link to
2543 * the index. If we find one, create a CONSTRAINT entry linked
2544 * to the INDEX entry. We assume an index won't have more than
2545 * one internal dependency.
2547 resetPQExpBuffer(query);
2548 if (g_fout->remoteVersion >= 70300)
2550 appendPQExpBuffer(query,
2551 "SELECT t.tableoid, t.oid, "
2552 "t.relname as indexname, "
2553 "pg_catalog.pg_get_indexdef(i.indexrelid) as indexdef, "
2554 "t.relnatts as indnkeys, "
2555 "i.indkey, i.indisclustered, "
2556 "c.contype, c.conname, "
2557 "c.tableoid as contableoid, "
2559 "FROM pg_catalog.pg_index i "
2560 "JOIN pg_catalog.pg_class t ON (t.oid = i.indexrelid) "
2561 "LEFT JOIN pg_catalog.pg_depend d "
2562 "ON (d.classid = t.tableoid "
2563 "AND d.objid = t.oid "
2564 "AND d.deptype = 'i') "
2565 "LEFT JOIN pg_catalog.pg_constraint c "
2566 "ON (d.refclassid = c.tableoid "
2567 "AND d.refobjid = c.oid) "
2568 "WHERE i.indrelid = '%u'::pg_catalog.oid "
2569 "ORDER BY indexname",
2570 tbinfo->dobj.catId.oid);
2572 else if (g_fout->remoteVersion >= 70100)
2574 appendPQExpBuffer(query,
2575 "SELECT t.tableoid, t.oid, "
2576 "t.relname as indexname, "
2577 "pg_get_indexdef(i.indexrelid) as indexdef, "
2578 "t.relnatts as indnkeys, "
2579 "i.indkey, false as indisclustered, "
2580 "CASE WHEN i.indisprimary THEN 'p'::char "
2581 "ELSE '0'::char END as contype, "
2582 "t.relname as conname, "
2583 "0::oid as contableoid, "
2585 "FROM pg_index i, pg_class t "
2586 "WHERE t.oid = i.indexrelid "
2587 "AND i.indrelid = '%u'::oid "
2588 "ORDER BY indexname",
2589 tbinfo->dobj.catId.oid);
2593 appendPQExpBuffer(query,
2595 "(SELECT oid FROM pg_class WHERE relname = 'pg_class') AS tableoid, "
2597 "t.relname as indexname, "
2598 "pg_get_indexdef(i.indexrelid) as indexdef, "
2599 "t.relnatts as indnkeys, "
2600 "i.indkey, false as indisclustered, "
2601 "CASE WHEN i.indisprimary THEN 'p'::char "
2602 "ELSE '0'::char END as contype, "
2603 "t.relname as conname, "
2604 "0::oid as contableoid, "
2606 "FROM pg_index i, pg_class t "
2607 "WHERE t.oid = i.indexrelid "
2608 "AND i.indrelid = '%u'::oid "
2609 "ORDER BY indexname",
2610 tbinfo->dobj.catId.oid);
2613 res = PQexec(g_conn, query->data);
2614 check_sql_result(res, g_conn, query->data, PGRES_TUPLES_OK);
2616 ntups = PQntuples(res);
2618 i_tableoid = PQfnumber(res, "tableoid");
2619 i_oid = PQfnumber(res, "oid");
2620 i_indexname = PQfnumber(res, "indexname");
2621 i_indexdef = PQfnumber(res, "indexdef");
2622 i_indnkeys = PQfnumber(res, "indnkeys");
2623 i_indkey = PQfnumber(res, "indkey");
2624 i_indisclustered = PQfnumber(res, "indisclustered");
2625 i_contype = PQfnumber(res, "contype");
2626 i_conname = PQfnumber(res, "conname");
2627 i_contableoid = PQfnumber(res, "contableoid");
2628 i_conoid = PQfnumber(res, "conoid");
2630 indxinfo = (IndxInfo *) malloc(ntups * sizeof(IndxInfo));
2631 constrinfo = (ConstraintInfo *) malloc(ntups * sizeof(ConstraintInfo));
2633 for (j = 0; j < ntups; j++)
2637 indxinfo[j].dobj.objType = DO_INDEX;
2638 indxinfo[j].dobj.catId.tableoid = atooid(PQgetvalue(res, j, i_tableoid));
2639 indxinfo[j].dobj.catId.oid = atooid(PQgetvalue(res, j, i_oid));
2640 AssignDumpId(&indxinfo[j].dobj);
2641 indxinfo[j].indexname = strdup(PQgetvalue(res, j, i_indexname));
2642 indxinfo[j].indextable = tbinfo;
2643 indxinfo[j].indexdef = strdup(PQgetvalue(res, j, i_indexdef));
2644 indxinfo[j].indnkeys = atoi(PQgetvalue(res, j, i_indnkeys));
2646 * In pre-7.4 releases, indkeys may contain more entries than
2647 * indnkeys says (since indnkeys will be 1 for a functional
2648 * index). We don't actually care about this case since we don't
2649 * examine indkeys except for indexes associated with PRIMARY
2650 * and UNIQUE constraints, which are never functional indexes.
2651 * But we have to allocate enough space to keep parseOidArray
2654 indxinfo[j].indkeys = (Oid *) malloc(INDEX_MAX_KEYS * sizeof(Oid));
2655 parseOidArray(PQgetvalue(res, j, i_indkey),
2656 indxinfo[j].indkeys, INDEX_MAX_KEYS);
2657 indxinfo[j].indisclustered = (PQgetvalue(res, j, i_indisclustered)[0] == 't');
2658 contype = *(PQgetvalue(res, j, i_contype));
2660 if (contype == 'p' || contype == 'u')
2663 * If we found a constraint matching the index, create an
2666 * In a pre-7.3 database, we take this path iff the index was
2667 * marked indisprimary.
2669 constrinfo[j].dobj.objType = DO_CONSTRAINT;
2670 constrinfo[j].dobj.catId.tableoid = atooid(PQgetvalue(res, j, i_contableoid));
2671 constrinfo[j].dobj.catId.oid = atooid(PQgetvalue(res, j, i_conoid));
2672 AssignDumpId(&constrinfo[j].dobj);
2674 constrinfo[j].conname = strdup(PQgetvalue(res, j, i_conname));
2675 constrinfo[j].contable = tbinfo;
2676 constrinfo[j].condomain = NULL;
2677 constrinfo[j].contype = contype;
2678 constrinfo[j].condef = NULL;
2679 constrinfo[j].conindex = indxinfo[j].dobj.dumpId;
2680 constrinfo[j].coninherited = false;
2681 constrinfo[j].separate = true;
2683 indxinfo[j].indexconstraint = constrinfo[j].dobj.dumpId;
2685 /* If pre-7.3 DB, better make sure table comes first */
2686 addObjectDependency(&constrinfo[j].dobj,
2687 tbinfo->dobj.dumpId);
2691 /* Plain secondary index */
2692 indxinfo[j].indexconstraint = 0;
2699 destroyPQExpBuffer(query);
2705 * Get info about constraints on dumpable tables.
2707 * Currently handles foreign keys only.
2708 * Unique and primary key constraints are handled with indexes,
2709 * while check constraints are processed in getTableAttrs().
2712 getConstraints(TableInfo tblinfo[], int numTables)
2716 ConstraintInfo *constrinfo;
2725 /* pg_constraint was created in 7.3, so nothing to do if older */
2726 if (g_fout->remoteVersion < 70300)
2729 query = createPQExpBuffer();
2731 for (i = 0; i < numTables; i++)
2733 TableInfo *tbinfo = &tblinfo[i];
2735 if (tbinfo->ntrig == 0 || !tbinfo->dump)
2739 write_msg(NULL, "reading foreign key constraints for table \"%s\"\n",
2743 * select table schema to ensure constraint expr is qualified if
2746 selectSourceSchema(tbinfo->relnamespace->nspname);
2748 resetPQExpBuffer(query);
2749 appendPQExpBuffer(query,
2750 "SELECT tableoid, oid, conname, "
2751 "pg_catalog.pg_get_constraintdef(oid) as condef "
2752 "FROM pg_catalog.pg_constraint "
2753 "WHERE conrelid = '%u'::pg_catalog.oid "
2754 "AND contype = 'f'",
2755 tbinfo->dobj.catId.oid);
2756 res = PQexec(g_conn, query->data);
2757 check_sql_result(res, g_conn, query->data, PGRES_TUPLES_OK);
2759 ntups = PQntuples(res);
2761 i_contableoid = PQfnumber(res, "tableoid");
2762 i_conoid = PQfnumber(res, "oid");
2763 i_conname = PQfnumber(res, "conname");
2764 i_condef = PQfnumber(res, "condef");
2766 constrinfo = (ConstraintInfo *) malloc(ntups * sizeof(ConstraintInfo));
2768 for (j = 0; j < ntups; j++)
2770 constrinfo[j].dobj.objType = DO_FK_CONSTRAINT;
2771 constrinfo[j].dobj.catId.tableoid = atooid(PQgetvalue(res, j, i_contableoid));
2772 constrinfo[j].dobj.catId.oid = atooid(PQgetvalue(res, j, i_conoid));
2773 AssignDumpId(&constrinfo[j].dobj);
2774 constrinfo[j].conname = strdup(PQgetvalue(res, j, i_conname));
2775 constrinfo[j].contable = tbinfo;
2776 constrinfo[j].condomain = NULL;
2777 constrinfo[j].contype = 'f';
2778 constrinfo[j].condef = strdup(PQgetvalue(res, j, i_condef));
2779 constrinfo[j].conindex = 0;
2780 constrinfo[j].coninherited = false;
2781 constrinfo[j].separate = true;
2787 destroyPQExpBuffer(query);
2791 * getDomainConstraints
2793 * Get info about constraints on a domain.
2796 getDomainConstraints(TypeInfo *tinfo)
2799 ConstraintInfo *constrinfo;
2808 /* pg_constraint was created in 7.3, so nothing to do if older */
2809 if (g_fout->remoteVersion < 70300)
2813 * select appropriate schema to ensure names in constraint are properly
2816 selectSourceSchema(tinfo->typnamespace->nspname);
2818 query = createPQExpBuffer();
2820 if (g_fout->remoteVersion >= 70400)
2821 appendPQExpBuffer(query, "SELECT tableoid, oid, conname, "
2822 "pg_catalog.pg_get_constraintdef(oid) AS consrc "
2823 "FROM pg_catalog.pg_constraint "
2824 "WHERE contypid = '%u'::pg_catalog.oid "
2826 tinfo->dobj.catId.oid);
2828 appendPQExpBuffer(query, "SELECT tableoid, oid, conname, "
2829 "'CHECK (' || consrc || ')' AS consrc "
2830 "FROM pg_catalog.pg_constraint "
2831 "WHERE contypid = '%u'::pg_catalog.oid "
2833 tinfo->dobj.catId.oid);
2835 res = PQexec(g_conn, query->data);
2836 check_sql_result(res, g_conn, query->data, PGRES_TUPLES_OK);
2838 ntups = PQntuples(res);
2840 i_tableoid = PQfnumber(res, "tableoid");
2841 i_oid = PQfnumber(res, "oid");
2842 i_conname = PQfnumber(res, "conname");
2843 i_consrc = PQfnumber(res, "consrc");
2845 constrinfo = (ConstraintInfo *) malloc(ntups * sizeof(ConstraintInfo));
2847 tinfo->nDomChecks = ntups;
2848 tinfo->domChecks = constrinfo;
2850 for (i = 0; i < ntups; i++)
2852 constrinfo[i].dobj.objType = DO_CONSTRAINT;
2853 constrinfo[i].dobj.catId.tableoid = atooid(PQgetvalue(res, i, i_tableoid));
2854 constrinfo[i].dobj.catId.oid = atooid(PQgetvalue(res, i, i_oid));
2855 AssignDumpId(&constrinfo[i].dobj);
2856 constrinfo[i].conname = strdup(PQgetvalue(res, i, i_conname));
2857 constrinfo[i].contable = NULL;
2858 constrinfo[i].condomain = tinfo;
2859 constrinfo[i].contype = 'c';
2860 constrinfo[i].condef = strdup(PQgetvalue(res, i, i_consrc));
2861 constrinfo[i].conindex = 0;
2862 constrinfo[i].coninherited = false;
2863 constrinfo[i].separate = false;
2865 * Make the domain depend on the constraint, ensuring it won't
2866 * be output till any constraint dependencies are OK.
2868 addObjectDependency(&tinfo->dobj,
2869 constrinfo[i].dobj.dumpId);
2874 destroyPQExpBuffer(query);
2879 * get basic information about every rule in the system
2881 * numRules is set to the number of rules read in
2884 getRules(int *numRules)
2889 PQExpBuffer query = createPQExpBuffer();
2898 /* Make sure we are in proper schema */
2899 selectSourceSchema("pg_catalog");
2901 if (g_fout->remoteVersion >= 70100)
2903 appendPQExpBuffer(query, "SELECT "
2904 "tableoid, oid, rulename, "
2905 "ev_class as ruletable, ev_type, is_instead "
2911 appendPQExpBuffer(query, "SELECT "
2912 "(SELECT oid FROM pg_class WHERE relname = 'pg_rewrite') AS tableoid, "
2914 "ev_class as ruletable, ev_type, is_instead "
2919 res = PQexec(g_conn, query->data);
2920 check_sql_result(res, g_conn, query->data, PGRES_TUPLES_OK);
2922 ntups = PQntuples(res);
2926 ruleinfo = (RuleInfo *) malloc(ntups * sizeof(RuleInfo));
2928 i_tableoid = PQfnumber(res, "tableoid");
2929 i_oid = PQfnumber(res, "oid");
2930 i_rulename = PQfnumber(res, "rulename");
2931 i_ruletable = PQfnumber(res, "ruletable");
2932 i_ev_type = PQfnumber(res, "ev_type");
2933 i_is_instead = PQfnumber(res, "is_instead");
2935 for (i = 0; i < ntups; i++)
2939 ruleinfo[i].dobj.objType = DO_RULE;
2940 ruleinfo[i].dobj.catId.tableoid = atooid(PQgetvalue(res, i, i_tableoid));
2941 ruleinfo[i].dobj.catId.oid = atooid(PQgetvalue(res, i, i_oid));
2942 AssignDumpId(&ruleinfo[i].dobj);
2943 ruleinfo[i].rulename = strdup(PQgetvalue(res, i, i_rulename));
2944 ruletableoid = atooid(PQgetvalue(res, i, i_ruletable));
2945 ruleinfo[i].ruletable = findTableByOid(ruletableoid);
2946 ruleinfo[i].ev_type = *(PQgetvalue(res, i, i_ev_type));
2947 ruleinfo[i].is_instead = *(PQgetvalue(res, i, i_is_instead)) == 't';
2948 if (ruleinfo[i].ruletable)
2951 * If the table is a view, force its ON SELECT rule to be sorted
2952 * before the view itself --- this ensures that any dependencies
2953 * for the rule affect the table's positioning. Other rules
2954 * are forced to appear after their table.
2956 if (ruleinfo[i].ruletable->relkind == RELKIND_VIEW &&
2957 ruleinfo[i].ev_type == '1' && ruleinfo[i].is_instead)
2958 addObjectDependency(&ruleinfo[i].ruletable->dobj,
2959 ruleinfo[i].dobj.dumpId);
2961 addObjectDependency(&ruleinfo[i].dobj,
2962 ruleinfo[i].ruletable->dobj.dumpId);
2968 destroyPQExpBuffer(query);
2975 * get information about every trigger on a dumpable table
2977 * Note: trigger data is not returned directly to the caller, but it
2978 * does get entered into the DumpableObject tables.
2981 getTriggers(TableInfo tblinfo[], int numTables)
2985 PQExpBuffer query = createPQExpBuffer();
2987 TriggerInfo *tginfo;
3003 for (i = 0; i < numTables; i++)
3005 TableInfo *tbinfo = &tblinfo[i];
3007 if (tbinfo->ntrig == 0 || !tbinfo->dump)
3011 write_msg(NULL, "reading triggers for table \"%s\"\n",
3015 * select table schema to ensure regproc name is qualified if
3018 selectSourceSchema(tbinfo->relnamespace->nspname);
3020 resetPQExpBuffer(query);
3021 if (g_fout->remoteVersion >= 70300)
3024 * We ignore triggers that are tied to a foreign-key
3027 appendPQExpBuffer(query,
3029 "tgfoid::pg_catalog.regproc as tgfname, "
3030 "tgtype, tgnargs, tgargs, "
3031 "tgisconstraint, tgconstrname, tgdeferrable, "
3032 "tgconstrrelid, tginitdeferred, tableoid, oid, "
3033 "tgconstrrelid::pg_catalog.regclass as tgconstrrelname "
3034 "from pg_catalog.pg_trigger t "
3035 "where tgrelid = '%u'::pg_catalog.oid "
3036 "and (not tgisconstraint "
3038 " (SELECT 1 FROM pg_catalog.pg_depend d "
3039 " JOIN pg_catalog.pg_constraint c ON (d.refclassid = c.tableoid AND d.refobjid = c.oid) "
3040 " WHERE d.classid = t.tableoid AND d.objid = t.oid AND d.deptype = 'i' AND c.contype = 'f'))",
3041 tbinfo->dobj.catId.oid);
3043 else if (g_fout->remoteVersion >= 70100)
3045 appendPQExpBuffer(query,
3046 "SELECT tgname, tgfoid::regproc as tgfname, "
3047 "tgtype, tgnargs, tgargs, "
3048 "tgisconstraint, tgconstrname, tgdeferrable, "
3049 "tgconstrrelid, tginitdeferred, tableoid, oid, "
3050 "(select relname from pg_class where oid = tgconstrrelid) "
3051 " as tgconstrrelname "
3053 "where tgrelid = '%u'::oid",
3054 tbinfo->dobj.catId.oid);
3058 appendPQExpBuffer(query,
3059 "SELECT tgname, tgfoid::regproc as tgfname, "
3060 "tgtype, tgnargs, tgargs, "
3061 "tgisconstraint, tgconstrname, tgdeferrable, "
3062 "tgconstrrelid, tginitdeferred, "
3063 "(SELECT oid FROM pg_class WHERE relname = 'pg_trigger') AS tableoid, "
3066 "(select relname from pg_class where oid = tgconstrrelid) "
3067 " as tgconstrrelname "
3069 "where tgrelid = '%u'::oid",
3070 tbinfo->dobj.catId.oid);
3072 res = PQexec(g_conn, query->data);
3073 check_sql_result(res, g_conn, query->data, PGRES_TUPLES_OK);
3075 ntups = PQntuples(res);
3078 * We may have less triggers than recorded due to having ignored
3079 * foreign-key triggers
3081 if (ntups > tbinfo->ntrig)
3083 write_msg(NULL, "expected %d triggers on table \"%s\" but found %d\n",
3084 tbinfo->ntrig, tbinfo->relname, ntups);
3087 i_tableoid = PQfnumber(res, "tableoid");
3088 i_oid = PQfnumber(res, "oid");
3089 i_tgname = PQfnumber(res, "tgname");
3090 i_tgfname = PQfnumber(res, "tgfname");
3091 i_tgtype = PQfnumber(res, "tgtype");
3092 i_tgnargs = PQfnumber(res, "tgnargs");
3093 i_tgargs = PQfnumber(res, "tgargs");
3094 i_tgisconstraint = PQfnumber(res, "tgisconstraint");
3095 i_tgconstrname = PQfnumber(res, "tgconstrname");
3096 i_tgconstrrelid = PQfnumber(res, "tgconstrrelid");
3097 i_tgconstrrelname = PQfnumber(res, "tgconstrrelname");
3098 i_tgdeferrable = PQfnumber(res, "tgdeferrable");
3099 i_tginitdeferred = PQfnumber(res, "tginitdeferred");
3101 tginfo = (TriggerInfo *) malloc(ntups * sizeof(TriggerInfo));
3103 for (j = 0; j < ntups; j++)
3105 tginfo[j].dobj.objType = DO_TRIGGER;
3106 tginfo[j].dobj.catId.tableoid = atooid(PQgetvalue(res, j, i_tableoid));
3107 tginfo[j].dobj.catId.oid = atooid(PQgetvalue(res, j, i_oid));
3108 AssignDumpId(&tginfo[j].dobj);
3109 tginfo[j].tgtable = tbinfo;
3110 tginfo[j].tgname = strdup(PQgetvalue(res, j, i_tgname));
3111 tginfo[j].tgfname = strdup(PQgetvalue(res, j, i_tgfname));
3112 tginfo[j].tgtype = atoi(PQgetvalue(res, j, i_tgtype));
3113 tginfo[j].tgnargs = atoi(PQgetvalue(res, j, i_tgnargs));
3114 tginfo[j].tgargs = strdup(PQgetvalue(res, j, i_tgargs));
3115 tginfo[j].tgisconstraint = *(PQgetvalue(res, j, i_tgisconstraint)) == 't';
3116 tginfo[j].tgdeferrable = *(PQgetvalue(res, j, i_tgdeferrable)) == 't';
3117 tginfo[j].tginitdeferred = *(PQgetvalue(res, j, i_tginitdeferred)) == 't';
3119 if (tginfo[j].tgisconstraint)
3121 tginfo[j].tgconstrname = strdup(PQgetvalue(res, j, i_tgconstrname));
3122 tginfo[j].tgconstrrelid = atooid(PQgetvalue(res, j, i_tgconstrrelid));
3123 if (OidIsValid(tginfo[j].tgconstrrelid))
3125 if (PQgetisnull(res, j, i_tgconstrrelname))
3127 write_msg(NULL, "query produced null referenced table name for foreign key trigger \"%s\" on table \"%s\" (OID of table: %u)\n",
3128 tginfo[j].tgname, tbinfo->relname,
3129 tginfo[j].tgconstrrelid);
3132 tginfo[j].tgconstrrelname = strdup(PQgetvalue(res, j, i_tgconstrrelname));
3135 tginfo[j].tgconstrrelname = NULL;
3139 tginfo[j].tgconstrname = NULL;
3140 tginfo[j].tgconstrrelid = InvalidOid;
3141 tginfo[j].tgconstrrelname = NULL;
3148 destroyPQExpBuffer(query);
3153 * get basic information about every procedural language in the system
3155 * numProcLangs is set to the number of langs read in
3157 * NB: this must run after getFuncs() because we assume we can do
3161 getProcLangs(int *numProcLangs)
3166 PQExpBuffer query = createPQExpBuffer();
3167 ProcLangInfo *planginfo;
3172 int i_lanplcallfoid;
3173 int i_lanvalidator = -1;
3176 /* Make sure we are in proper schema */
3177 selectSourceSchema("pg_catalog");
3179 if (g_fout->remoteVersion >= 70100)
3181 appendPQExpBuffer(query, "SELECT tableoid, oid, * FROM pg_language "
3187 appendPQExpBuffer(query, "SELECT "
3188 "(SELECT oid FROM pg_class WHERE relname = 'pg_language') AS tableoid, "
3189 "oid, * FROM pg_language "
3194 res = PQexec(g_conn, query->data);
3195 check_sql_result(res, g_conn, query->data, PGRES_TUPLES_OK);
3197 ntups = PQntuples(res);
3199 *numProcLangs = ntups;
3201 planginfo = (ProcLangInfo *) malloc(ntups * sizeof(ProcLangInfo));
3203 i_tableoid = PQfnumber(res, "tableoid");
3204 i_oid = PQfnumber(res, "oid");
3205 i_lanname = PQfnumber(res, "lanname");
3206 i_lanpltrusted = PQfnumber(res, "lanpltrusted");
3207 i_lanplcallfoid = PQfnumber(res, "lanplcallfoid");
3208 if (g_fout->remoteVersion >= 70300)
3210 i_lanvalidator = PQfnumber(res, "lanvalidator");
3211 i_lanacl = PQfnumber(res, "lanacl");
3214 for (i = 0; i < ntups; i++)
3216 planginfo[i].dobj.objType = DO_PROCLANG;
3217 planginfo[i].dobj.catId.tableoid = atooid(PQgetvalue(res, i, i_tableoid));
3218 planginfo[i].dobj.catId.oid = atooid(PQgetvalue(res, i, i_oid));
3219 AssignDumpId(&planginfo[i].dobj);
3221 planginfo[i].lanname = strdup(PQgetvalue(res, i, i_lanname));
3222 planginfo[i].lanpltrusted = *(PQgetvalue(res, i, i_lanpltrusted)) == 't';
3223 planginfo[i].lanplcallfoid = atooid(PQgetvalue(res, i, i_lanplcallfoid));
3224 if (g_fout->remoteVersion >= 70300)
3226 planginfo[i].lanvalidator = atooid(PQgetvalue(res, i, i_lanvalidator));
3227 planginfo[i].lanacl = strdup(PQgetvalue(res, i, i_lanacl));
3233 planginfo[i].lanvalidator = InvalidOid;
3234 planginfo[i].lanacl = strdup("{=U}");
3236 * We need to make a dependency to ensure the function will
3237 * be dumped first. (In 7.3 and later the regular dependency
3238 * mechanism will handle this for us.)
3240 funcInfo = findFuncByOid(planginfo[i].lanplcallfoid);
3242 addObjectDependency(&planginfo[i].dobj,
3243 funcInfo->dobj.dumpId);
3249 destroyPQExpBuffer(query);
3256 * get basic information about every cast in the system
3258 * numCasts is set to the number of casts read in
3261 getCasts(int *numCasts)
3266 PQExpBuffer query = createPQExpBuffer();
3275 /* Make sure we are in proper schema */
3276 selectSourceSchema("pg_catalog");
3278 if (g_fout->remoteVersion >= 70300)
3280 appendPQExpBuffer(query, "SELECT tableoid, oid, "
3281 "castsource, casttarget, castfunc, castcontext "
3282 "FROM pg_cast ORDER BY 3,4");
3286 appendPQExpBuffer(query, "SELECT 0 as tableoid, p.oid, "
3287 "t1.oid as castsource, t2.oid as casttarget, "
3288 "p.oid as castfunc, 'e' as castcontext "
3289 "FROM pg_type t1, pg_type t2, pg_proc p "
3290 "WHERE p.pronargs = 1 AND "
3291 "p.proargtypes[0] = t1.oid AND "
3292 "p.prorettype = t2.oid AND p.proname = t2.typname "
3296 res = PQexec(g_conn, query->data);
3297 check_sql_result(res, g_conn, query->data, PGRES_TUPLES_OK);
3299 ntups = PQntuples(res);
3303 castinfo = (CastInfo *) malloc(ntups * sizeof(CastInfo));
3305 i_tableoid = PQfnumber(res, "tableoid");
3306 i_oid = PQfnumber(res, "oid");
3307 i_castsource = PQfnumber(res, "castsource");
3308 i_casttarget = PQfnumber(res, "casttarget");
3309 i_castfunc = PQfnumber(res, "castfunc");
3310 i_castcontext = PQfnumber(res, "castcontext");
3312 for (i = 0; i < ntups; i++)
3314 castinfo[i].dobj.objType = DO_CAST;
3315 castinfo[i].dobj.catId.tableoid = atooid(PQgetvalue(res, i, i_tableoid));
3316 castinfo[i].dobj.catId.oid = atooid(PQgetvalue(res, i, i_oid));
3317 AssignDumpId(&castinfo[i].dobj);
3318 castinfo[i].castsource = atooid(PQgetvalue(res, i, i_castsource));
3319 castinfo[i].casttarget = atooid(PQgetvalue(res, i, i_casttarget));
3320 castinfo[i].castfunc = atooid(PQgetvalue(res, i, i_castfunc));
3321 castinfo[i].castcontext = *(PQgetvalue(res, i, i_castcontext));
3323 if (OidIsValid(castinfo[i].castfunc))
3326 * We need to make a dependency to ensure the function will
3327 * be dumped first. (In 7.3 and later the regular dependency
3328 * mechanism will handle this for us.)
3332 funcInfo = findFuncByOid(castinfo[i].castfunc);
3334 addObjectDependency(&castinfo[i].dobj,
3335 funcInfo->dobj.dumpId);
3341 destroyPQExpBuffer(query);
3348 * for each interesting table, read info about its attributes
3349 * (names, types, default values, CHECK constraints, etc)
3351 * This is implemented in a very inefficient way right now, looping
3352 * through the tblinfo and doing a join per table to find the attrs and their
3353 * types. However, because we want type names and so forth to be named
3354 * relative to the schema of each table, we couldn't do it in just one
3355 * query. (Maybe one query per schema?)
3360 getTableAttrs(TableInfo *tblinfo, int numTables)
3365 PQExpBuffer q = createPQExpBuffer();
3370 int i_attstattarget;
3381 for (i = 0; i < numTables; i++)
3383 TableInfo *tbinfo = &tblinfo[i];
3385 /* Don't bother to collect info for sequences */
3386 if (tbinfo->relkind == RELKIND_SEQUENCE)
3389 /* Don't bother with uninteresting tables, either */
3390 if (!tbinfo->interesting)
3394 * Make sure we are in proper schema for this table; this allows
3395 * correct retrieval of formatted type names and default exprs
3397 selectSourceSchema(tbinfo->relnamespace->nspname);
3399 /* find all the user attributes and their types */
3402 * we must read the attribute names in attribute number order!
3403 * because we will use the attnum to index into the attnames array
3404 * later. We actually ask to order by "attrelid, attnum" because
3405 * (at least up to 7.3) the planner is not smart enough to realize
3406 * it needn't re-sort the output of an indexscan on
3407 * pg_attribute_relid_attnum_index.
3410 write_msg(NULL, "finding the columns and types of table \"%s\"\n",
3413 resetPQExpBuffer(q);
3415 if (g_fout->remoteVersion >= 70300)
3417 /* need left join here to not fail on dropped columns ... */
3418 appendPQExpBuffer(q, "SELECT a.attnum, a.attname, a.atttypmod, a.attstattarget, a.attstorage, t.typstorage, "
3419 "a.attnotnull, a.atthasdef, a.attisdropped, a.attislocal, "
3420 "pg_catalog.format_type(t.oid,a.atttypmod) as atttypname "
3421 "from pg_catalog.pg_attribute a left join pg_catalog.pg_type t "
3422 "on a.atttypid = t.oid "
3423 "where a.attrelid = '%u'::pg_catalog.oid "
3424 "and a.attnum > 0::pg_catalog.int2 "
3425 "order by a.attrelid, a.attnum",
3426 tbinfo->dobj.catId.oid);
3428 else if (g_fout->remoteVersion >= 70100)
3431 * attstattarget doesn't exist in 7.1. It does exist in 7.2,
3432 * but we don't dump it because we can't tell whether it's
3433 * been explicitly set or was just a default.
3435 appendPQExpBuffer(q, "SELECT a.attnum, a.attname, a.atttypmod, -1 as attstattarget, a.attstorage, t.typstorage, "
3436 "a.attnotnull, a.atthasdef, false as attisdropped, false as attislocal, "
3437 "format_type(t.oid,a.atttypmod) as atttypname "
3438 "from pg_attribute a left join pg_type t "
3439 "on a.atttypid = t.oid "
3440 "where a.attrelid = '%u'::oid "
3441 "and a.attnum > 0::int2 "
3442 "order by a.attrelid, a.attnum",
3443 tbinfo->dobj.catId.oid);
3447 /* format_type not available before 7.1 */
3448 appendPQExpBuffer(q, "SELECT attnum, attname, atttypmod, -1 as attstattarget, attstorage, attstorage as typstorage, "
3449 "attnotnull, atthasdef, false as attisdropped, false as attislocal, "
3450 "(select typname from pg_type where oid = atttypid) as atttypname "
3451 "from pg_attribute a "
3452 "where attrelid = '%u'::oid "
3453 "and attnum > 0::int2 "
3454 "order by attrelid, attnum",
3455 tbinfo->dobj.catId.oid);
3458 res = PQexec(g_conn, q->data);
3459 check_sql_result(res, g_conn, q->data, PGRES_TUPLES_OK);
3461 ntups = PQntuples(res);
3463 i_attnum = PQfnumber(res, "attnum");
3464 i_attname = PQfnumber(res, "attname");
3465 i_atttypname = PQfnumber(res, "atttypname");
3466 i_atttypmod = PQfnumber(res, "atttypmod");
3467 i_attstattarget = PQfnumber(res, "attstattarget");
3468 i_attstorage = PQfnumber(res, "attstorage");
3469 i_typstorage = PQfnumber(res, "typstorage");
3470 i_attnotnull = PQfnumber(res, "attnotnull");
3471 i_atthasdef = PQfnumber(res, "atthasdef");
3472 i_attisdropped = PQfnumber(res, "attisdropped");
3473 i_attislocal = PQfnumber(res, "attislocal");
3475 tbinfo->numatts = ntups;
3476 tbinfo->attnames = (char **) malloc(ntups * sizeof(char *));
3477 tbinfo->atttypnames = (char **) malloc(ntups * sizeof(char *));
3478 tbinfo->atttypmod = (int *) malloc(ntups * sizeof(int));
3479 tbinfo->attstattarget = (int *) malloc(ntups * sizeof(int));
3480 tbinfo->attstorage = (char *) malloc(ntups * sizeof(char));
3481 tbinfo->typstorage = (char *) malloc(ntups * sizeof(char));
3482 tbinfo->attisdropped = (bool *) malloc(ntups * sizeof(bool));
3483 tbinfo->attislocal = (bool *) malloc(ntups * sizeof(bool));
3484 tbinfo->attisserial = (bool *) malloc(ntups * sizeof(bool));
3485 tbinfo->notnull = (bool *) malloc(ntups * sizeof(bool));
3486 tbinfo->attrdefs = (AttrDefInfo **) malloc(ntups * sizeof(AttrDefInfo *));
3487 tbinfo->inhAttrs = (bool *) malloc(ntups * sizeof(bool));
3488 tbinfo->inhAttrDef = (bool *) malloc(ntups * sizeof(bool));
3489 tbinfo->inhNotNull = (bool *) malloc(ntups * sizeof(bool));
3490 hasdefaults = false;
3492 for (j = 0; j < ntups; j++)
3494 if (j + 1 != atoi(PQgetvalue(res, j, i_attnum)))
3496 write_msg(NULL, "invalid column numbering in table \"%s\"\n",
3500 tbinfo->attnames[j] = strdup(PQgetvalue(res, j, i_attname));
3501 tbinfo->atttypnames[j] = strdup(PQgetvalue(res, j, i_atttypname));
3502 tbinfo->atttypmod[j] = atoi(PQgetvalue(res, j, i_atttypmod));
3503 tbinfo->attstattarget[j] = atoi(PQgetvalue(res, j, i_attstattarget));
3504 tbinfo->attstorage[j] = *(PQgetvalue(res, j, i_attstorage));
3505 tbinfo->typstorage[j] = *(PQgetvalue(res, j, i_typstorage));
3506 tbinfo->attisdropped[j] = (PQgetvalue(res, j, i_attisdropped)[0] == 't');
3507 tbinfo->attislocal[j] = (PQgetvalue(res, j, i_attislocal)[0] == 't');
3508 tbinfo->attisserial[j] = false; /* fix below */
3509 tbinfo->notnull[j] = (PQgetvalue(res, j, i_attnotnull)[0] == 't');
3510 tbinfo->attrdefs[j] = NULL; /* fix below */
3511 if (PQgetvalue(res, j, i_atthasdef)[0] == 't')
3513 /* these flags will be set in flagInhAttrs() */
3514 tbinfo->inhAttrs[j] = false;
3515 tbinfo->inhAttrDef[j] = false;
3516 tbinfo->inhNotNull[j] = false;
3522 * Get info about column defaults
3526 AttrDefInfo *attrdefs;
3530 write_msg(NULL, "finding default expressions of table \"%s\"\n",
3533 resetPQExpBuffer(q);
3534 if (g_fout->remoteVersion >= 70300)
3536 appendPQExpBuffer(q, "SELECT tableoid, oid, adnum, "
3537 "pg_catalog.pg_get_expr(adbin, adrelid) AS adsrc "
3538 "FROM pg_catalog.pg_attrdef "
3539 "WHERE adrelid = '%u'::pg_catalog.oid",
3540 tbinfo->dobj.catId.oid);
3542 else if (g_fout->remoteVersion >= 70200)
3544 /* 7.2 did not have OIDs in pg_attrdef */
3545 appendPQExpBuffer(q, "SELECT tableoid, 0 as oid, adnum, "
3546 "pg_get_expr(adbin, adrelid) AS adsrc "
3548 "WHERE adrelid = '%u'::oid",
3549 tbinfo->dobj.catId.oid);
3551 else if (g_fout->remoteVersion >= 70100)
3553 /* no pg_get_expr, so must rely on adsrc */
3554 appendPQExpBuffer(q, "SELECT tableoid, oid, adnum, adsrc "
3556 "WHERE adrelid = '%u'::oid",
3557 tbinfo->dobj.catId.oid);
3561 /* no pg_get_expr, no tableoid either */
3562 appendPQExpBuffer(q, "SELECT "
3563 "(SELECT oid FROM pg_class WHERE relname = 'pg_attrdef') AS tableoid, "
3564 "oid, adnum, adsrc "
3566 "WHERE adrelid = '%u'::oid",
3567 tbinfo->dobj.catId.oid);
3569 res = PQexec(g_conn, q->data);
3570 check_sql_result(res, g_conn, q->data, PGRES_TUPLES_OK);
3572 numDefaults = PQntuples(res);
3573 attrdefs = (AttrDefInfo *) malloc(numDefaults * sizeof(AttrDefInfo));
3575 for (j = 0; j < numDefaults; j++)
3579 attrdefs[j].dobj.objType = DO_ATTRDEF;
3580 attrdefs[j].dobj.catId.tableoid = atooid(PQgetvalue(res, j, 0));
3581 attrdefs[j].dobj.catId.oid = atooid(PQgetvalue(res, j, 1));
3582 AssignDumpId(&attrdefs[j].dobj);
3583 attrdefs[j].adtable = tbinfo;
3584 attrdefs[j].adnum = adnum = atoi(PQgetvalue(res, j, 2));
3585 attrdefs[j].adef_expr = strdup(PQgetvalue(res, j, 3));
3588 * Defaults on a VIEW must always be dumped as separate
3589 * ALTER TABLE commands. Defaults on regular tables are
3590 * dumped as part of the CREATE TABLE if possible. To check
3591 * if it's safe, we mark the default as needing to appear
3592 * before the CREATE.
3594 if (tbinfo->relkind == RELKIND_VIEW)
3596 attrdefs[j].separate = true;
3597 /* needed in case pre-7.3 DB: */
3598 addObjectDependency(&attrdefs[j].dobj,
3599 tbinfo->dobj.dumpId);
3603 attrdefs[j].separate = false;
3604 addObjectDependency(&tbinfo->dobj,
3605 attrdefs[j].dobj.dumpId);
3608 if (adnum <= 0 || adnum > ntups)
3610 write_msg(NULL, "invalid adnum value %d for table \"%s\"\n",
3611 adnum, tbinfo->relname);
3614 tbinfo->attrdefs[adnum - 1] = &attrdefs[j];
3620 * Get info about table CHECK constraints
3622 if (tbinfo->ncheck > 0)
3624 ConstraintInfo *constrs;
3628 write_msg(NULL, "finding check constraints for table \"%s\"\n",
3631 resetPQExpBuffer(q);
3632 if (g_fout->remoteVersion >= 70400)
3634 appendPQExpBuffer(q, "SELECT tableoid, oid, conname, "
3635 "pg_catalog.pg_get_constraintdef(oid) AS consrc "
3636 "FROM pg_catalog.pg_constraint "
3637 "WHERE conrelid = '%u'::pg_catalog.oid "
3638 " AND contype = 'c' "
3640 tbinfo->dobj.catId.oid);
3642 else if (g_fout->remoteVersion >= 70300)
3644 /* no pg_get_constraintdef, must use consrc */
3645 appendPQExpBuffer(q, "SELECT tableoid, oid, conname, "
3646 "'CHECK (' || consrc || ')' AS consrc "
3647 "FROM pg_catalog.pg_constraint "
3648 "WHERE conrelid = '%u'::pg_catalog.oid "
3649 " AND contype = 'c' "
3651 tbinfo->dobj.catId.oid);
3653 else if (g_fout->remoteVersion >= 70200)
3655 /* 7.2 did not have OIDs in pg_relcheck */
3656 appendPQExpBuffer(q, "SELECT tableoid, 0 as oid, "
3657 "rcname AS conname, "
3658 "'CHECK (' || rcsrc || ')' AS consrc "
3660 "WHERE rcrelid = '%u'::oid "
3662 tbinfo->dobj.catId.oid);
3664 else if (g_fout->remoteVersion >= 70100)
3666 appendPQExpBuffer(q, "SELECT tableoid, oid, "
3667 "rcname AS conname, "
3668 "'CHECK (' || rcsrc || ')' AS consrc "
3670 "WHERE rcrelid = '%u'::oid "
3672 tbinfo->dobj.catId.oid);
3676 /* no tableoid in 7.0 */
3677 appendPQExpBuffer(q, "SELECT "
3678 "(SELECT oid FROM pg_class WHERE relname = 'pg_relcheck') AS tableoid, "
3679 "oid, rcname AS conname, "
3680 "'CHECK (' || rcsrc || ')' AS consrc "
3682 "WHERE rcrelid = '%u'::oid "
3684 tbinfo->dobj.catId.oid);
3686 res = PQexec(g_conn, q->data);
3687 check_sql_result(res, g_conn, q->data, PGRES_TUPLES_OK);
3689 numConstrs = PQntuples(res);
3690 if (numConstrs != tbinfo->ncheck)
3692 write_msg(NULL, "expected %d check constraints on table \"%s\" but found %d\n",
3693 tbinfo->ncheck, tbinfo->relname, numConstrs);
3694 write_msg(NULL, "(The system catalogs might be corrupted.)\n");
3698 constrs = (ConstraintInfo *) malloc(numConstrs * sizeof(ConstraintInfo));
3699 tbinfo->checkexprs = constrs;
3701 for (j = 0; j < numConstrs; j++)
3703 constrs[j].dobj.objType = DO_CONSTRAINT;
3704 constrs[j].dobj.catId.tableoid = atooid(PQgetvalue(res, j, 0));
3705 constrs[j].dobj.catId.oid = atooid(PQgetvalue(res, j, 1));
3706 AssignDumpId(&constrs[j].dobj);
3707 constrs[j].contable = tbinfo;
3708 constrs[j].condomain = NULL;
3709 constrs[j].contype = 'c';
3710 constrs[j].conname = strdup(PQgetvalue(res, j, 2));
3711 constrs[j].condef = strdup(PQgetvalue(res, j, 3));
3712 constrs[j].conindex = 0;
3713 constrs[j].coninherited = false;
3714 constrs[j].separate = false;
3715 addObjectDependency(&tbinfo->dobj,
3716 constrs[j].dobj.dumpId);
3718 * If the constraint is inherited, this will be detected
3719 * later. We also detect later if the constraint must be
3720 * split out from the table definition.
3727 * Check to see if any columns are serial columns. Our first
3728 * quick filter is that it must be integer or bigint with a
3729 * default. If so, we scan to see if we found a sequence linked
3730 * to this column. If we did, mark the column and sequence
3733 for (j = 0; j < ntups; j++)
3736 * Note assumption that format_type will show these types as
3737 * exactly "integer" and "bigint" regardless of schema path.
3738 * This is correct in 7.3 but needs to be watched.
3740 if (strcmp(tbinfo->atttypnames[j], "integer") != 0 &&
3741 strcmp(tbinfo->atttypnames[j], "bigint") != 0)
3743 if (tbinfo->attrdefs[j] == NULL)
3745 for (k = 0; k < numTables; k++)
3747 TableInfo *seqinfo = &tblinfo[k];
3749 if (OidIsValid(seqinfo->owning_tab) &&
3750 seqinfo->owning_tab == tbinfo->dobj.catId.oid &&
3751 seqinfo->owning_col == j + 1)
3754 * Found a match. Copy the table's interesting and
3755 * dumpable flags to the sequence.
3757 tbinfo->attisserial[j] = true;
3758 seqinfo->interesting = tbinfo->interesting;
3759 seqinfo->dump = tbinfo->dump;
3766 destroyPQExpBuffer(q);
3773 * This routine is used to dump any comments associated with the
3774 * object handed to this routine. The routine takes a constant character
3775 * string for the target part of the comment-creation command, plus
3776 * the namespace and owner of the object (for labeling the ArchiveEntry),
3777 * plus catalog ID and subid which are the lookup key for pg_description,
3778 * plus the dump ID for the object (for setting a dependency).
3779 * If a matching pg_description entry is found, it is dumped.
3782 dumpComment(Archive *fout, const char *target,
3783 const char *namespace, const char *owner,
3784 CatalogId catalogId, int subid, DumpId dumpId)
3790 /* Comments are SCHEMA not data */
3795 * Note we do NOT change source schema here; preserve the caller's
3799 /* Build query to find comment */
3801 query = createPQExpBuffer();
3803 if (fout->remoteVersion >= 70300)
3805 appendPQExpBuffer(query,
3806 "SELECT description FROM pg_catalog.pg_description "
3807 "WHERE classoid = '%u'::pg_catalog.oid and "
3808 "objoid = '%u'::pg_catalog.oid and objsubid = %d",
3809 catalogId.tableoid, catalogId.oid, subid);
3811 else if (fout->remoteVersion >= 70200)
3813 appendPQExpBuffer(query,
3814 "SELECT description FROM pg_description "
3815 "WHERE classoid = '%u'::oid and "
3816 "objoid = '%u'::oid and objsubid = %d",
3817 catalogId.tableoid, catalogId.oid, subid);
3821 /* Note: this will fail to find attribute comments in pre-7.2... */
3822 appendPQExpBuffer(query, "SELECT description FROM pg_description WHERE objoid = '%u'::oid", catalogId.oid);
3827 res = PQexec(g_conn, query->data);
3828 check_sql_result(res, g_conn, query->data, PGRES_TUPLES_OK);
3830 /* If a comment exists, build COMMENT ON statement */
3832 if (PQntuples(res) == 1)
3834 i_description = PQfnumber(res, "description");
3835 resetPQExpBuffer(query);
3836 appendPQExpBuffer(query, "COMMENT ON %s IS ", target);
3837 appendStringLiteral(query, PQgetvalue(res, 0, i_description), false);
3838 appendPQExpBuffer(query, ";\n");
3840 ArchiveEntry(fout, nilCatalogId, createDumpId(),
3841 target, namespace, owner,
3842 "COMMENT", query->data, "", NULL,
3848 destroyPQExpBuffer(query);
3852 * dumpTableComment --
3854 * As above, but dump comments for both the specified table (or view)
3855 * and its columns. For speed, we want to do this with only one query.
3858 dumpTableComment(Archive *fout, TableInfo *tbinfo,
3859 const char *reltypename)
3869 /* Comments are SCHEMA not data */
3874 * Note we do NOT change source schema here; preserve the caller's
3878 /* Build query to find comments */
3880 query = createPQExpBuffer();
3881 target = createPQExpBuffer();
3883 if (fout->remoteVersion >= 70300)
3885 appendPQExpBuffer(query, "SELECT description, objsubid FROM pg_catalog.pg_description "
3886 "WHERE classoid = '%u'::pg_catalog.oid and "
3887 "objoid = '%u'::pg_catalog.oid "
3888 "ORDER BY objoid, classoid, objsubid",
3889 tbinfo->dobj.catId.tableoid, tbinfo->dobj.catId.oid);
3891 else if (fout->remoteVersion >= 70200)
3893 appendPQExpBuffer(query, "SELECT description, objsubid FROM pg_description "
3894 "WHERE classoid = '%u'::oid and "
3895 "objoid = '%u'::oid "
3896 "ORDER BY objoid, classoid, objsubid",
3897 tbinfo->dobj.catId.tableoid, tbinfo->dobj.catId.oid);
3901 /* Note: this will fail to find attribute comments in pre-7.2... */
3902 appendPQExpBuffer(query, "SELECT description, 0 as objsubid FROM pg_description WHERE objoid = '%u'::oid",
3903 tbinfo->dobj.catId.oid);
3908 res = PQexec(g_conn, query->data);
3909 check_sql_result(res, g_conn, query->data, PGRES_TUPLES_OK);
3911 i_description = PQfnumber(res, "description");
3912 i_objsubid = PQfnumber(res, "objsubid");
3914 /* If comments exist, build COMMENT ON statements */
3916 ntups = PQntuples(res);
3917 for (i = 0; i < ntups; i++)
3919 const char *descr = PQgetvalue(res, i, i_description);
3920 int objsubid = atoi(PQgetvalue(res, i, i_objsubid));
3924 resetPQExpBuffer(target);
3925 appendPQExpBuffer(target, "%s %s", reltypename,
3926 fmtId(tbinfo->relname));
3928 resetPQExpBuffer(query);
3929 appendPQExpBuffer(query, "COMMENT ON %s IS ", target->data);
3930 appendStringLiteral(query, descr, false);
3931 appendPQExpBuffer(query, ";\n");
3933 ArchiveEntry(fout, nilCatalogId, createDumpId(),
3935 tbinfo->relnamespace->nspname, tbinfo->usename,
3936 "COMMENT", query->data, "", NULL,
3937 &(tbinfo->dobj.dumpId), 1,
3940 else if (objsubid > 0 && objsubid <= tbinfo->numatts)
3942 resetPQExpBuffer(target);
3943 appendPQExpBuffer(target, "COLUMN %s.",
3944 fmtId(tbinfo->relname));
3945 appendPQExpBuffer(target, "%s",
3946 fmtId(tbinfo->attnames[objsubid - 1]));
3948 resetPQExpBuffer(query);
3949 appendPQExpBuffer(query, "COMMENT ON %s IS ", target->data);
3950 appendStringLiteral(query, descr, false);
3951 appendPQExpBuffer(query, ";\n");
3953 ArchiveEntry(fout, nilCatalogId, createDumpId(),
3955 tbinfo->relnamespace->nspname, tbinfo->usename,
3956 "COMMENT", query->data, "", NULL,
3957 &(tbinfo->dobj.dumpId), 1,
3963 destroyPQExpBuffer(query);
3964 destroyPQExpBuffer(target);
3968 * dumpDumpableObject
3970 * This routine and its subsidiaries are responsible for creating
3971 * ArchiveEntries (TOC objects) for each object to be dumped.
3974 dumpDumpableObject(Archive *fout, DumpableObject *dobj)
3976 switch (dobj->objType)
3979 dumpNamespace(fout, (NamespaceInfo *) dobj);
3982 dumpType(fout, (TypeInfo *) dobj);
3985 dumpFunc(fout, (FuncInfo *) dobj);
3988 dumpAgg(fout, (AggInfo *) dobj);
3991 dumpOpr(fout, (OprInfo *) dobj);
3994 dumpOpclass(fout, (OpclassInfo *) dobj);
3997 dumpConversion(fout, (ConvInfo *) dobj);
4000 dumpTable(fout, (TableInfo *) dobj);
4003 dumpAttrDef(fout, (AttrDefInfo *) dobj);
4006 dumpIndex(fout, (IndxInfo *) dobj);
4009 dumpRule(fout, (RuleInfo *) dobj);
4012 dumpTrigger(fout, (TriggerInfo *) dobj);
4015 dumpConstraint(fout, (ConstraintInfo *) dobj);
4017 case DO_FK_CONSTRAINT:
4018 dumpConstraint(fout, (ConstraintInfo *) dobj);
4021 dumpProcLang(fout, (ProcLangInfo *) dobj);
4024 dumpCast(fout, (CastInfo *) dobj);
4027 dumpTableData(fout, (TableDataInfo *) dobj);
4034 * writes out to fout the queries to recreate a user-defined namespace
4037 dumpNamespace(Archive *fout, NamespaceInfo *nspinfo)
4043 /* skip if not to be dumped */
4044 if (!nspinfo->dump || dataOnly)
4047 /* don't dump dummy namespace from pre-7.3 source */
4048 if (strlen(nspinfo->nspname) == 0)
4051 q = createPQExpBuffer();
4052 delq = createPQExpBuffer();
4054 qnspname = strdup(fmtId(nspinfo->nspname));
4057 * If it's the PUBLIC namespace, suppress the CREATE SCHEMA record
4058 * for it, since we expect PUBLIC to exist already in the
4059 * destination database. But do emit ACL in case it's not standard,
4062 * Note that ownership is shown in the AUTHORIZATION clause,
4063 * while the archive entry is listed with empty owner (causing
4064 * it to be emitted with SET SESSION AUTHORIZATION DEFAULT).
4065 * This seems the best way of dealing with schemas owned by
4066 * users without CREATE SCHEMA privilege. Further hacking has
4067 * to be applied for --no-owner mode, though!
4069 if (strcmp(nspinfo->nspname, "public") != 0)
4071 appendPQExpBuffer(delq, "DROP SCHEMA %s;\n", qnspname);
4073 appendPQExpBuffer(q, "CREATE SCHEMA %s AUTHORIZATION %s;\n",
4074 qnspname, fmtId(nspinfo->usename));
4076 ArchiveEntry(fout, nspinfo->dobj.catId, nspinfo->dobj.dumpId,
4079 "SCHEMA", q->data, delq->data, NULL,
4080 nspinfo->dobj.dependencies, nspinfo->dobj.nDeps,
4084 /* Dump Schema Comments */
4085 resetPQExpBuffer(q);
4086 appendPQExpBuffer(q, "SCHEMA %s", qnspname);
4087 dumpComment(fout, q->data,
4088 NULL, nspinfo->usename,
4089 nspinfo->dobj.catId, 0, nspinfo->dobj.dumpId);
4091 dumpACL(fout, nspinfo->dobj.catId, nspinfo->dobj.dumpId, "SCHEMA",
4092 qnspname, nspinfo->nspname, NULL,
4093 nspinfo->usename, nspinfo->nspacl);
4097 destroyPQExpBuffer(q);
4098 destroyPQExpBuffer(delq);
4103 * writes out to fout the queries to recreate a user-defined type
4106 dumpType(Archive *fout, TypeInfo *tinfo)
4108 /* Dump only types in dumpable namespaces */
4109 if (!tinfo->typnamespace->dump || dataOnly)
4112 /* skip complex types, except for standalone composite types */
4113 if (OidIsValid(tinfo->typrelid) && tinfo->typrelkind != 'c')
4116 /* skip undefined placeholder types */
4117 if (!tinfo->isDefined)
4120 /* skip all array types that start w/ underscore */
4121 if ((tinfo->typname[0] == '_') &&
4122 OidIsValid(tinfo->typelem))
4125 /* Dump out in proper style */
4126 if (tinfo->typtype == 'b')
4127 dumpBaseType(fout, tinfo);
4128 else if (tinfo->typtype == 'd')
4129 dumpDomain(fout, tinfo);
4130 else if (tinfo->typtype == 'c')
4131 dumpCompositeType(fout, tinfo);
4136 * writes out to fout the queries to recreate a user-defined base type
4139 dumpBaseType(Archive *fout, TypeInfo *tinfo)
4141 PQExpBuffer q = createPQExpBuffer();
4142 PQExpBuffer delq = createPQExpBuffer();
4143 PQExpBuffer query = createPQExpBuffer();
4163 /* Set proper schema search path so regproc references list correctly */
4164 selectSourceSchema(tinfo->typnamespace->nspname);
4166 /* Fetch type-specific details */
4167 if (fout->remoteVersion >= 70500)
4169 appendPQExpBuffer(query, "SELECT typlen, "
4170 "typinput, typoutput, typreceive, typsend, "
4172 "typinput::pg_catalog.oid as typinputoid, "
4173 "typoutput::pg_catalog.oid as typoutputoid, "
4174 "typreceive::pg_catalog.oid as typreceiveoid, "
4175 "typsend::pg_catalog.oid as typsendoid, "
4176 "typanalyze::pg_catalog.oid as typanalyzeoid, "
4177 "typdelim, typdefault, typbyval, typalign, "
4179 "FROM pg_catalog.pg_type "
4180 "WHERE oid = '%u'::pg_catalog.oid",
4181 tinfo->dobj.catId.oid);
4183 else if (fout->remoteVersion >= 70400)
4185 appendPQExpBuffer(query, "SELECT typlen, "
4186 "typinput, typoutput, typreceive, typsend, "
4187 "'-' as typanalyze, "
4188 "typinput::pg_catalog.oid as typinputoid, "
4189 "typoutput::pg_catalog.oid as typoutputoid, "
4190 "typreceive::pg_catalog.oid as typreceiveoid, "
4191 "typsend::pg_catalog.oid as typsendoid, "
4192 "0 as typanalyzeoid, "
4193 "typdelim, typdefault, typbyval, typalign, "
4195 "FROM pg_catalog.pg_type "
4196 "WHERE oid = '%u'::pg_catalog.oid",
4197 tinfo->dobj.catId.oid);
4199 else if (fout->remoteVersion >= 70300)
4201 appendPQExpBuffer(query, "SELECT typlen, "
4202 "typinput, typoutput, "
4203 "'-' as typreceive, '-' as typsend, "
4204 "'-' as typanalyze, "
4205 "typinput::pg_catalog.oid as typinputoid, "
4206 "typoutput::pg_catalog.oid as typoutputoid, "
4207 "0 as typreceiveoid, 0 as typsendoid, "
4208 "0 as typanalyzeoid, "
4209 "typdelim, typdefault, typbyval, typalign, "
4211 "FROM pg_catalog.pg_type "
4212 "WHERE oid = '%u'::pg_catalog.oid",
4213 tinfo->dobj.catId.oid);
4215 else if (fout->remoteVersion >= 70100)
4218 * Note: although pre-7.3 catalogs contain typreceive and typsend,
4219 * ignore them because they are not right.
4221 appendPQExpBuffer(query, "SELECT typlen, "
4222 "typinput, typoutput, "
4223 "'-' as typreceive, '-' as typsend, "
4224 "'-' as typanalyze, "
4225 "typinput::oid as typinputoid, "
4226 "typoutput::oid as typoutputoid, "
4227 "0 as typreceiveoid, 0 as typsendoid, "
4228 "0 as typanalyzeoid, "
4229 "typdelim, typdefault, typbyval, typalign, "
4232 "WHERE oid = '%u'::oid",
4233 tinfo->dobj.catId.oid);
4237 appendPQExpBuffer(query, "SELECT typlen, "
4238 "typinput, typoutput, "
4239 "'-' as typreceive, '-' as typsend, "
4240 "'-' as typanalyze, "
4241 "typinput::oid as typinputoid, "
4242 "typoutput::oid as typoutputoid, "
4243 "0 as typreceiveoid, 0 as typsendoid, "
4244 "0 as typanalyzeoid, "
4245 "typdelim, typdefault, typbyval, typalign, "
4246 "'p'::char as typstorage "
4248 "WHERE oid = '%u'::oid",
4249 tinfo->dobj.catId.oid);
4252 res = PQexec(g_conn, query->data);
4253 check_sql_result(res, g_conn, query->data, PGRES_TUPLES_OK);
4255 /* Expecting a single result only */
4256 ntups = PQntuples(res);
4259 write_msg(NULL, "Got %d rows instead of one from: %s",
4260 ntups, query->data);
4264 typlen = PQgetvalue(res, 0, PQfnumber(res, "typlen"));
4265 typinput = PQgetvalue(res, 0, PQfnumber(res, "typinput"));
4266 typoutput = PQgetvalue(res, 0, PQfnumber(res, "typoutput"));
4267 typreceive = PQgetvalue(res, 0, PQfnumber(res, "typreceive"));
4268 typsend = PQgetvalue(res, 0, PQfnumber(res, "typsend"));
4269 typanalyze = PQgetvalue(res, 0, PQfnumber(res, "typanalyze"));
4270 typinputoid = atooid(PQgetvalue(res, 0, PQfnumber(res, "typinputoid")));
4271 typoutputoid = atooid(PQgetvalue(res, 0, PQfnumber(res, "typoutputoid")));
4272 typreceiveoid = atooid(PQgetvalue(res, 0, PQfnumber(res, "typreceiveoid")));
4273 typsendoid = atooid(PQgetvalue(res, 0, PQfnumber(res, "typsendoid")));
4274 typanalyzeoid = atooid(PQgetvalue(res, 0, PQfnumber(res, "typanalyzeoid")));
4275 typdelim = PQgetvalue(res, 0, PQfnumber(res, "typdelim"));
4276 if (PQgetisnull(res, 0, PQfnumber(res, "typdefault")))
4279 typdefault = PQgetvalue(res, 0, PQfnumber(res, "typdefault"));
4280 typbyval = PQgetvalue(res, 0, PQfnumber(res, "typbyval"));
4281 typalign = PQgetvalue(res, 0, PQfnumber(res, "typalign"));
4282 typstorage = PQgetvalue(res, 0, PQfnumber(res, "typstorage"));
4285 * DROP must be fully qualified in case same name appears in
4288 appendPQExpBuffer(delq, "DROP TYPE %s.",
4289 fmtId(tinfo->typnamespace->nspname));
4290 appendPQExpBuffer(delq, "%s CASCADE;\n",
4291 fmtId(tinfo->typname));
4293 appendPQExpBuffer(q,
4294 "CREATE TYPE %s (\n"
4295 " INTERNALLENGTH = %s",
4296 fmtId(tinfo->typname),
4297 (strcmp(typlen, "-1") == 0) ? "variable" : typlen);
4299 if (fout->remoteVersion >= 70300)
4301 /* regproc result is correctly quoted as of 7.3 */
4302 appendPQExpBuffer(q, ",\n INPUT = %s", typinput);
4303 appendPQExpBuffer(q, ",\n OUTPUT = %s", typoutput);
4304 if (OidIsValid(typreceiveoid))
4305 appendPQExpBuffer(q, ",\n RECEIVE = %s", typreceive);
4306 if (OidIsValid(typsendoid))
4307 appendPQExpBuffer(q, ",\n SEND = %s", typsend);
4308 if (OidIsValid(typanalyzeoid))
4309 appendPQExpBuffer(q, ",\n ANALYZE = %s", typanalyze);
4313 /* regproc delivers an unquoted name before 7.3 */
4314 /* cannot combine these because fmtId uses static result area */
4315 appendPQExpBuffer(q, ",\n INPUT = %s", fmtId(typinput));
4316 appendPQExpBuffer(q, ",\n OUTPUT = %s", fmtId(typoutput));
4317 /* no chance that receive/send/analyze need be printed */
4320 if (typdefault != NULL)
4322 appendPQExpBuffer(q, ",\n DEFAULT = ");
4323 appendStringLiteral(q, typdefault, true);
4330 /* reselect schema in case changed by function dump */
4331 selectSourceSchema(tinfo->typnamespace->nspname);
4332 elemType = getFormattedTypeName(tinfo->typelem, zeroAsOpaque);
4333 appendPQExpBuffer(q, ",\n ELEMENT = %s", elemType);
4337 if (typdelim && strcmp(typdelim, ",") != 0)
4339 appendPQExpBuffer(q, ",\n DELIMITER = ");
4340 appendStringLiteral(q, typdelim, true);
4343 if (strcmp(typalign, "c") == 0)
4344 appendPQExpBuffer(q, ",\n ALIGNMENT = char");
4345 else if (strcmp(typalign, "s") == 0)
4346 appendPQExpBuffer(q, ",\n ALIGNMENT = int2");
4347 else if (strcmp(typalign, "i") == 0)
4348 appendPQExpBuffer(q, ",\n ALIGNMENT = int4");
4349 else if (strcmp(typalign, "d") == 0)
4350 appendPQExpBuffer(q, ",\n ALIGNMENT = double");
4352 if (strcmp(typstorage, "p") == 0)
4353 appendPQExpBuffer(q, ",\n STORAGE = plain");
4354 else if (strcmp(typstorage, "e") == 0)
4355 appendPQExpBuffer(q, ",\n STORAGE = external");
4356 else if (strcmp(typstorage, "x") == 0)
4357 appendPQExpBuffer(q, ",\n STORAGE = extended");
4358 else if (strcmp(typstorage, "m") == 0)
4359 appendPQExpBuffer(q, ",\n STORAGE = main");
4361 if (strcmp(typbyval, "t") == 0)
4362 appendPQExpBuffer(q, ",\n PASSEDBYVALUE");
4364 appendPQExpBuffer(q, "\n);\n");
4366 ArchiveEntry(fout, tinfo->dobj.catId, tinfo->dobj.dumpId,
4368 tinfo->typnamespace->nspname,
4370 "TYPE", q->data, delq->data, NULL,
4371 tinfo->dobj.dependencies, tinfo->dobj.nDeps,
4374 /* Dump Type Comments */
4375 resetPQExpBuffer(q);
4377 appendPQExpBuffer(q, "TYPE %s", fmtId(tinfo->typname));
4378 dumpComment(fout, q->data,
4379 tinfo->typnamespace->nspname, tinfo->usename,
4380 tinfo->dobj.catId, 0, tinfo->dobj.dumpId);
4383 destroyPQExpBuffer(q);
4384 destroyPQExpBuffer(delq);
4385 destroyPQExpBuffer(query);
4390 * writes out to fout the queries to recreate a user-defined domain
4393 dumpDomain(Archive *fout, TypeInfo *tinfo)
4395 PQExpBuffer q = createPQExpBuffer();
4396 PQExpBuffer delq = createPQExpBuffer();
4397 PQExpBuffer query = createPQExpBuffer();
4405 /* Set proper schema search path so type references list correctly */
4406 selectSourceSchema(tinfo->typnamespace->nspname);
4408 /* Fetch domain specific details */
4409 /* We assume here that remoteVersion must be at least 70300 */
4410 appendPQExpBuffer(query, "SELECT typnotnull, "
4411 "pg_catalog.format_type(typbasetype, typtypmod) as typdefn, "
4413 "FROM pg_catalog.pg_type "
4414 "WHERE oid = '%u'::pg_catalog.oid",
4415 tinfo->dobj.catId.oid);
4417 res = PQexec(g_conn, query->data);
4418 check_sql_result(res, g_conn, query->data, PGRES_TUPLES_OK);
4420 /* Expecting a single result only */
4421 ntups = PQntuples(res);
4424 write_msg(NULL, "Got %d rows instead of one from: %s",
4425 ntups, query->data);
4429 typnotnull = PQgetvalue(res, 0, PQfnumber(res, "typnotnull"));
4430 typdefn = PQgetvalue(res, 0, PQfnumber(res, "typdefn"));
4431 if (PQgetisnull(res, 0, PQfnumber(res, "typdefault")))
4434 typdefault = PQgetvalue(res, 0, PQfnumber(res, "typdefault"));
4436 appendPQExpBuffer(q,
4437 "CREATE DOMAIN %s AS %s",
4438 fmtId(tinfo->typname),
4441 if (typnotnull[0] == 't')
4442 appendPQExpBuffer(q, " NOT NULL");
4445 appendPQExpBuffer(q, " DEFAULT %s", typdefault);
4450 * Add any CHECK constraints for the domain
4452 for (i = 0; i < tinfo->nDomChecks; i++)
4454 ConstraintInfo *domcheck = &(tinfo->domChecks[i]);
4456 if (!domcheck->separate)
4457 appendPQExpBuffer(q, "\n\tCONSTRAINT %s %s",
4458 fmtId(domcheck->conname), domcheck->condef);
4461 appendPQExpBuffer(q, ";\n");
4464 * DROP must be fully qualified in case same name appears in
4467 appendPQExpBuffer(delq, "DROP DOMAIN %s.",
4468 fmtId(tinfo->typnamespace->nspname));
4469 appendPQExpBuffer(delq, "%s;\n",
4470 fmtId(tinfo->typname));
4472 ArchiveEntry(fout, tinfo->dobj.catId, tinfo->dobj.dumpId,
4474 tinfo->typnamespace->nspname,
4476 "DOMAIN", q->data, delq->data, NULL,
4477 tinfo->dobj.dependencies, tinfo->dobj.nDeps,
4480 /* Dump Domain Comments */
4481 resetPQExpBuffer(q);
4483 appendPQExpBuffer(q, "DOMAIN %s", fmtId(tinfo->typname));
4484 dumpComment(fout, q->data,
4485 tinfo->typnamespace->nspname, tinfo->usename,
4486 tinfo->dobj.catId, 0, tinfo->dobj.dumpId);
4488 destroyPQExpBuffer(q);
4489 destroyPQExpBuffer(delq);
4490 destroyPQExpBuffer(query);
4495 * writes out to fout the queries to recreate a user-defined stand-alone
4499 dumpCompositeType(Archive *fout, TypeInfo *tinfo)
4501 PQExpBuffer q = createPQExpBuffer();
4502 PQExpBuffer delq = createPQExpBuffer();
4503 PQExpBuffer query = createPQExpBuffer();
4510 /* Set proper schema search path so type references list correctly */
4511 selectSourceSchema(tinfo->typnamespace->nspname);
4513 /* Fetch type specific details */
4514 /* We assume here that remoteVersion must be at least 70300 */
4516 appendPQExpBuffer(query, "SELECT a.attname, "
4517 "pg_catalog.format_type(a.atttypid, a.atttypmod) as atttypdefn "
4518 "FROM pg_catalog.pg_type t, pg_catalog.pg_attribute a "
4519 "WHERE t.oid = '%u'::pg_catalog.oid "
4520 "AND a.attrelid = t.typrelid "
4521 "AND NOT a.attisdropped "
4522 "ORDER BY a.attnum ",
4523 tinfo->dobj.catId.oid);
4525 res = PQexec(g_conn, query->data);
4526 check_sql_result(res, g_conn, query->data, PGRES_TUPLES_OK);
4528 /* Expecting at least a single result */
4529 ntups = PQntuples(res);
4532 write_msg(NULL, "query yielded no rows: %s\n", query->data);
4536 i_attname = PQfnumber(res, "attname");
4537 i_atttypdefn = PQfnumber(res, "atttypdefn");
4539 appendPQExpBuffer(q, "CREATE TYPE %s AS (",
4540 fmtId(tinfo->typname));
4542 for (i = 0; i < ntups; i++)
4547 attname = PQgetvalue(res, i, i_attname);
4548 atttypdefn = PQgetvalue(res, i, i_atttypdefn);
4550 appendPQExpBuffer(q, "\n\t%s %s", fmtId(attname), atttypdefn);
4552 appendPQExpBuffer(q, ",");
4554 appendPQExpBuffer(q, "\n);\n");
4557 * DROP must be fully qualified in case same name appears in
4560 appendPQExpBuffer(delq, "DROP TYPE %s.",
4561 fmtId(tinfo->typnamespace->nspname));
4562 appendPQExpBuffer(delq, "%s;\n",
4563 fmtId(tinfo->typname));
4565 ArchiveEntry(fout, tinfo->dobj.catId, tinfo->dobj.dumpId,
4567 tinfo->typnamespace->nspname,
4569 "TYPE", q->data, delq->data, NULL,
4570 tinfo->dobj.dependencies, tinfo->dobj.nDeps,
4574 /* Dump Type Comments */
4575 resetPQExpBuffer(q);
4577 appendPQExpBuffer(q, "TYPE %s", fmtId(tinfo->typname));
4578 dumpComment(fout, q->data,
4579 tinfo->typnamespace->nspname, tinfo->usename,
4580 tinfo->dobj.catId, 0, tinfo->dobj.dumpId);
4583 destroyPQExpBuffer(q);
4584 destroyPQExpBuffer(delq);
4585 destroyPQExpBuffer(query);
4590 * writes out to fout the queries to recreate a user-defined
4591 * procedural language
4594 dumpProcLang(Archive *fout, ProcLangInfo *plang)
4600 FuncInfo *validatorInfo = NULL;
4606 * Current theory is to dump PLs iff their underlying functions
4607 * will be dumped (are in a dumpable namespace, or have a
4608 * non-system OID in pre-7.3 databases). Actually, we treat the
4609 * PL itself as being in the underlying function's namespace,
4610 * though it isn't really. This avoids searchpath problems for
4611 * the HANDLER clause.
4613 * If the underlying function is in the pg_catalog namespace,
4614 * we won't have loaded it into finfo[] at all; therefore,
4615 * treat failure to find it in finfo[] as indicating we shouldn't
4616 * dump it, not as an error condition. Ditto for the validator.
4619 funcInfo = findFuncByOid(plang->lanplcallfoid);
4620 if (funcInfo == NULL)
4623 if (!funcInfo->pronamespace->dump)
4626 if (OidIsValid(plang->lanvalidator))
4628 validatorInfo = findFuncByOid(plang->lanvalidator);
4629 if (validatorInfo == NULL)
4633 defqry = createPQExpBuffer();
4634 delqry = createPQExpBuffer();
4636 qlanname = strdup(fmtId(plang->lanname));
4638 appendPQExpBuffer(delqry, "DROP PROCEDURAL LANGUAGE %s;\n",
4641 appendPQExpBuffer(defqry, "CREATE %sPROCEDURAL LANGUAGE %s",
4642 plang->lanpltrusted ? "TRUSTED " : "",
4644 appendPQExpBuffer(defqry, " HANDLER %s",
4645 fmtId(funcInfo->proname));
4646 if (OidIsValid(plang->lanvalidator))
4648 appendPQExpBuffer(defqry, " VALIDATOR ");
4649 /* Cope with possibility that validator is in different schema */
4650 if (validatorInfo->pronamespace != funcInfo->pronamespace)
4651 appendPQExpBuffer(defqry, "%s.",
4652 fmtId(validatorInfo->pronamespace->nspname));
4653 appendPQExpBuffer(defqry, "%s",
4654 fmtId(validatorInfo->proname));
4656 appendPQExpBuffer(defqry, ";\n");
4658 ArchiveEntry(fout, plang->dobj.catId, plang->dobj.dumpId,
4660 funcInfo->pronamespace->nspname, "",
4661 "PROCEDURAL LANGUAGE",
4662 defqry->data, delqry->data, NULL,
4663 plang->dobj.dependencies, plang->dobj.nDeps,
4666 /* Dump Proc Lang Comments */
4667 resetPQExpBuffer(defqry);
4669 appendPQExpBuffer(defqry, "LANGUAGE %s", qlanname);
4670 dumpComment(fout, defqry->data,
4672 plang->dobj.catId, 0, plang->dobj.dumpId);
4674 if (plang->lanpltrusted)
4675 dumpACL(fout, plang->dobj.catId, plang->dobj.dumpId, "LANGUAGE",
4676 qlanname, plang->lanname,
4677 funcInfo->pronamespace->nspname,
4678 NULL, plang->lanacl);
4682 destroyPQExpBuffer(defqry);
4683 destroyPQExpBuffer(delqry);
4687 * format_function_signature: generate function name and argument list
4689 * The argument type names are qualified if needed. The function name
4690 * is never qualified.
4692 * argnames may be NULL if no names are available.
4695 format_function_signature(FuncInfo *finfo, char **argnames,
4701 initPQExpBuffer(&fn);
4703 appendPQExpBuffer(&fn, "%s(", fmtId(finfo->proname));
4705 appendPQExpBuffer(&fn, "%s(", finfo->proname);
4706 for (j = 0; j < finfo->nargs; j++)
4711 typname = getFormattedTypeName(finfo->argtypes[j], zeroAsOpaque);
4713 argname = argnames ? argnames[j] : (char *) NULL;
4714 if (argname && argname[0] == '\0')
4717 appendPQExpBuffer(&fn, "%s%s%s%s",
4718 (j > 0) ? ", " : "",
4719 argname ? fmtId(argname) : "",
4724 appendPQExpBuffer(&fn, ")");
4731 * dump out one function
4734 dumpFunc(Archive *fout, FuncInfo *finfo)
4753 char **argnamearray = NULL;
4755 /* Dump only funcs in dumpable namespaces */
4756 if (!finfo->pronamespace->dump || dataOnly)
4759 query = createPQExpBuffer();
4760 q = createPQExpBuffer();
4761 delqry = createPQExpBuffer();
4762 asPart = createPQExpBuffer();
4764 /* Set proper schema search path so type references list correctly */
4765 selectSourceSchema(finfo->pronamespace->nspname);
4767 /* Fetch function-specific details */
4768 if (g_fout->remoteVersion >= 70500)
4770 appendPQExpBuffer(query,
4771 "SELECT proretset, prosrc, probin, "
4773 "provolatile, proisstrict, prosecdef, "
4774 "(SELECT lanname FROM pg_catalog.pg_language WHERE oid = prolang) as lanname "
4775 "FROM pg_catalog.pg_proc "
4776 "WHERE oid = '%u'::pg_catalog.oid",
4777 finfo->dobj.catId.oid);
4779 else if (g_fout->remoteVersion >= 70300)
4781 appendPQExpBuffer(query,
4782 "SELECT proretset, prosrc, probin, "
4783 "null::text as proargnames, "
4784 "provolatile, proisstrict, prosecdef, "
4785 "(SELECT lanname FROM pg_catalog.pg_language WHERE oid = prolang) as lanname "
4786 "FROM pg_catalog.pg_proc "
4787 "WHERE oid = '%u'::pg_catalog.oid",
4788 finfo->dobj.catId.oid);
4790 else if (g_fout->remoteVersion >= 70100)
4792 appendPQExpBuffer(query,
4793 "SELECT proretset, prosrc, probin, "
4794 "null::text as proargnames, "
4795 "case when proiscachable then 'i' else 'v' end as provolatile, "
4797 "'f'::boolean as prosecdef, "
4798 "(SELECT lanname FROM pg_language WHERE oid = prolang) as lanname "
4800 "WHERE oid = '%u'::oid",
4801 finfo->dobj.catId.oid);
4805 appendPQExpBuffer(query,
4806 "SELECT proretset, prosrc, probin, "
4807 "null::text as proargnames, "
4808 "case when proiscachable then 'i' else 'v' end as provolatile, "
4809 "'f'::boolean as proisstrict, "
4810 "'f'::boolean as prosecdef, "
4811 "(SELECT lanname FROM pg_language WHERE oid = prolang) as lanname "
4813 "WHERE oid = '%u'::oid",
4814 finfo->dobj.catId.oid);
4817 res = PQexec(g_conn, query->data);
4818 check_sql_result(res, g_conn, query->data, PGRES_TUPLES_OK);
4820 /* Expecting a single result only */
4821 ntups = PQntuples(res);
4824 write_msg(NULL, "Got %d rows instead of one from: %s",
4825 ntups, query->data);
4829 proretset = PQgetvalue(res, 0, PQfnumber(res, "proretset"));
4830 prosrc = PQgetvalue(res, 0, PQfnumber(res, "prosrc"));
4831 probin = PQgetvalue(res, 0, PQfnumber(res, "probin"));
4832 proargnames = PQgetvalue(res, 0, PQfnumber(res, "proargnames"));
4833 provolatile = PQgetvalue(res, 0, PQfnumber(res, "provolatile"));
4834 proisstrict = PQgetvalue(res, 0, PQfnumber(res, "proisstrict"));
4835 prosecdef = PQgetvalue(res, 0, PQfnumber(res, "prosecdef"));
4836 lanname = PQgetvalue(res, 0, PQfnumber(res, "lanname"));
4839 * See backend/commands/define.c for details of how the 'AS' clause is
4842 if (strcmp(probin, "-") != 0)
4844 appendPQExpBuffer(asPart, "AS ");
4845 appendStringLiteral(asPart, probin, true);
4846 if (strcmp(prosrc, "-") != 0)
4848 appendPQExpBuffer(asPart, ", ");
4849 appendStringLiteral(asPart, prosrc, false);
4854 if (strcmp(prosrc, "-") != 0)
4856 appendPQExpBuffer(asPart, "AS ");
4857 appendStringLiteral(asPart, prosrc, false);
4861 if (proargnames && *proargnames)
4865 if (!parsePGArray(proargnames, &argnamearray, &nitems) ||
4866 nitems != finfo->nargs)
4868 write_msg(NULL, "WARNING: could not parse proargnames array\n");
4871 argnamearray = NULL;
4875 funcsig = format_function_signature(finfo, argnamearray, true);
4876 funcsig_tag = format_function_signature(finfo, NULL, false);
4879 * DROP must be fully qualified in case same name appears in
4882 appendPQExpBuffer(delqry, "DROP FUNCTION %s.%s;\n",
4883 fmtId(finfo->pronamespace->nspname),
4886 rettypename = getFormattedTypeName(finfo->prorettype, zeroAsOpaque);
4888 appendPQExpBuffer(q, "CREATE FUNCTION %s ", funcsig);
4889 appendPQExpBuffer(q, "RETURNS %s%s\n %s\n LANGUAGE %s",
4890 (proretset[0] == 't') ? "SETOF " : "",
4897 if (provolatile[0] != PROVOLATILE_VOLATILE)
4899 if (provolatile[0] == PROVOLATILE_IMMUTABLE)
4900 appendPQExpBuffer(q, " IMMUTABLE");
4901 else if (provolatile[0] == PROVOLATILE_STABLE)
4902 appendPQExpBuffer(q, " STABLE");
4903 else if (provolatile[0] != PROVOLATILE_VOLATILE)
4905 write_msg(NULL, "unrecognized provolatile value for function \"%s\"\n",
4911 if (proisstrict[0] == 't')
4912 appendPQExpBuffer(q, " STRICT");
4914 if (prosecdef[0] == 't')
4915 appendPQExpBuffer(q, " SECURITY DEFINER");
4917 appendPQExpBuffer(q, ";\n");
4919 ArchiveEntry(fout, finfo->dobj.catId, finfo->dobj.dumpId,
4921 finfo->pronamespace->nspname,
4923 "FUNCTION", q->data, delqry->data, NULL,
4924 finfo->dobj.dependencies, finfo->dobj.nDeps,
4927 /* Dump Function Comments */
4928 resetPQExpBuffer(q);
4929 appendPQExpBuffer(q, "FUNCTION %s", funcsig);
4930 dumpComment(fout, q->data,
4931 finfo->pronamespace->nspname, finfo->usename,
4932 finfo->dobj.catId, 0, finfo->dobj.dumpId);
4934 dumpACL(fout, finfo->dobj.catId, finfo->dobj.dumpId, "FUNCTION",
4935 funcsig, funcsig_tag,
4936 finfo->pronamespace->nspname,
4937 finfo->usename, finfo->proacl);
4941 destroyPQExpBuffer(query);
4942 destroyPQExpBuffer(q);
4943 destroyPQExpBuffer(delqry);
4944 destroyPQExpBuffer(asPart);
4953 * Dump a user-defined cast
4956 dumpCast(Archive *fout, CastInfo *cast)
4960 PQExpBuffer castsig;
4961 FuncInfo *funcInfo = NULL;
4962 TypeInfo *sourceInfo;
4963 TypeInfo *targetInfo;
4968 if (OidIsValid(cast->castfunc))
4970 funcInfo = findFuncByOid(cast->castfunc);
4971 if (funcInfo == NULL)
4976 * As per discussion we dump casts if one or more of the underlying
4977 * objects (the conversion function and the two data types) are not
4978 * builtin AND if all of the non-builtin objects namespaces are
4979 * included in the dump. Builtin meaning, the namespace name does
4980 * not start with "pg_".
4982 sourceInfo = findTypeByOid(cast->castsource);
4983 targetInfo = findTypeByOid(cast->casttarget);
4985 if (sourceInfo == NULL || targetInfo == NULL)
4989 * Skip this cast if all objects are from pg_
4991 if ((funcInfo == NULL || strncmp(funcInfo->pronamespace->nspname, "pg_", 3) == 0) &&
4992 strncmp(sourceInfo->typnamespace->nspname, "pg_", 3) == 0 &&
4993 strncmp(targetInfo->typnamespace->nspname, "pg_", 3) == 0)
4997 * Skip cast if function isn't from pg_ and that namespace is
5001 strncmp(funcInfo->pronamespace->nspname, "pg_", 3) != 0 &&
5002 !funcInfo->pronamespace->dump)
5006 * Same for the Source type
5008 if (strncmp(sourceInfo->typnamespace->nspname, "pg_", 3) != 0 &&
5009 !sourceInfo->typnamespace->dump)
5013 * and the target type.
5015 if (strncmp(targetInfo->typnamespace->nspname, "pg_", 3) != 0 &&
5016 !targetInfo->typnamespace->dump)
5019 /* Make sure we are in proper schema (needed for getFormattedTypeName) */
5020 selectSourceSchema("pg_catalog");
5022 defqry = createPQExpBuffer();
5023 delqry = createPQExpBuffer();
5024 castsig = createPQExpBuffer();
5026 appendPQExpBuffer(delqry, "DROP CAST (%s AS %s);\n",
5027 getFormattedTypeName(cast->castsource, zeroAsNone),
5028 getFormattedTypeName(cast->casttarget, zeroAsNone));
5030 appendPQExpBuffer(defqry, "CREATE CAST (%s AS %s) ",
5031 getFormattedTypeName(cast->castsource, zeroAsNone),
5032 getFormattedTypeName(cast->casttarget, zeroAsNone));
5034 if (!OidIsValid(cast->castfunc))
5035 appendPQExpBuffer(defqry, "WITHOUT FUNCTION");
5037 appendPQExpBuffer(defqry, "WITH FUNCTION %s",
5038 format_function_signature(funcInfo, NULL, true));
5040 if (cast->castcontext == 'a')
5041 appendPQExpBuffer(defqry, " AS ASSIGNMENT");
5042 else if (cast->castcontext == 'i')
5043 appendPQExpBuffer(defqry, " AS IMPLICIT");
5044 appendPQExpBuffer(defqry, ";\n");
5046 appendPQExpBuffer(castsig, "CAST (%s AS %s)",
5047 getFormattedTypeName(cast->castsource, zeroAsNone),
5048 getFormattedTypeName(cast->casttarget, zeroAsNone));
5050 ArchiveEntry(fout, cast->dobj.catId, cast->dobj.dumpId,
5052 sourceInfo->typnamespace->nspname, "",
5053 "CAST", defqry->data, delqry->data, NULL,
5054 cast->dobj.dependencies, cast->dobj.nDeps,
5057 /* Dump Cast Comments */
5058 resetPQExpBuffer(defqry);
5059 appendPQExpBuffer(defqry, "CAST (%s AS %s)",
5060 getFormattedTypeName(cast->castsource, zeroAsNone),
5061 getFormattedTypeName(cast->casttarget, zeroAsNone));
5062 dumpComment(fout, defqry->data,
5064 cast->dobj.catId, 0, cast->dobj.dumpId);
5066 destroyPQExpBuffer(defqry);
5067 destroyPQExpBuffer(delqry);
5068 destroyPQExpBuffer(castsig);
5073 * write out a single operator definition
5076 dumpOpr(Archive *fout, OprInfo *oprinfo)
5082 PQExpBuffer details;
5113 /* Dump only operators in dumpable namespaces */
5114 if (!oprinfo->oprnamespace->dump || dataOnly)
5118 * some operators are invalid because they were the result of user
5119 * defining operators before commutators exist
5121 if (!OidIsValid(oprinfo->oprcode))
5124 query = createPQExpBuffer();
5125 q = createPQExpBuffer();
5126 delq = createPQExpBuffer();
5127 oprid = createPQExpBuffer();
5128 details = createPQExpBuffer();
5130 /* Make sure we are in proper schema so regoperator works correctly */
5131 selectSourceSchema(oprinfo->oprnamespace->nspname);
5133 if (g_fout->remoteVersion >= 70300)
5135 appendPQExpBuffer(query, "SELECT oprkind, "
5136 "oprcode::pg_catalog.regprocedure, "
5137 "oprleft::pg_catalog.regtype, "
5138 "oprright::pg_catalog.regtype, "
5139 "oprcom::pg_catalog.regoperator, "
5140 "oprnegate::pg_catalog.regoperator, "
5141 "oprrest::pg_catalog.regprocedure, "
5142 "oprjoin::pg_catalog.regprocedure, "
5144 "oprlsortop::pg_catalog.regoperator, "
5145 "oprrsortop::pg_catalog.regoperator, "
5146 "oprltcmpop::pg_catalog.regoperator, "
5147 "oprgtcmpop::pg_catalog.regoperator "
5148 "from pg_catalog.pg_operator "
5149 "where oid = '%u'::pg_catalog.oid",
5150 oprinfo->dobj.catId.oid);
5152 else if (g_fout->remoteVersion >= 70100)
5154 appendPQExpBuffer(query, "SELECT oprkind, oprcode, "
5155 "CASE WHEN oprleft = 0 THEN '-' "
5156 "ELSE format_type(oprleft, NULL) END as oprleft, "
5157 "CASE WHEN oprright = 0 THEN '-' "
5158 "ELSE format_type(oprright, NULL) END as oprright, "
5159 "oprcom, oprnegate, oprrest, oprjoin, "
5160 "oprcanhash, oprlsortop, oprrsortop, "
5161 "0 as oprltcmpop, 0 as oprgtcmpop "
5163 "where oid = '%u'::oid",
5164 oprinfo->dobj.catId.oid);
5168 appendPQExpBuffer(query, "SELECT oprkind, oprcode, "
5169 "CASE WHEN oprleft = 0 THEN '-'::name "
5170 "ELSE (select typname from pg_type where oid = oprleft) END as oprleft, "
5171 "CASE WHEN oprright = 0 THEN '-'::name "
5172 "ELSE (select typname from pg_type where oid = oprright) END as oprright, "
5173 "oprcom, oprnegate, oprrest, oprjoin, "
5174 "oprcanhash, oprlsortop, oprrsortop, "
5175 "0 as oprltcmpop, 0 as oprgtcmpop "
5177 "where oid = '%u'::oid",
5178 oprinfo->dobj.catId.oid);
5181 res = PQexec(g_conn, query->data);
5182 check_sql_result(res, g_conn, query->data, PGRES_TUPLES_OK);
5184 /* Expecting a single result only */
5185 ntups = PQntuples(res);
5188 write_msg(NULL, "Got %d rows instead of one from: %s",
5189 ntups, query->data);
5193 i_oprkind = PQfnumber(res, "oprkind");
5194 i_oprcode = PQfnumber(res, "oprcode");
5195 i_oprleft = PQfnumber(res, "oprleft");
5196 i_oprright = PQfnumber(res, "oprright");
5197 i_oprcom = PQfnumber(res, "oprcom");
5198 i_oprnegate = PQfnumber(res, "oprnegate");
5199 i_oprrest = PQfnumber(res, "oprrest");
5200 i_oprjoin = PQfnumber(res, "oprjoin");
5201 i_oprcanhash = PQfnumber(res, "oprcanhash");
5202 i_oprlsortop = PQfnumber(res, "oprlsortop");
5203 i_oprrsortop = PQfnumber(res, "oprrsortop");
5204 i_oprltcmpop = PQfnumber(res, "oprltcmpop");
5205 i_oprgtcmpop = PQfnumber(res, "oprgtcmpop");
5207 oprkind = PQgetvalue(res, 0, i_oprkind);
5208 oprcode = PQgetvalue(res, 0, i_oprcode);
5209 oprleft = PQgetvalue(res, 0, i_oprleft);
5210 oprright = PQgetvalue(res, 0, i_oprright);
5211 oprcom = PQgetvalue(res, 0, i_oprcom);
5212 oprnegate = PQgetvalue(res, 0, i_oprnegate);
5213 oprrest = PQgetvalue(res, 0, i_oprrest);
5214 oprjoin = PQgetvalue(res, 0, i_oprjoin);
5215 oprcanhash = PQgetvalue(res, 0, i_oprcanhash);
5216 oprlsortop = PQgetvalue(res, 0, i_oprlsortop);
5217 oprrsortop = PQgetvalue(res, 0, i_oprrsortop);
5218 oprltcmpop = PQgetvalue(res, 0, i_oprltcmpop);
5219 oprgtcmpop = PQgetvalue(res, 0, i_oprgtcmpop);
5221 appendPQExpBuffer(details, " PROCEDURE = %s",
5222 convertRegProcReference(oprcode));
5224 appendPQExpBuffer(oprid, "%s (",
5228 * right unary means there's a left arg and left unary means there's a
5231 if (strcmp(oprkind, "r") == 0 ||
5232 strcmp(oprkind, "b") == 0)
5234 if (g_fout->remoteVersion >= 70100)
5237 name = fmtId(oprleft);
5238 appendPQExpBuffer(details, ",\n LEFTARG = %s", name);
5239 appendPQExpBuffer(oprid, "%s", name);
5242 appendPQExpBuffer(oprid, "NONE");
5244 if (strcmp(oprkind, "l") == 0 ||
5245 strcmp(oprkind, "b") == 0)
5247 if (g_fout->remoteVersion >= 70100)
5250 name = fmtId(oprright);
5251 appendPQExpBuffer(details, ",\n RIGHTARG = %s", name);
5252 appendPQExpBuffer(oprid, ", %s)", name);
5255 appendPQExpBuffer(oprid, ", NONE)");
5257 name = convertOperatorReference(oprcom);
5259 appendPQExpBuffer(details, ",\n COMMUTATOR = %s", name);
5261 name = convertOperatorReference(oprnegate);
5263 appendPQExpBuffer(details, ",\n NEGATOR = %s", name);
5265 if (strcmp(oprcanhash, "t") == 0)
5266 appendPQExpBuffer(details, ",\n HASHES");
5268 name = convertRegProcReference(oprrest);
5270 appendPQExpBuffer(details, ",\n RESTRICT = %s", name);
5272 name = convertRegProcReference(oprjoin);
5274 appendPQExpBuffer(details, ",\n JOIN = %s", name);
5276 name = convertOperatorReference(oprlsortop);
5278 appendPQExpBuffer(details, ",\n SORT1 = %s", name);
5280 name = convertOperatorReference(oprrsortop);
5282 appendPQExpBuffer(details, ",\n SORT2 = %s", name);
5284 name = convertOperatorReference(oprltcmpop);
5286 appendPQExpBuffer(details, ",\n LTCMP = %s", name);
5288 name = convertOperatorReference(oprgtcmpop);
5290 appendPQExpBuffer(details, ",\n GTCMP = %s", name);
5293 * DROP must be fully qualified in case same name appears in
5296 appendPQExpBuffer(delq, "DROP OPERATOR %s.%s;\n",
5297 fmtId(oprinfo->oprnamespace->nspname),
5300 appendPQExpBuffer(q, "CREATE OPERATOR %s (\n%s\n);\n",
5301 oprinfo->oprname, details->data);
5303 ArchiveEntry(fout, oprinfo->dobj.catId, oprinfo->dobj.dumpId,
5305 oprinfo->oprnamespace->nspname, oprinfo->usename,
5306 "OPERATOR", q->data, delq->data, NULL,
5307 oprinfo->dobj.dependencies, oprinfo->dobj.nDeps,
5310 /* Dump Operator Comments */
5311 resetPQExpBuffer(q);
5312 appendPQExpBuffer(q, "OPERATOR %s", oprid->data);
5313 dumpComment(fout, q->data,
5314 oprinfo->oprnamespace->nspname, oprinfo->usename,
5315 oprinfo->dobj.catId, 0, oprinfo->dobj.dumpId);
5319 destroyPQExpBuffer(query);
5320 destroyPQExpBuffer(q);
5321 destroyPQExpBuffer(delq);
5322 destroyPQExpBuffer(oprid);
5323 destroyPQExpBuffer(details);
5327 * Convert a function reference obtained from pg_operator
5329 * Returns what to print, or NULL if function references is InvalidOid
5331 * In 7.3 the input is a REGPROCEDURE display; we have to strip the
5332 * argument-types part. In prior versions, the input is a REGPROC display.
5335 convertRegProcReference(const char *proc)
5337 /* In all cases "-" means a null reference */
5338 if (strcmp(proc, "-") == 0)
5341 if (g_fout->remoteVersion >= 70300)
5347 name = strdup(proc);
5348 /* find non-double-quoted left paren */
5350 for (paren = name; *paren; paren++)
5352 if (*paren == '(' && !inquote)
5363 /* REGPROC before 7.3 does not quote its result */
5368 * Convert an operator cross-reference obtained from pg_operator
5370 * Returns what to print, or NULL to print nothing
5372 * In 7.3 the input is a REGOPERATOR display; we have to strip the
5373 * argument-types part. In prior versions, the input is just a
5374 * numeric OID, which we search our operator list for.
5377 convertOperatorReference(const char *opr)
5381 /* In all cases "0" means a null reference */
5382 if (strcmp(opr, "0") == 0)
5385 if (g_fout->remoteVersion >= 70300)
5392 /* find non-double-quoted left paren */
5394 for (paren = name; *paren; paren++)
5396 if (*paren == '(' && !inquote)
5407 oprInfo = findOprByOid(atooid(opr));
5408 if (oprInfo == NULL)
5410 write_msg(NULL, "WARNING: could not find operator with OID %s\n",
5414 return oprInfo->oprname;
5419 * write out a single operator class definition
5422 dumpOpclass(Archive *fout, OpclassInfo *opcinfo)
5450 /* Dump only opclasses in dumpable namespaces */
5451 if (!opcinfo->opcnamespace->dump || dataOnly)
5455 * XXX currently we do not implement dumping of operator classes from
5456 * pre-7.3 databases. This could be done but it seems not worth the
5459 if (g_fout->remoteVersion < 70300)
5462 query = createPQExpBuffer();
5463 q = createPQExpBuffer();
5464 delq = createPQExpBuffer();
5466 /* Make sure we are in proper schema so regoperator works correctly */
5467 selectSourceSchema(opcinfo->opcnamespace->nspname);
5469 /* Get additional fields from the pg_opclass row */
5470 appendPQExpBuffer(query, "SELECT opcintype::pg_catalog.regtype, "
5471 "opckeytype::pg_catalog.regtype, "
5473 "(SELECT amname FROM pg_catalog.pg_am WHERE oid = opcamid) AS amname "
5474 "FROM pg_catalog.pg_opclass "
5475 "WHERE oid = '%u'::pg_catalog.oid",
5476 opcinfo->dobj.catId.oid);
5478 res = PQexec(g_conn, query->data);
5479 check_sql_result(res, g_conn, query->data, PGRES_TUPLES_OK);
5481 /* Expecting a single result only */
5482 ntups = PQntuples(res);
5485 write_msg(NULL, "Got %d rows instead of one from: %s",
5486 ntups, query->data);
5490 i_opcintype = PQfnumber(res, "opcintype");
5491 i_opckeytype = PQfnumber(res, "opckeytype");
5492 i_opcdefault = PQfnumber(res, "opcdefault");
5493 i_amname = PQfnumber(res, "amname");
5495 opcintype = PQgetvalue(res, 0, i_opcintype);
5496 opckeytype = PQgetvalue(res, 0, i_opckeytype);
5497 opcdefault = PQgetvalue(res, 0, i_opcdefault);
5498 /* amname will still be needed after we PQclear res */
5499 amname = strdup(PQgetvalue(res, 0, i_amname));
5502 * DROP must be fully qualified in case same name appears in
5505 appendPQExpBuffer(delq, "DROP OPERATOR CLASS %s",
5506 fmtId(opcinfo->opcnamespace->nspname));
5507 appendPQExpBuffer(delq, ".%s",
5508 fmtId(opcinfo->opcname));
5509 appendPQExpBuffer(delq, " USING %s;\n",
5512 /* Build the fixed portion of the CREATE command */
5513 appendPQExpBuffer(q, "CREATE OPERATOR CLASS %s\n ",
5514 fmtId(opcinfo->opcname));
5515 if (strcmp(opcdefault, "t") == 0)
5516 appendPQExpBuffer(q, "DEFAULT ");
5517 appendPQExpBuffer(q, "FOR TYPE %s USING %s AS\n ",
5523 if (strcmp(opckeytype, "-") != 0)
5525 appendPQExpBuffer(q, "STORAGE %s",
5533 * Now fetch and print the OPERATOR entries (pg_amop rows).
5535 resetPQExpBuffer(query);
5537 appendPQExpBuffer(query, "SELECT amopstrategy, amopreqcheck, "
5538 "amopopr::pg_catalog.regoperator "
5539 "FROM pg_catalog.pg_amop "
5540 "WHERE amopclaid = '%u'::pg_catalog.oid "
5541 "ORDER BY amopstrategy",
5542 opcinfo->dobj.catId.oid);
5544 res = PQexec(g_conn, query->data);
5545 check_sql_result(res, g_conn, query->data, PGRES_TUPLES_OK);
5547 ntups = PQntuples(res);
5549 i_amopstrategy = PQfnumber(res, "amopstrategy");
5550 i_amopreqcheck = PQfnumber(res, "amopreqcheck");
5551 i_amopopr = PQfnumber(res, "amopopr");
5553 for (i = 0; i < ntups; i++)
5555 amopstrategy = PQgetvalue(res, i, i_amopstrategy);
5556 amopreqcheck = PQgetvalue(res, i, i_amopreqcheck);
5557 amopopr = PQgetvalue(res, i, i_amopopr);
5560 appendPQExpBuffer(q, " ,\n ");
5562 appendPQExpBuffer(q, "OPERATOR %s %s",
5563 amopstrategy, amopopr);
5564 if (strcmp(amopreqcheck, "t") == 0)
5565 appendPQExpBuffer(q, " RECHECK");
5573 * Now fetch and print the FUNCTION entries (pg_amproc rows).
5575 resetPQExpBuffer(query);
5577 appendPQExpBuffer(query, "SELECT amprocnum, "
5578 "amproc::pg_catalog.regprocedure "
5579 "FROM pg_catalog.pg_amproc "
5580 "WHERE amopclaid = '%u'::pg_catalog.oid "
5581 "ORDER BY amprocnum",
5582 opcinfo->dobj.catId.oid);
5584 res = PQexec(g_conn, query->data);
5585 check_sql_result(res, g_conn, query->data, PGRES_TUPLES_OK);
5587 ntups = PQntuples(res);
5589 i_amprocnum = PQfnumber(res, "amprocnum");
5590 i_amproc = PQfnumber(res, "amproc");
5592 for (i = 0; i < ntups; i++)
5594 amprocnum = PQgetvalue(res, i, i_amprocnum);
5595 amproc = PQgetvalue(res, i, i_amproc);
5598 appendPQExpBuffer(q, " ,\n ");
5600 appendPQExpBuffer(q, "FUNCTION %s %s",
5608 appendPQExpBuffer(q, ";\n");
5610 ArchiveEntry(fout, opcinfo->dobj.catId, opcinfo->dobj.dumpId,
5612 opcinfo->opcnamespace->nspname, opcinfo->usename,
5613 "OPERATOR CLASS", q->data, delq->data, NULL,
5614 opcinfo->dobj.dependencies, opcinfo->dobj.nDeps,
5617 /* Dump Operator Class Comments */
5618 resetPQExpBuffer(q);
5619 appendPQExpBuffer(q, "OPERATOR CLASS %s",
5620 fmtId(opcinfo->opcname));
5621 appendPQExpBuffer(q, " USING %s",
5623 dumpComment(fout, q->data,
5624 NULL, opcinfo->usename,
5625 opcinfo->dobj.catId, 0, opcinfo->dobj.dumpId);
5628 destroyPQExpBuffer(query);
5629 destroyPQExpBuffer(q);
5630 destroyPQExpBuffer(delq);
5635 * write out a single conversion definition
5638 dumpConversion(Archive *fout, ConvInfo *convinfo)
5643 PQExpBuffer details;
5647 int i_conforencoding;
5648 int i_contoencoding;
5651 const char *conname;
5652 const char *conforencoding;
5653 const char *contoencoding;
5654 const char *conproc;
5657 /* Dump only conversions in dumpable namespaces */
5658 if (!convinfo->connamespace->dump || dataOnly)
5661 query = createPQExpBuffer();
5662 q = createPQExpBuffer();
5663 delq = createPQExpBuffer();
5664 details = createPQExpBuffer();
5666 /* Make sure we are in proper schema */
5667 selectSourceSchema(convinfo->connamespace->nspname);
5669 /* Get conversion-specific details */
5670 appendPQExpBuffer(query, "SELECT conname, "
5671 "pg_catalog.pg_encoding_to_char(conforencoding) AS conforencoding, "
5672 "pg_catalog.pg_encoding_to_char(contoencoding) AS contoencoding, "
5673 "conproc, condefault "
5674 "FROM pg_catalog.pg_conversion c "
5675 "WHERE c.oid = '%u'::pg_catalog.oid",
5676 convinfo->dobj.catId.oid);
5678 res = PQexec(g_conn, query->data);
5679 check_sql_result(res, g_conn, query->data, PGRES_TUPLES_OK);
5681 /* Expecting a single result only */
5682 ntups = PQntuples(res);
5685 write_msg(NULL, "Got %d rows instead of one from: %s",
5686 ntups, query->data);
5690 i_conname = PQfnumber(res, "conname");
5691 i_conforencoding = PQfnumber(res, "conforencoding");
5692 i_contoencoding = PQfnumber(res, "contoencoding");
5693 i_conproc = PQfnumber(res, "conproc");
5694 i_condefault = PQfnumber(res, "condefault");
5696 conname = PQgetvalue(res, 0, i_conname);
5697 conforencoding = PQgetvalue(res, 0, i_conforencoding);
5698 contoencoding = PQgetvalue(res, 0, i_contoencoding);
5699 conproc = PQgetvalue(res, 0, i_conproc);
5700 condefault = (PQgetvalue(res, 0, i_condefault)[0] == 't');
5703 * DROP must be fully qualified in case same name appears in
5706 appendPQExpBuffer(delq, "DROP CONVERSION %s",
5707 fmtId(convinfo->connamespace->nspname));
5708 appendPQExpBuffer(delq, ".%s;\n",
5709 fmtId(convinfo->conname));
5711 appendPQExpBuffer(q, "CREATE %sCONVERSION %s FOR ",
5712 (condefault) ? "DEFAULT " : "",
5713 fmtId(convinfo->conname));
5714 appendStringLiteral(q, conforencoding, true);
5715 appendPQExpBuffer(q, " TO ");
5716 appendStringLiteral(q, contoencoding, true);
5717 /* regproc is automatically quoted in 7.3 and above */
5718 appendPQExpBuffer(q, " FROM %s;\n", conproc);
5720 ArchiveEntry(fout, convinfo->dobj.catId, convinfo->dobj.dumpId,
5722 convinfo->connamespace->nspname, convinfo->usename,
5723 "CONVERSION", q->data, delq->data, NULL,
5724 convinfo->dobj.dependencies, convinfo->dobj.nDeps,
5727 /* Dump Conversion Comments */
5728 resetPQExpBuffer(q);
5729 appendPQExpBuffer(q, "CONVERSION %s", fmtId(convinfo->conname));
5730 dumpComment(fout, q->data,
5731 convinfo->connamespace->nspname, convinfo->usename,
5732 convinfo->dobj.catId, 0, convinfo->dobj.dumpId);
5736 destroyPQExpBuffer(query);
5737 destroyPQExpBuffer(q);
5738 destroyPQExpBuffer(delq);
5739 destroyPQExpBuffer(details);
5743 * format_aggregate_signature: generate aggregate name and argument list
5745 * The argument type names are qualified if needed. The aggregate name
5746 * is never qualified.
5749 format_aggregate_signature(AggInfo *agginfo, Archive *fout, bool honor_quotes)
5751 PQExpBufferData buf;
5753 initPQExpBuffer(&buf);
5755 appendPQExpBuffer(&buf, "%s",
5756 fmtId(agginfo->aggfn.proname));
5758 appendPQExpBuffer(&buf, "%s", agginfo->aggfn.proname);
5760 /* If using regtype or format_type, fmtbasetype is already quoted */
5761 if (fout->remoteVersion >= 70100)
5763 if (agginfo->anybasetype)
5764 appendPQExpBuffer(&buf, "(*)");
5766 appendPQExpBuffer(&buf, "(%s)", agginfo->fmtbasetype);
5770 if (agginfo->anybasetype)
5771 appendPQExpBuffer(&buf, "(*)");
5773 appendPQExpBuffer(&buf, "(%s)",
5774 fmtId(agginfo->fmtbasetype));
5782 * write out a single aggregate definition
5785 dumpAgg(Archive *fout, AggInfo *agginfo)
5790 PQExpBuffer details;
5802 const char *aggtransfn;
5803 const char *aggfinalfn;
5804 const char *aggtranstype;
5805 const char *agginitval;
5808 /* Dump only aggs in dumpable namespaces */
5809 if (!agginfo->aggfn.pronamespace->dump || dataOnly)
5812 query = createPQExpBuffer();
5813 q = createPQExpBuffer();
5814 delq = createPQExpBuffer();
5815 details = createPQExpBuffer();
5817 /* Make sure we are in proper schema */
5818 selectSourceSchema(agginfo->aggfn.pronamespace->nspname);
5820 /* Get aggregate-specific details */
5821 if (g_fout->remoteVersion >= 70300)
5823 appendPQExpBuffer(query, "SELECT aggtransfn, "
5824 "aggfinalfn, aggtranstype::pg_catalog.regtype, "
5826 "proargtypes[0] = 'pg_catalog.\"any\"'::pg_catalog.regtype as anybasetype, "
5827 "proargtypes[0]::pg_catalog.regtype as fmtbasetype, "
5828 "'t'::boolean as convertok "
5829 "from pg_catalog.pg_aggregate a, pg_catalog.pg_proc p "
5830 "where a.aggfnoid = p.oid "
5831 "and p.oid = '%u'::pg_catalog.oid",
5832 agginfo->aggfn.dobj.catId.oid);
5834 else if (g_fout->remoteVersion >= 70100)
5836 appendPQExpBuffer(query, "SELECT aggtransfn, aggfinalfn, "
5837 "format_type(aggtranstype, NULL) as aggtranstype, "
5839 "aggbasetype = 0 as anybasetype, "
5840 "CASE WHEN aggbasetype = 0 THEN '-' "
5841 "ELSE format_type(aggbasetype, NULL) END as fmtbasetype, "
5842 "'t'::boolean as convertok "
5843 "from pg_aggregate "
5844 "where oid = '%u'::oid",
5845 agginfo->aggfn.dobj.catId.oid);
5849 appendPQExpBuffer(query, "SELECT aggtransfn1 as aggtransfn, "
5851 "(select typname from pg_type where oid = aggtranstype1) as aggtranstype, "
5852 "agginitval1 as agginitval, "
5853 "aggbasetype = 0 as anybasetype, "
5854 "(select typname from pg_type where oid = aggbasetype) as fmtbasetype, "
5855 "(aggtransfn2 = 0 and aggtranstype2 = 0 and agginitval2 is null) as convertok "
5856 "from pg_aggregate "
5857 "where oid = '%u'::oid",
5858 agginfo->aggfn.dobj.catId.oid);
5861 res = PQexec(g_conn, query->data);
5862 check_sql_result(res, g_conn, query->data, PGRES_TUPLES_OK);
5864 /* Expecting a single result only */
5865 ntups = PQntuples(res);
5868 write_msg(NULL, "Got %d rows instead of one from: %s",
5869 ntups, query->data);
5873 i_aggtransfn = PQfnumber(res, "aggtransfn");
5874 i_aggfinalfn = PQfnumber(res, "aggfinalfn");
5875 i_aggtranstype = PQfnumber(res, "aggtranstype");
5876 i_agginitval = PQfnumber(res, "agginitval");
5877 i_anybasetype = PQfnumber(res, "anybasetype");
5878 i_fmtbasetype = PQfnumber(res, "fmtbasetype");
5879 i_convertok = PQfnumber(res, "convertok");
5881 aggtransfn = PQgetvalue(res, 0, i_aggtransfn);
5882 aggfinalfn = PQgetvalue(res, 0, i_aggfinalfn);
5883 aggtranstype = PQgetvalue(res, 0, i_aggtranstype);
5884 agginitval = PQgetvalue(res, 0, i_agginitval);
5885 /* we save anybasetype for format_aggregate_signature */
5886 agginfo->anybasetype = (PQgetvalue(res, 0, i_anybasetype)[0] == 't');
5887 /* we save fmtbasetype for format_aggregate_signature */
5888 agginfo->fmtbasetype = strdup(PQgetvalue(res, 0, i_fmtbasetype));
5889 convertok = (PQgetvalue(res, 0, i_convertok)[0] == 't');
5891 aggsig = format_aggregate_signature(agginfo, fout, true);
5892 aggsig_tag = format_aggregate_signature(agginfo, fout, false);
5896 write_msg(NULL, "WARNING: aggregate function %s could not be dumped correctly for this database version; ignored\n",
5901 if (g_fout->remoteVersion >= 70300)
5903 /* If using 7.3's regproc or regtype, data is already quoted */
5904 appendPQExpBuffer(details, " BASETYPE = %s,\n SFUNC = %s,\n STYPE = %s",
5905 agginfo->anybasetype ? "'any'" :
5906 agginfo->fmtbasetype,
5910 else if (g_fout->remoteVersion >= 70100)
5912 /* format_type quotes, regproc does not */
5913 appendPQExpBuffer(details, " BASETYPE = %s,\n SFUNC = %s,\n STYPE = %s",
5914 agginfo->anybasetype ? "'any'" :
5915 agginfo->fmtbasetype,
5921 /* need quotes all around */
5922 appendPQExpBuffer(details, " BASETYPE = %s,\n",
5923 agginfo->anybasetype ? "'any'" :
5924 fmtId(agginfo->fmtbasetype));
5925 appendPQExpBuffer(details, " SFUNC = %s,\n",
5927 appendPQExpBuffer(details, " STYPE = %s",
5928 fmtId(aggtranstype));
5931 if (!PQgetisnull(res, 0, i_agginitval))
5933 appendPQExpBuffer(details, ",\n INITCOND = ");
5934 appendStringLiteral(details, agginitval, true);
5937 if (strcmp(aggfinalfn, "-") != 0)
5939 appendPQExpBuffer(details, ",\n FINALFUNC = %s",
5944 * DROP must be fully qualified in case same name appears in
5947 appendPQExpBuffer(delq, "DROP AGGREGATE %s.%s;\n",
5948 fmtId(agginfo->aggfn.pronamespace->nspname),
5951 appendPQExpBuffer(q, "CREATE AGGREGATE %s (\n%s\n);\n",
5952 fmtId(agginfo->aggfn.proname),
5955 ArchiveEntry(fout, agginfo->aggfn.dobj.catId, agginfo->aggfn.dobj.dumpId,
5957 agginfo->aggfn.pronamespace->nspname, agginfo->aggfn.usename,
5958 "AGGREGATE", q->data, delq->data, NULL,
5959 agginfo->aggfn.dobj.dependencies, agginfo->aggfn.dobj.nDeps,
5962 /* Dump Aggregate Comments */
5963 resetPQExpBuffer(q);
5964 appendPQExpBuffer(q, "AGGREGATE %s", aggsig);
5965 dumpComment(fout, q->data,
5966 agginfo->aggfn.pronamespace->nspname, agginfo->aggfn.usename,
5967 agginfo->aggfn.dobj.catId, 0, agginfo->aggfn.dobj.dumpId);
5970 * Since there is no GRANT ON AGGREGATE syntax, we have to make the
5971 * ACL command look like a function's GRANT; in particular this affects
5972 * the syntax for aggregates on ANY.
5977 aggsig = format_function_signature(&agginfo->aggfn, NULL, true);
5978 aggsig_tag = format_function_signature(&agginfo->aggfn, NULL, false);
5980 dumpACL(fout, agginfo->aggfn.dobj.catId, agginfo->aggfn.dobj.dumpId,
5983 agginfo->aggfn.pronamespace->nspname,
5984 agginfo->aggfn.usename, agginfo->aggfn.proacl);
5991 destroyPQExpBuffer(query);
5992 destroyPQExpBuffer(q);
5993 destroyPQExpBuffer(delq);
5994 destroyPQExpBuffer(details);
5999 * Write out grant/revoke information
6001 * 'objCatId' is the catalog ID of the underlying object.
6002 * 'objDumpId' is the dump ID of the underlying object.
6003 * 'type' must be TABLE, FUNCTION, LANGUAGE, or SCHEMA.
6004 * 'name' is the formatted name of the object. Must be quoted etc. already.
6005 * 'tag' is the tag for the archive entry (typ. unquoted name of object).
6006 * 'nspname' is the namespace the object is in (NULL if none).
6007 * 'owner' is the owner, NULL if there is no owner (for languages).
6008 * 'acls' is the string read out of the fooacl system catalog field;
6009 * it will be parsed here.
6013 dumpACL(Archive *fout, CatalogId objCatId, DumpId objDumpId,
6014 const char *type, const char *name,
6015 const char *tag, const char *nspname, const char *owner,
6020 /* Do nothing if ACL dump is not enabled */
6021 if (dataOnly || aclsSkip)
6024 sql = createPQExpBuffer();
6026 if (!buildACLCommands(name, type, acls, owner, fout->remoteVersion, sql))
6028 write_msg(NULL, "could not parse ACL list (%s) for object \"%s\" (%s)\n",
6034 ArchiveEntry(fout, nilCatalogId, createDumpId(),
6037 "ACL", sql->data, "", NULL,
6041 destroyPQExpBuffer(sql);
6046 * write out to fout the declarations (not data) of a user-defined table
6049 dumpTable(Archive *fout, TableInfo *tbinfo)
6055 if (tbinfo->relkind == RELKIND_SEQUENCE)
6056 dumpSequence(fout, tbinfo);
6058 dumpTableSchema(fout, tbinfo);
6060 /* Handle the ACL here */
6061 namecopy = strdup(fmtId(tbinfo->relname));
6062 dumpACL(fout, tbinfo->dobj.catId, tbinfo->dobj.dumpId, "TABLE",
6063 namecopy, tbinfo->relname,
6064 tbinfo->relnamespace->nspname, tbinfo->usename,
6072 * write the declaration (not data) of one user-defined table or view
6075 dumpTableSchema(Archive *fout, TableInfo *tbinfo)
6077 PQExpBuffer query = createPQExpBuffer();
6078 PQExpBuffer q = createPQExpBuffer();
6079 PQExpBuffer delq = createPQExpBuffer();
6082 TableInfo **parents;
6083 int actual_atts; /* number of attrs in this CREATE statment */
6089 /* Make sure we are in proper schema */
6090 selectSourceSchema(tbinfo->relnamespace->nspname);
6092 /* Is it a table or a view? */
6093 if (tbinfo->relkind == RELKIND_VIEW)
6097 reltypename = "VIEW";
6099 /* Fetch the view definition */
6100 if (g_fout->remoteVersion >= 70300)
6102 /* Beginning in 7.3, viewname is not unique; rely on OID */
6103 appendPQExpBuffer(query,
6104 "SELECT pg_catalog.pg_get_viewdef('%u'::pg_catalog.oid) as viewdef",
6105 tbinfo->dobj.catId.oid);
6109 appendPQExpBuffer(query, "SELECT definition as viewdef "
6110 " from pg_views where viewname = ");
6111 appendStringLiteral(query, tbinfo->relname, true);
6112 appendPQExpBuffer(query, ";");
6115 res = PQexec(g_conn, query->data);
6116 check_sql_result(res, g_conn, query->data, PGRES_TUPLES_OK);
6118 if (PQntuples(res) != 1)
6120 if (PQntuples(res) < 1)
6121 write_msg(NULL, "query to obtain definition of view \"%s\" returned no data\n",
6124 write_msg(NULL, "query to obtain definition of view \"%s\" returned more than one definition\n",
6129 viewdef = PQgetvalue(res, 0, 0);
6131 if (strlen(viewdef) == 0)
6133 write_msg(NULL, "definition of view \"%s\" appears to be empty (length zero)\n",
6139 * DROP must be fully qualified in case same name appears in
6142 appendPQExpBuffer(delq, "DROP VIEW %s.",
6143 fmtId(tbinfo->relnamespace->nspname));
6144 appendPQExpBuffer(delq, "%s;\n",
6145 fmtId(tbinfo->relname));
6147 appendPQExpBuffer(q, "CREATE VIEW %s AS\n %s\n",
6148 fmtId(tbinfo->relname), viewdef);
6154 reltypename = "TABLE";
6155 numParents = tbinfo->numParents;
6156 parents = tbinfo->parents;
6159 * DROP must be fully qualified in case same name appears in
6162 appendPQExpBuffer(delq, "DROP TABLE %s.",
6163 fmtId(tbinfo->relnamespace->nspname));
6164 appendPQExpBuffer(delq, "%s;\n",
6165 fmtId(tbinfo->relname));
6167 appendPQExpBuffer(q, "CREATE TABLE %s (",
6168 fmtId(tbinfo->relname));
6170 for (j = 0; j < tbinfo->numatts; j++)
6172 /* Is this one of the table's own attrs, and not dropped ? */
6173 if (!tbinfo->inhAttrs[j] && !tbinfo->attisdropped[j])
6175 /* Format properly if not first attr */
6176 if (actual_atts > 0)
6177 appendPQExpBuffer(q, ",");
6178 appendPQExpBuffer(q, "\n ");
6180 /* Attribute name */
6181 appendPQExpBuffer(q, "%s ",
6182 fmtId(tbinfo->attnames[j]));
6184 /* Attribute type */
6185 if (g_fout->remoteVersion >= 70100)
6187 char *typname = tbinfo->atttypnames[j];
6189 if (tbinfo->attisserial[j])
6191 if (strcmp(typname, "integer") == 0)
6193 else if (strcmp(typname, "bigint") == 0)
6194 typname = "bigserial";
6196 appendPQExpBuffer(q, "%s", typname);
6200 /* If no format_type, fake it */
6201 appendPQExpBuffer(q, "%s",
6202 myFormatType(tbinfo->atttypnames[j],
6203 tbinfo->atttypmod[j]));
6207 * Default value --- suppress if inherited, serial,
6208 * or to be printed separately.
6210 if (tbinfo->attrdefs[j] != NULL &&
6211 !tbinfo->inhAttrDef[j] &&
6212 !tbinfo->attisserial[j] &&
6213 !tbinfo->attrdefs[j]->separate)
6214 appendPQExpBuffer(q, " DEFAULT %s",
6215 tbinfo->attrdefs[j]->adef_expr);
6218 * Not Null constraint --- suppress if inherited
6220 * Note: we could suppress this for serial columns since
6221 * SERIAL implies NOT NULL. We choose not to for forward
6222 * compatibility, since there has been some talk of making
6223 * SERIAL not imply NOT NULL, in which case the explicit
6224 * specification would be needed.
6226 if (tbinfo->notnull[j] && !tbinfo->inhNotNull[j])
6227 appendPQExpBuffer(q, " NOT NULL");
6234 * Add non-inherited CHECK constraints, if any.
6236 for (j = 0; j < tbinfo->ncheck; j++)
6238 ConstraintInfo *constr = &(tbinfo->checkexprs[j]);
6240 if (constr->coninherited || constr->separate)
6243 if (actual_atts > 0)
6244 appendPQExpBuffer(q, ",\n ");
6246 appendPQExpBuffer(q, "CONSTRAINT %s ",
6247 fmtId(constr->conname));
6248 appendPQExpBuffer(q, "%s", constr->condef);
6253 appendPQExpBuffer(q, "\n)");
6257 appendPQExpBuffer(q, "\nINHERITS (");
6258 for (k = 0; k < numParents; k++)
6260 TableInfo *parentRel = parents[k];
6263 appendPQExpBuffer(q, ", ");
6264 if (parentRel->relnamespace != tbinfo->relnamespace)
6265 appendPQExpBuffer(q, "%s.",
6266 fmtId(parentRel->relnamespace->nspname));
6267 appendPQExpBuffer(q, "%s",
6268 fmtId(parentRel->relname));
6270 appendPQExpBuffer(q, ")");
6273 appendPQExpBuffer(q, tbinfo->hasoids ? " WITH OIDS" : " WITHOUT OIDS");
6275 appendPQExpBuffer(q, ";\n");
6277 /* Loop dumping statistics and storage statements */
6278 for (j = 0; j < tbinfo->numatts; j++)
6281 * Dump per-column statistics information. We only issue an
6282 * ALTER TABLE statement if the attstattarget entry for this
6283 * column is non-negative (i.e. it's not the default value)
6285 if (tbinfo->attstattarget[j] >= 0 &&
6286 !tbinfo->attisdropped[j])
6288 appendPQExpBuffer(q, "ALTER TABLE ONLY %s ",
6289 fmtId(tbinfo->relname));
6290 appendPQExpBuffer(q, "ALTER COLUMN %s ",
6291 fmtId(tbinfo->attnames[j]));
6292 appendPQExpBuffer(q, "SET STATISTICS %d;\n",
6293 tbinfo->attstattarget[j]);
6297 * Dump per-column storage information. The statement is only
6298 * dumped if the storage has been changed from the type's
6301 if (!tbinfo->attisdropped[j] && tbinfo->attstorage[j] != tbinfo->typstorage[j])
6303 switch (tbinfo->attstorage[j])
6309 storage = "EXTERNAL";
6315 storage = "EXTENDED";
6322 * Only dump the statement if it's a storage type we
6325 if (storage != NULL)
6327 appendPQExpBuffer(q, "ALTER TABLE ONLY %s ",
6328 fmtId(tbinfo->relname));
6329 appendPQExpBuffer(q, "ALTER COLUMN %s ",
6330 fmtId(tbinfo->attnames[j]));
6331 appendPQExpBuffer(q, "SET STORAGE %s;\n",
6338 ArchiveEntry(fout, tbinfo->dobj.catId, tbinfo->dobj.dumpId,
6340 tbinfo->relnamespace->nspname, tbinfo->usename,
6341 reltypename, q->data, delq->data, NULL,
6342 tbinfo->dobj.dependencies, tbinfo->dobj.nDeps,
6345 /* Dump Table Comments */
6346 dumpTableComment(fout, tbinfo, reltypename);
6348 destroyPQExpBuffer(query);
6349 destroyPQExpBuffer(q);
6350 destroyPQExpBuffer(delq);
6354 * dumpAttrDef --- dump an attribute's default-value declaration
6357 dumpAttrDef(Archive *fout, AttrDefInfo *adinfo)
6359 TableInfo *tbinfo = adinfo->adtable;
6360 int adnum = adinfo->adnum;
6364 /* Only print it if "separate" mode is selected */
6365 if (!tbinfo->dump || !adinfo->separate || dataOnly)
6368 /* Don't print inherited or serial defaults, either */
6369 if (tbinfo->inhAttrDef[adnum-1] || tbinfo->attisserial[adnum-1])
6372 q = createPQExpBuffer();
6373 delq = createPQExpBuffer();
6375 appendPQExpBuffer(q, "ALTER TABLE %s ",
6376 fmtId(tbinfo->relname));
6377 appendPQExpBuffer(q, "ALTER COLUMN %s SET DEFAULT %s;\n",
6378 fmtId(tbinfo->attnames[adnum - 1]),
6382 * DROP must be fully qualified in case same name appears
6385 appendPQExpBuffer(delq, "ALTER TABLE %s.",
6386 fmtId(tbinfo->relnamespace->nspname));
6387 appendPQExpBuffer(delq, "%s ",
6388 fmtId(tbinfo->relname));
6389 appendPQExpBuffer(delq, "ALTER COLUMN %s DROP DEFAULT;\n",
6390 fmtId(tbinfo->attnames[adnum - 1]));
6392 ArchiveEntry(fout, adinfo->dobj.catId, adinfo->dobj.dumpId,
6393 tbinfo->attnames[adnum - 1],
6394 tbinfo->relnamespace->nspname, tbinfo->usename,
6395 "DEFAULT", q->data, delq->data, NULL,
6396 adinfo->dobj.dependencies, adinfo->dobj.nDeps,
6399 destroyPQExpBuffer(q);
6400 destroyPQExpBuffer(delq);
6404 * getAttrName: extract the correct name for an attribute
6406 * The array tblInfo->attnames[] only provides names of user attributes;
6407 * if a system attribute number is supplied, we have to fake it.
6408 * We also do a little bit of bounds checking for safety's sake.
6411 getAttrName(int attrnum, TableInfo *tblInfo)
6413 if (attrnum > 0 && attrnum <= tblInfo->numatts)
6414 return tblInfo->attnames[attrnum - 1];
6417 case SelfItemPointerAttributeNumber:
6419 case ObjectIdAttributeNumber:
6421 case MinTransactionIdAttributeNumber:
6423 case MinCommandIdAttributeNumber:
6425 case MaxTransactionIdAttributeNumber:
6427 case MaxCommandIdAttributeNumber:
6429 case TableOidAttributeNumber:
6432 write_msg(NULL, "invalid column number %d for table \"%s\"\n",
6433 attrnum, tblInfo->relname);
6435 return NULL; /* keep compiler quiet */
6440 * write out to fout a user-defined index
6443 dumpIndex(Archive *fout, IndxInfo *indxinfo)
6445 TableInfo *tbinfo = indxinfo->indextable;
6452 q = createPQExpBuffer();
6453 delq = createPQExpBuffer();
6456 * If there's an associated constraint, don't dump the index per se,
6457 * but do dump any comment for it.
6459 if (indxinfo->indexconstraint == 0)
6461 /* Plain secondary index */
6462 appendPQExpBuffer(q, "%s;\n", indxinfo->indexdef);
6464 /* If the index is clustered, we need to record that. */
6465 if (indxinfo->indisclustered)
6467 appendPQExpBuffer(q, "\nALTER TABLE %s CLUSTER",
6468 fmtId(tbinfo->relname));
6469 appendPQExpBuffer(q, " ON %s;\n",
6470 fmtId(indxinfo->indexname));
6474 * DROP must be fully qualified in case same name appears
6477 appendPQExpBuffer(delq, "DROP INDEX %s.",
6478 fmtId(tbinfo->relnamespace->nspname));
6479 appendPQExpBuffer(delq, "%s;\n",
6480 fmtId(indxinfo->indexname));
6482 ArchiveEntry(fout, indxinfo->dobj.catId, indxinfo->dobj.dumpId,
6483 indxinfo->indexname,
6484 tbinfo->relnamespace->nspname,
6486 "INDEX", q->data, delq->data, NULL,
6487 indxinfo->dobj.dependencies, indxinfo->dobj.nDeps,
6491 /* Dump Index Comments */
6492 resetPQExpBuffer(q);
6493 appendPQExpBuffer(q, "INDEX %s",
6494 fmtId(indxinfo->indexname));
6495 dumpComment(fout, q->data,
6496 tbinfo->relnamespace->nspname,
6498 indxinfo->dobj.catId, 0, indxinfo->dobj.dumpId);
6500 destroyPQExpBuffer(q);
6501 destroyPQExpBuffer(delq);
6506 * write out to fout a user-defined constraint
6509 dumpConstraint(Archive *fout, ConstraintInfo *coninfo)
6511 TableInfo *tbinfo = coninfo->contable;
6517 if (tbinfo && !tbinfo->dump)
6520 q = createPQExpBuffer();
6521 delq = createPQExpBuffer();
6523 if (coninfo->contype == 'p' || coninfo->contype == 'u')
6525 /* Index-related constraint */
6529 indxinfo = (IndxInfo *) findObjectByDumpId(coninfo->conindex);
6531 if (indxinfo == NULL)
6533 write_msg(NULL, "missing index for constraint %s\n",
6538 appendPQExpBuffer(q, "ALTER TABLE ONLY %s\n",
6539 fmtId(tbinfo->relname));
6540 appendPQExpBuffer(q, " ADD CONSTRAINT %s %s (",
6541 fmtId(coninfo->conname),
6542 coninfo->contype == 'p' ? "PRIMARY KEY" : "UNIQUE");
6544 for (k = 0; k < indxinfo->indnkeys; k++)
6546 int indkey = (int) indxinfo->indkeys[k];
6547 const char *attname;
6549 if (indkey == InvalidAttrNumber)
6551 attname = getAttrName(indkey, tbinfo);
6553 appendPQExpBuffer(q, "%s%s",
6554 (k == 0) ? "" : ", ",
6558 appendPQExpBuffer(q, ");\n");
6560 /* If the index is clustered, we need to record that. */
6561 if (indxinfo->indisclustered)
6563 appendPQExpBuffer(q, "\nALTER TABLE %s CLUSTER",
6564 fmtId(tbinfo->relname));
6565 appendPQExpBuffer(q, " ON %s;\n",
6566 fmtId(indxinfo->indexname));
6570 * DROP must be fully qualified in case same name appears
6573 appendPQExpBuffer(delq, "ALTER TABLE ONLY %s.",
6574 fmtId(tbinfo->relnamespace->nspname));
6575 appendPQExpBuffer(delq, "%s ",
6576 fmtId(tbinfo->relname));
6577 appendPQExpBuffer(delq, "DROP CONSTRAINT %s;\n",
6578 fmtId(coninfo->conname));
6580 ArchiveEntry(fout, coninfo->dobj.catId, coninfo->dobj.dumpId,
6582 tbinfo->relnamespace->nspname,
6584 "CONSTRAINT", q->data, delq->data, NULL,
6585 coninfo->dobj.dependencies, coninfo->dobj.nDeps,
6588 else if (coninfo->contype == 'f')
6591 * XXX Potentially wrap in a 'SET CONSTRAINTS OFF' block so that
6592 * the current table data is not processed
6594 appendPQExpBuffer(q, "ALTER TABLE ONLY %s\n",
6595 fmtId(tbinfo->relname));
6596 appendPQExpBuffer(q, " ADD CONSTRAINT %s %s;\n",
6597 fmtId(coninfo->conname),
6601 * DROP must be fully qualified in case same name appears in
6604 appendPQExpBuffer(delq, "ALTER TABLE ONLY %s.",
6605 fmtId(tbinfo->relnamespace->nspname));
6606 appendPQExpBuffer(delq, "%s ",
6607 fmtId(tbinfo->relname));
6608 appendPQExpBuffer(delq, "DROP CONSTRAINT %s;\n",
6609 fmtId(coninfo->conname));
6611 ArchiveEntry(fout, coninfo->dobj.catId, coninfo->dobj.dumpId,
6613 tbinfo->relnamespace->nspname,
6615 "FK CONSTRAINT", q->data, delq->data, NULL,
6616 coninfo->dobj.dependencies, coninfo->dobj.nDeps,
6619 else if (coninfo->contype == 'c' && tbinfo)
6621 /* CHECK constraint on a table */
6623 /* Ignore if not to be dumped separately */
6624 if (coninfo->separate)
6626 /* not ONLY since we want it to propagate to children */
6627 appendPQExpBuffer(q, "ALTER TABLE %s\n",
6628 fmtId(tbinfo->relname));
6629 appendPQExpBuffer(q, " ADD CONSTRAINT %s %s;\n",
6630 fmtId(coninfo->conname),
6634 * DROP must be fully qualified in case same name appears in
6637 appendPQExpBuffer(delq, "ALTER TABLE %s.",
6638 fmtId(tbinfo->relnamespace->nspname));
6639 appendPQExpBuffer(delq, "%s ",
6640 fmtId(tbinfo->relname));
6641 appendPQExpBuffer(delq, "DROP CONSTRAINT %s;\n",
6642 fmtId(coninfo->conname));
6644 ArchiveEntry(fout, coninfo->dobj.catId, coninfo->dobj.dumpId,
6646 tbinfo->relnamespace->nspname,
6648 "CHECK CONSTRAINT", q->data, delq->data, NULL,
6649 coninfo->dobj.dependencies, coninfo->dobj.nDeps,
6653 else if (coninfo->contype == 'c' && tbinfo == NULL)
6655 /* CHECK constraint on a domain */
6656 TypeInfo *tinfo = coninfo->condomain;
6658 /* Ignore if not to be dumped separately, or if not dumping domain */
6659 if (coninfo->separate && tinfo->typnamespace->dump)
6661 appendPQExpBuffer(q, "ALTER DOMAIN %s\n",
6662 fmtId(tinfo->typname));
6663 appendPQExpBuffer(q, " ADD CONSTRAINT %s %s;\n",
6664 fmtId(coninfo->conname),
6668 * DROP must be fully qualified in case same name appears in
6671 appendPQExpBuffer(delq, "ALTER DOMAIN %s.",
6672 fmtId(tinfo->typnamespace->nspname));
6673 appendPQExpBuffer(delq, "%s ",
6674 fmtId(tinfo->typname));
6675 appendPQExpBuffer(delq, "DROP CONSTRAINT %s;\n",
6676 fmtId(coninfo->conname));
6678 ArchiveEntry(fout, coninfo->dobj.catId, coninfo->dobj.dumpId,
6680 tinfo->typnamespace->nspname,
6682 "CHECK CONSTRAINT", q->data, delq->data, NULL,
6683 coninfo->dobj.dependencies, coninfo->dobj.nDeps,
6689 write_msg(NULL, "unexpected constraint type\n");
6693 /* Dump Constraint Comments --- only works for table constraints */
6696 resetPQExpBuffer(q);
6697 appendPQExpBuffer(q, "CONSTRAINT %s ",
6698 fmtId(coninfo->conname));
6699 appendPQExpBuffer(q, "ON %s",
6700 fmtId(tbinfo->relname));
6701 dumpComment(fout, q->data,
6702 tbinfo->relnamespace->nspname,
6704 coninfo->dobj.catId, 0, coninfo->dobj.dumpId);
6707 destroyPQExpBuffer(q);
6708 destroyPQExpBuffer(delq);
6713 * find the maximum oid and generate a COPY statement to set it
6717 setMaxOid(Archive *fout)
6723 do_sql_command(g_conn,
6724 "CREATE TEMPORARY TABLE pgdump_oid (dummy integer)");
6725 res = PQexec(g_conn, "INSERT INTO pgdump_oid VALUES (0)");
6726 check_sql_result(res, g_conn, "INSERT INTO pgdump_oid VALUES (0)",
6728 max_oid = PQoidValue(res);
6731 write_msg(NULL, "inserted invalid OID\n");
6735 do_sql_command(g_conn, "DROP TABLE pgdump_oid;");
6737 write_msg(NULL, "maximum system OID is %u\n", max_oid);
6738 snprintf(sql, sizeof(sql),
6739 "CREATE TEMPORARY TABLE pgdump_oid (dummy integer);\n"
6740 "COPY pgdump_oid WITH OIDS FROM stdin;\n"
6743 "DROP TABLE pgdump_oid;\n",
6746 ArchiveEntry(fout, nilCatalogId, createDumpId(),
6747 "Max OID", NULL, "",
6748 "<Init>", sql, "", NULL,
6754 * findLastBuiltInOid -
6755 * find the last built in oid
6757 * For 7.1 and 7.2, we do this by retrieving datlastsysoid from the
6758 * pg_database entry for the current database
6761 findLastBuiltinOid_V71(const char *dbname)
6766 PQExpBuffer query = createPQExpBuffer();
6768 resetPQExpBuffer(query);
6769 appendPQExpBuffer(query, "SELECT datlastsysoid from pg_database where datname = ");
6770 appendStringLiteral(query, dbname, true);
6772 res = PQexec(g_conn, query->data);
6773 check_sql_result(res, g_conn, query->data, PGRES_TUPLES_OK);
6775 ntups = PQntuples(res);
6778 write_msg(NULL, "missing pg_database entry for this database\n");
6783 write_msg(NULL, "found more than one pg_database entry for this database\n");
6786 last_oid = atooid(PQgetvalue(res, 0, PQfnumber(res, "datlastsysoid")));
6788 destroyPQExpBuffer(query);
6793 * findLastBuiltInOid -
6794 * find the last built in oid
6796 * For 7.0, we do this by assuming that the last thing that initdb does is to
6797 * create the pg_indexes view. This sucks in general, but seeing that 7.0.x
6798 * initdb won't be changing anymore, it'll do.
6801 findLastBuiltinOid_V70(void)
6807 res = PQexec(g_conn,
6808 "SELECT oid FROM pg_class WHERE relname = 'pg_indexes'");
6809 check_sql_result(res, g_conn,
6810 "SELECT oid FROM pg_class WHERE relname = 'pg_indexes'",
6812 ntups = PQntuples(res);
6815 write_msg(NULL, "could not find entry for pg_indexes in pg_class\n");
6820 write_msg(NULL, "found more than one entry for pg_indexes in pg_class\n");
6823 last_oid = atooid(PQgetvalue(res, 0, PQfnumber(res, "oid")));
6829 dumpSequence(Archive *fout, TableInfo *tbinfo)
6841 PQExpBuffer query = createPQExpBuffer();
6842 PQExpBuffer delqry = createPQExpBuffer();
6844 /* Make sure we are in proper schema */
6845 selectSourceSchema(tbinfo->relnamespace->nspname);
6847 snprintf(bufm, sizeof(bufm), INT64_FORMAT, SEQ_MINVALUE);
6848 snprintf(bufx, sizeof(bufx), INT64_FORMAT, SEQ_MAXVALUE);
6850 appendPQExpBuffer(query,
6851 "SELECT sequence_name, last_value, increment_by, "
6852 "CASE WHEN increment_by > 0 AND max_value = %s THEN NULL "
6853 " WHEN increment_by < 0 AND max_value = -1 THEN NULL "
6855 "END AS max_value, "
6856 "CASE WHEN increment_by > 0 AND min_value = 1 THEN NULL "
6857 " WHEN increment_by < 0 AND min_value = %s THEN NULL "
6859 "END AS min_value, "
6860 "cache_value, is_cycled, is_called from %s",
6862 fmtId(tbinfo->relname));
6864 res = PQexec(g_conn, query->data);
6865 check_sql_result(res, g_conn, query->data, PGRES_TUPLES_OK);
6867 if (PQntuples(res) != 1)
6869 write_msg(NULL, "query to get data of sequence \"%s\" returned %d rows (expected 1)\n",
6870 tbinfo->relname, PQntuples(res));
6874 /* Disable this check: it fails if sequence has been renamed */
6876 if (strcmp(PQgetvalue(res, 0, 0), tbinfo->relname) != 0)
6878 write_msg(NULL, "query to get data of sequence \"%s\" returned name \"%s\"\n",
6879 tbinfo->relname, PQgetvalue(res, 0, 0));
6884 last = PQgetvalue(res, 0, 1);
6885 incby = PQgetvalue(res, 0, 2);
6886 if (!PQgetisnull(res, 0, 3))
6887 maxv = PQgetvalue(res, 0, 3);
6888 if (!PQgetisnull(res, 0, 4))
6889 minv = PQgetvalue(res, 0, 4);
6890 cache = PQgetvalue(res, 0, 5);
6891 cycled = (strcmp(PQgetvalue(res, 0, 6), "t") == 0);
6892 called = (strcmp(PQgetvalue(res, 0, 7), "t") == 0);
6895 * The logic we use for restoring sequences is as follows:
6897 * Add a basic CREATE SEQUENCE statement (use last_val for start if
6898 * called is false, else use min_val for start_val). Skip this if the
6899 * sequence came from a SERIAL column.
6901 * Add a 'SETVAL(seq, last_val, iscalled)' at restore-time iff we load
6902 * data. We do this for serial sequences too.
6905 if (!dataOnly && !OidIsValid(tbinfo->owning_tab))
6907 resetPQExpBuffer(delqry);
6910 * DROP must be fully qualified in case same name appears in
6913 appendPQExpBuffer(delqry, "DROP SEQUENCE %s.",
6914 fmtId(tbinfo->relnamespace->nspname));
6915 appendPQExpBuffer(delqry, "%s;\n",
6916 fmtId(tbinfo->relname));
6918 resetPQExpBuffer(query);
6919 appendPQExpBuffer(query,
6920 "CREATE SEQUENCE %s\n",
6921 fmtId(tbinfo->relname));
6924 appendPQExpBuffer(query, " START WITH %s\n", last);
6926 appendPQExpBuffer(query, " INCREMENT BY %s\n", incby);
6929 appendPQExpBuffer(query, " MAXVALUE %s\n", maxv);
6931 appendPQExpBuffer(query, " NO MAXVALUE\n");
6934 appendPQExpBuffer(query, " MINVALUE %s\n", minv);
6936 appendPQExpBuffer(query, " NO MINVALUE\n");
6938 appendPQExpBuffer(query,
6940 cache, (cycled ? "\n CYCLE" : ""));
6942 ArchiveEntry(fout, tbinfo->dobj.catId, tbinfo->dobj.dumpId,
6944 tbinfo->relnamespace->nspname, tbinfo->usename,
6945 "SEQUENCE", query->data, delqry->data, NULL,
6946 tbinfo->dobj.dependencies, tbinfo->dobj.nDeps,
6952 resetPQExpBuffer(query);
6953 appendPQExpBuffer(query, "SELECT pg_catalog.setval(");
6954 appendStringLiteral(query, fmtId(tbinfo->relname), true);
6955 appendPQExpBuffer(query, ", %s, %s);\n",
6956 last, (called ? "true" : "false"));
6958 ArchiveEntry(fout, nilCatalogId, createDumpId(),
6960 tbinfo->relnamespace->nspname, tbinfo->usename,
6961 "SEQUENCE SET", query->data, "", NULL,
6962 &(tbinfo->dobj.dumpId), 1,
6968 /* Dump Sequence Comments */
6969 resetPQExpBuffer(query);
6970 appendPQExpBuffer(query, "SEQUENCE %s", fmtId(tbinfo->relname));
6971 dumpComment(fout, query->data,
6972 tbinfo->relnamespace->nspname, tbinfo->usename,
6973 tbinfo->dobj.catId, 0, tbinfo->dobj.dumpId);
6978 destroyPQExpBuffer(query);
6979 destroyPQExpBuffer(delqry);
6983 dumpTrigger(Archive *fout, TriggerInfo *tginfo)
6985 TableInfo *tbinfo = tginfo->tgtable;
6994 query = createPQExpBuffer();
6995 delqry = createPQExpBuffer();
6998 * DROP must be fully qualified in case same name appears in
7001 appendPQExpBuffer(delqry, "DROP TRIGGER %s ",
7002 fmtId(tginfo->tgname));
7003 appendPQExpBuffer(delqry, "ON %s.",
7004 fmtId(tbinfo->relnamespace->nspname));
7005 appendPQExpBuffer(delqry, "%s;\n",
7006 fmtId(tbinfo->relname));
7008 if (tginfo->tgisconstraint)
7010 appendPQExpBuffer(query, "CREATE CONSTRAINT TRIGGER ");
7011 appendPQExpBuffer(query, fmtId(tginfo->tgconstrname));
7015 appendPQExpBuffer(query, "CREATE TRIGGER ");
7016 appendPQExpBuffer(query, fmtId(tginfo->tgname));
7018 appendPQExpBuffer(query, "\n ");
7022 if (TRIGGER_FOR_BEFORE(tginfo->tgtype))
7023 appendPQExpBuffer(query, "BEFORE");
7025 appendPQExpBuffer(query, "AFTER");
7026 if (TRIGGER_FOR_INSERT(tginfo->tgtype))
7028 appendPQExpBuffer(query, " INSERT");
7031 if (TRIGGER_FOR_DELETE(tginfo->tgtype))
7034 appendPQExpBuffer(query, " OR DELETE");
7036 appendPQExpBuffer(query, " DELETE");
7039 if (TRIGGER_FOR_UPDATE(tginfo->tgtype))
7042 appendPQExpBuffer(query, " OR UPDATE");
7044 appendPQExpBuffer(query, " UPDATE");
7046 appendPQExpBuffer(query, " ON %s\n",
7047 fmtId(tbinfo->relname));
7049 if (tginfo->tgisconstraint)
7051 if (OidIsValid(tginfo->tgconstrrelid))
7053 /* If we are using regclass, name is already quoted */
7054 if (g_fout->remoteVersion >= 70300)
7055 appendPQExpBuffer(query, " FROM %s\n ",
7056 tginfo->tgconstrrelname);
7058 appendPQExpBuffer(query, " FROM %s\n ",
7059 fmtId(tginfo->tgconstrrelname));
7061 if (!tginfo->tgdeferrable)
7062 appendPQExpBuffer(query, "NOT ");
7063 appendPQExpBuffer(query, "DEFERRABLE INITIALLY ");
7064 if (tginfo->tginitdeferred)
7065 appendPQExpBuffer(query, "DEFERRED\n");
7067 appendPQExpBuffer(query, "IMMEDIATE\n");
7070 if (TRIGGER_FOR_ROW(tginfo->tgtype))
7071 appendPQExpBuffer(query, " FOR EACH ROW\n ");
7073 appendPQExpBuffer(query, " FOR EACH STATEMENT\n ");
7075 /* In 7.3, result of regproc is already quoted */
7076 if (g_fout->remoteVersion >= 70300)
7077 appendPQExpBuffer(query, "EXECUTE PROCEDURE %s(",
7080 appendPQExpBuffer(query, "EXECUTE PROCEDURE %s(",
7081 fmtId(tginfo->tgfname));
7084 for (findx = 0; findx < tginfo->tgnargs; findx++)
7090 p = strchr(p, '\\');
7093 write_msg(NULL, "invalid argument string (%s) for trigger \"%s\" on table \"%s\"\n",
7105 if (p[0] == '0' && p[1] == '0' && p[2] == '0')
7109 appendPQExpBufferChar(query, '\'');
7113 appendPQExpBufferChar(query, '\\');
7114 appendPQExpBufferChar(query, *s++);
7116 appendPQExpBufferChar(query, '\'');
7117 appendPQExpBuffer(query,
7118 (findx < tginfo->tgnargs - 1) ? ", " : "");
7121 appendPQExpBuffer(query, ");\n");
7123 ArchiveEntry(fout, tginfo->dobj.catId, tginfo->dobj.dumpId,
7125 tbinfo->relnamespace->nspname,
7127 "TRIGGER", query->data, delqry->data, NULL,
7128 tginfo->dobj.dependencies, tginfo->dobj.nDeps,
7131 resetPQExpBuffer(query);
7132 appendPQExpBuffer(query, "TRIGGER %s ",
7133 fmtId(tginfo->tgname));
7134 appendPQExpBuffer(query, "ON %s",
7135 fmtId(tbinfo->relname));
7137 dumpComment(fout, query->data,
7138 tbinfo->relnamespace->nspname, tbinfo->usename,
7139 tginfo->dobj.catId, 0, tginfo->dobj.dumpId);
7141 destroyPQExpBuffer(query);
7142 destroyPQExpBuffer(delqry);
7150 dumpRule(Archive *fout, RuleInfo *rinfo)
7152 TableInfo *tbinfo = rinfo->ruletable;
7159 * Ignore rules for not-to-be-dumped tables
7161 if (tbinfo == NULL || !tbinfo->dump || dataOnly)
7165 * If it is an ON SELECT rule, we do not need to dump it because
7166 * it will be handled via CREATE VIEW for the table.
7168 if (rinfo->ev_type == '1' && rinfo->is_instead)
7172 * Make sure we are in proper schema.
7174 selectSourceSchema(tbinfo->relnamespace->nspname);
7176 query = createPQExpBuffer();
7177 cmd = createPQExpBuffer();
7178 delcmd = createPQExpBuffer();
7180 if (g_fout->remoteVersion >= 70300)
7182 appendPQExpBuffer(query,
7183 "SELECT pg_catalog.pg_get_ruledef('%u'::pg_catalog.oid) AS definition",
7184 rinfo->dobj.catId.oid);
7188 /* Rule name was unique before 7.3 ... */
7189 appendPQExpBuffer(query,
7190 "SELECT pg_get_ruledef('%s') AS definition",
7194 res = PQexec(g_conn, query->data);
7195 check_sql_result(res, g_conn, query->data, PGRES_TUPLES_OK);
7197 if (PQntuples(res) != 1)
7199 write_msg(NULL, "query to get rule \"%s\" for table \"%s\" failed: wrong number of rows returned",
7200 rinfo->rulename, tbinfo->relname);
7204 printfPQExpBuffer(cmd, "%s\n", PQgetvalue(res, 0, 0));
7207 * DROP must be fully qualified in case same name appears in
7210 appendPQExpBuffer(delcmd, "DROP RULE %s ",
7211 fmtId(rinfo->rulename));
7212 appendPQExpBuffer(delcmd, "ON %s.",
7213 fmtId(tbinfo->relnamespace->nspname));
7214 appendPQExpBuffer(delcmd, "%s;\n",
7215 fmtId(tbinfo->relname));
7217 ArchiveEntry(fout, rinfo->dobj.catId, rinfo->dobj.dumpId,
7219 tbinfo->relnamespace->nspname,
7221 "RULE", cmd->data, delcmd->data, NULL,
7222 rinfo->dobj.dependencies, rinfo->dobj.nDeps,
7225 /* Dump rule comments */
7226 resetPQExpBuffer(query);
7227 appendPQExpBuffer(query, "RULE %s",
7228 fmtId(rinfo->rulename));
7229 appendPQExpBuffer(query, " ON %s",
7230 fmtId(tbinfo->relname));
7231 dumpComment(fout, query->data,
7232 tbinfo->relnamespace->nspname,
7234 rinfo->dobj.catId, 0, rinfo->dobj.dumpId);
7238 destroyPQExpBuffer(query);
7239 destroyPQExpBuffer(cmd);
7240 destroyPQExpBuffer(delcmd);
7244 * getDependencies --- obtain available dependency data
7247 getDependencies(void)
7258 DumpableObject *dobj,
7261 /* No dependency info available before 7.3 */
7262 if (g_fout->remoteVersion < 70300)
7266 write_msg(NULL, "fetching dependency data\n");
7268 /* Make sure we are in proper schema */
7269 selectSourceSchema("pg_catalog");
7271 query = createPQExpBuffer();
7273 appendPQExpBuffer(query, "SELECT "
7274 "classid, objid, refclassid, refobjid, deptype "
7276 "WHERE deptype != 'p' "
7279 res = PQexec(g_conn, query->data);
7280 check_sql_result(res, g_conn, query->data, PGRES_TUPLES_OK);
7282 ntups = PQntuples(res);
7284 i_classid = PQfnumber(res, "classid");
7285 i_objid = PQfnumber(res, "objid");
7286 i_refclassid = PQfnumber(res, "refclassid");
7287 i_refobjid = PQfnumber(res, "refobjid");
7288 i_deptype = PQfnumber(res, "deptype");
7291 * Since we ordered the SELECT by referencing ID, we can expect that
7292 * multiple entries for the same object will appear together; this
7293 * saves on searches.
7297 for (i = 0; i < ntups; i++)
7303 objId.tableoid = atooid(PQgetvalue(res, i, i_classid));
7304 objId.oid = atooid(PQgetvalue(res, i, i_objid));
7305 refobjId.tableoid = atooid(PQgetvalue(res, i, i_refclassid));
7306 refobjId.oid = atooid(PQgetvalue(res, i, i_refobjid));
7307 deptype = *(PQgetvalue(res, i, i_deptype));
7310 dobj->catId.tableoid != objId.tableoid ||
7311 dobj->catId.oid != objId.oid)
7312 dobj = findObjectByCatalogId(objId);
7315 * Failure to find objects mentioned in pg_depend is not unexpected,
7316 * since for example we don't collect info about TOAST tables.
7321 fprintf(stderr, "no referencing object %u %u\n",
7322 objId.tableoid, objId.oid);
7327 refdobj = findObjectByCatalogId(refobjId);
7329 if (refdobj == NULL)
7332 fprintf(stderr, "no referenced object %u %u\n",
7333 refobjId.tableoid, refobjId.oid);
7338 addObjectDependency(dobj, refdobj->dumpId);
7343 destroyPQExpBuffer(query);
7348 * selectSourceSchema - make the specified schema the active search path
7349 * in the source database.
7351 * NB: pg_catalog is explicitly searched after the specified schema;
7352 * so user names are only qualified if they are cross-schema references,
7353 * and system names are only qualified if they conflict with a user name
7354 * in the current schema.
7356 * Whenever the selected schema is not pg_catalog, be careful to qualify
7357 * references to system catalogs and types in our emitted commands!
7360 selectSourceSchema(const char *schemaName)
7362 static char *curSchemaName = NULL;
7365 /* Not relevant if fetching from pre-7.3 DB */
7366 if (g_fout->remoteVersion < 70300)
7368 /* Ignore null schema names */
7369 if (schemaName == NULL || *schemaName == '\0')
7371 /* Optimize away repeated selection of same schema */
7372 if (curSchemaName && strcmp(curSchemaName, schemaName) == 0)
7375 query = createPQExpBuffer();
7376 appendPQExpBuffer(query, "SET search_path = %s",
7378 if (strcmp(schemaName, "pg_catalog") != 0)
7379 appendPQExpBuffer(query, ", pg_catalog");
7381 do_sql_command(g_conn, query->data);
7383 destroyPQExpBuffer(query);
7385 free(curSchemaName);
7386 curSchemaName = strdup(schemaName);
7390 * getFormattedTypeName - retrieve a nicely-formatted type name for the
7393 * NB: in 7.3 and up the result may depend on the currently-selected
7394 * schema; this is why we don't try to cache the names.
7397 getFormattedTypeName(Oid oid, OidOptions opts)
7406 if ((opts & zeroAsOpaque) != 0)
7407 return strdup(g_opaque_type);
7408 else if ((opts & zeroAsAny) != 0)
7409 return strdup("'any'");
7410 else if ((opts & zeroAsStar) != 0)
7412 else if ((opts & zeroAsNone) != 0)
7413 return strdup("NONE");
7416 query = createPQExpBuffer();
7417 if (g_fout->remoteVersion >= 70300)
7419 appendPQExpBuffer(query, "SELECT pg_catalog.format_type('%u'::pg_catalog.oid, NULL)",
7422 else if (g_fout->remoteVersion >= 70100)
7424 appendPQExpBuffer(query, "SELECT format_type('%u'::oid, NULL)",
7429 appendPQExpBuffer(query, "SELECT typname "
7431 "WHERE oid = '%u'::oid",
7435 res = PQexec(g_conn, query->data);
7436 check_sql_result(res, g_conn, query->data, PGRES_TUPLES_OK);
7438 /* Expecting a single result only */
7439 ntups = PQntuples(res);
7442 write_msg(NULL, "query yielded %d rows instead of one: %s\n",
7443 ntups, query->data);
7447 if (g_fout->remoteVersion >= 70100)
7449 /* already quoted */
7450 result = strdup(PQgetvalue(res, 0, 0));
7454 /* may need to quote it */
7455 result = strdup(fmtId(PQgetvalue(res, 0, 0)));
7459 destroyPQExpBuffer(query);
7465 * myFormatType --- local implementation of format_type for use with 7.0.
7468 myFormatType(const char *typname, int32 typmod)
7471 PQExpBuffer buf = createPQExpBuffer();
7473 /* Show lengths on bpchar and varchar */
7474 if (!strcmp(typname, "bpchar"))
7476 int len = (typmod - VARHDRSZ);
7478 appendPQExpBuffer(buf, "character");
7480 appendPQExpBuffer(buf, "(%d)",
7483 else if (!strcmp(typname, "varchar"))
7485 appendPQExpBuffer(buf, "character varying");
7487 appendPQExpBuffer(buf, "(%d)",
7490 else if (!strcmp(typname, "numeric"))
7492 appendPQExpBuffer(buf, "numeric");
7499 tmp_typmod = typmod - VARHDRSZ;
7500 precision = (tmp_typmod >> 16) & 0xffff;
7501 scale = tmp_typmod & 0xffff;
7502 appendPQExpBuffer(buf, "(%d,%d)",
7508 * char is an internal single-byte data type; Let's make sure we force
7509 * it through with quotes. - thomas 1998-12-13
7511 else if (strcmp(typname, "char") == 0)
7512 appendPQExpBuffer(buf, "\"char\"");
7514 appendPQExpBuffer(buf, "%s", fmtId(typname));
7516 result = strdup(buf->data);
7517 destroyPQExpBuffer(buf);
7523 * fmtQualifiedId - convert a qualified name to the proper format for
7524 * the source database.
7526 * Like fmtId, use the result before calling again.
7529 fmtQualifiedId(const char *schema, const char *id)
7531 static PQExpBuffer id_return = NULL;
7533 if (id_return) /* first time through? */
7534 resetPQExpBuffer(id_return);
7536 id_return = createPQExpBuffer();
7538 /* Suppress schema name if fetching from pre-7.3 DB */
7539 if (g_fout->remoteVersion >= 70300 && schema && *schema)
7541 appendPQExpBuffer(id_return, "%s.",
7544 appendPQExpBuffer(id_return, "%s",
7547 return id_return->data;
7551 * Return a column list clause for the given relation.
7553 * Special case: if there are no undropped columns in the relation, return
7554 * "", not an invalid "()" column list.
7557 fmtCopyColumnList(const TableInfo *ti)
7559 static PQExpBuffer q = NULL;
7560 int numatts = ti->numatts;
7561 char **attnames = ti->attnames;
7562 bool *attisdropped = ti->attisdropped;
7566 if (q) /* first time through? */
7567 resetPQExpBuffer(q);
7569 q = createPQExpBuffer();
7571 appendPQExpBuffer(q, "(");
7573 for (i = 0; i < numatts; i++)
7575 if (attisdropped[i])
7578 appendPQExpBuffer(q, ", ");
7579 appendPQExpBuffer(q, "%s", fmtId(attnames[i]));
7584 return ""; /* no undropped columns */
7586 appendPQExpBuffer(q, ")");
7591 * Convenience subroutine to execute a SQL command and check for
7592 * COMMAND_OK status.
7595 do_sql_command(PGconn *conn, const char *query)
7599 res = PQexec(conn, query);
7600 check_sql_result(res, conn, query, PGRES_COMMAND_OK);
7605 * Convenience subroutine to verify a SQL command succeeded,
7606 * and exit with a useful error message if not.
7609 check_sql_result(PGresult *res, PGconn *conn, const char *query,
7610 ExecStatusType expected)
7614 if (res && PQresultStatus(res) == expected)
7617 write_msg(NULL, "SQL command failed\n");
7619 err = PQresultErrorMessage(res);
7621 err = PQerrorMessage(conn);
7622 write_msg(NULL, "Error message from server: %s", err);
7623 write_msg(NULL, "The command was: %s\n", query);