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 9.2 and up. It's okay to omit irrelevant
7 * information for an old server, but not to fail outright. (But failing
8 * against a pre-9.2 server is allowed.)
10 * Copyright (c) 2000-2023, PostgreSQL Global Development Group
12 * src/bin/psql/describe.c
14 #include "postgres_fe.h"
18 #include "catalog/pg_am.h"
19 #include "catalog/pg_attribute_d.h"
20 #include "catalog/pg_cast_d.h"
21 #include "catalog/pg_class_d.h"
22 #include "catalog/pg_default_acl_d.h"
24 #include "common/logging.h"
26 #include "fe_utils/mbprint.h"
27 #include "fe_utils/print.h"
28 #include "fe_utils/string_utils.h"
30 #include "variables.h"
32 static const char *map_typename_pattern(const char *pattern
);
33 static bool describeOneTableDetails(const char *schemaname
,
34 const char *relationname
,
37 static void add_tablespace_footer(printTableContent
*const cont
, char relkind
,
38 Oid tablespace
, const bool newline
);
39 static void add_role_attribute(PQExpBuffer buf
, const char *const str
);
40 static bool listTSParsersVerbose(const char *pattern
);
41 static bool describeOneTSParser(const char *oid
, const char *nspname
,
43 static bool listTSConfigsVerbose(const char *pattern
);
44 static bool describeOneTSConfig(const char *oid
, const char *nspname
,
46 const char *pnspname
, const char *prsname
);
47 static void printACLColumn(PQExpBuffer buf
, const char *colname
);
48 static bool listOneExtensionContents(const char *extname
, const char *oid
);
49 static bool validateSQLNamePattern(PQExpBuffer buf
, const char *pattern
,
50 bool have_where
, bool force_escape
,
51 const char *schemavar
, const char *namevar
,
52 const char *altnamevar
,
53 const char *visibilityrule
,
54 bool *added_clause
, int maxparts
);
58 * Handlers for various slash commands displaying some sort of list
59 * of things in the database.
61 * Note: try to format the queries to look nice in -E output.
68 * Takes an optional regexp to select particular aggregates
71 describeAggregates(const char *pattern
, bool verbose
, bool showSystem
)
75 printQueryOpt myopt
= pset
.popt
;
77 initPQExpBuffer(&buf
);
79 printfPQExpBuffer(&buf
,
80 "SELECT n.nspname as \"%s\",\n"
81 " p.proname AS \"%s\",\n"
82 " pg_catalog.format_type(p.prorettype, NULL) AS \"%s\",\n"
83 " CASE WHEN p.pronargs = 0\n"
84 " THEN CAST('*' AS pg_catalog.text)\n"
85 " ELSE pg_catalog.pg_get_function_arguments(p.oid)\n"
87 gettext_noop("Schema"),
89 gettext_noop("Result data type"),
90 gettext_noop("Argument data types"));
92 if (pset
.sversion
>= 110000)
93 appendPQExpBuffer(&buf
,
94 " pg_catalog.obj_description(p.oid, 'pg_proc') as \"%s\"\n"
95 "FROM pg_catalog.pg_proc p\n"
96 " LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace\n"
97 "WHERE p.prokind = 'a'\n",
98 gettext_noop("Description"));
100 appendPQExpBuffer(&buf
,
101 " pg_catalog.obj_description(p.oid, 'pg_proc') as \"%s\"\n"
102 "FROM pg_catalog.pg_proc p\n"
103 " LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace\n"
104 "WHERE p.proisagg\n",
105 gettext_noop("Description"));
107 if (!showSystem
&& !pattern
)
108 appendPQExpBufferStr(&buf
, " AND n.nspname <> 'pg_catalog'\n"
109 " AND n.nspname <> 'information_schema'\n");
111 if (!validateSQLNamePattern(&buf
, pattern
, true, false,
112 "n.nspname", "p.proname", NULL
,
113 "pg_catalog.pg_function_is_visible(p.oid)",
116 termPQExpBuffer(&buf
);
120 appendPQExpBufferStr(&buf
, "ORDER BY 1, 2, 4;");
122 res
= PSQLexec(buf
.data
);
123 termPQExpBuffer(&buf
);
127 myopt
.nullPrint
= NULL
;
128 myopt
.title
= _("List of aggregate functions");
129 myopt
.translate_header
= true;
131 printQuery(res
, &myopt
, pset
.queryFout
, false, pset
.logfile
);
139 * Takes an optional regexp to select particular access methods
142 describeAccessMethods(const char *pattern
, bool verbose
)
146 printQueryOpt myopt
= pset
.popt
;
147 static const bool translate_columns
[] = {false, true, false, false};
149 if (pset
.sversion
< 90600)
153 pg_log_error("The server (version %s) does not support access methods.",
154 formatPGVersionNumber(pset
.sversion
, false,
155 sverbuf
, sizeof(sverbuf
)));
159 initPQExpBuffer(&buf
);
161 printfPQExpBuffer(&buf
,
162 "SELECT amname AS \"%s\",\n"
164 " WHEN 'i' THEN '%s'"
165 " WHEN 't' THEN '%s'"
167 gettext_noop("Name"),
168 gettext_noop("Index"),
169 gettext_noop("Table"),
170 gettext_noop("Type"));
174 appendPQExpBuffer(&buf
,
175 ",\n amhandler AS \"%s\",\n"
176 " pg_catalog.obj_description(oid, 'pg_am') AS \"%s\"",
177 gettext_noop("Handler"),
178 gettext_noop("Description"));
181 appendPQExpBufferStr(&buf
,
182 "\nFROM pg_catalog.pg_am\n");
184 if (!validateSQLNamePattern(&buf
, pattern
, false, false,
185 NULL
, "amname", NULL
,
189 termPQExpBuffer(&buf
);
193 appendPQExpBufferStr(&buf
, "ORDER BY 1;");
195 res
= PSQLexec(buf
.data
);
196 termPQExpBuffer(&buf
);
200 myopt
.nullPrint
= NULL
;
201 myopt
.title
= _("List of access methods");
202 myopt
.translate_header
= true;
203 myopt
.translate_columns
= translate_columns
;
204 myopt
.n_translate_columns
= lengthof(translate_columns
);
206 printQuery(res
, &myopt
, pset
.queryFout
, false, pset
.logfile
);
214 * Takes an optional regexp to select particular tablespaces
217 describeTablespaces(const char *pattern
, bool verbose
)
221 printQueryOpt myopt
= pset
.popt
;
223 initPQExpBuffer(&buf
);
225 printfPQExpBuffer(&buf
,
226 "SELECT spcname AS \"%s\",\n"
227 " pg_catalog.pg_get_userbyid(spcowner) AS \"%s\",\n"
228 " pg_catalog.pg_tablespace_location(oid) AS \"%s\"",
229 gettext_noop("Name"),
230 gettext_noop("Owner"),
231 gettext_noop("Location"));
235 appendPQExpBufferStr(&buf
, ",\n ");
236 printACLColumn(&buf
, "spcacl");
237 appendPQExpBuffer(&buf
,
238 ",\n spcoptions AS \"%s\""
239 ",\n pg_catalog.pg_size_pretty(pg_catalog.pg_tablespace_size(oid)) AS \"%s\""
240 ",\n pg_catalog.shobj_description(oid, 'pg_tablespace') AS \"%s\"",
241 gettext_noop("Options"),
242 gettext_noop("Size"),
243 gettext_noop("Description"));
246 appendPQExpBufferStr(&buf
,
247 "\nFROM pg_catalog.pg_tablespace\n");
249 if (!validateSQLNamePattern(&buf
, pattern
, false, false,
250 NULL
, "spcname", NULL
,
254 termPQExpBuffer(&buf
);
258 appendPQExpBufferStr(&buf
, "ORDER BY 1;");
260 res
= PSQLexec(buf
.data
);
261 termPQExpBuffer(&buf
);
265 myopt
.nullPrint
= NULL
;
266 myopt
.title
= _("List of tablespaces");
267 myopt
.translate_header
= true;
269 printQuery(res
, &myopt
, pset
.queryFout
, false, pset
.logfile
);
278 * Takes an optional regexp to select particular functions.
280 * As with \d, you can specify the kinds of functions you want:
288 * and you can mix and match these in any order.
291 describeFunctions(const char *functypes
, const char *func_pattern
,
292 char **arg_patterns
, int num_arg_patterns
,
293 bool verbose
, bool showSystem
)
295 bool showAggregate
= strchr(functypes
, 'a') != NULL
;
296 bool showNormal
= strchr(functypes
, 'n') != NULL
;
297 bool showProcedure
= strchr(functypes
, 'p') != NULL
;
298 bool showTrigger
= strchr(functypes
, 't') != NULL
;
299 bool showWindow
= strchr(functypes
, 'w') != NULL
;
303 printQueryOpt myopt
= pset
.popt
;
304 static const bool translate_columns
[] = {false, false, false, false, true, true, true, false, true, false, false, false, false};
306 /* No "Parallel" column before 9.6 */
307 static const bool translate_columns_pre_96
[] = {false, false, false, false, true, true, false, true, false, false, false, false};
309 if (strlen(functypes
) != strspn(functypes
, "anptwS+"))
311 pg_log_error("\\df only takes [anptwS+] as options");
315 if (showProcedure
&& pset
.sversion
< 110000)
319 pg_log_error("\\df does not take a \"%c\" option with server version %s",
321 formatPGVersionNumber(pset
.sversion
, false,
322 sverbuf
, sizeof(sverbuf
)));
326 if (!showAggregate
&& !showNormal
&& !showProcedure
&& !showTrigger
&& !showWindow
)
328 showAggregate
= showNormal
= showTrigger
= showWindow
= true;
329 if (pset
.sversion
>= 110000)
330 showProcedure
= true;
333 initPQExpBuffer(&buf
);
335 printfPQExpBuffer(&buf
,
336 "SELECT n.nspname as \"%s\",\n"
337 " p.proname as \"%s\",\n",
338 gettext_noop("Schema"),
339 gettext_noop("Name"));
341 if (pset
.sversion
>= 110000)
342 appendPQExpBuffer(&buf
,
343 " pg_catalog.pg_get_function_result(p.oid) as \"%s\",\n"
344 " pg_catalog.pg_get_function_arguments(p.oid) as \"%s\",\n"
346 " WHEN 'a' THEN '%s'\n"
347 " WHEN 'w' THEN '%s'\n"
348 " WHEN 'p' THEN '%s'\n"
351 gettext_noop("Result data type"),
352 gettext_noop("Argument data types"),
353 /* translator: "agg" is short for "aggregate" */
355 gettext_noop("window"),
356 gettext_noop("proc"),
357 gettext_noop("func"),
358 gettext_noop("Type"));
360 appendPQExpBuffer(&buf
,
361 " pg_catalog.pg_get_function_result(p.oid) as \"%s\",\n"
362 " pg_catalog.pg_get_function_arguments(p.oid) as \"%s\",\n"
364 " WHEN p.proisagg THEN '%s'\n"
365 " WHEN p.proiswindow THEN '%s'\n"
366 " WHEN p.prorettype = 'pg_catalog.trigger'::pg_catalog.regtype THEN '%s'\n"
369 gettext_noop("Result data type"),
370 gettext_noop("Argument data types"),
371 /* translator: "agg" is short for "aggregate" */
373 gettext_noop("window"),
374 gettext_noop("trigger"),
375 gettext_noop("func"),
376 gettext_noop("Type"));
380 appendPQExpBuffer(&buf
,
382 " WHEN p.provolatile = 'i' THEN '%s'\n"
383 " WHEN p.provolatile = 's' THEN '%s'\n"
384 " WHEN p.provolatile = 'v' THEN '%s'\n"
386 gettext_noop("immutable"),
387 gettext_noop("stable"),
388 gettext_noop("volatile"),
389 gettext_noop("Volatility"));
390 if (pset
.sversion
>= 90600)
391 appendPQExpBuffer(&buf
,
393 " WHEN p.proparallel = 'r' THEN '%s'\n"
394 " WHEN p.proparallel = 's' THEN '%s'\n"
395 " WHEN p.proparallel = 'u' THEN '%s'\n"
397 gettext_noop("restricted"),
398 gettext_noop("safe"),
399 gettext_noop("unsafe"),
400 gettext_noop("Parallel"));
401 appendPQExpBuffer(&buf
,
402 ",\n pg_catalog.pg_get_userbyid(p.proowner) as \"%s\""
403 ",\n CASE WHEN prosecdef THEN '%s' ELSE '%s' END AS \"%s\"",
404 gettext_noop("Owner"),
405 gettext_noop("definer"),
406 gettext_noop("invoker"),
407 gettext_noop("Security"));
408 appendPQExpBufferStr(&buf
, ",\n ");
409 printACLColumn(&buf
, "p.proacl");
410 appendPQExpBuffer(&buf
,
411 ",\n l.lanname as \"%s\"",
412 gettext_noop("Language"));
413 appendPQExpBuffer(&buf
,
414 ",\n CASE WHEN l.lanname IN ('internal', 'c') THEN p.prosrc END as \"%s\"",
415 gettext_noop("Internal name"));
416 appendPQExpBuffer(&buf
,
417 ",\n pg_catalog.obj_description(p.oid, 'pg_proc') as \"%s\"",
418 gettext_noop("Description"));
421 appendPQExpBufferStr(&buf
,
422 "\nFROM pg_catalog.pg_proc p"
423 "\n LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace\n");
425 for (int i
= 0; i
< num_arg_patterns
; i
++)
427 appendPQExpBuffer(&buf
,
428 " LEFT JOIN pg_catalog.pg_type t%d ON t%d.oid = p.proargtypes[%d]\n"
429 " LEFT JOIN pg_catalog.pg_namespace nt%d ON nt%d.oid = t%d.typnamespace\n",
434 appendPQExpBufferStr(&buf
,
435 " LEFT JOIN pg_catalog.pg_language l ON l.oid = p.prolang\n");
439 /* filter by function type, if requested */
440 if (showNormal
&& showAggregate
&& showProcedure
&& showTrigger
&& showWindow
)
447 appendPQExpBufferStr(&buf
, " AND ");
450 appendPQExpBufferStr(&buf
, "WHERE ");
453 if (pset
.sversion
>= 110000)
454 appendPQExpBufferStr(&buf
, "p.prokind <> 'a'\n");
456 appendPQExpBufferStr(&buf
, "NOT p.proisagg\n");
458 if (!showProcedure
&& pset
.sversion
>= 110000)
461 appendPQExpBufferStr(&buf
, " AND ");
464 appendPQExpBufferStr(&buf
, "WHERE ");
467 appendPQExpBufferStr(&buf
, "p.prokind <> 'p'\n");
472 appendPQExpBufferStr(&buf
, " AND ");
475 appendPQExpBufferStr(&buf
, "WHERE ");
478 appendPQExpBufferStr(&buf
, "p.prorettype <> 'pg_catalog.trigger'::pg_catalog.regtype\n");
483 appendPQExpBufferStr(&buf
, " AND ");
486 appendPQExpBufferStr(&buf
, "WHERE ");
489 if (pset
.sversion
>= 110000)
490 appendPQExpBufferStr(&buf
, "p.prokind <> 'w'\n");
492 appendPQExpBufferStr(&buf
, "NOT p.proiswindow\n");
497 bool needs_or
= false;
499 appendPQExpBufferStr(&buf
, "WHERE (\n ");
501 /* Note: at least one of these must be true ... */
504 if (pset
.sversion
>= 110000)
505 appendPQExpBufferStr(&buf
, "p.prokind = 'a'\n");
507 appendPQExpBufferStr(&buf
, "p.proisagg\n");
513 appendPQExpBufferStr(&buf
, " OR ");
514 appendPQExpBufferStr(&buf
,
515 "p.prorettype = 'pg_catalog.trigger'::pg_catalog.regtype\n");
521 appendPQExpBufferStr(&buf
, " OR ");
522 appendPQExpBufferStr(&buf
, "p.prokind = 'p'\n");
528 appendPQExpBufferStr(&buf
, " OR ");
529 if (pset
.sversion
>= 110000)
530 appendPQExpBufferStr(&buf
, "p.prokind = 'w'\n");
532 appendPQExpBufferStr(&buf
, "p.proiswindow\n");
534 appendPQExpBufferStr(&buf
, " )\n");
537 if (!validateSQLNamePattern(&buf
, func_pattern
, have_where
, false,
538 "n.nspname", "p.proname", NULL
,
539 "pg_catalog.pg_function_is_visible(p.oid)",
543 for (int i
= 0; i
< num_arg_patterns
; i
++)
545 if (strcmp(arg_patterns
[i
], "-") != 0)
548 * Match type-name patterns against either internal or external
549 * name, like \dT. Unlike \dT, there seems no reason to
550 * discriminate against arrays or composite types.
557 snprintf(nspname
, sizeof(nspname
), "nt%d.nspname", i
);
558 snprintf(typname
, sizeof(typname
), "t%d.typname", i
);
559 snprintf(ft
, sizeof(ft
),
560 "pg_catalog.format_type(t%d.oid, NULL)", i
);
561 snprintf(tiv
, sizeof(tiv
),
562 "pg_catalog.pg_type_is_visible(t%d.oid)", i
);
563 if (!validateSQLNamePattern(&buf
,
564 map_typename_pattern(arg_patterns
[i
]),
566 nspname
, typname
, ft
, tiv
,
572 /* "-" pattern specifies no such parameter */
573 appendPQExpBuffer(&buf
, " AND t%d.typname IS NULL\n", i
);
577 if (!showSystem
&& !func_pattern
)
578 appendPQExpBufferStr(&buf
, " AND n.nspname <> 'pg_catalog'\n"
579 " AND n.nspname <> 'information_schema'\n");
581 appendPQExpBufferStr(&buf
, "ORDER BY 1, 2, 4;");
583 res
= PSQLexec(buf
.data
);
584 termPQExpBuffer(&buf
);
588 myopt
.nullPrint
= NULL
;
589 myopt
.title
= _("List of functions");
590 myopt
.translate_header
= true;
591 if (pset
.sversion
>= 90600)
593 myopt
.translate_columns
= translate_columns
;
594 myopt
.n_translate_columns
= lengthof(translate_columns
);
598 myopt
.translate_columns
= translate_columns_pre_96
;
599 myopt
.n_translate_columns
= lengthof(translate_columns_pre_96
);
602 printQuery(res
, &myopt
, pset
.queryFout
, false, pset
.logfile
);
608 termPQExpBuffer(&buf
);
619 describeTypes(const char *pattern
, bool verbose
, bool showSystem
)
623 printQueryOpt myopt
= pset
.popt
;
625 initPQExpBuffer(&buf
);
627 printfPQExpBuffer(&buf
,
628 "SELECT n.nspname as \"%s\",\n"
629 " pg_catalog.format_type(t.oid, NULL) AS \"%s\",\n",
630 gettext_noop("Schema"),
631 gettext_noop("Name"));
634 appendPQExpBuffer(&buf
,
635 " t.typname AS \"%s\",\n"
636 " CASE WHEN t.typrelid != 0\n"
637 " THEN CAST('tuple' AS pg_catalog.text)\n"
638 " WHEN t.typlen < 0\n"
639 " THEN CAST('var' AS pg_catalog.text)\n"
640 " ELSE CAST(t.typlen AS pg_catalog.text)\n"
642 " pg_catalog.array_to_string(\n"
644 " SELECT e.enumlabel\n"
645 " FROM pg_catalog.pg_enum e\n"
646 " WHERE e.enumtypid = t.oid\n"
647 " ORDER BY e.enumsortorder\n"
651 " pg_catalog.pg_get_userbyid(t.typowner) AS \"%s\",\n",
652 gettext_noop("Internal name"),
653 gettext_noop("Size"),
654 gettext_noop("Elements"),
655 gettext_noop("Owner"));
656 printACLColumn(&buf
, "t.typacl");
657 appendPQExpBufferStr(&buf
, ",\n ");
660 appendPQExpBuffer(&buf
,
661 " pg_catalog.obj_description(t.oid, 'pg_type') as \"%s\"\n",
662 gettext_noop("Description"));
664 appendPQExpBufferStr(&buf
, "FROM pg_catalog.pg_type t\n"
665 " LEFT JOIN pg_catalog.pg_namespace n ON n.oid = t.typnamespace\n");
668 * do not include complex types (typrelid!=0) unless they are standalone
671 appendPQExpBufferStr(&buf
, "WHERE (t.typrelid = 0 ");
672 appendPQExpBufferStr(&buf
, "OR (SELECT c.relkind = " CppAsString2(RELKIND_COMPOSITE_TYPE
)
673 " FROM pg_catalog.pg_class c "
674 "WHERE c.oid = t.typrelid))\n");
677 * do not include array types unless the pattern contains []
679 if (pattern
== NULL
|| strstr(pattern
, "[]") == NULL
)
680 appendPQExpBufferStr(&buf
, " AND NOT EXISTS(SELECT 1 FROM pg_catalog.pg_type el WHERE el.oid = t.typelem AND el.typarray = t.oid)\n");
682 if (!showSystem
&& !pattern
)
683 appendPQExpBufferStr(&buf
, " AND n.nspname <> 'pg_catalog'\n"
684 " AND n.nspname <> 'information_schema'\n");
686 /* Match name pattern against either internal or external name */
687 if (!validateSQLNamePattern(&buf
, map_typename_pattern(pattern
),
689 "n.nspname", "t.typname",
690 "pg_catalog.format_type(t.oid, NULL)",
691 "pg_catalog.pg_type_is_visible(t.oid)",
694 termPQExpBuffer(&buf
);
698 appendPQExpBufferStr(&buf
, "ORDER BY 1, 2;");
700 res
= PSQLexec(buf
.data
);
701 termPQExpBuffer(&buf
);
705 myopt
.nullPrint
= NULL
;
706 myopt
.title
= _("List of data types");
707 myopt
.translate_header
= true;
709 printQuery(res
, &myopt
, pset
.queryFout
, false, pset
.logfile
);
716 * Map some variant type names accepted by the backend grammar into
717 * canonical type names.
719 * Helper for \dT and other functions that take typename patterns.
720 * This doesn't completely mask the fact that these names are special;
721 * for example, a pattern of "dec*" won't magically match "numeric".
722 * But it goes a long way to reduce the surprise factor.
725 map_typename_pattern(const char *pattern
)
727 static const char *const typename_map
[] = {
729 * These names are accepted by gram.y, although they are neither the
730 * "real" name seen in pg_type nor the canonical name printed by
733 "decimal", "numeric",
734 "float", "double precision",
738 * We also have to map the array names for cases where the canonical
739 * name is different from what pg_type says.
741 "bool[]", "boolean[]",
742 "decimal[]", "numeric[]",
743 "float[]", "double precision[]",
744 "float4[]", "real[]",
745 "float8[]", "double precision[]",
746 "int[]", "integer[]",
747 "int2[]", "smallint[]",
748 "int4[]", "integer[]",
749 "int8[]", "bigint[]",
750 "time[]", "time without time zone[]",
751 "timetz[]", "time with time zone[]",
752 "timestamp[]", "timestamp without time zone[]",
753 "timestamptz[]", "timestamp with time zone[]",
754 "varbit[]", "bit varying[]",
755 "varchar[]", "character varying[]",
761 for (int i
= 0; typename_map
[i
] != NULL
; i
+= 2)
763 if (pg_strcasecmp(pattern
, typename_map
[i
]) == 0)
764 return typename_map
[i
+ 1];
775 describeOperators(const char *oper_pattern
,
776 char **arg_patterns
, int num_arg_patterns
,
777 bool verbose
, bool showSystem
)
781 printQueryOpt myopt
= pset
.popt
;
783 initPQExpBuffer(&buf
);
786 * Note: before Postgres 9.1, we did not assign comments to any built-in
787 * operators, preferring to let the comment on the underlying function
788 * suffice. The coalesce() on the obj_description() calls below supports
789 * this convention by providing a fallback lookup of a comment on the
790 * operator's function. Since 9.1 there is a policy that every built-in
791 * operator should have a comment; so the coalesce() is no longer
792 * necessary so far as built-in operators are concerned. We keep it
793 * anyway, for now, because third-party modules may still be following the
796 * The support for postfix operators in this query is dead code as of
797 * Postgres 14, but we need to keep it for as long as we support talking
798 * to pre-v14 servers.
801 printfPQExpBuffer(&buf
,
802 "SELECT n.nspname as \"%s\",\n"
803 " o.oprname AS \"%s\",\n"
804 " CASE WHEN o.oprkind='l' THEN NULL ELSE pg_catalog.format_type(o.oprleft, NULL) END AS \"%s\",\n"
805 " CASE WHEN o.oprkind='r' THEN NULL ELSE pg_catalog.format_type(o.oprright, NULL) END AS \"%s\",\n"
806 " pg_catalog.format_type(o.oprresult, NULL) AS \"%s\",\n",
807 gettext_noop("Schema"),
808 gettext_noop("Name"),
809 gettext_noop("Left arg type"),
810 gettext_noop("Right arg type"),
811 gettext_noop("Result type"));
814 appendPQExpBuffer(&buf
,
815 " o.oprcode AS \"%s\",\n",
816 gettext_noop("Function"));
818 appendPQExpBuffer(&buf
,
819 " coalesce(pg_catalog.obj_description(o.oid, 'pg_operator'),\n"
820 " pg_catalog.obj_description(o.oprcode, 'pg_proc')) AS \"%s\"\n"
821 "FROM pg_catalog.pg_operator o\n"
822 " LEFT JOIN pg_catalog.pg_namespace n ON n.oid = o.oprnamespace\n",
823 gettext_noop("Description"));
825 if (num_arg_patterns
>= 2)
827 num_arg_patterns
= 2; /* ignore any additional arguments */
828 appendPQExpBufferStr(&buf
,
829 " LEFT JOIN pg_catalog.pg_type t0 ON t0.oid = o.oprleft\n"
830 " LEFT JOIN pg_catalog.pg_namespace nt0 ON nt0.oid = t0.typnamespace\n"
831 " LEFT JOIN pg_catalog.pg_type t1 ON t1.oid = o.oprright\n"
832 " LEFT JOIN pg_catalog.pg_namespace nt1 ON nt1.oid = t1.typnamespace\n");
834 else if (num_arg_patterns
== 1)
836 appendPQExpBufferStr(&buf
,
837 " LEFT JOIN pg_catalog.pg_type t0 ON t0.oid = o.oprright\n"
838 " LEFT JOIN pg_catalog.pg_namespace nt0 ON nt0.oid = t0.typnamespace\n");
841 if (!showSystem
&& !oper_pattern
)
842 appendPQExpBufferStr(&buf
, "WHERE n.nspname <> 'pg_catalog'\n"
843 " AND n.nspname <> 'information_schema'\n");
845 if (!validateSQLNamePattern(&buf
, oper_pattern
,
846 !showSystem
&& !oper_pattern
, true,
847 "n.nspname", "o.oprname", NULL
,
848 "pg_catalog.pg_operator_is_visible(o.oid)",
852 if (num_arg_patterns
== 1)
853 appendPQExpBufferStr(&buf
, " AND o.oprleft = 0\n");
855 for (int i
= 0; i
< num_arg_patterns
; i
++)
857 if (strcmp(arg_patterns
[i
], "-") != 0)
860 * Match type-name patterns against either internal or external
861 * name, like \dT. Unlike \dT, there seems no reason to
862 * discriminate against arrays or composite types.
869 snprintf(nspname
, sizeof(nspname
), "nt%d.nspname", i
);
870 snprintf(typname
, sizeof(typname
), "t%d.typname", i
);
871 snprintf(ft
, sizeof(ft
),
872 "pg_catalog.format_type(t%d.oid, NULL)", i
);
873 snprintf(tiv
, sizeof(tiv
),
874 "pg_catalog.pg_type_is_visible(t%d.oid)", i
);
875 if (!validateSQLNamePattern(&buf
,
876 map_typename_pattern(arg_patterns
[i
]),
878 nspname
, typname
, ft
, tiv
,
884 /* "-" pattern specifies no such parameter */
885 appendPQExpBuffer(&buf
, " AND t%d.typname IS NULL\n", i
);
889 appendPQExpBufferStr(&buf
, "ORDER BY 1, 2, 3, 4;");
891 res
= PSQLexec(buf
.data
);
892 termPQExpBuffer(&buf
);
896 myopt
.nullPrint
= NULL
;
897 myopt
.title
= _("List of operators");
898 myopt
.translate_header
= true;
900 printQuery(res
, &myopt
, pset
.queryFout
, false, pset
.logfile
);
906 termPQExpBuffer(&buf
);
914 * for \l, \list, and -l switch
917 listAllDbs(const char *pattern
, bool verbose
)
921 printQueryOpt myopt
= pset
.popt
;
923 initPQExpBuffer(&buf
);
925 printfPQExpBuffer(&buf
,
927 " d.datname as \"%s\",\n"
928 " pg_catalog.pg_get_userbyid(d.datdba) as \"%s\",\n"
929 " pg_catalog.pg_encoding_to_char(d.encoding) as \"%s\",\n",
930 gettext_noop("Name"),
931 gettext_noop("Owner"),
932 gettext_noop("Encoding"));
933 if (pset
.sversion
>= 150000)
934 appendPQExpBuffer(&buf
,
935 " CASE d.datlocprovider WHEN 'c' THEN 'libc' WHEN 'i' THEN 'icu' END AS \"%s\",\n",
936 gettext_noop("Locale Provider"));
938 appendPQExpBuffer(&buf
,
939 " 'libc' AS \"%s\",\n",
940 gettext_noop("Locale Provider"));
941 appendPQExpBuffer(&buf
,
942 " d.datcollate as \"%s\",\n"
943 " d.datctype as \"%s\",\n",
944 gettext_noop("Collate"),
945 gettext_noop("Ctype"));
946 if (pset
.sversion
>= 150000)
947 appendPQExpBuffer(&buf
,
948 " d.daticulocale as \"%s\",\n",
949 gettext_noop("ICU Locale"));
951 appendPQExpBuffer(&buf
,
952 " NULL as \"%s\",\n",
953 gettext_noop("ICU Locale"));
954 if (pset
.sversion
>= 160000)
955 appendPQExpBuffer(&buf
,
956 " d.daticurules as \"%s\",\n",
957 gettext_noop("ICU Rules"));
959 appendPQExpBuffer(&buf
,
960 " NULL as \"%s\",\n",
961 gettext_noop("ICU Rules"));
962 appendPQExpBufferStr(&buf
, " ");
963 printACLColumn(&buf
, "d.datacl");
965 appendPQExpBuffer(&buf
,
966 ",\n CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT')\n"
967 " THEN pg_catalog.pg_size_pretty(pg_catalog.pg_database_size(d.datname))\n"
968 " ELSE 'No Access'\n"
970 ",\n t.spcname as \"%s\""
971 ",\n pg_catalog.shobj_description(d.oid, 'pg_database') as \"%s\"",
972 gettext_noop("Size"),
973 gettext_noop("Tablespace"),
974 gettext_noop("Description"));
975 appendPQExpBufferStr(&buf
,
976 "\nFROM pg_catalog.pg_database d\n");
978 appendPQExpBufferStr(&buf
,
979 " JOIN pg_catalog.pg_tablespace t on d.dattablespace = t.oid\n");
983 if (!validateSQLNamePattern(&buf
, pattern
, false, false,
984 NULL
, "d.datname", NULL
, NULL
,
987 termPQExpBuffer(&buf
);
992 appendPQExpBufferStr(&buf
, "ORDER BY 1;");
993 res
= PSQLexec(buf
.data
);
994 termPQExpBuffer(&buf
);
998 myopt
.nullPrint
= NULL
;
999 myopt
.title
= _("List of databases");
1000 myopt
.translate_header
= true;
1002 printQuery(res
, &myopt
, pset
.queryFout
, false, pset
.logfile
);
1010 * List Tables' Grant/Revoke Permissions
1011 * \z (now also \dp -- perhaps more mnemonic)
1014 permissionsList(const char *pattern
, bool showSystem
)
1016 PQExpBufferData buf
;
1018 printQueryOpt myopt
= pset
.popt
;
1019 static const bool translate_columns
[] = {false, false, true, false, false, false};
1021 initPQExpBuffer(&buf
);
1024 * we ignore indexes and toast tables since they have no meaningful rights
1026 printfPQExpBuffer(&buf
,
1027 "SELECT n.nspname as \"%s\",\n"
1028 " c.relname as \"%s\",\n"
1030 " WHEN " CppAsString2(RELKIND_RELATION
) " THEN '%s'"
1031 " WHEN " CppAsString2(RELKIND_VIEW
) " THEN '%s'"
1032 " WHEN " CppAsString2(RELKIND_MATVIEW
) " THEN '%s'"
1033 " WHEN " CppAsString2(RELKIND_SEQUENCE
) " THEN '%s'"
1034 " WHEN " CppAsString2(RELKIND_FOREIGN_TABLE
) " THEN '%s'"
1035 " WHEN " CppAsString2(RELKIND_PARTITIONED_TABLE
) " THEN '%s'"
1038 gettext_noop("Schema"),
1039 gettext_noop("Name"),
1040 gettext_noop("table"),
1041 gettext_noop("view"),
1042 gettext_noop("materialized view"),
1043 gettext_noop("sequence"),
1044 gettext_noop("foreign table"),
1045 gettext_noop("partitioned table"),
1046 gettext_noop("Type"));
1048 printACLColumn(&buf
, "c.relacl");
1050 appendPQExpBuffer(&buf
,
1051 ",\n pg_catalog.array_to_string(ARRAY(\n"
1052 " SELECT attname || E':\\n ' || pg_catalog.array_to_string(attacl, E'\\n ')\n"
1053 " FROM pg_catalog.pg_attribute a\n"
1054 " WHERE attrelid = c.oid AND NOT attisdropped AND attacl IS NOT NULL\n"
1055 " ), E'\\n') AS \"%s\"",
1056 gettext_noop("Column privileges"));
1058 if (pset
.sversion
>= 90500 && pset
.sversion
< 100000)
1059 appendPQExpBuffer(&buf
,
1060 ",\n pg_catalog.array_to_string(ARRAY(\n"
1062 " || CASE WHEN polcmd != '*' THEN\n"
1063 " E' (' || polcmd::pg_catalog.text || E'):'\n"
1066 " || CASE WHEN polqual IS NOT NULL THEN\n"
1067 " E'\\n (u): ' || pg_catalog.pg_get_expr(polqual, polrelid)\n"
1070 " || CASE WHEN polwithcheck IS NOT NULL THEN\n"
1071 " E'\\n (c): ' || pg_catalog.pg_get_expr(polwithcheck, polrelid)\n"
1074 " || CASE WHEN polroles <> '{0}' THEN\n"
1075 " E'\\n to: ' || pg_catalog.array_to_string(\n"
1078 " FROM pg_catalog.pg_roles\n"
1079 " WHERE oid = ANY (polroles)\n"
1084 " FROM pg_catalog.pg_policy pol\n"
1085 " WHERE polrelid = c.oid), E'\\n')\n"
1087 gettext_noop("Policies"));
1089 if (pset
.sversion
>= 100000)
1090 appendPQExpBuffer(&buf
,
1091 ",\n pg_catalog.array_to_string(ARRAY(\n"
1093 " || CASE WHEN NOT polpermissive THEN\n"
1094 " E' (RESTRICTIVE)'\n"
1096 " || CASE WHEN polcmd != '*' THEN\n"
1097 " E' (' || polcmd::pg_catalog.text || E'):'\n"
1100 " || CASE WHEN polqual IS NOT NULL THEN\n"
1101 " E'\\n (u): ' || pg_catalog.pg_get_expr(polqual, polrelid)\n"
1104 " || CASE WHEN polwithcheck IS NOT NULL THEN\n"
1105 " E'\\n (c): ' || pg_catalog.pg_get_expr(polwithcheck, polrelid)\n"
1108 " || CASE WHEN polroles <> '{0}' THEN\n"
1109 " E'\\n to: ' || pg_catalog.array_to_string(\n"
1112 " FROM pg_catalog.pg_roles\n"
1113 " WHERE oid = ANY (polroles)\n"
1118 " FROM pg_catalog.pg_policy pol\n"
1119 " WHERE polrelid = c.oid), E'\\n')\n"
1121 gettext_noop("Policies"));
1123 appendPQExpBufferStr(&buf
, "\nFROM pg_catalog.pg_class c\n"
1124 " LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace\n"
1125 "WHERE c.relkind IN ("
1126 CppAsString2(RELKIND_RELATION
) ","
1127 CppAsString2(RELKIND_VIEW
) ","
1128 CppAsString2(RELKIND_MATVIEW
) ","
1129 CppAsString2(RELKIND_SEQUENCE
) ","
1130 CppAsString2(RELKIND_FOREIGN_TABLE
) ","
1131 CppAsString2(RELKIND_PARTITIONED_TABLE
) ")\n");
1133 if (!showSystem
&& !pattern
)
1134 appendPQExpBufferStr(&buf
, " AND n.nspname <> 'pg_catalog'\n"
1135 " AND n.nspname <> 'information_schema'\n");
1137 if (!validateSQLNamePattern(&buf
, pattern
, true, false,
1138 "n.nspname", "c.relname", NULL
,
1139 "pg_catalog.pg_table_is_visible(c.oid)",
1143 appendPQExpBufferStr(&buf
, "ORDER BY 1, 2;");
1145 res
= PSQLexec(buf
.data
);
1149 myopt
.nullPrint
= NULL
;
1150 printfPQExpBuffer(&buf
, _("Access privileges"));
1151 myopt
.title
= buf
.data
;
1152 myopt
.translate_header
= true;
1153 myopt
.translate_columns
= translate_columns
;
1154 myopt
.n_translate_columns
= lengthof(translate_columns
);
1156 printQuery(res
, &myopt
, pset
.queryFout
, false, pset
.logfile
);
1158 termPQExpBuffer(&buf
);
1163 termPQExpBuffer(&buf
);
1171 * List Default ACLs. The pattern can match either schema or role name.
1174 listDefaultACLs(const char *pattern
)
1176 PQExpBufferData buf
;
1178 printQueryOpt myopt
= pset
.popt
;
1179 static const bool translate_columns
[] = {false, false, true, false};
1181 initPQExpBuffer(&buf
);
1183 printfPQExpBuffer(&buf
,
1184 "SELECT pg_catalog.pg_get_userbyid(d.defaclrole) AS \"%s\",\n"
1185 " n.nspname AS \"%s\",\n"
1186 " 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"
1188 gettext_noop("Owner"),
1189 gettext_noop("Schema"),
1191 gettext_noop("table"),
1193 gettext_noop("sequence"),
1195 gettext_noop("function"),
1197 gettext_noop("type"),
1198 DEFACLOBJ_NAMESPACE
,
1199 gettext_noop("schema"),
1200 gettext_noop("Type"));
1202 printACLColumn(&buf
, "d.defaclacl");
1204 appendPQExpBufferStr(&buf
, "\nFROM pg_catalog.pg_default_acl d\n"
1205 " LEFT JOIN pg_catalog.pg_namespace n ON n.oid = d.defaclnamespace\n");
1207 if (!validateSQLNamePattern(&buf
, pattern
, false, false,
1210 "pg_catalog.pg_get_userbyid(d.defaclrole)",
1215 appendPQExpBufferStr(&buf
, "ORDER BY 1, 2, 3;");
1217 res
= PSQLexec(buf
.data
);
1221 myopt
.nullPrint
= NULL
;
1222 printfPQExpBuffer(&buf
, _("Default access privileges"));
1223 myopt
.title
= buf
.data
;
1224 myopt
.translate_header
= true;
1225 myopt
.translate_columns
= translate_columns
;
1226 myopt
.n_translate_columns
= lengthof(translate_columns
);
1228 printQuery(res
, &myopt
, pset
.queryFout
, false, pset
.logfile
);
1230 termPQExpBuffer(&buf
);
1235 termPQExpBuffer(&buf
);
1241 * Get object comments
1245 * Note: This command only lists comments for object types which do not have
1246 * their comments displayed by their own backslash commands. The following
1247 * types of objects will be displayed: constraint, operator class,
1248 * operator family, rule, and trigger.
1252 objectDescription(const char *pattern
, bool showSystem
)
1254 PQExpBufferData buf
;
1256 printQueryOpt myopt
= pset
.popt
;
1257 static const bool translate_columns
[] = {false, false, true, false};
1259 initPQExpBuffer(&buf
);
1261 appendPQExpBuffer(&buf
,
1262 "SELECT DISTINCT tt.nspname AS \"%s\", tt.name AS \"%s\", tt.object AS \"%s\", d.description AS \"%s\"\n"
1264 gettext_noop("Schema"),
1265 gettext_noop("Name"),
1266 gettext_noop("Object"),
1267 gettext_noop("Description"));
1269 /* Table constraint descriptions */
1270 appendPQExpBuffer(&buf
,
1271 " SELECT pgc.oid as oid, pgc.tableoid AS tableoid,\n"
1272 " n.nspname as nspname,\n"
1273 " CAST(pgc.conname AS pg_catalog.text) as name,"
1274 " CAST('%s' AS pg_catalog.text) as object\n"
1275 " FROM pg_catalog.pg_constraint pgc\n"
1276 " JOIN pg_catalog.pg_class c "
1277 "ON c.oid = pgc.conrelid\n"
1278 " LEFT JOIN pg_catalog.pg_namespace n "
1279 " ON n.oid = c.relnamespace\n",
1280 gettext_noop("table constraint"));
1282 if (!showSystem
&& !pattern
)
1283 appendPQExpBufferStr(&buf
, "WHERE n.nspname <> 'pg_catalog'\n"
1284 " AND n.nspname <> 'information_schema'\n");
1286 if (!validateSQLNamePattern(&buf
, pattern
, !showSystem
&& !pattern
,
1287 false, "n.nspname", "pgc.conname", NULL
,
1288 "pg_catalog.pg_table_is_visible(c.oid)",
1292 /* Domain constraint descriptions */
1293 appendPQExpBuffer(&buf
,
1295 " SELECT pgc.oid as oid, pgc.tableoid AS tableoid,\n"
1296 " n.nspname as nspname,\n"
1297 " CAST(pgc.conname AS pg_catalog.text) as name,"
1298 " CAST('%s' AS pg_catalog.text) as object\n"
1299 " FROM pg_catalog.pg_constraint pgc\n"
1300 " JOIN pg_catalog.pg_type t "
1301 "ON t.oid = pgc.contypid\n"
1302 " LEFT JOIN pg_catalog.pg_namespace n "
1303 " ON n.oid = t.typnamespace\n",
1304 gettext_noop("domain constraint"));
1306 if (!showSystem
&& !pattern
)
1307 appendPQExpBufferStr(&buf
, "WHERE n.nspname <> 'pg_catalog'\n"
1308 " AND n.nspname <> 'information_schema'\n");
1310 if (!validateSQLNamePattern(&buf
, pattern
, !showSystem
&& !pattern
,
1311 false, "n.nspname", "pgc.conname", NULL
,
1312 "pg_catalog.pg_type_is_visible(t.oid)",
1316 /* Operator class descriptions */
1317 appendPQExpBuffer(&buf
,
1319 " SELECT o.oid as oid, o.tableoid as tableoid,\n"
1320 " n.nspname as nspname,\n"
1321 " CAST(o.opcname AS pg_catalog.text) as name,\n"
1322 " CAST('%s' AS pg_catalog.text) as object\n"
1323 " FROM pg_catalog.pg_opclass o\n"
1324 " JOIN pg_catalog.pg_am am ON "
1325 "o.opcmethod = am.oid\n"
1326 " JOIN pg_catalog.pg_namespace n ON "
1327 "n.oid = o.opcnamespace\n",
1328 gettext_noop("operator class"));
1330 if (!showSystem
&& !pattern
)
1331 appendPQExpBufferStr(&buf
, " AND n.nspname <> 'pg_catalog'\n"
1332 " AND n.nspname <> 'information_schema'\n");
1334 if (!validateSQLNamePattern(&buf
, pattern
, true, false,
1335 "n.nspname", "o.opcname", NULL
,
1336 "pg_catalog.pg_opclass_is_visible(o.oid)",
1340 /* Operator family descriptions */
1341 appendPQExpBuffer(&buf
,
1343 " SELECT opf.oid as oid, opf.tableoid as tableoid,\n"
1344 " n.nspname as nspname,\n"
1345 " CAST(opf.opfname AS pg_catalog.text) AS name,\n"
1346 " CAST('%s' AS pg_catalog.text) as object\n"
1347 " FROM pg_catalog.pg_opfamily opf\n"
1348 " JOIN pg_catalog.pg_am am "
1349 "ON opf.opfmethod = am.oid\n"
1350 " JOIN pg_catalog.pg_namespace n "
1351 "ON opf.opfnamespace = n.oid\n",
1352 gettext_noop("operator family"));
1354 if (!showSystem
&& !pattern
)
1355 appendPQExpBufferStr(&buf
, " AND n.nspname <> 'pg_catalog'\n"
1356 " AND n.nspname <> 'information_schema'\n");
1358 if (!validateSQLNamePattern(&buf
, pattern
, true, false,
1359 "n.nspname", "opf.opfname", NULL
,
1360 "pg_catalog.pg_opfamily_is_visible(opf.oid)",
1364 /* Rule descriptions (ignore rules for views) */
1365 appendPQExpBuffer(&buf
,
1367 " SELECT r.oid as oid, r.tableoid as tableoid,\n"
1368 " n.nspname as nspname,\n"
1369 " CAST(r.rulename AS pg_catalog.text) as name,"
1370 " CAST('%s' AS pg_catalog.text) as object\n"
1371 " FROM pg_catalog.pg_rewrite r\n"
1372 " JOIN pg_catalog.pg_class c ON c.oid = r.ev_class\n"
1373 " LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace\n"
1374 " WHERE r.rulename != '_RETURN'\n",
1375 gettext_noop("rule"));
1377 if (!showSystem
&& !pattern
)
1378 appendPQExpBufferStr(&buf
, " AND n.nspname <> 'pg_catalog'\n"
1379 " AND n.nspname <> 'information_schema'\n");
1381 if (!validateSQLNamePattern(&buf
, pattern
, true, false,
1382 "n.nspname", "r.rulename", NULL
,
1383 "pg_catalog.pg_table_is_visible(c.oid)",
1387 /* Trigger descriptions */
1388 appendPQExpBuffer(&buf
,
1390 " SELECT t.oid as oid, t.tableoid as tableoid,\n"
1391 " n.nspname as nspname,\n"
1392 " CAST(t.tgname AS pg_catalog.text) as name,"
1393 " CAST('%s' AS pg_catalog.text) as object\n"
1394 " FROM pg_catalog.pg_trigger t\n"
1395 " JOIN pg_catalog.pg_class c ON c.oid = t.tgrelid\n"
1396 " LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace\n",
1397 gettext_noop("trigger"));
1399 if (!showSystem
&& !pattern
)
1400 appendPQExpBufferStr(&buf
, "WHERE n.nspname <> 'pg_catalog'\n"
1401 " AND n.nspname <> 'information_schema'\n");
1403 if (!validateSQLNamePattern(&buf
, pattern
, !showSystem
&& !pattern
, false,
1404 "n.nspname", "t.tgname", NULL
,
1405 "pg_catalog.pg_table_is_visible(c.oid)",
1409 appendPQExpBufferStr(&buf
,
1411 " JOIN pg_catalog.pg_description d ON (tt.oid = d.objoid AND tt.tableoid = d.classoid AND d.objsubid = 0)\n");
1413 appendPQExpBufferStr(&buf
, "ORDER BY 1, 2, 3;");
1415 res
= PSQLexec(buf
.data
);
1416 termPQExpBuffer(&buf
);
1420 myopt
.nullPrint
= NULL
;
1421 myopt
.title
= _("Object descriptions");
1422 myopt
.translate_header
= true;
1423 myopt
.translate_columns
= translate_columns
;
1424 myopt
.n_translate_columns
= lengthof(translate_columns
);
1426 printQuery(res
, &myopt
, pset
.queryFout
, false, pset
.logfile
);
1432 termPQExpBuffer(&buf
);
1438 * describeTableDetails (for \d)
1440 * This routine finds the tables to be displayed, and calls
1441 * describeOneTableDetails for each one.
1443 * verbose: if true, this is \d+
1446 describeTableDetails(const char *pattern
, bool verbose
, bool showSystem
)
1448 PQExpBufferData buf
;
1452 initPQExpBuffer(&buf
);
1454 printfPQExpBuffer(&buf
,
1458 "FROM pg_catalog.pg_class c\n"
1459 " LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace\n");
1461 if (!showSystem
&& !pattern
)
1462 appendPQExpBufferStr(&buf
, "WHERE n.nspname <> 'pg_catalog'\n"
1463 " AND n.nspname <> 'information_schema'\n");
1465 if (!validateSQLNamePattern(&buf
, pattern
, !showSystem
&& !pattern
, false,
1466 "n.nspname", "c.relname", NULL
,
1467 "pg_catalog.pg_table_is_visible(c.oid)",
1470 termPQExpBuffer(&buf
);
1474 appendPQExpBufferStr(&buf
, "ORDER BY 2, 3;");
1476 res
= PSQLexec(buf
.data
);
1477 termPQExpBuffer(&buf
);
1481 if (PQntuples(res
) == 0)
1486 pg_log_error("Did not find any relation named \"%s\".",
1489 pg_log_error("Did not find any relations.");
1495 for (i
= 0; i
< PQntuples(res
); i
++)
1498 const char *nspname
;
1499 const char *relname
;
1501 oid
= PQgetvalue(res
, i
, 0);
1502 nspname
= PQgetvalue(res
, i
, 1);
1503 relname
= PQgetvalue(res
, i
, 2);
1505 if (!describeOneTableDetails(nspname
, relname
, oid
, verbose
))
1522 * describeOneTableDetails (for \d)
1524 * Unfortunately, the information presented here is so complicated that it
1525 * cannot be done in a single query. So we have to assemble the printed table
1526 * by hand and pass it to the underlying printTable() function.
1529 describeOneTableDetails(const char *schemaname
,
1530 const char *relationname
,
1534 bool retval
= false;
1535 PQExpBufferData buf
;
1536 PGresult
*res
= NULL
;
1537 printTableOpt myopt
= pset
.popt
.topt
;
1538 printTableContent cont
;
1539 bool printTableInitialized
= false;
1541 char *view_def
= NULL
;
1543 PQExpBufferData title
;
1544 PQExpBufferData tmpbuf
;
1546 int attname_col
= -1, /* column indexes in "res" */
1549 attnotnull_col
= -1,
1551 attidentity_col
= -1,
1552 attgenerated_col
= -1,
1553 isindexkey_col
= -1,
1556 attstorage_col
= -1,
1557 attcompression_col
= -1,
1558 attstattarget_col
= -1,
1569 bool forcerowsecurity
;
1575 char relpersistence
;
1579 bool show_column_details
= false;
1581 myopt
.default_footer
= false;
1582 /* This output looks confusing in expanded mode. */
1583 myopt
.expanded
= false;
1585 initPQExpBuffer(&buf
);
1586 initPQExpBuffer(&title
);
1587 initPQExpBuffer(&tmpbuf
);
1589 /* Get general table info */
1590 if (pset
.sversion
>= 120000)
1592 printfPQExpBuffer(&buf
,
1593 "SELECT c.relchecks, c.relkind, c.relhasindex, c.relhasrules, "
1594 "c.relhastriggers, c.relrowsecurity, c.relforcerowsecurity, "
1595 "false AS relhasoids, c.relispartition, %s, c.reltablespace, "
1596 "CASE WHEN c.reloftype = 0 THEN '' ELSE c.reloftype::pg_catalog.regtype::pg_catalog.text END, "
1597 "c.relpersistence, c.relreplident, am.amname\n"
1598 "FROM pg_catalog.pg_class c\n "
1599 "LEFT JOIN pg_catalog.pg_class tc ON (c.reltoastrelid = tc.oid)\n"
1600 "LEFT JOIN pg_catalog.pg_am am ON (c.relam = am.oid)\n"
1601 "WHERE c.oid = '%s';",
1603 "pg_catalog.array_to_string(c.reloptions || "
1604 "array(select 'toast.' || x from pg_catalog.unnest(tc.reloptions) x), ', ')\n"
1608 else if (pset
.sversion
>= 100000)
1610 printfPQExpBuffer(&buf
,
1611 "SELECT c.relchecks, c.relkind, c.relhasindex, c.relhasrules, "
1612 "c.relhastriggers, c.relrowsecurity, c.relforcerowsecurity, "
1613 "c.relhasoids, c.relispartition, %s, c.reltablespace, "
1614 "CASE WHEN c.reloftype = 0 THEN '' ELSE c.reloftype::pg_catalog.regtype::pg_catalog.text END, "
1615 "c.relpersistence, c.relreplident\n"
1616 "FROM pg_catalog.pg_class c\n "
1617 "LEFT JOIN pg_catalog.pg_class tc ON (c.reltoastrelid = tc.oid)\n"
1618 "WHERE c.oid = '%s';",
1620 "pg_catalog.array_to_string(c.reloptions || "
1621 "array(select 'toast.' || x from pg_catalog.unnest(tc.reloptions) x), ', ')\n"
1625 else if (pset
.sversion
>= 90500)
1627 printfPQExpBuffer(&buf
,
1628 "SELECT c.relchecks, c.relkind, c.relhasindex, c.relhasrules, "
1629 "c.relhastriggers, c.relrowsecurity, c.relforcerowsecurity, "
1630 "c.relhasoids, false as relispartition, %s, c.reltablespace, "
1631 "CASE WHEN c.reloftype = 0 THEN '' ELSE c.reloftype::pg_catalog.regtype::pg_catalog.text END, "
1632 "c.relpersistence, c.relreplident\n"
1633 "FROM pg_catalog.pg_class c\n "
1634 "LEFT JOIN pg_catalog.pg_class tc ON (c.reltoastrelid = tc.oid)\n"
1635 "WHERE c.oid = '%s';",
1637 "pg_catalog.array_to_string(c.reloptions || "
1638 "array(select 'toast.' || x from pg_catalog.unnest(tc.reloptions) x), ', ')\n"
1642 else if (pset
.sversion
>= 90400)
1644 printfPQExpBuffer(&buf
,
1645 "SELECT c.relchecks, c.relkind, c.relhasindex, c.relhasrules, "
1646 "c.relhastriggers, false, false, c.relhasoids, "
1647 "false as relispartition, %s, c.reltablespace, "
1648 "CASE WHEN c.reloftype = 0 THEN '' ELSE c.reloftype::pg_catalog.regtype::pg_catalog.text END, "
1649 "c.relpersistence, c.relreplident\n"
1650 "FROM pg_catalog.pg_class c\n "
1651 "LEFT JOIN pg_catalog.pg_class tc ON (c.reltoastrelid = tc.oid)\n"
1652 "WHERE c.oid = '%s';",
1654 "pg_catalog.array_to_string(c.reloptions || "
1655 "array(select 'toast.' || x from pg_catalog.unnest(tc.reloptions) x), ', ')\n"
1661 printfPQExpBuffer(&buf
,
1662 "SELECT c.relchecks, c.relkind, c.relhasindex, c.relhasrules, "
1663 "c.relhastriggers, false, false, c.relhasoids, "
1664 "false as relispartition, %s, c.reltablespace, "
1665 "CASE WHEN c.reloftype = 0 THEN '' ELSE c.reloftype::pg_catalog.regtype::pg_catalog.text END, "
1666 "c.relpersistence\n"
1667 "FROM pg_catalog.pg_class c\n "
1668 "LEFT JOIN pg_catalog.pg_class tc ON (c.reltoastrelid = tc.oid)\n"
1669 "WHERE c.oid = '%s';",
1671 "pg_catalog.array_to_string(c.reloptions || "
1672 "array(select 'toast.' || x from pg_catalog.unnest(tc.reloptions) x), ', ')\n"
1677 res
= PSQLexec(buf
.data
);
1681 /* Did we get anything? */
1682 if (PQntuples(res
) == 0)
1685 pg_log_error("Did not find any relation with OID %s.", oid
);
1689 tableinfo
.checks
= atoi(PQgetvalue(res
, 0, 0));
1690 tableinfo
.relkind
= *(PQgetvalue(res
, 0, 1));
1691 tableinfo
.hasindex
= strcmp(PQgetvalue(res
, 0, 2), "t") == 0;
1692 tableinfo
.hasrules
= strcmp(PQgetvalue(res
, 0, 3), "t") == 0;
1693 tableinfo
.hastriggers
= strcmp(PQgetvalue(res
, 0, 4), "t") == 0;
1694 tableinfo
.rowsecurity
= strcmp(PQgetvalue(res
, 0, 5), "t") == 0;
1695 tableinfo
.forcerowsecurity
= strcmp(PQgetvalue(res
, 0, 6), "t") == 0;
1696 tableinfo
.hasoids
= strcmp(PQgetvalue(res
, 0, 7), "t") == 0;
1697 tableinfo
.ispartition
= strcmp(PQgetvalue(res
, 0, 8), "t") == 0;
1698 tableinfo
.reloptions
= pg_strdup(PQgetvalue(res
, 0, 9));
1699 tableinfo
.tablespace
= atooid(PQgetvalue(res
, 0, 10));
1700 tableinfo
.reloftype
= (strcmp(PQgetvalue(res
, 0, 11), "") != 0) ?
1701 pg_strdup(PQgetvalue(res
, 0, 11)) : NULL
;
1702 tableinfo
.relpersistence
= *(PQgetvalue(res
, 0, 12));
1703 tableinfo
.relreplident
= (pset
.sversion
>= 90400) ?
1704 *(PQgetvalue(res
, 0, 13)) : 'd';
1705 if (pset
.sversion
>= 120000)
1706 tableinfo
.relam
= PQgetisnull(res
, 0, 14) ?
1707 (char *) NULL
: pg_strdup(PQgetvalue(res
, 0, 14));
1709 tableinfo
.relam
= NULL
;
1714 * If it's a sequence, deal with it here separately.
1716 if (tableinfo
.relkind
== RELKIND_SEQUENCE
)
1718 PGresult
*result
= NULL
;
1719 printQueryOpt myopt
= pset
.popt
;
1720 char *footers
[2] = {NULL
, NULL
};
1722 if (pset
.sversion
>= 100000)
1724 printfPQExpBuffer(&buf
,
1725 "SELECT pg_catalog.format_type(seqtypid, NULL) AS \"%s\",\n"
1726 " seqstart AS \"%s\",\n"
1727 " seqmin AS \"%s\",\n"
1728 " seqmax AS \"%s\",\n"
1729 " seqincrement AS \"%s\",\n"
1730 " CASE WHEN seqcycle THEN '%s' ELSE '%s' END AS \"%s\",\n"
1731 " seqcache AS \"%s\"\n",
1732 gettext_noop("Type"),
1733 gettext_noop("Start"),
1734 gettext_noop("Minimum"),
1735 gettext_noop("Maximum"),
1736 gettext_noop("Increment"),
1737 gettext_noop("yes"),
1739 gettext_noop("Cycles?"),
1740 gettext_noop("Cache"));
1741 appendPQExpBuffer(&buf
,
1742 "FROM pg_catalog.pg_sequence\n"
1743 "WHERE seqrelid = '%s';",
1748 printfPQExpBuffer(&buf
,
1749 "SELECT 'bigint' AS \"%s\",\n"
1750 " start_value AS \"%s\",\n"
1751 " min_value AS \"%s\",\n"
1752 " max_value AS \"%s\",\n"
1753 " increment_by AS \"%s\",\n"
1754 " CASE WHEN is_cycled THEN '%s' ELSE '%s' END AS \"%s\",\n"
1755 " cache_value AS \"%s\"\n",
1756 gettext_noop("Type"),
1757 gettext_noop("Start"),
1758 gettext_noop("Minimum"),
1759 gettext_noop("Maximum"),
1760 gettext_noop("Increment"),
1761 gettext_noop("yes"),
1763 gettext_noop("Cycles?"),
1764 gettext_noop("Cache"));
1765 appendPQExpBuffer(&buf
, "FROM %s", fmtId(schemaname
));
1766 /* must be separate because fmtId isn't reentrant */
1767 appendPQExpBuffer(&buf
, ".%s;", fmtId(relationname
));
1770 res
= PSQLexec(buf
.data
);
1774 /* Get the column that owns this sequence */
1775 printfPQExpBuffer(&buf
, "SELECT pg_catalog.quote_ident(nspname) || '.' ||"
1776 "\n pg_catalog.quote_ident(relname) || '.' ||"
1777 "\n pg_catalog.quote_ident(attname),"
1779 "\nFROM pg_catalog.pg_class c"
1780 "\nINNER JOIN pg_catalog.pg_depend d ON c.oid=d.refobjid"
1781 "\nINNER JOIN pg_catalog.pg_namespace n ON n.oid=c.relnamespace"
1782 "\nINNER JOIN pg_catalog.pg_attribute a ON ("
1783 "\n a.attrelid=c.oid AND"
1784 "\n a.attnum=d.refobjsubid)"
1785 "\nWHERE d.classid='pg_catalog.pg_class'::pg_catalog.regclass"
1786 "\n AND d.refclassid='pg_catalog.pg_class'::pg_catalog.regclass"
1787 "\n AND d.objid='%s'"
1788 "\n AND d.deptype IN ('a', 'i')",
1791 result
= PSQLexec(buf
.data
);
1794 * If we get no rows back, don't show anything (obviously). We should
1795 * never get more than one row back, but if we do, just ignore it and
1796 * don't print anything.
1800 else if (PQntuples(result
) == 1)
1802 switch (PQgetvalue(result
, 0, 1)[0])
1805 footers
[0] = psprintf(_("Owned by: %s"),
1806 PQgetvalue(result
, 0, 0));
1809 footers
[0] = psprintf(_("Sequence for identity column: %s"),
1810 PQgetvalue(result
, 0, 0));
1816 if (tableinfo
.relpersistence
== 'u')
1817 printfPQExpBuffer(&title
, _("Unlogged sequence \"%s.%s\""),
1818 schemaname
, relationname
);
1820 printfPQExpBuffer(&title
, _("Sequence \"%s.%s\""),
1821 schemaname
, relationname
);
1823 myopt
.footers
= footers
;
1824 myopt
.topt
.default_footer
= false;
1825 myopt
.title
= title
.data
;
1826 myopt
.translate_header
= true;
1828 printQuery(res
, &myopt
, pset
.queryFout
, false, pset
.logfile
);
1833 goto error_return
; /* not an error, just return early */
1836 /* Identify whether we should print collation, nullable, default vals */
1837 if (tableinfo
.relkind
== RELKIND_RELATION
||
1838 tableinfo
.relkind
== RELKIND_VIEW
||
1839 tableinfo
.relkind
== RELKIND_MATVIEW
||
1840 tableinfo
.relkind
== RELKIND_FOREIGN_TABLE
||
1841 tableinfo
.relkind
== RELKIND_COMPOSITE_TYPE
||
1842 tableinfo
.relkind
== RELKIND_PARTITIONED_TABLE
)
1843 show_column_details
= true;
1846 * Get per-column info
1848 * Since the set of query columns we need varies depending on relkind and
1849 * server version, we compute all the column numbers on-the-fly. Column
1850 * number variables for columns not fetched are left as -1; this avoids
1851 * duplicative test logic below.
1854 printfPQExpBuffer(&buf
, "SELECT a.attname");
1855 attname_col
= cols
++;
1856 appendPQExpBufferStr(&buf
, ",\n pg_catalog.format_type(a.atttypid, a.atttypmod)");
1857 atttype_col
= cols
++;
1859 if (show_column_details
)
1861 /* use "pretty" mode for expression to avoid excessive parentheses */
1862 appendPQExpBufferStr(&buf
,
1863 ",\n (SELECT pg_catalog.pg_get_expr(d.adbin, d.adrelid, true)"
1864 "\n FROM pg_catalog.pg_attrdef d"
1865 "\n WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef)"
1866 ",\n a.attnotnull");
1867 attrdef_col
= cols
++;
1868 attnotnull_col
= cols
++;
1869 appendPQExpBufferStr(&buf
, ",\n (SELECT c.collname FROM pg_catalog.pg_collation c, pg_catalog.pg_type t\n"
1870 " WHERE c.oid = a.attcollation AND t.oid = a.atttypid AND a.attcollation <> t.typcollation) AS attcollation");
1871 attcoll_col
= cols
++;
1872 if (pset
.sversion
>= 100000)
1873 appendPQExpBufferStr(&buf
, ",\n a.attidentity");
1875 appendPQExpBufferStr(&buf
, ",\n ''::pg_catalog.char AS attidentity");
1876 attidentity_col
= cols
++;
1877 if (pset
.sversion
>= 120000)
1878 appendPQExpBufferStr(&buf
, ",\n a.attgenerated");
1880 appendPQExpBufferStr(&buf
, ",\n ''::pg_catalog.char AS attgenerated");
1881 attgenerated_col
= cols
++;
1883 if (tableinfo
.relkind
== RELKIND_INDEX
||
1884 tableinfo
.relkind
== RELKIND_PARTITIONED_INDEX
)
1886 if (pset
.sversion
>= 110000)
1888 appendPQExpBuffer(&buf
, ",\n CASE WHEN a.attnum <= (SELECT i.indnkeyatts FROM pg_catalog.pg_index i WHERE i.indexrelid = '%s') THEN '%s' ELSE '%s' END AS is_key",
1890 gettext_noop("yes"),
1891 gettext_noop("no"));
1892 isindexkey_col
= cols
++;
1894 appendPQExpBufferStr(&buf
, ",\n pg_catalog.pg_get_indexdef(a.attrelid, a.attnum, TRUE) AS indexdef");
1895 indexdef_col
= cols
++;
1897 /* FDW options for foreign table column */
1898 if (tableinfo
.relkind
== RELKIND_FOREIGN_TABLE
)
1900 appendPQExpBufferStr(&buf
, ",\n CASE WHEN attfdwoptions IS NULL THEN '' ELSE "
1901 " '(' || pg_catalog.array_to_string(ARRAY(SELECT pg_catalog.quote_ident(option_name) || ' ' || pg_catalog.quote_literal(option_value) FROM "
1902 " pg_catalog.pg_options_to_table(attfdwoptions)), ', ') || ')' END AS attfdwoptions");
1903 fdwopts_col
= cols
++;
1907 appendPQExpBufferStr(&buf
, ",\n a.attstorage");
1908 attstorage_col
= cols
++;
1910 /* compression info, if relevant to relkind */
1911 if (pset
.sversion
>= 140000 &&
1912 !pset
.hide_compression
&&
1913 (tableinfo
.relkind
== RELKIND_RELATION
||
1914 tableinfo
.relkind
== RELKIND_PARTITIONED_TABLE
||
1915 tableinfo
.relkind
== RELKIND_MATVIEW
))
1917 appendPQExpBufferStr(&buf
, ",\n a.attcompression AS attcompression");
1918 attcompression_col
= cols
++;
1921 /* stats target, if relevant to relkind */
1922 if (tableinfo
.relkind
== RELKIND_RELATION
||
1923 tableinfo
.relkind
== RELKIND_INDEX
||
1924 tableinfo
.relkind
== RELKIND_PARTITIONED_INDEX
||
1925 tableinfo
.relkind
== RELKIND_MATVIEW
||
1926 tableinfo
.relkind
== RELKIND_FOREIGN_TABLE
||
1927 tableinfo
.relkind
== RELKIND_PARTITIONED_TABLE
)
1929 appendPQExpBufferStr(&buf
, ",\n CASE WHEN a.attstattarget=-1 THEN NULL ELSE a.attstattarget END AS attstattarget");
1930 attstattarget_col
= cols
++;
1934 * In 9.0+, we have column comments for: relations, views, composite
1935 * types, and foreign tables (cf. CommentObject() in comment.c).
1937 if (tableinfo
.relkind
== RELKIND_RELATION
||
1938 tableinfo
.relkind
== RELKIND_VIEW
||
1939 tableinfo
.relkind
== RELKIND_MATVIEW
||
1940 tableinfo
.relkind
== RELKIND_FOREIGN_TABLE
||
1941 tableinfo
.relkind
== RELKIND_COMPOSITE_TYPE
||
1942 tableinfo
.relkind
== RELKIND_PARTITIONED_TABLE
)
1944 appendPQExpBufferStr(&buf
, ",\n pg_catalog.col_description(a.attrelid, a.attnum)");
1945 attdescr_col
= cols
++;
1949 appendPQExpBufferStr(&buf
, "\nFROM pg_catalog.pg_attribute a");
1950 appendPQExpBuffer(&buf
, "\nWHERE a.attrelid = '%s' AND a.attnum > 0 AND NOT a.attisdropped", oid
);
1951 appendPQExpBufferStr(&buf
, "\nORDER BY a.attnum;");
1953 res
= PSQLexec(buf
.data
);
1956 numrows
= PQntuples(res
);
1959 switch (tableinfo
.relkind
)
1961 case RELKIND_RELATION
:
1962 if (tableinfo
.relpersistence
== 'u')
1963 printfPQExpBuffer(&title
, _("Unlogged table \"%s.%s\""),
1964 schemaname
, relationname
);
1966 printfPQExpBuffer(&title
, _("Table \"%s.%s\""),
1967 schemaname
, relationname
);
1970 printfPQExpBuffer(&title
, _("View \"%s.%s\""),
1971 schemaname
, relationname
);
1973 case RELKIND_MATVIEW
:
1974 if (tableinfo
.relpersistence
== 'u')
1975 printfPQExpBuffer(&title
, _("Unlogged materialized view \"%s.%s\""),
1976 schemaname
, relationname
);
1978 printfPQExpBuffer(&title
, _("Materialized view \"%s.%s\""),
1979 schemaname
, relationname
);
1982 if (tableinfo
.relpersistence
== 'u')
1983 printfPQExpBuffer(&title
, _("Unlogged index \"%s.%s\""),
1984 schemaname
, relationname
);
1986 printfPQExpBuffer(&title
, _("Index \"%s.%s\""),
1987 schemaname
, relationname
);
1989 case RELKIND_PARTITIONED_INDEX
:
1990 if (tableinfo
.relpersistence
== 'u')
1991 printfPQExpBuffer(&title
, _("Unlogged partitioned index \"%s.%s\""),
1992 schemaname
, relationname
);
1994 printfPQExpBuffer(&title
, _("Partitioned index \"%s.%s\""),
1995 schemaname
, relationname
);
1997 case RELKIND_TOASTVALUE
:
1998 printfPQExpBuffer(&title
, _("TOAST table \"%s.%s\""),
1999 schemaname
, relationname
);
2001 case RELKIND_COMPOSITE_TYPE
:
2002 printfPQExpBuffer(&title
, _("Composite type \"%s.%s\""),
2003 schemaname
, relationname
);
2005 case RELKIND_FOREIGN_TABLE
:
2006 printfPQExpBuffer(&title
, _("Foreign table \"%s.%s\""),
2007 schemaname
, relationname
);
2009 case RELKIND_PARTITIONED_TABLE
:
2010 if (tableinfo
.relpersistence
== 'u')
2011 printfPQExpBuffer(&title
, _("Unlogged partitioned table \"%s.%s\""),
2012 schemaname
, relationname
);
2014 printfPQExpBuffer(&title
, _("Partitioned table \"%s.%s\""),
2015 schemaname
, relationname
);
2018 /* untranslated unknown relkind */
2019 printfPQExpBuffer(&title
, "?%c? \"%s.%s\"",
2020 tableinfo
.relkind
, schemaname
, relationname
);
2024 /* Fill headers[] with the names of the columns we will output */
2026 headers
[cols
++] = gettext_noop("Column");
2027 headers
[cols
++] = gettext_noop("Type");
2028 if (show_column_details
)
2030 headers
[cols
++] = gettext_noop("Collation");
2031 headers
[cols
++] = gettext_noop("Nullable");
2032 headers
[cols
++] = gettext_noop("Default");
2034 if (isindexkey_col
>= 0)
2035 headers
[cols
++] = gettext_noop("Key?");
2036 if (indexdef_col
>= 0)
2037 headers
[cols
++] = gettext_noop("Definition");
2038 if (fdwopts_col
>= 0)
2039 headers
[cols
++] = gettext_noop("FDW options");
2040 if (attstorage_col
>= 0)
2041 headers
[cols
++] = gettext_noop("Storage");
2042 if (attcompression_col
>= 0)
2043 headers
[cols
++] = gettext_noop("Compression");
2044 if (attstattarget_col
>= 0)
2045 headers
[cols
++] = gettext_noop("Stats target");
2046 if (attdescr_col
>= 0)
2047 headers
[cols
++] = gettext_noop("Description");
2049 Assert(cols
<= lengthof(headers
));
2051 printTableInit(&cont
, &myopt
, title
.data
, cols
, numrows
);
2052 printTableInitialized
= true;
2054 for (i
= 0; i
< cols
; i
++)
2055 printTableAddHeader(&cont
, headers
[i
], true, 'l');
2057 /* Generate table cells to be printed */
2058 for (i
= 0; i
< numrows
; i
++)
2061 printTableAddCell(&cont
, PQgetvalue(res
, i
, attname_col
), false, false);
2064 printTableAddCell(&cont
, PQgetvalue(res
, i
, atttype_col
), false, false);
2066 /* Collation, Nullable, Default */
2067 if (show_column_details
)
2072 bool mustfree
= false;
2074 printTableAddCell(&cont
, PQgetvalue(res
, i
, attcoll_col
), false, false);
2076 printTableAddCell(&cont
,
2077 strcmp(PQgetvalue(res
, i
, attnotnull_col
), "t") == 0 ? "not null" : "",
2080 identity
= PQgetvalue(res
, i
, attidentity_col
);
2081 generated
= PQgetvalue(res
, i
, attgenerated_col
);
2083 if (identity
[0] == ATTRIBUTE_IDENTITY_ALWAYS
)
2084 default_str
= "generated always as identity";
2085 else if (identity
[0] == ATTRIBUTE_IDENTITY_BY_DEFAULT
)
2086 default_str
= "generated by default as identity";
2087 else if (generated
[0] == ATTRIBUTE_GENERATED_STORED
)
2089 default_str
= psprintf("generated always as (%s) stored",
2090 PQgetvalue(res
, i
, attrdef_col
));
2094 default_str
= PQgetvalue(res
, i
, attrdef_col
);
2096 printTableAddCell(&cont
, default_str
, false, mustfree
);
2099 /* Info for index columns */
2100 if (isindexkey_col
>= 0)
2101 printTableAddCell(&cont
, PQgetvalue(res
, i
, isindexkey_col
), true, false);
2102 if (indexdef_col
>= 0)
2103 printTableAddCell(&cont
, PQgetvalue(res
, i
, indexdef_col
), false, false);
2105 /* FDW options for foreign table columns */
2106 if (fdwopts_col
>= 0)
2107 printTableAddCell(&cont
, PQgetvalue(res
, i
, fdwopts_col
), false, false);
2109 /* Storage mode, if relevant */
2110 if (attstorage_col
>= 0)
2112 char *storage
= PQgetvalue(res
, i
, attstorage_col
);
2114 /* these strings are literal in our syntax, so not translated. */
2115 printTableAddCell(&cont
, (storage
[0] == 'p' ? "plain" :
2116 (storage
[0] == 'm' ? "main" :
2117 (storage
[0] == 'x' ? "extended" :
2118 (storage
[0] == 'e' ? "external" :
2123 /* Column compression, if relevant */
2124 if (attcompression_col
>= 0)
2126 char *compression
= PQgetvalue(res
, i
, attcompression_col
);
2128 /* these strings are literal in our syntax, so not translated. */
2129 printTableAddCell(&cont
, (compression
[0] == 'p' ? "pglz" :
2130 (compression
[0] == 'l' ? "lz4" :
2131 (compression
[0] == '\0' ? "" :
2136 /* Statistics target, if the relkind supports this feature */
2137 if (attstattarget_col
>= 0)
2138 printTableAddCell(&cont
, PQgetvalue(res
, i
, attstattarget_col
),
2141 /* Column comments, if the relkind supports this feature */
2142 if (attdescr_col
>= 0)
2143 printTableAddCell(&cont
, PQgetvalue(res
, i
, attdescr_col
),
2149 if (tableinfo
.ispartition
)
2151 /* Footer information for a partition child table */
2154 printfPQExpBuffer(&buf
,
2155 "SELECT inhparent::pg_catalog.regclass,\n"
2156 " pg_catalog.pg_get_expr(c.relpartbound, c.oid),\n ");
2158 appendPQExpBufferStr(&buf
,
2159 pset
.sversion
>= 140000 ? "inhdetachpending" :
2160 "false as inhdetachpending");
2162 /* If verbose, also request the partition constraint definition */
2164 appendPQExpBufferStr(&buf
,
2165 ",\n pg_catalog.pg_get_partition_constraintdef(c.oid)");
2166 appendPQExpBuffer(&buf
,
2167 "\nFROM pg_catalog.pg_class c"
2168 " JOIN pg_catalog.pg_inherits i"
2169 " ON c.oid = inhrelid"
2170 "\nWHERE c.oid = '%s';", oid
);
2171 result
= PSQLexec(buf
.data
);
2175 if (PQntuples(result
) > 0)
2177 char *parent_name
= PQgetvalue(result
, 0, 0);
2178 char *partdef
= PQgetvalue(result
, 0, 1);
2179 char *detached
= PQgetvalue(result
, 0, 2);
2181 printfPQExpBuffer(&tmpbuf
, _("Partition of: %s %s%s"), parent_name
,
2183 strcmp(detached
, "t") == 0 ? " DETACH PENDING" : "");
2184 printTableAddFooter(&cont
, tmpbuf
.data
);
2188 char *partconstraintdef
= NULL
;
2190 if (!PQgetisnull(result
, 0, 3))
2191 partconstraintdef
= PQgetvalue(result
, 0, 3);
2192 /* If there isn't any constraint, show that explicitly */
2193 if (partconstraintdef
== NULL
|| partconstraintdef
[0] == '\0')
2194 printfPQExpBuffer(&tmpbuf
, _("No partition constraint"));
2196 printfPQExpBuffer(&tmpbuf
, _("Partition constraint: %s"),
2198 printTableAddFooter(&cont
, tmpbuf
.data
);
2204 if (tableinfo
.relkind
== RELKIND_PARTITIONED_TABLE
)
2206 /* Footer information for a partitioned table (partitioning parent) */
2209 printfPQExpBuffer(&buf
,
2210 "SELECT pg_catalog.pg_get_partkeydef('%s'::pg_catalog.oid);",
2212 result
= PSQLexec(buf
.data
);
2216 if (PQntuples(result
) == 1)
2218 char *partkeydef
= PQgetvalue(result
, 0, 0);
2220 printfPQExpBuffer(&tmpbuf
, _("Partition key: %s"), partkeydef
);
2221 printTableAddFooter(&cont
, tmpbuf
.data
);
2226 if (tableinfo
.relkind
== RELKIND_TOASTVALUE
)
2228 /* For a TOAST table, print name of owning table */
2231 printfPQExpBuffer(&buf
,
2232 "SELECT n.nspname, c.relname\n"
2233 "FROM pg_catalog.pg_class c"
2234 " JOIN pg_catalog.pg_namespace n"
2235 " ON n.oid = c.relnamespace\n"
2236 "WHERE reltoastrelid = '%s';", oid
);
2237 result
= PSQLexec(buf
.data
);
2241 if (PQntuples(result
) == 1)
2243 char *schemaname
= PQgetvalue(result
, 0, 0);
2244 char *relname
= PQgetvalue(result
, 0, 1);
2246 printfPQExpBuffer(&tmpbuf
, _("Owning table: \"%s.%s\""),
2247 schemaname
, relname
);
2248 printTableAddFooter(&cont
, tmpbuf
.data
);
2253 if (tableinfo
.relkind
== RELKIND_INDEX
||
2254 tableinfo
.relkind
== RELKIND_PARTITIONED_INDEX
)
2256 /* Footer information about an index */
2259 printfPQExpBuffer(&buf
,
2260 "SELECT i.indisunique, i.indisprimary, i.indisclustered, "
2262 " (NOT i.indimmediate) AND "
2263 "EXISTS (SELECT 1 FROM pg_catalog.pg_constraint "
2264 "WHERE conrelid = i.indrelid AND "
2265 "conindid = i.indexrelid AND "
2266 "contype IN ('p','u','x') AND "
2267 "condeferrable) AS condeferrable,\n"
2268 " (NOT i.indimmediate) AND "
2269 "EXISTS (SELECT 1 FROM pg_catalog.pg_constraint "
2270 "WHERE conrelid = i.indrelid AND "
2271 "conindid = i.indexrelid AND "
2272 "contype IN ('p','u','x') AND "
2273 "condeferred) AS condeferred,\n");
2275 if (pset
.sversion
>= 90400)
2276 appendPQExpBufferStr(&buf
, "i.indisreplident,\n");
2278 appendPQExpBufferStr(&buf
, "false AS indisreplident,\n");
2280 if (pset
.sversion
>= 150000)
2281 appendPQExpBufferStr(&buf
, "i.indnullsnotdistinct,\n");
2283 appendPQExpBufferStr(&buf
, "false AS indnullsnotdistinct,\n");
2285 appendPQExpBuffer(&buf
, " a.amname, c2.relname, "
2286 "pg_catalog.pg_get_expr(i.indpred, i.indrelid, true)\n"
2287 "FROM pg_catalog.pg_index i, pg_catalog.pg_class c, pg_catalog.pg_class c2, pg_catalog.pg_am a\n"
2288 "WHERE i.indexrelid = c.oid AND c.oid = '%s' AND c.relam = a.oid\n"
2289 "AND i.indrelid = c2.oid;",
2292 result
= PSQLexec(buf
.data
);
2295 else if (PQntuples(result
) != 1)
2302 char *indisunique
= PQgetvalue(result
, 0, 0);
2303 char *indisprimary
= PQgetvalue(result
, 0, 1);
2304 char *indisclustered
= PQgetvalue(result
, 0, 2);
2305 char *indisvalid
= PQgetvalue(result
, 0, 3);
2306 char *deferrable
= PQgetvalue(result
, 0, 4);
2307 char *deferred
= PQgetvalue(result
, 0, 5);
2308 char *indisreplident
= PQgetvalue(result
, 0, 6);
2309 char *indnullsnotdistinct
= PQgetvalue(result
, 0, 7);
2310 char *indamname
= PQgetvalue(result
, 0, 8);
2311 char *indtable
= PQgetvalue(result
, 0, 9);
2312 char *indpred
= PQgetvalue(result
, 0, 10);
2314 if (strcmp(indisprimary
, "t") == 0)
2315 printfPQExpBuffer(&tmpbuf
, _("primary key, "));
2316 else if (strcmp(indisunique
, "t") == 0)
2318 printfPQExpBuffer(&tmpbuf
, _("unique"));
2319 if (strcmp(indnullsnotdistinct
, "t") == 0)
2320 appendPQExpBufferStr(&tmpbuf
, _(" nulls not distinct"));
2321 appendPQExpBufferStr(&tmpbuf
, _(", "));
2324 resetPQExpBuffer(&tmpbuf
);
2325 appendPQExpBuffer(&tmpbuf
, "%s, ", indamname
);
2327 /* we assume here that index and table are in same schema */
2328 appendPQExpBuffer(&tmpbuf
, _("for table \"%s.%s\""),
2329 schemaname
, indtable
);
2331 if (strlen(indpred
))
2332 appendPQExpBuffer(&tmpbuf
, _(", predicate (%s)"), indpred
);
2334 if (strcmp(indisclustered
, "t") == 0)
2335 appendPQExpBufferStr(&tmpbuf
, _(", clustered"));
2337 if (strcmp(indisvalid
, "t") != 0)
2338 appendPQExpBufferStr(&tmpbuf
, _(", invalid"));
2340 if (strcmp(deferrable
, "t") == 0)
2341 appendPQExpBufferStr(&tmpbuf
, _(", deferrable"));
2343 if (strcmp(deferred
, "t") == 0)
2344 appendPQExpBufferStr(&tmpbuf
, _(", initially deferred"));
2346 if (strcmp(indisreplident
, "t") == 0)
2347 appendPQExpBufferStr(&tmpbuf
, _(", replica identity"));
2349 printTableAddFooter(&cont
, tmpbuf
.data
);
2352 * If it's a partitioned index, we'll print the tablespace below
2354 if (tableinfo
.relkind
== RELKIND_INDEX
)
2355 add_tablespace_footer(&cont
, tableinfo
.relkind
,
2356 tableinfo
.tablespace
, true);
2361 /* If you add relkinds here, see also "Finish printing..." stanza below */
2362 else if (tableinfo
.relkind
== RELKIND_RELATION
||
2363 tableinfo
.relkind
== RELKIND_MATVIEW
||
2364 tableinfo
.relkind
== RELKIND_FOREIGN_TABLE
||
2365 tableinfo
.relkind
== RELKIND_PARTITIONED_TABLE
||
2366 tableinfo
.relkind
== RELKIND_PARTITIONED_INDEX
||
2367 tableinfo
.relkind
== RELKIND_TOASTVALUE
)
2369 /* Footer information about a table */
2370 PGresult
*result
= NULL
;
2374 if (tableinfo
.hasindex
)
2376 printfPQExpBuffer(&buf
,
2377 "SELECT c2.relname, i.indisprimary, i.indisunique, "
2378 "i.indisclustered, i.indisvalid, "
2379 "pg_catalog.pg_get_indexdef(i.indexrelid, 0, true),\n "
2380 "pg_catalog.pg_get_constraintdef(con.oid, true), "
2381 "contype, condeferrable, condeferred");
2382 if (pset
.sversion
>= 90400)
2383 appendPQExpBufferStr(&buf
, ", i.indisreplident");
2385 appendPQExpBufferStr(&buf
, ", false AS indisreplident");
2386 appendPQExpBufferStr(&buf
, ", c2.reltablespace");
2387 appendPQExpBuffer(&buf
,
2388 "\nFROM pg_catalog.pg_class c, pg_catalog.pg_class c2, pg_catalog.pg_index i\n"
2389 " LEFT JOIN pg_catalog.pg_constraint con ON (conrelid = i.indrelid AND conindid = i.indexrelid AND contype IN ('p','u','x'))\n"
2390 "WHERE c.oid = '%s' AND c.oid = i.indrelid AND i.indexrelid = c2.oid\n"
2391 "ORDER BY i.indisprimary DESC, c2.relname;",
2393 result
= PSQLexec(buf
.data
);
2397 tuples
= PQntuples(result
);
2401 printTableAddFooter(&cont
, _("Indexes:"));
2402 for (i
= 0; i
< tuples
; i
++)
2404 /* untranslated index name */
2405 printfPQExpBuffer(&buf
, " \"%s\"",
2406 PQgetvalue(result
, i
, 0));
2408 /* If exclusion constraint, print the constraintdef */
2409 if (strcmp(PQgetvalue(result
, i
, 7), "x") == 0)
2411 appendPQExpBuffer(&buf
, " %s",
2412 PQgetvalue(result
, i
, 6));
2416 const char *indexdef
;
2417 const char *usingpos
;
2419 /* Label as primary key or unique (but not both) */
2420 if (strcmp(PQgetvalue(result
, i
, 1), "t") == 0)
2421 appendPQExpBufferStr(&buf
, " PRIMARY KEY,");
2422 else if (strcmp(PQgetvalue(result
, i
, 2), "t") == 0)
2424 if (strcmp(PQgetvalue(result
, i
, 7), "u") == 0)
2425 appendPQExpBufferStr(&buf
, " UNIQUE CONSTRAINT,");
2427 appendPQExpBufferStr(&buf
, " UNIQUE,");
2430 /* Everything after "USING" is echoed verbatim */
2431 indexdef
= PQgetvalue(result
, i
, 5);
2432 usingpos
= strstr(indexdef
, " USING ");
2434 indexdef
= usingpos
+ 7;
2435 appendPQExpBuffer(&buf
, " %s", indexdef
);
2437 /* Need these for deferrable PK/UNIQUE indexes */
2438 if (strcmp(PQgetvalue(result
, i
, 8), "t") == 0)
2439 appendPQExpBufferStr(&buf
, " DEFERRABLE");
2441 if (strcmp(PQgetvalue(result
, i
, 9), "t") == 0)
2442 appendPQExpBufferStr(&buf
, " INITIALLY DEFERRED");
2445 /* Add these for all cases */
2446 if (strcmp(PQgetvalue(result
, i
, 3), "t") == 0)
2447 appendPQExpBufferStr(&buf
, " CLUSTER");
2449 if (strcmp(PQgetvalue(result
, i
, 4), "t") != 0)
2450 appendPQExpBufferStr(&buf
, " INVALID");
2452 if (strcmp(PQgetvalue(result
, i
, 10), "t") == 0)
2453 appendPQExpBufferStr(&buf
, " REPLICA IDENTITY");
2455 printTableAddFooter(&cont
, buf
.data
);
2457 /* Print tablespace of the index on the same line */
2458 add_tablespace_footer(&cont
, RELKIND_INDEX
,
2459 atooid(PQgetvalue(result
, i
, 11)),
2466 /* print table (and column) check constraints */
2467 if (tableinfo
.checks
)
2469 printfPQExpBuffer(&buf
,
2470 "SELECT r.conname, "
2471 "pg_catalog.pg_get_constraintdef(r.oid, true)\n"
2472 "FROM pg_catalog.pg_constraint r\n"
2473 "WHERE r.conrelid = '%s' AND r.contype = 'c'\n"
2476 result
= PSQLexec(buf
.data
);
2480 tuples
= PQntuples(result
);
2484 printTableAddFooter(&cont
, _("Check constraints:"));
2485 for (i
= 0; i
< tuples
; i
++)
2487 /* untranslated constraint name and def */
2488 printfPQExpBuffer(&buf
, " \"%s\" %s",
2489 PQgetvalue(result
, i
, 0),
2490 PQgetvalue(result
, i
, 1));
2492 printTableAddFooter(&cont
, buf
.data
);
2499 * Print foreign-key constraints (there are none if no triggers,
2500 * except if the table is partitioned, in which case the triggers
2501 * appear in the partitions)
2503 if (tableinfo
.hastriggers
||
2504 tableinfo
.relkind
== RELKIND_PARTITIONED_TABLE
)
2506 if (pset
.sversion
>= 120000 &&
2507 (tableinfo
.ispartition
|| tableinfo
.relkind
== RELKIND_PARTITIONED_TABLE
))
2510 * Put the constraints defined in this table first, followed
2511 * by the constraints defined in ancestor partitioned tables.
2513 printfPQExpBuffer(&buf
,
2514 "SELECT conrelid = '%s'::pg_catalog.regclass AS sametable,\n"
2516 " pg_catalog.pg_get_constraintdef(oid, true) AS condef,\n"
2517 " conrelid::pg_catalog.regclass AS ontable\n"
2518 " FROM pg_catalog.pg_constraint,\n"
2519 " pg_catalog.pg_partition_ancestors('%s')\n"
2520 " WHERE conrelid = relid AND contype = 'f' AND conparentid = 0\n"
2521 "ORDER BY sametable DESC, conname;",
2526 printfPQExpBuffer(&buf
,
2527 "SELECT true as sametable, conname,\n"
2528 " pg_catalog.pg_get_constraintdef(r.oid, true) as condef,\n"
2529 " conrelid::pg_catalog.regclass AS ontable\n"
2530 "FROM pg_catalog.pg_constraint r\n"
2531 "WHERE r.conrelid = '%s' AND r.contype = 'f'\n",
2534 if (pset
.sversion
>= 120000)
2535 appendPQExpBufferStr(&buf
, " AND conparentid = 0\n");
2536 appendPQExpBufferStr(&buf
, "ORDER BY conname");
2539 result
= PSQLexec(buf
.data
);
2543 tuples
= PQntuples(result
);
2547 int i_sametable
= PQfnumber(result
, "sametable"),
2548 i_conname
= PQfnumber(result
, "conname"),
2549 i_condef
= PQfnumber(result
, "condef"),
2550 i_ontable
= PQfnumber(result
, "ontable");
2552 printTableAddFooter(&cont
, _("Foreign-key constraints:"));
2553 for (i
= 0; i
< tuples
; i
++)
2556 * Print untranslated constraint name and definition. Use
2557 * a "TABLE tab" prefix when the constraint is defined in
2558 * a parent partitioned table.
2560 if (strcmp(PQgetvalue(result
, i
, i_sametable
), "f") == 0)
2561 printfPQExpBuffer(&buf
, " TABLE \"%s\" CONSTRAINT \"%s\" %s",
2562 PQgetvalue(result
, i
, i_ontable
),
2563 PQgetvalue(result
, i
, i_conname
),
2564 PQgetvalue(result
, i
, i_condef
));
2566 printfPQExpBuffer(&buf
, " \"%s\" %s",
2567 PQgetvalue(result
, i
, i_conname
),
2568 PQgetvalue(result
, i
, i_condef
));
2570 printTableAddFooter(&cont
, buf
.data
);
2576 /* print incoming foreign-key references */
2577 if (tableinfo
.hastriggers
||
2578 tableinfo
.relkind
== RELKIND_PARTITIONED_TABLE
)
2580 if (pset
.sversion
>= 120000)
2582 printfPQExpBuffer(&buf
,
2583 "SELECT conname, conrelid::pg_catalog.regclass AS ontable,\n"
2584 " pg_catalog.pg_get_constraintdef(oid, true) AS condef\n"
2585 " FROM pg_catalog.pg_constraint c\n"
2586 " WHERE confrelid IN (SELECT pg_catalog.pg_partition_ancestors('%s')\n"
2587 " UNION ALL VALUES ('%s'::pg_catalog.regclass))\n"
2588 " AND contype = 'f' AND conparentid = 0\n"
2589 "ORDER BY conname;",
2594 printfPQExpBuffer(&buf
,
2595 "SELECT conname, conrelid::pg_catalog.regclass AS ontable,\n"
2596 " pg_catalog.pg_get_constraintdef(oid, true) AS condef\n"
2597 " FROM pg_catalog.pg_constraint\n"
2598 " WHERE confrelid = %s AND contype = 'f'\n"
2599 "ORDER BY conname;",
2603 result
= PSQLexec(buf
.data
);
2607 tuples
= PQntuples(result
);
2611 int i_conname
= PQfnumber(result
, "conname"),
2612 i_ontable
= PQfnumber(result
, "ontable"),
2613 i_condef
= PQfnumber(result
, "condef");
2615 printTableAddFooter(&cont
, _("Referenced by:"));
2616 for (i
= 0; i
< tuples
; i
++)
2618 printfPQExpBuffer(&buf
, " TABLE \"%s\" CONSTRAINT \"%s\" %s",
2619 PQgetvalue(result
, i
, i_ontable
),
2620 PQgetvalue(result
, i
, i_conname
),
2621 PQgetvalue(result
, i
, i_condef
));
2623 printTableAddFooter(&cont
, buf
.data
);
2629 /* print any row-level policies */
2630 if (pset
.sversion
>= 90500)
2632 printfPQExpBuffer(&buf
, "SELECT pol.polname,");
2633 if (pset
.sversion
>= 100000)
2634 appendPQExpBufferStr(&buf
,
2635 " pol.polpermissive,\n");
2637 appendPQExpBufferStr(&buf
,
2638 " 't' as polpermissive,\n");
2639 appendPQExpBuffer(&buf
,
2640 " CASE WHEN pol.polroles = '{0}' THEN NULL ELSE pg_catalog.array_to_string(array(select rolname from pg_catalog.pg_roles where oid = any (pol.polroles) order by 1),',') END,\n"
2641 " pg_catalog.pg_get_expr(pol.polqual, pol.polrelid),\n"
2642 " pg_catalog.pg_get_expr(pol.polwithcheck, pol.polrelid),\n"
2643 " CASE pol.polcmd\n"
2644 " WHEN 'r' THEN 'SELECT'\n"
2645 " WHEN 'a' THEN 'INSERT'\n"
2646 " WHEN 'w' THEN 'UPDATE'\n"
2647 " WHEN 'd' THEN 'DELETE'\n"
2649 "FROM pg_catalog.pg_policy pol\n"
2650 "WHERE pol.polrelid = '%s' ORDER BY 1;",
2653 result
= PSQLexec(buf
.data
);
2657 tuples
= PQntuples(result
);
2660 * Handle cases where RLS is enabled and there are policies, or
2661 * there aren't policies, or RLS isn't enabled but there are
2664 if (tableinfo
.rowsecurity
&& !tableinfo
.forcerowsecurity
&& tuples
> 0)
2665 printTableAddFooter(&cont
, _("Policies:"));
2667 if (tableinfo
.rowsecurity
&& tableinfo
.forcerowsecurity
&& tuples
> 0)
2668 printTableAddFooter(&cont
, _("Policies (forced row security enabled):"));
2670 if (tableinfo
.rowsecurity
&& !tableinfo
.forcerowsecurity
&& tuples
== 0)
2671 printTableAddFooter(&cont
, _("Policies (row security enabled): (none)"));
2673 if (tableinfo
.rowsecurity
&& tableinfo
.forcerowsecurity
&& tuples
== 0)
2674 printTableAddFooter(&cont
, _("Policies (forced row security enabled): (none)"));
2676 if (!tableinfo
.rowsecurity
&& tuples
> 0)
2677 printTableAddFooter(&cont
, _("Policies (row security disabled):"));
2679 /* Might be an empty set - that's ok */
2680 for (i
= 0; i
< tuples
; i
++)
2682 printfPQExpBuffer(&buf
, " POLICY \"%s\"",
2683 PQgetvalue(result
, i
, 0));
2685 if (*(PQgetvalue(result
, i
, 1)) == 'f')
2686 appendPQExpBufferStr(&buf
, " AS RESTRICTIVE");
2688 if (!PQgetisnull(result
, i
, 5))
2689 appendPQExpBuffer(&buf
, " FOR %s",
2690 PQgetvalue(result
, i
, 5));
2692 if (!PQgetisnull(result
, i
, 2))
2694 appendPQExpBuffer(&buf
, "\n TO %s",
2695 PQgetvalue(result
, i
, 2));
2698 if (!PQgetisnull(result
, i
, 3))
2699 appendPQExpBuffer(&buf
, "\n USING (%s)",
2700 PQgetvalue(result
, i
, 3));
2702 if (!PQgetisnull(result
, i
, 4))
2703 appendPQExpBuffer(&buf
, "\n WITH CHECK (%s)",
2704 PQgetvalue(result
, i
, 4));
2706 printTableAddFooter(&cont
, buf
.data
);
2711 /* print any extended statistics */
2712 if (pset
.sversion
>= 140000)
2714 printfPQExpBuffer(&buf
,
2716 "stxrelid::pg_catalog.regclass, "
2717 "stxnamespace::pg_catalog.regnamespace::pg_catalog.text AS nsp, "
2719 "pg_catalog.pg_get_statisticsobjdef_columns(oid) AS columns,\n"
2720 " 'd' = any(stxkind) AS ndist_enabled,\n"
2721 " 'f' = any(stxkind) AS deps_enabled,\n"
2722 " 'm' = any(stxkind) AS mcv_enabled,\n"
2724 "FROM pg_catalog.pg_statistic_ext\n"
2725 "WHERE stxrelid = '%s'\n"
2726 "ORDER BY nsp, stxname;",
2729 result
= PSQLexec(buf
.data
);
2733 tuples
= PQntuples(result
);
2737 printTableAddFooter(&cont
, _("Statistics objects:"));
2739 for (i
= 0; i
< tuples
; i
++)
2741 bool gotone
= false;
2743 bool has_dependencies
;
2748 has_ndistinct
= (strcmp(PQgetvalue(result
, i
, 5), "t") == 0);
2749 has_dependencies
= (strcmp(PQgetvalue(result
, i
, 6), "t") == 0);
2750 has_mcv
= (strcmp(PQgetvalue(result
, i
, 7), "t") == 0);
2752 printfPQExpBuffer(&buf
, " ");
2754 /* statistics object name (qualified with namespace) */
2755 appendPQExpBuffer(&buf
, "\"%s.%s\"",
2756 PQgetvalue(result
, i
, 2),
2757 PQgetvalue(result
, i
, 3));
2760 * When printing kinds we ignore expression statistics,
2761 * which are used only internally and can't be specified
2762 * by user. We don't print the kinds when none are
2763 * specified (in which case it has to be statistics on a
2764 * single expr) or when all are specified (in which case
2765 * we assume it's expanded by CREATE STATISTICS).
2767 has_all
= (has_ndistinct
&& has_dependencies
&& has_mcv
);
2768 has_some
= (has_ndistinct
|| has_dependencies
|| has_mcv
);
2770 if (has_some
&& !has_all
)
2772 appendPQExpBufferStr(&buf
, " (");
2777 appendPQExpBufferStr(&buf
, "ndistinct");
2781 if (has_dependencies
)
2783 appendPQExpBuffer(&buf
, "%sdependencies", gotone
? ", " : "");
2789 appendPQExpBuffer(&buf
, "%smcv", gotone
? ", " : "");
2792 appendPQExpBufferChar(&buf
, ')');
2795 appendPQExpBuffer(&buf
, " ON %s FROM %s",
2796 PQgetvalue(result
, i
, 4),
2797 PQgetvalue(result
, i
, 1));
2799 /* Show the stats target if it's not default */
2800 if (strcmp(PQgetvalue(result
, i
, 8), "-1") != 0)
2801 appendPQExpBuffer(&buf
, "; STATISTICS %s",
2802 PQgetvalue(result
, i
, 8));
2804 printTableAddFooter(&cont
, buf
.data
);
2809 else if (pset
.sversion
>= 100000)
2811 printfPQExpBuffer(&buf
,
2813 "stxrelid::pg_catalog.regclass, "
2814 "stxnamespace::pg_catalog.regnamespace AS nsp, "
2816 " (SELECT pg_catalog.string_agg(pg_catalog.quote_ident(attname),', ')\n"
2817 " FROM pg_catalog.unnest(stxkeys) s(attnum)\n"
2818 " JOIN pg_catalog.pg_attribute a ON (stxrelid = a.attrelid AND\n"
2819 " a.attnum = s.attnum AND NOT attisdropped)) AS columns,\n"
2820 " 'd' = any(stxkind) AS ndist_enabled,\n"
2821 " 'f' = any(stxkind) AS deps_enabled,\n"
2822 " 'm' = any(stxkind) AS mcv_enabled,\n");
2824 if (pset
.sversion
>= 130000)
2825 appendPQExpBufferStr(&buf
, " stxstattarget\n");
2827 appendPQExpBufferStr(&buf
, " -1 AS stxstattarget\n");
2828 appendPQExpBuffer(&buf
, "FROM pg_catalog.pg_statistic_ext\n"
2829 "WHERE stxrelid = '%s'\n"
2833 result
= PSQLexec(buf
.data
);
2837 tuples
= PQntuples(result
);
2841 printTableAddFooter(&cont
, _("Statistics objects:"));
2843 for (i
= 0; i
< tuples
; i
++)
2845 bool gotone
= false;
2847 printfPQExpBuffer(&buf
, " ");
2849 /* statistics object name (qualified with namespace) */
2850 appendPQExpBuffer(&buf
, "\"%s.%s\" (",
2851 PQgetvalue(result
, i
, 2),
2852 PQgetvalue(result
, i
, 3));
2855 if (strcmp(PQgetvalue(result
, i
, 5), "t") == 0)
2857 appendPQExpBufferStr(&buf
, "ndistinct");
2861 if (strcmp(PQgetvalue(result
, i
, 6), "t") == 0)
2863 appendPQExpBuffer(&buf
, "%sdependencies", gotone
? ", " : "");
2867 if (strcmp(PQgetvalue(result
, i
, 7), "t") == 0)
2869 appendPQExpBuffer(&buf
, "%smcv", gotone
? ", " : "");
2872 appendPQExpBuffer(&buf
, ") ON %s FROM %s",
2873 PQgetvalue(result
, i
, 4),
2874 PQgetvalue(result
, i
, 1));
2876 /* Show the stats target if it's not default */
2877 if (strcmp(PQgetvalue(result
, i
, 8), "-1") != 0)
2878 appendPQExpBuffer(&buf
, "; STATISTICS %s",
2879 PQgetvalue(result
, i
, 8));
2881 printTableAddFooter(&cont
, buf
.data
);
2888 if (tableinfo
.hasrules
&& tableinfo
.relkind
!= RELKIND_MATVIEW
)
2890 printfPQExpBuffer(&buf
,
2891 "SELECT r.rulename, trim(trailing ';' from pg_catalog.pg_get_ruledef(r.oid, true)), "
2893 "FROM pg_catalog.pg_rewrite r\n"
2894 "WHERE r.ev_class = '%s' ORDER BY 1;",
2896 result
= PSQLexec(buf
.data
);
2900 tuples
= PQntuples(result
);
2907 for (category
= 0; category
< 4; category
++)
2909 have_heading
= false;
2911 for (i
= 0; i
< tuples
; i
++)
2913 const char *ruledef
;
2914 bool list_rule
= false;
2919 if (*PQgetvalue(result
, i
, 2) == 'O')
2923 if (*PQgetvalue(result
, i
, 2) == 'D')
2927 if (*PQgetvalue(result
, i
, 2) == 'A')
2931 if (*PQgetvalue(result
, i
, 2) == 'R')
2943 printfPQExpBuffer(&buf
, _("Rules:"));
2946 printfPQExpBuffer(&buf
, _("Disabled rules:"));
2949 printfPQExpBuffer(&buf
, _("Rules firing always:"));
2952 printfPQExpBuffer(&buf
, _("Rules firing on replica only:"));
2955 printTableAddFooter(&cont
, buf
.data
);
2956 have_heading
= true;
2959 /* Everything after "CREATE RULE" is echoed verbatim */
2960 ruledef
= PQgetvalue(result
, i
, 1);
2962 printfPQExpBuffer(&buf
, " %s", ruledef
);
2963 printTableAddFooter(&cont
, buf
.data
);
2970 /* print any publications */
2971 if (pset
.sversion
>= 100000)
2973 if (pset
.sversion
>= 150000)
2975 printfPQExpBuffer(&buf
,
2979 "FROM pg_catalog.pg_publication p\n"
2980 " JOIN pg_catalog.pg_publication_namespace pn ON p.oid = pn.pnpubid\n"
2981 " JOIN pg_catalog.pg_class pc ON pc.relnamespace = pn.pnnspid\n"
2982 "WHERE pc.oid ='%s' and pg_catalog.pg_relation_is_publishable('%s')\n"
2985 " , pg_get_expr(pr.prqual, c.oid)\n"
2986 " , (CASE WHEN pr.prattrs IS NOT NULL THEN\n"
2987 " (SELECT string_agg(attname, ', ')\n"
2988 " FROM pg_catalog.generate_series(0, pg_catalog.array_upper(pr.prattrs::pg_catalog.int2[], 1)) s,\n"
2989 " pg_catalog.pg_attribute\n"
2990 " WHERE attrelid = pr.prrelid AND attnum = prattrs[s])\n"
2992 "FROM pg_catalog.pg_publication p\n"
2993 " JOIN pg_catalog.pg_publication_rel pr ON p.oid = pr.prpubid\n"
2994 " JOIN pg_catalog.pg_class c ON c.oid = pr.prrelid\n"
2995 "WHERE pr.prrelid = '%s'\n"
3000 "FROM pg_catalog.pg_publication p\n"
3001 "WHERE p.puballtables AND pg_catalog.pg_relation_is_publishable('%s')\n"
3003 oid
, oid
, oid
, oid
);
3007 printfPQExpBuffer(&buf
,
3011 "FROM pg_catalog.pg_publication p\n"
3012 "JOIN pg_catalog.pg_publication_rel pr ON p.oid = pr.prpubid\n"
3013 "WHERE pr.prrelid = '%s'\n"
3018 "FROM pg_catalog.pg_publication p\n"
3019 "WHERE p.puballtables AND pg_catalog.pg_relation_is_publishable('%s')\n"
3024 result
= PSQLexec(buf
.data
);
3028 tuples
= PQntuples(result
);
3031 printTableAddFooter(&cont
, _("Publications:"));
3033 /* Might be an empty set - that's ok */
3034 for (i
= 0; i
< tuples
; i
++)
3036 printfPQExpBuffer(&buf
, " \"%s\"",
3037 PQgetvalue(result
, i
, 0));
3039 /* column list (if any) */
3040 if (!PQgetisnull(result
, i
, 2))
3041 appendPQExpBuffer(&buf
, " (%s)",
3042 PQgetvalue(result
, i
, 2));
3044 /* row filter (if any) */
3045 if (!PQgetisnull(result
, i
, 1))
3046 appendPQExpBuffer(&buf
, " WHERE %s",
3047 PQgetvalue(result
, i
, 1));
3049 printTableAddFooter(&cont
, buf
.data
);
3055 /* Get view_def if table is a view or materialized view */
3056 if ((tableinfo
.relkind
== RELKIND_VIEW
||
3057 tableinfo
.relkind
== RELKIND_MATVIEW
) && verbose
)
3061 printfPQExpBuffer(&buf
,
3062 "SELECT pg_catalog.pg_get_viewdef('%s'::pg_catalog.oid, true);",
3064 result
= PSQLexec(buf
.data
);
3068 if (PQntuples(result
) > 0)
3069 view_def
= pg_strdup(PQgetvalue(result
, 0, 0));
3076 PGresult
*result
= NULL
;
3078 /* Footer information about a view */
3079 printTableAddFooter(&cont
, _("View definition:"));
3080 printTableAddFooter(&cont
, view_def
);
3083 if (tableinfo
.hasrules
)
3085 printfPQExpBuffer(&buf
,
3086 "SELECT r.rulename, trim(trailing ';' from pg_catalog.pg_get_ruledef(r.oid, true))\n"
3087 "FROM pg_catalog.pg_rewrite r\n"
3088 "WHERE r.ev_class = '%s' AND r.rulename != '_RETURN' ORDER BY 1;",
3090 result
= PSQLexec(buf
.data
);
3094 if (PQntuples(result
) > 0)
3096 printTableAddFooter(&cont
, _("Rules:"));
3097 for (i
= 0; i
< PQntuples(result
); i
++)
3099 const char *ruledef
;
3101 /* Everything after "CREATE RULE" is echoed verbatim */
3102 ruledef
= PQgetvalue(result
, i
, 1);
3105 printfPQExpBuffer(&buf
, " %s", ruledef
);
3106 printTableAddFooter(&cont
, buf
.data
);
3114 * Print triggers next, if any (but only user-defined triggers). This
3115 * could apply to either a table or a view.
3117 if (tableinfo
.hastriggers
)
3122 printfPQExpBuffer(&buf
,
3124 "pg_catalog.pg_get_triggerdef(t.oid, true), "
3125 "t.tgenabled, t.tgisinternal,\n");
3128 * Detect whether each trigger is inherited, and if so, get the name
3129 * of the topmost table it's inherited from. We have no easy way to
3130 * do that pre-v13, for lack of the tgparentid column. Even with
3131 * tgparentid, a straightforward search for the topmost parent would
3132 * require a recursive CTE, which seems unduly expensive. We cheat a
3133 * bit by assuming parent triggers will match by tgname; then, joining
3134 * with pg_partition_ancestors() allows the planner to make use of
3135 * pg_trigger_tgrelid_tgname_index if it wishes. We ensure we find
3136 * the correct topmost parent by stopping at the first-in-partition-
3137 * ancestry-order trigger that has tgparentid = 0. (There might be
3138 * unrelated, non-inherited triggers with the same name further up the
3139 * stack, so this is important.)
3141 if (pset
.sversion
>= 130000)
3142 appendPQExpBufferStr(&buf
,
3143 " CASE WHEN t.tgparentid != 0 THEN\n"
3144 " (SELECT u.tgrelid::pg_catalog.regclass\n"
3145 " FROM pg_catalog.pg_trigger AS u,\n"
3146 " pg_catalog.pg_partition_ancestors(t.tgrelid) WITH ORDINALITY AS a(relid, depth)\n"
3147 " WHERE u.tgname = t.tgname AND u.tgrelid = a.relid\n"
3148 " AND u.tgparentid = 0\n"
3149 " ORDER BY a.depth LIMIT 1)\n"
3150 " END AS parent\n");
3152 appendPQExpBufferStr(&buf
, " NULL AS parent\n");
3154 appendPQExpBuffer(&buf
,
3155 "FROM pg_catalog.pg_trigger t\n"
3156 "WHERE t.tgrelid = '%s' AND ",
3160 * tgisinternal is set true for inherited triggers of partitions in
3161 * servers between v11 and v14, though these must still be shown to
3162 * the user. So we use another property that is true for such
3163 * inherited triggers to avoid them being hidden, which is their
3164 * dependence on another trigger.
3166 if (pset
.sversion
>= 110000 && pset
.sversion
< 150000)
3167 appendPQExpBufferStr(&buf
, "(NOT t.tgisinternal OR (t.tgisinternal AND t.tgenabled = 'D') \n"
3168 " OR EXISTS (SELECT 1 FROM pg_catalog.pg_depend WHERE objid = t.oid \n"
3169 " AND refclassid = 'pg_catalog.pg_trigger'::pg_catalog.regclass))");
3171 /* display/warn about disabled internal triggers */
3172 appendPQExpBufferStr(&buf
, "(NOT t.tgisinternal OR (t.tgisinternal AND t.tgenabled = 'D'))");
3173 appendPQExpBufferStr(&buf
, "\nORDER BY 1;");
3175 result
= PSQLexec(buf
.data
);
3179 tuples
= PQntuples(result
);
3187 * split the output into 4 different categories. Enabled triggers,
3188 * disabled triggers and the two special ALWAYS and REPLICA
3191 for (category
= 0; category
<= 4; category
++)
3193 have_heading
= false;
3194 for (i
= 0; i
< tuples
; i
++)
3198 const char *usingpos
;
3199 const char *tgenabled
;
3200 const char *tgisinternal
;
3203 * Check if this trigger falls into the current category
3205 tgenabled
= PQgetvalue(result
, i
, 2);
3206 tgisinternal
= PQgetvalue(result
, i
, 3);
3207 list_trigger
= false;
3211 if (*tgenabled
== 'O' || *tgenabled
== 't')
3212 list_trigger
= true;
3215 if ((*tgenabled
== 'D' || *tgenabled
== 'f') &&
3216 *tgisinternal
== 'f')
3217 list_trigger
= true;
3220 if ((*tgenabled
== 'D' || *tgenabled
== 'f') &&
3221 *tgisinternal
== 't')
3222 list_trigger
= true;
3225 if (*tgenabled
== 'A')
3226 list_trigger
= true;
3229 if (*tgenabled
== 'R')
3230 list_trigger
= true;
3233 if (list_trigger
== false)
3236 /* Print the category heading once */
3237 if (have_heading
== false)
3242 printfPQExpBuffer(&buf
, _("Triggers:"));
3245 printfPQExpBuffer(&buf
, _("Disabled user triggers:"));
3248 printfPQExpBuffer(&buf
, _("Disabled internal triggers:"));
3251 printfPQExpBuffer(&buf
, _("Triggers firing always:"));
3254 printfPQExpBuffer(&buf
, _("Triggers firing on replica only:"));
3257 printTableAddFooter(&cont
, buf
.data
);
3258 have_heading
= true;
3261 /* Everything after "TRIGGER" is echoed verbatim */
3262 tgdef
= PQgetvalue(result
, i
, 1);
3263 usingpos
= strstr(tgdef
, " TRIGGER ");
3265 tgdef
= usingpos
+ 9;
3267 printfPQExpBuffer(&buf
, " %s", tgdef
);
3269 /* Visually distinguish inherited triggers */
3270 if (!PQgetisnull(result
, i
, 4))
3271 appendPQExpBuffer(&buf
, ", ON TABLE %s",
3272 PQgetvalue(result
, i
, 4));
3274 printTableAddFooter(&cont
, buf
.data
);
3282 * Finish printing the footer information about a table.
3284 if (tableinfo
.relkind
== RELKIND_RELATION
||
3285 tableinfo
.relkind
== RELKIND_MATVIEW
||
3286 tableinfo
.relkind
== RELKIND_FOREIGN_TABLE
||
3287 tableinfo
.relkind
== RELKIND_PARTITIONED_TABLE
||
3288 tableinfo
.relkind
== RELKIND_PARTITIONED_INDEX
||
3289 tableinfo
.relkind
== RELKIND_TOASTVALUE
)
3291 bool is_partitioned
;
3295 /* simplify some repeated tests below */
3296 is_partitioned
= (tableinfo
.relkind
== RELKIND_PARTITIONED_TABLE
||
3297 tableinfo
.relkind
== RELKIND_PARTITIONED_INDEX
);
3299 /* print foreign server name */
3300 if (tableinfo
.relkind
== RELKIND_FOREIGN_TABLE
)
3304 /* Footer information about foreign table */
3305 printfPQExpBuffer(&buf
,
3306 "SELECT s.srvname,\n"
3307 " pg_catalog.array_to_string(ARRAY(\n"
3308 " SELECT pg_catalog.quote_ident(option_name)"
3309 " || ' ' || pg_catalog.quote_literal(option_value)\n"
3310 " FROM pg_catalog.pg_options_to_table(ftoptions)), ', ')\n"
3311 "FROM pg_catalog.pg_foreign_table f,\n"
3312 " pg_catalog.pg_foreign_server s\n"
3313 "WHERE f.ftrelid = '%s' AND s.oid = f.ftserver;",
3315 result
= PSQLexec(buf
.data
);
3318 else if (PQntuples(result
) != 1)
3324 /* Print server name */
3325 printfPQExpBuffer(&buf
, _("Server: %s"),
3326 PQgetvalue(result
, 0, 0));
3327 printTableAddFooter(&cont
, buf
.data
);
3329 /* Print per-table FDW options, if any */
3330 ftoptions
= PQgetvalue(result
, 0, 1);
3331 if (ftoptions
&& ftoptions
[0] != '\0')
3333 printfPQExpBuffer(&buf
, _("FDW options: (%s)"), ftoptions
);
3334 printTableAddFooter(&cont
, buf
.data
);
3339 /* print tables inherited from (exclude partitioned parents) */
3340 printfPQExpBuffer(&buf
,
3341 "SELECT c.oid::pg_catalog.regclass\n"
3342 "FROM pg_catalog.pg_class c, pg_catalog.pg_inherits i\n"
3343 "WHERE c.oid = i.inhparent AND i.inhrelid = '%s'\n"
3344 " AND c.relkind != " CppAsString2(RELKIND_PARTITIONED_TABLE
)
3345 " AND c.relkind != " CppAsString2(RELKIND_PARTITIONED_INDEX
)
3346 "\nORDER BY inhseqno;",
3349 result
= PSQLexec(buf
.data
);
3354 const char *s
= _("Inherits");
3355 int sw
= pg_wcswidth(s
, strlen(s
), pset
.encoding
);
3357 tuples
= PQntuples(result
);
3359 for (i
= 0; i
< tuples
; i
++)
3362 printfPQExpBuffer(&buf
, "%s: %s",
3363 s
, PQgetvalue(result
, i
, 0));
3365 printfPQExpBuffer(&buf
, "%*s %s",
3366 sw
, "", PQgetvalue(result
, i
, 0));
3368 appendPQExpBufferChar(&buf
, ',');
3370 printTableAddFooter(&cont
, buf
.data
);
3376 /* print child tables (with additional info if partitions) */
3377 if (pset
.sversion
>= 140000)
3378 printfPQExpBuffer(&buf
,
3379 "SELECT c.oid::pg_catalog.regclass, c.relkind,"
3380 " inhdetachpending,"
3381 " pg_catalog.pg_get_expr(c.relpartbound, c.oid)\n"
3382 "FROM pg_catalog.pg_class c, pg_catalog.pg_inherits i\n"
3383 "WHERE c.oid = i.inhrelid AND i.inhparent = '%s'\n"
3384 "ORDER BY pg_catalog.pg_get_expr(c.relpartbound, c.oid) = 'DEFAULT',"
3385 " c.oid::pg_catalog.regclass::pg_catalog.text;",
3387 else if (pset
.sversion
>= 100000)
3388 printfPQExpBuffer(&buf
,
3389 "SELECT c.oid::pg_catalog.regclass, c.relkind,"
3390 " false AS inhdetachpending,"
3391 " pg_catalog.pg_get_expr(c.relpartbound, c.oid)\n"
3392 "FROM pg_catalog.pg_class c, pg_catalog.pg_inherits i\n"
3393 "WHERE c.oid = i.inhrelid AND i.inhparent = '%s'\n"
3394 "ORDER BY pg_catalog.pg_get_expr(c.relpartbound, c.oid) = 'DEFAULT',"
3395 " c.oid::pg_catalog.regclass::pg_catalog.text;",
3398 printfPQExpBuffer(&buf
,
3399 "SELECT c.oid::pg_catalog.regclass, c.relkind,"
3400 " false AS inhdetachpending, NULL\n"
3401 "FROM pg_catalog.pg_class c, pg_catalog.pg_inherits i\n"
3402 "WHERE c.oid = i.inhrelid AND i.inhparent = '%s'\n"
3403 "ORDER BY c.oid::pg_catalog.regclass::pg_catalog.text;",
3406 result
= PSQLexec(buf
.data
);
3409 tuples
= PQntuples(result
);
3412 * For a partitioned table with no partitions, always print the number
3413 * of partitions as zero, even when verbose output is expected.
3414 * Otherwise, we will not print "Partitions" section for a partitioned
3415 * table without any partitions.
3417 if (is_partitioned
&& tuples
== 0)
3419 printfPQExpBuffer(&buf
, _("Number of partitions: %d"), tuples
);
3420 printTableAddFooter(&cont
, buf
.data
);
3424 /* print the number of child tables, if any */
3428 printfPQExpBuffer(&buf
, _("Number of partitions: %d (Use \\d+ to list them.)"), tuples
);
3430 printfPQExpBuffer(&buf
, _("Number of child tables: %d (Use \\d+ to list them.)"), tuples
);
3431 printTableAddFooter(&cont
, buf
.data
);
3436 /* display the list of child tables */
3437 const char *ct
= is_partitioned
? _("Partitions") : _("Child tables");
3438 int ctw
= pg_wcswidth(ct
, strlen(ct
), pset
.encoding
);
3440 for (i
= 0; i
< tuples
; i
++)
3442 char child_relkind
= *PQgetvalue(result
, i
, 1);
3445 printfPQExpBuffer(&buf
, "%s: %s",
3446 ct
, PQgetvalue(result
, i
, 0));
3448 printfPQExpBuffer(&buf
, "%*s %s",
3449 ctw
, "", PQgetvalue(result
, i
, 0));
3450 if (!PQgetisnull(result
, i
, 3))
3451 appendPQExpBuffer(&buf
, " %s", PQgetvalue(result
, i
, 3));
3452 if (child_relkind
== RELKIND_PARTITIONED_TABLE
||
3453 child_relkind
== RELKIND_PARTITIONED_INDEX
)
3454 appendPQExpBufferStr(&buf
, ", PARTITIONED");
3455 else if (child_relkind
== RELKIND_FOREIGN_TABLE
)
3456 appendPQExpBufferStr(&buf
, ", FOREIGN");
3457 if (strcmp(PQgetvalue(result
, i
, 2), "t") == 0)
3458 appendPQExpBufferStr(&buf
, " (DETACH PENDING)");
3460 appendPQExpBufferChar(&buf
, ',');
3462 printTableAddFooter(&cont
, buf
.data
);
3468 if (tableinfo
.reloftype
)
3470 printfPQExpBuffer(&buf
, _("Typed table of type: %s"), tableinfo
.reloftype
);
3471 printTableAddFooter(&cont
, buf
.data
);
3475 (tableinfo
.relkind
== RELKIND_RELATION
||
3476 tableinfo
.relkind
== RELKIND_MATVIEW
) &&
3479 * No need to display default values; we already display a REPLICA
3480 * IDENTITY marker on indexes.
3482 tableinfo
.relreplident
!= 'i' &&
3483 ((strcmp(schemaname
, "pg_catalog") != 0 && tableinfo
.relreplident
!= 'd') ||
3484 (strcmp(schemaname
, "pg_catalog") == 0 && tableinfo
.relreplident
!= 'n')))
3486 const char *s
= _("Replica Identity");
3488 printfPQExpBuffer(&buf
, "%s: %s",
3490 tableinfo
.relreplident
== 'f' ? "FULL" :
3491 tableinfo
.relreplident
== 'n' ? "NOTHING" :
3494 printTableAddFooter(&cont
, buf
.data
);
3497 /* OIDs, if verbose and not a materialized view */
3498 if (verbose
&& tableinfo
.relkind
!= RELKIND_MATVIEW
&& tableinfo
.hasoids
)
3499 printTableAddFooter(&cont
, _("Has OIDs: yes"));
3501 /* Tablespace info */
3502 add_tablespace_footer(&cont
, tableinfo
.relkind
, tableinfo
.tablespace
,
3505 /* Access method info */
3506 if (verbose
&& tableinfo
.relam
!= NULL
&& !pset
.hide_tableam
)
3508 printfPQExpBuffer(&buf
, _("Access method: %s"), tableinfo
.relam
);
3509 printTableAddFooter(&cont
, buf
.data
);
3513 /* reloptions, if verbose */
3515 tableinfo
.reloptions
&& tableinfo
.reloptions
[0] != '\0')
3517 const char *t
= _("Options");
3519 printfPQExpBuffer(&buf
, "%s: %s", t
, tableinfo
.reloptions
);
3520 printTableAddFooter(&cont
, buf
.data
);
3523 printTable(&cont
, pset
.queryFout
, false, pset
.logfile
);
3530 if (printTableInitialized
)
3531 printTableCleanup(&cont
);
3532 termPQExpBuffer(&buf
);
3533 termPQExpBuffer(&title
);
3534 termPQExpBuffer(&tmpbuf
);
3544 * Add a tablespace description to a footer. If 'newline' is true, it is added
3545 * in a new line; otherwise it's appended to the current value of the last
3549 add_tablespace_footer(printTableContent
*const cont
, char relkind
,
3550 Oid tablespace
, const bool newline
)
3552 /* relkinds for which we support tablespaces */
3553 if (relkind
== RELKIND_RELATION
||
3554 relkind
== RELKIND_MATVIEW
||
3555 relkind
== RELKIND_INDEX
||
3556 relkind
== RELKIND_PARTITIONED_TABLE
||
3557 relkind
== RELKIND_PARTITIONED_INDEX
||
3558 relkind
== RELKIND_TOASTVALUE
)
3561 * We ignore the database default tablespace so that users not using
3562 * tablespaces don't need to know about them.
3564 if (tablespace
!= 0)
3566 PGresult
*result
= NULL
;
3567 PQExpBufferData buf
;
3569 initPQExpBuffer(&buf
);
3570 printfPQExpBuffer(&buf
,
3571 "SELECT spcname FROM pg_catalog.pg_tablespace\n"
3572 "WHERE oid = '%u';", tablespace
);
3573 result
= PSQLexec(buf
.data
);
3576 termPQExpBuffer(&buf
);
3579 /* Should always be the case, but.... */
3580 if (PQntuples(result
) > 0)
3584 /* Add the tablespace as a new footer */
3585 printfPQExpBuffer(&buf
, _("Tablespace: \"%s\""),
3586 PQgetvalue(result
, 0, 0));
3587 printTableAddFooter(cont
, buf
.data
);
3591 /* Append the tablespace to the latest footer */
3592 printfPQExpBuffer(&buf
, "%s", cont
->footer
->data
);
3595 translator: before this string there's an index description like
3596 '"foo_pkey" PRIMARY KEY, btree (a)' */
3597 appendPQExpBuffer(&buf
, _(", tablespace \"%s\""),
3598 PQgetvalue(result
, 0, 0));
3599 printTableSetFooter(cont
, buf
.data
);
3603 termPQExpBuffer(&buf
);
3611 * Describes roles. Any schema portion of the pattern is ignored.
3614 describeRoles(const char *pattern
, bool verbose
, bool showSystem
)
3616 PQExpBufferData buf
;
3618 printTableContent cont
;
3619 printTableOpt myopt
= pset
.popt
.topt
;
3624 const char align
= 'l';
3627 myopt
.default_footer
= false;
3629 initPQExpBuffer(&buf
);
3631 printfPQExpBuffer(&buf
,
3632 "SELECT r.rolname, r.rolsuper, r.rolinherit,\n"
3633 " r.rolcreaterole, r.rolcreatedb, r.rolcanlogin,\n"
3634 " r.rolconnlimit, r.rolvaliduntil");
3638 appendPQExpBufferStr(&buf
, "\n, pg_catalog.shobj_description(r.oid, 'pg_authid') AS description");
3641 appendPQExpBufferStr(&buf
, "\n, r.rolreplication");
3643 if (pset
.sversion
>= 90500)
3645 appendPQExpBufferStr(&buf
, "\n, r.rolbypassrls");
3648 appendPQExpBufferStr(&buf
, "\nFROM pg_catalog.pg_roles r\n");
3650 if (!showSystem
&& !pattern
)
3651 appendPQExpBufferStr(&buf
, "WHERE r.rolname !~ '^pg_'\n");
3653 if (!validateSQLNamePattern(&buf
, pattern
, false, false,
3654 NULL
, "r.rolname", NULL
, NULL
,
3657 termPQExpBuffer(&buf
);
3661 appendPQExpBufferStr(&buf
, "ORDER BY 1;");
3663 res
= PSQLexec(buf
.data
);
3667 nrows
= PQntuples(res
);
3668 attr
= pg_malloc0((nrows
+ 1) * sizeof(*attr
));
3670 printTableInit(&cont
, &myopt
, _("List of roles"), ncols
, nrows
);
3672 printTableAddHeader(&cont
, gettext_noop("Role name"), true, align
);
3673 printTableAddHeader(&cont
, gettext_noop("Attributes"), true, align
);
3676 printTableAddHeader(&cont
, gettext_noop("Description"), true, align
);
3678 for (i
= 0; i
< nrows
; i
++)
3680 printTableAddCell(&cont
, PQgetvalue(res
, i
, 0), false, false);
3682 resetPQExpBuffer(&buf
);
3683 if (strcmp(PQgetvalue(res
, i
, 1), "t") == 0)
3684 add_role_attribute(&buf
, _("Superuser"));
3686 if (strcmp(PQgetvalue(res
, i
, 2), "t") != 0)
3687 add_role_attribute(&buf
, _("No inheritance"));
3689 if (strcmp(PQgetvalue(res
, i
, 3), "t") == 0)
3690 add_role_attribute(&buf
, _("Create role"));
3692 if (strcmp(PQgetvalue(res
, i
, 4), "t") == 0)
3693 add_role_attribute(&buf
, _("Create DB"));
3695 if (strcmp(PQgetvalue(res
, i
, 5), "t") != 0)
3696 add_role_attribute(&buf
, _("Cannot login"));
3698 if (strcmp(PQgetvalue(res
, i
, (verbose
? 9 : 8)), "t") == 0)
3699 add_role_attribute(&buf
, _("Replication"));
3701 if (pset
.sversion
>= 90500)
3702 if (strcmp(PQgetvalue(res
, i
, (verbose
? 10 : 9)), "t") == 0)
3703 add_role_attribute(&buf
, _("Bypass RLS"));
3705 conns
= atoi(PQgetvalue(res
, i
, 6));
3709 appendPQExpBufferChar(&buf
, '\n');
3712 appendPQExpBufferStr(&buf
, _("No connections"));
3714 appendPQExpBuffer(&buf
, ngettext("%d connection",
3720 if (strcmp(PQgetvalue(res
, i
, 7), "") != 0)
3723 appendPQExpBufferChar(&buf
, '\n');
3724 appendPQExpBufferStr(&buf
, _("Password valid until "));
3725 appendPQExpBufferStr(&buf
, PQgetvalue(res
, i
, 7));
3728 attr
[i
] = pg_strdup(buf
.data
);
3730 printTableAddCell(&cont
, attr
[i
], false, false);
3733 printTableAddCell(&cont
, PQgetvalue(res
, i
, 8), false, false);
3735 termPQExpBuffer(&buf
);
3737 printTable(&cont
, pset
.queryFout
, false, pset
.logfile
);
3738 printTableCleanup(&cont
);
3740 for (i
= 0; i
< nrows
; i
++)
3749 add_role_attribute(PQExpBuffer buf
, const char *const str
)
3752 appendPQExpBufferStr(buf
, ", ");
3754 appendPQExpBufferStr(buf
, str
);
3761 listDbRoleSettings(const char *pattern
, const char *pattern2
)
3763 PQExpBufferData buf
;
3765 printQueryOpt myopt
= pset
.popt
;
3768 initPQExpBuffer(&buf
);
3770 printfPQExpBuffer(&buf
, "SELECT rolname AS \"%s\", datname AS \"%s\",\n"
3771 "pg_catalog.array_to_string(setconfig, E'\\n') AS \"%s\"\n"
3772 "FROM pg_catalog.pg_db_role_setting s\n"
3773 "LEFT JOIN pg_catalog.pg_database d ON d.oid = setdatabase\n"
3774 "LEFT JOIN pg_catalog.pg_roles r ON r.oid = setrole\n",
3775 gettext_noop("Role"),
3776 gettext_noop("Database"),
3777 gettext_noop("Settings"));
3778 if (!validateSQLNamePattern(&buf
, pattern
, false, false,
3779 NULL
, "r.rolname", NULL
, NULL
, &havewhere
, 1))
3781 if (!validateSQLNamePattern(&buf
, pattern2
, havewhere
, false,
3782 NULL
, "d.datname", NULL
, NULL
,
3785 appendPQExpBufferStr(&buf
, "ORDER BY 1, 2;");
3787 res
= PSQLexec(buf
.data
);
3788 termPQExpBuffer(&buf
);
3793 * Most functions in this file are content to print an empty table when
3794 * there are no matching objects. We intentionally deviate from that
3795 * here, but only in !quiet mode, because of the possibility that the user
3796 * is confused about what the two pattern arguments mean.
3798 if (PQntuples(res
) == 0 && !pset
.quiet
)
3800 if (pattern
&& pattern2
)
3801 pg_log_error("Did not find any settings for role \"%s\" and database \"%s\".",
3804 pg_log_error("Did not find any settings for role \"%s\".",
3807 pg_log_error("Did not find any settings.");
3811 myopt
.nullPrint
= NULL
;
3812 myopt
.title
= _("List of settings");
3813 myopt
.translate_header
= true;
3815 printQuery(res
, &myopt
, pset
.queryFout
, false, pset
.logfile
);
3822 termPQExpBuffer(&buf
);
3828 * Describes role grants.
3831 describeRoleGrants(const char *pattern
, bool showSystem
)
3833 PQExpBufferData buf
;
3835 printQueryOpt myopt
= pset
.popt
;
3837 initPQExpBuffer(&buf
);
3838 printfPQExpBuffer(&buf
,
3839 "SELECT m.rolname AS \"%s\", r.rolname AS \"%s\",\n"
3840 " pg_catalog.concat_ws(', ',\n",
3841 gettext_noop("Role name"),
3842 gettext_noop("Member of"));
3844 if (pset
.sversion
>= 160000)
3845 appendPQExpBufferStr(&buf
,
3846 " CASE WHEN pam.admin_option THEN 'ADMIN' END,\n"
3847 " CASE WHEN pam.inherit_option THEN 'INHERIT' END,\n"
3848 " CASE WHEN pam.set_option THEN 'SET' END\n");
3850 appendPQExpBufferStr(&buf
,
3851 " CASE WHEN pam.admin_option THEN 'ADMIN' END,\n"
3852 " CASE WHEN m.rolinherit THEN 'INHERIT' END,\n"
3855 appendPQExpBuffer(&buf
,
3857 " g.rolname AS \"%s\"\n",
3858 gettext_noop("Options"),
3859 gettext_noop("Grantor"));
3861 appendPQExpBufferStr(&buf
,
3862 "FROM pg_catalog.pg_roles m\n"
3863 " JOIN pg_catalog.pg_auth_members pam ON (pam.member = m.oid)\n"
3864 " LEFT JOIN pg_catalog.pg_roles r ON (pam.roleid = r.oid)\n"
3865 " LEFT JOIN pg_catalog.pg_roles g ON (pam.grantor = g.oid)\n");
3867 if (!showSystem
&& !pattern
)
3868 appendPQExpBufferStr(&buf
, "WHERE m.rolname !~ '^pg_'\n");
3870 if (!validateSQLNamePattern(&buf
, pattern
, false, false,
3871 NULL
, "m.rolname", NULL
, NULL
,
3874 termPQExpBuffer(&buf
);
3878 appendPQExpBufferStr(&buf
, "ORDER BY 1, 2, 4;\n");
3880 res
= PSQLexec(buf
.data
);
3881 termPQExpBuffer(&buf
);
3885 myopt
.nullPrint
= NULL
;
3886 myopt
.title
= _("List of role grants");
3887 myopt
.translate_header
= true;
3889 printQuery(res
, &myopt
, pset
.queryFout
, false, pset
.logfile
);
3899 * handler for \dt, \di, etc.
3901 * tabtypes is an array of characters, specifying what info is desired:
3905 * m - materialized views
3907 * E - foreign table (Note: different from 'f', the relkind value)
3908 * (any order of the above is fine)
3911 listTables(const char *tabtypes
, const char *pattern
, bool verbose
, bool showSystem
)
3913 bool showTables
= strchr(tabtypes
, 't') != NULL
;
3914 bool showIndexes
= strchr(tabtypes
, 'i') != NULL
;
3915 bool showViews
= strchr(tabtypes
, 'v') != NULL
;
3916 bool showMatViews
= strchr(tabtypes
, 'm') != NULL
;
3917 bool showSeq
= strchr(tabtypes
, 's') != NULL
;
3918 bool showForeign
= strchr(tabtypes
, 'E') != NULL
;
3920 PQExpBufferData buf
;
3922 printQueryOpt myopt
= pset
.popt
;
3924 bool translate_columns
[] = {false, false, true, false, false, false, false, false, false};
3926 /* If tabtypes is empty, we default to \dtvmsE (but see also command.c) */
3927 if (!(showTables
|| showIndexes
|| showViews
|| showMatViews
|| showSeq
|| showForeign
))
3928 showTables
= showViews
= showMatViews
= showSeq
= showForeign
= true;
3930 initPQExpBuffer(&buf
);
3932 printfPQExpBuffer(&buf
,
3933 "SELECT n.nspname as \"%s\",\n"
3934 " c.relname as \"%s\",\n"
3936 " WHEN " CppAsString2(RELKIND_RELATION
) " THEN '%s'"
3937 " WHEN " CppAsString2(RELKIND_VIEW
) " THEN '%s'"
3938 " WHEN " CppAsString2(RELKIND_MATVIEW
) " THEN '%s'"
3939 " WHEN " CppAsString2(RELKIND_INDEX
) " THEN '%s'"
3940 " WHEN " CppAsString2(RELKIND_SEQUENCE
) " THEN '%s'"
3941 " WHEN " CppAsString2(RELKIND_TOASTVALUE
) " THEN '%s'"
3942 " WHEN " CppAsString2(RELKIND_FOREIGN_TABLE
) " THEN '%s'"
3943 " WHEN " CppAsString2(RELKIND_PARTITIONED_TABLE
) " THEN '%s'"
3944 " WHEN " CppAsString2(RELKIND_PARTITIONED_INDEX
) " THEN '%s'"
3946 " pg_catalog.pg_get_userbyid(c.relowner) as \"%s\"",
3947 gettext_noop("Schema"),
3948 gettext_noop("Name"),
3949 gettext_noop("table"),
3950 gettext_noop("view"),
3951 gettext_noop("materialized view"),
3952 gettext_noop("index"),
3953 gettext_noop("sequence"),
3954 gettext_noop("TOAST table"),
3955 gettext_noop("foreign table"),
3956 gettext_noop("partitioned table"),
3957 gettext_noop("partitioned index"),
3958 gettext_noop("Type"),
3959 gettext_noop("Owner"));
3964 appendPQExpBuffer(&buf
,
3965 ",\n c2.relname as \"%s\"",
3966 gettext_noop("Table"));
3973 * Show whether a relation is permanent, temporary, or unlogged.
3975 appendPQExpBuffer(&buf
,
3976 ",\n CASE c.relpersistence WHEN 'p' THEN '%s' WHEN 't' THEN '%s' WHEN 'u' THEN '%s' END as \"%s\"",
3977 gettext_noop("permanent"),
3978 gettext_noop("temporary"),
3979 gettext_noop("unlogged"),
3980 gettext_noop("Persistence"));
3981 translate_columns
[cols_so_far
] = true;
3984 * We don't bother to count cols_so_far below here, as there's no need
3985 * to; this might change with future additions to the output columns.
3989 * Access methods exist for tables, materialized views and indexes.
3990 * This has been introduced in PostgreSQL 12 for tables.
3992 if (pset
.sversion
>= 120000 && !pset
.hide_tableam
&&
3993 (showTables
|| showMatViews
|| showIndexes
))
3994 appendPQExpBuffer(&buf
,
3995 ",\n am.amname as \"%s\"",
3996 gettext_noop("Access method"));
3998 appendPQExpBuffer(&buf
,
3999 ",\n pg_catalog.pg_size_pretty(pg_catalog.pg_table_size(c.oid)) as \"%s\""
4000 ",\n pg_catalog.obj_description(c.oid, 'pg_class') as \"%s\"",
4001 gettext_noop("Size"),
4002 gettext_noop("Description"));
4005 appendPQExpBufferStr(&buf
,
4006 "\nFROM pg_catalog.pg_class c"
4007 "\n LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace");
4009 if (pset
.sversion
>= 120000 && !pset
.hide_tableam
&&
4010 (showTables
|| showMatViews
|| showIndexes
))
4011 appendPQExpBufferStr(&buf
,
4012 "\n LEFT JOIN pg_catalog.pg_am am ON am.oid = c.relam");
4015 appendPQExpBufferStr(&buf
,
4016 "\n LEFT JOIN pg_catalog.pg_index i ON i.indexrelid = c.oid"
4017 "\n LEFT JOIN pg_catalog.pg_class c2 ON i.indrelid = c2.oid");
4019 appendPQExpBufferStr(&buf
, "\nWHERE c.relkind IN (");
4022 appendPQExpBufferStr(&buf
, CppAsString2(RELKIND_RELATION
) ","
4023 CppAsString2(RELKIND_PARTITIONED_TABLE
) ",");
4024 /* with 'S' or a pattern, allow 't' to match TOAST tables too */
4025 if (showSystem
|| pattern
)
4026 appendPQExpBufferStr(&buf
, CppAsString2(RELKIND_TOASTVALUE
) ",");
4029 appendPQExpBufferStr(&buf
, CppAsString2(RELKIND_VIEW
) ",");
4031 appendPQExpBufferStr(&buf
, CppAsString2(RELKIND_MATVIEW
) ",");
4033 appendPQExpBufferStr(&buf
, CppAsString2(RELKIND_INDEX
) ","
4034 CppAsString2(RELKIND_PARTITIONED_INDEX
) ",");
4036 appendPQExpBufferStr(&buf
, CppAsString2(RELKIND_SEQUENCE
) ",");
4037 if (showSystem
|| pattern
)
4038 appendPQExpBufferStr(&buf
, "'s',"); /* was RELKIND_SPECIAL */
4040 appendPQExpBufferStr(&buf
, CppAsString2(RELKIND_FOREIGN_TABLE
) ",");
4042 appendPQExpBufferStr(&buf
, "''"); /* dummy */
4043 appendPQExpBufferStr(&buf
, ")\n");
4045 if (!showSystem
&& !pattern
)
4046 appendPQExpBufferStr(&buf
, " AND n.nspname <> 'pg_catalog'\n"
4047 " AND n.nspname !~ '^pg_toast'\n"
4048 " AND n.nspname <> 'information_schema'\n");
4050 if (!validateSQLNamePattern(&buf
, pattern
, true, false,
4051 "n.nspname", "c.relname", NULL
,
4052 "pg_catalog.pg_table_is_visible(c.oid)",
4055 termPQExpBuffer(&buf
);
4059 appendPQExpBufferStr(&buf
, "ORDER BY 1,2;");
4061 res
= PSQLexec(buf
.data
);
4062 termPQExpBuffer(&buf
);
4067 * Most functions in this file are content to print an empty table when
4068 * there are no matching objects. We intentionally deviate from that
4069 * here, but only in !quiet mode, for historical reasons.
4071 if (PQntuples(res
) == 0 && !pset
.quiet
)
4074 pg_log_error("Did not find any relation named \"%s\".",
4077 pg_log_error("Did not find any relations.");
4081 myopt
.nullPrint
= NULL
;
4082 myopt
.title
= _("List of relations");
4083 myopt
.translate_header
= true;
4084 myopt
.translate_columns
= translate_columns
;
4085 myopt
.n_translate_columns
= lengthof(translate_columns
);
4087 printQuery(res
, &myopt
, pset
.queryFout
, false, pset
.logfile
);
4096 * Takes an optional regexp to select particular relations
4098 * As with \d, you can specify the kinds of relations you want:
4103 * And there's additional flags:
4105 * n to list non-leaf partitioned tables
4107 * and you can mix and match these in any order.
4110 listPartitionedTables(const char *reltypes
, const char *pattern
, bool verbose
)
4112 bool showTables
= strchr(reltypes
, 't') != NULL
;
4113 bool showIndexes
= strchr(reltypes
, 'i') != NULL
;
4114 bool showNested
= strchr(reltypes
, 'n') != NULL
;
4115 PQExpBufferData buf
;
4116 PQExpBufferData title
;
4118 printQueryOpt myopt
= pset
.popt
;
4119 bool translate_columns
[] = {false, false, false, false, false, false, false, false, false};
4120 const char *tabletitle
;
4121 bool mixed_output
= false;
4124 * Note: Declarative table partitioning is only supported as of Pg 10.0.
4126 if (pset
.sversion
< 100000)
4130 pg_log_error("The server (version %s) does not support declarative table partitioning.",
4131 formatPGVersionNumber(pset
.sversion
, false,
4132 sverbuf
, sizeof(sverbuf
)));
4136 /* If no relation kind was selected, show them all */
4137 if (!showTables
&& !showIndexes
)
4138 showTables
= showIndexes
= true;
4140 if (showIndexes
&& !showTables
)
4141 tabletitle
= _("List of partitioned indexes"); /* \dPi */
4142 else if (showTables
&& !showIndexes
)
4143 tabletitle
= _("List of partitioned tables"); /* \dPt */
4146 /* show all kinds */
4147 tabletitle
= _("List of partitioned relations");
4148 mixed_output
= true;
4151 initPQExpBuffer(&buf
);
4153 printfPQExpBuffer(&buf
,
4154 "SELECT n.nspname as \"%s\",\n"
4155 " c.relname as \"%s\",\n"
4156 " pg_catalog.pg_get_userbyid(c.relowner) as \"%s\"",
4157 gettext_noop("Schema"),
4158 gettext_noop("Name"),
4159 gettext_noop("Owner"));
4163 appendPQExpBuffer(&buf
,
4164 ",\n CASE c.relkind"
4165 " WHEN " CppAsString2(RELKIND_PARTITIONED_TABLE
) " THEN '%s'"
4166 " WHEN " CppAsString2(RELKIND_PARTITIONED_INDEX
) " THEN '%s'"
4168 gettext_noop("partitioned table"),
4169 gettext_noop("partitioned index"),
4170 gettext_noop("Type"));
4172 translate_columns
[3] = true;
4175 if (showNested
|| pattern
)
4176 appendPQExpBuffer(&buf
,
4177 ",\n inh.inhparent::pg_catalog.regclass as \"%s\"",
4178 gettext_noop("Parent name"));
4181 appendPQExpBuffer(&buf
,
4182 ",\n c2.oid::pg_catalog.regclass as \"%s\"",
4183 gettext_noop("Table"));
4189 appendPQExpBuffer(&buf
,
4190 ",\n s.dps as \"%s\"",
4191 gettext_noop("Leaf partition size"));
4192 appendPQExpBuffer(&buf
,
4193 ",\n s.tps as \"%s\"",
4194 gettext_noop("Total size"));
4197 /* Sizes of all partitions are considered in this case. */
4198 appendPQExpBuffer(&buf
,
4199 ",\n s.tps as \"%s\"",
4200 gettext_noop("Total size"));
4202 appendPQExpBuffer(&buf
,
4203 ",\n pg_catalog.obj_description(c.oid, 'pg_class') as \"%s\"",
4204 gettext_noop("Description"));
4207 appendPQExpBufferStr(&buf
,
4208 "\nFROM pg_catalog.pg_class c"
4209 "\n LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace");
4212 appendPQExpBufferStr(&buf
,
4213 "\n LEFT JOIN pg_catalog.pg_index i ON i.indexrelid = c.oid"
4214 "\n LEFT JOIN pg_catalog.pg_class c2 ON i.indrelid = c2.oid");
4216 if (showNested
|| pattern
)
4217 appendPQExpBufferStr(&buf
,
4218 "\n LEFT JOIN pg_catalog.pg_inherits inh ON c.oid = inh.inhrelid");
4222 if (pset
.sversion
< 120000)
4224 appendPQExpBufferStr(&buf
,
4225 ",\n LATERAL (WITH RECURSIVE d\n"
4226 " AS (SELECT inhrelid AS oid, 1 AS level\n"
4227 " FROM pg_catalog.pg_inherits\n"
4228 " WHERE inhparent = c.oid\n"
4230 " SELECT inhrelid, level + 1\n"
4231 " FROM pg_catalog.pg_inherits i\n"
4232 " JOIN d ON i.inhparent = d.oid)\n"
4233 " SELECT pg_catalog.pg_size_pretty(sum(pg_catalog.pg_table_size("
4234 "d.oid))) AS tps,\n"
4235 " pg_catalog.pg_size_pretty(sum("
4236 "\n CASE WHEN d.level = 1"
4237 " THEN pg_catalog.pg_table_size(d.oid) ELSE 0 END)) AS dps\n"
4242 /* PostgreSQL 12 has pg_partition_tree function */
4243 appendPQExpBufferStr(&buf
,
4244 ",\n LATERAL (SELECT pg_catalog.pg_size_pretty(sum("
4245 "\n CASE WHEN ppt.isleaf AND ppt.level = 1"
4246 "\n THEN pg_catalog.pg_table_size(ppt.relid)"
4247 " ELSE 0 END)) AS dps"
4248 ",\n pg_catalog.pg_size_pretty(sum("
4249 "pg_catalog.pg_table_size(ppt.relid))) AS tps"
4250 "\n FROM pg_catalog.pg_partition_tree(c.oid) ppt) s");
4254 appendPQExpBufferStr(&buf
, "\nWHERE c.relkind IN (");
4256 appendPQExpBufferStr(&buf
, CppAsString2(RELKIND_PARTITIONED_TABLE
) ",");
4258 appendPQExpBufferStr(&buf
, CppAsString2(RELKIND_PARTITIONED_INDEX
) ",");
4259 appendPQExpBufferStr(&buf
, "''"); /* dummy */
4260 appendPQExpBufferStr(&buf
, ")\n");
4262 appendPQExpBufferStr(&buf
, !showNested
&& !pattern
?
4263 " AND NOT c.relispartition\n" : "");
4266 appendPQExpBufferStr(&buf
, " AND n.nspname <> 'pg_catalog'\n"
4267 " AND n.nspname !~ '^pg_toast'\n"
4268 " AND n.nspname <> 'information_schema'\n");
4270 if (!validateSQLNamePattern(&buf
, pattern
, true, false,
4271 "n.nspname", "c.relname", NULL
,
4272 "pg_catalog.pg_table_is_visible(c.oid)",
4275 termPQExpBuffer(&buf
);
4279 appendPQExpBuffer(&buf
, "ORDER BY \"Schema\", %s%s\"Name\";",
4280 mixed_output
? "\"Type\" DESC, " : "",
4281 showNested
|| pattern
? "\"Parent name\" NULLS FIRST, " : "");
4283 res
= PSQLexec(buf
.data
);
4284 termPQExpBuffer(&buf
);
4288 initPQExpBuffer(&title
);
4289 appendPQExpBufferStr(&title
, tabletitle
);
4291 myopt
.nullPrint
= NULL
;
4292 myopt
.title
= title
.data
;
4293 myopt
.translate_header
= true;
4294 myopt
.translate_columns
= translate_columns
;
4295 myopt
.n_translate_columns
= lengthof(translate_columns
);
4297 printQuery(res
, &myopt
, pset
.queryFout
, false, pset
.logfile
);
4299 termPQExpBuffer(&title
);
4308 * Describes languages.
4311 listLanguages(const char *pattern
, bool verbose
, bool showSystem
)
4313 PQExpBufferData buf
;
4315 printQueryOpt myopt
= pset
.popt
;
4317 initPQExpBuffer(&buf
);
4319 printfPQExpBuffer(&buf
,
4320 "SELECT l.lanname AS \"%s\",\n"
4321 " pg_catalog.pg_get_userbyid(l.lanowner) as \"%s\",\n"
4322 " l.lanpltrusted AS \"%s\"",
4323 gettext_noop("Name"),
4324 gettext_noop("Owner"),
4325 gettext_noop("Trusted"));
4329 appendPQExpBuffer(&buf
,
4330 ",\n NOT l.lanispl AS \"%s\",\n"
4331 " l.lanplcallfoid::pg_catalog.regprocedure AS \"%s\",\n"
4332 " l.lanvalidator::pg_catalog.regprocedure AS \"%s\",\n "
4333 "l.laninline::pg_catalog.regprocedure AS \"%s\",\n ",
4334 gettext_noop("Internal language"),
4335 gettext_noop("Call handler"),
4336 gettext_noop("Validator"),
4337 gettext_noop("Inline handler"));
4338 printACLColumn(&buf
, "l.lanacl");
4341 appendPQExpBuffer(&buf
,
4342 ",\n d.description AS \"%s\""
4343 "\nFROM pg_catalog.pg_language l\n"
4344 "LEFT JOIN pg_catalog.pg_description d\n"
4345 " ON d.classoid = l.tableoid AND d.objoid = l.oid\n"
4346 " AND d.objsubid = 0\n",
4347 gettext_noop("Description"));
4351 if (!validateSQLNamePattern(&buf
, pattern
, false, false,
4352 NULL
, "l.lanname", NULL
, NULL
,
4355 termPQExpBuffer(&buf
);
4360 if (!showSystem
&& !pattern
)
4361 appendPQExpBufferStr(&buf
, "WHERE l.lanplcallfoid != 0\n");
4364 appendPQExpBufferStr(&buf
, "ORDER BY 1;");
4366 res
= PSQLexec(buf
.data
);
4367 termPQExpBuffer(&buf
);
4371 myopt
.nullPrint
= NULL
;
4372 myopt
.title
= _("List of languages");
4373 myopt
.translate_header
= true;
4375 printQuery(res
, &myopt
, pset
.queryFout
, false, pset
.logfile
);
4385 * Describes domains.
4388 listDomains(const char *pattern
, bool verbose
, bool showSystem
)
4390 PQExpBufferData buf
;
4392 printQueryOpt myopt
= pset
.popt
;
4394 initPQExpBuffer(&buf
);
4396 printfPQExpBuffer(&buf
,
4397 "SELECT n.nspname as \"%s\",\n"
4398 " t.typname as \"%s\",\n"
4399 " pg_catalog.format_type(t.typbasetype, t.typtypmod) as \"%s\",\n"
4400 " (SELECT c.collname FROM pg_catalog.pg_collation c, pg_catalog.pg_type bt\n"
4401 " WHERE c.oid = t.typcollation AND bt.oid = t.typbasetype AND t.typcollation <> bt.typcollation) as \"%s\",\n"
4402 " CASE WHEN t.typnotnull THEN 'not null' END as \"%s\",\n"
4403 " t.typdefault as \"%s\",\n"
4404 " pg_catalog.array_to_string(ARRAY(\n"
4405 " SELECT pg_catalog.pg_get_constraintdef(r.oid, true) FROM pg_catalog.pg_constraint r WHERE t.oid = r.contypid\n"
4406 " ), ' ') as \"%s\"",
4407 gettext_noop("Schema"),
4408 gettext_noop("Name"),
4409 gettext_noop("Type"),
4410 gettext_noop("Collation"),
4411 gettext_noop("Nullable"),
4412 gettext_noop("Default"),
4413 gettext_noop("Check"));
4417 appendPQExpBufferStr(&buf
, ",\n ");
4418 printACLColumn(&buf
, "t.typacl");
4419 appendPQExpBuffer(&buf
,
4420 ",\n d.description as \"%s\"",
4421 gettext_noop("Description"));
4424 appendPQExpBufferStr(&buf
,
4425 "\nFROM pg_catalog.pg_type t\n"
4426 " LEFT JOIN pg_catalog.pg_namespace n ON n.oid = t.typnamespace\n");
4429 appendPQExpBufferStr(&buf
,
4430 " LEFT JOIN pg_catalog.pg_description d "
4431 "ON d.classoid = t.tableoid AND d.objoid = t.oid "
4432 "AND d.objsubid = 0\n");
4434 appendPQExpBufferStr(&buf
, "WHERE t.typtype = 'd'\n");
4436 if (!showSystem
&& !pattern
)
4437 appendPQExpBufferStr(&buf
, " AND n.nspname <> 'pg_catalog'\n"
4438 " AND n.nspname <> 'information_schema'\n");
4440 if (!validateSQLNamePattern(&buf
, pattern
, true, false,
4441 "n.nspname", "t.typname", NULL
,
4442 "pg_catalog.pg_type_is_visible(t.oid)",
4445 termPQExpBuffer(&buf
);
4449 appendPQExpBufferStr(&buf
, "ORDER BY 1, 2;");
4451 res
= PSQLexec(buf
.data
);
4452 termPQExpBuffer(&buf
);
4456 myopt
.nullPrint
= NULL
;
4457 myopt
.title
= _("List of domains");
4458 myopt
.translate_header
= true;
4460 printQuery(res
, &myopt
, pset
.queryFout
, false, pset
.logfile
);
4469 * Describes conversions.
4472 listConversions(const char *pattern
, bool verbose
, bool showSystem
)
4474 PQExpBufferData buf
;
4476 printQueryOpt myopt
= pset
.popt
;
4477 static const bool translate_columns
[] =
4478 {false, false, false, false, true, false};
4480 initPQExpBuffer(&buf
);
4482 printfPQExpBuffer(&buf
,
4483 "SELECT n.nspname AS \"%s\",\n"
4484 " c.conname AS \"%s\",\n"
4485 " pg_catalog.pg_encoding_to_char(c.conforencoding) AS \"%s\",\n"
4486 " pg_catalog.pg_encoding_to_char(c.contoencoding) AS \"%s\",\n"
4487 " CASE WHEN c.condefault THEN '%s'\n"
4488 " ELSE '%s' END AS \"%s\"",
4489 gettext_noop("Schema"),
4490 gettext_noop("Name"),
4491 gettext_noop("Source"),
4492 gettext_noop("Destination"),
4493 gettext_noop("yes"), gettext_noop("no"),
4494 gettext_noop("Default?"));
4497 appendPQExpBuffer(&buf
,
4498 ",\n d.description AS \"%s\"",
4499 gettext_noop("Description"));
4501 appendPQExpBufferStr(&buf
,
4502 "\nFROM pg_catalog.pg_conversion c\n"
4503 " JOIN pg_catalog.pg_namespace n "
4504 "ON n.oid = c.connamespace\n");
4507 appendPQExpBufferStr(&buf
,
4508 "LEFT JOIN pg_catalog.pg_description d "
4509 "ON d.classoid = c.tableoid\n"
4510 " AND d.objoid = c.oid "
4511 "AND d.objsubid = 0\n");
4513 appendPQExpBufferStr(&buf
, "WHERE true\n");
4515 if (!showSystem
&& !pattern
)
4516 appendPQExpBufferStr(&buf
, " AND n.nspname <> 'pg_catalog'\n"
4517 " AND n.nspname <> 'information_schema'\n");
4519 if (!validateSQLNamePattern(&buf
, pattern
, true, false,
4520 "n.nspname", "c.conname", NULL
,
4521 "pg_catalog.pg_conversion_is_visible(c.oid)",
4524 termPQExpBuffer(&buf
);
4528 appendPQExpBufferStr(&buf
, "ORDER BY 1, 2;");
4530 res
= PSQLexec(buf
.data
);
4531 termPQExpBuffer(&buf
);
4535 myopt
.nullPrint
= NULL
;
4536 myopt
.title
= _("List of conversions");
4537 myopt
.translate_header
= true;
4538 myopt
.translate_columns
= translate_columns
;
4539 myopt
.n_translate_columns
= lengthof(translate_columns
);
4541 printQuery(res
, &myopt
, pset
.queryFout
, false, pset
.logfile
);
4550 * Describes configuration parameters.
4553 describeConfigurationParameters(const char *pattern
, bool verbose
,
4556 PQExpBufferData buf
;
4558 printQueryOpt myopt
= pset
.popt
;
4560 initPQExpBuffer(&buf
);
4561 printfPQExpBuffer(&buf
,
4562 "SELECT s.name AS \"%s\", "
4563 "pg_catalog.current_setting(s.name) AS \"%s\"",
4564 gettext_noop("Parameter"),
4565 gettext_noop("Value"));
4569 appendPQExpBuffer(&buf
,
4570 ", s.vartype AS \"%s\", s.context AS \"%s\", ",
4571 gettext_noop("Type"),
4572 gettext_noop("Context"));
4573 if (pset
.sversion
>= 150000)
4574 printACLColumn(&buf
, "p.paracl");
4576 appendPQExpBuffer(&buf
, "NULL AS \"%s\"",
4577 gettext_noop("Access privileges"));
4580 appendPQExpBufferStr(&buf
, "\nFROM pg_catalog.pg_settings s\n");
4582 if (verbose
&& pset
.sversion
>= 150000)
4583 appendPQExpBufferStr(&buf
,
4584 " LEFT JOIN pg_catalog.pg_parameter_acl p\n"
4585 " ON pg_catalog.lower(s.name) = p.parname\n");
4588 processSQLNamePattern(pset
.db
, &buf
, pattern
,
4590 NULL
, "pg_catalog.lower(s.name)", NULL
,
4593 appendPQExpBufferStr(&buf
, "WHERE s.source <> 'default' AND\n"
4594 " s.setting IS DISTINCT FROM s.boot_val\n");
4596 appendPQExpBufferStr(&buf
, "ORDER BY 1;");
4598 res
= PSQLexec(buf
.data
);
4599 termPQExpBuffer(&buf
);
4603 myopt
.nullPrint
= NULL
;
4605 myopt
.title
= _("List of configuration parameters");
4607 myopt
.title
= _("List of non-default configuration parameters");
4608 myopt
.translate_header
= true;
4610 printQuery(res
, &myopt
, pset
.queryFout
, false, pset
.logfile
);
4619 * Describes Event Triggers.
4622 listEventTriggers(const char *pattern
, bool verbose
)
4624 PQExpBufferData buf
;
4626 printQueryOpt myopt
= pset
.popt
;
4627 static const bool translate_columns
[] =
4628 {false, false, false, true, false, false, false};
4630 if (pset
.sversion
< 90300)
4634 pg_log_error("The server (version %s) does not support event triggers.",
4635 formatPGVersionNumber(pset
.sversion
, false,
4636 sverbuf
, sizeof(sverbuf
)));
4640 initPQExpBuffer(&buf
);
4642 printfPQExpBuffer(&buf
,
4643 "SELECT evtname as \"%s\", "
4644 "evtevent as \"%s\", "
4645 "pg_catalog.pg_get_userbyid(e.evtowner) as \"%s\",\n"
4646 " case evtenabled when 'O' then '%s'"
4647 " when 'R' then '%s'"
4648 " when 'A' then '%s'"
4649 " when 'D' then '%s' end as \"%s\",\n"
4650 " e.evtfoid::pg_catalog.regproc as \"%s\", "
4651 "pg_catalog.array_to_string(array(select x"
4652 " from pg_catalog.unnest(evttags) as t(x)), ', ') as \"%s\"",
4653 gettext_noop("Name"),
4654 gettext_noop("Event"),
4655 gettext_noop("Owner"),
4656 gettext_noop("enabled"),
4657 gettext_noop("replica"),
4658 gettext_noop("always"),
4659 gettext_noop("disabled"),
4660 gettext_noop("Enabled"),
4661 gettext_noop("Function"),
4662 gettext_noop("Tags"));
4664 appendPQExpBuffer(&buf
,
4665 ",\npg_catalog.obj_description(e.oid, 'pg_event_trigger') as \"%s\"",
4666 gettext_noop("Description"));
4667 appendPQExpBufferStr(&buf
,
4668 "\nFROM pg_catalog.pg_event_trigger e ");
4670 if (!validateSQLNamePattern(&buf
, pattern
, false, false,
4671 NULL
, "evtname", NULL
, NULL
,
4674 termPQExpBuffer(&buf
);
4678 appendPQExpBufferStr(&buf
, "ORDER BY 1");
4680 res
= PSQLexec(buf
.data
);
4681 termPQExpBuffer(&buf
);
4685 myopt
.nullPrint
= NULL
;
4686 myopt
.title
= _("List of event triggers");
4687 myopt
.translate_header
= true;
4688 myopt
.translate_columns
= translate_columns
;
4689 myopt
.n_translate_columns
= lengthof(translate_columns
);
4691 printQuery(res
, &myopt
, pset
.queryFout
, false, pset
.logfile
);
4700 * Describes extended statistics.
4703 listExtendedStats(const char *pattern
)
4705 PQExpBufferData buf
;
4707 printQueryOpt myopt
= pset
.popt
;
4709 if (pset
.sversion
< 100000)
4713 pg_log_error("The server (version %s) does not support extended statistics.",
4714 formatPGVersionNumber(pset
.sversion
, false,
4715 sverbuf
, sizeof(sverbuf
)));
4719 initPQExpBuffer(&buf
);
4720 printfPQExpBuffer(&buf
,
4722 "es.stxnamespace::pg_catalog.regnamespace::pg_catalog.text AS \"%s\", \n"
4723 "es.stxname AS \"%s\", \n",
4724 gettext_noop("Schema"),
4725 gettext_noop("Name"));
4727 if (pset
.sversion
>= 140000)
4728 appendPQExpBuffer(&buf
,
4729 "pg_catalog.format('%%s FROM %%s', \n"
4730 " pg_catalog.pg_get_statisticsobjdef_columns(es.oid), \n"
4731 " es.stxrelid::pg_catalog.regclass) AS \"%s\"",
4732 gettext_noop("Definition"));
4734 appendPQExpBuffer(&buf
,
4735 "pg_catalog.format('%%s FROM %%s', \n"
4736 " (SELECT pg_catalog.string_agg(pg_catalog.quote_ident(a.attname),', ') \n"
4737 " FROM pg_catalog.unnest(es.stxkeys) s(attnum) \n"
4738 " JOIN pg_catalog.pg_attribute a \n"
4739 " ON (es.stxrelid = a.attrelid \n"
4740 " AND a.attnum = s.attnum \n"
4741 " AND NOT a.attisdropped)), \n"
4742 "es.stxrelid::pg_catalog.regclass) AS \"%s\"",
4743 gettext_noop("Definition"));
4745 appendPQExpBuffer(&buf
,
4746 ",\nCASE WHEN 'd' = any(es.stxkind) THEN 'defined' \n"
4748 "CASE WHEN 'f' = any(es.stxkind) THEN 'defined' \n"
4750 gettext_noop("Ndistinct"),
4751 gettext_noop("Dependencies"));
4754 * Include the MCV statistics kind.
4756 if (pset
.sversion
>= 120000)
4758 appendPQExpBuffer(&buf
,
4759 ",\nCASE WHEN 'm' = any(es.stxkind) THEN 'defined' \n"
4761 gettext_noop("MCV"));
4764 appendPQExpBufferStr(&buf
,
4765 " \nFROM pg_catalog.pg_statistic_ext es \n");
4767 if (!validateSQLNamePattern(&buf
, pattern
,
4769 "es.stxnamespace::pg_catalog.regnamespace::pg_catalog.text", "es.stxname",
4770 NULL
, "pg_catalog.pg_statistics_obj_is_visible(es.oid)",
4773 termPQExpBuffer(&buf
);
4777 appendPQExpBufferStr(&buf
, "ORDER BY 1, 2;");
4779 res
= PSQLexec(buf
.data
);
4780 termPQExpBuffer(&buf
);
4784 myopt
.nullPrint
= NULL
;
4785 myopt
.title
= _("List of extended statistics");
4786 myopt
.translate_header
= true;
4788 printQuery(res
, &myopt
, pset
.queryFout
, false, pset
.logfile
);
4800 listCasts(const char *pattern
, bool verbose
)
4802 PQExpBufferData buf
;
4804 printQueryOpt myopt
= pset
.popt
;
4805 static const bool translate_columns
[] = {false, false, false, true, false};
4807 initPQExpBuffer(&buf
);
4809 printfPQExpBuffer(&buf
,
4810 "SELECT pg_catalog.format_type(castsource, NULL) AS \"%s\",\n"
4811 " pg_catalog.format_type(casttarget, NULL) AS \"%s\",\n",
4812 gettext_noop("Source type"),
4813 gettext_noop("Target type"));
4816 * We don't attempt to localize '(binary coercible)' or '(with inout)',
4817 * because there's too much risk of gettext translating a function name
4818 * that happens to match some string in the PO database.
4820 appendPQExpBuffer(&buf
,
4821 " CASE WHEN c.castmethod = '%c' THEN '(binary coercible)'\n"
4822 " WHEN c.castmethod = '%c' THEN '(with inout)'\n"
4824 " END AS \"%s\",\n",
4825 COERCION_METHOD_BINARY
,
4826 COERCION_METHOD_INOUT
,
4827 gettext_noop("Function"));
4829 appendPQExpBuffer(&buf
,
4830 " CASE WHEN c.castcontext = '%c' THEN '%s'\n"
4831 " WHEN c.castcontext = '%c' THEN '%s'\n"
4834 COERCION_CODE_EXPLICIT
,
4836 COERCION_CODE_ASSIGNMENT
,
4837 gettext_noop("in assignment"),
4838 gettext_noop("yes"),
4839 gettext_noop("Implicit?"));
4842 appendPQExpBuffer(&buf
,
4843 ",\n d.description AS \"%s\"",
4844 gettext_noop("Description"));
4847 * We need a left join to pg_proc for binary casts; the others are just
4850 appendPQExpBufferStr(&buf
,
4851 "\nFROM pg_catalog.pg_cast c LEFT JOIN pg_catalog.pg_proc p\n"
4852 " ON c.castfunc = p.oid\n"
4853 " LEFT JOIN pg_catalog.pg_type ts\n"
4854 " ON c.castsource = ts.oid\n"
4855 " LEFT JOIN pg_catalog.pg_namespace ns\n"
4856 " ON ns.oid = ts.typnamespace\n"
4857 " LEFT JOIN pg_catalog.pg_type tt\n"
4858 " ON c.casttarget = tt.oid\n"
4859 " LEFT JOIN pg_catalog.pg_namespace nt\n"
4860 " ON nt.oid = tt.typnamespace\n");
4863 appendPQExpBufferStr(&buf
,
4864 " LEFT JOIN pg_catalog.pg_description d\n"
4865 " ON d.classoid = c.tableoid AND d.objoid = "
4866 "c.oid AND d.objsubid = 0\n");
4868 appendPQExpBufferStr(&buf
, "WHERE ( (true");
4871 * Match name pattern against either internal or external name of either
4872 * castsource or casttarget
4874 if (!validateSQLNamePattern(&buf
, pattern
, true, false,
4875 "ns.nspname", "ts.typname",
4876 "pg_catalog.format_type(ts.oid, NULL)",
4877 "pg_catalog.pg_type_is_visible(ts.oid)",
4881 appendPQExpBufferStr(&buf
, ") OR (true");
4883 if (!validateSQLNamePattern(&buf
, pattern
, true, false,
4884 "nt.nspname", "tt.typname",
4885 "pg_catalog.format_type(tt.oid, NULL)",
4886 "pg_catalog.pg_type_is_visible(tt.oid)",
4890 appendPQExpBufferStr(&buf
, ") )\nORDER BY 1, 2;");
4892 res
= PSQLexec(buf
.data
);
4893 termPQExpBuffer(&buf
);
4897 myopt
.nullPrint
= NULL
;
4898 myopt
.title
= _("List of casts");
4899 myopt
.translate_header
= true;
4900 myopt
.translate_columns
= translate_columns
;
4901 myopt
.n_translate_columns
= lengthof(translate_columns
);
4903 printQuery(res
, &myopt
, pset
.queryFout
, false, pset
.logfile
);
4909 termPQExpBuffer(&buf
);
4916 * Describes collations.
4919 listCollations(const char *pattern
, bool verbose
, bool showSystem
)
4921 PQExpBufferData buf
;
4923 printQueryOpt myopt
= pset
.popt
;
4924 static const bool translate_columns
[] = {false, false, false, false, false, false, false, true, false};
4926 initPQExpBuffer(&buf
);
4928 printfPQExpBuffer(&buf
,
4930 " n.nspname AS \"%s\",\n"
4931 " c.collname AS \"%s\",\n",
4932 gettext_noop("Schema"),
4933 gettext_noop("Name"));
4935 if (pset
.sversion
>= 100000)
4936 appendPQExpBuffer(&buf
,
4937 " CASE c.collprovider WHEN 'd' THEN 'default' WHEN 'c' THEN 'libc' WHEN 'i' THEN 'icu' END AS \"%s\",\n",
4938 gettext_noop("Provider"));
4940 appendPQExpBuffer(&buf
,
4941 " 'libc' AS \"%s\",\n",
4942 gettext_noop("Provider"));
4944 appendPQExpBuffer(&buf
,
4945 " c.collcollate AS \"%s\",\n"
4946 " c.collctype AS \"%s\",\n",
4947 gettext_noop("Collate"),
4948 gettext_noop("Ctype"));
4950 if (pset
.sversion
>= 150000)
4951 appendPQExpBuffer(&buf
,
4952 " c.colliculocale AS \"%s\",\n",
4953 gettext_noop("ICU Locale"));
4955 appendPQExpBuffer(&buf
,
4956 " c.collcollate AS \"%s\",\n",
4957 gettext_noop("ICU Locale"));
4959 if (pset
.sversion
>= 160000)
4960 appendPQExpBuffer(&buf
,
4961 " c.collicurules AS \"%s\",\n",
4962 gettext_noop("ICU Rules"));
4964 appendPQExpBuffer(&buf
,
4965 " NULL AS \"%s\",\n",
4966 gettext_noop("ICU Rules"));
4968 if (pset
.sversion
>= 120000)
4969 appendPQExpBuffer(&buf
,
4970 " CASE WHEN c.collisdeterministic THEN '%s' ELSE '%s' END AS \"%s\"",
4971 gettext_noop("yes"), gettext_noop("no"),
4972 gettext_noop("Deterministic?"));
4974 appendPQExpBuffer(&buf
,
4976 gettext_noop("yes"),
4977 gettext_noop("Deterministic?"));
4980 appendPQExpBuffer(&buf
,
4981 ",\n pg_catalog.obj_description(c.oid, 'pg_collation') AS \"%s\"",
4982 gettext_noop("Description"));
4984 appendPQExpBufferStr(&buf
,
4985 "\nFROM pg_catalog.pg_collation c, pg_catalog.pg_namespace n\n"
4986 "WHERE n.oid = c.collnamespace\n");
4988 if (!showSystem
&& !pattern
)
4989 appendPQExpBufferStr(&buf
, " AND n.nspname <> 'pg_catalog'\n"
4990 " AND n.nspname <> 'information_schema'\n");
4993 * Hide collations that aren't usable in the current database's encoding.
4994 * If you think to change this, note that pg_collation_is_visible rejects
4995 * unusable collations, so you will need to hack name pattern processing
4996 * somehow to avoid inconsistent behavior.
4998 appendPQExpBufferStr(&buf
, " AND c.collencoding IN (-1, pg_catalog.pg_char_to_encoding(pg_catalog.getdatabaseencoding()))\n");
5000 if (!validateSQLNamePattern(&buf
, pattern
, true, false,
5001 "n.nspname", "c.collname", NULL
,
5002 "pg_catalog.pg_collation_is_visible(c.oid)",
5005 termPQExpBuffer(&buf
);
5009 appendPQExpBufferStr(&buf
, "ORDER BY 1, 2;");
5011 res
= PSQLexec(buf
.data
);
5012 termPQExpBuffer(&buf
);
5016 myopt
.nullPrint
= NULL
;
5017 myopt
.title
= _("List of collations");
5018 myopt
.translate_header
= true;
5019 myopt
.translate_columns
= translate_columns
;
5020 myopt
.n_translate_columns
= lengthof(translate_columns
);
5022 printQuery(res
, &myopt
, pset
.queryFout
, false, pset
.logfile
);
5031 * Describes schemas (namespaces)
5034 listSchemas(const char *pattern
, bool verbose
, bool showSystem
)
5036 PQExpBufferData buf
;
5038 printQueryOpt myopt
= pset
.popt
;
5039 int pub_schema_tuples
= 0;
5040 char **footers
= NULL
;
5042 initPQExpBuffer(&buf
);
5043 printfPQExpBuffer(&buf
,
5044 "SELECT n.nspname AS \"%s\",\n"
5045 " pg_catalog.pg_get_userbyid(n.nspowner) AS \"%s\"",
5046 gettext_noop("Name"),
5047 gettext_noop("Owner"));
5051 appendPQExpBufferStr(&buf
, ",\n ");
5052 printACLColumn(&buf
, "n.nspacl");
5053 appendPQExpBuffer(&buf
,
5054 ",\n pg_catalog.obj_description(n.oid, 'pg_namespace') AS \"%s\"",
5055 gettext_noop("Description"));
5058 appendPQExpBufferStr(&buf
,
5059 "\nFROM pg_catalog.pg_namespace n\n");
5061 if (!showSystem
&& !pattern
)
5062 appendPQExpBufferStr(&buf
,
5063 "WHERE n.nspname !~ '^pg_' AND n.nspname <> 'information_schema'\n");
5065 if (!validateSQLNamePattern(&buf
, pattern
,
5066 !showSystem
&& !pattern
, false,
5067 NULL
, "n.nspname", NULL
,
5072 appendPQExpBufferStr(&buf
, "ORDER BY 1;");
5074 res
= PSQLexec(buf
.data
);
5078 myopt
.nullPrint
= NULL
;
5079 myopt
.title
= _("List of schemas");
5080 myopt
.translate_header
= true;
5082 if (pattern
&& pset
.sversion
>= 150000)
5087 printfPQExpBuffer(&buf
,
5089 "FROM pg_catalog.pg_publication p\n"
5090 " JOIN pg_catalog.pg_publication_namespace pn ON p.oid = pn.pnpubid\n"
5091 " JOIN pg_catalog.pg_namespace n ON n.oid = pn.pnnspid \n"
5092 "WHERE n.nspname = '%s'\n"
5095 result
= PSQLexec(buf
.data
);
5099 pub_schema_tuples
= PQntuples(result
);
5101 if (pub_schema_tuples
> 0)
5104 * Allocate memory for footers. Size of footers will be 1 (for
5105 * storing "Publications:" string) + publication schema mapping
5106 * count + 1 (for storing NULL).
5108 footers
= (char **) pg_malloc((1 + pub_schema_tuples
+ 1) * sizeof(char *));
5109 footers
[0] = pg_strdup(_("Publications:"));
5111 /* Might be an empty set - that's ok */
5112 for (i
= 0; i
< pub_schema_tuples
; i
++)
5114 printfPQExpBuffer(&buf
, " \"%s\"",
5115 PQgetvalue(result
, i
, 0));
5117 footers
[i
+ 1] = pg_strdup(buf
.data
);
5120 footers
[i
+ 1] = NULL
;
5121 myopt
.footers
= footers
;
5127 printQuery(res
, &myopt
, pset
.queryFout
, false, pset
.logfile
);
5129 termPQExpBuffer(&buf
);
5132 /* Free the memory allocated for the footer */
5135 char **footer
= NULL
;
5137 for (footer
= footers
; *footer
; footer
++)
5146 termPQExpBuffer(&buf
);
5153 * list text search parsers
5156 listTSParsers(const char *pattern
, bool verbose
)
5158 PQExpBufferData buf
;
5160 printQueryOpt myopt
= pset
.popt
;
5163 return listTSParsersVerbose(pattern
);
5165 initPQExpBuffer(&buf
);
5167 printfPQExpBuffer(&buf
,
5169 " n.nspname as \"%s\",\n"
5170 " p.prsname as \"%s\",\n"
5171 " pg_catalog.obj_description(p.oid, 'pg_ts_parser') as \"%s\"\n"
5172 "FROM pg_catalog.pg_ts_parser p\n"
5173 "LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.prsnamespace\n",
5174 gettext_noop("Schema"),
5175 gettext_noop("Name"),
5176 gettext_noop("Description")
5179 if (!validateSQLNamePattern(&buf
, pattern
, false, false,
5180 "n.nspname", "p.prsname", NULL
,
5181 "pg_catalog.pg_ts_parser_is_visible(p.oid)",
5184 termPQExpBuffer(&buf
);
5188 appendPQExpBufferStr(&buf
, "ORDER BY 1, 2;");
5190 res
= PSQLexec(buf
.data
);
5191 termPQExpBuffer(&buf
);
5195 myopt
.nullPrint
= NULL
;
5196 myopt
.title
= _("List of text search parsers");
5197 myopt
.translate_header
= true;
5199 printQuery(res
, &myopt
, pset
.queryFout
, false, pset
.logfile
);
5206 * full description of parsers
5209 listTSParsersVerbose(const char *pattern
)
5211 PQExpBufferData buf
;
5215 initPQExpBuffer(&buf
);
5217 printfPQExpBuffer(&buf
,
5221 "FROM pg_catalog.pg_ts_parser p\n"
5222 "LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.prsnamespace\n"
5225 if (!validateSQLNamePattern(&buf
, pattern
, false, false,
5226 "n.nspname", "p.prsname", NULL
,
5227 "pg_catalog.pg_ts_parser_is_visible(p.oid)",
5230 termPQExpBuffer(&buf
);
5234 appendPQExpBufferStr(&buf
, "ORDER BY 1, 2;");
5236 res
= PSQLexec(buf
.data
);
5237 termPQExpBuffer(&buf
);
5241 if (PQntuples(res
) == 0)
5246 pg_log_error("Did not find any text search parser named \"%s\".",
5249 pg_log_error("Did not find any text search parsers.");
5255 for (i
= 0; i
< PQntuples(res
); i
++)
5258 const char *nspname
= NULL
;
5259 const char *prsname
;
5261 oid
= PQgetvalue(res
, i
, 0);
5262 if (!PQgetisnull(res
, i
, 1))
5263 nspname
= PQgetvalue(res
, i
, 1);
5264 prsname
= PQgetvalue(res
, i
, 2);
5266 if (!describeOneTSParser(oid
, nspname
, prsname
))
5284 describeOneTSParser(const char *oid
, const char *nspname
, const char *prsname
)
5286 PQExpBufferData buf
;
5288 PQExpBufferData title
;
5289 printQueryOpt myopt
= pset
.popt
;
5290 static const bool translate_columns
[] = {true, false, false};
5292 initPQExpBuffer(&buf
);
5294 printfPQExpBuffer(&buf
,
5295 "SELECT '%s' AS \"%s\",\n"
5296 " p.prsstart::pg_catalog.regproc AS \"%s\",\n"
5297 " pg_catalog.obj_description(p.prsstart, 'pg_proc') as \"%s\"\n"
5298 " FROM pg_catalog.pg_ts_parser p\n"
5299 " WHERE p.oid = '%s'\n"
5302 " p.prstoken::pg_catalog.regproc,\n"
5303 " pg_catalog.obj_description(p.prstoken, 'pg_proc')\n"
5304 " FROM pg_catalog.pg_ts_parser p\n"
5305 " WHERE p.oid = '%s'\n"
5308 " p.prsend::pg_catalog.regproc,\n"
5309 " pg_catalog.obj_description(p.prsend, 'pg_proc')\n"
5310 " FROM pg_catalog.pg_ts_parser p\n"
5311 " WHERE p.oid = '%s'\n"
5314 " p.prsheadline::pg_catalog.regproc,\n"
5315 " pg_catalog.obj_description(p.prsheadline, 'pg_proc')\n"
5316 " FROM pg_catalog.pg_ts_parser p\n"
5317 " WHERE p.oid = '%s'\n"
5320 " p.prslextype::pg_catalog.regproc,\n"
5321 " pg_catalog.obj_description(p.prslextype, 'pg_proc')\n"
5322 " FROM pg_catalog.pg_ts_parser p\n"
5323 " WHERE p.oid = '%s';",
5324 gettext_noop("Start parse"),
5325 gettext_noop("Method"),
5326 gettext_noop("Function"),
5327 gettext_noop("Description"),
5329 gettext_noop("Get next token"),
5331 gettext_noop("End parse"),
5333 gettext_noop("Get headline"),
5335 gettext_noop("Get token types"),
5338 res
= PSQLexec(buf
.data
);
5339 termPQExpBuffer(&buf
);
5343 myopt
.nullPrint
= NULL
;
5344 initPQExpBuffer(&title
);
5346 printfPQExpBuffer(&title
, _("Text search parser \"%s.%s\""),
5349 printfPQExpBuffer(&title
, _("Text search parser \"%s\""), prsname
);
5350 myopt
.title
= title
.data
;
5351 myopt
.footers
= NULL
;
5352 myopt
.topt
.default_footer
= false;
5353 myopt
.translate_header
= true;
5354 myopt
.translate_columns
= translate_columns
;
5355 myopt
.n_translate_columns
= lengthof(translate_columns
);
5357 printQuery(res
, &myopt
, pset
.queryFout
, false, pset
.logfile
);
5361 initPQExpBuffer(&buf
);
5363 printfPQExpBuffer(&buf
,
5364 "SELECT t.alias as \"%s\",\n"
5365 " t.description as \"%s\"\n"
5366 "FROM pg_catalog.ts_token_type( '%s'::pg_catalog.oid ) as t\n"
5368 gettext_noop("Token name"),
5369 gettext_noop("Description"),
5372 res
= PSQLexec(buf
.data
);
5373 termPQExpBuffer(&buf
);
5376 termPQExpBuffer(&title
);
5380 myopt
.nullPrint
= NULL
;
5382 printfPQExpBuffer(&title
, _("Token types for parser \"%s.%s\""),
5385 printfPQExpBuffer(&title
, _("Token types for parser \"%s\""), prsname
);
5386 myopt
.title
= title
.data
;
5387 myopt
.footers
= NULL
;
5388 myopt
.topt
.default_footer
= true;
5389 myopt
.translate_header
= true;
5390 myopt
.translate_columns
= NULL
;
5391 myopt
.n_translate_columns
= 0;
5393 printQuery(res
, &myopt
, pset
.queryFout
, false, pset
.logfile
);
5395 termPQExpBuffer(&title
);
5403 * list text search dictionaries
5406 listTSDictionaries(const char *pattern
, bool verbose
)
5408 PQExpBufferData buf
;
5410 printQueryOpt myopt
= pset
.popt
;
5412 initPQExpBuffer(&buf
);
5414 printfPQExpBuffer(&buf
,
5416 " n.nspname as \"%s\",\n"
5417 " d.dictname as \"%s\",\n",
5418 gettext_noop("Schema"),
5419 gettext_noop("Name"));
5423 appendPQExpBuffer(&buf
,
5424 " ( SELECT COALESCE(nt.nspname, '(null)')::pg_catalog.text || '.' || t.tmplname FROM\n"
5425 " pg_catalog.pg_ts_template t\n"
5426 " LEFT JOIN pg_catalog.pg_namespace nt ON nt.oid = t.tmplnamespace\n"
5427 " WHERE d.dicttemplate = t.oid ) AS \"%s\",\n"
5428 " d.dictinitoption as \"%s\",\n",
5429 gettext_noop("Template"),
5430 gettext_noop("Init options"));
5433 appendPQExpBuffer(&buf
,
5434 " pg_catalog.obj_description(d.oid, 'pg_ts_dict') as \"%s\"\n",
5435 gettext_noop("Description"));
5437 appendPQExpBufferStr(&buf
, "FROM pg_catalog.pg_ts_dict d\n"
5438 "LEFT JOIN pg_catalog.pg_namespace n ON n.oid = d.dictnamespace\n");
5440 if (!validateSQLNamePattern(&buf
, pattern
, false, false,
5441 "n.nspname", "d.dictname", NULL
,
5442 "pg_catalog.pg_ts_dict_is_visible(d.oid)",
5445 termPQExpBuffer(&buf
);
5449 appendPQExpBufferStr(&buf
, "ORDER BY 1, 2;");
5451 res
= PSQLexec(buf
.data
);
5452 termPQExpBuffer(&buf
);
5456 myopt
.nullPrint
= NULL
;
5457 myopt
.title
= _("List of text search dictionaries");
5458 myopt
.translate_header
= true;
5460 printQuery(res
, &myopt
, pset
.queryFout
, false, pset
.logfile
);
5469 * list text search templates
5472 listTSTemplates(const char *pattern
, bool verbose
)
5474 PQExpBufferData buf
;
5476 printQueryOpt myopt
= pset
.popt
;
5478 initPQExpBuffer(&buf
);
5481 printfPQExpBuffer(&buf
,
5483 " n.nspname AS \"%s\",\n"
5484 " t.tmplname AS \"%s\",\n"
5485 " t.tmplinit::pg_catalog.regproc AS \"%s\",\n"
5486 " t.tmpllexize::pg_catalog.regproc AS \"%s\",\n"
5487 " pg_catalog.obj_description(t.oid, 'pg_ts_template') AS \"%s\"\n",
5488 gettext_noop("Schema"),
5489 gettext_noop("Name"),
5490 gettext_noop("Init"),
5491 gettext_noop("Lexize"),
5492 gettext_noop("Description"));
5494 printfPQExpBuffer(&buf
,
5496 " n.nspname AS \"%s\",\n"
5497 " t.tmplname AS \"%s\",\n"
5498 " pg_catalog.obj_description(t.oid, 'pg_ts_template') AS \"%s\"\n",
5499 gettext_noop("Schema"),
5500 gettext_noop("Name"),
5501 gettext_noop("Description"));
5503 appendPQExpBufferStr(&buf
, "FROM pg_catalog.pg_ts_template t\n"
5504 "LEFT JOIN pg_catalog.pg_namespace n ON n.oid = t.tmplnamespace\n");
5506 if (!validateSQLNamePattern(&buf
, pattern
, false, false,
5507 "n.nspname", "t.tmplname", NULL
,
5508 "pg_catalog.pg_ts_template_is_visible(t.oid)",
5511 termPQExpBuffer(&buf
);
5515 appendPQExpBufferStr(&buf
, "ORDER BY 1, 2;");
5517 res
= PSQLexec(buf
.data
);
5518 termPQExpBuffer(&buf
);
5522 myopt
.nullPrint
= NULL
;
5523 myopt
.title
= _("List of text search templates");
5524 myopt
.translate_header
= true;
5526 printQuery(res
, &myopt
, pset
.queryFout
, false, pset
.logfile
);
5535 * list text search configurations
5538 listTSConfigs(const char *pattern
, bool verbose
)
5540 PQExpBufferData buf
;
5542 printQueryOpt myopt
= pset
.popt
;
5545 return listTSConfigsVerbose(pattern
);
5547 initPQExpBuffer(&buf
);
5549 printfPQExpBuffer(&buf
,
5551 " n.nspname as \"%s\",\n"
5552 " c.cfgname as \"%s\",\n"
5553 " pg_catalog.obj_description(c.oid, 'pg_ts_config') as \"%s\"\n"
5554 "FROM pg_catalog.pg_ts_config c\n"
5555 "LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.cfgnamespace\n",
5556 gettext_noop("Schema"),
5557 gettext_noop("Name"),
5558 gettext_noop("Description")
5561 if (!validateSQLNamePattern(&buf
, pattern
, false, false,
5562 "n.nspname", "c.cfgname", NULL
,
5563 "pg_catalog.pg_ts_config_is_visible(c.oid)",
5566 termPQExpBuffer(&buf
);
5570 appendPQExpBufferStr(&buf
, "ORDER BY 1, 2;");
5572 res
= PSQLexec(buf
.data
);
5573 termPQExpBuffer(&buf
);
5577 myopt
.nullPrint
= NULL
;
5578 myopt
.title
= _("List of text search configurations");
5579 myopt
.translate_header
= true;
5581 printQuery(res
, &myopt
, pset
.queryFout
, false, pset
.logfile
);
5588 listTSConfigsVerbose(const char *pattern
)
5590 PQExpBufferData buf
;
5594 initPQExpBuffer(&buf
);
5596 printfPQExpBuffer(&buf
,
5597 "SELECT c.oid, c.cfgname,\n"
5600 " np.nspname as pnspname\n"
5601 "FROM pg_catalog.pg_ts_config c\n"
5602 " LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.cfgnamespace,\n"
5603 " pg_catalog.pg_ts_parser p\n"
5604 " LEFT JOIN pg_catalog.pg_namespace np ON np.oid = p.prsnamespace\n"
5605 "WHERE p.oid = c.cfgparser\n"
5608 if (!validateSQLNamePattern(&buf
, pattern
, true, false,
5609 "n.nspname", "c.cfgname", NULL
,
5610 "pg_catalog.pg_ts_config_is_visible(c.oid)",
5613 termPQExpBuffer(&buf
);
5617 appendPQExpBufferStr(&buf
, "ORDER BY 3, 2;");
5619 res
= PSQLexec(buf
.data
);
5620 termPQExpBuffer(&buf
);
5624 if (PQntuples(res
) == 0)
5629 pg_log_error("Did not find any text search configuration named \"%s\".",
5632 pg_log_error("Did not find any text search configurations.");
5638 for (i
= 0; i
< PQntuples(res
); i
++)
5641 const char *cfgname
;
5642 const char *nspname
= NULL
;
5643 const char *prsname
;
5644 const char *pnspname
= NULL
;
5646 oid
= PQgetvalue(res
, i
, 0);
5647 cfgname
= PQgetvalue(res
, i
, 1);
5648 if (!PQgetisnull(res
, i
, 2))
5649 nspname
= PQgetvalue(res
, i
, 2);
5650 prsname
= PQgetvalue(res
, i
, 3);
5651 if (!PQgetisnull(res
, i
, 4))
5652 pnspname
= PQgetvalue(res
, i
, 4);
5654 if (!describeOneTSConfig(oid
, nspname
, cfgname
, pnspname
, prsname
))
5672 describeOneTSConfig(const char *oid
, const char *nspname
, const char *cfgname
,
5673 const char *pnspname
, const char *prsname
)
5675 PQExpBufferData buf
,
5678 printQueryOpt myopt
= pset
.popt
;
5680 initPQExpBuffer(&buf
);
5682 printfPQExpBuffer(&buf
,
5684 " ( SELECT t.alias FROM\n"
5685 " pg_catalog.ts_token_type(c.cfgparser) AS t\n"
5686 " WHERE t.tokid = m.maptokentype ) AS \"%s\",\n"
5687 " pg_catalog.btrim(\n"
5688 " ARRAY( SELECT mm.mapdict::pg_catalog.regdictionary\n"
5689 " FROM pg_catalog.pg_ts_config_map AS mm\n"
5690 " WHERE mm.mapcfg = m.mapcfg AND mm.maptokentype = m.maptokentype\n"
5691 " ORDER BY mapcfg, maptokentype, mapseqno\n"
5692 " ) :: pg_catalog.text,\n"
5693 " '{}') AS \"%s\"\n"
5694 "FROM pg_catalog.pg_ts_config AS c, pg_catalog.pg_ts_config_map AS m\n"
5695 "WHERE c.oid = '%s' AND m.mapcfg = c.oid\n"
5696 "GROUP BY m.mapcfg, m.maptokentype, c.cfgparser\n"
5698 gettext_noop("Token"),
5699 gettext_noop("Dictionaries"),
5702 res
= PSQLexec(buf
.data
);
5703 termPQExpBuffer(&buf
);
5707 initPQExpBuffer(&title
);
5710 appendPQExpBuffer(&title
, _("Text search configuration \"%s.%s\""),
5713 appendPQExpBuffer(&title
, _("Text search configuration \"%s\""),
5717 appendPQExpBuffer(&title
, _("\nParser: \"%s.%s\""),
5720 appendPQExpBuffer(&title
, _("\nParser: \"%s\""),
5723 myopt
.nullPrint
= NULL
;
5724 myopt
.title
= title
.data
;
5725 myopt
.footers
= NULL
;
5726 myopt
.topt
.default_footer
= false;
5727 myopt
.translate_header
= true;
5729 printQuery(res
, &myopt
, pset
.queryFout
, false, pset
.logfile
);
5731 termPQExpBuffer(&title
);
5741 * Describes foreign-data wrappers
5744 listForeignDataWrappers(const char *pattern
, bool verbose
)
5746 PQExpBufferData buf
;
5748 printQueryOpt myopt
= pset
.popt
;
5750 initPQExpBuffer(&buf
);
5751 printfPQExpBuffer(&buf
,
5752 "SELECT fdw.fdwname AS \"%s\",\n"
5753 " pg_catalog.pg_get_userbyid(fdw.fdwowner) AS \"%s\",\n"
5754 " fdw.fdwhandler::pg_catalog.regproc AS \"%s\",\n"
5755 " fdw.fdwvalidator::pg_catalog.regproc AS \"%s\"",
5756 gettext_noop("Name"),
5757 gettext_noop("Owner"),
5758 gettext_noop("Handler"),
5759 gettext_noop("Validator"));
5763 appendPQExpBufferStr(&buf
, ",\n ");
5764 printACLColumn(&buf
, "fdwacl");
5765 appendPQExpBuffer(&buf
,
5766 ",\n CASE WHEN fdwoptions IS NULL THEN '' ELSE "
5767 " '(' || pg_catalog.array_to_string(ARRAY(SELECT "
5768 " pg_catalog.quote_ident(option_name) || ' ' || "
5769 " pg_catalog.quote_literal(option_value) FROM "
5770 " pg_catalog.pg_options_to_table(fdwoptions)), ', ') || ')' "
5772 ",\n d.description AS \"%s\" ",
5773 gettext_noop("FDW options"),
5774 gettext_noop("Description"));
5777 appendPQExpBufferStr(&buf
, "\nFROM pg_catalog.pg_foreign_data_wrapper fdw\n");
5780 appendPQExpBufferStr(&buf
,
5781 "LEFT JOIN pg_catalog.pg_description d\n"
5782 " ON d.classoid = fdw.tableoid "
5783 "AND d.objoid = fdw.oid AND d.objsubid = 0\n");
5785 if (!validateSQLNamePattern(&buf
, pattern
, false, false,
5786 NULL
, "fdwname", NULL
, NULL
,
5789 termPQExpBuffer(&buf
);
5793 appendPQExpBufferStr(&buf
, "ORDER BY 1;");
5795 res
= PSQLexec(buf
.data
);
5796 termPQExpBuffer(&buf
);
5800 myopt
.nullPrint
= NULL
;
5801 myopt
.title
= _("List of foreign-data wrappers");
5802 myopt
.translate_header
= true;
5804 printQuery(res
, &myopt
, pset
.queryFout
, false, pset
.logfile
);
5813 * Describes foreign servers.
5816 listForeignServers(const char *pattern
, bool verbose
)
5818 PQExpBufferData buf
;
5820 printQueryOpt myopt
= pset
.popt
;
5822 initPQExpBuffer(&buf
);
5823 printfPQExpBuffer(&buf
,
5824 "SELECT s.srvname AS \"%s\",\n"
5825 " pg_catalog.pg_get_userbyid(s.srvowner) AS \"%s\",\n"
5826 " f.fdwname AS \"%s\"",
5827 gettext_noop("Name"),
5828 gettext_noop("Owner"),
5829 gettext_noop("Foreign-data wrapper"));
5833 appendPQExpBufferStr(&buf
, ",\n ");
5834 printACLColumn(&buf
, "s.srvacl");
5835 appendPQExpBuffer(&buf
,
5837 " s.srvtype AS \"%s\",\n"
5838 " s.srvversion AS \"%s\",\n"
5839 " CASE WHEN srvoptions IS NULL THEN '' ELSE "
5840 " '(' || pg_catalog.array_to_string(ARRAY(SELECT "
5841 " pg_catalog.quote_ident(option_name) || ' ' || "
5842 " pg_catalog.quote_literal(option_value) FROM "
5843 " pg_catalog.pg_options_to_table(srvoptions)), ', ') || ')' "
5845 " d.description AS \"%s\"",
5846 gettext_noop("Type"),
5847 gettext_noop("Version"),
5848 gettext_noop("FDW options"),
5849 gettext_noop("Description"));
5852 appendPQExpBufferStr(&buf
,
5853 "\nFROM pg_catalog.pg_foreign_server s\n"
5854 " JOIN pg_catalog.pg_foreign_data_wrapper f ON f.oid=s.srvfdw\n");
5857 appendPQExpBufferStr(&buf
,
5858 "LEFT JOIN pg_catalog.pg_description d\n "
5859 "ON d.classoid = s.tableoid AND d.objoid = s.oid "
5860 "AND d.objsubid = 0\n");
5862 if (!validateSQLNamePattern(&buf
, pattern
, false, false,
5863 NULL
, "s.srvname", NULL
, NULL
,
5866 termPQExpBuffer(&buf
);
5870 appendPQExpBufferStr(&buf
, "ORDER BY 1;");
5872 res
= PSQLexec(buf
.data
);
5873 termPQExpBuffer(&buf
);
5877 myopt
.nullPrint
= NULL
;
5878 myopt
.title
= _("List of foreign servers");
5879 myopt
.translate_header
= true;
5881 printQuery(res
, &myopt
, pset
.queryFout
, false, pset
.logfile
);
5890 * Describes user mappings.
5893 listUserMappings(const char *pattern
, bool verbose
)
5895 PQExpBufferData buf
;
5897 printQueryOpt myopt
= pset
.popt
;
5899 initPQExpBuffer(&buf
);
5900 printfPQExpBuffer(&buf
,
5901 "SELECT um.srvname AS \"%s\",\n"
5902 " um.usename AS \"%s\"",
5903 gettext_noop("Server"),
5904 gettext_noop("User name"));
5907 appendPQExpBuffer(&buf
,
5908 ",\n CASE WHEN umoptions IS NULL THEN '' ELSE "
5909 " '(' || pg_catalog.array_to_string(ARRAY(SELECT "
5910 " pg_catalog.quote_ident(option_name) || ' ' || "
5911 " pg_catalog.quote_literal(option_value) FROM "
5912 " pg_catalog.pg_options_to_table(umoptions)), ', ') || ')' "
5914 gettext_noop("FDW options"));
5916 appendPQExpBufferStr(&buf
, "\nFROM pg_catalog.pg_user_mappings um\n");
5918 if (!validateSQLNamePattern(&buf
, pattern
, false, false,
5919 NULL
, "um.srvname", "um.usename", NULL
,
5922 termPQExpBuffer(&buf
);
5926 appendPQExpBufferStr(&buf
, "ORDER BY 1, 2;");
5928 res
= PSQLexec(buf
.data
);
5929 termPQExpBuffer(&buf
);
5933 myopt
.nullPrint
= NULL
;
5934 myopt
.title
= _("List of user mappings");
5935 myopt
.translate_header
= true;
5937 printQuery(res
, &myopt
, pset
.queryFout
, false, pset
.logfile
);
5946 * Describes foreign tables.
5949 listForeignTables(const char *pattern
, bool verbose
)
5951 PQExpBufferData buf
;
5953 printQueryOpt myopt
= pset
.popt
;
5955 initPQExpBuffer(&buf
);
5956 printfPQExpBuffer(&buf
,
5957 "SELECT n.nspname AS \"%s\",\n"
5958 " c.relname AS \"%s\",\n"
5959 " s.srvname AS \"%s\"",
5960 gettext_noop("Schema"),
5961 gettext_noop("Table"),
5962 gettext_noop("Server"));
5965 appendPQExpBuffer(&buf
,
5966 ",\n CASE WHEN ftoptions IS NULL THEN '' ELSE "
5967 " '(' || pg_catalog.array_to_string(ARRAY(SELECT "
5968 " pg_catalog.quote_ident(option_name) || ' ' || "
5969 " pg_catalog.quote_literal(option_value) FROM "
5970 " pg_catalog.pg_options_to_table(ftoptions)), ', ') || ')' "
5972 " d.description AS \"%s\"",
5973 gettext_noop("FDW options"),
5974 gettext_noop("Description"));
5976 appendPQExpBufferStr(&buf
,
5977 "\nFROM pg_catalog.pg_foreign_table ft\n"
5978 " INNER JOIN pg_catalog.pg_class c"
5979 " ON c.oid = ft.ftrelid\n"
5980 " INNER JOIN pg_catalog.pg_namespace n"
5981 " ON n.oid = c.relnamespace\n"
5982 " INNER JOIN pg_catalog.pg_foreign_server s"
5983 " ON s.oid = ft.ftserver\n");
5985 appendPQExpBufferStr(&buf
,
5986 " LEFT JOIN pg_catalog.pg_description d\n"
5987 " ON d.classoid = c.tableoid AND "
5988 "d.objoid = c.oid AND d.objsubid = 0\n");
5990 if (!validateSQLNamePattern(&buf
, pattern
, false, false,
5991 "n.nspname", "c.relname", NULL
,
5992 "pg_catalog.pg_table_is_visible(c.oid)",
5995 termPQExpBuffer(&buf
);
5999 appendPQExpBufferStr(&buf
, "ORDER BY 1, 2;");
6001 res
= PSQLexec(buf
.data
);
6002 termPQExpBuffer(&buf
);
6006 myopt
.nullPrint
= NULL
;
6007 myopt
.title
= _("List of foreign tables");
6008 myopt
.translate_header
= true;
6010 printQuery(res
, &myopt
, pset
.queryFout
, false, pset
.logfile
);
6019 * Briefly describes installed extensions.
6022 listExtensions(const char *pattern
)
6024 PQExpBufferData buf
;
6026 printQueryOpt myopt
= pset
.popt
;
6028 initPQExpBuffer(&buf
);
6029 printfPQExpBuffer(&buf
,
6030 "SELECT e.extname AS \"%s\", "
6031 "e.extversion AS \"%s\", n.nspname AS \"%s\", c.description AS \"%s\"\n"
6032 "FROM pg_catalog.pg_extension e "
6033 "LEFT JOIN pg_catalog.pg_namespace n ON n.oid = e.extnamespace "
6034 "LEFT JOIN pg_catalog.pg_description c ON c.objoid = e.oid "
6035 "AND c.classoid = 'pg_catalog.pg_extension'::pg_catalog.regclass\n",
6036 gettext_noop("Name"),
6037 gettext_noop("Version"),
6038 gettext_noop("Schema"),
6039 gettext_noop("Description"));
6041 if (!validateSQLNamePattern(&buf
, pattern
,
6043 NULL
, "e.extname", NULL
,
6047 termPQExpBuffer(&buf
);
6051 appendPQExpBufferStr(&buf
, "ORDER BY 1;");
6053 res
= PSQLexec(buf
.data
);
6054 termPQExpBuffer(&buf
);
6058 myopt
.nullPrint
= NULL
;
6059 myopt
.title
= _("List of installed extensions");
6060 myopt
.translate_header
= true;
6062 printQuery(res
, &myopt
, pset
.queryFout
, false, pset
.logfile
);
6071 * List contents of installed extensions.
6074 listExtensionContents(const char *pattern
)
6076 PQExpBufferData buf
;
6080 initPQExpBuffer(&buf
);
6081 printfPQExpBuffer(&buf
,
6082 "SELECT e.extname, e.oid\n"
6083 "FROM pg_catalog.pg_extension e\n");
6085 if (!validateSQLNamePattern(&buf
, pattern
,
6087 NULL
, "e.extname", NULL
,
6091 termPQExpBuffer(&buf
);
6095 appendPQExpBufferStr(&buf
, "ORDER BY 1;");
6097 res
= PSQLexec(buf
.data
);
6098 termPQExpBuffer(&buf
);
6102 if (PQntuples(res
) == 0)
6107 pg_log_error("Did not find any extension named \"%s\".",
6110 pg_log_error("Did not find any extensions.");
6116 for (i
= 0; i
< PQntuples(res
); i
++)
6118 const char *extname
;
6121 extname
= PQgetvalue(res
, i
, 0);
6122 oid
= PQgetvalue(res
, i
, 1);
6124 if (!listOneExtensionContents(extname
, oid
))
6141 listOneExtensionContents(const char *extname
, const char *oid
)
6143 PQExpBufferData buf
;
6145 PQExpBufferData title
;
6146 printQueryOpt myopt
= pset
.popt
;
6148 initPQExpBuffer(&buf
);
6149 printfPQExpBuffer(&buf
,
6150 "SELECT pg_catalog.pg_describe_object(classid, objid, 0) AS \"%s\"\n"
6151 "FROM pg_catalog.pg_depend\n"
6152 "WHERE refclassid = 'pg_catalog.pg_extension'::pg_catalog.regclass AND refobjid = '%s' AND deptype = 'e'\n"
6154 gettext_noop("Object description"),
6157 res
= PSQLexec(buf
.data
);
6158 termPQExpBuffer(&buf
);
6162 myopt
.nullPrint
= NULL
;
6163 initPQExpBuffer(&title
);
6164 printfPQExpBuffer(&title
, _("Objects in extension \"%s\""), extname
);
6165 myopt
.title
= title
.data
;
6166 myopt
.translate_header
= true;
6168 printQuery(res
, &myopt
, pset
.queryFout
, false, pset
.logfile
);
6170 termPQExpBuffer(&title
);
6176 * validateSQLNamePattern
6178 * Wrapper around string_utils's processSQLNamePattern which also checks the
6179 * pattern's validity. In addition to that function's parameters, takes a
6180 * 'maxparts' parameter specifying the maximum number of dotted names the
6181 * pattern is allowed to have, and a 'added_clause' parameter that returns by
6182 * reference whether a clause was added to 'buf'. Returns whether the pattern
6183 * passed validation, after logging any errors.
6186 validateSQLNamePattern(PQExpBuffer buf
, const char *pattern
, bool have_where
,
6187 bool force_escape
, const char *schemavar
,
6188 const char *namevar
, const char *altnamevar
,
6189 const char *visibilityrule
, bool *added_clause
,
6192 PQExpBufferData dbbuf
;
6196 initPQExpBuffer(&dbbuf
);
6197 added
= processSQLNamePattern(pset
.db
, buf
, pattern
, have_where
, force_escape
,
6198 schemavar
, namevar
, altnamevar
,
6199 visibilityrule
, &dbbuf
, &dotcnt
);
6200 if (added_clause
!= NULL
)
6201 *added_clause
= added
;
6203 if (dotcnt
>= maxparts
)
6205 pg_log_error("improper qualified name (too many dotted names): %s",
6210 if (maxparts
> 1 && dotcnt
== maxparts
- 1)
6212 if (PQdb(pset
.db
) == NULL
)
6214 pg_log_error("You are currently not connected to a database.");
6217 if (strcmp(PQdb(pset
.db
), dbbuf
.data
) != 0)
6219 pg_log_error("cross-database references are not implemented: %s",
6224 termPQExpBuffer(&dbbuf
);
6228 termPQExpBuffer(&dbbuf
);
6234 * Lists publications.
6236 * Takes an optional regexp to select particular publications
6239 listPublications(const char *pattern
)
6241 PQExpBufferData buf
;
6243 printQueryOpt myopt
= pset
.popt
;
6244 static const bool translate_columns
[] = {false, false, false, false, false, false, false, false};
6246 if (pset
.sversion
< 100000)
6250 pg_log_error("The server (version %s) does not support publications.",
6251 formatPGVersionNumber(pset
.sversion
, false,
6252 sverbuf
, sizeof(sverbuf
)));
6256 initPQExpBuffer(&buf
);
6258 printfPQExpBuffer(&buf
,
6259 "SELECT pubname AS \"%s\",\n"
6260 " pg_catalog.pg_get_userbyid(pubowner) AS \"%s\",\n"
6261 " puballtables AS \"%s\",\n"
6262 " pubinsert AS \"%s\",\n"
6263 " pubupdate AS \"%s\",\n"
6264 " pubdelete AS \"%s\"",
6265 gettext_noop("Name"),
6266 gettext_noop("Owner"),
6267 gettext_noop("All tables"),
6268 gettext_noop("Inserts"),
6269 gettext_noop("Updates"),
6270 gettext_noop("Deletes"));
6271 if (pset
.sversion
>= 110000)
6272 appendPQExpBuffer(&buf
,
6273 ",\n pubtruncate AS \"%s\"",
6274 gettext_noop("Truncates"));
6275 if (pset
.sversion
>= 130000)
6276 appendPQExpBuffer(&buf
,
6277 ",\n pubviaroot AS \"%s\"",
6278 gettext_noop("Via root"));
6280 appendPQExpBufferStr(&buf
,
6281 "\nFROM pg_catalog.pg_publication\n");
6283 if (!validateSQLNamePattern(&buf
, pattern
, false, false,
6284 NULL
, "pubname", NULL
,
6288 termPQExpBuffer(&buf
);
6292 appendPQExpBufferStr(&buf
, "ORDER BY 1;");
6294 res
= PSQLexec(buf
.data
);
6295 termPQExpBuffer(&buf
);
6299 myopt
.nullPrint
= NULL
;
6300 myopt
.title
= _("List of publications");
6301 myopt
.translate_header
= true;
6302 myopt
.translate_columns
= translate_columns
;
6303 myopt
.n_translate_columns
= lengthof(translate_columns
);
6305 printQuery(res
, &myopt
, pset
.queryFout
, false, pset
.logfile
);
6313 * Add footer to publication description.
6316 addFooterToPublicationDesc(PQExpBuffer buf
, const char *footermsg
,
6317 bool as_schema
, printTableContent
*const cont
)
6323 res
= PSQLexec(buf
->data
);
6327 count
= PQntuples(res
);
6330 printTableAddFooter(cont
, footermsg
);
6332 for (i
= 0; i
< count
; i
++)
6335 printfPQExpBuffer(buf
, " \"%s\"", PQgetvalue(res
, i
, 0));
6338 printfPQExpBuffer(buf
, " \"%s.%s\"", PQgetvalue(res
, i
, 0),
6339 PQgetvalue(res
, i
, 1));
6341 if (!PQgetisnull(res
, i
, 3))
6342 appendPQExpBuffer(buf
, " (%s)", PQgetvalue(res
, i
, 3));
6344 if (!PQgetisnull(res
, i
, 2))
6345 appendPQExpBuffer(buf
, " WHERE %s", PQgetvalue(res
, i
, 2));
6348 printTableAddFooter(cont
, buf
->data
);
6357 * Describes publications including the contents.
6359 * Takes an optional regexp to select particular publications
6362 describePublications(const char *pattern
)
6364 PQExpBufferData buf
;
6367 bool has_pubtruncate
;
6368 bool has_pubviaroot
;
6370 PQExpBufferData title
;
6371 printTableContent cont
;
6373 if (pset
.sversion
< 100000)
6377 pg_log_error("The server (version %s) does not support publications.",
6378 formatPGVersionNumber(pset
.sversion
, false,
6379 sverbuf
, sizeof(sverbuf
)));
6383 has_pubtruncate
= (pset
.sversion
>= 110000);
6384 has_pubviaroot
= (pset
.sversion
>= 130000);
6386 initPQExpBuffer(&buf
);
6388 printfPQExpBuffer(&buf
,
6389 "SELECT oid, pubname,\n"
6390 " pg_catalog.pg_get_userbyid(pubowner) AS owner,\n"
6391 " puballtables, pubinsert, pubupdate, pubdelete");
6392 if (has_pubtruncate
)
6393 appendPQExpBufferStr(&buf
,
6396 appendPQExpBufferStr(&buf
,
6398 appendPQExpBufferStr(&buf
,
6399 "\nFROM pg_catalog.pg_publication\n");
6401 if (!validateSQLNamePattern(&buf
, pattern
, false, false,
6402 NULL
, "pubname", NULL
,
6406 termPQExpBuffer(&buf
);
6410 appendPQExpBufferStr(&buf
, "ORDER BY 2;");
6412 res
= PSQLexec(buf
.data
);
6415 termPQExpBuffer(&buf
);
6419 if (PQntuples(res
) == 0)
6424 pg_log_error("Did not find any publication named \"%s\".",
6427 pg_log_error("Did not find any publications.");
6430 termPQExpBuffer(&buf
);
6435 for (i
= 0; i
< PQntuples(res
); i
++)
6437 const char align
= 'l';
6440 char *pubid
= PQgetvalue(res
, i
, 0);
6441 char *pubname
= PQgetvalue(res
, i
, 1);
6442 bool puballtables
= strcmp(PQgetvalue(res
, i
, 3), "t") == 0;
6443 printTableOpt myopt
= pset
.popt
.topt
;
6445 if (has_pubtruncate
)
6450 initPQExpBuffer(&title
);
6451 printfPQExpBuffer(&title
, _("Publication %s"), pubname
);
6452 printTableInit(&cont
, &myopt
, title
.data
, ncols
, nrows
);
6454 printTableAddHeader(&cont
, gettext_noop("Owner"), true, align
);
6455 printTableAddHeader(&cont
, gettext_noop("All tables"), true, align
);
6456 printTableAddHeader(&cont
, gettext_noop("Inserts"), true, align
);
6457 printTableAddHeader(&cont
, gettext_noop("Updates"), true, align
);
6458 printTableAddHeader(&cont
, gettext_noop("Deletes"), true, align
);
6459 if (has_pubtruncate
)
6460 printTableAddHeader(&cont
, gettext_noop("Truncates"), true, align
);
6462 printTableAddHeader(&cont
, gettext_noop("Via root"), true, align
);
6464 printTableAddCell(&cont
, PQgetvalue(res
, i
, 2), false, false);
6465 printTableAddCell(&cont
, PQgetvalue(res
, i
, 3), false, false);
6466 printTableAddCell(&cont
, PQgetvalue(res
, i
, 4), false, false);
6467 printTableAddCell(&cont
, PQgetvalue(res
, i
, 5), false, false);
6468 printTableAddCell(&cont
, PQgetvalue(res
, i
, 6), false, false);
6469 if (has_pubtruncate
)
6470 printTableAddCell(&cont
, PQgetvalue(res
, i
, 7), false, false);
6472 printTableAddCell(&cont
, PQgetvalue(res
, i
, 8), false, false);
6476 /* Get the tables for the specified publication */
6477 printfPQExpBuffer(&buf
,
6478 "SELECT n.nspname, c.relname");
6479 if (pset
.sversion
>= 150000)
6481 appendPQExpBufferStr(&buf
,
6482 ", pg_get_expr(pr.prqual, c.oid)");
6483 appendPQExpBufferStr(&buf
,
6484 ", (CASE WHEN pr.prattrs IS NOT NULL THEN\n"
6485 " pg_catalog.array_to_string("
6486 " ARRAY(SELECT attname\n"
6488 " pg_catalog.generate_series(0, pg_catalog.array_upper(pr.prattrs::pg_catalog.int2[], 1)) s,\n"
6489 " pg_catalog.pg_attribute\n"
6490 " WHERE attrelid = c.oid AND attnum = prattrs[s]), ', ')\n"
6494 appendPQExpBufferStr(&buf
,
6496 appendPQExpBuffer(&buf
,
6497 "\nFROM pg_catalog.pg_class c,\n"
6498 " pg_catalog.pg_namespace n,\n"
6499 " pg_catalog.pg_publication_rel pr\n"
6500 "WHERE c.relnamespace = n.oid\n"
6501 " AND c.oid = pr.prrelid\n"
6502 " AND pr.prpubid = '%s'\n"
6503 "ORDER BY 1,2", pubid
);
6504 if (!addFooterToPublicationDesc(&buf
, _("Tables:"), false, &cont
))
6507 if (pset
.sversion
>= 150000)
6509 /* Get the schemas for the specified publication */
6510 printfPQExpBuffer(&buf
,
6511 "SELECT n.nspname\n"
6512 "FROM pg_catalog.pg_namespace n\n"
6513 " JOIN pg_catalog.pg_publication_namespace pn ON n.oid = pn.pnnspid\n"
6514 "WHERE pn.pnpubid = '%s'\n"
6515 "ORDER BY 1", pubid
);
6516 if (!addFooterToPublicationDesc(&buf
, _("Tables from schemas:"),
6522 printTable(&cont
, pset
.queryFout
, false, pset
.logfile
);
6523 printTableCleanup(&cont
);
6525 termPQExpBuffer(&title
);
6528 termPQExpBuffer(&buf
);
6534 printTableCleanup(&cont
);
6536 termPQExpBuffer(&buf
);
6537 termPQExpBuffer(&title
);
6543 * Describes subscriptions.
6545 * Takes an optional regexp to select particular subscriptions
6548 describeSubscriptions(const char *pattern
, bool verbose
)
6550 PQExpBufferData buf
;
6552 printQueryOpt myopt
= pset
.popt
;
6553 static const bool translate_columns
[] = {false, false, false, false,
6554 false, false, false, false, false, false, false, false, false, false};
6556 if (pset
.sversion
< 100000)
6560 pg_log_error("The server (version %s) does not support subscriptions.",
6561 formatPGVersionNumber(pset
.sversion
, false,
6562 sverbuf
, sizeof(sverbuf
)));
6566 initPQExpBuffer(&buf
);
6568 printfPQExpBuffer(&buf
,
6569 "SELECT subname AS \"%s\"\n"
6570 ", pg_catalog.pg_get_userbyid(subowner) AS \"%s\"\n"
6571 ", subenabled AS \"%s\"\n"
6572 ", subpublications AS \"%s\"\n",
6573 gettext_noop("Name"),
6574 gettext_noop("Owner"),
6575 gettext_noop("Enabled"),
6576 gettext_noop("Publication"));
6580 /* Binary mode and streaming are only supported in v14 and higher */
6581 if (pset
.sversion
>= 140000)
6583 appendPQExpBuffer(&buf
,
6584 ", subbinary AS \"%s\"\n",
6585 gettext_noop("Binary"));
6587 if (pset
.sversion
>= 160000)
6588 appendPQExpBuffer(&buf
,
6589 ", (CASE substream\n"
6590 " WHEN 'f' THEN 'off'\n"
6591 " WHEN 't' THEN 'on'\n"
6592 " WHEN 'p' THEN 'parallel'\n"
6593 " END) AS \"%s\"\n",
6594 gettext_noop("Streaming"));
6596 appendPQExpBuffer(&buf
,
6597 ", substream AS \"%s\"\n",
6598 gettext_noop("Streaming"));
6601 /* Two_phase and disable_on_error are only supported in v15 and higher */
6602 if (pset
.sversion
>= 150000)
6603 appendPQExpBuffer(&buf
,
6604 ", subtwophasestate AS \"%s\"\n"
6605 ", subdisableonerr AS \"%s\"\n",
6606 gettext_noop("Two-phase commit"),
6607 gettext_noop("Disable on error"));
6609 if (pset
.sversion
>= 160000)
6610 appendPQExpBuffer(&buf
,
6611 ", suborigin AS \"%s\"\n"
6612 ", subpasswordrequired AS \"%s\"\n"
6613 ", subrunasowner AS \"%s\"\n",
6614 gettext_noop("Origin"),
6615 gettext_noop("Password required"),
6616 gettext_noop("Run as owner?"));
6618 appendPQExpBuffer(&buf
,
6619 ", subsynccommit AS \"%s\"\n"
6620 ", subconninfo AS \"%s\"\n",
6621 gettext_noop("Synchronous commit"),
6622 gettext_noop("Conninfo"));
6624 /* Skip LSN is only supported in v15 and higher */
6625 if (pset
.sversion
>= 150000)
6626 appendPQExpBuffer(&buf
,
6627 ", subskiplsn AS \"%s\"\n",
6628 gettext_noop("Skip LSN"));
6631 /* Only display subscriptions in current database. */
6632 appendPQExpBufferStr(&buf
,
6633 "FROM pg_catalog.pg_subscription\n"
6634 "WHERE subdbid = (SELECT oid\n"
6635 " FROM pg_catalog.pg_database\n"
6636 " WHERE datname = pg_catalog.current_database())");
6638 if (!validateSQLNamePattern(&buf
, pattern
, true, false,
6639 NULL
, "subname", NULL
,
6643 termPQExpBuffer(&buf
);
6647 appendPQExpBufferStr(&buf
, "ORDER BY 1;");
6649 res
= PSQLexec(buf
.data
);
6650 termPQExpBuffer(&buf
);
6654 myopt
.nullPrint
= NULL
;
6655 myopt
.title
= _("List of subscriptions");
6656 myopt
.translate_header
= true;
6657 myopt
.translate_columns
= translate_columns
;
6658 myopt
.n_translate_columns
= lengthof(translate_columns
);
6660 printQuery(res
, &myopt
, pset
.queryFout
, false, pset
.logfile
);
6669 * Helper function for consistently formatting ACL (privilege) columns.
6670 * The proper targetlist entry is appended to buf. Note lack of any
6671 * whitespace or comma decoration.
6674 printACLColumn(PQExpBuffer buf
, const char *colname
)
6676 appendPQExpBuffer(buf
,
6677 "pg_catalog.array_to_string(%s, E'\\n') AS \"%s\"",
6678 colname
, gettext_noop("Access privileges"));
6683 * Lists operator classes
6685 * Takes optional regexps to filter by index access method and input data type.
6688 listOperatorClasses(const char *access_method_pattern
,
6689 const char *type_pattern
, bool verbose
)
6691 PQExpBufferData buf
;
6693 printQueryOpt myopt
= pset
.popt
;
6694 bool have_where
= false;
6695 static const bool translate_columns
[] = {false, false, false, false, false, false, false};
6697 initPQExpBuffer(&buf
);
6699 printfPQExpBuffer(&buf
,
6701 " am.amname AS \"%s\",\n"
6702 " pg_catalog.format_type(c.opcintype, NULL) AS \"%s\",\n"
6704 " WHEN c.opckeytype <> 0 AND c.opckeytype <> c.opcintype\n"
6705 " THEN pg_catalog.format_type(c.opckeytype, NULL)\n"
6709 " WHEN pg_catalog.pg_opclass_is_visible(c.oid)\n"
6710 " THEN pg_catalog.format('%%I', c.opcname)\n"
6711 " ELSE pg_catalog.format('%%I.%%I', n.nspname, c.opcname)\n"
6713 " (CASE WHEN c.opcdefault\n"
6718 gettext_noop("Input type"),
6719 gettext_noop("Storage type"),
6720 gettext_noop("Operator class"),
6721 gettext_noop("yes"),
6723 gettext_noop("Default?"));
6725 appendPQExpBuffer(&buf
,
6727 " WHEN pg_catalog.pg_opfamily_is_visible(of.oid)\n"
6728 " THEN pg_catalog.format('%%I', of.opfname)\n"
6729 " ELSE pg_catalog.format('%%I.%%I', ofn.nspname, of.opfname)\n"
6731 " pg_catalog.pg_get_userbyid(c.opcowner) AS \"%s\"\n",
6732 gettext_noop("Operator family"),
6733 gettext_noop("Owner"));
6734 appendPQExpBufferStr(&buf
,
6735 "\nFROM pg_catalog.pg_opclass c\n"
6736 " LEFT JOIN pg_catalog.pg_am am on am.oid = c.opcmethod\n"
6737 " LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.opcnamespace\n"
6738 " LEFT JOIN pg_catalog.pg_type t ON t.oid = c.opcintype\n"
6739 " LEFT JOIN pg_catalog.pg_namespace tn ON tn.oid = t.typnamespace\n");
6741 appendPQExpBufferStr(&buf
,
6742 " LEFT JOIN pg_catalog.pg_opfamily of ON of.oid = c.opcfamily\n"
6743 " LEFT JOIN pg_catalog.pg_namespace ofn ON ofn.oid = of.opfnamespace\n");
6745 if (access_method_pattern
)
6746 if (!validateSQLNamePattern(&buf
, access_method_pattern
,
6747 false, false, NULL
, "am.amname", NULL
, NULL
,
6752 /* Match type name pattern against either internal or external name */
6753 if (!validateSQLNamePattern(&buf
, type_pattern
, have_where
, false,
6754 "tn.nspname", "t.typname",
6755 "pg_catalog.format_type(t.oid, NULL)",
6756 "pg_catalog.pg_type_is_visible(t.oid)",
6761 appendPQExpBufferStr(&buf
, "ORDER BY 1, 2, 4;");
6762 res
= PSQLexec(buf
.data
);
6763 termPQExpBuffer(&buf
);
6767 myopt
.nullPrint
= NULL
;
6768 myopt
.title
= _("List of operator classes");
6769 myopt
.translate_header
= true;
6770 myopt
.translate_columns
= translate_columns
;
6771 myopt
.n_translate_columns
= lengthof(translate_columns
);
6773 printQuery(res
, &myopt
, pset
.queryFout
, false, pset
.logfile
);
6779 termPQExpBuffer(&buf
);
6785 * Lists operator families
6787 * Takes optional regexps to filter by index access method and input data type.
6790 listOperatorFamilies(const char *access_method_pattern
,
6791 const char *type_pattern
, bool verbose
)
6793 PQExpBufferData buf
;
6795 printQueryOpt myopt
= pset
.popt
;
6796 bool have_where
= false;
6797 static const bool translate_columns
[] = {false, false, false, false};
6799 initPQExpBuffer(&buf
);
6801 printfPQExpBuffer(&buf
,
6803 " am.amname AS \"%s\",\n"
6805 " WHEN pg_catalog.pg_opfamily_is_visible(f.oid)\n"
6806 " THEN pg_catalog.format('%%I', f.opfname)\n"
6807 " ELSE pg_catalog.format('%%I.%%I', n.nspname, f.opfname)\n"
6810 " pg_catalog.string_agg(pg_catalog.format_type(oc.opcintype, NULL), ', ')\n"
6811 " FROM pg_catalog.pg_opclass oc\n"
6812 " WHERE oc.opcfamily = f.oid) \"%s\"",
6814 gettext_noop("Operator family"),
6815 gettext_noop("Applicable types"));
6817 appendPQExpBuffer(&buf
,
6818 ",\n pg_catalog.pg_get_userbyid(f.opfowner) AS \"%s\"\n",
6819 gettext_noop("Owner"));
6820 appendPQExpBufferStr(&buf
,
6821 "\nFROM pg_catalog.pg_opfamily f\n"
6822 " LEFT JOIN pg_catalog.pg_am am on am.oid = f.opfmethod\n"
6823 " LEFT JOIN pg_catalog.pg_namespace n ON n.oid = f.opfnamespace\n");
6825 if (access_method_pattern
)
6826 if (!validateSQLNamePattern(&buf
, access_method_pattern
,
6827 false, false, NULL
, "am.amname", NULL
, NULL
,
6832 appendPQExpBuffer(&buf
,
6835 " FROM pg_catalog.pg_type t\n"
6836 " JOIN pg_catalog.pg_opclass oc ON oc.opcintype = t.oid\n"
6837 " LEFT JOIN pg_catalog.pg_namespace tn ON tn.oid = t.typnamespace\n"
6838 " WHERE oc.opcfamily = f.oid\n",
6839 have_where
? "AND" : "WHERE");
6840 /* Match type name pattern against either internal or external name */
6841 if (!validateSQLNamePattern(&buf
, type_pattern
, true, false,
6842 "tn.nspname", "t.typname",
6843 "pg_catalog.format_type(t.oid, NULL)",
6844 "pg_catalog.pg_type_is_visible(t.oid)",
6847 appendPQExpBufferStr(&buf
, " )\n");
6850 appendPQExpBufferStr(&buf
, "ORDER BY 1, 2;");
6851 res
= PSQLexec(buf
.data
);
6852 termPQExpBuffer(&buf
);
6856 myopt
.nullPrint
= NULL
;
6857 myopt
.title
= _("List of operator families");
6858 myopt
.translate_header
= true;
6859 myopt
.translate_columns
= translate_columns
;
6860 myopt
.n_translate_columns
= lengthof(translate_columns
);
6862 printQuery(res
, &myopt
, pset
.queryFout
, false, pset
.logfile
);
6868 termPQExpBuffer(&buf
);
6874 * Lists operators of operator families
6876 * Takes optional regexps to filter by index access method and operator
6880 listOpFamilyOperators(const char *access_method_pattern
,
6881 const char *family_pattern
, bool verbose
)
6883 PQExpBufferData buf
;
6885 printQueryOpt myopt
= pset
.popt
;
6886 bool have_where
= false;
6888 static const bool translate_columns
[] = {false, false, false, false, false, false};
6890 initPQExpBuffer(&buf
);
6892 printfPQExpBuffer(&buf
,
6894 " am.amname AS \"%s\",\n"
6896 " WHEN pg_catalog.pg_opfamily_is_visible(of.oid)\n"
6897 " THEN pg_catalog.format('%%I', of.opfname)\n"
6898 " ELSE pg_catalog.format('%%I.%%I', nsf.nspname, of.opfname)\n"
6900 " o.amopopr::pg_catalog.regoperator AS \"%s\"\n,"
6901 " o.amopstrategy AS \"%s\",\n"
6902 " CASE o.amoppurpose\n"
6903 " WHEN 'o' THEN '%s'\n"
6904 " WHEN 's' THEN '%s'\n"
6907 gettext_noop("Operator family"),
6908 gettext_noop("Operator"),
6909 gettext_noop("Strategy"),
6910 gettext_noop("ordering"),
6911 gettext_noop("search"),
6912 gettext_noop("Purpose"));
6915 appendPQExpBuffer(&buf
,
6916 ", ofs.opfname AS \"%s\"\n",
6917 gettext_noop("Sort opfamily"));
6918 appendPQExpBufferStr(&buf
,
6919 "FROM pg_catalog.pg_amop o\n"
6920 " LEFT JOIN pg_catalog.pg_opfamily of ON of.oid = o.amopfamily\n"
6921 " LEFT JOIN pg_catalog.pg_am am ON am.oid = of.opfmethod AND am.oid = o.amopmethod\n"
6922 " LEFT JOIN pg_catalog.pg_namespace nsf ON of.opfnamespace = nsf.oid\n");
6924 appendPQExpBufferStr(&buf
,
6925 " LEFT JOIN pg_catalog.pg_opfamily ofs ON ofs.oid = o.amopsortfamily\n");
6927 if (access_method_pattern
)
6929 if (!validateSQLNamePattern(&buf
, access_method_pattern
,
6930 false, false, NULL
, "am.amname",
6938 if (!validateSQLNamePattern(&buf
, family_pattern
, have_where
, false,
6939 "nsf.nspname", "of.opfname", NULL
, NULL
,
6944 appendPQExpBufferStr(&buf
, "ORDER BY 1, 2,\n"
6945 " o.amoplefttype = o.amoprighttype DESC,\n"
6946 " pg_catalog.format_type(o.amoplefttype, NULL),\n"
6947 " pg_catalog.format_type(o.amoprighttype, NULL),\n"
6948 " o.amopstrategy;");
6950 res
= PSQLexec(buf
.data
);
6951 termPQExpBuffer(&buf
);
6955 myopt
.nullPrint
= NULL
;
6956 myopt
.title
= _("List of operators of operator families");
6957 myopt
.translate_header
= true;
6958 myopt
.translate_columns
= translate_columns
;
6959 myopt
.n_translate_columns
= lengthof(translate_columns
);
6961 printQuery(res
, &myopt
, pset
.queryFout
, false, pset
.logfile
);
6967 termPQExpBuffer(&buf
);
6973 * Lists support functions of operator families
6975 * Takes optional regexps to filter by index access method and operator
6979 listOpFamilyFunctions(const char *access_method_pattern
,
6980 const char *family_pattern
, bool verbose
)
6982 PQExpBufferData buf
;
6984 printQueryOpt myopt
= pset
.popt
;
6985 bool have_where
= false;
6986 static const bool translate_columns
[] = {false, false, false, false, false, false};
6988 initPQExpBuffer(&buf
);
6990 printfPQExpBuffer(&buf
,
6992 " am.amname AS \"%s\",\n"
6994 " WHEN pg_catalog.pg_opfamily_is_visible(of.oid)\n"
6995 " THEN pg_catalog.format('%%I', of.opfname)\n"
6996 " ELSE pg_catalog.format('%%I.%%I', ns.nspname, of.opfname)\n"
6998 " pg_catalog.format_type(ap.amproclefttype, NULL) AS \"%s\",\n"
6999 " pg_catalog.format_type(ap.amprocrighttype, NULL) AS \"%s\",\n"
7000 " ap.amprocnum AS \"%s\"\n",
7002 gettext_noop("Operator family"),
7003 gettext_noop("Registered left type"),
7004 gettext_noop("Registered right type"),
7005 gettext_noop("Number"));
7008 appendPQExpBuffer(&buf
,
7009 ", p.proname AS \"%s\"\n",
7010 gettext_noop("Function"));
7012 appendPQExpBuffer(&buf
,
7013 ", ap.amproc::pg_catalog.regprocedure AS \"%s\"\n",
7014 gettext_noop("Function"));
7016 appendPQExpBufferStr(&buf
,
7017 "FROM pg_catalog.pg_amproc ap\n"
7018 " LEFT JOIN pg_catalog.pg_opfamily of ON of.oid = ap.amprocfamily\n"
7019 " LEFT JOIN pg_catalog.pg_am am ON am.oid = of.opfmethod\n"
7020 " LEFT JOIN pg_catalog.pg_namespace ns ON of.opfnamespace = ns.oid\n"
7021 " LEFT JOIN pg_catalog.pg_proc p ON ap.amproc = p.oid\n");
7023 if (access_method_pattern
)
7025 if (!validateSQLNamePattern(&buf
, access_method_pattern
,
7026 false, false, NULL
, "am.amname",
7033 if (!validateSQLNamePattern(&buf
, family_pattern
, have_where
, false,
7034 "ns.nspname", "of.opfname", NULL
, NULL
,
7039 appendPQExpBufferStr(&buf
, "ORDER BY 1, 2,\n"
7040 " ap.amproclefttype = ap.amprocrighttype DESC,\n"
7043 res
= PSQLexec(buf
.data
);
7044 termPQExpBuffer(&buf
);
7048 myopt
.nullPrint
= NULL
;
7049 myopt
.title
= _("List of support functions of operator families");
7050 myopt
.translate_header
= true;
7051 myopt
.translate_columns
= translate_columns
;
7052 myopt
.n_translate_columns
= lengthof(translate_columns
);
7054 printQuery(res
, &myopt
, pset
.queryFout
, false, pset
.logfile
);
7060 termPQExpBuffer(&buf
);
7066 * Lists large objects
7069 listLargeObjects(bool verbose
)
7071 PQExpBufferData buf
;
7073 printQueryOpt myopt
= pset
.popt
;
7075 initPQExpBuffer(&buf
);
7077 printfPQExpBuffer(&buf
,
7078 "SELECT oid as \"%s\",\n"
7079 " pg_catalog.pg_get_userbyid(lomowner) as \"%s\",\n ",
7081 gettext_noop("Owner"));
7085 printACLColumn(&buf
, "lomacl");
7086 appendPQExpBufferStr(&buf
, ",\n ");
7089 appendPQExpBuffer(&buf
,
7090 "pg_catalog.obj_description(oid, 'pg_largeobject') as \"%s\"\n"
7091 "FROM pg_catalog.pg_largeobject_metadata\n"
7093 gettext_noop("Description"));
7095 res
= PSQLexec(buf
.data
);
7096 termPQExpBuffer(&buf
);
7100 myopt
.nullPrint
= NULL
;
7101 myopt
.title
= _("Large objects");
7102 myopt
.translate_header
= true;
7104 printQuery(res
, &myopt
, pset
.queryFout
, false, pset
.logfile
);