1 /*-------------------------------------------------------------------------
4 * pg_dump is a utility for dumping out a postgres database
7 * Portions Copyright (c) 1996-2006, 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.440 2006/07/11 17:26:59 momjian Exp $
17 *-------------------------------------------------------------------------
21 * Although this is not a backend module, we must include postgres.h anyway
22 * so that we can include a bunch of backend include files. pg_dump has
23 * never pretended to be very independent of the backend anyhow ...
41 #include "getopt_long.h"
43 #ifndef HAVE_INT_OPTRESET
49 #include "access/attnum.h"
50 #include "access/htup.h"
51 #include "catalog/pg_class.h"
52 #include "catalog/pg_proc.h"
53 #include "catalog/pg_trigger.h"
54 #include "catalog/pg_type.h"
55 #include "commands/sequence.h"
57 #include "libpq/libpq-fs.h"
58 #include "mb/pg_wchar.h"
61 #include "pg_backup.h"
62 #include "pg_backup_archiver.h"
63 #include "dumputils.h"
72 const char *descr; /* comment for an object */
73 Oid classoid; /* object class (catalog OID) */
74 Oid objoid; /* object OID */
75 int objsubid; /* subobject (table column #) */
80 bool g_verbose; /* User wants verbose narration of our
82 Archive *g_fout; /* the script file */
83 PGconn *g_conn; /* the database connection */
85 /* various user-settable parameters */
86 bool dumpInserts; /* dump data using proper insert strings */
87 bool attrNames; /* put attr names into insert strings */
92 /* subquery used to convert user ID (eg, datdba) to user name */
93 static const char *username_subquery;
95 /* obsolete as of 7.3: */
96 static Oid g_last_builtin_oid; /* value of the last builtin oid */
98 static char *selectTableName = NULL; /* name of a single table to dump */
99 static char *selectSchemaName = NULL; /* name of a single schema to dump */
101 char g_opaque_type[10]; /* name for the opaque type */
103 /* placeholders for the delimiters for comments */
104 char g_comment_start[10];
105 char g_comment_end[10];
107 static const CatalogId nilCatalogId = {0, 0};
109 /* these are to avoid passing around info for findNamespace() */
110 static NamespaceInfo *g_namespaces;
111 static int g_numNamespaces;
113 /* flag to turn on/off dollar quoting */
114 static int disable_dollar_quoting = 0;
117 static void help(const char *progname);
118 static NamespaceInfo *findNamespace(Oid nsoid, Oid objoid);
119 static void dumpTableData(Archive *fout, TableDataInfo *tdinfo);
120 static void dumpComment(Archive *fout, const char *target,
121 const char *namespace, const char *owner,
122 CatalogId catalogId, int subid, DumpId dumpId);
123 static int findComments(Archive *fout, Oid classoid, Oid objoid,
124 CommentItem **items);
125 static int collectComments(Archive *fout, CommentItem **items);
126 static void dumpDumpableObject(Archive *fout, DumpableObject *dobj);
127 static void dumpNamespace(Archive *fout, NamespaceInfo *nspinfo);
128 static void dumpType(Archive *fout, TypeInfo *tinfo);
129 static void dumpBaseType(Archive *fout, TypeInfo *tinfo);
130 static void dumpDomain(Archive *fout, TypeInfo *tinfo);
131 static void dumpCompositeType(Archive *fout, TypeInfo *tinfo);
132 static void dumpShellType(Archive *fout, ShellTypeInfo *stinfo);
133 static void dumpProcLang(Archive *fout, ProcLangInfo *plang);
134 static void dumpFunc(Archive *fout, FuncInfo *finfo);
135 static void dumpCast(Archive *fout, CastInfo *cast);
136 static void dumpOpr(Archive *fout, OprInfo *oprinfo);
137 static void dumpOpclass(Archive *fout, OpclassInfo *opcinfo);
138 static void dumpConversion(Archive *fout, ConvInfo *convinfo);
139 static void dumpRule(Archive *fout, RuleInfo *rinfo);
140 static void dumpAgg(Archive *fout, AggInfo *agginfo);
141 static void dumpTrigger(Archive *fout, TriggerInfo *tginfo);
142 static void dumpTable(Archive *fout, TableInfo *tbinfo);
143 static void dumpTableSchema(Archive *fout, TableInfo *tbinfo);
144 static void dumpAttrDef(Archive *fout, AttrDefInfo *adinfo);
145 static void dumpSequence(Archive *fout, TableInfo *tbinfo);
146 static void dumpIndex(Archive *fout, IndxInfo *indxinfo);
147 static void dumpConstraint(Archive *fout, ConstraintInfo *coninfo);
148 static void dumpTableConstraintComment(Archive *fout, ConstraintInfo *coninfo);
150 static void dumpACL(Archive *fout, CatalogId objCatId, DumpId objDumpId,
151 const char *type, const char *name,
152 const char *tag, const char *nspname, const char *owner,
155 static void getDependencies(void);
156 static void getDomainConstraints(TypeInfo *tinfo);
157 static void getTableData(TableInfo *tblinfo, int numTables, bool oids);
158 static char *format_function_arguments(FuncInfo *finfo, int nallargs,
162 static char *format_function_signature(FuncInfo *finfo, bool honor_quotes);
163 static const char *convertRegProcReference(const char *proc);
164 static const char *convertOperatorReference(const char *opr);
165 static Oid findLastBuiltinOid_V71(const char *);
166 static Oid findLastBuiltinOid_V70(void);
167 static void selectSourceSchema(const char *schemaName);
168 static char *getFormattedTypeName(Oid oid, OidOptions opts);
169 static char *myFormatType(const char *typname, int32 typmod);
170 static const char *fmtQualifiedId(const char *schema, const char *id);
171 static bool hasBlobs(Archive *AH);
172 static int dumpBlobs(Archive *AH, void *arg);
173 static int dumpBlobComments(Archive *AH, void *arg);
174 static void dumpDatabase(Archive *AH);
175 static void dumpEncoding(Archive *AH);
176 static void dumpStdStrings(Archive *AH);
177 static const char *getAttrName(int attrnum, TableInfo *tblInfo);
178 static const char *fmtCopyColumnList(const TableInfo *ti);
179 static void do_sql_command(PGconn *conn, const char *query);
180 static void check_sql_result(PGresult *res, PGconn *conn, const char *query,
181 ExecStatusType expected);
185 main(int argc, char **argv)
188 const char *filename = NULL;
189 const char *format = "p";
190 const char *dbname = NULL;
191 const char *pghost = NULL;
192 const char *pgport = NULL;
193 const char *username = NULL;
194 const char *dumpencoding = NULL;
195 const char *std_strings;
199 DumpableObject **dobjs;
202 bool force_password = false;
203 int compressLevel = -1;
204 bool ignore_version = false;
207 int outputCreate = 0;
208 bool outputBlobs = true;
209 int outputNoOwner = 0;
210 static int use_setsessauth = 0;
211 static int disable_triggers = 0;
212 char *outputSuperuser = NULL;
214 RestoreOptions *ropt;
216 static struct option long_options[] = {
217 {"data-only", no_argument, NULL, 'a'},
218 {"blobs", no_argument, NULL, 'b'},
219 {"clean", no_argument, NULL, 'c'},
220 {"create", no_argument, NULL, 'C'},
221 {"file", required_argument, NULL, 'f'},
222 {"format", required_argument, NULL, 'F'},
223 {"inserts", no_argument, NULL, 'd'},
224 {"attribute-inserts", no_argument, NULL, 'D'},
225 {"column-inserts", no_argument, NULL, 'D'},
226 {"host", required_argument, NULL, 'h'},
227 {"ignore-version", no_argument, NULL, 'i'},
228 {"no-reconnect", no_argument, NULL, 'R'},
229 {"oids", no_argument, NULL, 'o'},
230 {"no-owner", no_argument, NULL, 'O'},
231 {"port", required_argument, NULL, 'p'},
232 {"schema", required_argument, NULL, 'n'},
233 {"schema-only", no_argument, NULL, 's'},
234 {"superuser", required_argument, NULL, 'S'},
235 {"table", required_argument, NULL, 't'},
236 {"password", no_argument, NULL, 'W'},
237 {"username", required_argument, NULL, 'U'},
238 {"verbose", no_argument, NULL, 'v'},
239 {"no-privileges", no_argument, NULL, 'x'},
240 {"no-acl", no_argument, NULL, 'x'},
241 {"compress", required_argument, NULL, 'Z'},
242 {"encoding", required_argument, NULL, 'E'},
243 {"help", no_argument, NULL, '?'},
244 {"version", no_argument, NULL, 'V'},
247 * the following options don't have an equivalent short option letter,
248 * but are available as '-X long-name'
250 {"disable-dollar-quoting", no_argument, &disable_dollar_quoting, 1},
251 {"disable-triggers", no_argument, &disable_triggers, 1},
252 {"use-set-session-authorization", no_argument, &use_setsessauth, 1},
258 set_pglocale_pgservice(argv[0], "pg_dump");
262 strcpy(g_comment_start, "-- ");
263 g_comment_end[0] = '\0';
264 strcpy(g_opaque_type, "opaque");
266 dataOnly = schemaOnly = dumpInserts = attrNames = false;
268 progname = get_progname(argv[0]);
270 /* Set default options based on progname */
271 if (strcmp(progname, "pg_backup") == 0)
276 if (strcmp(argv[1], "--help") == 0 || strcmp(argv[1], "-?") == 0)
281 if (strcmp(argv[1], "--version") == 0 || strcmp(argv[1], "-V") == 0)
283 puts("pg_dump (PostgreSQL) " PG_VERSION);
288 while ((c = getopt_long(argc, argv, "abcCdDE:f:F:h:in:oOp:RsS:t:uU:vWxX:Z:",
289 long_options, &optindex)) != -1)
293 case 'a': /* Dump data only */
297 case 'b': /* Dump blobs */
298 /* this is now default, so just ignore the switch */
301 case 'c': /* clean (i.e., drop) schema prior to create */
305 case 'C': /* Create DB */
309 case 'd': /* dump data as proper insert strings */
313 case 'D': /* dump data as proper insert strings with
319 case 'E': /* Dump encoding */
320 dumpencoding = optarg;
331 case 'h': /* server host */
335 case 'i': /* ignore database version mismatch */
336 ignore_version = true;
339 case 'n': /* Dump data for this schema only */
340 selectSchemaName = strdup(optarg);
343 case 'o': /* Dump oids */
347 case 'O': /* Don't reconnect to match owner */
351 case 'p': /* server port */
356 /* no-op, still accepted for backwards compatibility */
359 case 's': /* dump schema only */
364 case 'S': /* Username for superuser in plain text output */
365 outputSuperuser = strdup(optarg);
368 case 't': /* Dump data for this table only */
369 selectTableName = strdup(optarg);
373 force_password = true;
374 username = simple_prompt("User name: ", 100, true);
381 case 'v': /* verbose */
386 force_password = true;
389 case 'x': /* skip ACL dump */
394 * Option letters were getting scarce, so I invented this new
395 * scheme: '-X feature' turns on some feature. Compare to the
396 * -f option in GCC. You should also add an equivalent
397 * GNU-style option --feature. Features that require
398 * arguments should use '-X feature=foo'.
401 if (strcmp(optarg, "disable-dollar-quoting") == 0)
402 disable_dollar_quoting = 1;
403 else if (strcmp(optarg, "disable-triggers") == 0)
404 disable_triggers = 1;
405 else if (strcmp(optarg, "use-set-session-authorization") == 0)
410 _("%s: invalid -X option -- %s\n"),
412 fprintf(stderr, _("Try \"%s --help\" for more information.\n"), progname);
417 case 'Z': /* Compression Level */
418 compressLevel = atoi(optarg);
420 /* This covers the long options equivalent to -X xxx. */
426 fprintf(stderr, _("Try \"%s --help\" for more information.\n"), progname);
431 if (optind < (argc - 1))
433 fprintf(stderr, _("%s: too many command-line arguments (first is \"%s\")\n"),
434 progname, argv[optind + 1]);
435 fprintf(stderr, _("Try \"%s --help\" for more information.\n"),
440 /* Get database name from command line */
442 dbname = argv[optind];
444 if (dataOnly && schemaOnly)
446 write_msg(NULL, "options \"schema only\" (-s) and \"data only\" (-a) cannot be used together\n");
450 if (dataOnly && outputClean)
452 write_msg(NULL, "options \"clean\" (-c) and \"data only\" (-a) cannot be used together\n");
456 if (selectTableName != NULL || selectSchemaName != NULL)
459 if (dumpInserts == true && oids == true)
461 write_msg(NULL, "INSERT (-d, -D) and OID (-o) options cannot be used together\n");
462 write_msg(NULL, "(The INSERT command cannot set OIDs.)\n");
466 /* open the output file */
471 g_fout = CreateArchive(filename, archCustom, compressLevel);
476 g_fout = CreateArchive(filename, archFiles, compressLevel);
482 g_fout = CreateArchive(filename, archNull, 0);
487 g_fout = CreateArchive(filename, archTar, compressLevel);
491 write_msg(NULL, "invalid output format \"%s\" specified\n", format);
497 write_msg(NULL, "could not open output file \"%s\" for writing\n", filename);
501 /* Let the archiver know how noisy to be */
502 g_fout->verbose = g_verbose;
504 g_fout->minRemoteVersion = 70000; /* we can handle back to 7.0 */
505 g_fout->maxRemoteVersion = parse_version(PG_VERSION);
506 if (g_fout->maxRemoteVersion < 0)
508 write_msg(NULL, "could not parse version string \"%s\"\n", PG_VERSION);
513 * Open the database using the Archiver, so it knows about it. Errors mean
516 g_conn = ConnectDatabase(g_fout, dbname, pghost, pgport,
517 username, force_password, ignore_version);
519 /* Set the client encoding if requested */
522 if (PQsetClientEncoding(g_conn, dumpencoding) < 0)
524 write_msg(NULL, "invalid client encoding \"%s\" specified\n",
531 * Get the active encoding and the standard_conforming_strings setting,
532 * so we know how to escape strings.
534 g_fout->encoding = PQclientEncoding(g_conn);
536 std_strings = PQparameterStatus(g_conn, "standard_conforming_strings");
537 g_fout->std_strings = (std_strings && strcmp(std_strings, "on") == 0);
539 /* Set the datestyle to ISO to ensure the dump's portability */
540 do_sql_command(g_conn, "SET DATESTYLE = ISO");
543 * Start serializable transaction to dump consistent data.
545 do_sql_command(g_conn, "BEGIN");
547 do_sql_command(g_conn, "SET TRANSACTION ISOLATION LEVEL SERIALIZABLE");
549 /* Select the appropriate subquery to convert user IDs to names */
550 if (g_fout->remoteVersion >= 80100)
551 username_subquery = "SELECT rolname FROM pg_catalog.pg_roles WHERE oid =";
552 else if (g_fout->remoteVersion >= 70300)
553 username_subquery = "SELECT usename FROM pg_catalog.pg_user WHERE usesysid =";
555 username_subquery = "SELECT usename FROM pg_user WHERE usesysid =";
558 * If supported, set extra_float_digits so that we can dump float data
559 * exactly (given correctly implemented float I/O code, anyway)
561 if (g_fout->remoteVersion >= 70400)
562 do_sql_command(g_conn, "SET extra_float_digits TO 2");
564 /* Find the last built-in OID, if needed */
565 if (g_fout->remoteVersion < 70300)
567 if (g_fout->remoteVersion >= 70100)
568 g_last_builtin_oid = findLastBuiltinOid_V71(PQdb(g_conn));
570 g_last_builtin_oid = findLastBuiltinOid_V70();
572 write_msg(NULL, "last built-in OID is %u\n", g_last_builtin_oid);
576 * Now scan the database and create DumpableObject structs for all the
577 * objects we intend to dump.
579 tblinfo = getSchemaData(&numTables, schemaOnly, dataOnly);
582 getTableData(tblinfo, numTables, oids);
584 if (outputBlobs && hasBlobs(g_fout))
586 /* Add placeholders to allow correct sorting of blobs */
587 DumpableObject *blobobj;
589 blobobj = (DumpableObject *) malloc(sizeof(DumpableObject));
590 blobobj->objType = DO_BLOBS;
591 blobobj->catId = nilCatalogId;
592 AssignDumpId(blobobj);
593 blobobj->name = strdup("BLOBS");
595 blobobj = (DumpableObject *) malloc(sizeof(DumpableObject));
596 blobobj->objType = DO_BLOB_COMMENTS;
597 blobobj->catId = nilCatalogId;
598 AssignDumpId(blobobj);
599 blobobj->name = strdup("BLOB COMMENTS");
603 * Collect dependency data to assist in ordering the objects.
608 * Sort the objects into a safe dump order (no forward references).
610 * In 7.3 or later, we can rely on dependency information to help us
611 * determine a safe order, so the initial sort is mostly for cosmetic
612 * purposes: we sort by name to ensure that logically identical schemas
613 * will dump identically. Before 7.3 we don't have dependencies and we
614 * use OID ordering as an (unreliable) guide to creation order.
616 getDumpableObjects(&dobjs, &numObjs);
618 if (g_fout->remoteVersion >= 70300)
619 sortDumpableObjectsByTypeName(dobjs, numObjs);
621 sortDumpableObjectsByTypeOid(dobjs, numObjs);
623 sortDumpableObjects(dobjs, numObjs);
626 * Create archive TOC entries for all the objects to be dumped, in a safe
630 /* First the special ENCODING and STDSTRINGS entries. */
631 dumpEncoding(g_fout);
632 dumpStdStrings(g_fout);
634 /* The database item is always next, unless we don't want it at all */
635 if (!dataOnly && selectTableName == NULL && selectSchemaName == NULL)
636 dumpDatabase(g_fout);
638 /* Now the rearrangeable objects. */
639 for (i = 0; i < numObjs; i++)
640 dumpDumpableObject(g_fout, dobjs[i]);
643 * And finally we can do the actual output.
647 ropt = NewRestoreOptions();
648 ropt->filename = (char *) filename;
649 ropt->dropSchema = outputClean;
650 ropt->aclsSkip = aclsSkip;
651 ropt->superuser = outputSuperuser;
652 ropt->create = outputCreate;
653 ropt->noOwner = outputNoOwner;
654 ropt->disable_triggers = disable_triggers;
655 ropt->use_setsessauth = use_setsessauth;
656 ropt->dataOnly = dataOnly;
658 if (compressLevel == -1)
659 ropt->compression = 0;
661 ropt->compression = compressLevel;
663 ropt->suppressDumpWarnings = true; /* We've already shown them */
665 RestoreArchive(g_fout, ropt);
668 CloseArchive(g_fout);
677 help(const char *progname)
679 printf(_("%s dumps a database as a text file or to other formats.\n\n"), progname);
680 printf(_("Usage:\n"));
681 printf(_(" %s [OPTION]... [DBNAME]\n"), progname);
683 printf(_("\nGeneral options:\n"));
684 printf(_(" -f, --file=FILENAME output file name\n"));
685 printf(_(" -F, --format=c|t|p output file format (custom, tar, plain text)\n"));
686 printf(_(" -i, --ignore-version proceed even when server version mismatches\n"
687 " pg_dump version\n"));
688 printf(_(" -v, --verbose verbose mode\n"));
689 printf(_(" -Z, --compress=0-9 compression level for compressed formats\n"));
690 printf(_(" --help show this help, then exit\n"));
691 printf(_(" --version output version information, then exit\n"));
693 printf(_("\nOptions controlling the output content:\n"));
694 printf(_(" -a, --data-only dump only the data, not the schema\n"));
695 printf(_(" -c, --clean clean (drop) schema prior to create\n"));
696 printf(_(" -C, --create include commands to create database in dump\n"));
697 printf(_(" -d, --inserts dump data as INSERT, rather than COPY, commands\n"));
698 printf(_(" -D, --column-inserts dump data as INSERT commands with column names\n"));
699 printf(_(" -E, --encoding=ENCODING dump the data in encoding ENCODING\n"));
700 printf(_(" -n, --schema=SCHEMA dump the named schema only\n"));
701 printf(_(" -o, --oids include OIDs in dump\n"));
702 printf(_(" -O, --no-owner skip restoration of object ownership\n"
703 " in plain text format\n"));
704 printf(_(" -s, --schema-only dump only the schema, no data\n"));
705 printf(_(" -S, --superuser=NAME specify the superuser user name to use in\n"
706 " plain text format\n"));
707 printf(_(" -t, --table=TABLE dump the named table only\n"));
708 printf(_(" -x, --no-privileges do not dump privileges (grant/revoke)\n"));
709 printf(_(" -X disable-dollar-quoting, --disable-dollar-quoting\n"
710 " disable dollar quoting, use SQL standard quoting\n"));
711 printf(_(" -X disable-triggers, --disable-triggers\n"
712 " disable triggers during data-only restore\n"));
713 printf(_(" -X use-set-session-authorization, --use-set-session-authorization\n"
714 " use SESSION AUTHORIZATION commands instead of\n"
715 " OWNER TO commands\n"));
717 printf(_("\nConnection options:\n"));
718 printf(_(" -h, --host=HOSTNAME database server host or socket directory\n"));
719 printf(_(" -p, --port=PORT database server port number\n"));
720 printf(_(" -U, --username=NAME connect as specified database user\n"));
721 printf(_(" -W, --password force password prompt (should happen automatically)\n"));
723 printf(_("\nIf no database name is supplied, then the PGDATABASE environment\n"
724 "variable value is used.\n\n"));
725 printf(_("Report bugs to <pgsql-bugs@postgresql.org>.\n"));
733 write_msg(NULL, "*** aborted because of error\n");
738 * selectDumpableNamespace: policy-setting subroutine
739 * Mark a namespace as to be dumped or not
742 selectDumpableNamespace(NamespaceInfo *nsinfo)
745 * If a specific table is being dumped, do not dump any complete
746 * namespaces. If a specific namespace is being dumped, dump just that
747 * namespace. Otherwise, dump all non-system namespaces.
749 if (selectTableName != NULL)
750 nsinfo->dobj.dump = false;
751 else if (selectSchemaName != NULL)
753 if (strcmp(nsinfo->dobj.name, selectSchemaName) == 0)
754 nsinfo->dobj.dump = true;
756 nsinfo->dobj.dump = false;
758 else if (strncmp(nsinfo->dobj.name, "pg_", 3) == 0 ||
759 strcmp(nsinfo->dobj.name, "information_schema") == 0)
760 nsinfo->dobj.dump = false;
762 nsinfo->dobj.dump = true;
766 * selectDumpableTable: policy-setting subroutine
767 * Mark a table as to be dumped or not
770 selectDumpableTable(TableInfo *tbinfo)
773 * Always dump if dumping parent namespace; else, if a particular
774 * tablename has been specified, dump matching table name; else, do not
777 tbinfo->dobj.dump = false;
778 if (tbinfo->dobj.namespace->dobj.dump)
779 tbinfo->dobj.dump = true;
780 else if (selectTableName != NULL &&
781 strcmp(tbinfo->dobj.name, selectTableName) == 0)
783 /* If both -s and -t specified, must match both to dump */
784 if (selectSchemaName == NULL)
785 tbinfo->dobj.dump = true;
786 else if (strcmp(tbinfo->dobj.namespace->dobj.name, selectSchemaName) == 0)
787 tbinfo->dobj.dump = true;
792 * selectDumpableType: policy-setting subroutine
793 * Mark a type as to be dumped or not
796 selectDumpableType(TypeInfo *tinfo)
798 /* Dump only types in dumpable namespaces */
799 if (!tinfo->dobj.namespace->dobj.dump)
800 tinfo->dobj.dump = false;
802 /* skip complex types, except for standalone composite types */
803 /* (note: this test should now be unnecessary) */
804 else if (OidIsValid(tinfo->typrelid) &&
805 tinfo->typrelkind != RELKIND_COMPOSITE_TYPE)
806 tinfo->dobj.dump = false;
808 /* skip undefined placeholder types */
809 else if (!tinfo->isDefined)
810 tinfo->dobj.dump = false;
812 /* skip all array types that start w/ underscore */
813 else if ((tinfo->dobj.name[0] == '_') &&
814 OidIsValid(tinfo->typelem))
815 tinfo->dobj.dump = false;
818 tinfo->dobj.dump = true;
822 * selectDumpableObject: policy-setting subroutine
823 * Mark a generic dumpable object as to be dumped or not
825 * Use this only for object types without a special-case routine above.
828 selectDumpableObject(DumpableObject *dobj)
831 * Default policy is to dump if parent namespace is dumpable,
832 * or always for non-namespace-associated items.
835 dobj->dump = dobj->namespace->dobj.dump;
841 * Dump a table's contents for loading using the COPY command
842 * - this routine is called by the Archiver when it wants the table
847 dumpTableData_copy(Archive *fout, void *dcontext)
849 TableDataInfo *tdinfo = (TableDataInfo *) dcontext;
850 TableInfo *tbinfo = tdinfo->tdtable;
851 const char *classname = tbinfo->dobj.name;
852 const bool hasoids = tbinfo->hasoids;
853 const bool oids = tdinfo->oids;
854 PQExpBuffer q = createPQExpBuffer();
858 const char *column_list;
861 write_msg(NULL, "dumping contents of table %s\n", classname);
864 * Make sure we are in proper schema. We will qualify the table name
865 * below anyway (in case its name conflicts with a pg_catalog table); but
866 * this ensures reproducible results in case the table contains regproc,
867 * regclass, etc columns.
869 selectSourceSchema(tbinfo->dobj.namespace->dobj.name);
872 * If possible, specify the column list explicitly so that we have no
873 * possibility of retrieving data in the wrong column order. (The default
874 * column ordering of COPY will not be what we want in certain corner
875 * cases involving ADD COLUMN and inheritance.)
877 if (g_fout->remoteVersion >= 70300)
878 column_list = fmtCopyColumnList(tbinfo);
880 column_list = ""; /* can't select columns in COPY */
884 appendPQExpBuffer(q, "COPY %s %s WITH OIDS TO stdout;",
885 fmtQualifiedId(tbinfo->dobj.namespace->dobj.name,
891 appendPQExpBuffer(q, "COPY %s %s TO stdout;",
892 fmtQualifiedId(tbinfo->dobj.namespace->dobj.name,
896 res = PQexec(g_conn, q->data);
897 check_sql_result(res, g_conn, q->data, PGRES_COPY_OUT);
902 ret = PQgetCopyData(g_conn, ©buf, 0);
905 break; /* done or error */
909 WriteData(fout, copybuf, ret);
916 * There was considerable discussion in late July, 2000 regarding
917 * slowing down pg_dump when backing up large tables. Users with both
918 * slow & fast (multi-processor) machines experienced performance
919 * degradation when doing a backup.
921 * Initial attempts based on sleeping for a number of ms for each ms
922 * of work were deemed too complex, then a simple 'sleep in each loop'
923 * implementation was suggested. The latter failed because the loop
924 * was too tight. Finally, the following was implemented:
926 * If throttle is non-zero, then See how long since the last sleep.
927 * Work out how long to sleep (based on ratio). If sleep is more than
928 * 100ms, then sleep reset timer EndIf EndIf
930 * where the throttle value was the number of ms to sleep per ms of
931 * work. The calculation was done in each loop.
933 * Most of the hard work is done in the backend, and this solution
934 * still did not work particularly well: on slow machines, the ratio
935 * was 50:1, and on medium paced machines, 1:1, and on fast
936 * multi-processor machines, it had little or no effect, for reasons
939 * Further discussion ensued, and the proposal was dropped.
941 * For those people who want this feature, it can be implemented using
942 * gettimeofday in each loop, calculating the time since last sleep,
943 * multiplying that by the sleep ratio, then if the result is more
944 * than a preset 'minimum sleep time' (say 100ms), call the 'select'
945 * function to sleep for a subsecond period ie.
947 * select(0, NULL, NULL, NULL, &tvi);
949 * This will return after the interval specified in the structure tvi.
950 * Finally, call gettimeofday again to save the 'last sleep time'.
953 archprintf(fout, "\\.\n\n\n");
957 /* copy data transfer failed */
958 write_msg(NULL, "Dumping the contents of table \"%s\" failed: PQgetCopyData() failed.\n", classname);
959 write_msg(NULL, "Error message from server: %s", PQerrorMessage(g_conn));
960 write_msg(NULL, "The command was: %s\n", q->data);
964 /* Check command status and return to normal libpq state */
965 res = PQgetResult(g_conn);
966 check_sql_result(res, g_conn, q->data, PGRES_COMMAND_OK);
969 destroyPQExpBuffer(q);
974 dumpTableData_insert(Archive *fout, void *dcontext)
976 TableDataInfo *tdinfo = (TableDataInfo *) dcontext;
977 TableInfo *tbinfo = tdinfo->tdtable;
978 const char *classname = tbinfo->dobj.name;
979 PQExpBuffer q = createPQExpBuffer();
986 * Make sure we are in proper schema. We will qualify the table name
987 * below anyway (in case its name conflicts with a pg_catalog table); but
988 * this ensures reproducible results in case the table contains regproc,
989 * regclass, etc columns.
991 selectSourceSchema(tbinfo->dobj.namespace->dobj.name);
993 if (fout->remoteVersion >= 70100)
995 appendPQExpBuffer(q, "DECLARE _pg_dump_cursor CURSOR FOR "
996 "SELECT * FROM ONLY %s",
997 fmtQualifiedId(tbinfo->dobj.namespace->dobj.name,
1002 appendPQExpBuffer(q, "DECLARE _pg_dump_cursor CURSOR FOR "
1004 fmtQualifiedId(tbinfo->dobj.namespace->dobj.name,
1008 res = PQexec(g_conn, q->data);
1009 check_sql_result(res, g_conn, q->data, PGRES_COMMAND_OK);
1015 res = PQexec(g_conn, "FETCH 100 FROM _pg_dump_cursor");
1016 check_sql_result(res, g_conn, "FETCH 100 FROM _pg_dump_cursor",
1018 nfields = PQnfields(res);
1019 for (tuple = 0; tuple < PQntuples(res); tuple++)
1021 archprintf(fout, "INSERT INTO %s ", fmtId(classname));
1024 /* corner case for zero-column table */
1025 archprintf(fout, "DEFAULT VALUES;\n");
1028 if (attrNames == true)
1030 resetPQExpBuffer(q);
1031 appendPQExpBuffer(q, "(");
1032 for (field = 0; field < nfields; field++)
1035 appendPQExpBuffer(q, ", ");
1036 appendPQExpBufferStr(q, fmtId(PQfname(res, field)));
1038 appendPQExpBuffer(q, ") ");
1039 archputs(q->data, fout);
1041 archprintf(fout, "VALUES (");
1042 for (field = 0; field < nfields; field++)
1045 archprintf(fout, ", ");
1046 if (PQgetisnull(res, tuple, field))
1048 archprintf(fout, "NULL");
1052 /* XXX This code is partially duplicated in ruleutils.c */
1053 switch (PQftype(res, field))
1064 * These types are printed without quotes unless
1065 * they contain values that aren't accepted by the
1066 * scanner unquoted (e.g., 'NaN'). Note that
1067 * strtod() and friends might accept NaN, so we
1068 * can't use that to test.
1070 * In reality we only need to defend against
1071 * infinity and NaN, so we need not get too crazy
1072 * about pattern matching here.
1074 const char *s = PQgetvalue(res, tuple, field);
1076 if (strspn(s, "0123456789 +-eE.") == strlen(s))
1077 archprintf(fout, "%s", s);
1079 archprintf(fout, "'%s'", s);
1085 archprintf(fout, "B'%s'",
1086 PQgetvalue(res, tuple, field));
1090 if (strcmp(PQgetvalue(res, tuple, field), "t") == 0)
1091 archprintf(fout, "true");
1093 archprintf(fout, "false");
1097 /* All other types are printed as string literals. */
1098 resetPQExpBuffer(q);
1099 appendStringLiteralAH(q,
1100 PQgetvalue(res, tuple, field),
1102 archputs(q->data, fout);
1106 archprintf(fout, ");\n");
1108 } while (PQntuples(res) > 0);
1112 archprintf(fout, "\n\n");
1114 do_sql_command(g_conn, "CLOSE _pg_dump_cursor");
1116 destroyPQExpBuffer(q);
1123 * dump the contents of a single table
1125 * Actually, this just makes an ArchiveEntry for the table contents.
1128 dumpTableData(Archive *fout, TableDataInfo *tdinfo)
1130 TableInfo *tbinfo = tdinfo->tdtable;
1131 PQExpBuffer copyBuf = createPQExpBuffer();
1132 DataDumperPtr dumpFn;
1137 /* Dump/restore using COPY */
1138 dumpFn = dumpTableData_copy;
1139 /* must use 2 steps here 'cause fmtId is nonreentrant */
1140 appendPQExpBuffer(copyBuf, "COPY %s ",
1141 fmtId(tbinfo->dobj.name));
1142 appendPQExpBuffer(copyBuf, "%s %sFROM stdin;\n",
1143 fmtCopyColumnList(tbinfo),
1144 (tdinfo->oids && tbinfo->hasoids) ? "WITH OIDS " : "");
1145 copyStmt = copyBuf->data;
1149 /* Restore using INSERT */
1150 dumpFn = dumpTableData_insert;
1154 ArchiveEntry(fout, tdinfo->dobj.catId, tdinfo->dobj.dumpId,
1156 tbinfo->dobj.namespace->dobj.name,
1158 tbinfo->rolname, false,
1159 "TABLE DATA", "", "", copyStmt,
1160 tdinfo->dobj.dependencies, tdinfo->dobj.nDeps,
1163 destroyPQExpBuffer(copyBuf);
1168 * set up dumpable objects representing the contents of tables
1171 getTableData(TableInfo *tblinfo, int numTables, bool oids)
1175 for (i = 0; i < numTables; i++)
1177 /* Skip VIEWs (no data to dump) */
1178 if (tblinfo[i].relkind == RELKIND_VIEW)
1180 /* Skip SEQUENCEs (handled elsewhere) */
1181 if (tblinfo[i].relkind == RELKIND_SEQUENCE)
1184 if (tblinfo[i].dobj.dump)
1186 TableDataInfo *tdinfo;
1188 tdinfo = (TableDataInfo *) malloc(sizeof(TableDataInfo));
1190 tdinfo->dobj.objType = DO_TABLE_DATA;
1193 * Note: use tableoid 0 so that this object won't be mistaken for
1194 * something that pg_depend entries apply to.
1196 tdinfo->dobj.catId.tableoid = 0;
1197 tdinfo->dobj.catId.oid = tblinfo[i].dobj.catId.oid;
1198 AssignDumpId(&tdinfo->dobj);
1199 tdinfo->dobj.name = tblinfo[i].dobj.name;
1200 tdinfo->dobj.namespace = tblinfo[i].dobj.namespace;
1201 tdinfo->tdtable = &(tblinfo[i]);
1202 tdinfo->oids = oids;
1203 addObjectDependency(&tdinfo->dobj, tblinfo[i].dobj.dumpId);
1211 * dump the database definition
1214 dumpDatabase(Archive *AH)
1216 PQExpBuffer dbQry = createPQExpBuffer();
1217 PQExpBuffer delQry = createPQExpBuffer();
1218 PQExpBuffer creaQry = createPQExpBuffer();
1228 const char *datname,
1233 datname = PQdb(g_conn);
1236 write_msg(NULL, "saving database definition\n");
1238 /* Make sure we are in proper schema */
1239 selectSourceSchema("pg_catalog");
1241 /* Get the database owner and parameters from pg_database */
1242 if (g_fout->remoteVersion >= 80200)
1244 appendPQExpBuffer(dbQry, "SELECT tableoid, oid, "
1245 "(%s datdba) as dba, "
1246 "pg_encoding_to_char(encoding) as encoding, "
1247 "(SELECT spcname FROM pg_tablespace t WHERE t.oid = dattablespace) as tablespace, "
1248 "shobj_description(oid, 'pg_database') as description "
1253 appendStringLiteralAH(dbQry, datname, AH);
1255 else if (g_fout->remoteVersion >= 80000)
1257 appendPQExpBuffer(dbQry, "SELECT tableoid, oid, "
1258 "(%s datdba) as dba, "
1259 "pg_encoding_to_char(encoding) as encoding, "
1260 "(SELECT spcname FROM pg_tablespace t WHERE t.oid = dattablespace) as tablespace "
1264 appendStringLiteralAH(dbQry, datname, AH);
1266 else if (g_fout->remoteVersion >= 70100)
1268 appendPQExpBuffer(dbQry, "SELECT tableoid, oid, "
1269 "(%s datdba) as dba, "
1270 "pg_encoding_to_char(encoding) as encoding, "
1271 "NULL as tablespace "
1275 appendStringLiteralAH(dbQry, datname, AH);
1279 appendPQExpBuffer(dbQry, "SELECT "
1280 "(SELECT oid FROM pg_class WHERE relname = 'pg_database') AS tableoid, "
1282 "(%s datdba) as dba, "
1283 "pg_encoding_to_char(encoding) as encoding, "
1284 "NULL as tablespace "
1288 appendStringLiteralAH(dbQry, datname, AH);
1291 res = PQexec(g_conn, dbQry->data);
1292 check_sql_result(res, g_conn, dbQry->data, PGRES_TUPLES_OK);
1294 ntups = PQntuples(res);
1298 write_msg(NULL, "missing pg_database entry for database \"%s\"\n",
1305 write_msg(NULL, "query returned more than one (%d) pg_database entry for database \"%s\"\n",
1310 i_tableoid = PQfnumber(res, "tableoid");
1311 i_oid = PQfnumber(res, "oid");
1312 i_dba = PQfnumber(res, "dba");
1313 i_encoding = PQfnumber(res, "encoding");
1314 i_tablespace = PQfnumber(res, "tablespace");
1316 dbCatId.tableoid = atooid(PQgetvalue(res, 0, i_tableoid));
1317 dbCatId.oid = atooid(PQgetvalue(res, 0, i_oid));
1318 dba = PQgetvalue(res, 0, i_dba);
1319 encoding = PQgetvalue(res, 0, i_encoding);
1320 tablespace = PQgetvalue(res, 0, i_tablespace);
1322 appendPQExpBuffer(creaQry, "CREATE DATABASE %s WITH TEMPLATE = template0",
1324 if (strlen(encoding) > 0)
1326 appendPQExpBuffer(creaQry, " ENCODING = ");
1327 appendStringLiteralAH(creaQry, encoding, AH);
1329 if (strlen(tablespace) > 0 && strcmp(tablespace, "pg_default") != 0)
1330 appendPQExpBuffer(creaQry, " TABLESPACE = %s",
1332 appendPQExpBuffer(creaQry, ";\n");
1334 appendPQExpBuffer(delQry, "DROP DATABASE %s;\n",
1337 dbDumpId = createDumpId();
1340 dbCatId, /* catalog ID */
1341 dbDumpId, /* dump ID */
1343 NULL, /* Namespace */
1344 NULL, /* Tablespace */
1346 false, /* with oids */
1347 "DATABASE", /* Desc */
1348 creaQry->data, /* Create */
1349 delQry->data, /* Del */
1354 NULL); /* Dumper Arg */
1356 /* Dump DB comment if any */
1357 if (g_fout->remoteVersion >= 80200)
1359 /* 8.2 keeps comments on shared objects in a shared table, so
1360 * we cannot use the dumpComment used for other database objects.
1362 char *comment = PQgetvalue(res, 0, PQfnumber(res, "description"));
1363 if (comment && strlen(comment)) {
1364 resetPQExpBuffer(dbQry);
1365 appendPQExpBuffer(dbQry, "COMMENT ON DATABASE %s IS ", fmtId(datname));
1366 appendStringLiteralAH(dbQry, comment, AH);
1367 appendPQExpBuffer(dbQry, ";\n");
1369 ArchiveEntry(AH, dbCatId, createDumpId(), datname, NULL, NULL,
1370 dba, false, "COMMENT", dbQry->data, "", NULL,
1371 &dbDumpId, 1, NULL, NULL);
1374 resetPQExpBuffer(dbQry);
1375 appendPQExpBuffer(dbQry, "DATABASE %s", fmtId(datname));
1376 dumpComment(AH, dbQry->data, NULL, "",
1377 dbCatId, 0, dbDumpId);
1382 destroyPQExpBuffer(dbQry);
1383 destroyPQExpBuffer(delQry);
1384 destroyPQExpBuffer(creaQry);
1389 * dumpEncoding: put the correct encoding into the archive
1392 dumpEncoding(Archive *AH)
1394 const char *encname = pg_encoding_to_char(AH->encoding);
1395 PQExpBuffer qry = createPQExpBuffer();
1398 write_msg(NULL, "saving encoding = %s\n", encname);
1400 appendPQExpBuffer(qry, "SET client_encoding = ");
1401 appendStringLiteralAH(qry, encname, AH);
1402 appendPQExpBuffer(qry, ";\n");
1404 ArchiveEntry(AH, nilCatalogId, createDumpId(),
1405 "ENCODING", NULL, NULL, "",
1406 false, "ENCODING", qry->data, "", NULL,
1410 destroyPQExpBuffer(qry);
1415 * dumpStdStrings: put the correct escape string behavior into the archive
1418 dumpStdStrings(Archive *AH)
1420 const char *stdstrings = AH->std_strings ? "on" : "off";
1421 PQExpBuffer qry = createPQExpBuffer();
1424 write_msg(NULL, "saving standard_conforming_strings = %s\n",
1427 appendPQExpBuffer(qry, "SET standard_conforming_strings = '%s';\n",
1430 ArchiveEntry(AH, nilCatalogId, createDumpId(),
1431 "STDSTRINGS", NULL, NULL, "",
1432 false, "STDSTRINGS", qry->data, "", NULL,
1436 destroyPQExpBuffer(qry);
1442 * Test whether database contains any large objects
1445 hasBlobs(Archive *AH)
1448 const char *blobQry;
1451 /* Make sure we are in proper schema */
1452 selectSourceSchema("pg_catalog");
1454 /* Check for BLOB OIDs */
1455 if (AH->remoteVersion >= 70100)
1456 blobQry = "SELECT loid FROM pg_largeobject LIMIT 1";
1458 blobQry = "SELECT oid FROM pg_class WHERE relkind = 'l' LIMIT 1";
1460 res = PQexec(g_conn, blobQry);
1461 check_sql_result(res, g_conn, blobQry, PGRES_TUPLES_OK);
1463 result = PQntuples(res) > 0;
1475 dumpBlobs(Archive *AH, void *arg)
1477 const char *blobQry;
1478 const char *blobFetchQry;
1480 char buf[LOBBUFSIZE];
1485 write_msg(NULL, "saving large objects\n");
1487 /* Make sure we are in proper schema */
1488 selectSourceSchema("pg_catalog");
1490 /* Cursor to get all BLOB OIDs */
1491 if (AH->remoteVersion >= 70100)
1492 blobQry = "DECLARE bloboid CURSOR FOR SELECT DISTINCT loid FROM pg_largeobject";
1494 blobQry = "DECLARE bloboid CURSOR FOR SELECT oid FROM pg_class WHERE relkind = 'l'";
1496 res = PQexec(g_conn, blobQry);
1497 check_sql_result(res, g_conn, blobQry, PGRES_COMMAND_OK);
1499 /* Command to fetch from cursor */
1500 blobFetchQry = "FETCH 1000 IN bloboid";
1507 res = PQexec(g_conn, blobFetchQry);
1508 check_sql_result(res, g_conn, blobFetchQry, PGRES_TUPLES_OK);
1510 /* Process the tuples, if any */
1511 for (i = 0; i < PQntuples(res); i++)
1516 blobOid = atooid(PQgetvalue(res, i, 0));
1518 loFd = lo_open(g_conn, blobOid, INV_READ);
1521 write_msg(NULL, "dumpBlobs(): could not open large object: %s",
1522 PQerrorMessage(g_conn));
1526 StartBlob(AH, blobOid);
1528 /* Now read it in chunks, sending data to archive */
1531 cnt = lo_read(g_conn, loFd, buf, LOBBUFSIZE);
1534 write_msg(NULL, "dumpBlobs(): error reading large object: %s",
1535 PQerrorMessage(g_conn));
1539 WriteData(AH, buf, cnt);
1542 lo_close(g_conn, loFd);
1544 EndBlob(AH, blobOid);
1546 } while (PQntuples(res) > 0);
1555 * dump all blob comments
1557 * Since we don't provide any way to be selective about dumping blobs,
1558 * there's no need to be selective about their comments either. We put
1559 * all the comments into one big TOC entry.
1562 dumpBlobComments(Archive *AH, void *arg)
1564 const char *blobQry;
1565 const char *blobFetchQry;
1566 PQExpBuffer commentcmd = createPQExpBuffer();
1571 write_msg(NULL, "saving large object comments\n");
1573 /* Make sure we are in proper schema */
1574 selectSourceSchema("pg_catalog");
1576 /* Cursor to get all BLOB comments */
1577 if (AH->remoteVersion >= 70200)
1578 blobQry = "DECLARE blobcmt CURSOR FOR SELECT loid, obj_description(loid, 'pg_largeobject') FROM (SELECT DISTINCT loid FROM pg_largeobject) ss";
1579 else if (AH->remoteVersion >= 70100)
1580 blobQry = "DECLARE blobcmt CURSOR FOR SELECT loid, obj_description(loid) FROM (SELECT DISTINCT loid FROM pg_largeobject) ss";
1582 blobQry = "DECLARE blobcmt CURSOR FOR SELECT oid, (SELECT description FROM pg_description pd WHERE pd.objoid=pc.oid) FROM pg_class pc WHERE relkind = 'l'";
1584 res = PQexec(g_conn, blobQry);
1585 check_sql_result(res, g_conn, blobQry, PGRES_COMMAND_OK);
1587 /* Command to fetch from cursor */
1588 blobFetchQry = "FETCH 100 IN blobcmt";
1595 res = PQexec(g_conn, blobFetchQry);
1596 check_sql_result(res, g_conn, blobFetchQry, PGRES_TUPLES_OK);
1598 /* Process the tuples, if any */
1599 for (i = 0; i < PQntuples(res); i++)
1604 /* ignore blobs without comments */
1605 if (PQgetisnull(res, i, 1))
1608 blobOid = atooid(PQgetvalue(res, i, 0));
1609 comment = PQgetvalue(res, i, 1);
1611 printfPQExpBuffer(commentcmd, "COMMENT ON LARGE OBJECT %u IS ",
1613 appendStringLiteralAH(commentcmd, comment, AH);
1614 appendPQExpBuffer(commentcmd, ";\n");
1616 archputs(commentcmd->data, AH);
1618 } while (PQntuples(res) > 0);
1624 destroyPQExpBuffer(commentcmd);
1631 * read all namespaces in the system catalogs and return them in the
1632 * NamespaceInfo* structure
1634 * numNamespaces is set to the number of namespaces read in
1637 getNamespaces(int *numNamespaces)
1643 NamespaceInfo *nsinfo;
1651 * Before 7.3, there are no real namespaces; create two dummy entries, one
1652 * for user stuff and one for system stuff.
1654 if (g_fout->remoteVersion < 70300)
1656 nsinfo = (NamespaceInfo *) malloc(2 * sizeof(NamespaceInfo));
1658 nsinfo[0].dobj.objType = DO_NAMESPACE;
1659 nsinfo[0].dobj.catId.tableoid = 0;
1660 nsinfo[0].dobj.catId.oid = 0;
1661 AssignDumpId(&nsinfo[0].dobj);
1662 nsinfo[0].dobj.name = strdup("public");
1663 nsinfo[0].rolname = strdup("");
1664 nsinfo[0].nspacl = strdup("");
1666 selectDumpableNamespace(&nsinfo[0]);
1668 nsinfo[1].dobj.objType = DO_NAMESPACE;
1669 nsinfo[1].dobj.catId.tableoid = 0;
1670 nsinfo[1].dobj.catId.oid = 1;
1671 AssignDumpId(&nsinfo[1].dobj);
1672 nsinfo[1].dobj.name = strdup("pg_catalog");
1673 nsinfo[1].rolname = strdup("");
1674 nsinfo[1].nspacl = strdup("");
1676 selectDumpableNamespace(&nsinfo[1]);
1678 g_namespaces = nsinfo;
1679 g_numNamespaces = *numNamespaces = 2;
1684 query = createPQExpBuffer();
1686 /* Make sure we are in proper schema */
1687 selectSourceSchema("pg_catalog");
1690 * we fetch all namespaces including system ones, so that every object we
1691 * read in can be linked to a containing namespace.
1693 appendPQExpBuffer(query, "SELECT tableoid, oid, nspname, "
1694 "(%s nspowner) as rolname, "
1695 "nspacl FROM pg_namespace",
1698 res = PQexec(g_conn, query->data);
1699 check_sql_result(res, g_conn, query->data, PGRES_TUPLES_OK);
1701 ntups = PQntuples(res);
1703 nsinfo = (NamespaceInfo *) malloc(ntups * sizeof(NamespaceInfo));
1705 i_tableoid = PQfnumber(res, "tableoid");
1706 i_oid = PQfnumber(res, "oid");
1707 i_nspname = PQfnumber(res, "nspname");
1708 i_rolname = PQfnumber(res, "rolname");
1709 i_nspacl = PQfnumber(res, "nspacl");
1711 for (i = 0; i < ntups; i++)
1713 nsinfo[i].dobj.objType = DO_NAMESPACE;
1714 nsinfo[i].dobj.catId.tableoid = atooid(PQgetvalue(res, i, i_tableoid));
1715 nsinfo[i].dobj.catId.oid = atooid(PQgetvalue(res, i, i_oid));
1716 AssignDumpId(&nsinfo[i].dobj);
1717 nsinfo[i].dobj.name = strdup(PQgetvalue(res, i, i_nspname));
1718 nsinfo[i].rolname = strdup(PQgetvalue(res, i, i_rolname));
1719 nsinfo[i].nspacl = strdup(PQgetvalue(res, i, i_nspacl));
1721 /* Decide whether to dump this namespace */
1722 selectDumpableNamespace(&nsinfo[i]);
1724 if (strlen(nsinfo[i].rolname) == 0)
1725 write_msg(NULL, "WARNING: owner of schema \"%s\" appears to be invalid\n",
1726 nsinfo[i].dobj.name);
1730 * If the user attempted to dump a specific namespace, check to ensure
1731 * that the specified namespace actually exists.
1733 if (selectSchemaName)
1735 for (i = 0; i < ntups; i++)
1736 if (strcmp(nsinfo[i].dobj.name, selectSchemaName) == 0)
1739 /* Didn't find a match */
1742 write_msg(NULL, "specified schema \"%s\" does not exist\n",
1749 destroyPQExpBuffer(query);
1751 g_namespaces = nsinfo;
1752 g_numNamespaces = *numNamespaces = ntups;
1759 * given a namespace OID and an object OID, look up the info read by
1762 * NB: for pre-7.3 source database, we use object OID to guess whether it's
1763 * a system object or not. In 7.3 and later there is no guessing.
1765 static NamespaceInfo *
1766 findNamespace(Oid nsoid, Oid objoid)
1770 if (g_fout->remoteVersion >= 70300)
1772 for (i = 0; i < g_numNamespaces; i++)
1774 NamespaceInfo *nsinfo = &g_namespaces[i];
1776 if (nsoid == nsinfo->dobj.catId.oid)
1779 write_msg(NULL, "schema with OID %u does not exist\n", nsoid);
1784 /* This code depends on the layout set up by getNamespaces. */
1785 if (objoid > g_last_builtin_oid)
1786 i = 0; /* user object */
1788 i = 1; /* system object */
1789 return &g_namespaces[i];
1792 return NULL; /* keep compiler quiet */
1797 * read all types in the system catalogs and return them in the
1798 * TypeInfo* structure
1800 * numTypes is set to the number of types read in
1802 * NB: this must run after getFuncs() because we assume we can do
1806 getTypes(int *numTypes)
1811 PQExpBuffer query = createPQExpBuffer();
1813 ShellTypeInfo *stinfo;
1828 * we include even the built-in types because those may be used as array
1829 * elements by user-defined types
1831 * we filter out the built-in types when we dump out the types
1833 * same approach for undefined (shell) types
1836 /* Make sure we are in proper schema */
1837 selectSourceSchema("pg_catalog");
1839 if (g_fout->remoteVersion >= 70300)
1841 appendPQExpBuffer(query, "SELECT tableoid, oid, typname, "
1843 "(%s typowner) as rolname, "
1844 "typinput::oid as typinput, "
1845 "typoutput::oid as typoutput, typelem, typrelid, "
1846 "CASE WHEN typrelid = 0 THEN ' '::\"char\" "
1847 "ELSE (SELECT relkind FROM pg_class WHERE oid = typrelid) END as typrelkind, "
1848 "typtype, typisdefined "
1852 else if (g_fout->remoteVersion >= 70100)
1854 appendPQExpBuffer(query, "SELECT tableoid, oid, typname, "
1855 "0::oid as typnamespace, "
1856 "(%s typowner) as rolname, "
1857 "typinput::oid as typinput, "
1858 "typoutput::oid as typoutput, typelem, typrelid, "
1859 "CASE WHEN typrelid = 0 THEN ' '::\"char\" "
1860 "ELSE (SELECT relkind FROM pg_class WHERE oid = typrelid) END as typrelkind, "
1861 "typtype, typisdefined "
1867 appendPQExpBuffer(query, "SELECT "
1868 "(SELECT oid FROM pg_class WHERE relname = 'pg_type') AS tableoid, "
1870 "0::oid as typnamespace, "
1871 "(%s typowner) as rolname, "
1872 "typinput::oid as typinput, "
1873 "typoutput::oid as typoutput, typelem, typrelid, "
1874 "CASE WHEN typrelid = 0 THEN ' '::\"char\" "
1875 "ELSE (SELECT relkind FROM pg_class WHERE oid = typrelid) END as typrelkind, "
1876 "typtype, typisdefined "
1881 res = PQexec(g_conn, query->data);
1882 check_sql_result(res, g_conn, query->data, PGRES_TUPLES_OK);
1884 ntups = PQntuples(res);
1886 tinfo = (TypeInfo *) malloc(ntups * sizeof(TypeInfo));
1888 i_tableoid = PQfnumber(res, "tableoid");
1889 i_oid = PQfnumber(res, "oid");
1890 i_typname = PQfnumber(res, "typname");
1891 i_typnamespace = PQfnumber(res, "typnamespace");
1892 i_rolname = PQfnumber(res, "rolname");
1893 i_typinput = PQfnumber(res, "typinput");
1894 i_typoutput = PQfnumber(res, "typoutput");
1895 i_typelem = PQfnumber(res, "typelem");
1896 i_typrelid = PQfnumber(res, "typrelid");
1897 i_typrelkind = PQfnumber(res, "typrelkind");
1898 i_typtype = PQfnumber(res, "typtype");
1899 i_typisdefined = PQfnumber(res, "typisdefined");
1901 for (i = 0; i < ntups; i++)
1903 tinfo[i].dobj.objType = DO_TYPE;
1904 tinfo[i].dobj.catId.tableoid = atooid(PQgetvalue(res, i, i_tableoid));
1905 tinfo[i].dobj.catId.oid = atooid(PQgetvalue(res, i, i_oid));
1906 AssignDumpId(&tinfo[i].dobj);
1907 tinfo[i].dobj.name = strdup(PQgetvalue(res, i, i_typname));
1908 tinfo[i].dobj.namespace = findNamespace(atooid(PQgetvalue(res, i, i_typnamespace)),
1909 tinfo[i].dobj.catId.oid);
1910 tinfo[i].rolname = strdup(PQgetvalue(res, i, i_rolname));
1911 tinfo[i].typelem = atooid(PQgetvalue(res, i, i_typelem));
1912 tinfo[i].typrelid = atooid(PQgetvalue(res, i, i_typrelid));
1913 tinfo[i].typrelkind = *PQgetvalue(res, i, i_typrelkind);
1914 tinfo[i].typtype = *PQgetvalue(res, i, i_typtype);
1915 tinfo[i].shellType = NULL;
1918 * If it's a table's rowtype, use special type code to facilitate
1919 * sorting into the desired order. (We don't want to consider it an
1920 * ordinary type because that would bring the table up into the
1921 * datatype part of the dump order.)
1923 if (OidIsValid(tinfo[i].typrelid) &&
1924 tinfo[i].typrelkind != RELKIND_COMPOSITE_TYPE)
1925 tinfo[i].dobj.objType = DO_TABLE_TYPE;
1928 * check for user-defined array types, omit system generated ones
1930 if (OidIsValid(tinfo[i].typelem) &&
1931 tinfo[i].dobj.name[0] != '_')
1932 tinfo[i].isArray = true;
1934 tinfo[i].isArray = false;
1936 if (strcmp(PQgetvalue(res, i, i_typisdefined), "t") == 0)
1937 tinfo[i].isDefined = true;
1939 tinfo[i].isDefined = false;
1941 /* Decide whether we want to dump it */
1942 selectDumpableType(&tinfo[i]);
1945 * If it's a domain, fetch info about its constraints, if any
1947 tinfo[i].nDomChecks = 0;
1948 tinfo[i].domChecks = NULL;
1949 if (tinfo[i].dobj.dump && tinfo[i].typtype == 'd')
1950 getDomainConstraints(&(tinfo[i]));
1953 * If it's a base type, make a DumpableObject representing a shell
1954 * definition of the type. We will need to dump that ahead of the
1955 * I/O functions for the type.
1957 * Note: the shell type doesn't have a catId. You might think it
1958 * should copy the base type's catId, but then it might capture
1959 * the pg_depend entries for the type, which we don't want.
1961 if (tinfo[i].dobj.dump && tinfo[i].typtype == 'b')
1963 stinfo = (ShellTypeInfo *) malloc(sizeof(ShellTypeInfo));
1964 stinfo->dobj.objType = DO_SHELL_TYPE;
1965 stinfo->dobj.catId = nilCatalogId;
1966 AssignDumpId(&stinfo->dobj);
1967 stinfo->dobj.name = strdup(tinfo[i].dobj.name);
1968 stinfo->dobj.namespace = tinfo[i].dobj.namespace;
1969 stinfo->baseType = &(tinfo[i]);
1970 tinfo[i].shellType = stinfo;
1973 * Initially mark the shell type as not to be dumped. We'll
1974 * only dump it if the I/O functions need to be dumped; this
1975 * is taken care of while sorting dependencies.
1977 stinfo->dobj.dump = false;
1980 * However, if dumping from pre-7.3, there will be no dependency
1981 * info so we have to fake it here. We only need to worry about
1982 * typinput and typoutput since the other functions only exist
1985 if (g_fout->remoteVersion < 70300)
1991 typinput = atooid(PQgetvalue(res, i, i_typinput));
1992 typoutput = atooid(PQgetvalue(res, i, i_typoutput));
1994 funcInfo = findFuncByOid(typinput);
1995 if (funcInfo && funcInfo->dobj.dump)
1997 /* base type depends on function */
1998 addObjectDependency(&tinfo[i].dobj,
1999 funcInfo->dobj.dumpId);
2000 /* function depends on shell type */
2001 addObjectDependency(&funcInfo->dobj,
2002 stinfo->dobj.dumpId);
2003 /* mark shell type as to be dumped */
2004 stinfo->dobj.dump = true;
2007 funcInfo = findFuncByOid(typoutput);
2008 if (funcInfo && funcInfo->dobj.dump)
2010 /* base type depends on function */
2011 addObjectDependency(&tinfo[i].dobj,
2012 funcInfo->dobj.dumpId);
2013 /* function depends on shell type */
2014 addObjectDependency(&funcInfo->dobj,
2015 stinfo->dobj.dumpId);
2016 /* mark shell type as to be dumped */
2017 stinfo->dobj.dump = true;
2022 if (strlen(tinfo[i].rolname) == 0 && tinfo[i].isDefined)
2023 write_msg(NULL, "WARNING: owner of data type \"%s\" appears to be invalid\n",
2024 tinfo[i].dobj.name);
2031 destroyPQExpBuffer(query);
2038 * read all operators in the system catalogs and return them in the
2039 * OprInfo* structure
2041 * numOprs is set to the number of operators read in
2044 getOperators(int *numOprs)
2049 PQExpBuffer query = createPQExpBuffer();
2059 * find all operators, including builtin operators; we filter out
2060 * system-defined operators at dump-out time.
2063 /* Make sure we are in proper schema */
2064 selectSourceSchema("pg_catalog");
2066 if (g_fout->remoteVersion >= 70300)
2068 appendPQExpBuffer(query, "SELECT tableoid, oid, oprname, "
2070 "(%s oprowner) as rolname, "
2071 "oprcode::oid as oprcode "
2075 else if (g_fout->remoteVersion >= 70100)
2077 appendPQExpBuffer(query, "SELECT tableoid, oid, oprname, "
2078 "0::oid as oprnamespace, "
2079 "(%s oprowner) as rolname, "
2080 "oprcode::oid as oprcode "
2086 appendPQExpBuffer(query, "SELECT "
2087 "(SELECT oid FROM pg_class WHERE relname = 'pg_operator') AS tableoid, "
2089 "0::oid as oprnamespace, "
2090 "(%s oprowner) as rolname, "
2091 "oprcode::oid as oprcode "
2096 res = PQexec(g_conn, query->data);
2097 check_sql_result(res, g_conn, query->data, PGRES_TUPLES_OK);
2099 ntups = PQntuples(res);
2102 oprinfo = (OprInfo *) malloc(ntups * sizeof(OprInfo));
2104 i_tableoid = PQfnumber(res, "tableoid");
2105 i_oid = PQfnumber(res, "oid");
2106 i_oprname = PQfnumber(res, "oprname");
2107 i_oprnamespace = PQfnumber(res, "oprnamespace");
2108 i_rolname = PQfnumber(res, "rolname");
2109 i_oprcode = PQfnumber(res, "oprcode");
2111 for (i = 0; i < ntups; i++)
2113 oprinfo[i].dobj.objType = DO_OPERATOR;
2114 oprinfo[i].dobj.catId.tableoid = atooid(PQgetvalue(res, i, i_tableoid));
2115 oprinfo[i].dobj.catId.oid = atooid(PQgetvalue(res, i, i_oid));
2116 AssignDumpId(&oprinfo[i].dobj);
2117 oprinfo[i].dobj.name = strdup(PQgetvalue(res, i, i_oprname));
2118 oprinfo[i].dobj.namespace = findNamespace(atooid(PQgetvalue(res, i, i_oprnamespace)),
2119 oprinfo[i].dobj.catId.oid);
2120 oprinfo[i].rolname = strdup(PQgetvalue(res, i, i_rolname));
2121 oprinfo[i].oprcode = atooid(PQgetvalue(res, i, i_oprcode));
2123 /* Decide whether we want to dump it */
2124 selectDumpableObject(&(oprinfo[i].dobj));
2126 if (strlen(oprinfo[i].rolname) == 0)
2127 write_msg(NULL, "WARNING: owner of operator \"%s\" appears to be invalid\n",
2128 oprinfo[i].dobj.name);
2133 destroyPQExpBuffer(query);
2140 * read all conversions in the system catalogs and return them in the
2141 * ConvInfo* structure
2143 * numConversions is set to the number of conversions read in
2146 getConversions(int *numConversions)
2151 PQExpBuffer query = createPQExpBuffer();
2159 /* Conversions didn't exist pre-7.3 */
2160 if (g_fout->remoteVersion < 70300)
2162 *numConversions = 0;
2167 * find all conversions, including builtin conversions; we filter out
2168 * system-defined conversions at dump-out time.
2171 /* Make sure we are in proper schema */
2172 selectSourceSchema("pg_catalog");
2174 appendPQExpBuffer(query, "SELECT tableoid, oid, conname, "
2176 "(%s conowner) as rolname "
2177 "FROM pg_conversion",
2180 res = PQexec(g_conn, query->data);
2181 check_sql_result(res, g_conn, query->data, PGRES_TUPLES_OK);
2183 ntups = PQntuples(res);
2184 *numConversions = ntups;
2186 convinfo = (ConvInfo *) malloc(ntups * sizeof(ConvInfo));
2188 i_tableoid = PQfnumber(res, "tableoid");
2189 i_oid = PQfnumber(res, "oid");
2190 i_conname = PQfnumber(res, "conname");
2191 i_connamespace = PQfnumber(res, "connamespace");
2192 i_rolname = PQfnumber(res, "rolname");
2194 for (i = 0; i < ntups; i++)
2196 convinfo[i].dobj.objType = DO_CONVERSION;
2197 convinfo[i].dobj.catId.tableoid = atooid(PQgetvalue(res, i, i_tableoid));
2198 convinfo[i].dobj.catId.oid = atooid(PQgetvalue(res, i, i_oid));
2199 AssignDumpId(&convinfo[i].dobj);
2200 convinfo[i].dobj.name = strdup(PQgetvalue(res, i, i_conname));
2201 convinfo[i].dobj.namespace = findNamespace(atooid(PQgetvalue(res, i, i_connamespace)),
2202 convinfo[i].dobj.catId.oid);
2203 convinfo[i].rolname = strdup(PQgetvalue(res, i, i_rolname));
2205 /* Decide whether we want to dump it */
2206 selectDumpableObject(&(convinfo[i].dobj));
2211 destroyPQExpBuffer(query);
2218 * read all opclasses in the system catalogs and return them in the
2219 * OpclassInfo* structure
2221 * numOpclasses is set to the number of opclasses read in
2224 getOpclasses(int *numOpclasses)
2229 PQExpBuffer query = createPQExpBuffer();
2230 OpclassInfo *opcinfo;
2238 * find all opclasses, including builtin opclasses; we filter out
2239 * system-defined opclasses at dump-out time.
2242 /* Make sure we are in proper schema */
2243 selectSourceSchema("pg_catalog");
2245 if (g_fout->remoteVersion >= 70300)
2247 appendPQExpBuffer(query, "SELECT tableoid, oid, opcname, "
2249 "(%s opcowner) as rolname "
2253 else if (g_fout->remoteVersion >= 70100)
2255 appendPQExpBuffer(query, "SELECT tableoid, oid, opcname, "
2256 "0::oid as opcnamespace, "
2257 "''::name as rolname "
2262 appendPQExpBuffer(query, "SELECT "
2263 "(SELECT oid FROM pg_class WHERE relname = 'pg_opclass') AS tableoid, "
2265 "0::oid as opcnamespace, "
2266 "''::name as rolname "
2270 res = PQexec(g_conn, query->data);
2271 check_sql_result(res, g_conn, query->data, PGRES_TUPLES_OK);
2273 ntups = PQntuples(res);
2274 *numOpclasses = ntups;
2276 opcinfo = (OpclassInfo *) malloc(ntups * sizeof(OpclassInfo));
2278 i_tableoid = PQfnumber(res, "tableoid");
2279 i_oid = PQfnumber(res, "oid");
2280 i_opcname = PQfnumber(res, "opcname");
2281 i_opcnamespace = PQfnumber(res, "opcnamespace");
2282 i_rolname = PQfnumber(res, "rolname");
2284 for (i = 0; i < ntups; i++)
2286 opcinfo[i].dobj.objType = DO_OPCLASS;
2287 opcinfo[i].dobj.catId.tableoid = atooid(PQgetvalue(res, i, i_tableoid));
2288 opcinfo[i].dobj.catId.oid = atooid(PQgetvalue(res, i, i_oid));
2289 AssignDumpId(&opcinfo[i].dobj);
2290 opcinfo[i].dobj.name = strdup(PQgetvalue(res, i, i_opcname));
2291 opcinfo[i].dobj.namespace = findNamespace(atooid(PQgetvalue(res, i, i_opcnamespace)),
2292 opcinfo[i].dobj.catId.oid);
2293 opcinfo[i].rolname = strdup(PQgetvalue(res, i, i_rolname));
2295 /* Decide whether we want to dump it */
2296 selectDumpableObject(&(opcinfo[i].dobj));
2298 if (g_fout->remoteVersion >= 70300)
2300 if (strlen(opcinfo[i].rolname) == 0)
2301 write_msg(NULL, "WARNING: owner of operator class \"%s\" appears to be invalid\n",
2302 opcinfo[i].dobj.name);
2308 destroyPQExpBuffer(query);
2315 * read all the user-defined aggregates in the system catalogs and
2316 * return them in the AggInfo* structure
2318 * numAggs is set to the number of aggregates read in
2321 getAggregates(int *numAggs)
2326 PQExpBuffer query = createPQExpBuffer();
2336 /* Make sure we are in proper schema */
2337 selectSourceSchema("pg_catalog");
2339 /* find all user-defined aggregates */
2341 if (g_fout->remoteVersion >= 70300)
2343 appendPQExpBuffer(query, "SELECT tableoid, oid, proname as aggname, "
2344 "pronamespace as aggnamespace, "
2345 "proargtypes[0] as aggbasetype, "
2346 "(%s proowner) as rolname, "
2350 "AND pronamespace != "
2351 "(select oid from pg_namespace where nspname = 'pg_catalog')",
2354 else if (g_fout->remoteVersion >= 70100)
2356 appendPQExpBuffer(query, "SELECT tableoid, oid, aggname, "
2357 "0::oid as aggnamespace, "
2359 "(%s aggowner) as rolname, "
2361 "FROM pg_aggregate "
2362 "where oid > '%u'::oid",
2364 g_last_builtin_oid);
2368 appendPQExpBuffer(query, "SELECT "
2369 "(SELECT oid FROM pg_class WHERE relname = 'pg_aggregate') AS tableoid, "
2371 "0::oid as aggnamespace, "
2373 "(%s aggowner) as rolname, "
2375 "FROM pg_aggregate "
2376 "where oid > '%u'::oid",
2378 g_last_builtin_oid);
2381 res = PQexec(g_conn, query->data);
2382 check_sql_result(res, g_conn, query->data, PGRES_TUPLES_OK);
2384 ntups = PQntuples(res);
2387 agginfo = (AggInfo *) malloc(ntups * sizeof(AggInfo));
2389 i_tableoid = PQfnumber(res, "tableoid");
2390 i_oid = PQfnumber(res, "oid");
2391 i_aggname = PQfnumber(res, "aggname");
2392 i_aggnamespace = PQfnumber(res, "aggnamespace");
2393 i_aggbasetype = PQfnumber(res, "aggbasetype");
2394 i_rolname = PQfnumber(res, "rolname");
2395 i_aggacl = PQfnumber(res, "aggacl");
2397 for (i = 0; i < ntups; i++)
2399 agginfo[i].aggfn.dobj.objType = DO_AGG;
2400 agginfo[i].aggfn.dobj.catId.tableoid = atooid(PQgetvalue(res, i, i_tableoid));
2401 agginfo[i].aggfn.dobj.catId.oid = atooid(PQgetvalue(res, i, i_oid));
2402 AssignDumpId(&agginfo[i].aggfn.dobj);
2403 agginfo[i].aggfn.dobj.name = strdup(PQgetvalue(res, i, i_aggname));
2404 agginfo[i].aggfn.dobj.namespace = findNamespace(atooid(PQgetvalue(res, i, i_aggnamespace)),
2405 agginfo[i].aggfn.dobj.catId.oid);
2406 agginfo[i].aggfn.rolname = strdup(PQgetvalue(res, i, i_rolname));
2407 if (strlen(agginfo[i].aggfn.rolname) == 0)
2408 write_msg(NULL, "WARNING: owner of aggregate function \"%s\" appears to be invalid\n",
2409 agginfo[i].aggfn.dobj.name);
2410 agginfo[i].aggfn.lang = InvalidOid; /* not currently interesting */
2411 agginfo[i].aggfn.nargs = 1;
2412 agginfo[i].aggfn.argtypes = (Oid *) malloc(sizeof(Oid));
2413 agginfo[i].aggfn.argtypes[0] = atooid(PQgetvalue(res, i, i_aggbasetype));
2414 agginfo[i].aggfn.prorettype = InvalidOid; /* not saved */
2415 agginfo[i].aggfn.proacl = strdup(PQgetvalue(res, i, i_aggacl));
2416 agginfo[i].anybasetype = false; /* computed when it's dumped */
2417 agginfo[i].fmtbasetype = NULL; /* computed when it's dumped */
2419 /* Decide whether we want to dump it */
2420 selectDumpableObject(&(agginfo[i].aggfn.dobj));
2425 destroyPQExpBuffer(query);
2432 * read all the user-defined functions in the system catalogs and
2433 * return them in the FuncInfo* structure
2435 * numFuncs is set to the number of functions read in
2438 getFuncs(int *numFuncs)
2443 PQExpBuffer query = createPQExpBuffer();
2456 /* Make sure we are in proper schema */
2457 selectSourceSchema("pg_catalog");
2459 /* find all user-defined funcs */
2461 if (g_fout->remoteVersion >= 70300)
2463 appendPQExpBuffer(query,
2464 "SELECT tableoid, oid, proname, prolang, "
2465 "pronargs, proargtypes, prorettype, proacl, "
2467 "(%s proowner) as rolname "
2469 "WHERE NOT proisagg "
2470 "AND pronamespace != "
2471 "(select oid from pg_namespace"
2472 " where nspname = 'pg_catalog')",
2475 else if (g_fout->remoteVersion >= 70100)
2477 appendPQExpBuffer(query,
2478 "SELECT tableoid, oid, proname, prolang, "
2479 "pronargs, proargtypes, prorettype, "
2480 "'{=X}' as proacl, "
2481 "0::oid as pronamespace, "
2482 "(%s proowner) as rolname "
2484 "where pg_proc.oid > '%u'::oid",
2486 g_last_builtin_oid);
2490 appendPQExpBuffer(query,
2492 "(SELECT oid FROM pg_class "
2493 " WHERE relname = 'pg_proc') AS tableoid, "
2494 "oid, proname, prolang, "
2495 "pronargs, proargtypes, prorettype, "
2496 "'{=X}' as proacl, "
2497 "0::oid as pronamespace, "
2498 "(%s proowner) as rolname "
2500 "where pg_proc.oid > '%u'::oid",
2502 g_last_builtin_oid);
2505 res = PQexec(g_conn, query->data);
2506 check_sql_result(res, g_conn, query->data, PGRES_TUPLES_OK);
2508 ntups = PQntuples(res);
2512 finfo = (FuncInfo *) calloc(ntups, sizeof(FuncInfo));
2514 i_tableoid = PQfnumber(res, "tableoid");
2515 i_oid = PQfnumber(res, "oid");
2516 i_proname = PQfnumber(res, "proname");
2517 i_pronamespace = PQfnumber(res, "pronamespace");
2518 i_rolname = PQfnumber(res, "rolname");
2519 i_prolang = PQfnumber(res, "prolang");
2520 i_pronargs = PQfnumber(res, "pronargs");
2521 i_proargtypes = PQfnumber(res, "proargtypes");
2522 i_prorettype = PQfnumber(res, "prorettype");
2523 i_proacl = PQfnumber(res, "proacl");
2525 for (i = 0; i < ntups; i++)
2527 finfo[i].dobj.objType = DO_FUNC;
2528 finfo[i].dobj.catId.tableoid = atooid(PQgetvalue(res, i, i_tableoid));
2529 finfo[i].dobj.catId.oid = atooid(PQgetvalue(res, i, i_oid));
2530 AssignDumpId(&finfo[i].dobj);
2531 finfo[i].dobj.name = strdup(PQgetvalue(res, i, i_proname));
2532 finfo[i].dobj.namespace =
2533 findNamespace(atooid(PQgetvalue(res, i, i_pronamespace)),
2534 finfo[i].dobj.catId.oid);
2535 finfo[i].rolname = strdup(PQgetvalue(res, i, i_rolname));
2536 finfo[i].lang = atooid(PQgetvalue(res, i, i_prolang));
2537 finfo[i].prorettype = atooid(PQgetvalue(res, i, i_prorettype));
2538 finfo[i].proacl = strdup(PQgetvalue(res, i, i_proacl));
2539 finfo[i].nargs = atoi(PQgetvalue(res, i, i_pronargs));
2540 if (finfo[i].nargs == 0)
2541 finfo[i].argtypes = NULL;
2544 finfo[i].argtypes = (Oid *) malloc(finfo[i].nargs * sizeof(Oid));
2545 parseOidArray(PQgetvalue(res, i, i_proargtypes),
2546 finfo[i].argtypes, finfo[i].nargs);
2549 /* Decide whether we want to dump it */
2550 selectDumpableObject(&(finfo[i].dobj));
2552 if (strlen(finfo[i].rolname) == 0)
2554 "WARNING: owner of function \"%s\" appears to be invalid\n",
2555 finfo[i].dobj.name);
2560 destroyPQExpBuffer(query);
2567 * read all the user-defined tables (no indexes, no catalogs)
2568 * in the system catalogs return them in the TableInfo* structure
2570 * numTables is set to the number of tables read in
2573 getTables(int *numTables)
2578 PQExpBuffer query = createPQExpBuffer();
2579 PQExpBuffer delqry = createPQExpBuffer();
2580 PQExpBuffer lockquery = createPQExpBuffer();
2596 int i_reltablespace;
2599 /* Make sure we are in proper schema */
2600 selectSourceSchema("pg_catalog");
2603 * Find all the tables (including views and sequences).
2605 * We include system catalogs, so that we can work if a user table is
2606 * defined to inherit from a system catalog (pretty weird, but...)
2608 * We ignore tables that are not type 'r' (ordinary relation), 'S'
2609 * (sequence), 'v' (view), or 'c' (composite type).
2611 * Composite-type table entries won't be dumped as such, but we have to
2612 * make a DumpableObject for them so that we can track dependencies of the
2613 * composite type (pg_depend entries for columns of the composite type
2614 * link to the pg_class entry not the pg_type entry).
2616 * Note: in this phase we should collect only a minimal amount of
2617 * information about each table, basically just enough to decide if it is
2618 * interesting. We must fetch all tables in this phase because otherwise
2619 * we cannot correctly identify inherited columns, serial columns, etc.
2622 if (g_fout->remoteVersion >= 80200)
2625 * Left join to pick up dependency info linking sequences to their
2626 * serial column, if any
2628 appendPQExpBuffer(query,
2629 "SELECT c.tableoid, c.oid, relname, "
2630 "relacl, relkind, relnamespace, "
2631 "(%s relowner) as rolname, "
2632 "relchecks, reltriggers, "
2633 "relhasindex, relhasrules, relhasoids, "
2634 "d.refobjid as owning_tab, "
2635 "d.refobjsubid as owning_col, "
2636 "(SELECT spcname FROM pg_tablespace t WHERE t.oid = c.reltablespace) AS reltablespace, "
2637 "array_to_string(c.reloptions, ', ') as reloptions "
2639 "left join pg_depend d on "
2640 "(c.relkind = '%c' and "
2641 "d.classid = c.tableoid and d.objid = c.oid and "
2642 "d.objsubid = 0 and "
2643 "d.refclassid = c.tableoid and d.deptype = 'i') "
2644 "where relkind in ('%c', '%c', '%c', '%c') "
2648 RELKIND_RELATION, RELKIND_SEQUENCE,
2649 RELKIND_VIEW, RELKIND_COMPOSITE_TYPE);
2651 else if (g_fout->remoteVersion >= 80000)
2654 * Left join to pick up dependency info linking sequences to their
2655 * serial column, if any
2657 appendPQExpBuffer(query,
2658 "SELECT c.tableoid, c.oid, relname, "
2659 "relacl, relkind, relnamespace, "
2660 "(%s relowner) as rolname, "
2661 "relchecks, reltriggers, "
2662 "relhasindex, relhasrules, relhasoids, "
2663 "d.refobjid as owning_tab, "
2664 "d.refobjsubid as owning_col, "
2665 "(SELECT spcname FROM pg_tablespace t WHERE t.oid = c.reltablespace) AS reltablespace, "
2666 "NULL as reloptions "
2668 "left join pg_depend d on "
2669 "(c.relkind = '%c' and "
2670 "d.classid = c.tableoid and d.objid = c.oid and "
2671 "d.objsubid = 0 and "
2672 "d.refclassid = c.tableoid and d.deptype = 'i') "
2673 "where relkind in ('%c', '%c', '%c', '%c') "
2677 RELKIND_RELATION, RELKIND_SEQUENCE,
2678 RELKIND_VIEW, RELKIND_COMPOSITE_TYPE);
2680 else if (g_fout->remoteVersion >= 70300)
2683 * Left join to pick up dependency info linking sequences to their
2684 * serial column, if any
2686 appendPQExpBuffer(query,
2687 "SELECT c.tableoid, c.oid, relname, "
2688 "relacl, relkind, relnamespace, "
2689 "(%s relowner) as rolname, "
2690 "relchecks, reltriggers, "
2691 "relhasindex, relhasrules, relhasoids, "
2692 "d.refobjid as owning_tab, "
2693 "d.refobjsubid as owning_col, "
2694 "NULL as reltablespace, "
2695 "NULL as reloptions "
2697 "left join pg_depend d on "
2698 "(c.relkind = '%c' and "
2699 "d.classid = c.tableoid and d.objid = c.oid and "
2700 "d.objsubid = 0 and "
2701 "d.refclassid = c.tableoid and d.deptype = 'i') "
2702 "where relkind in ('%c', '%c', '%c', '%c') "
2706 RELKIND_RELATION, RELKIND_SEQUENCE,
2707 RELKIND_VIEW, RELKIND_COMPOSITE_TYPE);
2709 else if (g_fout->remoteVersion >= 70200)
2711 appendPQExpBuffer(query,
2712 "SELECT tableoid, oid, relname, relacl, relkind, "
2713 "0::oid as relnamespace, "
2714 "(%s relowner) as rolname, "
2715 "relchecks, reltriggers, "
2716 "relhasindex, relhasrules, relhasoids, "
2717 "NULL::oid as owning_tab, "
2718 "NULL::int4 as owning_col, "
2719 "NULL as reltablespace, "
2720 "NULL as reloptions "
2722 "where relkind in ('%c', '%c', '%c') "
2725 RELKIND_RELATION, RELKIND_SEQUENCE, RELKIND_VIEW);
2727 else if (g_fout->remoteVersion >= 70100)
2729 /* all tables have oids in 7.1 */
2730 appendPQExpBuffer(query,
2731 "SELECT tableoid, oid, relname, relacl, relkind, "
2732 "0::oid as relnamespace, "
2733 "(%s relowner) as rolname, "
2734 "relchecks, reltriggers, "
2735 "relhasindex, relhasrules, "
2736 "'t'::bool as relhasoids, "
2737 "NULL::oid as owning_tab, "
2738 "NULL::int4 as owning_col, "
2739 "NULL as reltablespace, "
2740 "NULL as reloptions "
2742 "where relkind in ('%c', '%c', '%c') "
2745 RELKIND_RELATION, RELKIND_SEQUENCE, RELKIND_VIEW);
2750 * Before 7.1, view relkind was not set to 'v', so we must check if we
2751 * have a view by looking for a rule in pg_rewrite.
2753 appendPQExpBuffer(query,
2755 "(SELECT oid FROM pg_class WHERE relname = 'pg_class') AS tableoid, "
2756 "oid, relname, relacl, "
2757 "CASE WHEN relhasrules and relkind = 'r' "
2758 " and EXISTS(SELECT rulename FROM pg_rewrite r WHERE "
2759 " r.ev_class = c.oid AND r.ev_type = '1') "
2760 "THEN '%c'::\"char\" "
2761 "ELSE relkind END AS relkind,"
2762 "0::oid as relnamespace, "
2763 "(%s relowner) as rolname, "
2764 "relchecks, reltriggers, "
2765 "relhasindex, relhasrules, "
2766 "'t'::bool as relhasoids, "
2767 "NULL::oid as owning_tab, "
2768 "NULL::int4 as owning_col, "
2769 "NULL as reltablespace, "
2770 "NULL as reloptions "
2772 "where relkind in ('%c', '%c') "
2776 RELKIND_RELATION, RELKIND_SEQUENCE);
2779 res = PQexec(g_conn, query->data);
2780 check_sql_result(res, g_conn, query->data, PGRES_TUPLES_OK);
2782 ntups = PQntuples(res);
2787 * Extract data from result and lock dumpable tables. We do the locking
2788 * before anything else, to minimize the window wherein a table could
2789 * disappear under us.
2791 * Note that we have to save info about all tables here, even when dumping
2792 * only one, because we don't yet know which tables might be inheritance
2793 * ancestors of the target table.
2795 tblinfo = (TableInfo *) calloc(ntups, sizeof(TableInfo));
2797 i_reltableoid = PQfnumber(res, "tableoid");
2798 i_reloid = PQfnumber(res, "oid");
2799 i_relname = PQfnumber(res, "relname");
2800 i_relnamespace = PQfnumber(res, "relnamespace");
2801 i_relacl = PQfnumber(res, "relacl");
2802 i_relkind = PQfnumber(res, "relkind");
2803 i_rolname = PQfnumber(res, "rolname");
2804 i_relchecks = PQfnumber(res, "relchecks");
2805 i_reltriggers = PQfnumber(res, "reltriggers");
2806 i_relhasindex = PQfnumber(res, "relhasindex");
2807 i_relhasrules = PQfnumber(res, "relhasrules");
2808 i_relhasoids = PQfnumber(res, "relhasoids");
2809 i_owning_tab = PQfnumber(res, "owning_tab");
2810 i_owning_col = PQfnumber(res, "owning_col");
2811 i_reltablespace = PQfnumber(res, "reltablespace");
2812 i_reloptions = PQfnumber(res, "reloptions");
2814 for (i = 0; i < ntups; i++)
2816 tblinfo[i].dobj.objType = DO_TABLE;
2817 tblinfo[i].dobj.catId.tableoid = atooid(PQgetvalue(res, i, i_reltableoid));
2818 tblinfo[i].dobj.catId.oid = atooid(PQgetvalue(res, i, i_reloid));
2819 AssignDumpId(&tblinfo[i].dobj);
2820 tblinfo[i].dobj.name = strdup(PQgetvalue(res, i, i_relname));
2821 tblinfo[i].dobj.namespace = findNamespace(atooid(PQgetvalue(res, i, i_relnamespace)),
2822 tblinfo[i].dobj.catId.oid);
2823 tblinfo[i].rolname = strdup(PQgetvalue(res, i, i_rolname));
2824 tblinfo[i].relacl = strdup(PQgetvalue(res, i, i_relacl));
2825 tblinfo[i].relkind = *(PQgetvalue(res, i, i_relkind));
2826 tblinfo[i].hasindex = (strcmp(PQgetvalue(res, i, i_relhasindex), "t") == 0);
2827 tblinfo[i].hasrules = (strcmp(PQgetvalue(res, i, i_relhasrules), "t") == 0);
2828 tblinfo[i].hasoids = (strcmp(PQgetvalue(res, i, i_relhasoids), "t") == 0);
2829 tblinfo[i].ncheck = atoi(PQgetvalue(res, i, i_relchecks));
2830 tblinfo[i].ntrig = atoi(PQgetvalue(res, i, i_reltriggers));
2831 if (PQgetisnull(res, i, i_owning_tab))
2833 tblinfo[i].owning_tab = InvalidOid;
2834 tblinfo[i].owning_col = 0;
2838 tblinfo[i].owning_tab = atooid(PQgetvalue(res, i, i_owning_tab));
2839 tblinfo[i].owning_col = atoi(PQgetvalue(res, i, i_owning_col));
2841 tblinfo[i].reltablespace = strdup(PQgetvalue(res, i, i_reltablespace));
2842 tblinfo[i].reloptions = strdup(PQgetvalue(res, i, i_reloptions));
2844 /* other fields were zeroed above */
2847 * Decide whether we want to dump this table. Sequences owned by
2848 * serial columns are never dumpable on their own; we will transpose
2849 * their owning table's dump flag to them below.
2851 if (tblinfo[i].relkind == RELKIND_COMPOSITE_TYPE)
2852 tblinfo[i].dobj.dump = false;
2853 else if (OidIsValid(tblinfo[i].owning_tab))
2854 tblinfo[i].dobj.dump = false;
2856 selectDumpableTable(&tblinfo[i]);
2857 tblinfo[i].interesting = tblinfo[i].dobj.dump;
2860 * Read-lock target tables to make sure they aren't DROPPED or altered
2861 * in schema before we get around to dumping them.
2863 * Note that we don't explicitly lock parents of the target tables; we
2864 * assume our lock on the child is enough to prevent schema
2865 * alterations to parent tables.
2867 * NOTE: it'd be kinda nice to lock views and sequences too, not only
2868 * plain tables, but the backend doesn't presently allow that.
2870 if (tblinfo[i].dobj.dump && tblinfo[i].relkind == RELKIND_RELATION)
2872 resetPQExpBuffer(lockquery);
2873 appendPQExpBuffer(lockquery,
2874 "LOCK TABLE %s IN ACCESS SHARE MODE",
2875 fmtQualifiedId(tblinfo[i].dobj.namespace->dobj.name,
2876 tblinfo[i].dobj.name));
2877 do_sql_command(g_conn, lockquery->data);
2880 /* Emit notice if join for owner failed */
2881 if (strlen(tblinfo[i].rolname) == 0)
2882 write_msg(NULL, "WARNING: owner of table \"%s\" appears to be invalid\n",
2883 tblinfo[i].dobj.name);
2887 * If the user is attempting to dump a specific table, check to ensure
2888 * that the specified table actually exists. (This is a bit simplistic
2889 * since we don't fully check the combination of -n and -t switches.)
2891 if (selectTableName)
2893 for (i = 0; i < ntups; i++)
2894 if (strcmp(tblinfo[i].dobj.name, selectTableName) == 0)
2897 /* Didn't find a match */
2900 write_msg(NULL, "specified table \"%s\" does not exist\n",
2907 destroyPQExpBuffer(query);
2908 destroyPQExpBuffer(delqry);
2909 destroyPQExpBuffer(lockquery);
2916 * read all the inheritance information
2917 * from the system catalogs return them in the InhInfo* structure
2919 * numInherits is set to the number of pairs read in
2922 getInherits(int *numInherits)
2927 PQExpBuffer query = createPQExpBuffer();
2933 /* Make sure we are in proper schema */
2934 selectSourceSchema("pg_catalog");
2936 /* find all the inheritance information */
2938 appendPQExpBuffer(query, "SELECT inhrelid, inhparent from pg_inherits");
2940 res = PQexec(g_conn, query->data);
2941 check_sql_result(res, g_conn, query->data, PGRES_TUPLES_OK);
2943 ntups = PQntuples(res);
2945 *numInherits = ntups;
2947 inhinfo = (InhInfo *) malloc(ntups * sizeof(InhInfo));
2949 i_inhrelid = PQfnumber(res, "inhrelid");
2950 i_inhparent = PQfnumber(res, "inhparent");
2952 for (i = 0; i < ntups; i++)
2954 inhinfo[i].inhrelid = atooid(PQgetvalue(res, i, i_inhrelid));
2955 inhinfo[i].inhparent = atooid(PQgetvalue(res, i, i_inhparent));
2960 destroyPQExpBuffer(query);
2967 * get information about every index on a dumpable table
2969 * Note: index data is not returned directly to the caller, but it
2970 * does get entered into the DumpableObject tables.
2973 getIndexes(TableInfo tblinfo[], int numTables)
2977 PQExpBuffer query = createPQExpBuffer();
2980 ConstraintInfo *constrinfo;
2996 for (i = 0; i < numTables; i++)
2998 TableInfo *tbinfo = &tblinfo[i];
3000 /* Only plain tables have indexes */
3001 if (tbinfo->relkind != RELKIND_RELATION || !tbinfo->hasindex)
3004 /* Ignore indexes of tables not to be dumped */
3005 if (!tbinfo->dobj.dump)
3009 write_msg(NULL, "reading indexes for table \"%s\"\n",
3012 /* Make sure we are in proper schema so indexdef is right */
3013 selectSourceSchema(tbinfo->dobj.namespace->dobj.name);
3016 * The point of the messy-looking outer join is to find a constraint
3017 * that is related by an internal dependency link to the index. If we
3018 * find one, create a CONSTRAINT entry linked to the INDEX entry. We
3019 * assume an index won't have more than one internal dependency.
3021 resetPQExpBuffer(query);
3022 if (g_fout->remoteVersion >= 80200)
3024 appendPQExpBuffer(query,
3025 "SELECT t.tableoid, t.oid, "
3026 "t.relname as indexname, "
3027 "pg_catalog.pg_get_indexdef(i.indexrelid) as indexdef, "
3028 "t.relnatts as indnkeys, "
3029 "i.indkey, i.indisclustered, "
3030 "c.contype, c.conname, "
3031 "c.tableoid as contableoid, "
3033 "(SELECT spcname FROM pg_catalog.pg_tablespace s WHERE s.oid = t.reltablespace) as tablespace, "
3034 "array_to_string(t.reloptions, ', ') as options "
3035 "FROM pg_catalog.pg_index i "
3036 "JOIN pg_catalog.pg_class t ON (t.oid = i.indexrelid) "
3037 "LEFT JOIN pg_catalog.pg_depend d "
3038 "ON (d.classid = t.tableoid "
3039 "AND d.objid = t.oid "
3040 "AND d.deptype = 'i') "
3041 "LEFT JOIN pg_catalog.pg_constraint c "
3042 "ON (d.refclassid = c.tableoid "
3043 "AND d.refobjid = c.oid) "
3044 "WHERE i.indrelid = '%u'::pg_catalog.oid "
3045 "ORDER BY indexname",
3046 tbinfo->dobj.catId.oid);
3048 else if (g_fout->remoteVersion >= 80000)
3050 appendPQExpBuffer(query,
3051 "SELECT t.tableoid, t.oid, "
3052 "t.relname as indexname, "
3053 "pg_catalog.pg_get_indexdef(i.indexrelid) as indexdef, "
3054 "t.relnatts as indnkeys, "
3055 "i.indkey, i.indisclustered, "
3056 "c.contype, c.conname, "
3057 "c.tableoid as contableoid, "
3059 "(SELECT spcname FROM pg_catalog.pg_tablespace s WHERE s.oid = t.reltablespace) as tablespace, "
3061 "FROM pg_catalog.pg_index i "
3062 "JOIN pg_catalog.pg_class t ON (t.oid = i.indexrelid) "
3063 "LEFT JOIN pg_catalog.pg_depend d "
3064 "ON (d.classid = t.tableoid "
3065 "AND d.objid = t.oid "
3066 "AND d.deptype = 'i') "
3067 "LEFT JOIN pg_catalog.pg_constraint c "
3068 "ON (d.refclassid = c.tableoid "
3069 "AND d.refobjid = c.oid) "
3070 "WHERE i.indrelid = '%u'::pg_catalog.oid "
3071 "ORDER BY indexname",
3072 tbinfo->dobj.catId.oid);
3074 else if (g_fout->remoteVersion >= 70300)
3076 appendPQExpBuffer(query,
3077 "SELECT t.tableoid, t.oid, "
3078 "t.relname as indexname, "
3079 "pg_catalog.pg_get_indexdef(i.indexrelid) as indexdef, "
3080 "t.relnatts as indnkeys, "
3081 "i.indkey, i.indisclustered, "
3082 "c.contype, c.conname, "
3083 "c.tableoid as contableoid, "
3085 "NULL as tablespace, "
3087 "FROM pg_catalog.pg_index i "
3088 "JOIN pg_catalog.pg_class t ON (t.oid = i.indexrelid) "
3089 "LEFT JOIN pg_catalog.pg_depend d "
3090 "ON (d.classid = t.tableoid "
3091 "AND d.objid = t.oid "
3092 "AND d.deptype = 'i') "
3093 "LEFT JOIN pg_catalog.pg_constraint c "
3094 "ON (d.refclassid = c.tableoid "
3095 "AND d.refobjid = c.oid) "
3096 "WHERE i.indrelid = '%u'::pg_catalog.oid "
3097 "ORDER BY indexname",
3098 tbinfo->dobj.catId.oid);
3100 else if (g_fout->remoteVersion >= 70100)
3102 appendPQExpBuffer(query,
3103 "SELECT t.tableoid, t.oid, "
3104 "t.relname as indexname, "
3105 "pg_get_indexdef(i.indexrelid) as indexdef, "
3106 "t.relnatts as indnkeys, "
3107 "i.indkey, false as indisclustered, "
3108 "CASE WHEN i.indisprimary THEN 'p'::char "
3109 "ELSE '0'::char END as contype, "
3110 "t.relname as conname, "
3111 "0::oid as contableoid, "
3113 "NULL as tablespace, "
3115 "FROM pg_index i, pg_class t "
3116 "WHERE t.oid = i.indexrelid "
3117 "AND i.indrelid = '%u'::oid "
3118 "ORDER BY indexname",
3119 tbinfo->dobj.catId.oid);
3123 appendPQExpBuffer(query,
3125 "(SELECT oid FROM pg_class WHERE relname = 'pg_class') AS tableoid, "
3127 "t.relname as indexname, "
3128 "pg_get_indexdef(i.indexrelid) as indexdef, "
3129 "t.relnatts as indnkeys, "
3130 "i.indkey, false as indisclustered, "
3131 "CASE WHEN i.indisprimary THEN 'p'::char "
3132 "ELSE '0'::char END as contype, "
3133 "t.relname as conname, "
3134 "0::oid as contableoid, "
3136 "NULL as tablespace, "
3138 "FROM pg_index i, pg_class t "
3139 "WHERE t.oid = i.indexrelid "
3140 "AND i.indrelid = '%u'::oid "
3141 "ORDER BY indexname",
3142 tbinfo->dobj.catId.oid);
3145 res = PQexec(g_conn, query->data);
3146 check_sql_result(res, g_conn, query->data, PGRES_TUPLES_OK);
3148 ntups = PQntuples(res);
3150 i_tableoid = PQfnumber(res, "tableoid");
3151 i_oid = PQfnumber(res, "oid");
3152 i_indexname = PQfnumber(res, "indexname");
3153 i_indexdef = PQfnumber(res, "indexdef");
3154 i_indnkeys = PQfnumber(res, "indnkeys");
3155 i_indkey = PQfnumber(res, "indkey");
3156 i_indisclustered = PQfnumber(res, "indisclustered");
3157 i_contype = PQfnumber(res, "contype");
3158 i_conname = PQfnumber(res, "conname");
3159 i_contableoid = PQfnumber(res, "contableoid");
3160 i_conoid = PQfnumber(res, "conoid");
3161 i_tablespace = PQfnumber(res, "tablespace");
3162 i_options = PQfnumber(res, "options");
3164 indxinfo = (IndxInfo *) malloc(ntups * sizeof(IndxInfo));
3165 constrinfo = (ConstraintInfo *) malloc(ntups * sizeof(ConstraintInfo));
3167 for (j = 0; j < ntups; j++)
3171 indxinfo[j].dobj.objType = DO_INDEX;
3172 indxinfo[j].dobj.catId.tableoid = atooid(PQgetvalue(res, j, i_tableoid));
3173 indxinfo[j].dobj.catId.oid = atooid(PQgetvalue(res, j, i_oid));
3174 AssignDumpId(&indxinfo[j].dobj);
3175 indxinfo[j].dobj.name = strdup(PQgetvalue(res, j, i_indexname));
3176 indxinfo[j].dobj.namespace = tbinfo->dobj.namespace;
3177 indxinfo[j].indextable = tbinfo;
3178 indxinfo[j].indexdef = strdup(PQgetvalue(res, j, i_indexdef));
3179 indxinfo[j].indnkeys = atoi(PQgetvalue(res, j, i_indnkeys));
3180 indxinfo[j].tablespace = strdup(PQgetvalue(res, j, i_tablespace));
3181 indxinfo[j].options = strdup(PQgetvalue(res, j, i_options));
3184 * In pre-7.4 releases, indkeys may contain more entries than
3185 * indnkeys says (since indnkeys will be 1 for a functional
3186 * index). We don't actually care about this case since we don't
3187 * examine indkeys except for indexes associated with PRIMARY and
3188 * UNIQUE constraints, which are never functional indexes. But we
3189 * have to allocate enough space to keep parseOidArray from
3192 indxinfo[j].indkeys = (Oid *) malloc(INDEX_MAX_KEYS * sizeof(Oid));
3193 parseOidArray(PQgetvalue(res, j, i_indkey),
3194 indxinfo[j].indkeys, INDEX_MAX_KEYS);
3195 indxinfo[j].indisclustered = (PQgetvalue(res, j, i_indisclustered)[0] == 't');
3196 contype = *(PQgetvalue(res, j, i_contype));
3198 if (contype == 'p' || contype == 'u')
3201 * If we found a constraint matching the index, create an
3204 * In a pre-7.3 database, we take this path iff the index was
3205 * marked indisprimary.
3207 constrinfo[j].dobj.objType = DO_CONSTRAINT;
3208 constrinfo[j].dobj.catId.tableoid = atooid(PQgetvalue(res, j, i_contableoid));
3209 constrinfo[j].dobj.catId.oid = atooid(PQgetvalue(res, j, i_conoid));
3210 AssignDumpId(&constrinfo[j].dobj);
3211 constrinfo[j].dobj.name = strdup(PQgetvalue(res, j, i_conname));
3212 constrinfo[j].dobj.namespace = tbinfo->dobj.namespace;
3213 constrinfo[j].contable = tbinfo;
3214 constrinfo[j].condomain = NULL;
3215 constrinfo[j].contype = contype;
3216 constrinfo[j].condef = NULL;
3217 constrinfo[j].conindex = indxinfo[j].dobj.dumpId;
3218 constrinfo[j].coninherited = false;
3219 constrinfo[j].separate = true;
3221 indxinfo[j].indexconstraint = constrinfo[j].dobj.dumpId;
3223 /* If pre-7.3 DB, better make sure table comes first */
3224 addObjectDependency(&constrinfo[j].dobj,
3225 tbinfo->dobj.dumpId);
3229 /* Plain secondary index */
3230 indxinfo[j].indexconstraint = 0;
3237 destroyPQExpBuffer(query);
3243 * Get info about constraints on dumpable tables.
3245 * Currently handles foreign keys only.
3246 * Unique and primary key constraints are handled with indexes,
3247 * while check constraints are processed in getTableAttrs().
3250 getConstraints(TableInfo tblinfo[], int numTables)
3254 ConstraintInfo *constrinfo;
3263 /* pg_constraint was created in 7.3, so nothing to do if older */
3264 if (g_fout->remoteVersion < 70300)
3267 query = createPQExpBuffer();
3269 for (i = 0; i < numTables; i++)
3271 TableInfo *tbinfo = &tblinfo[i];
3273 if (tbinfo->ntrig == 0 || !tbinfo->dobj.dump)
3277 write_msg(NULL, "reading foreign key constraints for table \"%s\"\n",
3281 * select table schema to ensure constraint expr is qualified if
3284 selectSourceSchema(tbinfo->dobj.namespace->dobj.name);
3286 resetPQExpBuffer(query);
3287 appendPQExpBuffer(query,
3288 "SELECT tableoid, oid, conname, "
3289 "pg_catalog.pg_get_constraintdef(oid) as condef "
3290 "FROM pg_catalog.pg_constraint "
3291 "WHERE conrelid = '%u'::pg_catalog.oid "
3292 "AND contype = 'f'",
3293 tbinfo->dobj.catId.oid);
3294 res = PQexec(g_conn, query->data);
3295 check_sql_result(res, g_conn, query->data, PGRES_TUPLES_OK);
3297 ntups = PQntuples(res);
3299 i_contableoid = PQfnumber(res, "tableoid");
3300 i_conoid = PQfnumber(res, "oid");
3301 i_conname = PQfnumber(res, "conname");
3302 i_condef = PQfnumber(res, "condef");
3304 constrinfo = (ConstraintInfo *) malloc(ntups * sizeof(ConstraintInfo));
3306 for (j = 0; j < ntups; j++)
3308 constrinfo[j].dobj.objType = DO_FK_CONSTRAINT;
3309 constrinfo[j].dobj.catId.tableoid = atooid(PQgetvalue(res, j, i_contableoid));
3310 constrinfo[j].dobj.catId.oid = atooid(PQgetvalue(res, j, i_conoid));
3311 AssignDumpId(&constrinfo[j].dobj);
3312 constrinfo[j].dobj.name = strdup(PQgetvalue(res, j, i_conname));
3313 constrinfo[j].dobj.namespace = tbinfo->dobj.namespace;
3314 constrinfo[j].contable = tbinfo;
3315 constrinfo[j].condomain = NULL;
3316 constrinfo[j].contype = 'f';
3317 constrinfo[j].condef = strdup(PQgetvalue(res, j, i_condef));
3318 constrinfo[j].conindex = 0;
3319 constrinfo[j].coninherited = false;
3320 constrinfo[j].separate = true;
3326 destroyPQExpBuffer(query);
3330 * getDomainConstraints
3332 * Get info about constraints on a domain.
3335 getDomainConstraints(TypeInfo *tinfo)
3338 ConstraintInfo *constrinfo;
3347 /* pg_constraint was created in 7.3, so nothing to do if older */
3348 if (g_fout->remoteVersion < 70300)
3352 * select appropriate schema to ensure names in constraint are properly
3355 selectSourceSchema(tinfo->dobj.namespace->dobj.name);
3357 query = createPQExpBuffer();
3359 if (g_fout->remoteVersion >= 70400)
3360 appendPQExpBuffer(query, "SELECT tableoid, oid, conname, "
3361 "pg_catalog.pg_get_constraintdef(oid) AS consrc "
3362 "FROM pg_catalog.pg_constraint "
3363 "WHERE contypid = '%u'::pg_catalog.oid "
3365 tinfo->dobj.catId.oid);
3367 appendPQExpBuffer(query, "SELECT tableoid, oid, conname, "
3368 "'CHECK (' || consrc || ')' AS consrc "
3369 "FROM pg_catalog.pg_constraint "
3370 "WHERE contypid = '%u'::pg_catalog.oid "
3372 tinfo->dobj.catId.oid);
3374 res = PQexec(g_conn, query->data);
3375 check_sql_result(res, g_conn, query->data, PGRES_TUPLES_OK);
3377 ntups = PQntuples(res);
3379 i_tableoid = PQfnumber(res, "tableoid");
3380 i_oid = PQfnumber(res, "oid");
3381 i_conname = PQfnumber(res, "conname");
3382 i_consrc = PQfnumber(res, "consrc");
3384 constrinfo = (ConstraintInfo *) malloc(ntups * sizeof(ConstraintInfo));
3386 tinfo->nDomChecks = ntups;
3387 tinfo->domChecks = constrinfo;
3389 for (i = 0; i < ntups; i++)
3391 constrinfo[i].dobj.objType = DO_CONSTRAINT;
3392 constrinfo[i].dobj.catId.tableoid = atooid(PQgetvalue(res, i, i_tableoid));
3393 constrinfo[i].dobj.catId.oid = atooid(PQgetvalue(res, i, i_oid));
3394 AssignDumpId(&constrinfo[i].dobj);
3395 constrinfo[i].dobj.name = strdup(PQgetvalue(res, i, i_conname));
3396 constrinfo[i].dobj.namespace = tinfo->dobj.namespace;
3397 constrinfo[i].contable = NULL;
3398 constrinfo[i].condomain = tinfo;
3399 constrinfo[i].contype = 'c';
3400 constrinfo[i].condef = strdup(PQgetvalue(res, i, i_consrc));
3401 constrinfo[i].conindex = 0;
3402 constrinfo[i].coninherited = false;
3403 constrinfo[i].separate = false;
3406 * Make the domain depend on the constraint, ensuring it won't be
3407 * output till any constraint dependencies are OK.
3409 addObjectDependency(&tinfo->dobj,
3410 constrinfo[i].dobj.dumpId);
3415 destroyPQExpBuffer(query);
3420 * get basic information about every rule in the system
3422 * numRules is set to the number of rules read in
3425 getRules(int *numRules)
3430 PQExpBuffer query = createPQExpBuffer();
3439 /* Make sure we are in proper schema */
3440 selectSourceSchema("pg_catalog");
3442 if (g_fout->remoteVersion >= 70100)
3444 appendPQExpBuffer(query, "SELECT "
3445 "tableoid, oid, rulename, "
3446 "ev_class as ruletable, ev_type, is_instead "
3452 appendPQExpBuffer(query, "SELECT "
3453 "(SELECT oid FROM pg_class WHERE relname = 'pg_rewrite') AS tableoid, "
3455 "ev_class as ruletable, ev_type, is_instead "
3460 res = PQexec(g_conn, query->data);
3461 check_sql_result(res, g_conn, query->data, PGRES_TUPLES_OK);
3463 ntups = PQntuples(res);
3467 ruleinfo = (RuleInfo *) malloc(ntups * sizeof(RuleInfo));
3469 i_tableoid = PQfnumber(res, "tableoid");
3470 i_oid = PQfnumber(res, "oid");
3471 i_rulename = PQfnumber(res, "rulename");
3472 i_ruletable = PQfnumber(res, "ruletable");
3473 i_ev_type = PQfnumber(res, "ev_type");
3474 i_is_instead = PQfnumber(res, "is_instead");
3476 for (i = 0; i < ntups; i++)
3480 ruleinfo[i].dobj.objType = DO_RULE;
3481 ruleinfo[i].dobj.catId.tableoid = atooid(PQgetvalue(res, i, i_tableoid));
3482 ruleinfo[i].dobj.catId.oid = atooid(PQgetvalue(res, i, i_oid));
3483 AssignDumpId(&ruleinfo[i].dobj);
3484 ruleinfo[i].dobj.name = strdup(PQgetvalue(res, i, i_rulename));
3485 ruletableoid = atooid(PQgetvalue(res, i, i_ruletable));
3486 ruleinfo[i].ruletable = findTableByOid(ruletableoid);
3487 if (ruleinfo[i].ruletable == NULL)
3489 write_msg(NULL, "failed sanity check, parent table OID %u of pg_rewrite entry OID %u not found\n",
3491 ruleinfo[i].dobj.catId.oid);
3494 ruleinfo[i].dobj.namespace = ruleinfo[i].ruletable->dobj.namespace;
3495 ruleinfo[i].dobj.dump = ruleinfo[i].ruletable->dobj.dump;
3496 ruleinfo[i].ev_type = *(PQgetvalue(res, i, i_ev_type));
3497 ruleinfo[i].is_instead = *(PQgetvalue(res, i, i_is_instead)) == 't';
3498 if (ruleinfo[i].ruletable)
3501 * If the table is a view, force its ON SELECT rule to be sorted
3502 * before the view itself --- this ensures that any dependencies
3503 * for the rule affect the table's positioning. Other rules are
3504 * forced to appear after their table.
3506 if (ruleinfo[i].ruletable->relkind == RELKIND_VIEW &&
3507 ruleinfo[i].ev_type == '1' && ruleinfo[i].is_instead)
3509 addObjectDependency(&ruleinfo[i].ruletable->dobj,
3510 ruleinfo[i].dobj.dumpId);
3511 /* We'll merge the rule into CREATE VIEW, if possible */
3512 ruleinfo[i].separate = false;
3516 addObjectDependency(&ruleinfo[i].dobj,
3517 ruleinfo[i].ruletable->dobj.dumpId);
3518 ruleinfo[i].separate = true;
3522 ruleinfo[i].separate = true;
3527 destroyPQExpBuffer(query);
3534 * get information about every trigger on a dumpable table
3536 * Note: trigger data is not returned directly to the caller, but it
3537 * does get entered into the DumpableObject tables.
3540 getTriggers(TableInfo tblinfo[], int numTables)
3544 PQExpBuffer query = createPQExpBuffer();
3546 TriggerInfo *tginfo;
3563 for (i = 0; i < numTables; i++)
3565 TableInfo *tbinfo = &tblinfo[i];
3567 if (tbinfo->ntrig == 0 || !tbinfo->dobj.dump)
3571 write_msg(NULL, "reading triggers for table \"%s\"\n",
3575 * select table schema to ensure regproc name is qualified if needed
3577 selectSourceSchema(tbinfo->dobj.namespace->dobj.name);
3579 resetPQExpBuffer(query);
3580 if (g_fout->remoteVersion >= 70300)
3583 * We ignore triggers that are tied to a foreign-key constraint
3585 appendPQExpBuffer(query,
3587 "tgfoid::pg_catalog.regproc as tgfname, "
3588 "tgtype, tgnargs, tgargs, tgenabled, "
3589 "tgisconstraint, tgconstrname, tgdeferrable, "
3590 "tgconstrrelid, tginitdeferred, tableoid, oid, "
3591 "tgconstrrelid::pg_catalog.regclass as tgconstrrelname "
3592 "from pg_catalog.pg_trigger t "
3593 "where tgrelid = '%u'::pg_catalog.oid "
3594 "and (not tgisconstraint "
3596 " (SELECT 1 FROM pg_catalog.pg_depend d "
3597 " JOIN pg_catalog.pg_constraint c ON (d.refclassid = c.tableoid AND d.refobjid = c.oid) "
3598 " WHERE d.classid = t.tableoid AND d.objid = t.oid AND d.deptype = 'i' AND c.contype = 'f'))",
3599 tbinfo->dobj.catId.oid);
3601 else if (g_fout->remoteVersion >= 70100)
3603 appendPQExpBuffer(query,
3604 "SELECT tgname, tgfoid::regproc as tgfname, "
3605 "tgtype, tgnargs, tgargs, tgenabled, "
3606 "tgisconstraint, tgconstrname, tgdeferrable, "
3607 "tgconstrrelid, tginitdeferred, tableoid, oid, "
3608 "(select relname from pg_class where oid = tgconstrrelid) "
3609 " as tgconstrrelname "
3611 "where tgrelid = '%u'::oid",
3612 tbinfo->dobj.catId.oid);
3616 appendPQExpBuffer(query,
3617 "SELECT tgname, tgfoid::regproc as tgfname, "
3618 "tgtype, tgnargs, tgargs, tgenabled, "
3619 "tgisconstraint, tgconstrname, tgdeferrable, "
3620 "tgconstrrelid, tginitdeferred, "
3621 "(SELECT oid FROM pg_class WHERE relname = 'pg_trigger') AS tableoid, "
3624 "(select relname from pg_class where oid = tgconstrrelid) "
3625 " as tgconstrrelname "
3627 "where tgrelid = '%u'::oid",
3628 tbinfo->dobj.catId.oid);
3630 res = PQexec(g_conn, query->data);
3631 check_sql_result(res, g_conn, query->data, PGRES_TUPLES_OK);
3633 ntups = PQntuples(res);
3636 * We may have less triggers than recorded due to having ignored
3637 * foreign-key triggers
3639 if (ntups > tbinfo->ntrig)
3641 write_msg(NULL, "expected %d triggers on table \"%s\" but found %d\n",
3642 tbinfo->ntrig, tbinfo->dobj.name, ntups);
3645 i_tableoid = PQfnumber(res, "tableoid");
3646 i_oid = PQfnumber(res, "oid");
3647 i_tgname = PQfnumber(res, "tgname");
3648 i_tgfname = PQfnumber(res, "tgfname");
3649 i_tgtype = PQfnumber(res, "tgtype");
3650 i_tgnargs = PQfnumber(res, "tgnargs");
3651 i_tgargs = PQfnumber(res, "tgargs");
3652 i_tgisconstraint = PQfnumber(res, "tgisconstraint");
3653 i_tgconstrname = PQfnumber(res, "tgconstrname");
3654 i_tgconstrrelid = PQfnumber(res, "tgconstrrelid");
3655 i_tgconstrrelname = PQfnumber(res, "tgconstrrelname");
3656 i_tgenabled = PQfnumber(res, "tgenabled");
3657 i_tgdeferrable = PQfnumber(res, "tgdeferrable");
3658 i_tginitdeferred = PQfnumber(res, "tginitdeferred");
3660 tginfo = (TriggerInfo *) malloc(ntups * sizeof(TriggerInfo));
3662 for (j = 0; j < ntups; j++)
3664 tginfo[j].dobj.objType = DO_TRIGGER;
3665 tginfo[j].dobj.catId.tableoid = atooid(PQgetvalue(res, j, i_tableoid));
3666 tginfo[j].dobj.catId.oid = atooid(PQgetvalue(res, j, i_oid));
3667 AssignDumpId(&tginfo[j].dobj);
3668 tginfo[j].dobj.name = strdup(PQgetvalue(res, j, i_tgname));
3669 tginfo[j].dobj.namespace = tbinfo->dobj.namespace;
3670 tginfo[j].tgtable = tbinfo;
3671 tginfo[j].tgfname = strdup(PQgetvalue(res, j, i_tgfname));
3672 tginfo[j].tgtype = atoi(PQgetvalue(res, j, i_tgtype));
3673 tginfo[j].tgnargs = atoi(PQgetvalue(res, j, i_tgnargs));
3674 tginfo[j].tgargs = strdup(PQgetvalue(res, j, i_tgargs));
3675 tginfo[j].tgisconstraint = *(PQgetvalue(res, j, i_tgisconstraint)) == 't';
3676 tginfo[j].tgenabled = *(PQgetvalue(res, j, i_tgenabled)) == 't';
3677 tginfo[j].tgdeferrable = *(PQgetvalue(res, j, i_tgdeferrable)) == 't';
3678 tginfo[j].tginitdeferred = *(PQgetvalue(res, j, i_tginitdeferred)) == 't';
3680 if (tginfo[j].tgisconstraint)
3682 tginfo[j].tgconstrname = strdup(PQgetvalue(res, j, i_tgconstrname));
3683 tginfo[j].tgconstrrelid = atooid(PQgetvalue(res, j, i_tgconstrrelid));
3684 if (OidIsValid(tginfo[j].tgconstrrelid))
3686 if (PQgetisnull(res, j, i_tgconstrrelname))
3688 write_msg(NULL, "query produced null referenced table name for foreign key trigger \"%s\" on table \"%s\" (OID of table: %u)\n",
3689 tginfo[j].dobj.name, tbinfo->dobj.name,
3690 tginfo[j].tgconstrrelid);
3693 tginfo[j].tgconstrrelname = strdup(PQgetvalue(res, j, i_tgconstrrelname));
3696 tginfo[j].tgconstrrelname = NULL;
3700 tginfo[j].tgconstrname = NULL;
3701 tginfo[j].tgconstrrelid = InvalidOid;
3702 tginfo[j].tgconstrrelname = NULL;
3709 destroyPQExpBuffer(query);
3714 * get basic information about every procedural language in the system
3716 * numProcLangs is set to the number of langs read in
3718 * NB: this must run after getFuncs() because we assume we can do
3722 getProcLangs(int *numProcLangs)
3727 PQExpBuffer query = createPQExpBuffer();
3728 ProcLangInfo *planginfo;
3733 int i_lanplcallfoid;
3738 /* Make sure we are in proper schema */
3739 selectSourceSchema("pg_catalog");
3741 if (g_fout->remoteVersion >= 80100)
3743 /* Languages are owned by the bootstrap superuser, OID 10 */
3744 appendPQExpBuffer(query, "SELECT tableoid, oid, *, "
3745 "(%s '10') as lanowner "
3751 else if (g_fout->remoteVersion >= 70400)
3753 /* Languages are owned by the bootstrap superuser, sysid 1 */
3754 appendPQExpBuffer(query, "SELECT tableoid, oid, *, "
3755 "(%s '1') as lanowner "
3761 else if (g_fout->remoteVersion >= 70100)
3763 /* No clear notion of an owner at all before 7.4 ... */
3764 appendPQExpBuffer(query, "SELECT tableoid, oid, * FROM pg_language "
3770 appendPQExpBuffer(query, "SELECT "
3771 "(SELECT oid FROM pg_class WHERE relname = 'pg_language') AS tableoid, "
3772 "oid, * FROM pg_language "
3777 res = PQexec(g_conn, query->data);
3778 check_sql_result(res, g_conn, query->data, PGRES_TUPLES_OK);
3780 ntups = PQntuples(res);
3782 *numProcLangs = ntups;
3784 planginfo = (ProcLangInfo *) malloc(ntups * sizeof(ProcLangInfo));
3786 i_tableoid = PQfnumber(res, "tableoid");
3787 i_oid = PQfnumber(res, "oid");
3788 i_lanname = PQfnumber(res, "lanname");
3789 i_lanpltrusted = PQfnumber(res, "lanpltrusted");
3790 i_lanplcallfoid = PQfnumber(res, "lanplcallfoid");
3791 /* these may fail and return -1: */
3792 i_lanvalidator = PQfnumber(res, "lanvalidator");
3793 i_lanacl = PQfnumber(res, "lanacl");
3794 i_lanowner = PQfnumber(res, "lanowner");
3796 for (i = 0; i < ntups; i++)
3798 planginfo[i].dobj.objType = DO_PROCLANG;
3799 planginfo[i].dobj.catId.tableoid = atooid(PQgetvalue(res, i, i_tableoid));
3800 planginfo[i].dobj.catId.oid = atooid(PQgetvalue(res, i, i_oid));
3801 AssignDumpId(&planginfo[i].dobj);
3803 planginfo[i].dobj.name = strdup(PQgetvalue(res, i, i_lanname));
3804 planginfo[i].lanpltrusted = *(PQgetvalue(res, i, i_lanpltrusted)) == 't';
3805 planginfo[i].lanplcallfoid = atooid(PQgetvalue(res, i, i_lanplcallfoid));
3806 if (i_lanvalidator >= 0)
3807 planginfo[i].lanvalidator = atooid(PQgetvalue(res, i, i_lanvalidator));
3809 planginfo[i].lanvalidator = InvalidOid;
3811 planginfo[i].lanacl = strdup(PQgetvalue(res, i, i_lanacl));
3813 planginfo[i].lanacl = strdup("{=U}");
3814 if (i_lanowner >= 0)
3815 planginfo[i].lanowner = strdup(PQgetvalue(res, i, i_lanowner));
3817 planginfo[i].lanowner = strdup("");
3819 if (g_fout->remoteVersion < 70300)
3822 * We need to make a dependency to ensure the function will be
3823 * dumped first. (In 7.3 and later the regular dependency
3824 * mechanism will handle this for us.)
3826 FuncInfo *funcInfo = findFuncByOid(planginfo[i].lanplcallfoid);
3829 addObjectDependency(&planginfo[i].dobj,
3830 funcInfo->dobj.dumpId);
3836 destroyPQExpBuffer(query);
3843 * get basic information about every cast in the system
3845 * numCasts is set to the number of casts read in
3848 getCasts(int *numCasts)
3853 PQExpBuffer query = createPQExpBuffer();
3862 /* Make sure we are in proper schema */
3863 selectSourceSchema("pg_catalog");
3865 if (g_fout->remoteVersion >= 70300)
3867 appendPQExpBuffer(query, "SELECT tableoid, oid, "
3868 "castsource, casttarget, castfunc, castcontext "
3869 "FROM pg_cast ORDER BY 3,4");
3873 appendPQExpBuffer(query, "SELECT 0 as tableoid, p.oid, "
3874 "t1.oid as castsource, t2.oid as casttarget, "
3875 "p.oid as castfunc, 'e' as castcontext "
3876 "FROM pg_type t1, pg_type t2, pg_proc p "
3877 "WHERE p.pronargs = 1 AND "
3878 "p.proargtypes[0] = t1.oid AND "
3879 "p.prorettype = t2.oid AND p.proname = t2.typname "
3883 res = PQexec(g_conn, query->data);
3884 check_sql_result(res, g_conn, query->data, PGRES_TUPLES_OK);
3886 ntups = PQntuples(res);
3890 castinfo = (CastInfo *) malloc(ntups * sizeof(CastInfo));
3892 i_tableoid = PQfnumber(res, "tableoid");
3893 i_oid = PQfnumber(res, "oid");
3894 i_castsource = PQfnumber(res, "castsource");
3895 i_casttarget = PQfnumber(res, "casttarget");
3896 i_castfunc = PQfnumber(res, "castfunc");
3897 i_castcontext = PQfnumber(res, "castcontext");
3899 for (i = 0; i < ntups; i++)
3901 PQExpBufferData namebuf;
3902 TypeInfo *sTypeInfo;
3903 TypeInfo *tTypeInfo;
3905 castinfo[i].dobj.objType = DO_CAST;
3906 castinfo[i].dobj.catId.tableoid = atooid(PQgetvalue(res, i, i_tableoid));
3907 castinfo[i].dobj.catId.oid = atooid(PQgetvalue(res, i, i_oid));
3908 AssignDumpId(&castinfo[i].dobj);
3909 castinfo[i].castsource = atooid(PQgetvalue(res, i, i_castsource));
3910 castinfo[i].casttarget = atooid(PQgetvalue(res, i, i_casttarget));
3911 castinfo[i].castfunc = atooid(PQgetvalue(res, i, i_castfunc));
3912 castinfo[i].castcontext = *(PQgetvalue(res, i, i_castcontext));
3915 * Try to name cast as concatenation of typnames. This is only used
3916 * for purposes of sorting. If we fail to find either type, the name
3917 * will be an empty string.
3919 initPQExpBuffer(&namebuf);
3920 sTypeInfo = findTypeByOid(castinfo[i].castsource);
3921 tTypeInfo = findTypeByOid(castinfo[i].casttarget);
3922 if (sTypeInfo && tTypeInfo)
3923 appendPQExpBuffer(&namebuf, "%s %s",
3924 sTypeInfo->dobj.name, tTypeInfo->dobj.name);
3925 castinfo[i].dobj.name = namebuf.data;
3927 if (OidIsValid(castinfo[i].castfunc))
3930 * We need to make a dependency to ensure the function will be
3931 * dumped first. (In 7.3 and later the regular dependency
3932 * mechanism will handle this for us.)
3936 funcInfo = findFuncByOid(castinfo[i].castfunc);
3938 addObjectDependency(&castinfo[i].dobj,
3939 funcInfo->dobj.dumpId);
3945 destroyPQExpBuffer(query);
3952 * for each interesting table, read info about its attributes
3953 * (names, types, default values, CHECK constraints, etc)
3955 * This is implemented in a very inefficient way right now, looping
3956 * through the tblinfo and doing a join per table to find the attrs and their
3957 * types. However, because we want type names and so forth to be named
3958 * relative to the schema of each table, we couldn't do it in just one
3959 * query. (Maybe one query per schema?)
3964 getTableAttrs(TableInfo *tblinfo, int numTables)
3969 PQExpBuffer q = createPQExpBuffer();
3974 int i_attstattarget;
3985 for (i = 0; i < numTables; i++)
3987 TableInfo *tbinfo = &tblinfo[i];
3989 /* Don't bother to collect info for sequences */
3990 if (tbinfo->relkind == RELKIND_SEQUENCE)
3993 /* Don't bother with uninteresting tables, either */
3994 if (!tbinfo->interesting)
3998 * Make sure we are in proper schema for this table; this allows
3999 * correct retrieval of formatted type names and default exprs
4001 selectSourceSchema(tbinfo->dobj.namespace->dobj.name);
4003 /* find all the user attributes and their types */
4006 * we must read the attribute names in attribute number order! because
4007 * we will use the attnum to index into the attnames array later. We
4008 * actually ask to order by "attrelid, attnum" because (at least up to
4009 * 7.3) the planner is not smart enough to realize it needn't re-sort
4010 * the output of an indexscan on pg_attribute_relid_attnum_index.
4013 write_msg(NULL, "finding the columns and types of table \"%s\"\n",
4016 resetPQExpBuffer(q);
4018 if (g_fout->remoteVersion >= 70300)
4020 /* need left join here to not fail on dropped columns ... */
4021 appendPQExpBuffer(q, "SELECT a.attnum, a.attname, a.atttypmod, a.attstattarget, a.attstorage, t.typstorage, "
4022 "a.attnotnull, a.atthasdef, a.attisdropped, a.attislocal, "
4023 "pg_catalog.format_type(t.oid,a.atttypmod) as atttypname "
4024 "from pg_catalog.pg_attribute a left join pg_catalog.pg_type t "
4025 "on a.atttypid = t.oid "
4026 "where a.attrelid = '%u'::pg_catalog.oid "
4027 "and a.attnum > 0::pg_catalog.int2 "
4028 "order by a.attrelid, a.attnum",
4029 tbinfo->dobj.catId.oid);
4031 else if (g_fout->remoteVersion >= 70100)
4034 * attstattarget doesn't exist in 7.1. It does exist in 7.2, but
4035 * we don't dump it because we can't tell whether it's been
4036 * explicitly set or was just a default.
4038 appendPQExpBuffer(q, "SELECT a.attnum, a.attname, a.atttypmod, -1 as attstattarget, a.attstorage, t.typstorage, "
4039 "a.attnotnull, a.atthasdef, false as attisdropped, false as attislocal, "
4040 "format_type(t.oid,a.atttypmod) as atttypname "
4041 "from pg_attribute a left join pg_type t "
4042 "on a.atttypid = t.oid "
4043 "where a.attrelid = '%u'::oid "
4044 "and a.attnum > 0::int2 "
4045 "order by a.attrelid, a.attnum",
4046 tbinfo->dobj.catId.oid);
4050 /* format_type not available before 7.1 */
4051 appendPQExpBuffer(q, "SELECT attnum, attname, atttypmod, -1 as attstattarget, attstorage, attstorage as typstorage, "
4052 "attnotnull, atthasdef, false as attisdropped, false as attislocal, "
4053 "(select typname from pg_type where oid = atttypid) as atttypname "
4054 "from pg_attribute a "
4055 "where attrelid = '%u'::oid "
4056 "and attnum > 0::int2 "
4057 "order by attrelid, attnum",
4058 tbinfo->dobj.catId.oid);
4061 res = PQexec(g_conn, q->data);
4062 check_sql_result(res, g_conn, q->data, PGRES_TUPLES_OK);
4064 ntups = PQntuples(res);
4066 i_attnum = PQfnumber(res, "attnum");
4067 i_attname = PQfnumber(res, "attname");
4068 i_atttypname = PQfnumber(res, "atttypname");
4069 i_atttypmod = PQfnumber(res, "atttypmod");
4070 i_attstattarget = PQfnumber(res, "attstattarget");
4071 i_attstorage = PQfnumber(res, "attstorage");
4072 i_typstorage = PQfnumber(res, "typstorage");
4073 i_attnotnull = PQfnumber(res, "attnotnull");
4074 i_atthasdef = PQfnumber(res, "atthasdef");
4075 i_attisdropped = PQfnumber(res, "attisdropped");
4076 i_attislocal = PQfnumber(res, "attislocal");
4078 tbinfo->numatts = ntups;
4079 tbinfo->attnames = (char **) malloc(ntups * sizeof(char *));
4080 tbinfo->atttypnames = (char **) malloc(ntups * sizeof(char *));
4081 tbinfo->atttypmod = (int *) malloc(ntups * sizeof(int));
4082 tbinfo->attstattarget = (int *) malloc(ntups * sizeof(int));
4083 tbinfo->attstorage = (char *) malloc(ntups * sizeof(char));
4084 tbinfo->typstorage = (char *) malloc(ntups * sizeof(char));
4085 tbinfo->attisdropped = (bool *) malloc(ntups * sizeof(bool));
4086 tbinfo->attislocal = (bool *) malloc(ntups * sizeof(bool));
4087 tbinfo->attisserial = (bool *) malloc(ntups * sizeof(bool));
4088 tbinfo->notnull = (bool *) malloc(ntups * sizeof(bool));
4089 tbinfo->attrdefs = (AttrDefInfo **) malloc(ntups * sizeof(AttrDefInfo *));
4090 tbinfo->inhAttrs = (bool *) malloc(ntups * sizeof(bool));
4091 tbinfo->inhAttrDef = (bool *) malloc(ntups * sizeof(bool));
4092 tbinfo->inhNotNull = (bool *) malloc(ntups * sizeof(bool));
4093 hasdefaults = false;
4095 for (j = 0; j < ntups; j++)
4097 if (j + 1 != atoi(PQgetvalue(res, j, i_attnum)))
4099 write_msg(NULL, "invalid column numbering in table \"%s\"\n",
4103 tbinfo->attnames[j] = strdup(PQgetvalue(res, j, i_attname));
4104 tbinfo->atttypnames[j] = strdup(PQgetvalue(res, j, i_atttypname));
4105 tbinfo->atttypmod[j] = atoi(PQgetvalue(res, j, i_atttypmod));
4106 tbinfo->attstattarget[j] = atoi(PQgetvalue(res, j, i_attstattarget));
4107 tbinfo->attstorage[j] = *(PQgetvalue(res, j, i_attstorage));
4108 tbinfo->typstorage[j] = *(PQgetvalue(res, j, i_typstorage));
4109 tbinfo->attisdropped[j] = (PQgetvalue(res, j, i_attisdropped)[0] == 't');
4110 tbinfo->attislocal[j] = (PQgetvalue(res, j, i_attislocal)[0] == 't');
4111 tbinfo->attisserial[j] = false; /* fix below */
4112 tbinfo->notnull[j] = (PQgetvalue(res, j, i_attnotnull)[0] == 't');
4113 tbinfo->attrdefs[j] = NULL; /* fix below */
4114 if (PQgetvalue(res, j, i_atthasdef)[0] == 't')
4116 /* these flags will be set in flagInhAttrs() */
4117 tbinfo->inhAttrs[j] = false;
4118 tbinfo->inhAttrDef[j] = false;
4119 tbinfo->inhNotNull[j] = false;
4125 * Get info about column defaults
4129 AttrDefInfo *attrdefs;
4133 write_msg(NULL, "finding default expressions of table \"%s\"\n",
4136 resetPQExpBuffer(q);
4137 if (g_fout->remoteVersion >= 70300)
4139 appendPQExpBuffer(q, "SELECT tableoid, oid, adnum, "
4140 "pg_catalog.pg_get_expr(adbin, adrelid) AS adsrc "
4141 "FROM pg_catalog.pg_attrdef "
4142 "WHERE adrelid = '%u'::pg_catalog.oid",
4143 tbinfo->dobj.catId.oid);
4145 else if (g_fout->remoteVersion >= 70200)
4147 /* 7.2 did not have OIDs in pg_attrdef */
4148 appendPQExpBuffer(q, "SELECT tableoid, 0 as oid, adnum, "
4149 "pg_get_expr(adbin, adrelid) AS adsrc "
4151 "WHERE adrelid = '%u'::oid",
4152 tbinfo->dobj.catId.oid);
4154 else if (g_fout->remoteVersion >= 70100)
4156 /* no pg_get_expr, so must rely on adsrc */
4157 appendPQExpBuffer(q, "SELECT tableoid, oid, adnum, adsrc "
4159 "WHERE adrelid = '%u'::oid",
4160 tbinfo->dobj.catId.oid);
4164 /* no pg_get_expr, no tableoid either */
4165 appendPQExpBuffer(q, "SELECT "
4166 "(SELECT oid FROM pg_class WHERE relname = 'pg_attrdef') AS tableoid, "
4167 "oid, adnum, adsrc "
4169 "WHERE adrelid = '%u'::oid",
4170 tbinfo->dobj.catId.oid);
4172 res = PQexec(g_conn, q->data);
4173 check_sql_result(res, g_conn, q->data, PGRES_TUPLES_OK);
4175 numDefaults = PQntuples(res);
4176 attrdefs = (AttrDefInfo *) malloc(numDefaults * sizeof(AttrDefInfo));
4178 for (j = 0; j < numDefaults; j++)
4182 attrdefs[j].dobj.objType = DO_ATTRDEF;
4183 attrdefs[j].dobj.catId.tableoid = atooid(PQgetvalue(res, j, 0));
4184 attrdefs[j].dobj.catId.oid = atooid(PQgetvalue(res, j, 1));
4185 AssignDumpId(&attrdefs[j].dobj);
4186 attrdefs[j].adtable = tbinfo;
4187 attrdefs[j].adnum = adnum = atoi(PQgetvalue(res, j, 2));
4188 attrdefs[j].adef_expr = strdup(PQgetvalue(res, j, 3));
4190 attrdefs[j].dobj.name = strdup(tbinfo->dobj.name);
4191 attrdefs[j].dobj.namespace = tbinfo->dobj.namespace;
4193 attrdefs[j].dobj.dump = tbinfo->dobj.dump;
4196 * Defaults on a VIEW must always be dumped as separate ALTER
4197 * TABLE commands. Defaults on regular tables are dumped as
4198 * part of the CREATE TABLE if possible. To check if it's
4199 * safe, we mark the default as needing to appear before the
4202 if (tbinfo->relkind == RELKIND_VIEW)
4204 attrdefs[j].separate = true;
4205 /* needed in case pre-7.3 DB: */
4206 addObjectDependency(&attrdefs[j].dobj,
4207 tbinfo->dobj.dumpId);
4211 attrdefs[j].separate = false;
4212 addObjectDependency(&tbinfo->dobj,
4213 attrdefs[j].dobj.dumpId);
4216 if (adnum <= 0 || adnum > ntups)
4218 write_msg(NULL, "invalid adnum value %d for table \"%s\"\n",
4219 adnum, tbinfo->dobj.name);
4222 tbinfo->attrdefs[adnum - 1] = &attrdefs[j];
4228 * Get info about table CHECK constraints
4230 if (tbinfo->ncheck > 0)
4232 ConstraintInfo *constrs;
4236 write_msg(NULL, "finding check constraints for table \"%s\"\n",
4239 resetPQExpBuffer(q);
4240 if (g_fout->remoteVersion >= 70400)
4242 appendPQExpBuffer(q, "SELECT tableoid, oid, conname, "
4243 "pg_catalog.pg_get_constraintdef(oid) AS consrc "
4244 "FROM pg_catalog.pg_constraint "
4245 "WHERE conrelid = '%u'::pg_catalog.oid "
4246 " AND contype = 'c' "
4248 tbinfo->dobj.catId.oid);
4250 else if (g_fout->remoteVersion >= 70300)
4252 /* no pg_get_constraintdef, must use consrc */
4253 appendPQExpBuffer(q, "SELECT tableoid, oid, conname, "
4254 "'CHECK (' || consrc || ')' AS consrc "
4255 "FROM pg_catalog.pg_constraint "
4256 "WHERE conrelid = '%u'::pg_catalog.oid "
4257 " AND contype = 'c' "
4259 tbinfo->dobj.catId.oid);
4261 else if (g_fout->remoteVersion >= 70200)
4263 /* 7.2 did not have OIDs in pg_relcheck */
4264 appendPQExpBuffer(q, "SELECT tableoid, 0 as oid, "
4265 "rcname AS conname, "
4266 "'CHECK (' || rcsrc || ')' AS consrc "
4268 "WHERE rcrelid = '%u'::oid "
4270 tbinfo->dobj.catId.oid);
4272 else if (g_fout->remoteVersion >= 70100)
4274 appendPQExpBuffer(q, "SELECT tableoid, oid, "
4275 "rcname AS conname, "
4276 "'CHECK (' || rcsrc || ')' AS consrc "
4278 "WHERE rcrelid = '%u'::oid "
4280 tbinfo->dobj.catId.oid);
4284 /* no tableoid in 7.0 */
4285 appendPQExpBuffer(q, "SELECT "
4286 "(SELECT oid FROM pg_class WHERE relname = 'pg_relcheck') AS tableoid, "
4287 "oid, rcname AS conname, "
4288 "'CHECK (' || rcsrc || ')' AS consrc "
4290 "WHERE rcrelid = '%u'::oid "
4292 tbinfo->dobj.catId.oid);
4294 res = PQexec(g_conn, q->data);
4295 check_sql_result(res, g_conn, q->data, PGRES_TUPLES_OK);
4297 numConstrs = PQntuples(res);
4298 if (numConstrs != tbinfo->ncheck)
4300 write_msg(NULL, "expected %d check constraints on table \"%s\" but found %d\n",
4301 tbinfo->ncheck, tbinfo->dobj.name, numConstrs);
4302 write_msg(NULL, "(The system catalogs might be corrupted.)\n");
4306 constrs = (ConstraintInfo *) malloc(numConstrs * sizeof(ConstraintInfo));
4307 tbinfo->checkexprs = constrs;
4309 for (j = 0; j < numConstrs; j++)
4311 constrs[j].dobj.objType = DO_CONSTRAINT;
4312 constrs[j].dobj.catId.tableoid = atooid(PQgetvalue(res, j, 0));
4313 constrs[j].dobj.catId.oid = atooid(PQgetvalue(res, j, 1));
4314 AssignDumpId(&constrs[j].dobj);
4315 constrs[j].dobj.name = strdup(PQgetvalue(res, j, 2));
4316 constrs[j].dobj.namespace = tbinfo->dobj.namespace;
4317 constrs[j].contable = tbinfo;
4318 constrs[j].condomain = NULL;
4319 constrs[j].contype = 'c';
4320 constrs[j].condef = strdup(PQgetvalue(res, j, 3));
4321 constrs[j].conindex = 0;
4322 constrs[j].coninherited = false;
4323 constrs[j].separate = false;
4325 constrs[j].dobj.dump = tbinfo->dobj.dump;
4328 * Mark the constraint as needing to appear before the table
4329 * --- this is so that any other dependencies of the
4330 * constraint will be emitted before we try to create the
4333 addObjectDependency(&tbinfo->dobj,
4334 constrs[j].dobj.dumpId);
4337 * If the constraint is inherited, this will be detected
4338 * later. We also detect later if the constraint must be
4339 * split out from the table definition.
4346 * Check to see if any columns are serial columns. Our first quick
4347 * filter is that it must be integer or bigint with a default. If so,
4348 * we scan to see if we found a sequence linked to this column. If we
4349 * did, mark the column and sequence appropriately.
4351 for (j = 0; j < ntups; j++)
4354 * Note assumption that format_type will show these types as
4355 * exactly "integer" and "bigint" regardless of schema path. This
4356 * is correct in 7.3 but needs to be watched.
4358 if (strcmp(tbinfo->atttypnames[j], "integer") != 0 &&
4359 strcmp(tbinfo->atttypnames[j], "bigint") != 0)
4361 if (tbinfo->attrdefs[j] == NULL)
4363 for (k = 0; k < numTables; k++)
4365 TableInfo *seqinfo = &tblinfo[k];
4367 if (OidIsValid(seqinfo->owning_tab) &&
4368 seqinfo->owning_tab == tbinfo->dobj.catId.oid &&
4369 seqinfo->owning_col == j + 1)
4372 * Found a match. Copy the table's interesting and
4373 * dumpable flags to the sequence.
4375 tbinfo->attisserial[j] = true;
4376 seqinfo->interesting = tbinfo->interesting;
4377 seqinfo->dobj.dump = tbinfo->dobj.dump;
4384 destroyPQExpBuffer(q);
4391 * This routine is used to dump any comments associated with the
4392 * object handed to this routine. The routine takes a constant character
4393 * string for the target part of the comment-creation command, plus
4394 * the namespace and owner of the object (for labeling the ArchiveEntry),
4395 * plus catalog ID and subid which are the lookup key for pg_description,
4396 * plus the dump ID for the object (for setting a dependency).
4397 * If a matching pg_description entry is found, it is dumped.
4399 * Note: although this routine takes a dumpId for dependency purposes,
4400 * that purpose is just to mark the dependency in the emitted dump file
4401 * for possible future use by pg_restore. We do NOT use it for determining
4402 * ordering of the comment in the dump file, because this routine is called
4403 * after dependency sorting occurs. This routine should be called just after
4404 * calling ArchiveEntry() for the specified object.
4407 dumpComment(Archive *fout, const char *target,
4408 const char *namespace, const char *owner,
4409 CatalogId catalogId, int subid, DumpId dumpId)
4411 CommentItem *comments;
4414 /* Comments are SCHEMA not data */
4418 /* Search for comments associated with catalogId, using table */
4419 ncomments = findComments(fout, catalogId.tableoid, catalogId.oid,
4422 /* Is there one matching the subid? */
4423 while (ncomments > 0)
4425 if (comments->objsubid == subid)
4431 /* If a comment exists, build COMMENT ON statement */
4434 PQExpBuffer query = createPQExpBuffer();
4436 appendPQExpBuffer(query, "COMMENT ON %s IS ", target);
4437 appendStringLiteralAH(query, comments->descr, fout);
4438 appendPQExpBuffer(query, ";\n");
4440 ArchiveEntry(fout, nilCatalogId, createDumpId(),
4441 target, namespace, NULL, owner, false,
4442 "COMMENT", query->data, "", NULL,
4446 destroyPQExpBuffer(query);
4451 * dumpTableComment --
4453 * As above, but dump comments for both the specified table (or view)
4457 dumpTableComment(Archive *fout, TableInfo *tbinfo,
4458 const char *reltypename)
4460 CommentItem *comments;
4465 /* Comments are SCHEMA not data */
4469 /* Search for comments associated with relation, using table */
4470 ncomments = findComments(fout,
4471 tbinfo->dobj.catId.tableoid,
4472 tbinfo->dobj.catId.oid,
4475 /* If comments exist, build COMMENT ON statements */
4479 query = createPQExpBuffer();
4480 target = createPQExpBuffer();
4482 while (ncomments > 0)
4484 const char *descr = comments->descr;
4485 int objsubid = comments->objsubid;
4489 resetPQExpBuffer(target);
4490 appendPQExpBuffer(target, "%s %s", reltypename,
4491 fmtId(tbinfo->dobj.name));
4493 resetPQExpBuffer(query);
4494 appendPQExpBuffer(query, "COMMENT ON %s IS ", target->data);
4495 appendStringLiteralAH(query, descr, fout);
4496 appendPQExpBuffer(query, ";\n");
4498 ArchiveEntry(fout, nilCatalogId, createDumpId(),
4500 tbinfo->dobj.namespace->dobj.name,
4503 false, "COMMENT", query->data, "", NULL,
4504 &(tbinfo->dobj.dumpId), 1,
4507 else if (objsubid > 0 && objsubid <= tbinfo->numatts)
4509 resetPQExpBuffer(target);
4510 appendPQExpBuffer(target, "COLUMN %s.",
4511 fmtId(tbinfo->dobj.name));
4512 appendPQExpBuffer(target, "%s",
4513 fmtId(tbinfo->attnames[objsubid - 1]));
4515 resetPQExpBuffer(query);
4516 appendPQExpBuffer(query, "COMMENT ON %s IS ", target->data);
4517 appendStringLiteralAH(query, descr, fout);
4518 appendPQExpBuffer(query, ";\n");
4520 ArchiveEntry(fout, nilCatalogId, createDumpId(),
4522 tbinfo->dobj.namespace->dobj.name,
4525 false, "COMMENT", query->data, "", NULL,
4526 &(tbinfo->dobj.dumpId), 1,
4534 destroyPQExpBuffer(query);
4535 destroyPQExpBuffer(target);
4541 * Find the comment(s), if any, associated with the given object. All the
4542 * objsubid values associated with the given classoid/objoid are found with
4546 findComments(Archive *fout, Oid classoid, Oid objoid,
4547 CommentItem **items)
4549 /* static storage for table of comments */
4550 static CommentItem *comments = NULL;
4551 static int ncomments = -1;
4553 CommentItem *middle = NULL;
4558 /* Get comments if we didn't already */
4560 ncomments = collectComments(fout, &comments);
4563 * Pre-7.2, pg_description does not contain classoid, so collectComments
4564 * just stores a zero. If there's a collision on object OID, well, you
4565 * get duplicate comments.
4567 if (fout->remoteVersion < 70200)
4571 * Do binary search to find some item matching the object.
4574 high = &comments[ncomments - 1];
4577 middle = low + (high - low) / 2;
4579 if (classoid < middle->classoid)
4581 else if (classoid > middle->classoid)
4583 else if (objoid < middle->objoid)
4585 else if (objoid > middle->objoid)
4588 break; /* found a match */
4591 if (low > high) /* no matches */
4598 * Now determine how many items match the object. The search loop
4599 * invariant still holds: only items between low and high inclusive could
4603 while (middle > low)
4605 if (classoid != middle[-1].classoid ||
4606 objoid != middle[-1].objoid)
4615 while (middle <= high)
4617 if (classoid != middle->classoid ||
4618 objoid != middle->objoid)
4628 * collectComments --
4630 * Construct a table of all comments available for database objects.
4631 * We used to do per-object queries for the comments, but it's much faster
4632 * to pull them all over at once, and on most databases the memory cost
4635 * The table is sorted by classoid/objid/objsubid for speed in lookup.
4638 collectComments(Archive *fout, CommentItem **items)
4648 CommentItem *comments;
4651 * Note we do NOT change source schema here; preserve the caller's
4655 query = createPQExpBuffer();
4657 if (fout->remoteVersion >= 70300)
4659 appendPQExpBuffer(query, "SELECT description, classoid, objoid, objsubid "
4660 "FROM pg_catalog.pg_description "
4661 "ORDER BY classoid, objoid, objsubid");
4663 else if (fout->remoteVersion >= 70200)
4665 appendPQExpBuffer(query, "SELECT description, classoid, objoid, objsubid "
4666 "FROM pg_description "
4667 "ORDER BY classoid, objoid, objsubid");
4671 /* Note: this will fail to find attribute comments in pre-7.2... */
4672 appendPQExpBuffer(query, "SELECT description, 0 as classoid, objoid, 0 as objsubid "
4673 "FROM pg_description "
4677 res = PQexec(g_conn, query->data);
4678 check_sql_result(res, g_conn, query->data, PGRES_TUPLES_OK);
4680 /* Construct lookup table containing OIDs in numeric form */
4682 i_description = PQfnumber(res, "description");
4683 i_classoid = PQfnumber(res, "classoid");
4684 i_objoid = PQfnumber(res, "objoid");
4685 i_objsubid = PQfnumber(res, "objsubid");
4687 ntups = PQntuples(res);
4689 comments = (CommentItem *) malloc(ntups * sizeof(CommentItem));
4691 for (i = 0; i < ntups; i++)
4693 comments[i].descr = PQgetvalue(res, i, i_description);
4694 comments[i].classoid = atooid(PQgetvalue(res, i, i_classoid));
4695 comments[i].objoid = atooid(PQgetvalue(res, i, i_objoid));
4696 comments[i].objsubid = atoi(PQgetvalue(res, i, i_objsubid));
4699 /* Do NOT free the PGresult since we are keeping pointers into it */
4700 destroyPQExpBuffer(query);
4707 * dumpDumpableObject
4709 * This routine and its subsidiaries are responsible for creating
4710 * ArchiveEntries (TOC objects) for each object to be dumped.
4713 dumpDumpableObject(Archive *fout, DumpableObject *dobj)
4715 switch (dobj->objType)
4718 dumpNamespace(fout, (NamespaceInfo *) dobj);
4721 dumpType(fout, (TypeInfo *) dobj);
4724 dumpShellType(fout, (ShellTypeInfo *) dobj);
4727 dumpFunc(fout, (FuncInfo *) dobj);
4730 dumpAgg(fout, (AggInfo *) dobj);
4733 dumpOpr(fout, (OprInfo *) dobj);
4736 dumpOpclass(fout, (OpclassInfo *) dobj);
4739 dumpConversion(fout, (ConvInfo *) dobj);
4742 dumpTable(fout, (TableInfo *) dobj);
4745 dumpAttrDef(fout, (AttrDefInfo *) dobj);
4748 dumpIndex(fout, (IndxInfo *) dobj);
4751 dumpRule(fout, (RuleInfo *) dobj);
4754 dumpTrigger(fout, (TriggerInfo *) dobj);
4757 dumpConstraint(fout, (ConstraintInfo *) dobj);
4759 case DO_FK_CONSTRAINT:
4760 dumpConstraint(fout, (ConstraintInfo *) dobj);
4763 dumpProcLang(fout, (ProcLangInfo *) dobj);
4766 dumpCast(fout, (CastInfo *) dobj);
4769 dumpTableData(fout, (TableDataInfo *) dobj);
4772 /* table rowtypes are never dumped separately */
4775 ArchiveEntry(fout, dobj->catId, dobj->dumpId,
4776 dobj->name, NULL, NULL, "",
4777 false, "BLOBS", "", "", NULL,
4781 case DO_BLOB_COMMENTS:
4782 ArchiveEntry(fout, dobj->catId, dobj->dumpId,
4783 dobj->name, NULL, NULL, "",
4784 false, "BLOB COMMENTS", "", "", NULL,
4786 dumpBlobComments, NULL);
4793 * writes out to fout the queries to recreate a user-defined namespace
4796 dumpNamespace(Archive *fout, NamespaceInfo *nspinfo)
4802 /* Skip if not to be dumped */
4803 if (!nspinfo->dobj.dump || dataOnly)
4806 /* don't dump dummy namespace from pre-7.3 source */
4807 if (strlen(nspinfo->dobj.name) == 0)
4810 q = createPQExpBuffer();
4811 delq = createPQExpBuffer();
4813 qnspname = strdup(fmtId(nspinfo->dobj.name));
4815 appendPQExpBuffer(delq, "DROP SCHEMA %s;\n", qnspname);
4817 appendPQExpBuffer(q, "CREATE SCHEMA %s;\n", qnspname);
4819 ArchiveEntry(fout, nspinfo->dobj.catId, nspinfo->dobj.dumpId,
4823 false, "SCHEMA", q->data, delq->data, NULL,
4824 nspinfo->dobj.dependencies, nspinfo->dobj.nDeps,
4827 /* Dump Schema Comments */
4828 resetPQExpBuffer(q);
4829 appendPQExpBuffer(q, "SCHEMA %s", qnspname);
4830 dumpComment(fout, q->data,
4831 NULL, nspinfo->rolname,
4832 nspinfo->dobj.catId, 0, nspinfo->dobj.dumpId);
4834 dumpACL(fout, nspinfo->dobj.catId, nspinfo->dobj.dumpId, "SCHEMA",
4835 qnspname, nspinfo->dobj.name, NULL,
4836 nspinfo->rolname, nspinfo->nspacl);
4840 destroyPQExpBuffer(q);
4841 destroyPQExpBuffer(delq);
4846 * writes out to fout the queries to recreate a user-defined type
4849 dumpType(Archive *fout, TypeInfo *tinfo)
4851 /* Skip if not to be dumped */
4852 if (!tinfo->dobj.dump || dataOnly)
4855 /* Dump out in proper style */
4856 if (tinfo->typtype == 'b')
4857 dumpBaseType(fout, tinfo);
4858 else if (tinfo->typtype == 'd')
4859 dumpDomain(fout, tinfo);
4860 else if (tinfo->typtype == 'c')
4861 dumpCompositeType(fout, tinfo);
4866 * writes out to fout the queries to recreate a user-defined base type
4869 dumpBaseType(Archive *fout, TypeInfo *tinfo)
4871 PQExpBuffer q = createPQExpBuffer();
4872 PQExpBuffer delq = createPQExpBuffer();
4873 PQExpBuffer query = createPQExpBuffer();
4892 bool typdefault_is_literal = false;
4894 /* Set proper schema search path so regproc references list correctly */
4895 selectSourceSchema(tinfo->dobj.namespace->dobj.name);
4897 /* Fetch type-specific details */
4898 if (fout->remoteVersion >= 80000)
4900 appendPQExpBuffer(query, "SELECT typlen, "
4901 "typinput, typoutput, typreceive, typsend, "
4903 "typinput::pg_catalog.oid as typinputoid, "
4904 "typoutput::pg_catalog.oid as typoutputoid, "
4905 "typreceive::pg_catalog.oid as typreceiveoid, "
4906 "typsend::pg_catalog.oid as typsendoid, "
4907 "typanalyze::pg_catalog.oid as typanalyzeoid, "
4908 "typdelim, typbyval, typalign, typstorage, "
4909 "pg_catalog.pg_get_expr(typdefaultbin, 'pg_catalog.pg_type'::pg_catalog.regclass) as typdefaultbin, typdefault "
4910 "FROM pg_catalog.pg_type "
4911 "WHERE oid = '%u'::pg_catalog.oid",
4912 tinfo->dobj.catId.oid);
4914 else if (fout->remoteVersion >= 70400)
4916 appendPQExpBuffer(query, "SELECT typlen, "
4917 "typinput, typoutput, typreceive, typsend, "
4918 "'-' as typanalyze, "
4919 "typinput::pg_catalog.oid as typinputoid, "
4920 "typoutput::pg_catalog.oid as typoutputoid, "
4921 "typreceive::pg_catalog.oid as typreceiveoid, "
4922 "typsend::pg_catalog.oid as typsendoid, "
4923 "0 as typanalyzeoid, "
4924 "typdelim, typbyval, typalign, typstorage, "
4925 "pg_catalog.pg_get_expr(typdefaultbin, 'pg_catalog.pg_type'::pg_catalog.regclass) as typdefaultbin, typdefault "
4926 "FROM pg_catalog.pg_type "
4927 "WHERE oid = '%u'::pg_catalog.oid",
4928 tinfo->dobj.catId.oid);
4930 else if (fout->remoteVersion >= 70300)
4932 appendPQExpBuffer(query, "SELECT typlen, "
4933 "typinput, typoutput, "
4934 "'-' as typreceive, '-' as typsend, "
4935 "'-' as typanalyze, "
4936 "typinput::pg_catalog.oid as typinputoid, "
4937 "typoutput::pg_catalog.oid as typoutputoid, "
4938 "0 as typreceiveoid, 0 as typsendoid, "
4939 "0 as typanalyzeoid, "
4940 "typdelim, typbyval, typalign, typstorage, "
4941 "pg_catalog.pg_get_expr(typdefaultbin, 'pg_catalog.pg_type'::pg_catalog.regclass) as typdefaultbin, typdefault "
4942 "FROM pg_catalog.pg_type "
4943 "WHERE oid = '%u'::pg_catalog.oid",
4944 tinfo->dobj.catId.oid);
4946 else if (fout->remoteVersion >= 70200)
4949 * Note: although pre-7.3 catalogs contain typreceive and typsend,
4950 * ignore them because they are not right.
4952 appendPQExpBuffer(query, "SELECT typlen, "
4953 "typinput, typoutput, "
4954 "'-' as typreceive, '-' as typsend, "
4955 "'-' as typanalyze, "
4956 "typinput::oid as typinputoid, "
4957 "typoutput::oid as typoutputoid, "
4958 "0 as typreceiveoid, 0 as typsendoid, "
4959 "0 as typanalyzeoid, "
4960 "typdelim, typbyval, typalign, typstorage, "
4961 "NULL as typdefaultbin, typdefault "
4963 "WHERE oid = '%u'::oid",
4964 tinfo->dobj.catId.oid);
4966 else if (fout->remoteVersion >= 70100)
4969 * Ignore pre-7.2 typdefault; the field exists but has an unusable
4972 appendPQExpBuffer(query, "SELECT typlen, "
4973 "typinput, typoutput, "
4974 "'-' as typreceive, '-' as typsend, "
4975 "'-' as typanalyze, "
4976 "typinput::oid as typinputoid, "
4977 "typoutput::oid as typoutputoid, "
4978 "0 as typreceiveoid, 0 as typsendoid, "
4979 "0 as typanalyzeoid, "
4980 "typdelim, typbyval, typalign, typstorage, "
4981 "NULL as typdefaultbin, NULL as typdefault "
4983 "WHERE oid = '%u'::oid",
4984 tinfo->dobj.catId.oid);
4988 appendPQExpBuffer(query, "SELECT typlen, "
4989 "typinput, typoutput, "
4990 "'-' as typreceive, '-' as typsend, "
4991 "'-' as typanalyze, "
4992 "typinput::oid as typinputoid, "
4993 "typoutput::oid as typoutputoid, "
4994 "0 as typreceiveoid, 0 as typsendoid, "
4995 "0 as typanalyzeoid, "
4996 "typdelim, typbyval, typalign, "
4997 "'p'::char as typstorage, "
4998 "NULL as typdefaultbin, NULL as typdefault "
5000 "WHERE oid = '%u'::oid",
5001 tinfo->dobj.catId.oid);
5004 res = PQexec(g_conn, query->data);
5005 check_sql_result(res, g_conn, query->data, PGRES_TUPLES_OK);
5007 /* Expecting a single result only */
5008 ntups = PQntuples(res);
5011 write_msg(NULL, "Got %d rows instead of one from: %s",
5012 ntups, query->data);
5016 typlen = PQgetvalue(res, 0, PQfnumber(res, "typlen"));
5017 typinput = PQgetvalue(res, 0, PQfnumber(res, "typinput"));
5018 typoutput = PQgetvalue(res, 0, PQfnumber(res, "typoutput"));
5019 typreceive = PQgetvalue(res, 0, PQfnumber(res, "typreceive"));
5020 typsend = PQgetvalue(res, 0, PQfnumber(res, "typsend"));
5021 typanalyze = PQgetvalue(res, 0, PQfnumber(res, "typanalyze"));
5022 typinputoid = atooid(PQgetvalue(res, 0, PQfnumber(res, "typinputoid")));
5023 typoutputoid = atooid(PQgetvalue(res, 0, PQfnumber(res, "typoutputoid")));
5024 typreceiveoid = atooid(PQgetvalue(res, 0, PQfnumber(res, "typreceiveoid")));
5025 typsendoid = atooid(PQgetvalue(res, 0, PQfnumber(res, "typsendoid")));
5026 typanalyzeoid = atooid(PQgetvalue(res, 0, PQfnumber(res, "typanalyzeoid")));
5027 typdelim = PQgetvalue(res, 0, PQfnumber(res, "typdelim"));
5028 typbyval = PQgetvalue(res, 0, PQfnumber(res, "typbyval"));
5029 typalign = PQgetvalue(res, 0, PQfnumber(res, "typalign"));
5030 typstorage = PQgetvalue(res, 0, PQfnumber(res, "typstorage"));
5031 if (!PQgetisnull(res, 0, PQfnumber(res, "typdefaultbin")))
5032 typdefault = PQgetvalue(res, 0, PQfnumber(res, "typdefaultbin"));
5033 else if (!PQgetisnull(res, 0, PQfnumber(res, "typdefault")))
5035 typdefault = PQgetvalue(res, 0, PQfnumber(res, "typdefault"));
5036 typdefault_is_literal = true; /* it needs quotes */
5042 * DROP must be fully qualified in case same name appears in pg_catalog.
5043 * The reason we include CASCADE is that the circular dependency between
5044 * the type and its I/O functions makes it impossible to drop the type
5047 appendPQExpBuffer(delq, "DROP TYPE %s.",
5048 fmtId(tinfo->dobj.namespace->dobj.name));
5049 appendPQExpBuffer(delq, "%s CASCADE;\n",
5050 fmtId(tinfo->dobj.name));
5052 appendPQExpBuffer(q,
5053 "CREATE TYPE %s (\n"
5054 " INTERNALLENGTH = %s",
5055 fmtId(tinfo->dobj.name),
5056 (strcmp(typlen, "-1") == 0) ? "variable" : typlen);
5058 if (fout->remoteVersion >= 70300)
5060 /* regproc result is correctly quoted as of 7.3 */
5061 appendPQExpBuffer(q, ",\n INPUT = %s", typinput);
5062 appendPQExpBuffer(q, ",\n OUTPUT = %s", typoutput);
5063 if (OidIsValid(typreceiveoid))
5064 appendPQExpBuffer(q, ",\n RECEIVE = %s", typreceive);
5065 if (OidIsValid(typsendoid))
5066 appendPQExpBuffer(q, ",\n SEND = %s", typsend);
5067 if (OidIsValid(typanalyzeoid))
5068 appendPQExpBuffer(q, ",\n ANALYZE = %s", typanalyze);
5072 /* regproc delivers an unquoted name before 7.3 */
5073 /* cannot combine these because fmtId uses static result area */
5074 appendPQExpBuffer(q, ",\n INPUT = %s", fmtId(typinput));
5075 appendPQExpBuffer(q, ",\n OUTPUT = %s", fmtId(typoutput));
5076 /* no chance that receive/send/analyze need be printed */
5079 if (typdefault != NULL)
5081 appendPQExpBuffer(q, ",\n DEFAULT = ");
5082 if (typdefault_is_literal)
5083 appendStringLiteralAH(q, typdefault, fout);
5085 appendPQExpBufferStr(q, typdefault);
5092 /* reselect schema in case changed by function dump */
5093 selectSourceSchema(tinfo->dobj.namespace->dobj.name);
5094 elemType = getFormattedTypeName(tinfo->typelem, zeroAsOpaque);
5095 appendPQExpBuffer(q, ",\n ELEMENT = %s", elemType);
5099 if (typdelim && strcmp(typdelim, ",") != 0)
5101 appendPQExpBuffer(q, ",\n DELIMITER = ");
5102 appendStringLiteralAH(q, typdelim, fout);
5105 if (strcmp(typalign, "c") == 0)
5106 appendPQExpBuffer(q, ",\n ALIGNMENT = char");
5107 else if (strcmp(typalign, "s") == 0)
5108 appendPQExpBuffer(q, ",\n ALIGNMENT = int2");
5109 else if (strcmp(typalign, "i") == 0)
5110 appendPQExpBuffer(q, ",\n ALIGNMENT = int4");
5111 else if (strcmp(typalign, "d") == 0)
5112 appendPQExpBuffer(q, ",\n ALIGNMENT = double");
5114 if (strcmp(typstorage, "p") == 0)
5115 appendPQExpBuffer(q, ",\n STORAGE = plain");
5116 else if (strcmp(typstorage, "e") == 0)
5117 appendPQExpBuffer(q, ",\n STORAGE = external");
5118 else if (strcmp(typstorage, "x") == 0)
5119 appendPQExpBuffer(q, ",\n STORAGE = extended");
5120 else if (strcmp(typstorage, "m") == 0)
5121 appendPQExpBuffer(q, ",\n STORAGE = main");
5123 if (strcmp(typbyval, "t") == 0)
5124 appendPQExpBuffer(q, ",\n PASSEDBYVALUE");
5126 appendPQExpBuffer(q, "\n);\n");
5128 ArchiveEntry(fout, tinfo->dobj.catId, tinfo->dobj.dumpId,
5130 tinfo->dobj.namespace->dobj.name,
5132 tinfo->rolname, false,
5133 "TYPE", q->data, delq->data, NULL,
5134 tinfo->dobj.dependencies, tinfo->dobj.nDeps,
5137 /* Dump Type Comments */
5138 resetPQExpBuffer(q);
5140 appendPQExpBuffer(q, "TYPE %s", fmtId(tinfo->dobj.name));
5141 dumpComment(fout, q->data,
5142 tinfo->dobj.namespace->dobj.name, tinfo->rolname,
5143 tinfo->dobj.catId, 0, tinfo->dobj.dumpId);
5146 destroyPQExpBuffer(q);
5147 destroyPQExpBuffer(delq);
5148 destroyPQExpBuffer(query);
5153 * writes out to fout the queries to recreate a user-defined domain
5156 dumpDomain(Archive *fout, TypeInfo *tinfo)
5158 PQExpBuffer q = createPQExpBuffer();
5159 PQExpBuffer delq = createPQExpBuffer();
5160 PQExpBuffer query = createPQExpBuffer();
5167 bool typdefault_is_literal = false;
5169 /* Set proper schema search path so type references list correctly */
5170 selectSourceSchema(tinfo->dobj.namespace->dobj.name);
5172 /* Fetch domain specific details */
5173 /* We assume here that remoteVersion must be at least 70300 */
5174 appendPQExpBuffer(query, "SELECT typnotnull, "
5175 "pg_catalog.format_type(typbasetype, typtypmod) as typdefn, "
5176 "pg_catalog.pg_get_expr(typdefaultbin, 'pg_catalog.pg_type'::pg_catalog.regclass) as typdefaultbin, typdefault "
5177 "FROM pg_catalog.pg_type "
5178 "WHERE oid = '%u'::pg_catalog.oid",
5179 tinfo->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 typnotnull = PQgetvalue(res, 0, PQfnumber(res, "typnotnull"));
5194 typdefn = PQgetvalue(res, 0, PQfnumber(res, "typdefn"));
5195 if (!PQgetisnull(res, 0, PQfnumber(res, "typdefaultbin")))
5196 typdefault = PQgetvalue(res, 0, PQfnumber(res, "typdefaultbin"));
5197 else if (!PQgetisnull(res, 0, PQfnumber(res, "typdefault")))
5199 typdefault = PQgetvalue(res, 0, PQfnumber(res, "typdefault"));
5200 typdefault_is_literal = true; /* it needs quotes */
5205 appendPQExpBuffer(q,
5206 "CREATE DOMAIN %s AS %s",
5207 fmtId(tinfo->dobj.name),
5210 if (typnotnull[0] == 't')
5211 appendPQExpBuffer(q, " NOT NULL");
5213 if (typdefault != NULL)
5215 appendPQExpBuffer(q, " DEFAULT ");
5216 if (typdefault_is_literal)
5217 appendStringLiteralAH(q, typdefault, fout);
5219 appendPQExpBufferStr(q, typdefault);
5225 * Add any CHECK constraints for the domain
5227 for (i = 0; i < tinfo->nDomChecks; i++)
5229 ConstraintInfo *domcheck = &(tinfo->domChecks[i]);
5231 if (!domcheck->separate)
5232 appendPQExpBuffer(q, "\n\tCONSTRAINT %s %s",
5233 fmtId(domcheck->dobj.name), domcheck->condef);
5236 appendPQExpBuffer(q, ";\n");
5239 * DROP must be fully qualified in case same name appears in pg_catalog
5241 appendPQExpBuffer(delq, "DROP DOMAIN %s.",
5242 fmtId(tinfo->dobj.namespace->dobj.name));
5243 appendPQExpBuffer(delq, "%s;\n",
5244 fmtId(tinfo->dobj.name));
5246 ArchiveEntry(fout, tinfo->dobj.catId, tinfo->dobj.dumpId,
5248 tinfo->dobj.namespace->dobj.name,
5250 tinfo->rolname, false,
5251 "DOMAIN", q->data, delq->data, NULL,
5252 tinfo->dobj.dependencies, tinfo->dobj.nDeps,
5255 /* Dump Domain Comments */
5256 resetPQExpBuffer(q);
5258 appendPQExpBuffer(q, "DOMAIN %s", fmtId(tinfo->dobj.name));
5259 dumpComment(fout, q->data,
5260 tinfo->dobj.namespace->dobj.name, tinfo->rolname,
5261 tinfo->dobj.catId, 0, tinfo->dobj.dumpId);
5263 destroyPQExpBuffer(q);
5264 destroyPQExpBuffer(delq);
5265 destroyPQExpBuffer(query);
5270 * writes out to fout the queries to recreate a user-defined stand-alone
5274 dumpCompositeType(Archive *fout, TypeInfo *tinfo)
5276 PQExpBuffer q = createPQExpBuffer();
5277 PQExpBuffer delq = createPQExpBuffer();
5278 PQExpBuffer query = createPQExpBuffer();
5285 /* Set proper schema search path so type references list correctly */
5286 selectSourceSchema(tinfo->dobj.namespace->dobj.name);
5288 /* Fetch type specific details */
5289 /* We assume here that remoteVersion must be at least 70300 */
5291 appendPQExpBuffer(query, "SELECT a.attname, "
5292 "pg_catalog.format_type(a.atttypid, a.atttypmod) as atttypdefn "
5293 "FROM pg_catalog.pg_type t, pg_catalog.pg_attribute a "
5294 "WHERE t.oid = '%u'::pg_catalog.oid "
5295 "AND a.attrelid = t.typrelid "
5296 "AND NOT a.attisdropped "
5297 "ORDER BY a.attnum ",
5298 tinfo->dobj.catId.oid);
5300 res = PQexec(g_conn, query->data);
5301 check_sql_result(res, g_conn, query->data, PGRES_TUPLES_OK);
5303 /* Expecting at least a single result */
5304 ntups = PQntuples(res);
5307 write_msg(NULL, "query yielded no rows: %s\n", query->data);
5311 i_attname = PQfnumber(res, "attname");
5312 i_atttypdefn = PQfnumber(res, "atttypdefn");
5314 appendPQExpBuffer(q, "CREATE TYPE %s AS (",
5315 fmtId(tinfo->dobj.name));
5317 for (i = 0; i < ntups; i++)
5322 attname = PQgetvalue(res, i, i_attname);
5323 atttypdefn = PQgetvalue(res, i, i_atttypdefn);
5325 appendPQExpBuffer(q, "\n\t%s %s", fmtId(attname), atttypdefn);
5327 appendPQExpBuffer(q, ",");
5329 appendPQExpBuffer(q, "\n);\n");
5332 * DROP must be fully qualified in case same name appears in pg_catalog
5334 appendPQExpBuffer(delq, "DROP TYPE %s.",
5335 fmtId(tinfo->dobj.namespace->dobj.name));
5336 appendPQExpBuffer(delq, "%s;\n",
5337 fmtId(tinfo->dobj.name));
5339 ArchiveEntry(fout, tinfo->dobj.catId, tinfo->dobj.dumpId,
5341 tinfo->dobj.namespace->dobj.name,
5343 tinfo->rolname, false,
5344 "TYPE", q->data, delq->data, NULL,
5345 tinfo->dobj.dependencies, tinfo->dobj.nDeps,
5349 /* Dump Type Comments */
5350 resetPQExpBuffer(q);
5352 appendPQExpBuffer(q, "TYPE %s", fmtId(tinfo->dobj.name));
5353 dumpComment(fout, q->data,
5354 tinfo->dobj.namespace->dobj.name, tinfo->rolname,
5355 tinfo->dobj.catId, 0, tinfo->dobj.dumpId);
5358 destroyPQExpBuffer(q);
5359 destroyPQExpBuffer(delq);
5360 destroyPQExpBuffer(query);
5365 * writes out to fout the queries to create a shell type
5367 * We dump a shell definition in advance of the I/O functions for the type.
5370 dumpShellType(Archive *fout, ShellTypeInfo *stinfo)
5374 /* Skip if not to be dumped */
5375 if (!stinfo->dobj.dump || dataOnly)
5378 q = createPQExpBuffer();
5381 * Note the lack of a DROP command for the shell type; any required DROP
5382 * is driven off the base type entry, instead. This interacts with
5383 * _printTocEntry()'s use of the presence of a DROP command to decide
5384 * whether an entry needs an ALTER OWNER command. We don't want to
5385 * alter the shell type's owner immediately on creation; that should
5386 * happen only after it's filled in, otherwise the backend complains.
5389 appendPQExpBuffer(q, "CREATE TYPE %s;\n",
5390 fmtId(stinfo->dobj.name));
5392 ArchiveEntry(fout, stinfo->dobj.catId, stinfo->dobj.dumpId,
5394 stinfo->dobj.namespace->dobj.name,
5396 stinfo->baseType->rolname, false,
5397 "SHELL TYPE", q->data, "", NULL,
5398 stinfo->dobj.dependencies, stinfo->dobj.nDeps,
5401 destroyPQExpBuffer(q);
5405 * Determine whether we want to dump definitions for procedural languages.
5406 * Since the languages themselves don't have schemas, we can't rely on
5407 * the normal schema-based selection mechanism. We choose to dump them
5408 * whenever neither --schema nor --table was given. (Before 8.1, we used
5409 * the dump flag of the PL's call handler function, but in 8.1 this will
5410 * probably always be false since call handlers are created in pg_catalog.)
5412 * For some backwards compatibility with the older behavior, we forcibly
5413 * dump a PL if its handler function (and validator if any) are in a
5414 * dumpable namespace. That case is not checked here.
5417 shouldDumpProcLangs(void)
5419 if (selectTableName != NULL || selectSchemaName != NULL)
5421 /* And they're schema not data */
5429 * writes out to fout the queries to recreate a user-defined
5430 * procedural language
5433 dumpProcLang(Archive *fout, ProcLangInfo *plang)
5441 FuncInfo *validatorInfo = NULL;
5447 * Try to find the support function(s). It is not an error if we don't
5448 * find them --- if the functions are in the pg_catalog schema, as is
5449 * standard in 8.1 and up, then we won't have loaded them. (In this case
5450 * we will emit a parameterless CREATE LANGUAGE command, which will
5451 * require PL template knowledge in the backend to reload.)
5454 funcInfo = findFuncByOid(plang->lanplcallfoid);
5455 if (funcInfo != NULL && !funcInfo->dobj.dump)
5456 funcInfo = NULL; /* treat not-dumped same as not-found */
5458 if (OidIsValid(plang->lanvalidator))
5460 validatorInfo = findFuncByOid(plang->lanvalidator);
5461 if (validatorInfo != NULL && !validatorInfo->dobj.dump)
5462 validatorInfo = NULL;
5466 * If the functions are dumpable then emit a traditional CREATE LANGUAGE
5467 * with parameters. Otherwise, dump only if shouldDumpProcLangs() says to
5470 useParams = (funcInfo != NULL &&
5471 (validatorInfo != NULL || !OidIsValid(plang->lanvalidator)));
5473 if (!useParams && !shouldDumpProcLangs())
5476 defqry = createPQExpBuffer();
5477 delqry = createPQExpBuffer();
5479 qlanname = strdup(fmtId(plang->dobj.name));
5482 * If dumping a HANDLER clause, treat the language as being in the handler
5483 * function's schema; this avoids cluttering the HANDLER clause. Otherwise
5484 * it doesn't really have a schema.
5487 lanschema = funcInfo->dobj.namespace->dobj.name;
5491 appendPQExpBuffer(delqry, "DROP PROCEDURAL LANGUAGE %s;\n",
5494 appendPQExpBuffer(defqry, "CREATE %sPROCEDURAL LANGUAGE %s",
5495 (useParams && plang->lanpltrusted) ? "TRUSTED " : "",
5499 appendPQExpBuffer(defqry, " HANDLER %s",
5500 fmtId(funcInfo->dobj.name));
5501 if (OidIsValid(plang->lanvalidator))
5503 appendPQExpBuffer(defqry, " VALIDATOR ");
5504 /* Cope with possibility that validator is in different schema */
5505 if (validatorInfo->dobj.namespace != funcInfo->dobj.namespace)
5506 appendPQExpBuffer(defqry, "%s.",
5507 fmtId(validatorInfo->dobj.namespace->dobj.name));
5508 appendPQExpBuffer(defqry, "%s",
5509 fmtId(validatorInfo->dobj.name));
5512 appendPQExpBuffer(defqry, ";\n");
5514 ArchiveEntry(fout, plang->dobj.catId, plang->dobj.dumpId,
5516 lanschema, NULL, plang->lanowner,
5517 false, "PROCEDURAL LANGUAGE",
5518 defqry->data, delqry->data, NULL,
5519 plang->dobj.dependencies, plang->dobj.nDeps,
5522 /* Dump Proc Lang Comments */
5523 resetPQExpBuffer(defqry);
5524 appendPQExpBuffer(defqry, "LANGUAGE %s", qlanname);
5525 dumpComment(fout, defqry->data,
5527 plang->dobj.catId, 0, plang->dobj.dumpId);
5529 if (plang->lanpltrusted)
5530 dumpACL(fout, plang->dobj.catId, plang->dobj.dumpId, "LANGUAGE",
5531 qlanname, plang->dobj.name,
5533 plang->lanowner, plang->lanacl);
5537 destroyPQExpBuffer(defqry);
5538 destroyPQExpBuffer(delqry);
5542 * format_function_arguments: generate function name and argument list
5544 * The argument type names are qualified if needed. The function name
5545 * is never qualified.
5547 * Any or all of allargtypes, argmodes, argnames may be NULL.
5550 format_function_arguments(FuncInfo *finfo, int nallargs,
5558 initPQExpBuffer(&fn);
5559 appendPQExpBuffer(&fn, "%s(", fmtId(finfo->dobj.name));
5560 for (j = 0; j < nallargs; j++)
5564 const char *argmode;
5565 const char *argname;
5567 typid = allargtypes ? atooid(allargtypes[j]) : finfo->argtypes[j];
5568 typname = getFormattedTypeName(typid, zeroAsOpaque);
5572 switch (argmodes[j][0])
5584 write_msg(NULL, "WARNING: bogus value in proargmodes array\n");
5592 argname = argnames ? argnames[j] : (char *) NULL;
5593 if (argname && argname[0] == '\0')
5596 appendPQExpBuffer(&fn, "%s%s%s%s%s",
5597 (j > 0) ? ", " : "",
5599 argname ? fmtId(argname) : "",
5604 appendPQExpBuffer(&fn, ")");
5609 * format_function_signature: generate function name and argument list
5611 * This is like format_function_arguments except that only a minimal
5612 * list of input argument types is generated; this is sufficient to
5613 * reference the function, but not to define it.
5615 * If honor_quotes is false then the function name is never quoted.
5616 * This is appropriate for use in TOC tags, but not in SQL commands.
5619 format_function_signature(FuncInfo *finfo, bool honor_quotes)
5624 initPQExpBuffer(&fn);
5626 appendPQExpBuffer(&fn, "%s(", fmtId(finfo->dobj.name));
5628 appendPQExpBuffer(&fn, "%s(", finfo->dobj.name);
5629 for (j = 0; j < finfo->nargs; j++)
5633 typname = getFormattedTypeName(finfo->argtypes[j], zeroAsOpaque);
5635 appendPQExpBuffer(&fn, "%s%s",
5636 (j > 0) ? ", " : "",
5640 appendPQExpBuffer(&fn, ")");
5647 * dump out one function
5650 dumpFunc(Archive *fout, FuncInfo *finfo)
5663 char *proallargtypes;
5672 char **allargtypes = NULL;
5673 char **argmodes = NULL;
5674 char **argnames = NULL;
5676 /* Skip if not to be dumped */
5677 if (!finfo->dobj.dump || dataOnly)
5680 query = createPQExpBuffer();
5681 q = createPQExpBuffer();
5682 delqry = createPQExpBuffer();
5683 asPart = createPQExpBuffer();
5685 /* Set proper schema search path so type references list correctly */
5686 selectSourceSchema(finfo->dobj.namespace->dobj.name);
5688 /* Fetch function-specific details */
5689 if (g_fout->remoteVersion >= 80100)
5691 appendPQExpBuffer(query,
5692 "SELECT proretset, prosrc, probin, "
5693 "proallargtypes, proargmodes, proargnames, "
5694 "provolatile, proisstrict, prosecdef, "
5695 "(SELECT lanname FROM pg_catalog.pg_language WHERE oid = prolang) as lanname "
5696 "FROM pg_catalog.pg_proc "
5697 "WHERE oid = '%u'::pg_catalog.oid",
5698 finfo->dobj.catId.oid);
5700 else if (g_fout->remoteVersion >= 80000)
5702 appendPQExpBuffer(query,
5703 "SELECT proretset, prosrc, probin, "
5704 "null as proallargtypes, "
5705 "null as proargmodes, "
5707 "provolatile, proisstrict, prosecdef, "
5708 "(SELECT lanname FROM pg_catalog.pg_language WHERE oid = prolang) as lanname "
5709 "FROM pg_catalog.pg_proc "
5710 "WHERE oid = '%u'::pg_catalog.oid",
5711 finfo->dobj.catId.oid);
5713 else if (g_fout->remoteVersion >= 70300)
5715 appendPQExpBuffer(query,
5716 "SELECT proretset, prosrc, probin, "
5717 "null as proallargtypes, "
5718 "null as proargmodes, "
5719 "null as proargnames, "
5720 "provolatile, proisstrict, prosecdef, "
5721 "(SELECT lanname FROM pg_catalog.pg_language WHERE oid = prolang) as lanname "
5722 "FROM pg_catalog.pg_proc "
5723 "WHERE oid = '%u'::pg_catalog.oid",
5724 finfo->dobj.catId.oid);
5726 else if (g_fout->remoteVersion >= 70100)
5728 appendPQExpBuffer(query,
5729 "SELECT proretset, prosrc, probin, "
5730 "null as proallargtypes, "
5731 "null as proargmodes, "
5732 "null as proargnames, "
5733 "case when proiscachable then 'i' else 'v' end as provolatile, "
5735 "'f'::boolean as prosecdef, "
5736 "(SELECT lanname FROM pg_language WHERE oid = prolang) as lanname "
5738 "WHERE oid = '%u'::oid",
5739 finfo->dobj.catId.oid);
5743 appendPQExpBuffer(query,
5744 "SELECT proretset, prosrc, probin, "
5745 "null as proallargtypes, "
5746 "null as proargmodes, "
5747 "null as proargnames, "
5748 "case when proiscachable then 'i' else 'v' end as provolatile, "
5749 "'f'::boolean as proisstrict, "
5750 "'f'::boolean as prosecdef, "
5751 "(SELECT lanname FROM pg_language WHERE oid = prolang) as lanname "
5753 "WHERE oid = '%u'::oid",
5754 finfo->dobj.catId.oid);
5757 res = PQexec(g_conn, query->data);
5758 check_sql_result(res, g_conn, query->data, PGRES_TUPLES_OK);
5760 /* Expecting a single result only */
5761 ntups = PQntuples(res);
5764 write_msg(NULL, "Got %d rows instead of one from: %s",
5765 ntups, query->data);
5769 proretset = PQgetvalue(res, 0, PQfnumber(res, "proretset"));
5770 prosrc = PQgetvalue(res, 0, PQfnumber(res, "prosrc"));
5771 probin = PQgetvalue(res, 0, PQfnumber(res, "probin"));
5772 proallargtypes = PQgetvalue(res, 0, PQfnumber(res, "proallargtypes"));
5773 proargmodes = PQgetvalue(res, 0, PQfnumber(res, "proargmodes"));
5774 proargnames = PQgetvalue(res, 0, PQfnumber(res, "proargnames"));
5775 provolatile = PQgetvalue(res, 0, PQfnumber(res, "provolatile"));
5776 proisstrict = PQgetvalue(res, 0, PQfnumber(res, "proisstrict"));
5777 prosecdef = PQgetvalue(res, 0, PQfnumber(res, "prosecdef"));
5778 lanname = PQgetvalue(res, 0, PQfnumber(res, "lanname"));
5781 * See backend/commands/define.c for details of how the 'AS' clause is
5784 if (strcmp(probin, "-") != 0)
5786 appendPQExpBuffer(asPart, "AS ");
5787 appendStringLiteralAH(asPart, probin, fout);
5788 if (strcmp(prosrc, "-") != 0)
5790 appendPQExpBuffer(asPart, ", ");
5793 * where we have bin, use dollar quoting if allowed and src
5794 * contains quote or backslash; else use regular quoting.
5796 if (disable_dollar_quoting ||
5797 (strchr(prosrc, '\'') == NULL && strchr(prosrc, '\\') == NULL))
5798 appendStringLiteralAH(asPart, prosrc, fout);
5800 appendStringLiteralDQ(asPart, prosrc, NULL);
5805 if (strcmp(prosrc, "-") != 0)
5807 appendPQExpBuffer(asPart, "AS ");
5808 /* with no bin, dollar quote src unconditionally if allowed */
5809 if (disable_dollar_quoting)
5810 appendStringLiteralAH(asPart, prosrc, fout);
5812 appendStringLiteralDQ(asPart, prosrc, NULL);
5816 nallargs = finfo->nargs; /* unless we learn different from allargs */
5818 if (proallargtypes && *proallargtypes)
5822 if (!parsePGArray(proallargtypes, &allargtypes, &nitems) ||
5823 nitems < finfo->nargs)
5825 write_msg(NULL, "WARNING: could not parse proallargtypes array\n");
5834 if (proargmodes && *proargmodes)
5838 if (!parsePGArray(proargmodes, &argmodes, &nitems) ||
5841 write_msg(NULL, "WARNING: could not parse proargmodes array\n");
5848 if (proargnames && *proargnames)
5852 if (!parsePGArray(proargnames, &argnames, &nitems) ||
5855 write_msg(NULL, "WARNING: could not parse proargnames array\n");
5862 funcsig = format_function_arguments(finfo, nallargs, allargtypes,
5863 argmodes, argnames);
5864 funcsig_tag = format_function_signature(finfo, false);
5867 * DROP must be fully qualified in case same name appears in pg_catalog
5869 appendPQExpBuffer(delqry, "DROP FUNCTION %s.%s;\n",
5870 fmtId(finfo->dobj.namespace->dobj.name),
5873 rettypename = getFormattedTypeName(finfo->prorettype, zeroAsOpaque);
5875 appendPQExpBuffer(q, "CREATE FUNCTION %s ", funcsig);
5876 appendPQExpBuffer(q, "RETURNS %s%s\n %s\n LANGUAGE %s",
5877 (proretset[0] == 't') ? "SETOF " : "",
5884 if (provolatile[0] != PROVOLATILE_VOLATILE)
5886 if (provolatile[0] == PROVOLATILE_IMMUTABLE)
5887 appendPQExpBuffer(q, " IMMUTABLE");
5888 else if (provolatile[0] == PROVOLATILE_STABLE)
5889 appendPQExpBuffer(q, " STABLE");
5890 else if (provolatile[0] != PROVOLATILE_VOLATILE)
5892 write_msg(NULL, "unrecognized provolatile value for function \"%s\"\n",
5898 if (proisstrict[0] == 't')
5899 appendPQExpBuffer(q, " STRICT");
5901 if (prosecdef[0] == 't')
5902 appendPQExpBuffer(q, " SECURITY DEFINER");
5904 appendPQExpBuffer(q, ";\n");
5906 ArchiveEntry(fout, finfo->dobj.catId, finfo->dobj.dumpId,
5908 finfo->dobj.namespace->dobj.name,
5910 finfo->rolname, false,
5911 "FUNCTION", q->data, delqry->data, NULL,
5912 finfo->dobj.dependencies, finfo->dobj.nDeps,
5915 /* Dump Function Comments */
5916 resetPQExpBuffer(q);
5917 appendPQExpBuffer(q, "FUNCTION %s", funcsig);
5918 dumpComment(fout, q->data,
5919 finfo->dobj.namespace->dobj.name, finfo->rolname,
5920 finfo->dobj.catId, 0, finfo->dobj.dumpId);
5922 dumpACL(fout, finfo->dobj.catId, finfo->dobj.dumpId, "FUNCTION",
5923 funcsig, funcsig_tag,
5924 finfo->dobj.namespace->dobj.name,
5925 finfo->rolname, finfo->proacl);
5929 destroyPQExpBuffer(query);
5930 destroyPQExpBuffer(q);
5931 destroyPQExpBuffer(delqry);
5932 destroyPQExpBuffer(asPart);
5945 * Dump a user-defined cast
5948 dumpCast(Archive *fout, CastInfo *cast)
5952 PQExpBuffer castsig;
5953 FuncInfo *funcInfo = NULL;
5954 TypeInfo *sourceInfo;
5955 TypeInfo *targetInfo;
5960 if (OidIsValid(cast->castfunc))
5962 funcInfo = findFuncByOid(cast->castfunc);
5963 if (funcInfo == NULL)
5968 * As per discussion we dump casts if one or more of the underlying
5969 * objects (the conversion function and the two data types) are not
5970 * builtin AND if all of the non-builtin objects are included in the dump.
5971 * Builtin meaning, the namespace name does not start with "pg_".
5973 sourceInfo = findTypeByOid(cast->castsource);
5974 targetInfo = findTypeByOid(cast->casttarget);
5976 if (sourceInfo == NULL || targetInfo == NULL)
5980 * Skip this cast if all objects are from pg_
5982 if ((funcInfo == NULL ||
5983 strncmp(funcInfo->dobj.namespace->dobj.name, "pg_", 3) == 0) &&
5984 strncmp(sourceInfo->dobj.namespace->dobj.name, "pg_", 3) == 0 &&
5985 strncmp(targetInfo->dobj.namespace->dobj.name, "pg_", 3) == 0)
5989 * Skip cast if function isn't from pg_ and is not to be dumped.
5992 strncmp(funcInfo->dobj.namespace->dobj.name, "pg_", 3) != 0 &&
5993 !funcInfo->dobj.dump)
5997 * Same for the source type
5999 if (strncmp(sourceInfo->dobj.namespace->dobj.name, "pg_", 3) != 0 &&
6000 !sourceInfo->dobj.dump)
6004 * and the target type.
6006 if (strncmp(targetInfo->dobj.namespace->dobj.name, "pg_", 3) != 0 &&
6007 !targetInfo->dobj.dump)
6010 /* Make sure we are in proper schema (needed for getFormattedTypeName) */
6011 selectSourceSchema("pg_catalog");
6013 defqry = createPQExpBuffer();
6014 delqry = createPQExpBuffer();
6015 castsig = createPQExpBuffer();
6017 appendPQExpBuffer(delqry, "DROP CAST (%s AS %s);\n",
6018 getFormattedTypeName(cast->castsource, zeroAsNone),
6019 getFormattedTypeName(cast->casttarget, zeroAsNone));
6021 appendPQExpBuffer(defqry, "CREATE CAST (%s AS %s) ",
6022 getFormattedTypeName(cast->castsource, zeroAsNone),
6023 getFormattedTypeName(cast->casttarget, zeroAsNone));
6025 if (!OidIsValid(cast->castfunc))
6026 appendPQExpBuffer(defqry, "WITHOUT FUNCTION");
6030 * Always qualify the function name, in case it is not in pg_catalog
6031 * schema (format_function_signature won't qualify it).
6033 appendPQExpBuffer(defqry, "WITH FUNCTION %s.",
6034 fmtId(funcInfo->dobj.namespace->dobj.name));
6035 appendPQExpBuffer(defqry, "%s",
6036 format_function_signature(funcInfo, true));
6039 if (cast->castcontext == 'a')
6040 appendPQExpBuffer(defqry, " AS ASSIGNMENT");
6041 else if (cast->castcontext == 'i')
6042 appendPQExpBuffer(defqry, " AS IMPLICIT");
6043 appendPQExpBuffer(defqry, ";\n");
6045 appendPQExpBuffer(castsig, "CAST (%s AS %s)",
6046 getFormattedTypeName(cast->castsource, zeroAsNone),
6047 getFormattedTypeName(cast->casttarget, zeroAsNone));
6049 ArchiveEntry(fout, cast->dobj.catId, cast->dobj.dumpId,
6051 "pg_catalog", NULL, "",
6052 false, "CAST", defqry->data, delqry->data, NULL,
6053 cast->dobj.dependencies, cast->dobj.nDeps,
6056 /* Dump Cast Comments */
6057 resetPQExpBuffer(defqry);
6058 appendPQExpBuffer(defqry, "CAST (%s AS %s)",
6059 getFormattedTypeName(cast->castsource, zeroAsNone),
6060 getFormattedTypeName(cast->casttarget, zeroAsNone));
6061 dumpComment(fout, defqry->data,
6063 cast->dobj.catId, 0, cast->dobj.dumpId);
6065 destroyPQExpBuffer(defqry);
6066 destroyPQExpBuffer(delqry);
6067 destroyPQExpBuffer(castsig);
6072 * write out a single operator definition
6075 dumpOpr(Archive *fout, OprInfo *oprinfo)
6081 PQExpBuffer details;
6112 /* Skip if not to be dumped */
6113 if (!oprinfo->dobj.dump || dataOnly)
6117 * some operators are invalid because they were the result of user
6118 * defining operators before commutators exist
6120 if (!OidIsValid(oprinfo->oprcode))
6123 query = createPQExpBuffer();
6124 q = createPQExpBuffer();
6125 delq = createPQExpBuffer();
6126 oprid = createPQExpBuffer();
6127 details = createPQExpBuffer();
6129 /* Make sure we are in proper schema so regoperator works correctly */
6130 selectSourceSchema(oprinfo->dobj.namespace->dobj.name);
6132 if (g_fout->remoteVersion >= 70300)
6134 appendPQExpBuffer(query, "SELECT oprkind, "
6135 "oprcode::pg_catalog.regprocedure, "
6136 "oprleft::pg_catalog.regtype, "
6137 "oprright::pg_catalog.regtype, "
6138 "oprcom::pg_catalog.regoperator, "
6139 "oprnegate::pg_catalog.regoperator, "
6140 "oprrest::pg_catalog.regprocedure, "
6141 "oprjoin::pg_catalog.regprocedure, "
6143 "oprlsortop::pg_catalog.regoperator, "
6144 "oprrsortop::pg_catalog.regoperator, "
6145 "oprltcmpop::pg_catalog.regoperator, "
6146 "oprgtcmpop::pg_catalog.regoperator "
6147 "from pg_catalog.pg_operator "
6148 "where oid = '%u'::pg_catalog.oid",
6149 oprinfo->dobj.catId.oid);
6151 else if (g_fout->remoteVersion >= 70100)
6153 appendPQExpBuffer(query, "SELECT oprkind, oprcode, "
6154 "CASE WHEN oprleft = 0 THEN '-' "
6155 "ELSE format_type(oprleft, NULL) END as oprleft, "
6156 "CASE WHEN oprright = 0 THEN '-' "
6157 "ELSE format_type(oprright, NULL) END as oprright, "
6158 "oprcom, oprnegate, oprrest, oprjoin, "
6159 "oprcanhash, oprlsortop, oprrsortop, "
6160 "0 as oprltcmpop, 0 as oprgtcmpop "
6162 "where oid = '%u'::oid",
6163 oprinfo->dobj.catId.oid);
6167 appendPQExpBuffer(query, "SELECT oprkind, oprcode, "
6168 "CASE WHEN oprleft = 0 THEN '-'::name "
6169 "ELSE (select typname from pg_type where oid = oprleft) END as oprleft, "
6170 "CASE WHEN oprright = 0 THEN '-'::name "
6171 "ELSE (select typname from pg_type where oid = oprright) END as oprright, "
6172 "oprcom, oprnegate, oprrest, oprjoin, "
6173 "oprcanhash, oprlsortop, oprrsortop, "
6174 "0 as oprltcmpop, 0 as oprgtcmpop "
6176 "where oid = '%u'::oid",
6177 oprinfo->dobj.catId.oid);
6180 res = PQexec(g_conn, query->data);
6181 check_sql_result(res, g_conn, query->data, PGRES_TUPLES_OK);
6183 /* Expecting a single result only */
6184 ntups = PQntuples(res);
6187 write_msg(NULL, "Got %d rows instead of one from: %s",
6188 ntups, query->data);
6192 i_oprkind = PQfnumber(res, "oprkind");
6193 i_oprcode = PQfnumber(res, "oprcode");
6194 i_oprleft = PQfnumber(res, "oprleft");
6195 i_oprright = PQfnumber(res, "oprright");
6196 i_oprcom = PQfnumber(res, "oprcom");
6197 i_oprnegate = PQfnumber(res, "oprnegate");
6198 i_oprrest = PQfnumber(res, "oprrest");
6199 i_oprjoin = PQfnumber(res, "oprjoin");
6200 i_oprcanhash = PQfnumber(res, "oprcanhash");
6201 i_oprlsortop = PQfnumber(res, "oprlsortop");
6202 i_oprrsortop = PQfnumber(res, "oprrsortop");
6203 i_oprltcmpop = PQfnumber(res, "oprltcmpop");
6204 i_oprgtcmpop = PQfnumber(res, "oprgtcmpop");
6206 oprkind = PQgetvalue(res, 0, i_oprkind);
6207 oprcode = PQgetvalue(res, 0, i_oprcode);
6208 oprleft = PQgetvalue(res, 0, i_oprleft);
6209 oprright = PQgetvalue(res, 0, i_oprright);
6210 oprcom = PQgetvalue(res, 0, i_oprcom);
6211 oprnegate = PQgetvalue(res, 0, i_oprnegate);
6212 oprrest = PQgetvalue(res, 0, i_oprrest);
6213 oprjoin = PQgetvalue(res, 0, i_oprjoin);
6214 oprcanhash = PQgetvalue(res, 0, i_oprcanhash);
6215 oprlsortop = PQgetvalue(res, 0, i_oprlsortop);
6216 oprrsortop = PQgetvalue(res, 0, i_oprrsortop);
6217 oprltcmpop = PQgetvalue(res, 0, i_oprltcmpop);
6218 oprgtcmpop = PQgetvalue(res, 0, i_oprgtcmpop);
6220 appendPQExpBuffer(details, " PROCEDURE = %s",
6221 convertRegProcReference(oprcode));
6223 appendPQExpBuffer(oprid, "%s (",
6224 oprinfo->dobj.name);
6227 * right unary means there's a left arg and left unary means there's a
6230 if (strcmp(oprkind, "r") == 0 ||
6231 strcmp(oprkind, "b") == 0)
6233 if (g_fout->remoteVersion >= 70100)
6236 name = fmtId(oprleft);
6237 appendPQExpBuffer(details, ",\n LEFTARG = %s", name);
6238 appendPQExpBuffer(oprid, "%s", name);
6241 appendPQExpBuffer(oprid, "NONE");
6243 if (strcmp(oprkind, "l") == 0 ||
6244 strcmp(oprkind, "b") == 0)
6246 if (g_fout->remoteVersion >= 70100)
6249 name = fmtId(oprright);
6250 appendPQExpBuffer(details, ",\n RIGHTARG = %s", name);
6251 appendPQExpBuffer(oprid, ", %s)", name);
6254 appendPQExpBuffer(oprid, ", NONE)");
6256 name = convertOperatorReference(oprcom);
6258 appendPQExpBuffer(details, ",\n COMMUTATOR = %s", name);
6260 name = convertOperatorReference(oprnegate);
6262 appendPQExpBuffer(details, ",\n NEGATOR = %s", name);
6264 if (strcmp(oprcanhash, "t") == 0)
6265 appendPQExpBuffer(details, ",\n HASHES");
6267 name = convertRegProcReference(oprrest);
6269 appendPQExpBuffer(details, ",\n RESTRICT = %s", name);
6271 name = convertRegProcReference(oprjoin);
6273 appendPQExpBuffer(details, ",\n JOIN = %s", name);
6275 name = convertOperatorReference(oprlsortop);
6277 appendPQExpBuffer(details, ",\n SORT1 = %s", name);
6279 name = convertOperatorReference(oprrsortop);
6281 appendPQExpBuffer(details, ",\n SORT2 = %s", name);
6283 name = convertOperatorReference(oprltcmpop);
6285 appendPQExpBuffer(details, ",\n LTCMP = %s", name);
6287 name = convertOperatorReference(oprgtcmpop);
6289 appendPQExpBuffer(details, ",\n GTCMP = %s", name);
6292 * DROP must be fully qualified in case same name appears in pg_catalog
6294 appendPQExpBuffer(delq, "DROP OPERATOR %s.%s;\n",
6295 fmtId(oprinfo->dobj.namespace->dobj.name),
6298 appendPQExpBuffer(q, "CREATE OPERATOR %s (\n%s\n);\n",
6299 oprinfo->dobj.name, details->data);
6301 ArchiveEntry(fout, oprinfo->dobj.catId, oprinfo->dobj.dumpId,
6303 oprinfo->dobj.namespace->dobj.name,
6306 false, "OPERATOR", q->data, delq->data, NULL,
6307 oprinfo->dobj.dependencies, oprinfo->dobj.nDeps,
6310 /* Dump Operator Comments */
6311 resetPQExpBuffer(q);
6312 appendPQExpBuffer(q, "OPERATOR %s", oprid->data);
6313 dumpComment(fout, q->data,
6314 oprinfo->dobj.namespace->dobj.name, oprinfo->rolname,
6315 oprinfo->dobj.catId, 0, oprinfo->dobj.dumpId);
6319 destroyPQExpBuffer(query);
6320 destroyPQExpBuffer(q);
6321 destroyPQExpBuffer(delq);
6322 destroyPQExpBuffer(oprid);
6323 destroyPQExpBuffer(details);
6327 * Convert a function reference obtained from pg_operator
6329 * Returns what to print, or NULL if function references is InvalidOid
6331 * In 7.3 the input is a REGPROCEDURE display; we have to strip the
6332 * argument-types part. In prior versions, the input is a REGPROC display.
6335 convertRegProcReference(const char *proc)
6337 /* In all cases "-" means a null reference */
6338 if (strcmp(proc, "-") == 0)
6341 if (g_fout->remoteVersion >= 70300)
6347 name = strdup(proc);
6348 /* find non-double-quoted left paren */
6350 for (paren = name; *paren; paren++)
6352 if (*paren == '(' && !inquote)
6363 /* REGPROC before 7.3 does not quote its result */
6368 * Convert an operator cross-reference obtained from pg_operator
6370 * Returns what to print, or NULL to print nothing
6372 * In 7.3 and up the input is a REGOPERATOR display; we have to strip the
6373 * argument-types part, and add OPERATOR() decoration if the name is
6374 * schema-qualified. In older versions, the input is just a numeric OID,
6375 * which we search our operator list for.
6378 convertOperatorReference(const char *opr)
6382 /* In all cases "0" means a null reference */
6383 if (strcmp(opr, "0") == 0)
6386 if (g_fout->remoteVersion >= 70300)
6395 /* find non-double-quoted left paren, and check for non-quoted dot */
6398 for (ptr = name; *ptr; ptr++)
6402 else if (*ptr == '.' && !inquote)
6404 else if (*ptr == '(' && !inquote)
6410 /* If not schema-qualified, don't need to add OPERATOR() */
6413 oname = malloc(strlen(name) + 11);
6414 sprintf(oname, "OPERATOR(%s)", name);
6419 oprInfo = findOprByOid(atooid(opr));
6420 if (oprInfo == NULL)
6422 write_msg(NULL, "WARNING: could not find operator with OID %s\n",
6426 return oprInfo->dobj.name;
6431 * write out a single operator class definition
6434 dumpOpclass(Archive *fout, OpclassInfo *opcinfo)
6462 /* Skip if not to be dumped */
6463 if (!opcinfo->dobj.dump || dataOnly)
6467 * XXX currently we do not implement dumping of operator classes from
6468 * pre-7.3 databases. This could be done but it seems not worth the
6471 if (g_fout->remoteVersion < 70300)
6474 query = createPQExpBuffer();
6475 q = createPQExpBuffer();
6476 delq = createPQExpBuffer();
6478 /* Make sure we are in proper schema so regoperator works correctly */
6479 selectSourceSchema(opcinfo->dobj.namespace->dobj.name);
6481 /* Get additional fields from the pg_opclass row */
6482 appendPQExpBuffer(query, "SELECT opcintype::pg_catalog.regtype, "
6483 "opckeytype::pg_catalog.regtype, "
6485 "(SELECT amname FROM pg_catalog.pg_am WHERE oid = opcamid) AS amname "
6486 "FROM pg_catalog.pg_opclass "
6487 "WHERE oid = '%u'::pg_catalog.oid",
6488 opcinfo->dobj.catId.oid);
6490 res = PQexec(g_conn, query->data);
6491 check_sql_result(res, g_conn, query->data, PGRES_TUPLES_OK);
6493 /* Expecting a single result only */
6494 ntups = PQntuples(res);
6497 write_msg(NULL, "Got %d rows instead of one from: %s",
6498 ntups, query->data);
6502 i_opcintype = PQfnumber(res, "opcintype");
6503 i_opckeytype = PQfnumber(res, "opckeytype");
6504 i_opcdefault = PQfnumber(res, "opcdefault");
6505 i_amname = PQfnumber(res, "amname");
6507 opcintype = PQgetvalue(res, 0, i_opcintype);
6508 opckeytype = PQgetvalue(res, 0, i_opckeytype);
6509 opcdefault = PQgetvalue(res, 0, i_opcdefault);
6510 /* amname will still be needed after we PQclear res */
6511 amname = strdup(PQgetvalue(res, 0, i_amname));
6514 * DROP must be fully qualified in case same name appears in pg_catalog
6516 appendPQExpBuffer(delq, "DROP OPERATOR CLASS %s",
6517 fmtId(opcinfo->dobj.namespace->dobj.name));
6518 appendPQExpBuffer(delq, ".%s",
6519 fmtId(opcinfo->dobj.name));
6520 appendPQExpBuffer(delq, " USING %s;\n",
6523 /* Build the fixed portion of the CREATE command */
6524 appendPQExpBuffer(q, "CREATE OPERATOR CLASS %s\n ",
6525 fmtId(opcinfo->dobj.name));
6526 if (strcmp(opcdefault, "t") == 0)
6527 appendPQExpBuffer(q, "DEFAULT ");
6528 appendPQExpBuffer(q, "FOR TYPE %s USING %s AS\n ",
6534 if (strcmp(opckeytype, "-") != 0)
6536 appendPQExpBuffer(q, "STORAGE %s",
6544 * Now fetch and print the OPERATOR entries (pg_amop rows).
6546 resetPQExpBuffer(query);
6548 appendPQExpBuffer(query, "SELECT amopstrategy, amopreqcheck, "
6549 "amopopr::pg_catalog.regoperator "
6550 "FROM pg_catalog.pg_amop "
6551 "WHERE amopclaid = '%u'::pg_catalog.oid "
6552 "ORDER BY amopstrategy",
6553 opcinfo->dobj.catId.oid);
6555 res = PQexec(g_conn, query->data);
6556 check_sql_result(res, g_conn, query->data, PGRES_TUPLES_OK);
6558 ntups = PQntuples(res);
6560 i_amopstrategy = PQfnumber(res, "amopstrategy");
6561 i_amopreqcheck = PQfnumber(res, "amopreqcheck");
6562 i_amopopr = PQfnumber(res, "amopopr");
6564 for (i = 0; i < ntups; i++)
6566 amopstrategy = PQgetvalue(res, i, i_amopstrategy);
6567 amopreqcheck = PQgetvalue(res, i, i_amopreqcheck);
6568 amopopr = PQgetvalue(res, i, i_amopopr);
6571 appendPQExpBuffer(q, " ,\n ");
6573 appendPQExpBuffer(q, "OPERATOR %s %s",
6574 amopstrategy, amopopr);
6575 if (strcmp(amopreqcheck, "t") == 0)
6576 appendPQExpBuffer(q, " RECHECK");
6584 * Now fetch and print the FUNCTION entries (pg_amproc rows).
6586 resetPQExpBuffer(query);
6588 appendPQExpBuffer(query, "SELECT amprocnum, "
6589 "amproc::pg_catalog.regprocedure "
6590 "FROM pg_catalog.pg_amproc "
6591 "WHERE amopclaid = '%u'::pg_catalog.oid "
6592 "ORDER BY amprocnum",
6593 opcinfo->dobj.catId.oid);
6595 res = PQexec(g_conn, query->data);
6596 check_sql_result(res, g_conn, query->data, PGRES_TUPLES_OK);
6598 ntups = PQntuples(res);
6600 i_amprocnum = PQfnumber(res, "amprocnum");
6601 i_amproc = PQfnumber(res, "amproc");
6603 for (i = 0; i < ntups; i++)
6605 amprocnum = PQgetvalue(res, i, i_amprocnum);
6606 amproc = PQgetvalue(res, i, i_amproc);
6609 appendPQExpBuffer(q, " ,\n ");
6611 appendPQExpBuffer(q, "FUNCTION %s %s",
6619 appendPQExpBuffer(q, ";\n");
6621 ArchiveEntry(fout, opcinfo->dobj.catId, opcinfo->dobj.dumpId,
6623 opcinfo->dobj.namespace->dobj.name,
6626 false, "OPERATOR CLASS", q->data, delq->data, NULL,
6627 opcinfo->dobj.dependencies, opcinfo->dobj.nDeps,
6630 /* Dump Operator Class Comments */
6631 resetPQExpBuffer(q);
6632 appendPQExpBuffer(q, "OPERATOR CLASS %s",
6633 fmtId(opcinfo->dobj.name));
6634 appendPQExpBuffer(q, " USING %s",
6636 dumpComment(fout, q->data,
6637 NULL, opcinfo->rolname,
6638 opcinfo->dobj.catId, 0, opcinfo->dobj.dumpId);
6641 destroyPQExpBuffer(query);
6642 destroyPQExpBuffer(q);
6643 destroyPQExpBuffer(delq);
6648 * write out a single conversion definition
6651 dumpConversion(Archive *fout, ConvInfo *convinfo)
6656 PQExpBuffer details;
6660 int i_conforencoding;
6661 int i_contoencoding;
6664 const char *conname;
6665 const char *conforencoding;
6666 const char *contoencoding;
6667 const char *conproc;
6670 /* Skip if not to be dumped */
6671 if (!convinfo->dobj.dump || dataOnly)
6674 query = createPQExpBuffer();
6675 q = createPQExpBuffer();
6676 delq = createPQExpBuffer();
6677 details = createPQExpBuffer();
6679 /* Make sure we are in proper schema */
6680 selectSourceSchema(convinfo->dobj.namespace->dobj.name);
6682 /* Get conversion-specific details */
6683 appendPQExpBuffer(query, "SELECT conname, "
6684 "pg_catalog.pg_encoding_to_char(conforencoding) AS conforencoding, "
6685 "pg_catalog.pg_encoding_to_char(contoencoding) AS contoencoding, "
6686 "conproc, condefault "
6687 "FROM pg_catalog.pg_conversion c "
6688 "WHERE c.oid = '%u'::pg_catalog.oid",
6689 convinfo->dobj.catId.oid);
6691 res = PQexec(g_conn, query->data);
6692 check_sql_result(res, g_conn, query->data, PGRES_TUPLES_OK);
6694 /* Expecting a single result only */
6695 ntups = PQntuples(res);
6698 write_msg(NULL, "Got %d rows instead of one from: %s",
6699 ntups, query->data);
6703 i_conname = PQfnumber(res, "conname");
6704 i_conforencoding = PQfnumber(res, "conforencoding");
6705 i_contoencoding = PQfnumber(res, "contoencoding");
6706 i_conproc = PQfnumber(res, "conproc");
6707 i_condefault = PQfnumber(res, "condefault");
6709 conname = PQgetvalue(res, 0, i_conname);
6710 conforencoding = PQgetvalue(res, 0, i_conforencoding);
6711 contoencoding = PQgetvalue(res, 0, i_contoencoding);
6712 conproc = PQgetvalue(res, 0, i_conproc);
6713 condefault = (PQgetvalue(res, 0, i_condefault)[0] == 't');
6716 * DROP must be fully qualified in case same name appears in pg_catalog
6718 appendPQExpBuffer(delq, "DROP CONVERSION %s",
6719 fmtId(convinfo->dobj.namespace->dobj.name));
6720 appendPQExpBuffer(delq, ".%s;\n",
6721 fmtId(convinfo->dobj.name));
6723 appendPQExpBuffer(q, "CREATE %sCONVERSION %s FOR ",
6724 (condefault) ? "DEFAULT " : "",
6725 fmtId(convinfo->dobj.name));
6726 appendStringLiteralAH(q, conforencoding, fout);
6727 appendPQExpBuffer(q, " TO ");
6728 appendStringLiteralAH(q, contoencoding, fout);
6729 /* regproc is automatically quoted in 7.3 and above */
6730 appendPQExpBuffer(q, " FROM %s;\n", conproc);
6732 ArchiveEntry(fout, convinfo->dobj.catId, convinfo->dobj.dumpId,
6733 convinfo->dobj.name,
6734 convinfo->dobj.namespace->dobj.name,
6737 false, "CONVERSION", q->data, delq->data, NULL,
6738 convinfo->dobj.dependencies, convinfo->dobj.nDeps,
6741 /* Dump Conversion Comments */
6742 resetPQExpBuffer(q);
6743 appendPQExpBuffer(q, "CONVERSION %s", fmtId(convinfo->dobj.name));
6744 dumpComment(fout, q->data,
6745 convinfo->dobj.namespace->dobj.name, convinfo->rolname,
6746 convinfo->dobj.catId, 0, convinfo->dobj.dumpId);
6750 destroyPQExpBuffer(query);
6751 destroyPQExpBuffer(q);
6752 destroyPQExpBuffer(delq);
6753 destroyPQExpBuffer(details);
6757 * format_aggregate_signature: generate aggregate name and argument list
6759 * The argument type names are qualified if needed. The aggregate name
6760 * is never qualified.
6763 format_aggregate_signature(AggInfo *agginfo, Archive *fout, bool honor_quotes)
6765 PQExpBufferData buf;
6767 initPQExpBuffer(&buf);
6769 appendPQExpBuffer(&buf, "%s",
6770 fmtId(agginfo->aggfn.dobj.name));
6772 appendPQExpBuffer(&buf, "%s", agginfo->aggfn.dobj.name);
6774 /* If using regtype or format_type, fmtbasetype is already quoted */
6775 if (fout->remoteVersion >= 70100)
6777 if (agginfo->anybasetype)
6778 appendPQExpBuffer(&buf, "(*)");
6780 appendPQExpBuffer(&buf, "(%s)", agginfo->fmtbasetype);
6784 if (agginfo->anybasetype)
6785 appendPQExpBuffer(&buf, "(*)");
6787 appendPQExpBuffer(&buf, "(%s)",
6788 fmtId(agginfo->fmtbasetype));
6796 * write out a single aggregate definition
6799 dumpAgg(Archive *fout, AggInfo *agginfo)
6804 PQExpBuffer details;
6817 const char *aggtransfn;
6818 const char *aggfinalfn;
6819 const char *aggsortop;
6820 const char *aggtranstype;
6821 const char *agginitval;
6824 /* Skip if not to be dumped */
6825 if (!agginfo->aggfn.dobj.dump || dataOnly)
6828 query = createPQExpBuffer();
6829 q = createPQExpBuffer();
6830 delq = createPQExpBuffer();
6831 details = createPQExpBuffer();
6833 /* Make sure we are in proper schema */
6834 selectSourceSchema(agginfo->aggfn.dobj.namespace->dobj.name);
6836 /* Get aggregate-specific details */
6837 if (g_fout->remoteVersion >= 80100)
6839 appendPQExpBuffer(query, "SELECT aggtransfn, "
6840 "aggfinalfn, aggtranstype::pg_catalog.regtype, "
6841 "aggsortop::pg_catalog.regoperator, "
6843 "proargtypes[0] = 'pg_catalog.\"any\"'::pg_catalog.regtype as anybasetype, "
6844 "proargtypes[0]::pg_catalog.regtype as fmtbasetype, "
6845 "'t'::boolean as convertok "
6846 "from pg_catalog.pg_aggregate a, pg_catalog.pg_proc p "
6847 "where a.aggfnoid = p.oid "
6848 "and p.oid = '%u'::pg_catalog.oid",
6849 agginfo->aggfn.dobj.catId.oid);
6851 else if (g_fout->remoteVersion >= 70300)
6853 appendPQExpBuffer(query, "SELECT aggtransfn, "
6854 "aggfinalfn, aggtranstype::pg_catalog.regtype, "
6857 "proargtypes[0] = 'pg_catalog.\"any\"'::pg_catalog.regtype as anybasetype, "
6858 "proargtypes[0]::pg_catalog.regtype as fmtbasetype, "
6859 "'t'::boolean as convertok "
6860 "from pg_catalog.pg_aggregate a, pg_catalog.pg_proc p "
6861 "where a.aggfnoid = p.oid "
6862 "and p.oid = '%u'::pg_catalog.oid",
6863 agginfo->aggfn.dobj.catId.oid);
6865 else if (g_fout->remoteVersion >= 70100)
6867 appendPQExpBuffer(query, "SELECT aggtransfn, aggfinalfn, "
6868 "format_type(aggtranstype, NULL) as aggtranstype, "
6871 "aggbasetype = 0 as anybasetype, "
6872 "CASE WHEN aggbasetype = 0 THEN '-' "
6873 "ELSE format_type(aggbasetype, NULL) END as fmtbasetype, "
6874 "'t'::boolean as convertok "
6875 "from pg_aggregate "
6876 "where oid = '%u'::oid",
6877 agginfo->aggfn.dobj.catId.oid);
6881 appendPQExpBuffer(query, "SELECT aggtransfn1 as aggtransfn, "
6883 "(select typname from pg_type where oid = aggtranstype1) as aggtranstype, "
6885 "agginitval1 as agginitval, "
6886 "aggbasetype = 0 as anybasetype, "
6887 "(select typname from pg_type where oid = aggbasetype) as fmtbasetype, "
6888 "(aggtransfn2 = 0 and aggtranstype2 = 0 and agginitval2 is null) as convertok "
6889 "from pg_aggregate "
6890 "where oid = '%u'::oid",
6891 agginfo->aggfn.dobj.catId.oid);
6894 res = PQexec(g_conn, query->data);
6895 check_sql_result(res, g_conn, query->data, PGRES_TUPLES_OK);
6897 /* Expecting a single result only */
6898 ntups = PQntuples(res);
6901 write_msg(NULL, "Got %d rows instead of one from: %s",
6902 ntups, query->data);
6906 i_aggtransfn = PQfnumber(res, "aggtransfn");
6907 i_aggfinalfn = PQfnumber(res, "aggfinalfn");
6908 i_aggsortop = PQfnumber(res, "aggsortop");
6909 i_aggtranstype = PQfnumber(res, "aggtranstype");
6910 i_agginitval = PQfnumber(res, "agginitval");
6911 i_anybasetype = PQfnumber(res, "anybasetype");
6912 i_fmtbasetype = PQfnumber(res, "fmtbasetype");
6913 i_convertok = PQfnumber(res, "convertok");
6915 aggtransfn = PQgetvalue(res, 0, i_aggtransfn);
6916 aggfinalfn = PQgetvalue(res, 0, i_aggfinalfn);
6917 aggsortop = PQgetvalue(res, 0, i_aggsortop);
6918 aggtranstype = PQgetvalue(res, 0, i_aggtranstype);
6919 agginitval = PQgetvalue(res, 0, i_agginitval);
6920 /* we save anybasetype for format_aggregate_signature */
6921 agginfo->anybasetype = (PQgetvalue(res, 0, i_anybasetype)[0] == 't');
6922 /* we save fmtbasetype for format_aggregate_signature */
6923 agginfo->fmtbasetype = strdup(PQgetvalue(res, 0, i_fmtbasetype));
6924 convertok = (PQgetvalue(res, 0, i_convertok)[0] == 't');
6926 aggsig = format_aggregate_signature(agginfo, fout, true);
6927 aggsig_tag = format_aggregate_signature(agginfo, fout, false);
6931 write_msg(NULL, "WARNING: aggregate function %s could not be dumped correctly for this database version; ignored\n",
6936 if (g_fout->remoteVersion >= 70300)
6938 /* If using 7.3's regproc or regtype, data is already quoted */
6939 appendPQExpBuffer(details, " BASETYPE = %s,\n SFUNC = %s,\n STYPE = %s",
6940 agginfo->anybasetype ? "'any'" :
6941 agginfo->fmtbasetype,
6945 else if (g_fout->remoteVersion >= 70100)
6947 /* format_type quotes, regproc does not */
6948 appendPQExpBuffer(details, " BASETYPE = %s,\n SFUNC = %s,\n STYPE = %s",
6949 agginfo->anybasetype ? "'any'" :
6950 agginfo->fmtbasetype,
6956 /* need quotes all around */
6957 appendPQExpBuffer(details, " BASETYPE = %s,\n",
6958 agginfo->anybasetype ? "'any'" :
6959 fmtId(agginfo->fmtbasetype));
6960 appendPQExpBuffer(details, " SFUNC = %s,\n",
6962 appendPQExpBuffer(details, " STYPE = %s",
6963 fmtId(aggtranstype));
6966 if (!PQgetisnull(res, 0, i_agginitval))
6968 appendPQExpBuffer(details, ",\n INITCOND = ");
6969 appendStringLiteralAH(details, agginitval, fout);
6972 if (strcmp(aggfinalfn, "-") != 0)
6974 appendPQExpBuffer(details, ",\n FINALFUNC = %s",
6978 aggsortop = convertOperatorReference(aggsortop);
6981 appendPQExpBuffer(details, ",\n SORTOP = %s",
6986 * DROP must be fully qualified in case same name appears in pg_catalog
6988 appendPQExpBuffer(delq, "DROP AGGREGATE %s.%s;\n",
6989 fmtId(agginfo->aggfn.dobj.namespace->dobj.name),
6992 appendPQExpBuffer(q, "CREATE AGGREGATE %s (\n%s\n);\n",
6993 fmtId(agginfo->aggfn.dobj.name),
6996 ArchiveEntry(fout, agginfo->aggfn.dobj.catId, agginfo->aggfn.dobj.dumpId,
6998 agginfo->aggfn.dobj.namespace->dobj.name,
7000 agginfo->aggfn.rolname,
7001 false, "AGGREGATE", q->data, delq->data, NULL,
7002 agginfo->aggfn.dobj.dependencies, agginfo->aggfn.dobj.nDeps,
7005 /* Dump Aggregate Comments */
7006 resetPQExpBuffer(q);
7007 appendPQExpBuffer(q, "AGGREGATE %s", aggsig);
7008 dumpComment(fout, q->data,
7009 agginfo->aggfn.dobj.namespace->dobj.name, agginfo->aggfn.rolname,
7010 agginfo->aggfn.dobj.catId, 0, agginfo->aggfn.dobj.dumpId);
7013 * Since there is no GRANT ON AGGREGATE syntax, we have to make the ACL
7014 * command look like a function's GRANT; in particular this affects the
7015 * syntax for aggregates on ANY.
7020 aggsig = format_function_signature(&agginfo->aggfn, true);
7021 aggsig_tag = format_function_signature(&agginfo->aggfn, false);
7023 dumpACL(fout, agginfo->aggfn.dobj.catId, agginfo->aggfn.dobj.dumpId,
7026 agginfo->aggfn.dobj.namespace->dobj.name,
7027 agginfo->aggfn.rolname, agginfo->aggfn.proacl);
7034 destroyPQExpBuffer(query);
7035 destroyPQExpBuffer(q);
7036 destroyPQExpBuffer(delq);
7037 destroyPQExpBuffer(details);
7042 * Write out grant/revoke information
7044 * 'objCatId' is the catalog ID of the underlying object.
7045 * 'objDumpId' is the dump ID of the underlying object.
7046 * 'type' must be TABLE, FUNCTION, LANGUAGE, SCHEMA, DATABASE, or TABLESPACE.
7047 * 'name' is the formatted name of the object. Must be quoted etc. already.
7048 * 'tag' is the tag for the archive entry (typ. unquoted name of object).
7049 * 'nspname' is the namespace the object is in (NULL if none).
7050 * 'owner' is the owner, NULL if there is no owner (for languages).
7051 * 'acls' is the string read out of the fooacl system catalog field;
7052 * it will be parsed here.
7056 dumpACL(Archive *fout, CatalogId objCatId, DumpId objDumpId,
7057 const char *type, const char *name,
7058 const char *tag, const char *nspname, const char *owner,
7063 /* Do nothing if ACL dump is not enabled */
7064 if (dataOnly || aclsSkip)
7067 sql = createPQExpBuffer();
7069 if (!buildACLCommands(name, type, acls, owner, fout->remoteVersion, sql))
7071 write_msg(NULL, "could not parse ACL list (%s) for object \"%s\" (%s)\n",
7077 ArchiveEntry(fout, nilCatalogId, createDumpId(),
7081 false, "ACL", sql->data, "", NULL,
7085 destroyPQExpBuffer(sql);
7090 * write out to fout the declarations (not data) of a user-defined table
7093 dumpTable(Archive *fout, TableInfo *tbinfo)
7097 if (tbinfo->dobj.dump)
7099 if (tbinfo->relkind == RELKIND_SEQUENCE)
7100 dumpSequence(fout, tbinfo);
7102 dumpTableSchema(fout, tbinfo);
7104 /* Handle the ACL here */
7105 namecopy = strdup(fmtId(tbinfo->dobj.name));
7106 dumpACL(fout, tbinfo->dobj.catId, tbinfo->dobj.dumpId,
7107 (tbinfo->relkind == RELKIND_SEQUENCE) ? "SEQUENCE" : "TABLE",
7108 namecopy, tbinfo->dobj.name,
7109 tbinfo->dobj.namespace->dobj.name, tbinfo->rolname,
7117 * write the declaration (not data) of one user-defined table or view
7120 dumpTableSchema(Archive *fout, TableInfo *tbinfo)
7122 PQExpBuffer query = createPQExpBuffer();
7123 PQExpBuffer q = createPQExpBuffer();
7124 PQExpBuffer delq = createPQExpBuffer();
7127 TableInfo **parents;
7128 int actual_atts; /* number of attrs in this CREATE statment */
7134 /* Make sure we are in proper schema */
7135 selectSourceSchema(tbinfo->dobj.namespace->dobj.name);
7137 /* Is it a table or a view? */
7138 if (tbinfo->relkind == RELKIND_VIEW)
7142 reltypename = "VIEW";
7144 /* Fetch the view definition */
7145 if (g_fout->remoteVersion >= 70300)
7147 /* Beginning in 7.3, viewname is not unique; rely on OID */
7148 appendPQExpBuffer(query,
7149 "SELECT pg_catalog.pg_get_viewdef('%u'::pg_catalog.oid) as viewdef",
7150 tbinfo->dobj.catId.oid);
7154 appendPQExpBuffer(query, "SELECT definition as viewdef "
7155 " from pg_views where viewname = ");
7156 appendStringLiteralAH(query, tbinfo->dobj.name, fout);
7157 appendPQExpBuffer(query, ";");
7160 res = PQexec(g_conn, query->data);
7161 check_sql_result(res, g_conn, query->data, PGRES_TUPLES_OK);
7163 if (PQntuples(res) != 1)
7165 if (PQntuples(res) < 1)
7166 write_msg(NULL, "query to obtain definition of view \"%s\" returned no data\n",
7169 write_msg(NULL, "query to obtain definition of view \"%s\" returned more than one definition\n",
7174 viewdef = PQgetvalue(res, 0, 0);
7176 if (strlen(viewdef) == 0)
7178 write_msg(NULL, "definition of view \"%s\" appears to be empty (length zero)\n",
7184 * DROP must be fully qualified in case same name appears in
7187 appendPQExpBuffer(delq, "DROP VIEW %s.",
7188 fmtId(tbinfo->dobj.namespace->dobj.name));
7189 appendPQExpBuffer(delq, "%s;\n",
7190 fmtId(tbinfo->dobj.name));
7192 appendPQExpBuffer(q, "CREATE VIEW %s AS\n %s\n",
7193 fmtId(tbinfo->dobj.name), viewdef);
7199 reltypename = "TABLE";
7200 numParents = tbinfo->numParents;
7201 parents = tbinfo->parents;
7204 * DROP must be fully qualified in case same name appears in
7207 appendPQExpBuffer(delq, "DROP TABLE %s.",
7208 fmtId(tbinfo->dobj.namespace->dobj.name));
7209 appendPQExpBuffer(delq, "%s;\n",
7210 fmtId(tbinfo->dobj.name));
7212 appendPQExpBuffer(q, "CREATE TABLE %s (",
7213 fmtId(tbinfo->dobj.name));
7215 for (j = 0; j < tbinfo->numatts; j++)
7217 /* Is this one of the table's own attrs, and not dropped ? */
7218 if (!tbinfo->inhAttrs[j] && !tbinfo->attisdropped[j])
7220 /* Format properly if not first attr */
7221 if (actual_atts > 0)
7222 appendPQExpBuffer(q, ",");
7223 appendPQExpBuffer(q, "\n ");
7225 /* Attribute name */
7226 appendPQExpBuffer(q, "%s ",
7227 fmtId(tbinfo->attnames[j]));
7229 /* Attribute type */
7230 if (g_fout->remoteVersion >= 70100)
7232 char *typname = tbinfo->atttypnames[j];
7234 if (tbinfo->attisserial[j])
7236 if (strcmp(typname, "integer") == 0)
7238 else if (strcmp(typname, "bigint") == 0)
7239 typname = "bigserial";
7241 appendPQExpBuffer(q, "%s", typname);
7245 /* If no format_type, fake it */
7246 appendPQExpBuffer(q, "%s",
7247 myFormatType(tbinfo->atttypnames[j],
7248 tbinfo->atttypmod[j]));
7252 * Default value --- suppress if inherited, serial, or to be
7253 * printed separately.
7255 if (tbinfo->attrdefs[j] != NULL &&
7256 !tbinfo->inhAttrDef[j] &&
7257 !tbinfo->attisserial[j] &&
7258 !tbinfo->attrdefs[j]->separate)
7259 appendPQExpBuffer(q, " DEFAULT %s",
7260 tbinfo->attrdefs[j]->adef_expr);
7263 * Not Null constraint --- suppress if inherited
7265 * Note: we could suppress this for serial columns since
7266 * SERIAL implies NOT NULL. We choose not to for forward
7267 * compatibility, since there has been some talk of making
7268 * SERIAL not imply NOT NULL, in which case the explicit
7269 * specification would be needed.
7271 if (tbinfo->notnull[j] && !tbinfo->inhNotNull[j])
7272 appendPQExpBuffer(q, " NOT NULL");
7279 * Add non-inherited CHECK constraints, if any.
7281 for (j = 0; j < tbinfo->ncheck; j++)
7283 ConstraintInfo *constr = &(tbinfo->checkexprs[j]);
7285 if (constr->coninherited || constr->separate)
7288 if (actual_atts > 0)
7289 appendPQExpBuffer(q, ",\n ");
7291 appendPQExpBuffer(q, "CONSTRAINT %s ",
7292 fmtId(constr->dobj.name));
7293 appendPQExpBuffer(q, "%s", constr->condef);
7298 appendPQExpBuffer(q, "\n)");
7302 appendPQExpBuffer(q, "\nINHERITS (");
7303 for (k = 0; k < numParents; k++)
7305 TableInfo *parentRel = parents[k];
7308 appendPQExpBuffer(q, ", ");
7309 if (parentRel->dobj.namespace != tbinfo->dobj.namespace)
7310 appendPQExpBuffer(q, "%s.",
7311 fmtId(parentRel->dobj.namespace->dobj.name));
7312 appendPQExpBuffer(q, "%s",
7313 fmtId(parentRel->dobj.name));
7315 appendPQExpBuffer(q, ")");
7318 if (tbinfo->reloptions && strlen(tbinfo->reloptions) > 0)
7319 appendPQExpBuffer(q, "\nWITH (%s)", tbinfo->reloptions);
7321 appendPQExpBuffer(q, ";\n");
7323 /* Loop dumping statistics and storage statements */
7324 for (j = 0; j < tbinfo->numatts; j++)
7327 * Dump per-column statistics information. We only issue an ALTER
7328 * TABLE statement if the attstattarget entry for this column is
7329 * non-negative (i.e. it's not the default value)
7331 if (tbinfo->attstattarget[j] >= 0 &&
7332 !tbinfo->attisdropped[j])
7334 appendPQExpBuffer(q, "ALTER TABLE ONLY %s ",
7335 fmtId(tbinfo->dobj.name));
7336 appendPQExpBuffer(q, "ALTER COLUMN %s ",
7337 fmtId(tbinfo->attnames[j]));
7338 appendPQExpBuffer(q, "SET STATISTICS %d;\n",
7339 tbinfo->attstattarget[j]);
7343 * Dump per-column storage information. The statement is only
7344 * dumped if the storage has been changed from the type's default.
7346 if (!tbinfo->attisdropped[j] && tbinfo->attstorage[j] != tbinfo->typstorage[j])
7348 switch (tbinfo->attstorage[j])
7354 storage = "EXTERNAL";
7360 storage = "EXTENDED";
7367 * Only dump the statement if it's a storage type we recognize
7369 if (storage != NULL)
7371 appendPQExpBuffer(q, "ALTER TABLE ONLY %s ",
7372 fmtId(tbinfo->dobj.name));
7373 appendPQExpBuffer(q, "ALTER COLUMN %s ",
7374 fmtId(tbinfo->attnames[j]));
7375 appendPQExpBuffer(q, "SET STORAGE %s;\n",
7382 ArchiveEntry(fout, tbinfo->dobj.catId, tbinfo->dobj.dumpId,
7384 tbinfo->dobj.namespace->dobj.name,
7385 (tbinfo->relkind == RELKIND_VIEW) ? NULL : tbinfo->reltablespace,
7387 (strcmp(reltypename, "TABLE") == 0) ? tbinfo->hasoids : false,
7388 reltypename, q->data, delq->data, NULL,
7389 tbinfo->dobj.dependencies, tbinfo->dobj.nDeps,
7392 /* Dump Table Comments */
7393 dumpTableComment(fout, tbinfo, reltypename);
7395 /* Dump comments on inlined table constraints */
7396 for (j = 0; j < tbinfo->ncheck; j++)
7398 ConstraintInfo *constr = &(tbinfo->checkexprs[j]);
7400 if (constr->coninherited || constr->separate)
7403 dumpTableConstraintComment(fout, constr);
7406 destroyPQExpBuffer(query);
7407 destroyPQExpBuffer(q);
7408 destroyPQExpBuffer(delq);
7412 * dumpAttrDef --- dump an attribute's default-value declaration
7415 dumpAttrDef(Archive *fout, AttrDefInfo *adinfo)
7417 TableInfo *tbinfo = adinfo->adtable;
7418 int adnum = adinfo->adnum;
7422 /* Only print it if "separate" mode is selected */
7423 if (!tbinfo->dobj.dump || !adinfo->separate || dataOnly)
7426 /* Don't print inherited or serial defaults, either */
7427 if (tbinfo->inhAttrDef[adnum - 1] || tbinfo->attisserial[adnum - 1])
7430 q = createPQExpBuffer();
7431 delq = createPQExpBuffer();
7433 appendPQExpBuffer(q, "ALTER TABLE %s ",
7434 fmtId(tbinfo->dobj.name));
7435 appendPQExpBuffer(q, "ALTER COLUMN %s SET DEFAULT %s;\n",
7436 fmtId(tbinfo->attnames[adnum - 1]),
7440 * DROP must be fully qualified in case same name appears in pg_catalog
7442 appendPQExpBuffer(delq, "ALTER TABLE %s.",
7443 fmtId(tbinfo->dobj.namespace->dobj.name));
7444 appendPQExpBuffer(delq, "%s ",
7445 fmtId(tbinfo->dobj.name));
7446 appendPQExpBuffer(delq, "ALTER COLUMN %s DROP DEFAULT;\n",
7447 fmtId(tbinfo->attnames[adnum - 1]));
7449 ArchiveEntry(fout, adinfo->dobj.catId, adinfo->dobj.dumpId,
7450 tbinfo->attnames[adnum - 1],
7451 tbinfo->dobj.namespace->dobj.name,
7454 false, "DEFAULT", q->data, delq->data, NULL,
7455 adinfo->dobj.dependencies, adinfo->dobj.nDeps,
7458 destroyPQExpBuffer(q);
7459 destroyPQExpBuffer(delq);
7463 * getAttrName: extract the correct name for an attribute
7465 * The array tblInfo->attnames[] only provides names of user attributes;
7466 * if a system attribute number is supplied, we have to fake it.
7467 * We also do a little bit of bounds checking for safety's sake.
7470 getAttrName(int attrnum, TableInfo *tblInfo)
7472 if (attrnum > 0 && attrnum <= tblInfo->numatts)
7473 return tblInfo->attnames[attrnum - 1];
7476 case SelfItemPointerAttributeNumber:
7478 case ObjectIdAttributeNumber:
7480 case MinTransactionIdAttributeNumber:
7482 case MinCommandIdAttributeNumber:
7484 case MaxTransactionIdAttributeNumber:
7486 case MaxCommandIdAttributeNumber:
7488 case TableOidAttributeNumber:
7491 write_msg(NULL, "invalid column number %d for table \"%s\"\n",
7492 attrnum, tblInfo->dobj.name);
7494 return NULL; /* keep compiler quiet */
7499 * write out to fout a user-defined index
7502 dumpIndex(Archive *fout, IndxInfo *indxinfo)
7504 TableInfo *tbinfo = indxinfo->indextable;
7511 q = createPQExpBuffer();
7512 delq = createPQExpBuffer();
7515 * If there's an associated constraint, don't dump the index per se, but
7516 * do dump any comment for it. (This is safe because dependency ordering
7517 * will have ensured the constraint is emitted first.)
7519 if (indxinfo->indexconstraint == 0)
7521 /* Plain secondary index */
7522 appendPQExpBuffer(q, "%s;\n", indxinfo->indexdef);
7524 /* If the index is clustered, we need to record that. */
7525 if (indxinfo->indisclustered)
7527 appendPQExpBuffer(q, "\nALTER TABLE %s CLUSTER",
7528 fmtId(tbinfo->dobj.name));
7529 appendPQExpBuffer(q, " ON %s;\n",
7530 fmtId(indxinfo->dobj.name));
7534 * DROP must be fully qualified in case same name appears in
7537 appendPQExpBuffer(delq, "DROP INDEX %s.",
7538 fmtId(tbinfo->dobj.namespace->dobj.name));
7539 appendPQExpBuffer(delq, "%s;\n",
7540 fmtId(indxinfo->dobj.name));
7542 ArchiveEntry(fout, indxinfo->dobj.catId, indxinfo->dobj.dumpId,
7543 indxinfo->dobj.name,
7544 tbinfo->dobj.namespace->dobj.name,
7545 indxinfo->tablespace,
7546 tbinfo->rolname, false,
7547 "INDEX", q->data, delq->data, NULL,
7548 indxinfo->dobj.dependencies, indxinfo->dobj.nDeps,
7552 /* Dump Index Comments */
7553 resetPQExpBuffer(q);
7554 appendPQExpBuffer(q, "INDEX %s",
7555 fmtId(indxinfo->dobj.name));
7556 dumpComment(fout, q->data,
7557 tbinfo->dobj.namespace->dobj.name,
7559 indxinfo->dobj.catId, 0, indxinfo->dobj.dumpId);
7561 destroyPQExpBuffer(q);
7562 destroyPQExpBuffer(delq);
7567 * write out to fout a user-defined constraint
7570 dumpConstraint(Archive *fout, ConstraintInfo *coninfo)
7572 TableInfo *tbinfo = coninfo->contable;
7576 /* Skip if not to be dumped */
7577 if (!coninfo->dobj.dump || dataOnly)
7580 q = createPQExpBuffer();
7581 delq = createPQExpBuffer();
7583 if (coninfo->contype == 'p' || coninfo->contype == 'u')
7585 /* Index-related constraint */
7589 indxinfo = (IndxInfo *) findObjectByDumpId(coninfo->conindex);
7591 if (indxinfo == NULL)
7593 write_msg(NULL, "missing index for constraint \"%s\"\n",
7594 coninfo->dobj.name);
7598 appendPQExpBuffer(q, "ALTER TABLE ONLY %s\n",
7599 fmtId(tbinfo->dobj.name));
7600 appendPQExpBuffer(q, " ADD CONSTRAINT %s %s (",
7601 fmtId(coninfo->dobj.name),
7602 coninfo->contype == 'p' ? "PRIMARY KEY" : "UNIQUE");
7604 for (k = 0; k < indxinfo->indnkeys; k++)
7606 int indkey = (int) indxinfo->indkeys[k];
7607 const char *attname;
7609 if (indkey == InvalidAttrNumber)
7611 attname = getAttrName(indkey, tbinfo);
7613 appendPQExpBuffer(q, "%s%s",
7614 (k == 0) ? "" : ", ",
7618 appendPQExpBuffer(q, ")");
7620 if (indxinfo->options && strlen(indxinfo->options) > 0)
7621 appendPQExpBuffer(q, " WITH (%s)", indxinfo->options);
7623 appendPQExpBuffer(q, ";\n");
7625 /* If the index is clustered, we need to record that. */
7626 if (indxinfo->indisclustered)
7628 appendPQExpBuffer(q, "\nALTER TABLE %s CLUSTER",
7629 fmtId(tbinfo->dobj.name));
7630 appendPQExpBuffer(q, " ON %s;\n",
7631 fmtId(indxinfo->dobj.name));
7635 * DROP must be fully qualified in case same name appears in
7638 appendPQExpBuffer(delq, "ALTER TABLE ONLY %s.",
7639 fmtId(tbinfo->dobj.namespace->dobj.name));
7640 appendPQExpBuffer(delq, "%s ",
7641 fmtId(tbinfo->dobj.name));
7642 appendPQExpBuffer(delq, "DROP CONSTRAINT %s;\n",
7643 fmtId(coninfo->dobj.name));
7645 ArchiveEntry(fout, coninfo->dobj.catId, coninfo->dobj.dumpId,
7647 tbinfo->dobj.namespace->dobj.name,
7648 indxinfo->tablespace,
7649 tbinfo->rolname, false,
7650 "CONSTRAINT", q->data, delq->data, NULL,
7651 coninfo->dobj.dependencies, coninfo->dobj.nDeps,
7654 else if (coninfo->contype == 'f')
7657 * XXX Potentially wrap in a 'SET CONSTRAINTS OFF' block so that the
7658 * current table data is not processed
7660 appendPQExpBuffer(q, "ALTER TABLE ONLY %s\n",
7661 fmtId(tbinfo->dobj.name));
7662 appendPQExpBuffer(q, " ADD CONSTRAINT %s %s;\n",
7663 fmtId(coninfo->dobj.name),
7667 * DROP must be fully qualified in case same name appears in
7670 appendPQExpBuffer(delq, "ALTER TABLE ONLY %s.",
7671 fmtId(tbinfo->dobj.namespace->dobj.name));
7672 appendPQExpBuffer(delq, "%s ",
7673 fmtId(tbinfo->dobj.name));
7674 appendPQExpBuffer(delq, "DROP CONSTRAINT %s;\n",
7675 fmtId(coninfo->dobj.name));
7677 ArchiveEntry(fout, coninfo->dobj.catId, coninfo->dobj.dumpId,
7679 tbinfo->dobj.namespace->dobj.name,
7681 tbinfo->rolname, false,
7682 "FK CONSTRAINT", q->data, delq->data, NULL,
7683 coninfo->dobj.dependencies, coninfo->dobj.nDeps,
7686 else if (coninfo->contype == 'c' && tbinfo)
7688 /* CHECK constraint on a table */
7690 /* Ignore if not to be dumped separately */
7691 if (coninfo->separate)
7693 /* not ONLY since we want it to propagate to children */
7694 appendPQExpBuffer(q, "ALTER TABLE %s\n",
7695 fmtId(tbinfo->dobj.name));
7696 appendPQExpBuffer(q, " ADD CONSTRAINT %s %s;\n",
7697 fmtId(coninfo->dobj.name),
7701 * DROP must be fully qualified in case same name appears in
7704 appendPQExpBuffer(delq, "ALTER TABLE %s.",
7705 fmtId(tbinfo->dobj.namespace->dobj.name));
7706 appendPQExpBuffer(delq, "%s ",
7707 fmtId(tbinfo->dobj.name));
7708 appendPQExpBuffer(delq, "DROP CONSTRAINT %s;\n",
7709 fmtId(coninfo->dobj.name));
7711 ArchiveEntry(fout, coninfo->dobj.catId, coninfo->dobj.dumpId,
7713 tbinfo->dobj.namespace->dobj.name,
7715 tbinfo->rolname, false,
7716 "CHECK CONSTRAINT", q->data, delq->data, NULL,
7717 coninfo->dobj.dependencies, coninfo->dobj.nDeps,
7721 else if (coninfo->contype == 'c' && tbinfo == NULL)
7723 /* CHECK constraint on a domain */
7724 TypeInfo *tinfo = coninfo->condomain;
7726 /* Ignore if not to be dumped separately */
7727 if (coninfo->separate)
7729 appendPQExpBuffer(q, "ALTER DOMAIN %s\n",
7730 fmtId(tinfo->dobj.name));
7731 appendPQExpBuffer(q, " ADD CONSTRAINT %s %s;\n",
7732 fmtId(coninfo->dobj.name),
7736 * DROP must be fully qualified in case same name appears in
7739 appendPQExpBuffer(delq, "ALTER DOMAIN %s.",
7740 fmtId(tinfo->dobj.namespace->dobj.name));
7741 appendPQExpBuffer(delq, "%s ",
7742 fmtId(tinfo->dobj.name));
7743 appendPQExpBuffer(delq, "DROP CONSTRAINT %s;\n",
7744 fmtId(coninfo->dobj.name));
7746 ArchiveEntry(fout, coninfo->dobj.catId, coninfo->dobj.dumpId,
7748 tinfo->dobj.namespace->dobj.name,
7750 tinfo->rolname, false,
7751 "CHECK CONSTRAINT", q->data, delq->data, NULL,
7752 coninfo->dobj.dependencies, coninfo->dobj.nDeps,
7758 write_msg(NULL, "unrecognized constraint type: %c\n", coninfo->contype);
7762 /* Dump Constraint Comments --- only works for table constraints */
7763 if (tbinfo && coninfo->separate)
7764 dumpTableConstraintComment(fout, coninfo);
7766 destroyPQExpBuffer(q);
7767 destroyPQExpBuffer(delq);
7771 * dumpTableConstraintComment --- dump a constraint's comment if any
7773 * This is split out because we need the function in two different places
7774 * depending on whether the constraint is dumped as part of CREATE TABLE
7775 * or as a separate ALTER command.
7778 dumpTableConstraintComment(Archive *fout, ConstraintInfo *coninfo)
7780 TableInfo *tbinfo = coninfo->contable;
7781 PQExpBuffer q = createPQExpBuffer();
7783 appendPQExpBuffer(q, "CONSTRAINT %s ",
7784 fmtId(coninfo->dobj.name));
7785 appendPQExpBuffer(q, "ON %s",
7786 fmtId(tbinfo->dobj.name));
7787 dumpComment(fout, q->data,
7788 tbinfo->dobj.namespace->dobj.name,
7790 coninfo->dobj.catId, 0,
7791 coninfo->separate ? coninfo->dobj.dumpId : tbinfo->dobj.dumpId);
7793 destroyPQExpBuffer(q);
7797 * findLastBuiltInOid -
7798 * find the last built in oid
7800 * For 7.1 and 7.2, we do this by retrieving datlastsysoid from the
7801 * pg_database entry for the current database
7804 findLastBuiltinOid_V71(const char *dbname)
7809 PQExpBuffer query = createPQExpBuffer();
7811 resetPQExpBuffer(query);
7812 appendPQExpBuffer(query, "SELECT datlastsysoid from pg_database where datname = ");
7813 appendStringLiteralAH(query, dbname, g_fout);
7815 res = PQexec(g_conn, query->data);
7816 check_sql_result(res, g_conn, query->data, PGRES_TUPLES_OK);
7818 ntups = PQntuples(res);
7821 write_msg(NULL, "missing pg_database entry for this database\n");
7826 write_msg(NULL, "found more than one pg_database entry for this database\n");
7829 last_oid = atooid(PQgetvalue(res, 0, PQfnumber(res, "datlastsysoid")));
7831 destroyPQExpBuffer(query);
7836 * findLastBuiltInOid -
7837 * find the last built in oid
7839 * For 7.0, we do this by assuming that the last thing that initdb does is to
7840 * create the pg_indexes view. This sucks in general, but seeing that 7.0.x
7841 * initdb won't be changing anymore, it'll do.
7844 findLastBuiltinOid_V70(void)
7850 res = PQexec(g_conn,
7851 "SELECT oid FROM pg_class WHERE relname = 'pg_indexes'");
7852 check_sql_result(res, g_conn,
7853 "SELECT oid FROM pg_class WHERE relname = 'pg_indexes'",
7855 ntups = PQntuples(res);
7858 write_msg(NULL, "could not find entry for pg_indexes in pg_class\n");
7863 write_msg(NULL, "found more than one entry for pg_indexes in pg_class\n");
7866 last_oid = atooid(PQgetvalue(res, 0, PQfnumber(res, "oid")));
7872 dumpSequence(Archive *fout, TableInfo *tbinfo)
7884 PQExpBuffer query = createPQExpBuffer();
7885 PQExpBuffer delqry = createPQExpBuffer();
7887 /* Make sure we are in proper schema */
7888 selectSourceSchema(tbinfo->dobj.namespace->dobj.name);
7890 snprintf(bufm, sizeof(bufm), INT64_FORMAT, SEQ_MINVALUE);
7891 snprintf(bufx, sizeof(bufx), INT64_FORMAT, SEQ_MAXVALUE);
7893 appendPQExpBuffer(query,
7894 "SELECT sequence_name, last_value, increment_by, "
7895 "CASE WHEN increment_by > 0 AND max_value = %s THEN NULL "
7896 " WHEN increment_by < 0 AND max_value = -1 THEN NULL "
7898 "END AS max_value, "
7899 "CASE WHEN increment_by > 0 AND min_value = 1 THEN NULL "
7900 " WHEN increment_by < 0 AND min_value = %s THEN NULL "
7902 "END AS min_value, "
7903 "cache_value, is_cycled, is_called from %s",
7905 fmtId(tbinfo->dobj.name));
7907 res = PQexec(g_conn, query->data);
7908 check_sql_result(res, g_conn, query->data, PGRES_TUPLES_OK);
7910 if (PQntuples(res) != 1)
7912 write_msg(NULL, "query to get data of sequence \"%s\" returned %d rows (expected 1)\n",
7913 tbinfo->dobj.name, PQntuples(res));
7917 /* Disable this check: it fails if sequence has been renamed */
7919 if (strcmp(PQgetvalue(res, 0, 0), tbinfo->dobj.name) != 0)
7921 write_msg(NULL, "query to get data of sequence \"%s\" returned name \"%s\"\n",
7922 tbinfo->dobj.name, PQgetvalue(res, 0, 0));
7927 last = PQgetvalue(res, 0, 1);
7928 incby = PQgetvalue(res, 0, 2);
7929 if (!PQgetisnull(res, 0, 3))
7930 maxv = PQgetvalue(res, 0, 3);
7931 if (!PQgetisnull(res, 0, 4))
7932 minv = PQgetvalue(res, 0, 4);
7933 cache = PQgetvalue(res, 0, 5);
7934 cycled = (strcmp(PQgetvalue(res, 0, 6), "t") == 0);
7935 called = (strcmp(PQgetvalue(res, 0, 7), "t") == 0);
7938 * The logic we use for restoring sequences is as follows:
7940 * Add a basic CREATE SEQUENCE statement (use last_val for start if called
7941 * is false, else use min_val for start_val). Skip this if the sequence
7942 * came from a SERIAL column.
7944 * Add a 'SETVAL(seq, last_val, iscalled)' at restore-time iff we load
7945 * data. We do this for serial sequences too.
7948 if (!dataOnly && !OidIsValid(tbinfo->owning_tab))
7950 resetPQExpBuffer(delqry);
7953 * DROP must be fully qualified in case same name appears in
7956 appendPQExpBuffer(delqry, "DROP SEQUENCE %s.",
7957 fmtId(tbinfo->dobj.namespace->dobj.name));
7958 appendPQExpBuffer(delqry, "%s;\n",
7959 fmtId(tbinfo->dobj.name));
7961 resetPQExpBuffer(query);
7962 appendPQExpBuffer(query,
7963 "CREATE SEQUENCE %s\n",
7964 fmtId(tbinfo->dobj.name));
7967 appendPQExpBuffer(query, " START WITH %s\n", last);
7969 appendPQExpBuffer(query, " INCREMENT BY %s\n", incby);
7972 appendPQExpBuffer(query, " MAXVALUE %s\n", maxv);
7974 appendPQExpBuffer(query, " NO MAXVALUE\n");
7977 appendPQExpBuffer(query, " MINVALUE %s\n", minv);
7979 appendPQExpBuffer(query, " NO MINVALUE\n");
7981 appendPQExpBuffer(query,
7983 cache, (cycled ? "\n CYCLE" : ""));
7985 appendPQExpBuffer(query, ";\n");
7987 ArchiveEntry(fout, tbinfo->dobj.catId, tbinfo->dobj.dumpId,
7989 tbinfo->dobj.namespace->dobj.name,
7992 false, "SEQUENCE", query->data, delqry->data, NULL,
7993 tbinfo->dobj.dependencies, tbinfo->dobj.nDeps,
7999 TableInfo *owning_tab;
8001 resetPQExpBuffer(query);
8002 appendPQExpBuffer(query, "SELECT pg_catalog.setval(");
8005 * If this is a SERIAL sequence, then use the pg_get_serial_sequence
8006 * function to avoid hard-coding the sequence name. Note that this
8007 * implicitly assumes that the sequence and its owning table are in
8008 * the same schema, because we don't schema-qualify the reference.
8010 if (OidIsValid(tbinfo->owning_tab) &&
8011 (owning_tab = findTableByOid(tbinfo->owning_tab)) != NULL)
8013 appendPQExpBuffer(query, "pg_catalog.pg_get_serial_sequence(");
8014 appendStringLiteralAH(query, fmtId(owning_tab->dobj.name), fout);
8015 appendPQExpBuffer(query, ", ");
8016 appendStringLiteralAH(query, owning_tab->attnames[tbinfo->owning_col - 1], fout);
8017 appendPQExpBuffer(query, ")");
8020 appendStringLiteralAH(query, fmtId(tbinfo->dobj.name), fout);
8021 appendPQExpBuffer(query, ", %s, %s);\n",
8022 last, (called ? "true" : "false"));
8024 ArchiveEntry(fout, nilCatalogId, createDumpId(),
8026 tbinfo->dobj.namespace->dobj.name,
8029 false, "SEQUENCE SET", query->data, "", NULL,
8030 &(tbinfo->dobj.dumpId), 1,
8036 /* Dump Sequence Comments */
8037 resetPQExpBuffer(query);
8038 appendPQExpBuffer(query, "SEQUENCE %s", fmtId(tbinfo->dobj.name));
8039 dumpComment(fout, query->data,
8040 tbinfo->dobj.namespace->dobj.name, tbinfo->rolname,
8041 tbinfo->dobj.catId, 0, tbinfo->dobj.dumpId);
8046 destroyPQExpBuffer(query);
8047 destroyPQExpBuffer(delqry);
8051 dumpTrigger(Archive *fout, TriggerInfo *tginfo)
8053 TableInfo *tbinfo = tginfo->tgtable;
8062 query = createPQExpBuffer();
8063 delqry = createPQExpBuffer();
8066 * DROP must be fully qualified in case same name appears in pg_catalog
8068 appendPQExpBuffer(delqry, "DROP TRIGGER %s ",
8069 fmtId(tginfo->dobj.name));
8070 appendPQExpBuffer(delqry, "ON %s.",
8071 fmtId(tbinfo->dobj.namespace->dobj.name));
8072 appendPQExpBuffer(delqry, "%s;\n",
8073 fmtId(tbinfo->dobj.name));
8075 if (tginfo->tgisconstraint)
8077 appendPQExpBuffer(query, "CREATE CONSTRAINT TRIGGER ");
8078 appendPQExpBufferStr(query, fmtId(tginfo->tgconstrname));
8082 appendPQExpBuffer(query, "CREATE TRIGGER ");
8083 appendPQExpBufferStr(query, fmtId(tginfo->dobj.name));
8085 appendPQExpBuffer(query, "\n ");
8089 if (TRIGGER_FOR_BEFORE(tginfo->tgtype))
8090 appendPQExpBuffer(query, "BEFORE");
8092 appendPQExpBuffer(query, "AFTER");
8093 if (TRIGGER_FOR_INSERT(tginfo->tgtype))
8095 appendPQExpBuffer(query, " INSERT");
8098 if (TRIGGER_FOR_DELETE(tginfo->tgtype))
8101 appendPQExpBuffer(query, " OR DELETE");
8103 appendPQExpBuffer(query, " DELETE");
8106 if (TRIGGER_FOR_UPDATE(tginfo->tgtype))
8109 appendPQExpBuffer(query, " OR UPDATE");
8111 appendPQExpBuffer(query, " UPDATE");
8113 appendPQExpBuffer(query, " ON %s\n",
8114 fmtId(tbinfo->dobj.name));
8116 if (tginfo->tgisconstraint)
8118 if (OidIsValid(tginfo->tgconstrrelid))
8120 /* If we are using regclass, name is already quoted */
8121 if (g_fout->remoteVersion >= 70300)
8122 appendPQExpBuffer(query, " FROM %s\n ",
8123 tginfo->tgconstrrelname);
8125 appendPQExpBuffer(query, " FROM %s\n ",
8126 fmtId(tginfo->tgconstrrelname));
8128 if (!tginfo->tgdeferrable)
8129 appendPQExpBuffer(query, "NOT ");
8130 appendPQExpBuffer(query, "DEFERRABLE INITIALLY ");
8131 if (tginfo->tginitdeferred)
8132 appendPQExpBuffer(query, "DEFERRED\n");
8134 appendPQExpBuffer(query, "IMMEDIATE\n");
8137 if (TRIGGER_FOR_ROW(tginfo->tgtype))
8138 appendPQExpBuffer(query, " FOR EACH ROW\n ");
8140 appendPQExpBuffer(query, " FOR EACH STATEMENT\n ");
8142 /* In 7.3, result of regproc is already quoted */
8143 if (g_fout->remoteVersion >= 70300)
8144 appendPQExpBuffer(query, "EXECUTE PROCEDURE %s(",
8147 appendPQExpBuffer(query, "EXECUTE PROCEDURE %s(",
8148 fmtId(tginfo->tgfname));
8151 for (findx = 0; findx < tginfo->tgnargs; findx++)
8155 /* Set 'p' to end of arg string. marked by '\000' */
8158 p = strchr(p, '\\');
8161 write_msg(NULL, "invalid argument string (%s) for trigger \"%s\" on table \"%s\"\n",
8168 if (*p == '\\') /* is it '\\'? */
8173 if (p[0] == '0' && p[1] == '0' && p[2] == '0') /* is it '\000'? */
8178 appendPQExpBufferChar(query, '\'');
8182 appendPQExpBufferChar(query, '\'');
8184 * bytea unconditionally doubles backslashes, so we suppress
8185 * the doubling for standard_conforming_strings.
8187 if (fout->std_strings && *s == '\\' && s[1] == '\\')
8189 appendPQExpBufferChar(query, *s++);
8191 appendPQExpBufferChar(query, '\'');
8192 appendPQExpBuffer(query,
8193 (findx < tginfo->tgnargs - 1) ? ", " : "");
8196 appendPQExpBuffer(query, ");\n");
8198 if (!tginfo->tgenabled)
8200 appendPQExpBuffer(query, "\nALTER TABLE %s ",
8201 fmtId(tbinfo->dobj.name));
8202 appendPQExpBuffer(query, "DISABLE TRIGGER %s;\n",
8203 fmtId(tginfo->dobj.name));
8206 ArchiveEntry(fout, tginfo->dobj.catId, tginfo->dobj.dumpId,
8208 tbinfo->dobj.namespace->dobj.name,
8210 tbinfo->rolname, false,
8211 "TRIGGER", query->data, delqry->data, NULL,
8212 tginfo->dobj.dependencies, tginfo->dobj.nDeps,
8215 resetPQExpBuffer(query);
8216 appendPQExpBuffer(query, "TRIGGER %s ",
8217 fmtId(tginfo->dobj.name));
8218 appendPQExpBuffer(query, "ON %s",
8219 fmtId(tbinfo->dobj.name));
8221 dumpComment(fout, query->data,
8222 tbinfo->dobj.namespace->dobj.name, tbinfo->rolname,
8223 tginfo->dobj.catId, 0, tginfo->dobj.dumpId);
8225 destroyPQExpBuffer(query);
8226 destroyPQExpBuffer(delqry);
8234 dumpRule(Archive *fout, RuleInfo *rinfo)
8236 TableInfo *tbinfo = rinfo->ruletable;
8242 /* Skip if not to be dumped */
8243 if (!rinfo->dobj.dump || dataOnly)
8247 * If it is an ON SELECT rule that is created implicitly by CREATE VIEW,
8248 * we do not want to dump it as a separate object.
8250 if (!rinfo->separate)
8254 * Make sure we are in proper schema.
8256 selectSourceSchema(tbinfo->dobj.namespace->dobj.name);
8258 query = createPQExpBuffer();
8259 cmd = createPQExpBuffer();
8260 delcmd = createPQExpBuffer();
8262 if (g_fout->remoteVersion >= 70300)
8264 appendPQExpBuffer(query,
8265 "SELECT pg_catalog.pg_get_ruledef('%u'::pg_catalog.oid) AS definition",
8266 rinfo->dobj.catId.oid);
8270 /* Rule name was unique before 7.3 ... */
8271 appendPQExpBuffer(query,
8272 "SELECT pg_get_ruledef('%s') AS definition",
8276 res = PQexec(g_conn, query->data);
8277 check_sql_result(res, g_conn, query->data, PGRES_TUPLES_OK);
8279 if (PQntuples(res) != 1)
8281 write_msg(NULL, "query to get rule \"%s\" for table \"%s\" failed: wrong number of rows returned",
8282 rinfo->dobj.name, tbinfo->dobj.name);
8286 printfPQExpBuffer(cmd, "%s\n", PQgetvalue(res, 0, 0));
8289 * DROP must be fully qualified in case same name appears in pg_catalog
8291 appendPQExpBuffer(delcmd, "DROP RULE %s ",
8292 fmtId(rinfo->dobj.name));
8293 appendPQExpBuffer(delcmd, "ON %s.",
8294 fmtId(tbinfo->dobj.namespace->dobj.name));
8295 appendPQExpBuffer(delcmd, "%s;\n",
8296 fmtId(tbinfo->dobj.name));
8298 ArchiveEntry(fout, rinfo->dobj.catId, rinfo->dobj.dumpId,
8300 tbinfo->dobj.namespace->dobj.name,
8302 tbinfo->rolname, false,
8303 "RULE", cmd->data, delcmd->data, NULL,
8304 rinfo->dobj.dependencies, rinfo->dobj.nDeps,
8307 /* Dump rule comments */
8308 resetPQExpBuffer(query);
8309 appendPQExpBuffer(query, "RULE %s",
8310 fmtId(rinfo->dobj.name));
8311 appendPQExpBuffer(query, " ON %s",
8312 fmtId(tbinfo->dobj.name));
8313 dumpComment(fout, query->data,
8314 tbinfo->dobj.namespace->dobj.name,
8316 rinfo->dobj.catId, 0, rinfo->dobj.dumpId);
8320 destroyPQExpBuffer(query);
8321 destroyPQExpBuffer(cmd);
8322 destroyPQExpBuffer(delcmd);
8326 * getDependencies --- obtain available dependency data
8329 getDependencies(void)
8340 DumpableObject *dobj,
8343 /* No dependency info available before 7.3 */
8344 if (g_fout->remoteVersion < 70300)
8348 write_msg(NULL, "reading dependency data\n");
8350 /* Make sure we are in proper schema */
8351 selectSourceSchema("pg_catalog");
8353 query = createPQExpBuffer();
8355 appendPQExpBuffer(query, "SELECT "
8356 "classid, objid, refclassid, refobjid, deptype "
8358 "WHERE deptype != 'p' "
8361 res = PQexec(g_conn, query->data);
8362 check_sql_result(res, g_conn, query->data, PGRES_TUPLES_OK);
8364 ntups = PQntuples(res);
8366 i_classid = PQfnumber(res, "classid");
8367 i_objid = PQfnumber(res, "objid");
8368 i_refclassid = PQfnumber(res, "refclassid");
8369 i_refobjid = PQfnumber(res, "refobjid");
8370 i_deptype = PQfnumber(res, "deptype");
8373 * Since we ordered the SELECT by referencing ID, we can expect that
8374 * multiple entries for the same object will appear together; this saves
8379 for (i = 0; i < ntups; i++)
8385 objId.tableoid = atooid(PQgetvalue(res, i, i_classid));
8386 objId.oid = atooid(PQgetvalue(res, i, i_objid));
8387 refobjId.tableoid = atooid(PQgetvalue(res, i, i_refclassid));
8388 refobjId.oid = atooid(PQgetvalue(res, i, i_refobjid));
8389 deptype = *(PQgetvalue(res, i, i_deptype));
8392 dobj->catId.tableoid != objId.tableoid ||
8393 dobj->catId.oid != objId.oid)
8394 dobj = findObjectByCatalogId(objId);
8397 * Failure to find objects mentioned in pg_depend is not unexpected,
8398 * since for example we don't collect info about TOAST tables.
8403 fprintf(stderr, "no referencing object %u %u\n",
8404 objId.tableoid, objId.oid);
8409 refdobj = findObjectByCatalogId(refobjId);
8411 if (refdobj == NULL)
8414 fprintf(stderr, "no referenced object %u %u\n",
8415 refobjId.tableoid, refobjId.oid);
8421 * Ordinarily, table rowtypes have implicit dependencies on their
8422 * tables. However, for a composite type the implicit dependency goes
8423 * the other way in pg_depend; which is the right thing for DROP but
8424 * it doesn't produce the dependency ordering we need. So in that one
8425 * case, we reverse the direction of the dependency.
8427 if (deptype == 'i' &&
8428 dobj->objType == DO_TABLE &&
8429 refdobj->objType == DO_TYPE)
8430 addObjectDependency(refdobj, dobj->dumpId);
8433 addObjectDependency(dobj, refdobj->dumpId);
8438 destroyPQExpBuffer(query);
8443 * selectSourceSchema - make the specified schema the active search path
8444 * in the source database.
8446 * NB: pg_catalog is explicitly searched after the specified schema;
8447 * so user names are only qualified if they are cross-schema references,
8448 * and system names are only qualified if they conflict with a user name
8449 * in the current schema.
8451 * Whenever the selected schema is not pg_catalog, be careful to qualify
8452 * references to system catalogs and types in our emitted commands!
8455 selectSourceSchema(const char *schemaName)
8457 static char *curSchemaName = NULL;
8460 /* Not relevant if fetching from pre-7.3 DB */
8461 if (g_fout->remoteVersion < 70300)
8463 /* Ignore null schema names */
8464 if (schemaName == NULL || *schemaName == '\0')
8466 /* Optimize away repeated selection of same schema */
8467 if (curSchemaName && strcmp(curSchemaName, schemaName) == 0)
8470 query = createPQExpBuffer();
8471 appendPQExpBuffer(query, "SET search_path = %s",
8473 if (strcmp(schemaName, "pg_catalog") != 0)
8474 appendPQExpBuffer(query, ", pg_catalog");
8476 do_sql_command(g_conn, query->data);
8478 destroyPQExpBuffer(query);
8480 free(curSchemaName);
8481 curSchemaName = strdup(schemaName);
8485 * getFormattedTypeName - retrieve a nicely-formatted type name for the
8488 * NB: in 7.3 and up the result may depend on the currently-selected
8489 * schema; this is why we don't try to cache the names.
8492 getFormattedTypeName(Oid oid, OidOptions opts)
8501 if ((opts & zeroAsOpaque) != 0)
8502 return strdup(g_opaque_type);
8503 else if ((opts & zeroAsAny) != 0)
8504 return strdup("'any'");
8505 else if ((opts & zeroAsStar) != 0)
8507 else if ((opts & zeroAsNone) != 0)
8508 return strdup("NONE");
8511 query = createPQExpBuffer();
8512 if (g_fout->remoteVersion >= 70300)
8514 appendPQExpBuffer(query, "SELECT pg_catalog.format_type('%u'::pg_catalog.oid, NULL)",
8517 else if (g_fout->remoteVersion >= 70100)
8519 appendPQExpBuffer(query, "SELECT format_type('%u'::oid, NULL)",
8524 appendPQExpBuffer(query, "SELECT typname "
8526 "WHERE oid = '%u'::oid",
8530 res = PQexec(g_conn, query->data);
8531 check_sql_result(res, g_conn, query->data, PGRES_TUPLES_OK);
8533 /* Expecting a single result only */
8534 ntups = PQntuples(res);
8537 write_msg(NULL, "query yielded %d rows instead of one: %s\n",
8538 ntups, query->data);
8542 if (g_fout->remoteVersion >= 70100)
8544 /* already quoted */
8545 result = strdup(PQgetvalue(res, 0, 0));
8549 /* may need to quote it */
8550 result = strdup(fmtId(PQgetvalue(res, 0, 0)));
8554 destroyPQExpBuffer(query);
8560 * myFormatType --- local implementation of format_type for use with 7.0.
8563 myFormatType(const char *typname, int32 typmod)
8566 bool isarray = false;
8567 PQExpBuffer buf = createPQExpBuffer();
8569 /* Handle array types */
8570 if (typname[0] == '_')
8576 /* Show lengths on bpchar and varchar */
8577 if (!strcmp(typname, "bpchar"))
8579 int len = (typmod - VARHDRSZ);
8581 appendPQExpBuffer(buf, "character");
8583 appendPQExpBuffer(buf, "(%d)",
8586 else if (!strcmp(typname, "varchar"))
8588 appendPQExpBuffer(buf, "character varying");
8590 appendPQExpBuffer(buf, "(%d)",
8593 else if (!strcmp(typname, "numeric"))
8595 appendPQExpBuffer(buf, "numeric");
8602 tmp_typmod = typmod - VARHDRSZ;
8603 precision = (tmp_typmod >> 16) & 0xffff;
8604 scale = tmp_typmod & 0xffff;
8605 appendPQExpBuffer(buf, "(%d,%d)",
8611 * char is an internal single-byte data type; Let's make sure we force it
8612 * through with quotes. - thomas 1998-12-13
8614 else if (strcmp(typname, "char") == 0)
8615 appendPQExpBuffer(buf, "\"char\"");
8617 appendPQExpBuffer(buf, "%s", fmtId(typname));
8619 /* Append array qualifier for array types */
8621 appendPQExpBuffer(buf, "[]");
8623 result = strdup(buf->data);
8624 destroyPQExpBuffer(buf);
8630 * fmtQualifiedId - convert a qualified name to the proper format for
8631 * the source database.
8633 * Like fmtId, use the result before calling again.
8636 fmtQualifiedId(const char *schema, const char *id)
8638 static PQExpBuffer id_return = NULL;
8640 if (id_return) /* first time through? */
8641 resetPQExpBuffer(id_return);
8643 id_return = createPQExpBuffer();
8645 /* Suppress schema name if fetching from pre-7.3 DB */
8646 if (g_fout->remoteVersion >= 70300 && schema && *schema)
8648 appendPQExpBuffer(id_return, "%s.",
8651 appendPQExpBuffer(id_return, "%s",
8654 return id_return->data;
8658 * Return a column list clause for the given relation.
8660 * Special case: if there are no undropped columns in the relation, return
8661 * "", not an invalid "()" column list.
8664 fmtCopyColumnList(const TableInfo *ti)
8666 static PQExpBuffer q = NULL;
8667 int numatts = ti->numatts;
8668 char **attnames = ti->attnames;
8669 bool *attisdropped = ti->attisdropped;
8673 if (q) /* first time through? */
8674 resetPQExpBuffer(q);
8676 q = createPQExpBuffer();
8678 appendPQExpBuffer(q, "(");
8680 for (i = 0; i < numatts; i++)
8682 if (attisdropped[i])
8685 appendPQExpBuffer(q, ", ");
8686 appendPQExpBuffer(q, "%s", fmtId(attnames[i]));
8691 return ""; /* no undropped columns */
8693 appendPQExpBuffer(q, ")");
8698 * Convenience subroutine to execute a SQL command and check for
8699 * COMMAND_OK status.
8702 do_sql_command(PGconn *conn, const char *query)
8706 res = PQexec(conn, query);
8707 check_sql_result(res, conn, query, PGRES_COMMAND_OK);
8712 * Convenience subroutine to verify a SQL command succeeded,
8713 * and exit with a useful error message if not.
8716 check_sql_result(PGresult *res, PGconn *conn, const char *query,
8717 ExecStatusType expected)
8721 if (res && PQresultStatus(res) == expected)
8724 write_msg(NULL, "SQL command failed\n");
8726 err = PQresultErrorMessage(res);
8728 err = PQerrorMessage(conn);
8729 write_msg(NULL, "Error message from server: %s", err);
8730 write_msg(NULL, "The command was: %s\n", query);