2 * psql - the PostgreSQL interactive terminal
4 * Support for the various \d ("describe") commands. Note that the current
5 * expectation is that all functions in this file will succeed when working
6 * with servers of versions 7.4 and up. It's okay to omit irrelevant
7 * information for an old server, but not to fail outright.
9 * Copyright (c) 2000-2017, PostgreSQL Global Development Group
11 * src/bin/psql/describe.c
13 #include "postgres_fe.h"
17 #include "catalog/pg_attribute.h"
18 #include "catalog/pg_class.h"
19 #include "catalog/pg_default_acl.h"
20 #include "fe_utils/string_utils.h"
24 #include "fe_utils/mbprint.h"
25 #include "fe_utils/print.h"
27 #include "variables.h"
30 static bool describeOneTableDetails(const char *schemaname,
31 const char *relationname,
34 static void add_tablespace_footer(printTableContent *const cont, char relkind,
35 Oid tablespace, const bool newline);
36 static void add_role_attribute(PQExpBuffer buf, const char *const str);
37 static bool listTSParsersVerbose(const char *pattern);
38 static bool describeOneTSParser(const char *oid, const char *nspname,
40 static bool listTSConfigsVerbose(const char *pattern);
41 static bool describeOneTSConfig(const char *oid, const char *nspname,
43 const char *pnspname, const char *prsname);
44 static void printACLColumn(PQExpBuffer buf, const char *colname);
45 static bool listOneExtensionContents(const char *extname, const char *oid);
49 * Handlers for various slash commands displaying some sort of list
50 * of things in the database.
52 * Note: try to format the queries to look nice in -E output.
58 * Takes an optional regexp to select particular aggregates
61 describeAggregates(const char *pattern, bool verbose, bool showSystem)
65 printQueryOpt myopt = pset.popt;
67 initPQExpBuffer(&buf);
69 printfPQExpBuffer(&buf,
70 "SELECT n.nspname as \"%s\",\n"
71 " p.proname AS \"%s\",\n"
72 " pg_catalog.format_type(p.prorettype, NULL) AS \"%s\",\n",
73 gettext_noop("Schema"),
75 gettext_noop("Result data type"));
77 if (pset.sversion >= 80400)
78 appendPQExpBuffer(&buf,
79 " CASE WHEN p.pronargs = 0\n"
80 " THEN CAST('*' AS pg_catalog.text)\n"
81 " ELSE pg_catalog.pg_get_function_arguments(p.oid)\n"
83 gettext_noop("Argument data types"));
84 else if (pset.sversion >= 80200)
85 appendPQExpBuffer(&buf,
86 " CASE WHEN p.pronargs = 0\n"
87 " THEN CAST('*' AS pg_catalog.text)\n"
89 " pg_catalog.array_to_string(ARRAY(\n"
91 " pg_catalog.format_type(p.proargtypes[s.i], NULL)\n"
93 " pg_catalog.generate_series(0, pg_catalog.array_upper(p.proargtypes, 1)) AS s(i)\n"
96 gettext_noop("Argument data types"));
98 appendPQExpBuffer(&buf,
99 " pg_catalog.format_type(p.proargtypes[0], NULL) AS \"%s\",\n",
100 gettext_noop("Argument data types"));
102 appendPQExpBuffer(&buf,
103 " pg_catalog.obj_description(p.oid, 'pg_proc') as \"%s\"\n"
104 "FROM pg_catalog.pg_proc p\n"
105 " LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace\n"
106 "WHERE p.proisagg\n",
107 gettext_noop("Description"));
109 if (!showSystem && !pattern)
110 appendPQExpBufferStr(&buf, " AND n.nspname <> 'pg_catalog'\n"
111 " AND n.nspname <> 'information_schema'\n");
113 processSQLNamePattern(pset.db, &buf, pattern, true, false,
114 "n.nspname", "p.proname", NULL,
115 "pg_catalog.pg_function_is_visible(p.oid)");
117 appendPQExpBufferStr(&buf, "ORDER BY 1, 2, 4;");
119 res = PSQLexec(buf.data);
120 termPQExpBuffer(&buf);
124 myopt.nullPrint = NULL;
125 myopt.title = _("List of aggregate functions");
126 myopt.translate_header = true;
128 printQuery(res, &myopt, pset.queryFout, false, pset.logfile);
135 * Takes an optional regexp to select particular access methods
138 describeAccessMethods(const char *pattern, bool verbose)
142 printQueryOpt myopt = pset.popt;
143 static const bool translate_columns[] = {false, true, false, false};
145 if (pset.sversion < 90600)
149 psql_error("The server (version %s) does not support access methods.\n",
150 formatPGVersionNumber(pset.sversion, false,
151 sverbuf, sizeof(sverbuf)));
155 initPQExpBuffer(&buf);
157 printfPQExpBuffer(&buf,
158 "SELECT amname AS \"%s\",\n"
160 " WHEN 'i' THEN '%s'"
162 gettext_noop("Name"),
163 gettext_noop("Index"),
164 gettext_noop("Type"));
168 appendPQExpBuffer(&buf,
169 ",\n amhandler AS \"%s\",\n"
170 " pg_catalog.obj_description(oid, 'pg_am') AS \"%s\"",
171 gettext_noop("Handler"),
172 gettext_noop("Description"));
175 appendPQExpBufferStr(&buf,
176 "\nFROM pg_catalog.pg_am\n");
178 processSQLNamePattern(pset.db, &buf, pattern, false, false,
179 NULL, "amname", NULL,
182 appendPQExpBufferStr(&buf, "ORDER BY 1;");
184 res = PSQLexec(buf.data);
185 termPQExpBuffer(&buf);
189 myopt.nullPrint = NULL;
190 myopt.title = _("List of access methods");
191 myopt.translate_header = true;
192 myopt.translate_columns = translate_columns;
193 myopt.n_translate_columns = lengthof(translate_columns);
195 printQuery(res, &myopt, pset.queryFout, false, pset.logfile);
202 * Takes an optional regexp to select particular tablespaces
205 describeTablespaces(const char *pattern, bool verbose)
209 printQueryOpt myopt = pset.popt;
211 if (pset.sversion < 80000)
215 psql_error("The server (version %s) does not support tablespaces.\n",
216 formatPGVersionNumber(pset.sversion, false,
217 sverbuf, sizeof(sverbuf)));
221 initPQExpBuffer(&buf);
223 if (pset.sversion >= 90200)
224 printfPQExpBuffer(&buf,
225 "SELECT spcname AS \"%s\",\n"
226 " pg_catalog.pg_get_userbyid(spcowner) AS \"%s\",\n"
227 " pg_catalog.pg_tablespace_location(oid) AS \"%s\"",
228 gettext_noop("Name"),
229 gettext_noop("Owner"),
230 gettext_noop("Location"));
232 printfPQExpBuffer(&buf,
233 "SELECT spcname AS \"%s\",\n"
234 " pg_catalog.pg_get_userbyid(spcowner) AS \"%s\",\n"
235 " spclocation AS \"%s\"",
236 gettext_noop("Name"),
237 gettext_noop("Owner"),
238 gettext_noop("Location"));
242 appendPQExpBufferStr(&buf, ",\n ");
243 printACLColumn(&buf, "spcacl");
246 if (verbose && pset.sversion >= 90000)
247 appendPQExpBuffer(&buf,
248 ",\n spcoptions AS \"%s\"",
249 gettext_noop("Options"));
251 if (verbose && pset.sversion >= 90200)
252 appendPQExpBuffer(&buf,
253 ",\n pg_catalog.pg_size_pretty(pg_catalog.pg_tablespace_size(oid)) AS \"%s\"",
254 gettext_noop("Size"));
256 if (verbose && pset.sversion >= 80200)
257 appendPQExpBuffer(&buf,
258 ",\n pg_catalog.shobj_description(oid, 'pg_tablespace') AS \"%s\"",
259 gettext_noop("Description"));
261 appendPQExpBufferStr(&buf,
262 "\nFROM pg_catalog.pg_tablespace\n");
264 processSQLNamePattern(pset.db, &buf, pattern, false, false,
265 NULL, "spcname", NULL,
268 appendPQExpBufferStr(&buf, "ORDER BY 1;");
270 res = PSQLexec(buf.data);
271 termPQExpBuffer(&buf);
275 myopt.nullPrint = NULL;
276 myopt.title = _("List of tablespaces");
277 myopt.translate_header = true;
279 printQuery(res, &myopt, pset.queryFout, false, pset.logfile);
287 * Takes an optional regexp to select particular functions.
289 * As with \d, you can specify the kinds of functions you want:
296 * and you can mix and match these in any order.
299 describeFunctions(const char *functypes, const char *pattern, bool verbose, bool showSystem)
301 bool showAggregate = strchr(functypes, 'a') != NULL;
302 bool showNormal = strchr(functypes, 'n') != NULL;
303 bool showTrigger = strchr(functypes, 't') != NULL;
304 bool showWindow = strchr(functypes, 'w') != NULL;
308 printQueryOpt myopt = pset.popt;
309 static const bool translate_columns[] = {false, false, false, false, true, true, true, false, true, false, false, false, false};
311 /* No "Parallel" column before 9.6 */
312 static const bool translate_columns_pre_96[] = {false, false, false, false, true, true, false, true, false, false, false, false};
314 if (strlen(functypes) != strspn(functypes, "antwS+"))
316 psql_error("\\df only takes [antwS+] as options\n");
320 if (showWindow && pset.sversion < 80400)
324 psql_error("\\df does not take a \"w\" option with server version %s\n",
325 formatPGVersionNumber(pset.sversion, false,
326 sverbuf, sizeof(sverbuf)));
330 if (!showAggregate && !showNormal && !showTrigger && !showWindow)
332 showAggregate = showNormal = showTrigger = true;
333 if (pset.sversion >= 80400)
337 initPQExpBuffer(&buf);
339 printfPQExpBuffer(&buf,
340 "SELECT n.nspname as \"%s\",\n"
341 " p.proname as \"%s\",\n",
342 gettext_noop("Schema"),
343 gettext_noop("Name"));
345 if (pset.sversion >= 80400)
346 appendPQExpBuffer(&buf,
347 " pg_catalog.pg_get_function_result(p.oid) as \"%s\",\n"
348 " pg_catalog.pg_get_function_arguments(p.oid) as \"%s\",\n"
350 " WHEN p.proisagg THEN '%s'\n"
351 " WHEN p.proiswindow THEN '%s'\n"
352 " WHEN p.prorettype = 'pg_catalog.trigger'::pg_catalog.regtype THEN '%s'\n"
355 gettext_noop("Result data type"),
356 gettext_noop("Argument data types"),
357 /* translator: "agg" is short for "aggregate" */
359 gettext_noop("window"),
360 gettext_noop("trigger"),
361 gettext_noop("normal"),
362 gettext_noop("Type"));
363 else if (pset.sversion >= 80100)
364 appendPQExpBuffer(&buf,
365 " CASE WHEN p.proretset THEN 'SETOF ' ELSE '' END ||\n"
366 " pg_catalog.format_type(p.prorettype, NULL) as \"%s\",\n"
367 " CASE WHEN proallargtypes IS NOT NULL THEN\n"
368 " pg_catalog.array_to_string(ARRAY(\n"
371 " WHEN p.proargmodes[s.i] = 'i' THEN ''\n"
372 " WHEN p.proargmodes[s.i] = 'o' THEN 'OUT '\n"
373 " WHEN p.proargmodes[s.i] = 'b' THEN 'INOUT '\n"
374 " WHEN p.proargmodes[s.i] = 'v' THEN 'VARIADIC '\n"
377 " WHEN COALESCE(p.proargnames[s.i], '') = '' THEN ''\n"
378 " ELSE p.proargnames[s.i] || ' '\n"
380 " pg_catalog.format_type(p.proallargtypes[s.i], NULL)\n"
382 " pg_catalog.generate_series(1, pg_catalog.array_upper(p.proallargtypes, 1)) AS s(i)\n"
385 " pg_catalog.array_to_string(ARRAY(\n"
388 " WHEN COALESCE(p.proargnames[s.i+1], '') = '' THEN ''\n"
389 " ELSE p.proargnames[s.i+1] || ' '\n"
391 " pg_catalog.format_type(p.proargtypes[s.i], NULL)\n"
393 " pg_catalog.generate_series(0, pg_catalog.array_upper(p.proargtypes, 1)) AS s(i)\n"
397 " WHEN p.proisagg THEN '%s'\n"
398 " WHEN p.prorettype = 'pg_catalog.trigger'::pg_catalog.regtype THEN '%s'\n"
401 gettext_noop("Result data type"),
402 gettext_noop("Argument data types"),
403 /* translator: "agg" is short for "aggregate" */
405 gettext_noop("trigger"),
406 gettext_noop("normal"),
407 gettext_noop("Type"));
409 appendPQExpBuffer(&buf,
410 " CASE WHEN p.proretset THEN 'SETOF ' ELSE '' END ||\n"
411 " pg_catalog.format_type(p.prorettype, NULL) as \"%s\",\n"
412 " pg_catalog.oidvectortypes(p.proargtypes) as \"%s\",\n"
414 " WHEN p.proisagg THEN '%s'\n"
415 " WHEN p.prorettype = 'pg_catalog.trigger'::pg_catalog.regtype THEN '%s'\n"
418 gettext_noop("Result data type"),
419 gettext_noop("Argument data types"),
420 /* translator: "agg" is short for "aggregate" */
422 gettext_noop("trigger"),
423 gettext_noop("normal"),
424 gettext_noop("Type"));
428 appendPQExpBuffer(&buf,
430 " WHEN p.provolatile = 'i' THEN '%s'\n"
431 " WHEN p.provolatile = 's' THEN '%s'\n"
432 " WHEN p.provolatile = 'v' THEN '%s'\n"
434 gettext_noop("immutable"),
435 gettext_noop("stable"),
436 gettext_noop("volatile"),
437 gettext_noop("Volatility"));
438 if (pset.sversion >= 90600)
439 appendPQExpBuffer(&buf,
441 " WHEN p.proparallel = 'r' THEN '%s'\n"
442 " WHEN p.proparallel = 's' THEN '%s'\n"
443 " WHEN p.proparallel = 'u' THEN '%s'\n"
445 gettext_noop("restricted"),
446 gettext_noop("safe"),
447 gettext_noop("unsafe"),
448 gettext_noop("Parallel"));
449 appendPQExpBuffer(&buf,
450 ",\n pg_catalog.pg_get_userbyid(p.proowner) as \"%s\""
451 ",\n CASE WHEN prosecdef THEN '%s' ELSE '%s' END AS \"%s\"",
452 gettext_noop("Owner"),
453 gettext_noop("definer"),
454 gettext_noop("invoker"),
455 gettext_noop("Security"));
456 appendPQExpBufferStr(&buf, ",\n ");
457 printACLColumn(&buf, "p.proacl");
458 appendPQExpBuffer(&buf,
459 ",\n l.lanname as \"%s\""
460 ",\n p.prosrc as \"%s\""
461 ",\n pg_catalog.obj_description(p.oid, 'pg_proc') as \"%s\"",
462 gettext_noop("Language"),
463 gettext_noop("Source code"),
464 gettext_noop("Description"));
467 appendPQExpBufferStr(&buf,
468 "\nFROM pg_catalog.pg_proc p"
469 "\n LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace\n");
472 appendPQExpBufferStr(&buf,
473 " LEFT JOIN pg_catalog.pg_language l ON l.oid = p.prolang\n");
477 /* filter by function type, if requested */
478 if (showNormal && showAggregate && showTrigger && showWindow)
485 appendPQExpBufferStr(&buf, " AND ");
488 appendPQExpBufferStr(&buf, "WHERE ");
491 appendPQExpBufferStr(&buf, "NOT p.proisagg\n");
496 appendPQExpBufferStr(&buf, " AND ");
499 appendPQExpBufferStr(&buf, "WHERE ");
502 appendPQExpBufferStr(&buf, "p.prorettype <> 'pg_catalog.trigger'::pg_catalog.regtype\n");
504 if (!showWindow && pset.sversion >= 80400)
507 appendPQExpBufferStr(&buf, " AND ");
510 appendPQExpBufferStr(&buf, "WHERE ");
513 appendPQExpBufferStr(&buf, "NOT p.proiswindow\n");
518 bool needs_or = false;
520 appendPQExpBufferStr(&buf, "WHERE (\n ");
522 /* Note: at least one of these must be true ... */
525 appendPQExpBufferStr(&buf, "p.proisagg\n");
531 appendPQExpBufferStr(&buf, " OR ");
532 appendPQExpBufferStr(&buf,
533 "p.prorettype = 'pg_catalog.trigger'::pg_catalog.regtype\n");
539 appendPQExpBufferStr(&buf, " OR ");
540 appendPQExpBufferStr(&buf, "p.proiswindow\n");
543 appendPQExpBufferStr(&buf, " )\n");
546 processSQLNamePattern(pset.db, &buf, pattern, have_where, false,
547 "n.nspname", "p.proname", NULL,
548 "pg_catalog.pg_function_is_visible(p.oid)");
550 if (!showSystem && !pattern)
551 appendPQExpBufferStr(&buf, " AND n.nspname <> 'pg_catalog'\n"
552 " AND n.nspname <> 'information_schema'\n");
554 appendPQExpBufferStr(&buf, "ORDER BY 1, 2, 4;");
556 res = PSQLexec(buf.data);
557 termPQExpBuffer(&buf);
561 myopt.nullPrint = NULL;
562 myopt.title = _("List of functions");
563 myopt.translate_header = true;
564 if (pset.sversion >= 90600)
566 myopt.translate_columns = translate_columns;
567 myopt.n_translate_columns = lengthof(translate_columns);
571 myopt.translate_columns = translate_columns_pre_96;
572 myopt.n_translate_columns = lengthof(translate_columns_pre_96);
575 printQuery(res, &myopt, pset.queryFout, false, pset.logfile);
588 describeTypes(const char *pattern, bool verbose, bool showSystem)
592 printQueryOpt myopt = pset.popt;
594 initPQExpBuffer(&buf);
596 printfPQExpBuffer(&buf,
597 "SELECT n.nspname as \"%s\",\n"
598 " pg_catalog.format_type(t.oid, NULL) AS \"%s\",\n",
599 gettext_noop("Schema"),
600 gettext_noop("Name"));
602 appendPQExpBuffer(&buf,
603 " t.typname AS \"%s\",\n"
604 " CASE WHEN t.typrelid != 0\n"
605 " THEN CAST('tuple' AS pg_catalog.text)\n"
606 " WHEN t.typlen < 0\n"
607 " THEN CAST('var' AS pg_catalog.text)\n"
608 " ELSE CAST(t.typlen AS pg_catalog.text)\n"
610 gettext_noop("Internal name"),
611 gettext_noop("Size"));
612 if (verbose && pset.sversion >= 80300)
614 appendPQExpBufferStr(&buf,
615 " pg_catalog.array_to_string(\n"
617 " SELECT e.enumlabel\n"
618 " FROM pg_catalog.pg_enum e\n"
619 " WHERE e.enumtypid = t.oid\n");
621 if (pset.sversion >= 90100)
622 appendPQExpBufferStr(&buf,
623 " ORDER BY e.enumsortorder\n");
625 appendPQExpBufferStr(&buf,
626 " ORDER BY e.oid\n");
628 appendPQExpBuffer(&buf,
632 gettext_noop("Elements"));
636 appendPQExpBuffer(&buf,
637 " pg_catalog.pg_get_userbyid(t.typowner) AS \"%s\",\n",
638 gettext_noop("Owner"));
640 if (verbose && pset.sversion >= 90200)
642 printACLColumn(&buf, "t.typacl");
643 appendPQExpBufferStr(&buf, ",\n ");
646 appendPQExpBuffer(&buf,
647 " pg_catalog.obj_description(t.oid, 'pg_type') as \"%s\"\n",
648 gettext_noop("Description"));
650 appendPQExpBufferStr(&buf, "FROM pg_catalog.pg_type t\n"
651 " LEFT JOIN pg_catalog.pg_namespace n ON n.oid = t.typnamespace\n");
654 * do not include complex types (typrelid!=0) unless they are standalone
657 appendPQExpBufferStr(&buf, "WHERE (t.typrelid = 0 ");
658 appendPQExpBufferStr(&buf, "OR (SELECT c.relkind = " CppAsString2(RELKIND_COMPOSITE_TYPE)
659 " FROM pg_catalog.pg_class c "
660 "WHERE c.oid = t.typrelid))\n");
663 * do not include array types (before 8.3 we have to use the assumption
664 * that their names start with underscore)
666 if (pset.sversion >= 80300)
667 appendPQExpBufferStr(&buf, " AND NOT EXISTS(SELECT 1 FROM pg_catalog.pg_type el WHERE el.oid = t.typelem AND el.typarray = t.oid)\n");
669 appendPQExpBufferStr(&buf, " AND t.typname !~ '^_'\n");
671 if (!showSystem && !pattern)
672 appendPQExpBufferStr(&buf, " AND n.nspname <> 'pg_catalog'\n"
673 " AND n.nspname <> 'information_schema'\n");
675 /* Match name pattern against either internal or external name */
676 processSQLNamePattern(pset.db, &buf, pattern, true, false,
677 "n.nspname", "t.typname",
678 "pg_catalog.format_type(t.oid, NULL)",
679 "pg_catalog.pg_type_is_visible(t.oid)");
681 appendPQExpBufferStr(&buf, "ORDER BY 1, 2;");
683 res = PSQLexec(buf.data);
684 termPQExpBuffer(&buf);
688 myopt.nullPrint = NULL;
689 myopt.title = _("List of data types");
690 myopt.translate_header = true;
692 printQuery(res, &myopt, pset.queryFout, false, pset.logfile);
703 describeOperators(const char *pattern, bool verbose, bool showSystem)
707 printQueryOpt myopt = pset.popt;
709 initPQExpBuffer(&buf);
712 * Note: before Postgres 9.1, we did not assign comments to any built-in
713 * operators, preferring to let the comment on the underlying function
714 * suffice. The coalesce() on the obj_description() calls below supports
715 * this convention by providing a fallback lookup of a comment on the
716 * operator's function. As of 9.1 there is a policy that every built-in
717 * operator should have a comment; so the coalesce() is no longer
718 * necessary so far as built-in operators are concerned. We keep it
719 * anyway, for now, because (1) third-party modules may still be following
720 * the old convention, and (2) we'd need to do it anyway when talking to a
724 printfPQExpBuffer(&buf,
725 "SELECT n.nspname as \"%s\",\n"
726 " o.oprname AS \"%s\",\n"
727 " CASE WHEN o.oprkind='l' THEN NULL ELSE pg_catalog.format_type(o.oprleft, NULL) END AS \"%s\",\n"
728 " CASE WHEN o.oprkind='r' THEN NULL ELSE pg_catalog.format_type(o.oprright, NULL) END AS \"%s\",\n"
729 " pg_catalog.format_type(o.oprresult, NULL) AS \"%s\",\n",
730 gettext_noop("Schema"),
731 gettext_noop("Name"),
732 gettext_noop("Left arg type"),
733 gettext_noop("Right arg type"),
734 gettext_noop("Result type"));
737 appendPQExpBuffer(&buf,
738 " o.oprcode AS \"%s\",\n",
739 gettext_noop("Function"));
741 appendPQExpBuffer(&buf,
742 " coalesce(pg_catalog.obj_description(o.oid, 'pg_operator'),\n"
743 " pg_catalog.obj_description(o.oprcode, 'pg_proc')) AS \"%s\"\n"
744 "FROM pg_catalog.pg_operator o\n"
745 " LEFT JOIN pg_catalog.pg_namespace n ON n.oid = o.oprnamespace\n",
746 gettext_noop("Description"));
748 if (!showSystem && !pattern)
749 appendPQExpBufferStr(&buf, "WHERE n.nspname <> 'pg_catalog'\n"
750 " AND n.nspname <> 'information_schema'\n");
752 processSQLNamePattern(pset.db, &buf, pattern, !showSystem && !pattern, true,
753 "n.nspname", "o.oprname", NULL,
754 "pg_catalog.pg_operator_is_visible(o.oid)");
756 appendPQExpBufferStr(&buf, "ORDER BY 1, 2, 3, 4;");
758 res = PSQLexec(buf.data);
759 termPQExpBuffer(&buf);
763 myopt.nullPrint = NULL;
764 myopt.title = _("List of operators");
765 myopt.translate_header = true;
767 printQuery(res, &myopt, pset.queryFout, false, pset.logfile);
777 * for \l, \list, and -l switch
780 listAllDbs(const char *pattern, bool verbose)
784 printQueryOpt myopt = pset.popt;
786 initPQExpBuffer(&buf);
788 printfPQExpBuffer(&buf,
789 "SELECT d.datname as \"%s\",\n"
790 " pg_catalog.pg_get_userbyid(d.datdba) as \"%s\",\n"
791 " pg_catalog.pg_encoding_to_char(d.encoding) as \"%s\",\n",
792 gettext_noop("Name"),
793 gettext_noop("Owner"),
794 gettext_noop("Encoding"));
795 if (pset.sversion >= 80400)
796 appendPQExpBuffer(&buf,
797 " d.datcollate as \"%s\",\n"
798 " d.datctype as \"%s\",\n",
799 gettext_noop("Collate"),
800 gettext_noop("Ctype"));
801 appendPQExpBufferStr(&buf, " ");
802 printACLColumn(&buf, "d.datacl");
803 if (verbose && pset.sversion >= 80200)
804 appendPQExpBuffer(&buf,
805 ",\n CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT')\n"
806 " THEN pg_catalog.pg_size_pretty(pg_catalog.pg_database_size(d.datname))\n"
807 " ELSE 'No Access'\n"
809 gettext_noop("Size"));
810 if (verbose && pset.sversion >= 80000)
811 appendPQExpBuffer(&buf,
812 ",\n t.spcname as \"%s\"",
813 gettext_noop("Tablespace"));
814 if (verbose && pset.sversion >= 80200)
815 appendPQExpBuffer(&buf,
816 ",\n pg_catalog.shobj_description(d.oid, 'pg_database') as \"%s\"",
817 gettext_noop("Description"));
818 appendPQExpBufferStr(&buf,
819 "\nFROM pg_catalog.pg_database d\n");
820 if (verbose && pset.sversion >= 80000)
821 appendPQExpBufferStr(&buf,
822 " JOIN pg_catalog.pg_tablespace t on d.dattablespace = t.oid\n");
825 processSQLNamePattern(pset.db, &buf, pattern, false, false,
826 NULL, "d.datname", NULL, NULL);
828 appendPQExpBufferStr(&buf, "ORDER BY 1;");
829 res = PSQLexec(buf.data);
830 termPQExpBuffer(&buf);
834 myopt.nullPrint = NULL;
835 myopt.title = _("List of databases");
836 myopt.translate_header = true;
838 printQuery(res, &myopt, pset.queryFout, false, pset.logfile);
846 * List Tables' Grant/Revoke Permissions
847 * \z (now also \dp -- perhaps more mnemonic)
850 permissionsList(const char *pattern)
854 printQueryOpt myopt = pset.popt;
855 static const bool translate_columns[] = {false, false, true, false, false, false};
857 initPQExpBuffer(&buf);
860 * we ignore indexes and toast tables since they have no meaningful rights
862 printfPQExpBuffer(&buf,
863 "SELECT n.nspname as \"%s\",\n"
864 " c.relname as \"%s\",\n"
866 " WHEN " CppAsString2(RELKIND_RELATION) " THEN '%s'"
867 " WHEN " CppAsString2(RELKIND_VIEW) " THEN '%s'"
868 " WHEN " CppAsString2(RELKIND_MATVIEW) " THEN '%s'"
869 " WHEN " CppAsString2(RELKIND_SEQUENCE) " THEN '%s'"
870 " WHEN " CppAsString2(RELKIND_FOREIGN_TABLE) " THEN '%s'"
871 " WHEN " CppAsString2(RELKIND_PARTITIONED_TABLE) " THEN '%s'"
874 gettext_noop("Schema"),
875 gettext_noop("Name"),
876 gettext_noop("table"),
877 gettext_noop("view"),
878 gettext_noop("materialized view"),
879 gettext_noop("sequence"),
880 gettext_noop("foreign table"),
881 gettext_noop("table"), /* partitioned table */
882 gettext_noop("Type"));
884 printACLColumn(&buf, "c.relacl");
886 if (pset.sversion >= 80400)
887 appendPQExpBuffer(&buf,
888 ",\n pg_catalog.array_to_string(ARRAY(\n"
889 " SELECT attname || E':\\n ' || pg_catalog.array_to_string(attacl, E'\\n ')\n"
890 " FROM pg_catalog.pg_attribute a\n"
891 " WHERE attrelid = c.oid AND NOT attisdropped AND attacl IS NOT NULL\n"
892 " ), E'\\n') AS \"%s\"",
893 gettext_noop("Column privileges"));
895 if (pset.sversion >= 90500 && pset.sversion < 100000)
896 appendPQExpBuffer(&buf,
897 ",\n pg_catalog.array_to_string(ARRAY(\n"
899 " || CASE WHEN polcmd != '*' THEN\n"
900 " E' (' || polcmd || E'):'\n"
903 " || CASE WHEN polqual IS NOT NULL THEN\n"
904 " E'\\n (u): ' || pg_catalog.pg_get_expr(polqual, polrelid)\n"
907 " || CASE WHEN polwithcheck IS NOT NULL THEN\n"
908 " E'\\n (c): ' || pg_catalog.pg_get_expr(polwithcheck, polrelid)\n"
911 " || CASE WHEN polroles <> '{0}' THEN\n"
912 " E'\\n to: ' || pg_catalog.array_to_string(\n"
915 " FROM pg_catalog.pg_roles\n"
916 " WHERE oid = ANY (polroles)\n"
921 " FROM pg_catalog.pg_policy pol\n"
922 " WHERE polrelid = c.oid), E'\\n')\n"
924 gettext_noop("Policies"));
926 if (pset.sversion >= 100000)
927 appendPQExpBuffer(&buf,
928 ",\n pg_catalog.array_to_string(ARRAY(\n"
930 " || CASE WHEN NOT polpermissive THEN\n"
931 " E' (RESTRICTIVE)'\n"
933 " || CASE WHEN polcmd != '*' THEN\n"
934 " E' (' || polcmd || E'):'\n"
937 " || CASE WHEN polqual IS NOT NULL THEN\n"
938 " E'\\n (u): ' || pg_catalog.pg_get_expr(polqual, polrelid)\n"
941 " || CASE WHEN polwithcheck IS NOT NULL THEN\n"
942 " E'\\n (c): ' || pg_catalog.pg_get_expr(polwithcheck, polrelid)\n"
945 " || CASE WHEN polroles <> '{0}' THEN\n"
946 " E'\\n to: ' || pg_catalog.array_to_string(\n"
949 " FROM pg_catalog.pg_roles\n"
950 " WHERE oid = ANY (polroles)\n"
955 " FROM pg_catalog.pg_policy pol\n"
956 " WHERE polrelid = c.oid), E'\\n')\n"
958 gettext_noop("Policies"));
960 appendPQExpBufferStr(&buf, "\nFROM pg_catalog.pg_class c\n"
961 " LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace\n"
962 "WHERE c.relkind IN ("
963 CppAsString2(RELKIND_RELATION) ","
964 CppAsString2(RELKIND_VIEW) ","
965 CppAsString2(RELKIND_MATVIEW) ","
966 CppAsString2(RELKIND_SEQUENCE) ","
967 CppAsString2(RELKIND_FOREIGN_TABLE) ","
968 CppAsString2(RELKIND_PARTITIONED_TABLE) ")\n");
971 * Unless a schema pattern is specified, we suppress system and temp
972 * tables, since they normally aren't very interesting from a permissions
973 * point of view. You can see 'em by explicit request though, eg with \z
976 processSQLNamePattern(pset.db, &buf, pattern, true, false,
977 "n.nspname", "c.relname", NULL,
978 "n.nspname !~ '^pg_' AND pg_catalog.pg_table_is_visible(c.oid)");
980 appendPQExpBufferStr(&buf, "ORDER BY 1, 2;");
982 res = PSQLexec(buf.data);
985 termPQExpBuffer(&buf);
989 myopt.nullPrint = NULL;
990 printfPQExpBuffer(&buf, _("Access privileges"));
991 myopt.title = buf.data;
992 myopt.translate_header = true;
993 myopt.translate_columns = translate_columns;
994 myopt.n_translate_columns = lengthof(translate_columns);
996 printQuery(res, &myopt, pset.queryFout, false, pset.logfile);
998 termPQExpBuffer(&buf);
1007 * List Default ACLs. The pattern can match either schema or role name.
1010 listDefaultACLs(const char *pattern)
1012 PQExpBufferData buf;
1014 printQueryOpt myopt = pset.popt;
1015 static const bool translate_columns[] = {false, false, true, false};
1017 if (pset.sversion < 90000)
1021 psql_error("The server (version %s) does not support altering default privileges.\n",
1022 formatPGVersionNumber(pset.sversion, false,
1023 sverbuf, sizeof(sverbuf)));
1027 initPQExpBuffer(&buf);
1029 printfPQExpBuffer(&buf,
1030 "SELECT pg_catalog.pg_get_userbyid(d.defaclrole) AS \"%s\",\n"
1031 " n.nspname AS \"%s\",\n"
1032 " CASE d.defaclobjtype WHEN '%c' THEN '%s' WHEN '%c' THEN '%s' WHEN '%c' THEN '%s' WHEN '%c' THEN '%s' WHEN '%c' THEN '%s' END AS \"%s\",\n"
1034 gettext_noop("Owner"),
1035 gettext_noop("Schema"),
1037 gettext_noop("table"),
1039 gettext_noop("sequence"),
1041 gettext_noop("function"),
1043 gettext_noop("type"),
1044 DEFACLOBJ_NAMESPACE,
1045 gettext_noop("schema"),
1046 gettext_noop("Type"));
1048 printACLColumn(&buf, "d.defaclacl");
1050 appendPQExpBufferStr(&buf, "\nFROM pg_catalog.pg_default_acl d\n"
1051 " LEFT JOIN pg_catalog.pg_namespace n ON n.oid = d.defaclnamespace\n");
1053 processSQLNamePattern(pset.db, &buf, pattern, false, false,
1056 "pg_catalog.pg_get_userbyid(d.defaclrole)",
1059 appendPQExpBufferStr(&buf, "ORDER BY 1, 2, 3;");
1061 res = PSQLexec(buf.data);
1064 termPQExpBuffer(&buf);
1068 myopt.nullPrint = NULL;
1069 printfPQExpBuffer(&buf, _("Default access privileges"));
1070 myopt.title = buf.data;
1071 myopt.translate_header = true;
1072 myopt.translate_columns = translate_columns;
1073 myopt.n_translate_columns = lengthof(translate_columns);
1075 printQuery(res, &myopt, pset.queryFout, false, pset.logfile);
1077 termPQExpBuffer(&buf);
1084 * Get object comments
1088 * Note: This command only lists comments for object types which do not have
1089 * their comments displayed by their own backslash commands. The following
1090 * types of objects will be displayed: constraint, operator class,
1091 * operator family, rule, and trigger.
1095 objectDescription(const char *pattern, bool showSystem)
1097 PQExpBufferData buf;
1099 printQueryOpt myopt = pset.popt;
1100 static const bool translate_columns[] = {false, false, true, false};
1102 initPQExpBuffer(&buf);
1104 appendPQExpBuffer(&buf,
1105 "SELECT DISTINCT tt.nspname AS \"%s\", tt.name AS \"%s\", tt.object AS \"%s\", d.description AS \"%s\"\n"
1107 gettext_noop("Schema"),
1108 gettext_noop("Name"),
1109 gettext_noop("Object"),
1110 gettext_noop("Description"));
1112 /* Table constraint descriptions */
1113 appendPQExpBuffer(&buf,
1114 " SELECT pgc.oid as oid, pgc.tableoid AS tableoid,\n"
1115 " n.nspname as nspname,\n"
1116 " CAST(pgc.conname AS pg_catalog.text) as name,"
1117 " CAST('%s' AS pg_catalog.text) as object\n"
1118 " FROM pg_catalog.pg_constraint pgc\n"
1119 " JOIN pg_catalog.pg_class c "
1120 "ON c.oid = pgc.conrelid\n"
1121 " LEFT JOIN pg_catalog.pg_namespace n "
1122 " ON n.oid = c.relnamespace\n",
1123 gettext_noop("table constraint"));
1125 if (!showSystem && !pattern)
1126 appendPQExpBufferStr(&buf, "WHERE n.nspname <> 'pg_catalog'\n"
1127 " AND n.nspname <> 'information_schema'\n");
1129 processSQLNamePattern(pset.db, &buf, pattern, !showSystem && !pattern,
1130 false, "n.nspname", "pgc.conname", NULL,
1131 "pg_catalog.pg_table_is_visible(c.oid)");
1133 /* Domain constraint descriptions */
1134 appendPQExpBuffer(&buf,
1136 " SELECT pgc.oid as oid, pgc.tableoid AS tableoid,\n"
1137 " n.nspname as nspname,\n"
1138 " CAST(pgc.conname AS pg_catalog.text) as name,"
1139 " CAST('%s' AS pg_catalog.text) as object\n"
1140 " FROM pg_catalog.pg_constraint pgc\n"
1141 " JOIN pg_catalog.pg_type t "
1142 "ON t.oid = pgc.contypid\n"
1143 " LEFT JOIN pg_catalog.pg_namespace n "
1144 " ON n.oid = t.typnamespace\n",
1145 gettext_noop("domain constraint"));
1147 if (!showSystem && !pattern)
1148 appendPQExpBufferStr(&buf, "WHERE n.nspname <> 'pg_catalog'\n"
1149 " AND n.nspname <> 'information_schema'\n");
1151 processSQLNamePattern(pset.db, &buf, pattern, !showSystem && !pattern,
1152 false, "n.nspname", "pgc.conname", NULL,
1153 "pg_catalog.pg_type_is_visible(t.oid)");
1157 * pg_opclass.opcmethod only available in 8.3+
1159 if (pset.sversion >= 80300)
1161 /* Operator class descriptions */
1162 appendPQExpBuffer(&buf,
1164 " SELECT o.oid as oid, o.tableoid as tableoid,\n"
1165 " n.nspname as nspname,\n"
1166 " CAST(o.opcname AS pg_catalog.text) as name,\n"
1167 " CAST('%s' AS pg_catalog.text) as object\n"
1168 " FROM pg_catalog.pg_opclass o\n"
1169 " JOIN pg_catalog.pg_am am ON "
1170 "o.opcmethod = am.oid\n"
1171 " JOIN pg_catalog.pg_namespace n ON "
1172 "n.oid = o.opcnamespace\n",
1173 gettext_noop("operator class"));
1175 if (!showSystem && !pattern)
1176 appendPQExpBufferStr(&buf, " AND n.nspname <> 'pg_catalog'\n"
1177 " AND n.nspname <> 'information_schema'\n");
1179 processSQLNamePattern(pset.db, &buf, pattern, true, false,
1180 "n.nspname", "o.opcname", NULL,
1181 "pg_catalog.pg_opclass_is_visible(o.oid)");
1185 * although operator family comments have been around since 8.3,
1186 * pg_opfamily_is_visible is only available in 9.2+
1188 if (pset.sversion >= 90200)
1190 /* Operator family descriptions */
1191 appendPQExpBuffer(&buf,
1193 " SELECT opf.oid as oid, opf.tableoid as tableoid,\n"
1194 " n.nspname as nspname,\n"
1195 " CAST(opf.opfname AS pg_catalog.text) AS name,\n"
1196 " CAST('%s' AS pg_catalog.text) as object\n"
1197 " FROM pg_catalog.pg_opfamily opf\n"
1198 " JOIN pg_catalog.pg_am am "
1199 "ON opf.opfmethod = am.oid\n"
1200 " JOIN pg_catalog.pg_namespace n "
1201 "ON opf.opfnamespace = n.oid\n",
1202 gettext_noop("operator family"));
1204 if (!showSystem && !pattern)
1205 appendPQExpBufferStr(&buf, " AND n.nspname <> 'pg_catalog'\n"
1206 " AND n.nspname <> 'information_schema'\n");
1208 processSQLNamePattern(pset.db, &buf, pattern, true, false,
1209 "n.nspname", "opf.opfname", NULL,
1210 "pg_catalog.pg_opfamily_is_visible(opf.oid)");
1213 /* Rule descriptions (ignore rules for views) */
1214 appendPQExpBuffer(&buf,
1216 " SELECT r.oid as oid, r.tableoid as tableoid,\n"
1217 " n.nspname as nspname,\n"
1218 " CAST(r.rulename AS pg_catalog.text) as name,"
1219 " CAST('%s' AS pg_catalog.text) as object\n"
1220 " FROM pg_catalog.pg_rewrite r\n"
1221 " JOIN pg_catalog.pg_class c ON c.oid = r.ev_class\n"
1222 " LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace\n"
1223 " WHERE r.rulename != '_RETURN'\n",
1224 gettext_noop("rule"));
1226 if (!showSystem && !pattern)
1227 appendPQExpBufferStr(&buf, " AND n.nspname <> 'pg_catalog'\n"
1228 " AND n.nspname <> 'information_schema'\n");
1230 processSQLNamePattern(pset.db, &buf, pattern, true, false,
1231 "n.nspname", "r.rulename", NULL,
1232 "pg_catalog.pg_table_is_visible(c.oid)");
1234 /* Trigger descriptions */
1235 appendPQExpBuffer(&buf,
1237 " SELECT t.oid as oid, t.tableoid as tableoid,\n"
1238 " n.nspname as nspname,\n"
1239 " CAST(t.tgname AS pg_catalog.text) as name,"
1240 " CAST('%s' AS pg_catalog.text) as object\n"
1241 " FROM pg_catalog.pg_trigger t\n"
1242 " JOIN pg_catalog.pg_class c ON c.oid = t.tgrelid\n"
1243 " LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace\n",
1244 gettext_noop("trigger"));
1246 if (!showSystem && !pattern)
1247 appendPQExpBufferStr(&buf, "WHERE n.nspname <> 'pg_catalog'\n"
1248 " AND n.nspname <> 'information_schema'\n");
1250 processSQLNamePattern(pset.db, &buf, pattern, !showSystem && !pattern, false,
1251 "n.nspname", "t.tgname", NULL,
1252 "pg_catalog.pg_table_is_visible(c.oid)");
1254 appendPQExpBufferStr(&buf,
1256 " JOIN pg_catalog.pg_description d ON (tt.oid = d.objoid AND tt.tableoid = d.classoid AND d.objsubid = 0)\n");
1258 appendPQExpBufferStr(&buf, "ORDER BY 1, 2, 3;");
1260 res = PSQLexec(buf.data);
1261 termPQExpBuffer(&buf);
1265 myopt.nullPrint = NULL;
1266 myopt.title = _("Object descriptions");
1267 myopt.translate_header = true;
1268 myopt.translate_columns = translate_columns;
1269 myopt.n_translate_columns = lengthof(translate_columns);
1271 printQuery(res, &myopt, pset.queryFout, false, pset.logfile);
1279 * describeTableDetails (for \d)
1281 * This routine finds the tables to be displayed, and calls
1282 * describeOneTableDetails for each one.
1284 * verbose: if true, this is \d+
1287 describeTableDetails(const char *pattern, bool verbose, bool showSystem)
1289 PQExpBufferData buf;
1293 initPQExpBuffer(&buf);
1295 printfPQExpBuffer(&buf,
1299 "FROM pg_catalog.pg_class c\n"
1300 " LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace\n");
1302 if (!showSystem && !pattern)
1303 appendPQExpBufferStr(&buf, "WHERE n.nspname <> 'pg_catalog'\n"
1304 " AND n.nspname <> 'information_schema'\n");
1306 processSQLNamePattern(pset.db, &buf, pattern, !showSystem && !pattern, false,
1307 "n.nspname", "c.relname", NULL,
1308 "pg_catalog.pg_table_is_visible(c.oid)");
1310 appendPQExpBufferStr(&buf, "ORDER BY 2, 3;");
1312 res = PSQLexec(buf.data);
1313 termPQExpBuffer(&buf);
1317 if (PQntuples(res) == 0)
1320 psql_error("Did not find any relation named \"%s\".\n",
1326 for (i = 0; i < PQntuples(res); i++)
1329 const char *nspname;
1330 const char *relname;
1332 oid = PQgetvalue(res, i, 0);
1333 nspname = PQgetvalue(res, i, 1);
1334 relname = PQgetvalue(res, i, 2);
1336 if (!describeOneTableDetails(nspname, relname, oid, verbose))
1353 * describeOneTableDetails (for \d)
1355 * Unfortunately, the information presented here is so complicated that it
1356 * cannot be done in a single query. So we have to assemble the printed table
1357 * by hand and pass it to the underlying printTable() function.
1360 describeOneTableDetails(const char *schemaname,
1361 const char *relationname,
1365 PQExpBufferData buf;
1366 PGresult *res = NULL;
1367 printTableOpt myopt = pset.popt.topt;
1368 printTableContent cont;
1369 bool printTableInitialized = false;
1371 char *view_def = NULL;
1373 char **seq_values = NULL;
1375 PQExpBufferData title;
1376 PQExpBufferData tmpbuf;
1387 bool forcerowsecurity;
1392 char relpersistence;
1395 bool show_column_details = false;
1400 myopt.default_footer = false;
1401 /* This output looks confusing in expanded mode. */
1402 myopt.expanded = false;
1404 initPQExpBuffer(&buf);
1405 initPQExpBuffer(&title);
1406 initPQExpBuffer(&tmpbuf);
1408 /* Get general table info */
1409 if (pset.sversion >= 90500)
1411 printfPQExpBuffer(&buf,
1412 "SELECT c.relchecks, c.relkind, c.relhasindex, c.relhasrules, "
1413 "c.relhastriggers, c.relrowsecurity, c.relforcerowsecurity, "
1414 "c.relhasoids, %s, c.reltablespace, "
1415 "CASE WHEN c.reloftype = 0 THEN '' ELSE c.reloftype::pg_catalog.regtype::pg_catalog.text END, "
1416 "c.relpersistence, c.relreplident\n"
1417 "FROM pg_catalog.pg_class c\n "
1418 "LEFT JOIN pg_catalog.pg_class tc ON (c.reltoastrelid = tc.oid)\n"
1419 "WHERE c.oid = '%s';",
1421 "pg_catalog.array_to_string(c.reloptions || "
1422 "array(select 'toast.' || x from pg_catalog.unnest(tc.reloptions) x), ', ')\n"
1426 else if (pset.sversion >= 90400)
1428 printfPQExpBuffer(&buf,
1429 "SELECT c.relchecks, c.relkind, c.relhasindex, c.relhasrules, "
1430 "c.relhastriggers, false, false, c.relhasoids, "
1431 "%s, c.reltablespace, "
1432 "CASE WHEN c.reloftype = 0 THEN '' ELSE c.reloftype::pg_catalog.regtype::pg_catalog.text END, "
1433 "c.relpersistence, c.relreplident\n"
1434 "FROM pg_catalog.pg_class c\n "
1435 "LEFT JOIN pg_catalog.pg_class tc ON (c.reltoastrelid = tc.oid)\n"
1436 "WHERE c.oid = '%s';",
1438 "pg_catalog.array_to_string(c.reloptions || "
1439 "array(select 'toast.' || x from pg_catalog.unnest(tc.reloptions) x), ', ')\n"
1443 else if (pset.sversion >= 90100)
1445 printfPQExpBuffer(&buf,
1446 "SELECT c.relchecks, c.relkind, c.relhasindex, c.relhasrules, "
1447 "c.relhastriggers, false, false, c.relhasoids, "
1448 "%s, c.reltablespace, "
1449 "CASE WHEN c.reloftype = 0 THEN '' ELSE c.reloftype::pg_catalog.regtype::pg_catalog.text END, "
1450 "c.relpersistence\n"
1451 "FROM pg_catalog.pg_class c\n "
1452 "LEFT JOIN pg_catalog.pg_class tc ON (c.reltoastrelid = tc.oid)\n"
1453 "WHERE c.oid = '%s';",
1455 "pg_catalog.array_to_string(c.reloptions || "
1456 "array(select 'toast.' || x from pg_catalog.unnest(tc.reloptions) x), ', ')\n"
1460 else if (pset.sversion >= 90000)
1462 printfPQExpBuffer(&buf,
1463 "SELECT c.relchecks, c.relkind, c.relhasindex, c.relhasrules, "
1464 "c.relhastriggers, false, false, c.relhasoids, "
1465 "%s, c.reltablespace, "
1466 "CASE WHEN c.reloftype = 0 THEN '' ELSE c.reloftype::pg_catalog.regtype::pg_catalog.text END\n"
1467 "FROM pg_catalog.pg_class c\n "
1468 "LEFT JOIN pg_catalog.pg_class tc ON (c.reltoastrelid = tc.oid)\n"
1469 "WHERE c.oid = '%s';",
1471 "pg_catalog.array_to_string(c.reloptions || "
1472 "array(select 'toast.' || x from pg_catalog.unnest(tc.reloptions) x), ', ')\n"
1476 else if (pset.sversion >= 80400)
1478 printfPQExpBuffer(&buf,
1479 "SELECT c.relchecks, c.relkind, c.relhasindex, c.relhasrules, "
1480 "c.relhastriggers, false, false, c.relhasoids, "
1481 "%s, c.reltablespace\n"
1482 "FROM pg_catalog.pg_class c\n "
1483 "LEFT JOIN pg_catalog.pg_class tc ON (c.reltoastrelid = tc.oid)\n"
1484 "WHERE c.oid = '%s';",
1486 "pg_catalog.array_to_string(c.reloptions || "
1487 "array(select 'toast.' || x from pg_catalog.unnest(tc.reloptions) x), ', ')\n"
1491 else if (pset.sversion >= 80200)
1493 printfPQExpBuffer(&buf,
1494 "SELECT relchecks, relkind, relhasindex, relhasrules, "
1495 "reltriggers <> 0, false, false, relhasoids, "
1496 "%s, reltablespace\n"
1497 "FROM pg_catalog.pg_class WHERE oid = '%s';",
1499 "pg_catalog.array_to_string(reloptions, E', ')" : "''"),
1502 else if (pset.sversion >= 80000)
1504 printfPQExpBuffer(&buf,
1505 "SELECT relchecks, relkind, relhasindex, relhasrules, "
1506 "reltriggers <> 0, false, false, relhasoids, "
1507 "'', reltablespace\n"
1508 "FROM pg_catalog.pg_class WHERE oid = '%s';",
1513 printfPQExpBuffer(&buf,
1514 "SELECT relchecks, relkind, relhasindex, relhasrules, "
1515 "reltriggers <> 0, false, false, relhasoids, "
1517 "FROM pg_catalog.pg_class WHERE oid = '%s';",
1521 res = PSQLexec(buf.data);
1525 /* Did we get anything? */
1526 if (PQntuples(res) == 0)
1529 psql_error("Did not find any relation with OID %s.\n", oid);
1533 tableinfo.checks = atoi(PQgetvalue(res, 0, 0));
1534 tableinfo.relkind = *(PQgetvalue(res, 0, 1));
1535 tableinfo.hasindex = strcmp(PQgetvalue(res, 0, 2), "t") == 0;
1536 tableinfo.hasrules = strcmp(PQgetvalue(res, 0, 3), "t") == 0;
1537 tableinfo.hastriggers = strcmp(PQgetvalue(res, 0, 4), "t") == 0;
1538 tableinfo.rowsecurity = strcmp(PQgetvalue(res, 0, 5), "t") == 0;
1539 tableinfo.forcerowsecurity = strcmp(PQgetvalue(res, 0, 6), "t") == 0;
1540 tableinfo.hasoids = strcmp(PQgetvalue(res, 0, 7), "t") == 0;
1541 tableinfo.reloptions = (pset.sversion >= 80200) ?
1542 pg_strdup(PQgetvalue(res, 0, 8)) : NULL;
1543 tableinfo.tablespace = (pset.sversion >= 80000) ?
1544 atooid(PQgetvalue(res, 0, 9)) : 0;
1545 tableinfo.reloftype = (pset.sversion >= 90000 &&
1546 strcmp(PQgetvalue(res, 0, 10), "") != 0) ?
1547 pg_strdup(PQgetvalue(res, 0, 10)) : NULL;
1548 tableinfo.relpersistence = (pset.sversion >= 90100) ?
1549 *(PQgetvalue(res, 0, 11)) : 0;
1550 tableinfo.relreplident = (pset.sversion >= 90400) ?
1551 *(PQgetvalue(res, 0, 12)) : 'd';
1556 * If it's a sequence, fetch its values and store into an array that will
1559 if (tableinfo.relkind == RELKIND_SEQUENCE)
1561 printfPQExpBuffer(&buf, "SELECT * FROM %s", fmtId(schemaname));
1562 /* must be separate because fmtId isn't reentrant */
1563 appendPQExpBuffer(&buf, ".%s;", fmtId(relationname));
1565 res = PSQLexec(buf.data);
1569 seq_values = pg_malloc((PQnfields(res) + 1) * sizeof(*seq_values));
1571 for (i = 0; i < PQnfields(res); i++)
1572 seq_values[i] = pg_strdup(PQgetvalue(res, 0, i));
1573 seq_values[i] = NULL;
1582 * You need to modify value of "firstvcol" which will be defined below if
1583 * you are adding column(s) preceding to verbose-only columns.
1585 printfPQExpBuffer(&buf, "SELECT a.attname,");
1586 appendPQExpBufferStr(&buf, "\n pg_catalog.format_type(a.atttypid, a.atttypmod),"
1587 "\n (SELECT substring(pg_catalog.pg_get_expr(d.adbin, d.adrelid) for 128)"
1588 "\n FROM pg_catalog.pg_attrdef d"
1589 "\n WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef),"
1590 "\n a.attnotnull, a.attnum,");
1591 if (pset.sversion >= 90100)
1592 appendPQExpBufferStr(&buf, "\n (SELECT c.collname FROM pg_catalog.pg_collation c, pg_catalog.pg_type t\n"
1593 " WHERE c.oid = a.attcollation AND t.oid = a.atttypid AND a.attcollation <> t.typcollation) AS attcollation");
1595 appendPQExpBufferStr(&buf, "\n NULL AS attcollation");
1596 if (pset.sversion >= 100000)
1597 appendPQExpBufferStr(&buf, ", a.attidentity");
1599 appendPQExpBufferStr(&buf, ", ''::\"char\" AS attidentity");
1600 if (tableinfo.relkind == RELKIND_INDEX)
1601 appendPQExpBufferStr(&buf, ",\n pg_catalog.pg_get_indexdef(a.attrelid, a.attnum, TRUE) AS indexdef");
1603 appendPQExpBufferStr(&buf, ",\n NULL AS indexdef");
1604 if (tableinfo.relkind == RELKIND_FOREIGN_TABLE && pset.sversion >= 90200)
1605 appendPQExpBufferStr(&buf, ",\n CASE WHEN attfdwoptions IS NULL THEN '' ELSE "
1606 " '(' || array_to_string(ARRAY(SELECT quote_ident(option_name) || ' ' || quote_literal(option_value) FROM "
1607 " pg_options_to_table(attfdwoptions)), ', ') || ')' END AS attfdwoptions");
1609 appendPQExpBufferStr(&buf, ",\n NULL AS attfdwoptions");
1612 appendPQExpBufferStr(&buf, ",\n a.attstorage");
1613 appendPQExpBufferStr(&buf, ",\n CASE WHEN a.attstattarget=-1 THEN NULL ELSE a.attstattarget END AS attstattarget");
1616 * In 9.0+, we have column comments for: relations, views, composite
1617 * types, and foreign tables (c.f. CommentObject() in comment.c).
1619 if (tableinfo.relkind == RELKIND_RELATION ||
1620 tableinfo.relkind == RELKIND_VIEW ||
1621 tableinfo.relkind == RELKIND_MATVIEW ||
1622 tableinfo.relkind == RELKIND_FOREIGN_TABLE ||
1623 tableinfo.relkind == RELKIND_COMPOSITE_TYPE ||
1624 tableinfo.relkind == RELKIND_PARTITIONED_TABLE)
1625 appendPQExpBufferStr(&buf, ", pg_catalog.col_description(a.attrelid, a.attnum)");
1628 appendPQExpBufferStr(&buf, "\nFROM pg_catalog.pg_attribute a");
1629 appendPQExpBuffer(&buf, "\nWHERE a.attrelid = '%s' AND a.attnum > 0 AND NOT a.attisdropped", oid);
1630 appendPQExpBufferStr(&buf, "\nORDER BY a.attnum;");
1632 res = PSQLexec(buf.data);
1635 numrows = PQntuples(res);
1638 switch (tableinfo.relkind)
1640 case RELKIND_RELATION:
1641 if (tableinfo.relpersistence == 'u')
1642 printfPQExpBuffer(&title, _("Unlogged table \"%s.%s\""),
1643 schemaname, relationname);
1645 printfPQExpBuffer(&title, _("Table \"%s.%s\""),
1646 schemaname, relationname);
1649 printfPQExpBuffer(&title, _("View \"%s.%s\""),
1650 schemaname, relationname);
1652 case RELKIND_MATVIEW:
1653 if (tableinfo.relpersistence == 'u')
1654 printfPQExpBuffer(&title, _("Unlogged materialized view \"%s.%s\""),
1655 schemaname, relationname);
1657 printfPQExpBuffer(&title, _("Materialized view \"%s.%s\""),
1658 schemaname, relationname);
1660 case RELKIND_SEQUENCE:
1661 printfPQExpBuffer(&title, _("Sequence \"%s.%s\""),
1662 schemaname, relationname);
1665 if (tableinfo.relpersistence == 'u')
1666 printfPQExpBuffer(&title, _("Unlogged index \"%s.%s\""),
1667 schemaname, relationname);
1669 printfPQExpBuffer(&title, _("Index \"%s.%s\""),
1670 schemaname, relationname);
1673 /* not used as of 8.2, but keep it for backwards compatibility */
1674 printfPQExpBuffer(&title, _("Special relation \"%s.%s\""),
1675 schemaname, relationname);
1677 case RELKIND_TOASTVALUE:
1678 printfPQExpBuffer(&title, _("TOAST table \"%s.%s\""),
1679 schemaname, relationname);
1681 case RELKIND_COMPOSITE_TYPE:
1682 printfPQExpBuffer(&title, _("Composite type \"%s.%s\""),
1683 schemaname, relationname);
1685 case RELKIND_FOREIGN_TABLE:
1686 printfPQExpBuffer(&title, _("Foreign table \"%s.%s\""),
1687 schemaname, relationname);
1689 case RELKIND_PARTITIONED_TABLE:
1690 if (tableinfo.relpersistence == 'u')
1691 printfPQExpBuffer(&title, _("Unlogged table \"%s.%s\""),
1692 schemaname, relationname);
1694 printfPQExpBuffer(&title, _("Table \"%s.%s\""),
1695 schemaname, relationname);
1698 /* untranslated unknown relkind */
1699 printfPQExpBuffer(&title, "?%c? \"%s.%s\"",
1700 tableinfo.relkind, schemaname, relationname);
1704 /* Set the number of columns, and their names */
1705 headers[0] = gettext_noop("Column");
1706 headers[1] = gettext_noop("Type");
1709 if (tableinfo.relkind == RELKIND_RELATION ||
1710 tableinfo.relkind == RELKIND_VIEW ||
1711 tableinfo.relkind == RELKIND_MATVIEW ||
1712 tableinfo.relkind == RELKIND_FOREIGN_TABLE ||
1713 tableinfo.relkind == RELKIND_COMPOSITE_TYPE ||
1714 tableinfo.relkind == RELKIND_PARTITIONED_TABLE)
1716 headers[cols++] = gettext_noop("Collation");
1717 headers[cols++] = gettext_noop("Nullable");
1718 headers[cols++] = gettext_noop("Default");
1719 show_column_details = true;
1722 if (tableinfo.relkind == RELKIND_SEQUENCE)
1723 headers[cols++] = gettext_noop("Value");
1725 if (tableinfo.relkind == RELKIND_INDEX)
1726 headers[cols++] = gettext_noop("Definition");
1728 if (tableinfo.relkind == RELKIND_FOREIGN_TABLE && pset.sversion >= 90200)
1729 headers[cols++] = gettext_noop("FDW options");
1733 headers[cols++] = gettext_noop("Storage");
1734 if (tableinfo.relkind == RELKIND_RELATION ||
1735 tableinfo.relkind == RELKIND_MATVIEW ||
1736 tableinfo.relkind == RELKIND_FOREIGN_TABLE ||
1737 tableinfo.relkind == RELKIND_PARTITIONED_TABLE)
1738 headers[cols++] = gettext_noop("Stats target");
1739 /* Column comments, if the relkind supports this feature. */
1740 if (tableinfo.relkind == RELKIND_RELATION ||
1741 tableinfo.relkind == RELKIND_VIEW ||
1742 tableinfo.relkind == RELKIND_MATVIEW ||
1743 tableinfo.relkind == RELKIND_COMPOSITE_TYPE ||
1744 tableinfo.relkind == RELKIND_FOREIGN_TABLE ||
1745 tableinfo.relkind == RELKIND_PARTITIONED_TABLE)
1746 headers[cols++] = gettext_noop("Description");
1749 printTableInit(&cont, &myopt, title.data, cols, numrows);
1750 printTableInitialized = true;
1752 for (i = 0; i < cols; i++)
1753 printTableAddHeader(&cont, headers[i], true, 'l');
1755 /* Get view_def if table is a view or materialized view */
1756 if ((tableinfo.relkind == RELKIND_VIEW ||
1757 tableinfo.relkind == RELKIND_MATVIEW) && verbose)
1761 printfPQExpBuffer(&buf,
1762 "SELECT pg_catalog.pg_get_viewdef('%s'::pg_catalog.oid, true);",
1764 result = PSQLexec(buf.data);
1768 if (PQntuples(result) > 0)
1769 view_def = pg_strdup(PQgetvalue(result, 0, 0));
1774 /* Generate table cells to be printed */
1775 for (i = 0; i < numrows; i++)
1778 printTableAddCell(&cont, PQgetvalue(res, i, 0), false, false);
1781 printTableAddCell(&cont, PQgetvalue(res, i, 1), false, false);
1783 /* Collation, Nullable, Default */
1784 if (show_column_details)
1787 char *default_str = "";
1789 printTableAddCell(&cont, PQgetvalue(res, i, 5), false, false);
1791 printTableAddCell(&cont, strcmp(PQgetvalue(res, i, 3), "t") == 0 ? "not null" : "", false, false);
1793 identity = PQgetvalue(res, i, 6);
1796 /* (note: above we cut off the 'default' string at 128) */
1797 default_str = PQgetvalue(res, i, 2);
1798 else if (identity[0] == ATTRIBUTE_IDENTITY_ALWAYS)
1799 default_str = "generated always as identity";
1800 else if (identity[0] == ATTRIBUTE_IDENTITY_BY_DEFAULT)
1801 default_str = "generated by default as identity";
1803 printTableAddCell(&cont, default_str, false, false);
1806 /* Value: for sequences only */
1807 if (tableinfo.relkind == RELKIND_SEQUENCE)
1808 printTableAddCell(&cont, seq_values[i], false, false);
1810 /* Expression for index column */
1811 if (tableinfo.relkind == RELKIND_INDEX)
1812 printTableAddCell(&cont, PQgetvalue(res, i, 7), false, false);
1814 /* FDW options for foreign table column, only for 9.2 or later */
1815 if (tableinfo.relkind == RELKIND_FOREIGN_TABLE && pset.sversion >= 90200)
1816 printTableAddCell(&cont, PQgetvalue(res, i, 8), false, false);
1818 /* Storage and Description */
1822 char *storage = PQgetvalue(res, i, firstvcol);
1824 /* these strings are literal in our syntax, so not translated. */
1825 printTableAddCell(&cont, (storage[0] == 'p' ? "plain" :
1826 (storage[0] == 'm' ? "main" :
1827 (storage[0] == 'x' ? "extended" :
1828 (storage[0] == 'e' ? "external" :
1832 /* Statistics target, if the relkind supports this feature */
1833 if (tableinfo.relkind == RELKIND_RELATION ||
1834 tableinfo.relkind == RELKIND_MATVIEW ||
1835 tableinfo.relkind == RELKIND_FOREIGN_TABLE ||
1836 tableinfo.relkind == RELKIND_PARTITIONED_TABLE)
1838 printTableAddCell(&cont, PQgetvalue(res, i, firstvcol + 1),
1842 /* Column comments, if the relkind supports this feature. */
1843 if (tableinfo.relkind == RELKIND_RELATION ||
1844 tableinfo.relkind == RELKIND_VIEW ||
1845 tableinfo.relkind == RELKIND_MATVIEW ||
1846 tableinfo.relkind == RELKIND_COMPOSITE_TYPE ||
1847 tableinfo.relkind == RELKIND_FOREIGN_TABLE ||
1848 tableinfo.relkind == RELKIND_PARTITIONED_TABLE)
1849 printTableAddCell(&cont, PQgetvalue(res, i, firstvcol + 2),
1855 if (pset.sversion >= 100000)
1857 /* Get the partition information */
1861 char *partconstraintdef = NULL;
1863 /* If verbose, also request the partition constraint definition */
1865 printfPQExpBuffer(&buf,
1866 "SELECT inhparent::pg_catalog.regclass,"
1867 " pg_get_expr(c.relpartbound, inhrelid),"
1868 " pg_get_partition_constraintdef(inhrelid)"
1869 " FROM pg_catalog.pg_class c"
1870 " JOIN pg_catalog.pg_inherits"
1871 " ON c.oid = inhrelid"
1872 " WHERE c.oid = '%s' AND c.relispartition;", oid);
1874 printfPQExpBuffer(&buf,
1875 "SELECT inhparent::pg_catalog.regclass,"
1876 " pg_get_expr(c.relpartbound, inhrelid)"
1877 " FROM pg_catalog.pg_class c"
1878 " JOIN pg_catalog.pg_inherits"
1879 " ON c.oid = inhrelid"
1880 " WHERE c.oid = '%s' AND c.relispartition;", oid);
1881 result = PSQLexec(buf.data);
1885 if (PQntuples(result) > 0)
1887 parent_name = PQgetvalue(result, 0, 0);
1888 partdef = PQgetvalue(result, 0, 1);
1890 if (PQnfields(result) == 3)
1891 partconstraintdef = PQgetvalue(result, 0, 2);
1893 printfPQExpBuffer(&tmpbuf, _("Partition of: %s %s"), parent_name,
1895 printTableAddFooter(&cont, tmpbuf.data);
1897 if (partconstraintdef)
1899 printfPQExpBuffer(&tmpbuf, _("Partition constraint: %s"),
1901 printTableAddFooter(&cont, tmpbuf.data);
1908 if (tableinfo.relkind == RELKIND_PARTITIONED_TABLE)
1910 /* Get the partition key information */
1914 printfPQExpBuffer(&buf,
1915 "SELECT pg_catalog.pg_get_partkeydef('%s'::pg_catalog.oid);",
1917 result = PSQLexec(buf.data);
1918 if (!result || PQntuples(result) != 1)
1921 partkeydef = PQgetvalue(result, 0, 0);
1922 printfPQExpBuffer(&tmpbuf, _("Partition key: %s"), partkeydef);
1923 printTableAddFooter(&cont, tmpbuf.data);
1927 if (tableinfo.relkind == RELKIND_INDEX)
1929 /* Footer information about an index */
1932 printfPQExpBuffer(&buf,
1933 "SELECT i.indisunique, i.indisprimary, i.indisclustered, ");
1934 if (pset.sversion >= 80200)
1935 appendPQExpBufferStr(&buf, "i.indisvalid,\n");
1937 appendPQExpBufferStr(&buf, "true AS indisvalid,\n");
1938 if (pset.sversion >= 90000)
1939 appendPQExpBufferStr(&buf,
1940 " (NOT i.indimmediate) AND "
1941 "EXISTS (SELECT 1 FROM pg_catalog.pg_constraint "
1942 "WHERE conrelid = i.indrelid AND "
1943 "conindid = i.indexrelid AND "
1944 "contype IN ('p','u','x') AND "
1945 "condeferrable) AS condeferrable,\n"
1946 " (NOT i.indimmediate) AND "
1947 "EXISTS (SELECT 1 FROM pg_catalog.pg_constraint "
1948 "WHERE conrelid = i.indrelid AND "
1949 "conindid = i.indexrelid AND "
1950 "contype IN ('p','u','x') AND "
1951 "condeferred) AS condeferred,\n");
1953 appendPQExpBufferStr(&buf,
1954 " false AS condeferrable, false AS condeferred,\n");
1956 if (pset.sversion >= 90400)
1957 appendPQExpBuffer(&buf, "i.indisreplident,\n");
1959 appendPQExpBuffer(&buf, "false AS indisreplident,\n");
1961 appendPQExpBuffer(&buf, " a.amname, c2.relname, "
1962 "pg_catalog.pg_get_expr(i.indpred, i.indrelid, true)\n"
1963 "FROM pg_catalog.pg_index i, pg_catalog.pg_class c, pg_catalog.pg_class c2, pg_catalog.pg_am a\n"
1964 "WHERE i.indexrelid = c.oid AND c.oid = '%s' AND c.relam = a.oid\n"
1965 "AND i.indrelid = c2.oid;",
1968 result = PSQLexec(buf.data);
1971 else if (PQntuples(result) != 1)
1978 char *indisunique = PQgetvalue(result, 0, 0);
1979 char *indisprimary = PQgetvalue(result, 0, 1);
1980 char *indisclustered = PQgetvalue(result, 0, 2);
1981 char *indisvalid = PQgetvalue(result, 0, 3);
1982 char *deferrable = PQgetvalue(result, 0, 4);
1983 char *deferred = PQgetvalue(result, 0, 5);
1984 char *indisreplident = PQgetvalue(result, 0, 6);
1985 char *indamname = PQgetvalue(result, 0, 7);
1986 char *indtable = PQgetvalue(result, 0, 8);
1987 char *indpred = PQgetvalue(result, 0, 9);
1989 if (strcmp(indisprimary, "t") == 0)
1990 printfPQExpBuffer(&tmpbuf, _("primary key, "));
1991 else if (strcmp(indisunique, "t") == 0)
1992 printfPQExpBuffer(&tmpbuf, _("unique, "));
1994 resetPQExpBuffer(&tmpbuf);
1995 appendPQExpBuffer(&tmpbuf, "%s, ", indamname);
1997 /* we assume here that index and table are in same schema */
1998 appendPQExpBuffer(&tmpbuf, _("for table \"%s.%s\""),
1999 schemaname, indtable);
2001 if (strlen(indpred))
2002 appendPQExpBuffer(&tmpbuf, _(", predicate (%s)"), indpred);
2004 if (strcmp(indisclustered, "t") == 0)
2005 appendPQExpBufferStr(&tmpbuf, _(", clustered"));
2007 if (strcmp(indisvalid, "t") != 0)
2008 appendPQExpBufferStr(&tmpbuf, _(", invalid"));
2010 if (strcmp(deferrable, "t") == 0)
2011 appendPQExpBufferStr(&tmpbuf, _(", deferrable"));
2013 if (strcmp(deferred, "t") == 0)
2014 appendPQExpBufferStr(&tmpbuf, _(", initially deferred"));
2016 if (strcmp(indisreplident, "t") == 0)
2017 appendPQExpBuffer(&tmpbuf, _(", replica identity"));
2019 printTableAddFooter(&cont, tmpbuf.data);
2020 add_tablespace_footer(&cont, tableinfo.relkind,
2021 tableinfo.tablespace, true);
2026 else if (tableinfo.relkind == RELKIND_SEQUENCE)
2028 /* Footer information about a sequence */
2029 PGresult *result = NULL;
2031 /* Get the column that owns this sequence */
2032 printfPQExpBuffer(&buf, "SELECT pg_catalog.quote_ident(nspname) || '.' ||"
2033 "\n pg_catalog.quote_ident(relname) || '.' ||"
2034 "\n pg_catalog.quote_ident(attname),"
2036 "\nFROM pg_catalog.pg_class c"
2037 "\nINNER JOIN pg_catalog.pg_depend d ON c.oid=d.refobjid"
2038 "\nINNER JOIN pg_catalog.pg_namespace n ON n.oid=c.relnamespace"
2039 "\nINNER JOIN pg_catalog.pg_attribute a ON ("
2040 "\n a.attrelid=c.oid AND"
2041 "\n a.attnum=d.refobjsubid)"
2042 "\nWHERE d.classid='pg_catalog.pg_class'::pg_catalog.regclass"
2043 "\n AND d.refclassid='pg_catalog.pg_class'::pg_catalog.regclass"
2045 "\n AND d.deptype IN ('a', 'i')",
2048 result = PSQLexec(buf.data);
2051 else if (PQntuples(result) == 1)
2053 switch (PQgetvalue(result, 0, 1)[0])
2056 printfPQExpBuffer(&buf, _("Owned by: %s"),
2057 PQgetvalue(result, 0, 0));
2058 printTableAddFooter(&cont, buf.data);
2061 printfPQExpBuffer(&buf, _("Sequence for identity column: %s"),
2062 PQgetvalue(result, 0, 0));
2063 printTableAddFooter(&cont, buf.data);
2069 * If we get no rows back, don't show anything (obviously). We should
2070 * never get more than one row back, but if we do, just ignore it and
2071 * don't print anything.
2075 else if (tableinfo.relkind == RELKIND_RELATION ||
2076 tableinfo.relkind == RELKIND_MATVIEW ||
2077 tableinfo.relkind == RELKIND_FOREIGN_TABLE ||
2078 tableinfo.relkind == RELKIND_PARTITIONED_TABLE)
2080 /* Footer information about a table */
2081 PGresult *result = NULL;
2085 if (tableinfo.hasindex)
2087 printfPQExpBuffer(&buf,
2088 "SELECT c2.relname, i.indisprimary, i.indisunique, i.indisclustered, ");
2089 if (pset.sversion >= 80200)
2090 appendPQExpBufferStr(&buf, "i.indisvalid, ");
2092 appendPQExpBufferStr(&buf, "true as indisvalid, ");
2093 appendPQExpBufferStr(&buf, "pg_catalog.pg_get_indexdef(i.indexrelid, 0, true),\n ");
2094 if (pset.sversion >= 90000)
2095 appendPQExpBufferStr(&buf,
2096 "pg_catalog.pg_get_constraintdef(con.oid, true), "
2097 "contype, condeferrable, condeferred");
2099 appendPQExpBufferStr(&buf,
2100 "null AS constraintdef, null AS contype, "
2101 "false AS condeferrable, false AS condeferred");
2102 if (pset.sversion >= 90400)
2103 appendPQExpBufferStr(&buf, ", i.indisreplident");
2105 appendPQExpBufferStr(&buf, ", false AS indisreplident");
2106 if (pset.sversion >= 80000)
2107 appendPQExpBufferStr(&buf, ", c2.reltablespace");
2108 appendPQExpBufferStr(&buf,
2109 "\nFROM pg_catalog.pg_class c, pg_catalog.pg_class c2, pg_catalog.pg_index i\n");
2110 if (pset.sversion >= 90000)
2111 appendPQExpBufferStr(&buf,
2112 " LEFT JOIN pg_catalog.pg_constraint con ON (conrelid = i.indrelid AND conindid = i.indexrelid AND contype IN ('p','u','x'))\n");
2113 appendPQExpBuffer(&buf,
2114 "WHERE c.oid = '%s' AND c.oid = i.indrelid AND i.indexrelid = c2.oid\n"
2115 "ORDER BY i.indisprimary DESC, i.indisunique DESC, c2.relname;",
2117 result = PSQLexec(buf.data);
2121 tuples = PQntuples(result);
2125 printTableAddFooter(&cont, _("Indexes:"));
2126 for (i = 0; i < tuples; i++)
2128 /* untranslated index name */
2129 printfPQExpBuffer(&buf, " \"%s\"",
2130 PQgetvalue(result, i, 0));
2132 /* If exclusion constraint, print the constraintdef */
2133 if (strcmp(PQgetvalue(result, i, 7), "x") == 0)
2135 appendPQExpBuffer(&buf, " %s",
2136 PQgetvalue(result, i, 6));
2140 const char *indexdef;
2141 const char *usingpos;
2143 /* Label as primary key or unique (but not both) */
2144 if (strcmp(PQgetvalue(result, i, 1), "t") == 0)
2145 appendPQExpBufferStr(&buf, " PRIMARY KEY,");
2146 else if (strcmp(PQgetvalue(result, i, 2), "t") == 0)
2148 if (strcmp(PQgetvalue(result, i, 7), "u") == 0)
2149 appendPQExpBufferStr(&buf, " UNIQUE CONSTRAINT,");
2151 appendPQExpBufferStr(&buf, " UNIQUE,");
2154 /* Everything after "USING" is echoed verbatim */
2155 indexdef = PQgetvalue(result, i, 5);
2156 usingpos = strstr(indexdef, " USING ");
2158 indexdef = usingpos + 7;
2159 appendPQExpBuffer(&buf, " %s", indexdef);
2161 /* Need these for deferrable PK/UNIQUE indexes */
2162 if (strcmp(PQgetvalue(result, i, 8), "t") == 0)
2163 appendPQExpBufferStr(&buf, " DEFERRABLE");
2165 if (strcmp(PQgetvalue(result, i, 9), "t") == 0)
2166 appendPQExpBufferStr(&buf, " INITIALLY DEFERRED");
2169 /* Add these for all cases */
2170 if (strcmp(PQgetvalue(result, i, 3), "t") == 0)
2171 appendPQExpBufferStr(&buf, " CLUSTER");
2173 if (strcmp(PQgetvalue(result, i, 4), "t") != 0)
2174 appendPQExpBufferStr(&buf, " INVALID");
2176 if (strcmp(PQgetvalue(result, i, 10), "t") == 0)
2177 appendPQExpBuffer(&buf, " REPLICA IDENTITY");
2179 printTableAddFooter(&cont, buf.data);
2181 /* Print tablespace of the index on the same line */
2182 if (pset.sversion >= 80000)
2183 add_tablespace_footer(&cont, RELKIND_INDEX,
2184 atooid(PQgetvalue(result, i, 11)),
2191 /* print table (and column) check constraints */
2192 if (tableinfo.checks)
2194 printfPQExpBuffer(&buf,
2195 "SELECT r.conname, "
2196 "pg_catalog.pg_get_constraintdef(r.oid, true)\n"
2197 "FROM pg_catalog.pg_constraint r\n"
2198 "WHERE r.conrelid = '%s' AND r.contype = 'c'\n"
2201 result = PSQLexec(buf.data);
2205 tuples = PQntuples(result);
2209 printTableAddFooter(&cont, _("Check constraints:"));
2210 for (i = 0; i < tuples; i++)
2212 /* untranslated constraint name and def */
2213 printfPQExpBuffer(&buf, " \"%s\" %s",
2214 PQgetvalue(result, i, 0),
2215 PQgetvalue(result, i, 1));
2217 printTableAddFooter(&cont, buf.data);
2223 /* print foreign-key constraints (there are none if no triggers) */
2224 if (tableinfo.hastriggers)
2226 printfPQExpBuffer(&buf,
2228 " pg_catalog.pg_get_constraintdef(r.oid, true) as condef\n"
2229 "FROM pg_catalog.pg_constraint r\n"
2230 "WHERE r.conrelid = '%s' AND r.contype = 'f' ORDER BY 1;",
2232 result = PSQLexec(buf.data);
2236 tuples = PQntuples(result);
2240 printTableAddFooter(&cont, _("Foreign-key constraints:"));
2241 for (i = 0; i < tuples; i++)
2243 /* untranslated constraint name and def */
2244 printfPQExpBuffer(&buf, " \"%s\" %s",
2245 PQgetvalue(result, i, 0),
2246 PQgetvalue(result, i, 1));
2248 printTableAddFooter(&cont, buf.data);
2254 /* print incoming foreign-key references (none if no triggers) */
2255 if (tableinfo.hastriggers)
2257 printfPQExpBuffer(&buf,
2258 "SELECT conname, conrelid::pg_catalog.regclass,\n"
2259 " pg_catalog.pg_get_constraintdef(c.oid, true) as condef\n"
2260 "FROM pg_catalog.pg_constraint c\n"
2261 "WHERE c.confrelid = '%s' AND c.contype = 'f' ORDER BY 1;",
2263 result = PSQLexec(buf.data);
2267 tuples = PQntuples(result);
2271 printTableAddFooter(&cont, _("Referenced by:"));
2272 for (i = 0; i < tuples; i++)
2274 printfPQExpBuffer(&buf, " TABLE \"%s\" CONSTRAINT \"%s\" %s",
2275 PQgetvalue(result, i, 1),
2276 PQgetvalue(result, i, 0),
2277 PQgetvalue(result, i, 2));
2279 printTableAddFooter(&cont, buf.data);
2285 /* print any row-level policies */
2286 if (pset.sversion >= 90500)
2288 if (pset.sversion >= 100000)
2289 printfPQExpBuffer(&buf,
2290 "SELECT pol.polname, pol.polpermissive,\n"
2291 "CASE WHEN pol.polroles = '{0}' THEN NULL ELSE array_to_string(array(select rolname from pg_roles where oid = any (pol.polroles) order by 1),',') END,\n"
2292 "pg_catalog.pg_get_expr(pol.polqual, pol.polrelid),\n"
2293 "pg_catalog.pg_get_expr(pol.polwithcheck, pol.polrelid),\n"
2295 "WHEN 'r' THEN 'SELECT'\n"
2296 "WHEN 'a' THEN 'INSERT'\n"
2297 "WHEN 'w' THEN 'UPDATE'\n"
2298 "WHEN 'd' THEN 'DELETE'\n"
2300 "FROM pg_catalog.pg_policy pol\n"
2301 "WHERE pol.polrelid = '%s' ORDER BY 1;",
2304 printfPQExpBuffer(&buf,
2305 "SELECT pol.polname, 't' as polpermissive,\n"
2306 "CASE WHEN pol.polroles = '{0}' THEN NULL ELSE array_to_string(array(select rolname from pg_roles where oid = any (pol.polroles) order by 1),',') END,\n"
2307 "pg_catalog.pg_get_expr(pol.polqual, pol.polrelid),\n"
2308 "pg_catalog.pg_get_expr(pol.polwithcheck, pol.polrelid),\n"
2310 "WHEN 'r' THEN 'SELECT'\n"
2311 "WHEN 'a' THEN 'INSERT'\n"
2312 "WHEN 'w' THEN 'UPDATE'\n"
2313 "WHEN 'd' THEN 'DELETE'\n"
2315 "FROM pg_catalog.pg_policy pol\n"
2316 "WHERE pol.polrelid = '%s' ORDER BY 1;",
2319 result = PSQLexec(buf.data);
2323 tuples = PQntuples(result);
2326 * Handle cases where RLS is enabled and there are policies, or
2327 * there aren't policies, or RLS isn't enabled but there are
2330 if (tableinfo.rowsecurity && !tableinfo.forcerowsecurity && tuples > 0)
2331 printTableAddFooter(&cont, _("Policies:"));
2333 if (tableinfo.rowsecurity && tableinfo.forcerowsecurity && tuples > 0)
2334 printTableAddFooter(&cont, _("Policies (forced row security enabled):"));
2336 if (tableinfo.rowsecurity && !tableinfo.forcerowsecurity && tuples == 0)
2337 printTableAddFooter(&cont, _("Policies (row security enabled): (none)"));
2339 if (tableinfo.rowsecurity && tableinfo.forcerowsecurity && tuples == 0)
2340 printTableAddFooter(&cont, _("Policies (forced row security enabled): (none)"));
2342 if (!tableinfo.rowsecurity && tuples > 0)
2343 printTableAddFooter(&cont, _("Policies (row security disabled):"));
2345 /* Might be an empty set - that's ok */
2346 for (i = 0; i < tuples; i++)
2348 printfPQExpBuffer(&buf, " POLICY \"%s\"",
2349 PQgetvalue(result, i, 0));
2351 if (*(PQgetvalue(result, i, 1)) == 'f')
2352 appendPQExpBuffer(&buf, " AS RESTRICTIVE");
2354 if (!PQgetisnull(result, i, 5))
2355 appendPQExpBuffer(&buf, " FOR %s",
2356 PQgetvalue(result, i, 5));
2358 if (!PQgetisnull(result, i, 2))
2360 appendPQExpBuffer(&buf, "\n TO %s",
2361 PQgetvalue(result, i, 2));
2364 if (!PQgetisnull(result, i, 3))
2365 appendPQExpBuffer(&buf, "\n USING (%s)",
2366 PQgetvalue(result, i, 3));
2368 if (!PQgetisnull(result, i, 4))
2369 appendPQExpBuffer(&buf, "\n WITH CHECK (%s)",
2370 PQgetvalue(result, i, 4));
2372 printTableAddFooter(&cont, buf.data);
2378 /* print any extended statistics */
2379 if (pset.sversion >= 100000)
2381 printfPQExpBuffer(&buf,
2383 "stxrelid::pg_catalog.regclass, "
2384 "stxnamespace::pg_catalog.regnamespace AS nsp, "
2386 " (SELECT pg_catalog.string_agg(pg_catalog.quote_ident(attname),', ')\n"
2387 " FROM pg_catalog.unnest(stxkeys) s(attnum)\n"
2388 " JOIN pg_catalog.pg_attribute a ON (stxrelid = a.attrelid AND\n"
2389 " a.attnum = s.attnum AND NOT attisdropped)) AS columns,\n"
2390 " (stxkind @> '{d}') AS ndist_enabled,\n"
2391 " (stxkind @> '{f}') AS deps_enabled\n"
2392 "FROM pg_catalog.pg_statistic_ext stat "
2393 "WHERE stxrelid = '%s'\n"
2397 result = PSQLexec(buf.data);
2401 tuples = PQntuples(result);
2405 printTableAddFooter(&cont, _("Statistics objects:"));
2407 for (i = 0; i < tuples; i++)
2409 bool gotone = false;
2411 printfPQExpBuffer(&buf, " ");
2413 /* statistics object name (qualified with namespace) */
2414 appendPQExpBuffer(&buf, "\"%s\".\"%s\" (",
2415 PQgetvalue(result, i, 2),
2416 PQgetvalue(result, i, 3));
2419 if (strcmp(PQgetvalue(result, i, 5), "t") == 0)
2421 appendPQExpBufferStr(&buf, "ndistinct");
2425 if (strcmp(PQgetvalue(result, i, 6), "t") == 0)
2427 appendPQExpBuffer(&buf, "%sdependencies", gotone ? ", " : "");
2430 appendPQExpBuffer(&buf, ") ON %s FROM %s",
2431 PQgetvalue(result, i, 4),
2432 PQgetvalue(result, i, 1));
2434 printTableAddFooter(&cont, buf.data);
2441 if (tableinfo.hasrules && tableinfo.relkind != RELKIND_MATVIEW)
2443 if (pset.sversion >= 80300)
2445 printfPQExpBuffer(&buf,
2446 "SELECT r.rulename, trim(trailing ';' from pg_catalog.pg_get_ruledef(r.oid, true)), "
2448 "FROM pg_catalog.pg_rewrite r\n"
2449 "WHERE r.ev_class = '%s' ORDER BY 1;",
2454 printfPQExpBuffer(&buf,
2455 "SELECT r.rulename, trim(trailing ';' from pg_catalog.pg_get_ruledef(r.oid, true)), "
2456 "'O'::char AS ev_enabled\n"
2457 "FROM pg_catalog.pg_rewrite r\n"
2458 "WHERE r.ev_class = '%s' ORDER BY 1;",
2461 result = PSQLexec(buf.data);
2465 tuples = PQntuples(result);
2472 for (category = 0; category < 4; category++)
2474 have_heading = false;
2476 for (i = 0; i < tuples; i++)
2478 const char *ruledef;
2479 bool list_rule = false;
2484 if (*PQgetvalue(result, i, 2) == 'O')
2488 if (*PQgetvalue(result, i, 2) == 'D')
2492 if (*PQgetvalue(result, i, 2) == 'A')
2496 if (*PQgetvalue(result, i, 2) == 'R')
2508 printfPQExpBuffer(&buf, _("Rules:"));
2511 printfPQExpBuffer(&buf, _("Disabled rules:"));
2514 printfPQExpBuffer(&buf, _("Rules firing always:"));
2517 printfPQExpBuffer(&buf, _("Rules firing on replica only:"));
2520 printTableAddFooter(&cont, buf.data);
2521 have_heading = true;
2524 /* Everything after "CREATE RULE" is echoed verbatim */
2525 ruledef = PQgetvalue(result, i, 1);
2527 printfPQExpBuffer(&buf, " %s", ruledef);
2528 printTableAddFooter(&cont, buf.data);
2535 /* print any publications */
2536 if (pset.sversion >= 100000)
2538 printfPQExpBuffer(&buf,
2540 "FROM pg_catalog.pg_publication p\n"
2541 "JOIN pg_catalog.pg_publication_rel pr ON p.oid = pr.prpubid\n"
2542 "WHERE pr.prrelid = '%s'\n"
2545 "FROM pg_catalog.pg_publication p\n"
2546 "WHERE p.puballtables AND pg_relation_is_publishable('%s')\n"
2550 result = PSQLexec(buf.data);
2554 tuples = PQntuples(result);
2557 printTableAddFooter(&cont, _("Publications:"));
2559 /* Might be an empty set - that's ok */
2560 for (i = 0; i < tuples; i++)
2562 printfPQExpBuffer(&buf, " \"%s\"",
2563 PQgetvalue(result, i, 0));
2565 printTableAddFooter(&cont, buf.data);
2573 PGresult *result = NULL;
2575 /* Footer information about a view */
2576 printTableAddFooter(&cont, _("View definition:"));
2577 printTableAddFooter(&cont, view_def);
2580 if (tableinfo.hasrules)
2582 printfPQExpBuffer(&buf,
2583 "SELECT r.rulename, trim(trailing ';' from pg_catalog.pg_get_ruledef(r.oid, true))\n"
2584 "FROM pg_catalog.pg_rewrite r\n"
2585 "WHERE r.ev_class = '%s' AND r.rulename != '_RETURN' ORDER BY 1;",
2587 result = PSQLexec(buf.data);
2591 if (PQntuples(result) > 0)
2593 printTableAddFooter(&cont, _("Rules:"));
2594 for (i = 0; i < PQntuples(result); i++)
2596 const char *ruledef;
2598 /* Everything after "CREATE RULE" is echoed verbatim */
2599 ruledef = PQgetvalue(result, i, 1);
2602 printfPQExpBuffer(&buf, " %s", ruledef);
2603 printTableAddFooter(&cont, buf.data);
2611 * Print triggers next, if any (but only user-defined triggers). This
2612 * could apply to either a table or a view.
2614 if (tableinfo.hastriggers)
2619 printfPQExpBuffer(&buf,
2621 "pg_catalog.pg_get_triggerdef(t.oid%s), "
2623 "FROM pg_catalog.pg_trigger t\n"
2624 "WHERE t.tgrelid = '%s' AND ",
2625 (pset.sversion >= 90000 ? ", true" : ""),
2626 (pset.sversion >= 90000 ? "t.tgisinternal" :
2627 pset.sversion >= 80300 ?
2628 "t.tgconstraint <> 0 AS tgisinternal" :
2629 "false AS tgisinternal"), oid);
2630 if (pset.sversion >= 90000)
2631 /* display/warn about disabled internal triggers */
2632 appendPQExpBuffer(&buf, "(NOT t.tgisinternal OR (t.tgisinternal AND t.tgenabled = 'D'))");
2633 else if (pset.sversion >= 80300)
2634 appendPQExpBufferStr(&buf, "(t.tgconstraint = 0 OR (t.tgconstraint <> 0 AND t.tgenabled = 'D'))");
2636 appendPQExpBufferStr(&buf,
2637 "(NOT tgisconstraint "
2639 " (SELECT 1 FROM pg_catalog.pg_depend d "
2640 " JOIN pg_catalog.pg_constraint c ON (d.refclassid = c.tableoid AND d.refobjid = c.oid) "
2641 " WHERE d.classid = t.tableoid AND d.objid = t.oid AND d.deptype = 'i' AND c.contype = 'f'))");
2642 appendPQExpBufferStr(&buf, "\nORDER BY 1;");
2644 result = PSQLexec(buf.data);
2648 tuples = PQntuples(result);
2656 * split the output into 4 different categories. Enabled triggers,
2657 * disabled triggers and the two special ALWAYS and REPLICA
2660 for (category = 0; category <= 4; category++)
2662 have_heading = false;
2663 for (i = 0; i < tuples; i++)
2667 const char *usingpos;
2668 const char *tgenabled;
2669 const char *tgisinternal;
2672 * Check if this trigger falls into the current category
2674 tgenabled = PQgetvalue(result, i, 2);
2675 tgisinternal = PQgetvalue(result, i, 3);
2676 list_trigger = false;
2680 if (*tgenabled == 'O' || *tgenabled == 't')
2681 list_trigger = true;
2684 if ((*tgenabled == 'D' || *tgenabled == 'f') &&
2685 *tgisinternal == 'f')
2686 list_trigger = true;
2689 if ((*tgenabled == 'D' || *tgenabled == 'f') &&
2690 *tgisinternal == 't')
2691 list_trigger = true;
2694 if (*tgenabled == 'A')
2695 list_trigger = true;
2698 if (*tgenabled == 'R')
2699 list_trigger = true;
2702 if (list_trigger == false)
2705 /* Print the category heading once */
2706 if (have_heading == false)
2711 printfPQExpBuffer(&buf, _("Triggers:"));
2714 if (pset.sversion >= 80300)
2715 printfPQExpBuffer(&buf, _("Disabled user triggers:"));
2717 printfPQExpBuffer(&buf, _("Disabled triggers:"));
2720 printfPQExpBuffer(&buf, _("Disabled internal triggers:"));
2723 printfPQExpBuffer(&buf, _("Triggers firing always:"));
2726 printfPQExpBuffer(&buf, _("Triggers firing on replica only:"));
2730 printTableAddFooter(&cont, buf.data);
2731 have_heading = true;
2734 /* Everything after "TRIGGER" is echoed verbatim */
2735 tgdef = PQgetvalue(result, i, 1);
2736 usingpos = strstr(tgdef, " TRIGGER ");
2738 tgdef = usingpos + 9;
2740 printfPQExpBuffer(&buf, " %s", tgdef);
2741 printTableAddFooter(&cont, buf.data);
2749 * Finish printing the footer information about a table.
2751 if (tableinfo.relkind == RELKIND_RELATION ||
2752 tableinfo.relkind == RELKIND_MATVIEW ||
2753 tableinfo.relkind == RELKIND_FOREIGN_TABLE ||
2754 tableinfo.relkind == RELKIND_PARTITIONED_TABLE)
2759 /* print foreign server name */
2760 if (tableinfo.relkind == RELKIND_FOREIGN_TABLE)
2764 /* Footer information about foreign table */
2765 printfPQExpBuffer(&buf,
2766 "SELECT s.srvname,\n"
2767 " array_to_string(ARRAY(SELECT "
2768 " quote_ident(option_name) || ' ' || "
2769 " quote_literal(option_value) FROM "
2770 " pg_options_to_table(ftoptions)), ', ') "
2771 "FROM pg_catalog.pg_foreign_table f,\n"
2772 " pg_catalog.pg_foreign_server s\n"
2773 "WHERE f.ftrelid = %s AND s.oid = f.ftserver;",
2775 result = PSQLexec(buf.data);
2778 else if (PQntuples(result) != 1)
2784 /* Print server name */
2785 printfPQExpBuffer(&buf, _("Server: %s"),
2786 PQgetvalue(result, 0, 0));
2787 printTableAddFooter(&cont, buf.data);
2789 /* Print per-table FDW options, if any */
2790 ftoptions = PQgetvalue(result, 0, 1);
2791 if (ftoptions && ftoptions[0] != '\0')
2793 printfPQExpBuffer(&buf, _("FDW options: (%s)"), ftoptions);
2794 printTableAddFooter(&cont, buf.data);
2799 /* print inherited tables (exclude, if parent is a partitioned table) */
2800 printfPQExpBuffer(&buf,
2801 "SELECT c.oid::pg_catalog.regclass"
2802 " FROM pg_catalog.pg_class c, pg_catalog.pg_inherits i"
2803 " WHERE c.oid=i.inhparent AND i.inhrelid = '%s'"
2804 " AND c.relkind != " CppAsString2(RELKIND_PARTITIONED_TABLE)
2805 " ORDER BY inhseqno;", oid);
2807 result = PSQLexec(buf.data);
2812 const char *s = _("Inherits");
2813 int sw = pg_wcswidth(s, strlen(s), pset.encoding);
2815 tuples = PQntuples(result);
2817 for (i = 0; i < tuples; i++)
2820 printfPQExpBuffer(&buf, "%s: %s",
2821 s, PQgetvalue(result, i, 0));
2823 printfPQExpBuffer(&buf, "%*s %s",
2824 sw, "", PQgetvalue(result, i, 0));
2826 appendPQExpBufferChar(&buf, ',');
2828 printTableAddFooter(&cont, buf.data);
2834 /* print child tables (with additional info if partitions) */
2835 if (pset.sversion >= 100000)
2836 printfPQExpBuffer(&buf,
2837 "SELECT c.oid::pg_catalog.regclass, pg_get_expr(c.relpartbound, c.oid)"
2838 " FROM pg_catalog.pg_class c, pg_catalog.pg_inherits i"
2839 " WHERE c.oid=i.inhrelid AND"
2840 " i.inhparent = '%s' AND"
2841 " EXISTS (SELECT 1 FROM pg_class c WHERE c.oid = '%s')"
2842 " ORDER BY c.oid::pg_catalog.regclass::pg_catalog.text;", oid, oid);
2843 else if (pset.sversion >= 80300)
2844 printfPQExpBuffer(&buf,
2845 "SELECT c.oid::pg_catalog.regclass"
2846 " FROM pg_catalog.pg_class c, pg_catalog.pg_inherits i"
2847 " WHERE c.oid=i.inhrelid AND"
2848 " i.inhparent = '%s' AND"
2849 " EXISTS (SELECT 1 FROM pg_class c WHERE c.oid = '%s')"
2850 " ORDER BY c.oid::pg_catalog.regclass::pg_catalog.text;", oid, oid);
2852 printfPQExpBuffer(&buf, "SELECT c.oid::pg_catalog.regclass FROM pg_catalog.pg_class c, pg_catalog.pg_inherits i WHERE c.oid=i.inhrelid AND i.inhparent = '%s' ORDER BY c.relname;", oid);
2854 result = PSQLexec(buf.data);
2858 tuples = PQntuples(result);
2862 /* print the number of child tables, if any */
2865 if (tableinfo.relkind != RELKIND_PARTITIONED_TABLE)
2866 printfPQExpBuffer(&buf, _("Number of child tables: %d (Use \\d+ to list them.)"), tuples);
2868 printfPQExpBuffer(&buf, _("Number of partitions: %d (Use \\d+ to list them.)"), tuples);
2869 printTableAddFooter(&cont, buf.data);
2874 /* display the list of child tables */
2875 const char *ct = (tableinfo.relkind != RELKIND_PARTITIONED_TABLE) ?
2876 _("Child tables") : _("Partitions");
2877 int ctw = pg_wcswidth(ct, strlen(ct), pset.encoding);
2879 for (i = 0; i < tuples; i++)
2881 if (tableinfo.relkind != RELKIND_PARTITIONED_TABLE)
2884 printfPQExpBuffer(&buf, "%s: %s",
2885 ct, PQgetvalue(result, i, 0));
2887 printfPQExpBuffer(&buf, "%*s %s",
2888 ctw, "", PQgetvalue(result, i, 0));
2893 printfPQExpBuffer(&buf, "%s: %s %s",
2894 ct, PQgetvalue(result, i, 0), PQgetvalue(result, i, 1));
2896 printfPQExpBuffer(&buf, "%*s %s %s",
2897 ctw, "", PQgetvalue(result, i, 0), PQgetvalue(result, i, 1));
2900 appendPQExpBufferChar(&buf, ',');
2902 printTableAddFooter(&cont, buf.data);
2908 if (tableinfo.reloftype)
2910 printfPQExpBuffer(&buf, _("Typed table of type: %s"), tableinfo.reloftype);
2911 printTableAddFooter(&cont, buf.data);
2915 (tableinfo.relkind == RELKIND_RELATION ||
2916 tableinfo.relkind == RELKIND_MATVIEW) &&
2919 * No need to display default values; we already display a REPLICA
2920 * IDENTITY marker on indexes.
2922 tableinfo.relreplident != 'i' &&
2923 ((strcmp(schemaname, "pg_catalog") != 0 && tableinfo.relreplident != 'd') ||
2924 (strcmp(schemaname, "pg_catalog") == 0 && tableinfo.relreplident != 'n')))
2926 const char *s = _("Replica Identity");
2928 printfPQExpBuffer(&buf, "%s: %s",
2930 tableinfo.relreplident == 'f' ? "FULL" :
2931 tableinfo.relreplident == 'n' ? "NOTHING" :
2934 printTableAddFooter(&cont, buf.data);
2937 /* OIDs, if verbose and not a materialized view */
2938 if (verbose && tableinfo.relkind != RELKIND_MATVIEW && tableinfo.hasoids)
2939 printTableAddFooter(&cont, _("Has OIDs: yes"));
2941 /* Tablespace info */
2942 add_tablespace_footer(&cont, tableinfo.relkind, tableinfo.tablespace,
2946 /* reloptions, if verbose */
2948 tableinfo.reloptions && tableinfo.reloptions[0] != '\0')
2950 const char *t = _("Options");
2952 printfPQExpBuffer(&buf, "%s: %s", t, tableinfo.reloptions);
2953 printTableAddFooter(&cont, buf.data);
2956 printTable(&cont, pset.queryFout, false, pset.logfile);
2963 if (printTableInitialized)
2964 printTableCleanup(&cont);
2965 termPQExpBuffer(&buf);
2966 termPQExpBuffer(&title);
2967 termPQExpBuffer(&tmpbuf);
2971 for (ptr = seq_values; *ptr; ptr++)
2986 * Add a tablespace description to a footer. If 'newline' is true, it is added
2987 * in a new line; otherwise it's appended to the current value of the last
2991 add_tablespace_footer(printTableContent *const cont, char relkind,
2992 Oid tablespace, const bool newline)
2994 /* relkinds for which we support tablespaces */
2995 if (relkind == RELKIND_RELATION ||
2996 relkind == RELKIND_MATVIEW ||
2997 relkind == RELKIND_INDEX ||
2998 relkind == RELKIND_PARTITIONED_TABLE)
3001 * We ignore the database default tablespace so that users not using
3002 * tablespaces don't need to know about them. This case also covers
3003 * pre-8.0 servers, for which tablespace will always be 0.
3005 if (tablespace != 0)
3007 PGresult *result = NULL;
3008 PQExpBufferData buf;
3010 initPQExpBuffer(&buf);
3011 printfPQExpBuffer(&buf,
3012 "SELECT spcname FROM pg_catalog.pg_tablespace\n"
3013 "WHERE oid = '%u';", tablespace);
3014 result = PSQLexec(buf.data);
3017 /* Should always be the case, but.... */
3018 if (PQntuples(result) > 0)
3022 /* Add the tablespace as a new footer */
3023 printfPQExpBuffer(&buf, _("Tablespace: \"%s\""),
3024 PQgetvalue(result, 0, 0));
3025 printTableAddFooter(cont, buf.data);
3029 /* Append the tablespace to the latest footer */
3030 printfPQExpBuffer(&buf, "%s", cont->footer->data);
3033 translator: before this string there's an index description like
3034 '"foo_pkey" PRIMARY KEY, btree (a)' */
3035 appendPQExpBuffer(&buf, _(", tablespace \"%s\""),
3036 PQgetvalue(result, 0, 0));
3037 printTableSetFooter(cont, buf.data);
3041 termPQExpBuffer(&buf);
3049 * Describes roles. Any schema portion of the pattern is ignored.
3052 describeRoles(const char *pattern, bool verbose, bool showSystem)
3054 PQExpBufferData buf;
3056 printTableContent cont;
3057 printTableOpt myopt = pset.popt.topt;
3062 const char align = 'l';
3065 myopt.default_footer = false;
3067 initPQExpBuffer(&buf);
3069 if (pset.sversion >= 80100)
3071 printfPQExpBuffer(&buf,
3072 "SELECT r.rolname, r.rolsuper, r.rolinherit,\n"
3073 " r.rolcreaterole, r.rolcreatedb, r.rolcanlogin,\n"
3074 " r.rolconnlimit, r.rolvaliduntil,\n"
3075 " ARRAY(SELECT b.rolname\n"
3076 " FROM pg_catalog.pg_auth_members m\n"
3077 " JOIN pg_catalog.pg_roles b ON (m.roleid = b.oid)\n"
3078 " WHERE m.member = r.oid) as memberof");
3080 if (verbose && pset.sversion >= 80200)
3082 appendPQExpBufferStr(&buf, "\n, pg_catalog.shobj_description(r.oid, 'pg_authid') AS description");
3085 if (pset.sversion >= 90100)
3087 appendPQExpBufferStr(&buf, "\n, r.rolreplication");
3090 if (pset.sversion >= 90500)
3092 appendPQExpBufferStr(&buf, "\n, r.rolbypassrls");
3095 appendPQExpBufferStr(&buf, "\nFROM pg_catalog.pg_roles r\n");
3097 if (!showSystem && !pattern)
3098 appendPQExpBufferStr(&buf, "WHERE r.rolname !~ '^pg_'\n");
3100 processSQLNamePattern(pset.db, &buf, pattern, false, false,
3101 NULL, "r.rolname", NULL, NULL);
3105 printfPQExpBuffer(&buf,
3106 "SELECT u.usename AS rolname,\n"
3107 " u.usesuper AS rolsuper,\n"
3108 " true AS rolinherit, false AS rolcreaterole,\n"
3109 " u.usecreatedb AS rolcreatedb, true AS rolcanlogin,\n"
3110 " -1 AS rolconnlimit,"
3111 " u.valuntil as rolvaliduntil,\n"
3112 " ARRAY(SELECT g.groname FROM pg_catalog.pg_group g WHERE u.usesysid = ANY(g.grolist)) as memberof"
3113 "\nFROM pg_catalog.pg_user u\n");
3115 processSQLNamePattern(pset.db, &buf, pattern, false, false,
3116 NULL, "u.usename", NULL, NULL);
3119 appendPQExpBufferStr(&buf, "ORDER BY 1;");
3121 res = PSQLexec(buf.data);
3125 nrows = PQntuples(res);
3126 attr = pg_malloc0((nrows + 1) * sizeof(*attr));
3128 printTableInit(&cont, &myopt, _("List of roles"), ncols, nrows);
3130 printTableAddHeader(&cont, gettext_noop("Role name"), true, align);
3131 printTableAddHeader(&cont, gettext_noop("Attributes"), true, align);
3132 printTableAddHeader(&cont, gettext_noop("Member of"), true, align);
3134 if (verbose && pset.sversion >= 80200)
3135 printTableAddHeader(&cont, gettext_noop("Description"), true, align);
3137 for (i = 0; i < nrows; i++)
3139 printTableAddCell(&cont, PQgetvalue(res, i, 0), false, false);
3141 resetPQExpBuffer(&buf);
3142 if (strcmp(PQgetvalue(res, i, 1), "t") == 0)
3143 add_role_attribute(&buf, _("Superuser"));
3145 if (strcmp(PQgetvalue(res, i, 2), "t") != 0)
3146 add_role_attribute(&buf, _("No inheritance"));
3148 if (strcmp(PQgetvalue(res, i, 3), "t") == 0)
3149 add_role_attribute(&buf, _("Create role"));
3151 if (strcmp(PQgetvalue(res, i, 4), "t") == 0)
3152 add_role_attribute(&buf, _("Create DB"));
3154 if (strcmp(PQgetvalue(res, i, 5), "t") != 0)
3155 add_role_attribute(&buf, _("Cannot login"));
3157 if (pset.sversion >= 90100)
3158 if (strcmp(PQgetvalue(res, i, (verbose ? 10 : 9)), "t") == 0)
3159 add_role_attribute(&buf, _("Replication"));
3161 if (pset.sversion >= 90500)
3162 if (strcmp(PQgetvalue(res, i, (verbose ? 11 : 10)), "t") == 0)
3163 add_role_attribute(&buf, _("Bypass RLS"));
3165 conns = atoi(PQgetvalue(res, i, 6));
3169 appendPQExpBufferChar(&buf, '\n');
3172 appendPQExpBufferStr(&buf, _("No connections"));
3174 appendPQExpBuffer(&buf, ngettext("%d connection",
3180 if (strcmp(PQgetvalue(res, i, 7), "") != 0)
3183 appendPQExpBufferStr(&buf, "\n");
3184 appendPQExpBufferStr(&buf, _("Password valid until "));
3185 appendPQExpBufferStr(&buf, PQgetvalue(res, i, 7));
3188 attr[i] = pg_strdup(buf.data);
3190 printTableAddCell(&cont, attr[i], false, false);
3192 printTableAddCell(&cont, PQgetvalue(res, i, 8), false, false);
3194 if (verbose && pset.sversion >= 80200)
3195 printTableAddCell(&cont, PQgetvalue(res, i, 9), false, false);
3197 termPQExpBuffer(&buf);
3199 printTable(&cont, pset.queryFout, false, pset.logfile);
3200 printTableCleanup(&cont);
3202 for (i = 0; i < nrows; i++)
3211 add_role_attribute(PQExpBuffer buf, const char *const str)
3214 appendPQExpBufferStr(buf, ", ");
3216 appendPQExpBufferStr(buf, str);
3223 listDbRoleSettings(const char *pattern, const char *pattern2)
3225 PQExpBufferData buf;
3227 printQueryOpt myopt = pset.popt;
3229 initPQExpBuffer(&buf);
3231 if (pset.sversion >= 90000)
3235 printfPQExpBuffer(&buf, "SELECT rolname AS \"%s\", datname AS \"%s\",\n"
3236 "pg_catalog.array_to_string(setconfig, E'\\n') AS \"%s\"\n"
3237 "FROM pg_db_role_setting AS s\n"
3238 "LEFT JOIN pg_database ON pg_database.oid = setdatabase\n"
3239 "LEFT JOIN pg_roles ON pg_roles.oid = setrole\n",
3240 gettext_noop("Role"),
3241 gettext_noop("Database"),
3242 gettext_noop("Settings"));
3243 havewhere = processSQLNamePattern(pset.db, &buf, pattern, false, false,
3244 NULL, "pg_roles.rolname", NULL, NULL);
3245 processSQLNamePattern(pset.db, &buf, pattern2, havewhere, false,
3246 NULL, "pg_database.datname", NULL, NULL);
3247 appendPQExpBufferStr(&buf, "ORDER BY 1, 2;");
3251 fprintf(pset.queryFout,
3252 _("No per-database role settings support in this server version.\n"));
3256 res = PSQLexec(buf.data);
3260 if (PQntuples(res) == 0 && !pset.quiet)
3263 fprintf(pset.queryFout, _("No matching settings found.\n"));
3265 fprintf(pset.queryFout, _("No settings found.\n"));
3269 myopt.nullPrint = NULL;
3270 myopt.title = _("List of settings");
3271 myopt.translate_header = true;
3273 printQuery(res, &myopt, pset.queryFout, false, pset.logfile);
3277 resetPQExpBuffer(&buf);
3285 * handler for \dt, \di, etc.
3287 * tabtypes is an array of characters, specifying what info is desired:
3291 * m - materialized views
3293 * E - foreign table (Note: different from 'f', the relkind value)
3294 * (any order of the above is fine)
3297 listTables(const char *tabtypes, const char *pattern, bool verbose, bool showSystem)
3299 bool showTables = strchr(tabtypes, 't') != NULL;
3300 bool showIndexes = strchr(tabtypes, 'i') != NULL;
3301 bool showViews = strchr(tabtypes, 'v') != NULL;
3302 bool showMatViews = strchr(tabtypes, 'm') != NULL;
3303 bool showSeq = strchr(tabtypes, 's') != NULL;
3304 bool showForeign = strchr(tabtypes, 'E') != NULL;
3306 PQExpBufferData buf;
3308 printQueryOpt myopt = pset.popt;
3309 static const bool translate_columns[] = {false, false, true, false, false, false, false};
3311 /* If tabtypes is empty, we default to \dtvmsE (but see also command.c) */
3312 if (!(showTables || showIndexes || showViews || showMatViews || showSeq || showForeign))
3313 showTables = showViews = showMatViews = showSeq = showForeign = true;
3315 initPQExpBuffer(&buf);
3318 * Note: as of Pg 8.2, we no longer use relkind 's' (special), but we keep
3319 * it here for backwards compatibility.
3321 printfPQExpBuffer(&buf,
3322 "SELECT n.nspname as \"%s\",\n"
3323 " c.relname as \"%s\",\n"
3325 " WHEN " CppAsString2(RELKIND_RELATION) " THEN '%s'"
3326 " WHEN " CppAsString2(RELKIND_VIEW) " THEN '%s'"
3327 " WHEN " CppAsString2(RELKIND_MATVIEW) " THEN '%s'"
3328 " WHEN " CppAsString2(RELKIND_INDEX) " THEN '%s'"
3329 " WHEN " CppAsString2(RELKIND_SEQUENCE) " THEN '%s'"
3330 " WHEN 's' THEN '%s'"
3331 " WHEN " CppAsString2(RELKIND_FOREIGN_TABLE) " THEN '%s'"
3332 " WHEN " CppAsString2(RELKIND_PARTITIONED_TABLE) " THEN '%s'"
3334 " pg_catalog.pg_get_userbyid(c.relowner) as \"%s\"",
3335 gettext_noop("Schema"),
3336 gettext_noop("Name"),
3337 gettext_noop("table"),
3338 gettext_noop("view"),
3339 gettext_noop("materialized view"),
3340 gettext_noop("index"),
3341 gettext_noop("sequence"),
3342 gettext_noop("special"),
3343 gettext_noop("foreign table"),
3344 gettext_noop("table"), /* partitioned table */
3345 gettext_noop("Type"),
3346 gettext_noop("Owner"));
3349 appendPQExpBuffer(&buf,
3350 ",\n c2.relname as \"%s\"",
3351 gettext_noop("Table"));
3356 * As of PostgreSQL 9.0, use pg_table_size() to show a more accurate
3357 * size of a table, including FSM, VM and TOAST tables.
3359 if (pset.sversion >= 90000)
3360 appendPQExpBuffer(&buf,
3361 ",\n pg_catalog.pg_size_pretty(pg_catalog.pg_table_size(c.oid)) as \"%s\"",
3362 gettext_noop("Size"));
3363 else if (pset.sversion >= 80100)
3364 appendPQExpBuffer(&buf,
3365 ",\n pg_catalog.pg_size_pretty(pg_catalog.pg_relation_size(c.oid)) as \"%s\"",
3366 gettext_noop("Size"));
3368 appendPQExpBuffer(&buf,
3369 ",\n pg_catalog.obj_description(c.oid, 'pg_class') as \"%s\"",
3370 gettext_noop("Description"));
3373 appendPQExpBufferStr(&buf,
3374 "\nFROM pg_catalog.pg_class c"
3375 "\n LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace");
3377 appendPQExpBufferStr(&buf,
3378 "\n LEFT JOIN pg_catalog.pg_index i ON i.indexrelid = c.oid"
3379 "\n LEFT JOIN pg_catalog.pg_class c2 ON i.indrelid = c2.oid");
3381 appendPQExpBufferStr(&buf, "\nWHERE c.relkind IN (");
3383 appendPQExpBufferStr(&buf, CppAsString2(RELKIND_RELATION) ","
3384 CppAsString2(RELKIND_PARTITIONED_TABLE) ",");
3386 appendPQExpBufferStr(&buf, CppAsString2(RELKIND_VIEW) ",");
3388 appendPQExpBufferStr(&buf, CppAsString2(RELKIND_MATVIEW) ",");
3390 appendPQExpBufferStr(&buf, CppAsString2(RELKIND_INDEX) ",");
3392 appendPQExpBufferStr(&buf, CppAsString2(RELKIND_SEQUENCE) ",");
3393 if (showSystem || pattern)
3394 appendPQExpBufferStr(&buf, "'s',"); /* was RELKIND_SPECIAL */
3396 appendPQExpBufferStr(&buf, CppAsString2(RELKIND_FOREIGN_TABLE) ",");
3398 appendPQExpBufferStr(&buf, "''"); /* dummy */
3399 appendPQExpBufferStr(&buf, ")\n");
3401 if (!showSystem && !pattern)
3402 appendPQExpBufferStr(&buf, " AND n.nspname <> 'pg_catalog'\n"
3403 " AND n.nspname <> 'information_schema'\n");
3406 * TOAST objects are suppressed unconditionally. Since we don't provide
3407 * any way to select RELKIND_TOASTVALUE above, we would never show toast
3408 * tables in any case; it seems a bit confusing to allow their indexes to
3409 * be shown. Use plain \d if you really need to look at a TOAST
3412 appendPQExpBufferStr(&buf, " AND n.nspname !~ '^pg_toast'\n");
3414 processSQLNamePattern(pset.db, &buf, pattern, true, false,
3415 "n.nspname", "c.relname", NULL,
3416 "pg_catalog.pg_table_is_visible(c.oid)");
3418 appendPQExpBufferStr(&buf, "ORDER BY 1,2;");
3420 res = PSQLexec(buf.data);
3421 termPQExpBuffer(&buf);
3425 if (PQntuples(res) == 0 && !pset.quiet)
3428 fprintf(pset.queryFout, _("No matching relations found.\n"));
3430 fprintf(pset.queryFout, _("No relations found.\n"));
3434 myopt.nullPrint = NULL;
3435 myopt.title = _("List of relations");
3436 myopt.translate_header = true;
3437 myopt.translate_columns = translate_columns;
3438 myopt.n_translate_columns = lengthof(translate_columns);
3440 printQuery(res, &myopt, pset.queryFout, false, pset.logfile);
3451 * Describes languages.
3454 listLanguages(const char *pattern, bool verbose, bool showSystem)
3456 PQExpBufferData buf;
3458 printQueryOpt myopt = pset.popt;
3460 initPQExpBuffer(&buf);
3462 printfPQExpBuffer(&buf,
3463 "SELECT l.lanname AS \"%s\",\n",
3464 gettext_noop("Name"));
3465 if (pset.sversion >= 80300)
3466 appendPQExpBuffer(&buf,
3467 " pg_catalog.pg_get_userbyid(l.lanowner) as \"%s\",\n",
3468 gettext_noop("Owner"));
3470 appendPQExpBuffer(&buf,
3471 " l.lanpltrusted AS \"%s\"",
3472 gettext_noop("Trusted"));
3476 appendPQExpBuffer(&buf,
3477 ",\n NOT l.lanispl AS \"%s\",\n"
3478 " l.lanplcallfoid::regprocedure AS \"%s\",\n"
3479 " l.lanvalidator::regprocedure AS \"%s\",\n ",
3480 gettext_noop("Internal language"),
3481 gettext_noop("Call handler"),
3482 gettext_noop("Validator"));
3483 if (pset.sversion >= 90000)
3484 appendPQExpBuffer(&buf, "l.laninline::regprocedure AS \"%s\",\n ",
3485 gettext_noop("Inline handler"));
3486 printACLColumn(&buf, "l.lanacl");
3489 appendPQExpBuffer(&buf,
3490 ",\n d.description AS \"%s\""
3491 "\nFROM pg_catalog.pg_language l\n"
3492 "LEFT JOIN pg_catalog.pg_description d\n"
3493 " ON d.classoid = l.tableoid AND d.objoid = l.oid\n"
3494 " AND d.objsubid = 0\n",
3495 gettext_noop("Description"));
3498 processSQLNamePattern(pset.db, &buf, pattern, false, false,
3499 NULL, "l.lanname", NULL, NULL);
3501 if (!showSystem && !pattern)
3502 appendPQExpBufferStr(&buf, "WHERE l.lanplcallfoid != 0\n");
3505 appendPQExpBufferStr(&buf, "ORDER BY 1;");
3507 res = PSQLexec(buf.data);
3508 termPQExpBuffer(&buf);
3512 myopt.nullPrint = NULL;
3513 myopt.title = _("List of languages");
3514 myopt.translate_header = true;
3516 printQuery(res, &myopt, pset.queryFout, false, pset.logfile);
3526 * Describes domains.
3529 listDomains(const char *pattern, bool verbose, bool showSystem)
3531 PQExpBufferData buf;
3533 printQueryOpt myopt = pset.popt;
3535 initPQExpBuffer(&buf);
3537 printfPQExpBuffer(&buf,
3538 "SELECT n.nspname as \"%s\",\n"
3539 " t.typname as \"%s\",\n"
3540 " pg_catalog.format_type(t.typbasetype, t.typtypmod) as \"%s\",\n",
3541 gettext_noop("Schema"),
3542 gettext_noop("Name"),
3543 gettext_noop("Type"));
3545 if (pset.sversion >= 90100)
3546 appendPQExpBuffer(&buf,
3547 " (SELECT c.collname FROM pg_catalog.pg_collation c, pg_catalog.pg_type bt\n"
3548 " WHERE c.oid = t.typcollation AND bt.oid = t.typbasetype AND t.typcollation <> bt.typcollation) as \"%s\",\n",
3549 gettext_noop("Collation"));
3550 appendPQExpBuffer(&buf,
3551 " CASE WHEN t.typnotnull THEN 'not null' END as \"%s\",\n"
3552 " t.typdefault as \"%s\",\n"
3553 " pg_catalog.array_to_string(ARRAY(\n"
3554 " SELECT pg_catalog.pg_get_constraintdef(r.oid, true) FROM pg_catalog.pg_constraint r WHERE t.oid = r.contypid\n"
3555 " ), ' ') as \"%s\"",
3556 gettext_noop("Nullable"),
3557 gettext_noop("Default"),
3558 gettext_noop("Check"));
3562 if (pset.sversion >= 90200)
3564 appendPQExpBufferStr(&buf, ",\n ");
3565 printACLColumn(&buf, "t.typacl");
3567 appendPQExpBuffer(&buf,
3568 ",\n d.description as \"%s\"",
3569 gettext_noop("Description"));
3572 appendPQExpBufferStr(&buf,
3573 "\nFROM pg_catalog.pg_type t\n"
3574 " LEFT JOIN pg_catalog.pg_namespace n ON n.oid = t.typnamespace\n");
3577 appendPQExpBufferStr(&buf,
3578 " LEFT JOIN pg_catalog.pg_description d "
3579 "ON d.classoid = t.tableoid AND d.objoid = t.oid "
3580 "AND d.objsubid = 0\n");
3582 appendPQExpBufferStr(&buf, "WHERE t.typtype = 'd'\n");
3584 if (!showSystem && !pattern)
3585 appendPQExpBufferStr(&buf, " AND n.nspname <> 'pg_catalog'\n"
3586 " AND n.nspname <> 'information_schema'\n");
3588 processSQLNamePattern(pset.db, &buf, pattern, true, false,
3589 "n.nspname", "t.typname", NULL,
3590 "pg_catalog.pg_type_is_visible(t.oid)");
3592 appendPQExpBufferStr(&buf, "ORDER BY 1, 2;");
3594 res = PSQLexec(buf.data);
3595 termPQExpBuffer(&buf);
3599 myopt.nullPrint = NULL;
3600 myopt.title = _("List of domains");
3601 myopt.translate_header = true;
3603 printQuery(res, &myopt, pset.queryFout, false, pset.logfile);
3612 * Describes conversions.
3615 listConversions(const char *pattern, bool verbose, bool showSystem)
3617 PQExpBufferData buf;
3619 printQueryOpt myopt = pset.popt;
3620 static const bool translate_columns[] =
3621 {false, false, false, false, true, false};
3623 initPQExpBuffer(&buf);
3625 printfPQExpBuffer(&buf,
3626 "SELECT n.nspname AS \"%s\",\n"
3627 " c.conname AS \"%s\",\n"
3628 " pg_catalog.pg_encoding_to_char(c.conforencoding) AS \"%s\",\n"
3629 " pg_catalog.pg_encoding_to_char(c.contoencoding) AS \"%s\",\n"
3630 " CASE WHEN c.condefault THEN '%s'\n"
3631 " ELSE '%s' END AS \"%s\"",
3632 gettext_noop("Schema"),
3633 gettext_noop("Name"),
3634 gettext_noop("Source"),
3635 gettext_noop("Destination"),
3636 gettext_noop("yes"), gettext_noop("no"),
3637 gettext_noop("Default?"));
3640 appendPQExpBuffer(&buf,
3641 ",\n d.description AS \"%s\"",
3642 gettext_noop("Description"));
3644 appendPQExpBufferStr(&buf,
3645 "\nFROM pg_catalog.pg_conversion c\n"
3646 " JOIN pg_catalog.pg_namespace n "
3647 "ON n.oid = c.connamespace\n");
3650 appendPQExpBufferStr(&buf,
3651 "LEFT JOIN pg_catalog.pg_description d "
3652 "ON d.classoid = c.tableoid\n"
3653 " AND d.objoid = c.oid "
3654 "AND d.objsubid = 0\n");
3656 appendPQExpBufferStr(&buf, "WHERE true\n");
3658 if (!showSystem && !pattern)
3659 appendPQExpBufferStr(&buf, " AND n.nspname <> 'pg_catalog'\n"
3660 " AND n.nspname <> 'information_schema'\n");
3662 processSQLNamePattern(pset.db, &buf, pattern, true, false,
3663 "n.nspname", "c.conname", NULL,
3664 "pg_catalog.pg_conversion_is_visible(c.oid)");
3666 appendPQExpBufferStr(&buf, "ORDER BY 1, 2;");
3668 res = PSQLexec(buf.data);
3669 termPQExpBuffer(&buf);
3673 myopt.nullPrint = NULL;
3674 myopt.title = _("List of conversions");
3675 myopt.translate_header = true;
3676 myopt.translate_columns = translate_columns;
3677 myopt.n_translate_columns = lengthof(translate_columns);
3679 printQuery(res, &myopt, pset.queryFout, false, pset.logfile);
3688 * Describes Event Triggers.
3691 listEventTriggers(const char *pattern, bool verbose)
3693 PQExpBufferData buf;
3695 printQueryOpt myopt = pset.popt;
3696 static const bool translate_columns[] =
3697 {false, false, false, true, false, false, false};
3699 initPQExpBuffer(&buf);
3701 printfPQExpBuffer(&buf,
3702 "SELECT evtname as \"%s\", "
3703 "evtevent as \"%s\", "
3704 "pg_catalog.pg_get_userbyid(e.evtowner) as \"%s\",\n"
3705 " case evtenabled when 'O' then '%s'"
3706 " when 'R' then '%s'"
3707 " when 'A' then '%s'"
3708 " when 'D' then '%s' end as \"%s\",\n"
3709 " e.evtfoid::pg_catalog.regproc as \"%s\", "
3710 "pg_catalog.array_to_string(array(select x"
3711 " from pg_catalog.unnest(evttags) as t(x)), ', ') as \"%s\"",
3712 gettext_noop("Name"),
3713 gettext_noop("Event"),
3714 gettext_noop("Owner"),
3715 gettext_noop("enabled"),
3716 gettext_noop("replica"),
3717 gettext_noop("always"),
3718 gettext_noop("disabled"),
3719 gettext_noop("Enabled"),
3720 gettext_noop("Procedure"),
3721 gettext_noop("Tags"));
3723 appendPQExpBuffer(&buf,
3724 ",\npg_catalog.obj_description(e.oid, 'pg_event_trigger') as \"%s\"",
3725 gettext_noop("Description"));
3726 appendPQExpBufferStr(&buf,
3727 "\nFROM pg_catalog.pg_event_trigger e ");
3729 processSQLNamePattern(pset.db, &buf, pattern, false, false,
3730 NULL, "evtname", NULL, NULL);
3732 appendPQExpBufferStr(&buf, "ORDER BY 1");
3734 res = PSQLexec(buf.data);
3735 termPQExpBuffer(&buf);
3739 myopt.nullPrint = NULL;
3740 myopt.title = _("List of event triggers");
3741 myopt.translate_header = true;
3742 myopt.translate_columns = translate_columns;
3743 myopt.n_translate_columns = lengthof(translate_columns);
3745 printQuery(res, &myopt, pset.queryFout, false, pset.logfile);
3757 listCasts(const char *pattern, bool verbose)
3759 PQExpBufferData buf;
3761 printQueryOpt myopt = pset.popt;
3762 static const bool translate_columns[] = {false, false, false, true, false};
3764 initPQExpBuffer(&buf);
3767 * We need a left join to pg_proc for binary casts; the others are just
3768 * paranoia. Also note that we don't attempt to localize '(binary
3769 * coercible)', because there's too much risk of gettext translating a
3770 * function name that happens to match some string in the PO database.
3772 printfPQExpBuffer(&buf,
3773 "SELECT pg_catalog.format_type(castsource, NULL) AS \"%s\",\n"
3774 " pg_catalog.format_type(casttarget, NULL) AS \"%s\",\n"
3775 " CASE WHEN castfunc = 0 THEN '(binary coercible)'\n"
3778 " CASE WHEN c.castcontext = 'e' THEN '%s'\n"
3779 " WHEN c.castcontext = 'a' THEN '%s'\n"
3782 gettext_noop("Source type"),
3783 gettext_noop("Target type"),
3784 gettext_noop("Function"),
3786 gettext_noop("in assignment"),
3787 gettext_noop("yes"),
3788 gettext_noop("Implicit?"));
3791 appendPQExpBuffer(&buf,
3792 ",\n d.description AS \"%s\"\n",
3793 gettext_noop("Description"));
3795 appendPQExpBufferStr(&buf,
3796 "FROM pg_catalog.pg_cast c LEFT JOIN pg_catalog.pg_proc p\n"
3797 " ON c.castfunc = p.oid\n"
3798 " LEFT JOIN pg_catalog.pg_type ts\n"
3799 " ON c.castsource = ts.oid\n"
3800 " LEFT JOIN pg_catalog.pg_namespace ns\n"
3801 " ON ns.oid = ts.typnamespace\n"
3802 " LEFT JOIN pg_catalog.pg_type tt\n"
3803 " ON c.casttarget = tt.oid\n"
3804 " LEFT JOIN pg_catalog.pg_namespace nt\n"
3805 " ON nt.oid = tt.typnamespace\n");
3808 appendPQExpBufferStr(&buf,
3809 " LEFT JOIN pg_catalog.pg_description d\n"
3810 " ON d.classoid = c.tableoid AND d.objoid = "
3811 "c.oid AND d.objsubid = 0\n");
3813 appendPQExpBufferStr(&buf, "WHERE ( (true");
3816 * Match name pattern against either internal or external name of either
3817 * castsource or casttarget
3819 processSQLNamePattern(pset.db, &buf, pattern, true, false,
3820 "ns.nspname", "ts.typname",
3821 "pg_catalog.format_type(ts.oid, NULL)",
3822 "pg_catalog.pg_type_is_visible(ts.oid)");
3824 appendPQExpBufferStr(&buf, ") OR (true");
3826 processSQLNamePattern(pset.db, &buf, pattern, true, false,
3827 "nt.nspname", "tt.typname",
3828 "pg_catalog.format_type(tt.oid, NULL)",
3829 "pg_catalog.pg_type_is_visible(tt.oid)");
3831 appendPQExpBufferStr(&buf, ") )\nORDER BY 1, 2;");
3833 res = PSQLexec(buf.data);
3834 termPQExpBuffer(&buf);
3838 myopt.nullPrint = NULL;
3839 myopt.title = _("List of casts");
3840 myopt.translate_header = true;
3841 myopt.translate_columns = translate_columns;
3842 myopt.n_translate_columns = lengthof(translate_columns);
3844 printQuery(res, &myopt, pset.queryFout, false, pset.logfile);
3853 * Describes collations.
3856 listCollations(const char *pattern, bool verbose, bool showSystem)
3858 PQExpBufferData buf;
3860 printQueryOpt myopt = pset.popt;
3861 static const bool translate_columns[] = {false, false, false, false, false, false};
3863 if (pset.sversion < 90100)
3867 psql_error("The server (version %s) does not support collations.\n",
3868 formatPGVersionNumber(pset.sversion, false,
3869 sverbuf, sizeof(sverbuf)));
3873 initPQExpBuffer(&buf);
3875 printfPQExpBuffer(&buf,
3876 "SELECT n.nspname AS \"%s\",\n"
3877 " c.collname AS \"%s\",\n"
3878 " c.collcollate AS \"%s\",\n"
3879 " c.collctype AS \"%s\"",
3880 gettext_noop("Schema"),
3881 gettext_noop("Name"),
3882 gettext_noop("Collate"),
3883 gettext_noop("Ctype"));
3885 if (pset.sversion >= 100000)
3886 appendPQExpBuffer(&buf,
3887 ",\n CASE c.collprovider WHEN 'd' THEN 'default' WHEN 'c' THEN 'libc' WHEN 'i' THEN 'icu' END AS \"%s\"",
3888 gettext_noop("Provider"));
3891 appendPQExpBuffer(&buf,
3892 ",\n pg_catalog.obj_description(c.oid, 'pg_collation') AS \"%s\"",
3893 gettext_noop("Description"));
3895 appendPQExpBufferStr(&buf,
3896 "\nFROM pg_catalog.pg_collation c, pg_catalog.pg_namespace n\n"
3897 "WHERE n.oid = c.collnamespace\n");
3899 if (!showSystem && !pattern)
3900 appendPQExpBufferStr(&buf, " AND n.nspname <> 'pg_catalog'\n"
3901 " AND n.nspname <> 'information_schema'\n");
3904 * Hide collations that aren't usable in the current database's encoding.
3905 * If you think to change this, note that pg_collation_is_visible rejects
3906 * unusable collations, so you will need to hack name pattern processing
3907 * somehow to avoid inconsistent behavior.
3909 appendPQExpBufferStr(&buf, " AND c.collencoding IN (-1, pg_catalog.pg_char_to_encoding(pg_catalog.getdatabaseencoding()))\n");
3911 processSQLNamePattern(pset.db, &buf, pattern, true, false,
3912 "n.nspname", "c.collname", NULL,
3913 "pg_catalog.pg_collation_is_visible(c.oid)");
3915 appendPQExpBufferStr(&buf, "ORDER BY 1, 2;");
3917 res = PSQLexec(buf.data);
3918 termPQExpBuffer(&buf);
3922 myopt.nullPrint = NULL;
3923 myopt.title = _("List of collations");
3924 myopt.translate_header = true;
3925 myopt.translate_columns = translate_columns;
3926 myopt.n_translate_columns = lengthof(translate_columns);
3928 printQuery(res, &myopt, pset.queryFout, false, pset.logfile);
3937 * Describes schemas (namespaces)
3940 listSchemas(const char *pattern, bool verbose, bool showSystem)
3942 PQExpBufferData buf;
3944 printQueryOpt myopt = pset.popt;
3946 initPQExpBuffer(&buf);
3947 printfPQExpBuffer(&buf,
3948 "SELECT n.nspname AS \"%s\",\n"
3949 " pg_catalog.pg_get_userbyid(n.nspowner) AS \"%s\"",
3950 gettext_noop("Name"),
3951 gettext_noop("Owner"));
3955 appendPQExpBufferStr(&buf, ",\n ");
3956 printACLColumn(&buf, "n.nspacl");
3957 appendPQExpBuffer(&buf,
3958 ",\n pg_catalog.obj_description(n.oid, 'pg_namespace') AS \"%s\"",
3959 gettext_noop("Description"));
3962 appendPQExpBuffer(&buf,
3963 "\nFROM pg_catalog.pg_namespace n\n");
3965 if (!showSystem && !pattern)
3966 appendPQExpBufferStr(&buf,
3967 "WHERE n.nspname !~ '^pg_' AND n.nspname <> 'information_schema'\n");
3969 processSQLNamePattern(pset.db, &buf, pattern,
3970 !showSystem && !pattern, false,
3971 NULL, "n.nspname", NULL,
3974 appendPQExpBufferStr(&buf, "ORDER BY 1;");
3976 res = PSQLexec(buf.data);
3977 termPQExpBuffer(&buf);
3981 myopt.nullPrint = NULL;
3982 myopt.title = _("List of schemas");
3983 myopt.translate_header = true;
3985 printQuery(res, &myopt, pset.queryFout, false, pset.logfile);
3994 * list text search parsers
3997 listTSParsers(const char *pattern, bool verbose)
3999 PQExpBufferData buf;
4001 printQueryOpt myopt = pset.popt;
4003 if (pset.sversion < 80300)
4007 psql_error("The server (version %s) does not support full text search.\n",
4008 formatPGVersionNumber(pset.sversion, false,
4009 sverbuf, sizeof(sverbuf)));
4014 return listTSParsersVerbose(pattern);
4016 initPQExpBuffer(&buf);
4018 printfPQExpBuffer(&buf,
4020 " n.nspname as \"%s\",\n"
4021 " p.prsname as \"%s\",\n"
4022 " pg_catalog.obj_description(p.oid, 'pg_ts_parser') as \"%s\"\n"
4023 "FROM pg_catalog.pg_ts_parser p\n"
4024 "LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.prsnamespace\n",
4025 gettext_noop("Schema"),
4026 gettext_noop("Name"),
4027 gettext_noop("Description")
4030 processSQLNamePattern(pset.db, &buf, pattern, false, false,
4031 "n.nspname", "p.prsname", NULL,
4032 "pg_catalog.pg_ts_parser_is_visible(p.oid)");
4034 appendPQExpBufferStr(&buf, "ORDER BY 1, 2;");
4036 res = PSQLexec(buf.data);
4037 termPQExpBuffer(&buf);
4041 myopt.nullPrint = NULL;
4042 myopt.title = _("List of text search parsers");
4043 myopt.translate_header = true;
4045 printQuery(res, &myopt, pset.queryFout, false, pset.logfile);
4052 * full description of parsers
4055 listTSParsersVerbose(const char *pattern)
4057 PQExpBufferData buf;
4061 initPQExpBuffer(&buf);
4063 printfPQExpBuffer(&buf,
4067 "FROM pg_catalog.pg_ts_parser p\n"
4068 "LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.prsnamespace\n"
4071 processSQLNamePattern(pset.db, &buf, pattern, false, false,
4072 "n.nspname", "p.prsname", NULL,
4073 "pg_catalog.pg_ts_parser_is_visible(p.oid)");
4075 appendPQExpBufferStr(&buf, "ORDER BY 1, 2;");
4077 res = PSQLexec(buf.data);
4078 termPQExpBuffer(&buf);
4082 if (PQntuples(res) == 0)
4085 psql_error("Did not find any text search parser named \"%s\".\n",
4091 for (i = 0; i < PQntuples(res); i++)
4094 const char *nspname = NULL;
4095 const char *prsname;
4097 oid = PQgetvalue(res, i, 0);
4098 if (!PQgetisnull(res, i, 1))
4099 nspname = PQgetvalue(res, i, 1);
4100 prsname = PQgetvalue(res, i, 2);
4102 if (!describeOneTSParser(oid, nspname, prsname))
4120 describeOneTSParser(const char *oid, const char *nspname, const char *prsname)
4122 PQExpBufferData buf;
4125 printQueryOpt myopt = pset.popt;
4126 static const bool translate_columns[] = {true, false, false};
4128 initPQExpBuffer(&buf);
4130 printfPQExpBuffer(&buf,
4131 "SELECT '%s' AS \"%s\",\n"
4132 " p.prsstart::pg_catalog.regproc AS \"%s\",\n"
4133 " pg_catalog.obj_description(p.prsstart, 'pg_proc') as \"%s\"\n"
4134 " FROM pg_catalog.pg_ts_parser p\n"
4135 " WHERE p.oid = '%s'\n"
4138 " p.prstoken::pg_catalog.regproc,\n"
4139 " pg_catalog.obj_description(p.prstoken, 'pg_proc')\n"
4140 " FROM pg_catalog.pg_ts_parser p\n"
4141 " WHERE p.oid = '%s'\n"
4144 " p.prsend::pg_catalog.regproc,\n"
4145 " pg_catalog.obj_description(p.prsend, 'pg_proc')\n"
4146 " FROM pg_catalog.pg_ts_parser p\n"
4147 " WHERE p.oid = '%s'\n"
4150 " p.prsheadline::pg_catalog.regproc,\n"
4151 " pg_catalog.obj_description(p.prsheadline, 'pg_proc')\n"
4152 " FROM pg_catalog.pg_ts_parser p\n"
4153 " WHERE p.oid = '%s'\n"
4156 " p.prslextype::pg_catalog.regproc,\n"
4157 " pg_catalog.obj_description(p.prslextype, 'pg_proc')\n"
4158 " FROM pg_catalog.pg_ts_parser p\n"
4159 " WHERE p.oid = '%s';",
4160 gettext_noop("Start parse"),
4161 gettext_noop("Method"),
4162 gettext_noop("Function"),
4163 gettext_noop("Description"),
4165 gettext_noop("Get next token"),
4167 gettext_noop("End parse"),
4169 gettext_noop("Get headline"),
4171 gettext_noop("Get token types"),
4174 res = PSQLexec(buf.data);
4175 termPQExpBuffer(&buf);
4179 myopt.nullPrint = NULL;
4181 sprintf(title, _("Text search parser \"%s.%s\""), nspname, prsname);
4183 sprintf(title, _("Text search parser \"%s\""), prsname);
4184 myopt.title = title;
4185 myopt.footers = NULL;
4186 myopt.topt.default_footer = false;
4187 myopt.translate_header = true;
4188 myopt.translate_columns = translate_columns;
4189 myopt.n_translate_columns = lengthof(translate_columns);
4191 printQuery(res, &myopt, pset.queryFout, false, pset.logfile);
4195 initPQExpBuffer(&buf);
4197 printfPQExpBuffer(&buf,
4198 "SELECT t.alias as \"%s\",\n"
4199 " t.description as \"%s\"\n"
4200 "FROM pg_catalog.ts_token_type( '%s'::pg_catalog.oid ) as t\n"
4202 gettext_noop("Token name"),
4203 gettext_noop("Description"),
4206 res = PSQLexec(buf.data);
4207 termPQExpBuffer(&buf);
4211 myopt.nullPrint = NULL;
4213 sprintf(title, _("Token types for parser \"%s.%s\""), nspname, prsname);
4215 sprintf(title, _("Token types for parser \"%s\""), prsname);
4216 myopt.title = title;
4217 myopt.footers = NULL;
4218 myopt.topt.default_footer = true;
4219 myopt.translate_header = true;
4220 myopt.translate_columns = NULL;
4221 myopt.n_translate_columns = 0;
4223 printQuery(res, &myopt, pset.queryFout, false, pset.logfile);
4232 * list text search dictionaries
4235 listTSDictionaries(const char *pattern, bool verbose)
4237 PQExpBufferData buf;
4239 printQueryOpt myopt = pset.popt;
4241 if (pset.sversion < 80300)
4245 psql_error("The server (version %s) does not support full text search.\n",
4246 formatPGVersionNumber(pset.sversion, false,
4247 sverbuf, sizeof(sverbuf)));
4251 initPQExpBuffer(&buf);
4253 printfPQExpBuffer(&buf,
4255 " n.nspname as \"%s\",\n"
4256 " d.dictname as \"%s\",\n",
4257 gettext_noop("Schema"),
4258 gettext_noop("Name"));
4262 appendPQExpBuffer(&buf,
4263 " ( SELECT COALESCE(nt.nspname, '(null)')::pg_catalog.text || '.' || t.tmplname FROM\n"
4264 " pg_catalog.pg_ts_template t\n"
4265 " LEFT JOIN pg_catalog.pg_namespace nt ON nt.oid = t.tmplnamespace\n"
4266 " WHERE d.dicttemplate = t.oid ) AS \"%s\",\n"
4267 " d.dictinitoption as \"%s\",\n",
4268 gettext_noop("Template"),
4269 gettext_noop("Init options"));
4272 appendPQExpBuffer(&buf,
4273 " pg_catalog.obj_description(d.oid, 'pg_ts_dict') as \"%s\"\n",
4274 gettext_noop("Description"));
4276 appendPQExpBufferStr(&buf, "FROM pg_catalog.pg_ts_dict d\n"
4277 "LEFT JOIN pg_catalog.pg_namespace n ON n.oid = d.dictnamespace\n");
4279 processSQLNamePattern(pset.db, &buf, pattern, false, false,
4280 "n.nspname", "d.dictname", NULL,
4281 "pg_catalog.pg_ts_dict_is_visible(d.oid)");
4283 appendPQExpBufferStr(&buf, "ORDER BY 1, 2;");
4285 res = PSQLexec(buf.data);
4286 termPQExpBuffer(&buf);
4290 myopt.nullPrint = NULL;
4291 myopt.title = _("List of text search dictionaries");
4292 myopt.translate_header = true;
4294 printQuery(res, &myopt, pset.queryFout, false, pset.logfile);
4303 * list text search templates
4306 listTSTemplates(const char *pattern, bool verbose)
4308 PQExpBufferData buf;
4310 printQueryOpt myopt = pset.popt;
4312 if (pset.sversion < 80300)
4316 psql_error("The server (version %s) does not support full text search.\n",
4317 formatPGVersionNumber(pset.sversion, false,
4318 sverbuf, sizeof(sverbuf)));
4322 initPQExpBuffer(&buf);
4325 printfPQExpBuffer(&buf,
4327 " n.nspname AS \"%s\",\n"
4328 " t.tmplname AS \"%s\",\n"
4329 " t.tmplinit::pg_catalog.regproc AS \"%s\",\n"
4330 " t.tmpllexize::pg_catalog.regproc AS \"%s\",\n"
4331 " pg_catalog.obj_description(t.oid, 'pg_ts_template') AS \"%s\"\n",
4332 gettext_noop("Schema"),
4333 gettext_noop("Name"),
4334 gettext_noop("Init"),
4335 gettext_noop("Lexize"),
4336 gettext_noop("Description"));
4338 printfPQExpBuffer(&buf,
4340 " n.nspname AS \"%s\",\n"
4341 " t.tmplname AS \"%s\",\n"
4342 " pg_catalog.obj_description(t.oid, 'pg_ts_template') AS \"%s\"\n",
4343 gettext_noop("Schema"),
4344 gettext_noop("Name"),
4345 gettext_noop("Description"));
4347 appendPQExpBufferStr(&buf, "FROM pg_catalog.pg_ts_template t\n"
4348 "LEFT JOIN pg_catalog.pg_namespace n ON n.oid = t.tmplnamespace\n");
4350 processSQLNamePattern(pset.db, &buf, pattern, false, false,
4351 "n.nspname", "t.tmplname", NULL,
4352 "pg_catalog.pg_ts_template_is_visible(t.oid)");
4354 appendPQExpBufferStr(&buf, "ORDER BY 1, 2;");
4356 res = PSQLexec(buf.data);
4357 termPQExpBuffer(&buf);
4361 myopt.nullPrint = NULL;
4362 myopt.title = _("List of text search templates");
4363 myopt.translate_header = true;
4365 printQuery(res, &myopt, pset.queryFout, false, pset.logfile);
4374 * list text search configurations
4377 listTSConfigs(const char *pattern, bool verbose)
4379 PQExpBufferData buf;
4381 printQueryOpt myopt = pset.popt;
4383 if (pset.sversion < 80300)
4387 psql_error("The server (version %s) does not support full text search.\n",
4388 formatPGVersionNumber(pset.sversion, false,
4389 sverbuf, sizeof(sverbuf)));
4394 return listTSConfigsVerbose(pattern);
4396 initPQExpBuffer(&buf);
4398 printfPQExpBuffer(&buf,
4400 " n.nspname as \"%s\",\n"
4401 " c.cfgname as \"%s\",\n"
4402 " pg_catalog.obj_description(c.oid, 'pg_ts_config') as \"%s\"\n"
4403 "FROM pg_catalog.pg_ts_config c\n"
4404 "LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.cfgnamespace\n",
4405 gettext_noop("Schema"),
4406 gettext_noop("Name"),
4407 gettext_noop("Description")
4410 processSQLNamePattern(pset.db, &buf, pattern, false, false,
4411 "n.nspname", "c.cfgname", NULL,
4412 "pg_catalog.pg_ts_config_is_visible(c.oid)");
4414 appendPQExpBufferStr(&buf, "ORDER BY 1, 2;");
4416 res = PSQLexec(buf.data);
4417 termPQExpBuffer(&buf);
4421 myopt.nullPrint = NULL;
4422 myopt.title = _("List of text search configurations");
4423 myopt.translate_header = true;
4425 printQuery(res, &myopt, pset.queryFout, false, pset.logfile);
4432 listTSConfigsVerbose(const char *pattern)
4434 PQExpBufferData buf;
4438 initPQExpBuffer(&buf);
4440 printfPQExpBuffer(&buf,
4441 "SELECT c.oid, c.cfgname,\n"
4444 " np.nspname as pnspname\n"
4445 "FROM pg_catalog.pg_ts_config c\n"
4446 " LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.cfgnamespace,\n"
4447 " pg_catalog.pg_ts_parser p\n"
4448 " LEFT JOIN pg_catalog.pg_namespace np ON np.oid = p.prsnamespace\n"
4449 "WHERE p.oid = c.cfgparser\n"
4452 processSQLNamePattern(pset.db, &buf, pattern, true, false,
4453 "n.nspname", "c.cfgname", NULL,
4454 "pg_catalog.pg_ts_config_is_visible(c.oid)");
4456 appendPQExpBufferStr(&buf, "ORDER BY 3, 2;");
4458 res = PSQLexec(buf.data);
4459 termPQExpBuffer(&buf);
4463 if (PQntuples(res) == 0)
4466 psql_error("Did not find any text search configuration named \"%s\".\n",
4472 for (i = 0; i < PQntuples(res); i++)
4475 const char *cfgname;
4476 const char *nspname = NULL;
4477 const char *prsname;
4478 const char *pnspname = NULL;
4480 oid = PQgetvalue(res, i, 0);
4481 cfgname = PQgetvalue(res, i, 1);
4482 if (!PQgetisnull(res, i, 2))
4483 nspname = PQgetvalue(res, i, 2);
4484 prsname = PQgetvalue(res, i, 3);
4485 if (!PQgetisnull(res, i, 4))
4486 pnspname = PQgetvalue(res, i, 4);
4488 if (!describeOneTSConfig(oid, nspname, cfgname, pnspname, prsname))
4506 describeOneTSConfig(const char *oid, const char *nspname, const char *cfgname,
4507 const char *pnspname, const char *prsname)
4509 PQExpBufferData buf,
4512 printQueryOpt myopt = pset.popt;
4514 initPQExpBuffer(&buf);
4516 printfPQExpBuffer(&buf,
4518 " ( SELECT t.alias FROM\n"
4519 " pg_catalog.ts_token_type(c.cfgparser) AS t\n"
4520 " WHERE t.tokid = m.maptokentype ) AS \"%s\",\n"
4521 " pg_catalog.btrim(\n"
4522 " ARRAY( SELECT mm.mapdict::pg_catalog.regdictionary\n"
4523 " FROM pg_catalog.pg_ts_config_map AS mm\n"
4524 " WHERE mm.mapcfg = m.mapcfg AND mm.maptokentype = m.maptokentype\n"
4525 " ORDER BY mapcfg, maptokentype, mapseqno\n"
4526 " ) :: pg_catalog.text,\n"
4527 " '{}') AS \"%s\"\n"
4528 "FROM pg_catalog.pg_ts_config AS c, pg_catalog.pg_ts_config_map AS m\n"
4529 "WHERE c.oid = '%s' AND m.mapcfg = c.oid\n"
4530 "GROUP BY m.mapcfg, m.maptokentype, c.cfgparser\n"
4532 gettext_noop("Token"),
4533 gettext_noop("Dictionaries"),
4536 res = PSQLexec(buf.data);
4537 termPQExpBuffer(&buf);
4541 initPQExpBuffer(&title);
4544 appendPQExpBuffer(&title, _("Text search configuration \"%s.%s\""),
4547 appendPQExpBuffer(&title, _("Text search configuration \"%s\""),
4551 appendPQExpBuffer(&title, _("\nParser: \"%s.%s\""),
4554 appendPQExpBuffer(&title, _("\nParser: \"%s\""),
4557 myopt.nullPrint = NULL;
4558 myopt.title = title.data;
4559 myopt.footers = NULL;
4560 myopt.topt.default_footer = false;
4561 myopt.translate_header = true;
4563 printQuery(res, &myopt, pset.queryFout, false, pset.logfile);
4565 termPQExpBuffer(&title);
4575 * Describes foreign-data wrappers
4578 listForeignDataWrappers(const char *pattern, bool verbose)
4580 PQExpBufferData buf;
4582 printQueryOpt myopt = pset.popt;
4584 if (pset.sversion < 80400)
4588 psql_error("The server (version %s) does not support foreign-data wrappers.\n",
4589 formatPGVersionNumber(pset.sversion, false,
4590 sverbuf, sizeof(sverbuf)));
4594 initPQExpBuffer(&buf);
4595 printfPQExpBuffer(&buf,
4596 "SELECT fdw.fdwname AS \"%s\",\n"
4597 " pg_catalog.pg_get_userbyid(fdw.fdwowner) AS \"%s\",\n",
4598 gettext_noop("Name"),
4599 gettext_noop("Owner"));
4600 if (pset.sversion >= 90100)
4601 appendPQExpBuffer(&buf,
4602 " fdw.fdwhandler::pg_catalog.regproc AS \"%s\",\n",
4603 gettext_noop("Handler"));
4604 appendPQExpBuffer(&buf,
4605 " fdw.fdwvalidator::pg_catalog.regproc AS \"%s\"",
4606 gettext_noop("Validator"));
4610 appendPQExpBufferStr(&buf, ",\n ");
4611 printACLColumn(&buf, "fdwacl");
4612 appendPQExpBuffer(&buf,
4613 ",\n CASE WHEN fdwoptions IS NULL THEN '' ELSE "
4614 " '(' || array_to_string(ARRAY(SELECT "
4615 " quote_ident(option_name) || ' ' || "
4616 " quote_literal(option_value) FROM "
4617 " pg_options_to_table(fdwoptions)), ', ') || ')' "
4619 gettext_noop("FDW options"));
4621 if (pset.sversion >= 90100)
4622 appendPQExpBuffer(&buf,
4623 ",\n d.description AS \"%s\" ",
4624 gettext_noop("Description"));
4627 appendPQExpBufferStr(&buf, "\nFROM pg_catalog.pg_foreign_data_wrapper fdw\n");
4629 if (verbose && pset.sversion >= 90100)
4630 appendPQExpBufferStr(&buf,
4631 "LEFT JOIN pg_catalog.pg_description d\n"
4632 " ON d.classoid = fdw.tableoid "
4633 "AND d.objoid = fdw.oid AND d.objsubid = 0\n");
4635 processSQLNamePattern(pset.db, &buf, pattern, false, false,
4636 NULL, "fdwname", NULL, NULL);
4638 appendPQExpBufferStr(&buf, "ORDER BY 1;");
4640 res = PSQLexec(buf.data);
4641 termPQExpBuffer(&buf);
4645 myopt.nullPrint = NULL;
4646 myopt.title = _("List of foreign-data wrappers");
4647 myopt.translate_header = true;
4649 printQuery(res, &myopt, pset.queryFout, false, pset.logfile);
4658 * Describes foreign servers.
4661 listForeignServers(const char *pattern, bool verbose)
4663 PQExpBufferData buf;
4665 printQueryOpt myopt = pset.popt;
4667 if (pset.sversion < 80400)
4671 psql_error("The server (version %s) does not support foreign servers.\n",
4672 formatPGVersionNumber(pset.sversion, false,
4673 sverbuf, sizeof(sverbuf)));
4677 initPQExpBuffer(&buf);
4678 printfPQExpBuffer(&buf,
4679 "SELECT s.srvname AS \"%s\",\n"
4680 " pg_catalog.pg_get_userbyid(s.srvowner) AS \"%s\",\n"
4681 " f.fdwname AS \"%s\"",
4682 gettext_noop("Name"),
4683 gettext_noop("Owner"),
4684 gettext_noop("Foreign-data wrapper"));
4688 appendPQExpBufferStr(&buf, ",\n ");
4689 printACLColumn(&buf, "s.srvacl");
4690 appendPQExpBuffer(&buf,
4692 " s.srvtype AS \"%s\",\n"
4693 " s.srvversion AS \"%s\",\n"
4694 " CASE WHEN srvoptions IS NULL THEN '' ELSE "
4695 " '(' || array_to_string(ARRAY(SELECT "
4696 " quote_ident(option_name) || ' ' || "
4697 " quote_literal(option_value) FROM "
4698 " pg_options_to_table(srvoptions)), ', ') || ')' "
4700 " d.description AS \"%s\"",
4701 gettext_noop("Type"),
4702 gettext_noop("Version"),
4703 gettext_noop("FDW options"),
4704 gettext_noop("Description"));
4707 appendPQExpBufferStr(&buf,
4708 "\nFROM pg_catalog.pg_foreign_server s\n"
4709 " JOIN pg_catalog.pg_foreign_data_wrapper f ON f.oid=s.srvfdw\n");
4712 appendPQExpBufferStr(&buf,
4713 "LEFT JOIN pg_description d\n "
4714 "ON d.classoid = s.tableoid AND d.objoid = s.oid "
4715 "AND d.objsubid = 0\n");
4717 processSQLNamePattern(pset.db, &buf, pattern, false, false,
4718 NULL, "s.srvname", NULL, NULL);
4720 appendPQExpBufferStr(&buf, "ORDER BY 1;");
4722 res = PSQLexec(buf.data);
4723 termPQExpBuffer(&buf);
4727 myopt.nullPrint = NULL;
4728 myopt.title = _("List of foreign servers");
4729 myopt.translate_header = true;
4731 printQuery(res, &myopt, pset.queryFout, false, pset.logfile);
4740 * Describes user mappings.
4743 listUserMappings(const char *pattern, bool verbose)
4745 PQExpBufferData buf;
4747 printQueryOpt myopt = pset.popt;
4749 if (pset.sversion < 80400)
4753 psql_error("The server (version %s) does not support user mappings.\n",
4754 formatPGVersionNumber(pset.sversion, false,
4755 sverbuf, sizeof(sverbuf)));
4759 initPQExpBuffer(&buf);
4760 printfPQExpBuffer(&buf,
4761 "SELECT um.srvname AS \"%s\",\n"
4762 " um.usename AS \"%s\"",
4763 gettext_noop("Server"),
4764 gettext_noop("User name"));
4767 appendPQExpBuffer(&buf,
4768 ",\n CASE WHEN umoptions IS NULL THEN '' ELSE "
4769 " '(' || array_to_string(ARRAY(SELECT "
4770 " quote_ident(option_name) || ' ' || "
4771 " quote_literal(option_value) FROM "
4772 " pg_options_to_table(umoptions)), ', ') || ')' "
4774 gettext_noop("FDW options"));
4776 appendPQExpBufferStr(&buf, "\nFROM pg_catalog.pg_user_mappings um\n");
4778 processSQLNamePattern(pset.db, &buf, pattern, false, false,
4779 NULL, "um.srvname", "um.usename", NULL);
4781 appendPQExpBufferStr(&buf, "ORDER BY 1, 2;");
4783 res = PSQLexec(buf.data);
4784 termPQExpBuffer(&buf);
4788 myopt.nullPrint = NULL;
4789 myopt.title = _("List of user mappings");
4790 myopt.translate_header = true;
4792 printQuery(res, &myopt, pset.queryFout, false, pset.logfile);
4801 * Describes foreign tables.
4804 listForeignTables(const char *pattern, bool verbose)
4806 PQExpBufferData buf;
4808 printQueryOpt myopt = pset.popt;
4810 if (pset.sversion < 90100)
4814 psql_error("The server (version %s) does not support foreign tables.\n",
4815 formatPGVersionNumber(pset.sversion, false,
4816 sverbuf, sizeof(sverbuf)));
4820 initPQExpBuffer(&buf);
4821 printfPQExpBuffer(&buf,
4822 "SELECT n.nspname AS \"%s\",\n"
4823 " c.relname AS \"%s\",\n"
4824 " s.srvname AS \"%s\"",
4825 gettext_noop("Schema"),
4826 gettext_noop("Table"),
4827 gettext_noop("Server"));
4830 appendPQExpBuffer(&buf,
4831 ",\n CASE WHEN ftoptions IS NULL THEN '' ELSE "
4832 " '(' || array_to_string(ARRAY(SELECT "
4833 " quote_ident(option_name) || ' ' || "
4834 " quote_literal(option_value) FROM "
4835 " pg_options_to_table(ftoptions)), ', ') || ')' "
4837 " d.description AS \"%s\"",
4838 gettext_noop("FDW options"),
4839 gettext_noop("Description"));
4841 appendPQExpBufferStr(&buf,
4842 "\nFROM pg_catalog.pg_foreign_table ft\n"
4843 " INNER JOIN pg_catalog.pg_class c"
4844 " ON c.oid = ft.ftrelid\n"
4845 " INNER JOIN pg_catalog.pg_namespace n"
4846 " ON n.oid = c.relnamespace\n"
4847 " INNER JOIN pg_catalog.pg_foreign_server s"
4848 " ON s.oid = ft.ftserver\n");
4850 appendPQExpBufferStr(&buf,
4851 " LEFT JOIN pg_catalog.pg_description d\n"
4852 " ON d.classoid = c.tableoid AND "
4853 "d.objoid = c.oid AND d.objsubid = 0\n");
4855 processSQLNamePattern(pset.db, &buf, pattern, false, false,
4856 "n.nspname", "c.relname", NULL,
4857 "pg_catalog.pg_table_is_visible(c.oid)");
4859 appendPQExpBufferStr(&buf, "ORDER BY 1, 2;");
4861 res = PSQLexec(buf.data);
4862 termPQExpBuffer(&buf);
4866 myopt.nullPrint = NULL;
4867 myopt.title = _("List of foreign tables");
4868 myopt.translate_header = true;
4870 printQuery(res, &myopt, pset.queryFout, false, pset.logfile);
4879 * Briefly describes installed extensions.
4882 listExtensions(const char *pattern)
4884 PQExpBufferData buf;
4886 printQueryOpt myopt = pset.popt;
4888 if (pset.sversion < 90100)
4892 psql_error("The server (version %s) does not support extensions.\n",
4893 formatPGVersionNumber(pset.sversion, false,
4894 sverbuf, sizeof(sverbuf)));
4898 initPQExpBuffer(&buf);
4899 printfPQExpBuffer(&buf,
4900 "SELECT e.extname AS \"%s\", "
4901 "e.extversion AS \"%s\", n.nspname AS \"%s\", c.description AS \"%s\"\n"
4902 "FROM pg_catalog.pg_extension e "
4903 "LEFT JOIN pg_catalog.pg_namespace n ON n.oid = e.extnamespace "
4904 "LEFT JOIN pg_catalog.pg_description c ON c.objoid = e.oid "
4905 "AND c.classoid = 'pg_catalog.pg_extension'::pg_catalog.regclass\n",
4906 gettext_noop("Name"),
4907 gettext_noop("Version"),
4908 gettext_noop("Schema"),
4909 gettext_noop("Description"));
4911 processSQLNamePattern(pset.db, &buf, pattern,
4913 NULL, "e.extname", NULL,
4916 appendPQExpBufferStr(&buf, "ORDER BY 1;");
4918 res = PSQLexec(buf.data);
4919 termPQExpBuffer(&buf);
4923 myopt.nullPrint = NULL;
4924 myopt.title = _("List of installed extensions");
4925 myopt.translate_header = true;
4927 printQuery(res, &myopt, pset.queryFout, false, pset.logfile);
4936 * List contents of installed extensions.
4939 listExtensionContents(const char *pattern)
4941 PQExpBufferData buf;
4945 if (pset.sversion < 90100)
4949 psql_error("The server (version %s) does not support extensions.\n",
4950 formatPGVersionNumber(pset.sversion, false,
4951 sverbuf, sizeof(sverbuf)));
4955 initPQExpBuffer(&buf);
4956 printfPQExpBuffer(&buf,
4957 "SELECT e.extname, e.oid\n"
4958 "FROM pg_catalog.pg_extension e\n");
4960 processSQLNamePattern(pset.db, &buf, pattern,
4962 NULL, "e.extname", NULL,
4965 appendPQExpBufferStr(&buf, "ORDER BY 1;");
4967 res = PSQLexec(buf.data);
4968 termPQExpBuffer(&buf);
4972 if (PQntuples(res) == 0)
4977 psql_error("Did not find any extension named \"%s\".\n",
4980 psql_error("Did not find any extensions.\n");
4986 for (i = 0; i < PQntuples(res); i++)
4988 const char *extname;
4991 extname = PQgetvalue(res, i, 0);
4992 oid = PQgetvalue(res, i, 1);
4994 if (!listOneExtensionContents(extname, oid))
5011 listOneExtensionContents(const char *extname, const char *oid)
5013 PQExpBufferData buf;
5016 printQueryOpt myopt = pset.popt;
5018 initPQExpBuffer(&buf);
5019 printfPQExpBuffer(&buf,
5020 "SELECT pg_catalog.pg_describe_object(classid, objid, 0) AS \"%s\"\n"
5021 "FROM pg_catalog.pg_depend\n"
5022 "WHERE refclassid = 'pg_catalog.pg_extension'::pg_catalog.regclass AND refobjid = '%s' AND deptype = 'e'\n"
5024 gettext_noop("Object description"),
5027 res = PSQLexec(buf.data);
5028 termPQExpBuffer(&buf);
5032 myopt.nullPrint = NULL;
5033 snprintf(title, sizeof(title), _("Objects in extension \"%s\""), extname);
5034 myopt.title = title;
5035 myopt.translate_header = true;
5037 printQuery(res, &myopt, pset.queryFout, false, pset.logfile);
5044 * Lists publications.
5046 * Takes an optional regexp to select particular publications
5049 listPublications(const char *pattern)
5051 PQExpBufferData buf;
5053 printQueryOpt myopt = pset.popt;
5054 static const bool translate_columns[] = {false, false, false, false, false, false};
5056 if (pset.sversion < 100000)
5060 psql_error("The server (version %s) does not support publications.\n",
5061 formatPGVersionNumber(pset.sversion, false,
5062 sverbuf, sizeof(sverbuf)));
5066 initPQExpBuffer(&buf);
5068 printfPQExpBuffer(&buf,
5069 "SELECT pubname AS \"%s\",\n"
5070 " pg_catalog.pg_get_userbyid(pubowner) AS \"%s\",\n"
5071 " puballtables AS \"%s\",\n"
5072 " pubinsert AS \"%s\",\n"
5073 " pubupdate AS \"%s\",\n"
5074 " pubdelete AS \"%s\"\n",
5075 gettext_noop("Name"),
5076 gettext_noop("Owner"),
5077 gettext_noop("All tables"),
5078 gettext_noop("Inserts"),
5079 gettext_noop("Updates"),
5080 gettext_noop("Deletes"));
5082 appendPQExpBufferStr(&buf,
5083 "\nFROM pg_catalog.pg_publication\n");
5085 processSQLNamePattern(pset.db, &buf, pattern, false, false,
5086 NULL, "pubname", NULL,
5089 appendPQExpBufferStr(&buf, "ORDER BY 1;");
5091 res = PSQLexec(buf.data);
5092 termPQExpBuffer(&buf);
5096 myopt.nullPrint = NULL;
5097 myopt.title = _("List of publications");
5098 myopt.translate_header = true;
5099 myopt.translate_columns = translate_columns;
5100 myopt.n_translate_columns = lengthof(translate_columns);
5102 printQuery(res, &myopt, pset.queryFout, false, pset.logfile);
5110 * Describes publications including the contents.
5112 * Takes an optional regexp to select particular publications
5115 describePublications(const char *pattern)
5117 PQExpBufferData buf;
5121 if (pset.sversion < 100000)
5125 psql_error("The server (version %s) does not support publications.\n",
5126 formatPGVersionNumber(pset.sversion, false,
5127 sverbuf, sizeof(sverbuf)));
5131 initPQExpBuffer(&buf);
5133 printfPQExpBuffer(&buf,
5134 "SELECT oid, pubname, puballtables, pubinsert,\n"
5135 " pubupdate, pubdelete\n"
5136 "FROM pg_catalog.pg_publication\n");
5138 processSQLNamePattern(pset.db, &buf, pattern, false, false,
5139 NULL, "pubname", NULL,
5142 appendPQExpBufferStr(&buf, "ORDER BY 2;");
5144 res = PSQLexec(buf.data);
5147 termPQExpBuffer(&buf);
5151 for (i = 0; i < PQntuples(res); i++)
5153 const char align = 'l';
5158 char *pubid = PQgetvalue(res, i, 0);
5159 char *pubname = PQgetvalue(res, i, 1);
5160 bool puballtables = strcmp(PQgetvalue(res, i, 2), "t") == 0;
5162 PQExpBufferData title;
5163 printTableOpt myopt = pset.popt.topt;
5164 printTableContent cont;
5166 initPQExpBuffer(&title);
5167 printfPQExpBuffer(&title, _("Publication %s"), pubname);
5168 printTableInit(&cont, &myopt, title.data, ncols, nrows);
5170 printTableAddHeader(&cont, gettext_noop("All tables"), true, align);
5171 printTableAddHeader(&cont, gettext_noop("Inserts"), true, align);
5172 printTableAddHeader(&cont, gettext_noop("Updates"), true, align);
5173 printTableAddHeader(&cont, gettext_noop("Deletes"), true, align);
5175 printTableAddCell(&cont, PQgetvalue(res, i, 2), false, false);
5176 printTableAddCell(&cont, PQgetvalue(res, i, 3), false, false);
5177 printTableAddCell(&cont, PQgetvalue(res, i, 4), false, false);
5178 printTableAddCell(&cont, PQgetvalue(res, i, 5), false, false);
5182 printfPQExpBuffer(&buf,
5183 "SELECT n.nspname, c.relname\n"
5184 "FROM pg_catalog.pg_class c,\n"
5185 " pg_catalog.pg_namespace n,\n"
5186 " pg_catalog.pg_publication_rel pr\n"
5187 "WHERE c.relnamespace = n.oid\n"
5188 " AND c.oid = pr.prrelid\n"
5189 " AND pr.prpubid = '%s'\n"
5190 "ORDER BY 1,2", pubid);
5192 tabres = PSQLexec(buf.data);
5195 printTableCleanup(&cont);
5197 termPQExpBuffer(&buf);
5198 termPQExpBuffer(&title);
5202 tables = PQntuples(tabres);
5205 printTableAddFooter(&cont, _("Tables:"));
5207 for (j = 0; j < tables; j++)
5209 printfPQExpBuffer(&buf, " \"%s.%s\"",
5210 PQgetvalue(tabres, j, 0),
5211 PQgetvalue(tabres, j, 1));
5213 printTableAddFooter(&cont, buf.data);
5218 printTable(&cont, pset.queryFout, false, pset.logfile);
5219 printTableCleanup(&cont);
5221 termPQExpBuffer(&title);
5224 termPQExpBuffer(&buf);
5231 * Describes subscriptions.
5233 * Takes an optional regexp to select particular subscriptions
5236 describeSubscriptions(const char *pattern, bool verbose)
5238 PQExpBufferData buf;
5240 printQueryOpt myopt = pset.popt;
5241 static const bool translate_columns[] = {false, false, false, false,
5244 if (pset.sversion < 100000)
5248 psql_error("The server (version %s) does not support subscriptions.\n",
5249 formatPGVersionNumber(pset.sversion, false,
5250 sverbuf, sizeof(sverbuf)));
5254 initPQExpBuffer(&buf);
5256 printfPQExpBuffer(&buf,
5257 "SELECT subname AS \"%s\"\n"
5258 ", pg_catalog.pg_get_userbyid(subowner) AS \"%s\"\n"
5259 ", subenabled AS \"%s\"\n"
5260 ", subpublications AS \"%s\"\n",
5261 gettext_noop("Name"),
5262 gettext_noop("Owner"),
5263 gettext_noop("Enabled"),
5264 gettext_noop("Publication"));
5268 appendPQExpBuffer(&buf,
5269 ", subsynccommit AS \"%s\"\n"
5270 ", subconninfo AS \"%s\"\n",
5271 gettext_noop("Synchronous commit"),
5272 gettext_noop("Conninfo"));
5275 /* Only display subscriptions in current database. */
5276 appendPQExpBufferStr(&buf,
5277 "FROM pg_catalog.pg_subscription\n"
5278 "WHERE subdbid = (SELECT oid\n"
5279 " FROM pg_catalog.pg_database\n"
5280 " WHERE datname = current_database())");
5282 processSQLNamePattern(pset.db, &buf, pattern, true, false,
5283 NULL, "subname", NULL,
5286 appendPQExpBufferStr(&buf, "ORDER BY 1;");
5288 res = PSQLexec(buf.data);
5289 termPQExpBuffer(&buf);
5293 myopt.nullPrint = NULL;
5294 myopt.title = _("List of subscriptions");
5295 myopt.translate_header = true;
5296 myopt.translate_columns = translate_columns;
5297 myopt.n_translate_columns = lengthof(translate_columns);
5299 printQuery(res, &myopt, pset.queryFout, false, pset.logfile);
5308 * Helper function for consistently formatting ACL (privilege) columns.
5309 * The proper targetlist entry is appended to buf. Note lack of any
5310 * whitespace or comma decoration.
5313 printACLColumn(PQExpBuffer buf, const char *colname)
5315 if (pset.sversion >= 80100)
5316 appendPQExpBuffer(buf,
5317 "pg_catalog.array_to_string(%s, E'\\n') AS \"%s\"",
5318 colname, gettext_noop("Access privileges"));
5320 appendPQExpBuffer(buf,
5321 "pg_catalog.array_to_string(%s, '\\n') AS \"%s\"",
5322 colname, gettext_noop("Access privileges"));