PostgreSQL Source Code  git master
describe.c
Go to the documentation of this file.
1 /*
2  * psql - the PostgreSQL interactive terminal
3  *
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.)
9  *
10  * Copyright (c) 2000-2024, PostgreSQL Global Development Group
11  *
12  * src/bin/psql/describe.c
13  */
14 #include "postgres_fe.h"
15 
16 #include <ctype.h>
17 
18 #include "catalog/pg_am_d.h"
19 #include "catalog/pg_amop_d.h"
20 #include "catalog/pg_attribute_d.h"
21 #include "catalog/pg_cast_d.h"
22 #include "catalog/pg_class_d.h"
23 #include "catalog/pg_collation_d.h"
24 #include "catalog/pg_constraint_d.h"
25 #include "catalog/pg_default_acl_d.h"
26 #include "catalog/pg_proc_d.h"
27 #include "catalog/pg_statistic_ext_d.h"
28 #include "catalog/pg_subscription_d.h"
29 #include "catalog/pg_type_d.h"
30 #include "common.h"
31 #include "common/logging.h"
32 #include "describe.h"
33 #include "fe_utils/mbprint.h"
34 #include "fe_utils/print.h"
35 #include "fe_utils/string_utils.h"
36 #include "settings.h"
37 
38 static const char *map_typename_pattern(const char *pattern);
39 static bool describeOneTableDetails(const char *schemaname,
40  const char *relationname,
41  const char *oid,
42  bool verbose);
43 static void add_tablespace_footer(printTableContent *const cont, char relkind,
44  Oid tablespace, const bool newline);
45 static void add_role_attribute(PQExpBuffer buf, const char *const str);
46 static bool listTSParsersVerbose(const char *pattern);
47 static bool describeOneTSParser(const char *oid, const char *nspname,
48  const char *prsname);
49 static bool listTSConfigsVerbose(const char *pattern);
50 static bool describeOneTSConfig(const char *oid, const char *nspname,
51  const char *cfgname,
52  const char *pnspname, const char *prsname);
53 static void printACLColumn(PQExpBuffer buf, const char *colname);
54 static bool listOneExtensionContents(const char *extname, const char *oid);
55 static bool validateSQLNamePattern(PQExpBuffer buf, const char *pattern,
56  bool have_where, bool force_escape,
57  const char *schemavar, const char *namevar,
58  const char *altnamevar,
59  const char *visibilityrule,
60  bool *added_clause, int maxparts);
61 
62 
63 /*----------------
64  * Handlers for various slash commands displaying some sort of list
65  * of things in the database.
66  *
67  * Note: try to format the queries to look nice in -E output.
68  *----------------
69  */
70 
71 
72 /*
73  * \da
74  * Takes an optional regexp to select particular aggregates
75  */
76 bool
77 describeAggregates(const char *pattern, bool verbose, bool showSystem)
78 {
79  PQExpBufferData buf;
80  PGresult *res;
81  printQueryOpt myopt = pset.popt;
82 
83  initPQExpBuffer(&buf);
84 
85  printfPQExpBuffer(&buf,
86  "SELECT n.nspname as \"%s\",\n"
87  " p.proname AS \"%s\",\n"
88  " pg_catalog.format_type(p.prorettype, NULL) AS \"%s\",\n"
89  " CASE WHEN p.pronargs = 0\n"
90  " THEN CAST('*' AS pg_catalog.text)\n"
91  " ELSE pg_catalog.pg_get_function_arguments(p.oid)\n"
92  " END AS \"%s\",\n",
93  gettext_noop("Schema"),
94  gettext_noop("Name"),
95  gettext_noop("Result data type"),
96  gettext_noop("Argument data types"));
97 
98  if (pset.sversion >= 110000)
99  appendPQExpBuffer(&buf,
100  " pg_catalog.obj_description(p.oid, 'pg_proc') as \"%s\"\n"
101  "FROM pg_catalog.pg_proc p\n"
102  " LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace\n"
103  "WHERE p.prokind = " CppAsString2(PROKIND_AGGREGATE) "\n",
104  gettext_noop("Description"));
105  else
106  appendPQExpBuffer(&buf,
107  " pg_catalog.obj_description(p.oid, 'pg_proc') as \"%s\"\n"
108  "FROM pg_catalog.pg_proc p\n"
109  " LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace\n"
110  "WHERE p.proisagg\n",
111  gettext_noop("Description"));
112 
113  if (!showSystem && !pattern)
114  appendPQExpBufferStr(&buf, " AND n.nspname <> 'pg_catalog'\n"
115  " AND n.nspname <> 'information_schema'\n");
116 
117  if (!validateSQLNamePattern(&buf, pattern, true, false,
118  "n.nspname", "p.proname", NULL,
119  "pg_catalog.pg_function_is_visible(p.oid)",
120  NULL, 3))
121  {
122  termPQExpBuffer(&buf);
123  return false;
124  }
125 
126  appendPQExpBufferStr(&buf, "ORDER BY 1, 2, 4;");
127 
128  res = PSQLexec(buf.data);
129  termPQExpBuffer(&buf);
130  if (!res)
131  return false;
132 
133  myopt.title = _("List of aggregate functions");
134  myopt.translate_header = true;
135 
136  printQuery(res, &myopt, pset.queryFout, false, pset.logfile);
137 
138  PQclear(res);
139  return true;
140 }
141 
142 /*
143  * \dA
144  * Takes an optional regexp to select particular access methods
145  */
146 bool
147 describeAccessMethods(const char *pattern, bool verbose)
148 {
149  PQExpBufferData buf;
150  PGresult *res;
151  printQueryOpt myopt = pset.popt;
152  static const bool translate_columns[] = {false, true, false, false};
153 
154  if (pset.sversion < 90600)
155  {
156  char sverbuf[32];
157 
158  pg_log_error("The server (version %s) does not support access methods.",
159  formatPGVersionNumber(pset.sversion, false,
160  sverbuf, sizeof(sverbuf)));
161  return true;
162  }
163 
164  initPQExpBuffer(&buf);
165 
166  printfPQExpBuffer(&buf,
167  "SELECT amname AS \"%s\",\n"
168  " CASE amtype"
169  " WHEN " CppAsString2(AMTYPE_INDEX) " THEN '%s'"
170  " WHEN " CppAsString2(AMTYPE_TABLE) " THEN '%s'"
171  " END AS \"%s\"",
172  gettext_noop("Name"),
173  gettext_noop("Index"),
174  gettext_noop("Table"),
175  gettext_noop("Type"));
176 
177  if (verbose)
178  {
179  appendPQExpBuffer(&buf,
180  ",\n amhandler AS \"%s\",\n"
181  " pg_catalog.obj_description(oid, 'pg_am') AS \"%s\"",
182  gettext_noop("Handler"),
183  gettext_noop("Description"));
184  }
185 
186  appendPQExpBufferStr(&buf,
187  "\nFROM pg_catalog.pg_am\n");
188 
189  if (!validateSQLNamePattern(&buf, pattern, false, false,
190  NULL, "amname", NULL,
191  NULL,
192  NULL, 1))
193  {
194  termPQExpBuffer(&buf);
195  return false;
196  }
197 
198  appendPQExpBufferStr(&buf, "ORDER BY 1;");
199 
200  res = PSQLexec(buf.data);
201  termPQExpBuffer(&buf);
202  if (!res)
203  return false;
204 
205  myopt.title = _("List of access methods");
206  myopt.translate_header = true;
207  myopt.translate_columns = translate_columns;
208  myopt.n_translate_columns = lengthof(translate_columns);
209 
210  printQuery(res, &myopt, pset.queryFout, false, pset.logfile);
211 
212  PQclear(res);
213  return true;
214 }
215 
216 /*
217  * \db
218  * Takes an optional regexp to select particular tablespaces
219  */
220 bool
221 describeTablespaces(const char *pattern, bool verbose)
222 {
223  PQExpBufferData buf;
224  PGresult *res;
225  printQueryOpt myopt = pset.popt;
226 
227  initPQExpBuffer(&buf);
228 
229  printfPQExpBuffer(&buf,
230  "SELECT spcname AS \"%s\",\n"
231  " pg_catalog.pg_get_userbyid(spcowner) AS \"%s\",\n"
232  " pg_catalog.pg_tablespace_location(oid) AS \"%s\"",
233  gettext_noop("Name"),
234  gettext_noop("Owner"),
235  gettext_noop("Location"));
236 
237  if (verbose)
238  {
239  appendPQExpBufferStr(&buf, ",\n ");
240  printACLColumn(&buf, "spcacl");
241  appendPQExpBuffer(&buf,
242  ",\n spcoptions AS \"%s\""
243  ",\n pg_catalog.pg_size_pretty(pg_catalog.pg_tablespace_size(oid)) AS \"%s\""
244  ",\n pg_catalog.shobj_description(oid, 'pg_tablespace') AS \"%s\"",
245  gettext_noop("Options"),
246  gettext_noop("Size"),
247  gettext_noop("Description"));
248  }
249 
250  appendPQExpBufferStr(&buf,
251  "\nFROM pg_catalog.pg_tablespace\n");
252 
253  if (!validateSQLNamePattern(&buf, pattern, false, false,
254  NULL, "spcname", NULL,
255  NULL,
256  NULL, 1))
257  {
258  termPQExpBuffer(&buf);
259  return false;
260  }
261 
262  appendPQExpBufferStr(&buf, "ORDER BY 1;");
263 
264  res = PSQLexec(buf.data);
265  termPQExpBuffer(&buf);
266  if (!res)
267  return false;
268 
269  myopt.title = _("List of tablespaces");
270  myopt.translate_header = true;
271 
272  printQuery(res, &myopt, pset.queryFout, false, pset.logfile);
273 
274  PQclear(res);
275  return true;
276 }
277 
278 
279 /*
280  * \df
281  * Takes an optional regexp to select particular functions.
282  *
283  * As with \d, you can specify the kinds of functions you want:
284  *
285  * a for aggregates
286  * n for normal
287  * p for procedure
288  * t for trigger
289  * w for window
290  *
291  * and you can mix and match these in any order.
292  */
293 bool
294 describeFunctions(const char *functypes, const char *func_pattern,
295  char **arg_patterns, int num_arg_patterns,
296  bool verbose, bool showSystem)
297 {
298  bool showAggregate = strchr(functypes, 'a') != NULL;
299  bool showNormal = strchr(functypes, 'n') != NULL;
300  bool showProcedure = strchr(functypes, 'p') != NULL;
301  bool showTrigger = strchr(functypes, 't') != NULL;
302  bool showWindow = strchr(functypes, 'w') != NULL;
303  bool have_where;
304  PQExpBufferData buf;
305  PGresult *res;
306  printQueryOpt myopt = pset.popt;
307  static const bool translate_columns[] = {false, false, false, false, true, true, true, false, true, false, false, false, false};
308 
309  /* No "Parallel" column before 9.6 */
310  static const bool translate_columns_pre_96[] = {false, false, false, false, true, true, false, true, false, false, false, false};
311 
312  if (strlen(functypes) != strspn(functypes, "anptwS+"))
313  {
314  pg_log_error("\\df only takes [anptwS+] as options");
315  return true;
316  }
317 
318  if (showProcedure && pset.sversion < 110000)
319  {
320  char sverbuf[32];
321 
322  pg_log_error("\\df does not take a \"%c\" option with server version %s",
323  'p',
324  formatPGVersionNumber(pset.sversion, false,
325  sverbuf, sizeof(sverbuf)));
326  return true;
327  }
328 
329  if (!showAggregate && !showNormal && !showProcedure && !showTrigger && !showWindow)
330  {
331  showAggregate = showNormal = showTrigger = showWindow = true;
332  if (pset.sversion >= 110000)
333  showProcedure = true;
334  }
335 
336  initPQExpBuffer(&buf);
337 
338  printfPQExpBuffer(&buf,
339  "SELECT n.nspname as \"%s\",\n"
340  " p.proname as \"%s\",\n",
341  gettext_noop("Schema"),
342  gettext_noop("Name"));
343 
344  if (pset.sversion >= 110000)
345  appendPQExpBuffer(&buf,
346  " pg_catalog.pg_get_function_result(p.oid) as \"%s\",\n"
347  " pg_catalog.pg_get_function_arguments(p.oid) as \"%s\",\n"
348  " CASE p.prokind\n"
349  " WHEN " CppAsString2(PROKIND_AGGREGATE) " THEN '%s'\n"
350  " WHEN " CppAsString2(PROKIND_WINDOW) " THEN '%s'\n"
351  " WHEN " CppAsString2(PROKIND_PROCEDURE) " THEN '%s'\n"
352  " ELSE '%s'\n"
353  " END as \"%s\"",
354  gettext_noop("Result data type"),
355  gettext_noop("Argument data types"),
356  /* translator: "agg" is short for "aggregate" */
357  gettext_noop("agg"),
358  gettext_noop("window"),
359  gettext_noop("proc"),
360  gettext_noop("func"),
361  gettext_noop("Type"));
362  else
363  appendPQExpBuffer(&buf,
364  " pg_catalog.pg_get_function_result(p.oid) as \"%s\",\n"
365  " pg_catalog.pg_get_function_arguments(p.oid) as \"%s\",\n"
366  " CASE\n"
367  " WHEN p.proisagg THEN '%s'\n"
368  " WHEN p.proiswindow THEN '%s'\n"
369  " WHEN p.prorettype = 'pg_catalog.trigger'::pg_catalog.regtype THEN '%s'\n"
370  " ELSE '%s'\n"
371  " END as \"%s\"",
372  gettext_noop("Result data type"),
373  gettext_noop("Argument data types"),
374  /* translator: "agg" is short for "aggregate" */
375  gettext_noop("agg"),
376  gettext_noop("window"),
377  gettext_noop("trigger"),
378  gettext_noop("func"),
379  gettext_noop("Type"));
380 
381  if (verbose)
382  {
383  appendPQExpBuffer(&buf,
384  ",\n CASE\n"
385  " WHEN p.provolatile = "
386  CppAsString2(PROVOLATILE_IMMUTABLE) " THEN '%s'\n"
387  " WHEN p.provolatile = "
388  CppAsString2(PROVOLATILE_STABLE) " THEN '%s'\n"
389  " WHEN p.provolatile = "
390  CppAsString2(PROVOLATILE_VOLATILE) " THEN '%s'\n"
391  " END as \"%s\"",
392  gettext_noop("immutable"),
393  gettext_noop("stable"),
394  gettext_noop("volatile"),
395  gettext_noop("Volatility"));
396  if (pset.sversion >= 90600)
397  appendPQExpBuffer(&buf,
398  ",\n CASE\n"
399  " WHEN p.proparallel = "
400  CppAsString2(PROPARALLEL_RESTRICTED) " THEN '%s'\n"
401  " WHEN p.proparallel = "
402  CppAsString2(PROPARALLEL_SAFE) " THEN '%s'\n"
403  " WHEN p.proparallel = "
404  CppAsString2(PROPARALLEL_UNSAFE) " THEN '%s'\n"
405  " END as \"%s\"",
406  gettext_noop("restricted"),
407  gettext_noop("safe"),
408  gettext_noop("unsafe"),
409  gettext_noop("Parallel"));
410  appendPQExpBuffer(&buf,
411  ",\n pg_catalog.pg_get_userbyid(p.proowner) as \"%s\""
412  ",\n CASE WHEN prosecdef THEN '%s' ELSE '%s' END AS \"%s\"",
413  gettext_noop("Owner"),
414  gettext_noop("definer"),
415  gettext_noop("invoker"),
416  gettext_noop("Security"));
417  appendPQExpBufferStr(&buf, ",\n ");
418  printACLColumn(&buf, "p.proacl");
419  appendPQExpBuffer(&buf,
420  ",\n l.lanname as \"%s\"",
421  gettext_noop("Language"));
422  appendPQExpBuffer(&buf,
423  ",\n CASE WHEN l.lanname IN ('internal', 'c') THEN p.prosrc END as \"%s\"",
424  gettext_noop("Internal name"));
425  appendPQExpBuffer(&buf,
426  ",\n pg_catalog.obj_description(p.oid, 'pg_proc') as \"%s\"",
427  gettext_noop("Description"));
428  }
429 
430  appendPQExpBufferStr(&buf,
431  "\nFROM pg_catalog.pg_proc p"
432  "\n LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace\n");
433 
434  for (int i = 0; i < num_arg_patterns; i++)
435  {
436  appendPQExpBuffer(&buf,
437  " LEFT JOIN pg_catalog.pg_type t%d ON t%d.oid = p.proargtypes[%d]\n"
438  " LEFT JOIN pg_catalog.pg_namespace nt%d ON nt%d.oid = t%d.typnamespace\n",
439  i, i, i, i, i, i);
440  }
441 
442  if (verbose)
443  appendPQExpBufferStr(&buf,
444  " LEFT JOIN pg_catalog.pg_language l ON l.oid = p.prolang\n");
445 
446  have_where = false;
447 
448  /* filter by function type, if requested */
449  if (showNormal && showAggregate && showProcedure && showTrigger && showWindow)
450  /* Do nothing */ ;
451  else if (showNormal)
452  {
453  if (!showAggregate)
454  {
455  if (have_where)
456  appendPQExpBufferStr(&buf, " AND ");
457  else
458  {
459  appendPQExpBufferStr(&buf, "WHERE ");
460  have_where = true;
461  }
462  if (pset.sversion >= 110000)
463  appendPQExpBufferStr(&buf, "p.prokind <> "
464  CppAsString2(PROKIND_AGGREGATE) "\n");
465  else
466  appendPQExpBufferStr(&buf, "NOT p.proisagg\n");
467  }
468  if (!showProcedure && pset.sversion >= 110000)
469  {
470  if (have_where)
471  appendPQExpBufferStr(&buf, " AND ");
472  else
473  {
474  appendPQExpBufferStr(&buf, "WHERE ");
475  have_where = true;
476  }
477  appendPQExpBufferStr(&buf, "p.prokind <> "
478  CppAsString2(PROKIND_PROCEDURE) "\n");
479  }
480  if (!showTrigger)
481  {
482  if (have_where)
483  appendPQExpBufferStr(&buf, " AND ");
484  else
485  {
486  appendPQExpBufferStr(&buf, "WHERE ");
487  have_where = true;
488  }
489  appendPQExpBufferStr(&buf, "p.prorettype <> 'pg_catalog.trigger'::pg_catalog.regtype\n");
490  }
491  if (!showWindow)
492  {
493  if (have_where)
494  appendPQExpBufferStr(&buf, " AND ");
495  else
496  {
497  appendPQExpBufferStr(&buf, "WHERE ");
498  have_where = true;
499  }
500  if (pset.sversion >= 110000)
501  appendPQExpBufferStr(&buf, "p.prokind <> "
502  CppAsString2(PROKIND_WINDOW) "\n");
503  else
504  appendPQExpBufferStr(&buf, "NOT p.proiswindow\n");
505  }
506  }
507  else
508  {
509  bool needs_or = false;
510 
511  appendPQExpBufferStr(&buf, "WHERE (\n ");
512  have_where = true;
513  /* Note: at least one of these must be true ... */
514  if (showAggregate)
515  {
516  if (pset.sversion >= 110000)
517  appendPQExpBufferStr(&buf, "p.prokind = "
518  CppAsString2(PROKIND_AGGREGATE) "\n");
519  else
520  appendPQExpBufferStr(&buf, "p.proisagg\n");
521  needs_or = true;
522  }
523  if (showTrigger)
524  {
525  if (needs_or)
526  appendPQExpBufferStr(&buf, " OR ");
527  appendPQExpBufferStr(&buf,
528  "p.prorettype = 'pg_catalog.trigger'::pg_catalog.regtype\n");
529  needs_or = true;
530  }
531  if (showProcedure)
532  {
533  if (needs_or)
534  appendPQExpBufferStr(&buf, " OR ");
535  appendPQExpBufferStr(&buf, "p.prokind = "
536  CppAsString2(PROKIND_PROCEDURE) "\n");
537  needs_or = true;
538  }
539  if (showWindow)
540  {
541  if (needs_or)
542  appendPQExpBufferStr(&buf, " OR ");
543  if (pset.sversion >= 110000)
544  appendPQExpBufferStr(&buf, "p.prokind = "
545  CppAsString2(PROKIND_WINDOW) "\n");
546  else
547  appendPQExpBufferStr(&buf, "p.proiswindow\n");
548  }
549  appendPQExpBufferStr(&buf, " )\n");
550  }
551 
552  if (!validateSQLNamePattern(&buf, func_pattern, have_where, false,
553  "n.nspname", "p.proname", NULL,
554  "pg_catalog.pg_function_is_visible(p.oid)",
555  NULL, 3))
556  goto error_return;
557 
558  for (int i = 0; i < num_arg_patterns; i++)
559  {
560  if (strcmp(arg_patterns[i], "-") != 0)
561  {
562  /*
563  * Match type-name patterns against either internal or external
564  * name, like \dT. Unlike \dT, there seems no reason to
565  * discriminate against arrays or composite types.
566  */
567  char nspname[64];
568  char typname[64];
569  char ft[64];
570  char tiv[64];
571 
572  snprintf(nspname, sizeof(nspname), "nt%d.nspname", i);
573  snprintf(typname, sizeof(typname), "t%d.typname", i);
574  snprintf(ft, sizeof(ft),
575  "pg_catalog.format_type(t%d.oid, NULL)", i);
576  snprintf(tiv, sizeof(tiv),
577  "pg_catalog.pg_type_is_visible(t%d.oid)", i);
578  if (!validateSQLNamePattern(&buf,
579  map_typename_pattern(arg_patterns[i]),
580  true, false,
581  nspname, typname, ft, tiv,
582  NULL, 3))
583  goto error_return;
584  }
585  else
586  {
587  /* "-" pattern specifies no such parameter */
588  appendPQExpBuffer(&buf, " AND t%d.typname IS NULL\n", i);
589  }
590  }
591 
592  if (!showSystem && !func_pattern)
593  appendPQExpBufferStr(&buf, " AND n.nspname <> 'pg_catalog'\n"
594  " AND n.nspname <> 'information_schema'\n");
595 
596  appendPQExpBufferStr(&buf, "ORDER BY 1, 2, 4;");
597 
598  res = PSQLexec(buf.data);
599  termPQExpBuffer(&buf);
600  if (!res)
601  return false;
602 
603  myopt.title = _("List of functions");
604  myopt.translate_header = true;
605  if (pset.sversion >= 90600)
606  {
607  myopt.translate_columns = translate_columns;
608  myopt.n_translate_columns = lengthof(translate_columns);
609  }
610  else
611  {
612  myopt.translate_columns = translate_columns_pre_96;
613  myopt.n_translate_columns = lengthof(translate_columns_pre_96);
614  }
615 
616  printQuery(res, &myopt, pset.queryFout, false, pset.logfile);
617 
618  PQclear(res);
619  return true;
620 
621 error_return:
622  termPQExpBuffer(&buf);
623  return false;
624 }
625 
626 
627 
628 /*
629  * \dT
630  * describe types
631  */
632 bool
633 describeTypes(const char *pattern, bool verbose, bool showSystem)
634 {
635  PQExpBufferData buf;
636  PGresult *res;
637  printQueryOpt myopt = pset.popt;
638 
639  initPQExpBuffer(&buf);
640 
641  printfPQExpBuffer(&buf,
642  "SELECT n.nspname as \"%s\",\n"
643  " pg_catalog.format_type(t.oid, NULL) AS \"%s\",\n",
644  gettext_noop("Schema"),
645  gettext_noop("Name"));
646  if (verbose)
647  {
648  appendPQExpBuffer(&buf,
649  " t.typname AS \"%s\",\n"
650  " CASE WHEN t.typrelid != 0\n"
651  " THEN CAST('tuple' AS pg_catalog.text)\n"
652  " WHEN t.typlen < 0\n"
653  " THEN CAST('var' AS pg_catalog.text)\n"
654  " ELSE CAST(t.typlen AS pg_catalog.text)\n"
655  " END AS \"%s\",\n"
656  " pg_catalog.array_to_string(\n"
657  " ARRAY(\n"
658  " SELECT e.enumlabel\n"
659  " FROM pg_catalog.pg_enum e\n"
660  " WHERE e.enumtypid = t.oid\n"
661  " ORDER BY e.enumsortorder\n"
662  " ),\n"
663  " E'\\n'\n"
664  " ) AS \"%s\",\n"
665  " pg_catalog.pg_get_userbyid(t.typowner) AS \"%s\",\n",
666  gettext_noop("Internal name"),
667  gettext_noop("Size"),
668  gettext_noop("Elements"),
669  gettext_noop("Owner"));
670  printACLColumn(&buf, "t.typacl");
671  appendPQExpBufferStr(&buf, ",\n ");
672  }
673 
674  appendPQExpBuffer(&buf,
675  " pg_catalog.obj_description(t.oid, 'pg_type') as \"%s\"\n",
676  gettext_noop("Description"));
677 
678  appendPQExpBufferStr(&buf, "FROM pg_catalog.pg_type t\n"
679  " LEFT JOIN pg_catalog.pg_namespace n ON n.oid = t.typnamespace\n");
680 
681  /*
682  * do not include complex types (typrelid!=0) unless they are standalone
683  * composite types
684  */
685  appendPQExpBufferStr(&buf, "WHERE (t.typrelid = 0 ");
686  appendPQExpBufferStr(&buf, "OR (SELECT c.relkind = " CppAsString2(RELKIND_COMPOSITE_TYPE)
687  " FROM pg_catalog.pg_class c "
688  "WHERE c.oid = t.typrelid))\n");
689 
690  /*
691  * do not include array types unless the pattern contains []
692  */
693  if (pattern == NULL || strstr(pattern, "[]") == NULL)
694  appendPQExpBufferStr(&buf, " AND NOT EXISTS(SELECT 1 FROM pg_catalog.pg_type el WHERE el.oid = t.typelem AND el.typarray = t.oid)\n");
695 
696  if (!showSystem && !pattern)
697  appendPQExpBufferStr(&buf, " AND n.nspname <> 'pg_catalog'\n"
698  " AND n.nspname <> 'information_schema'\n");
699 
700  /* Match name pattern against either internal or external name */
701  if (!validateSQLNamePattern(&buf, map_typename_pattern(pattern),
702  true, false,
703  "n.nspname", "t.typname",
704  "pg_catalog.format_type(t.oid, NULL)",
705  "pg_catalog.pg_type_is_visible(t.oid)",
706  NULL, 3))
707  {
708  termPQExpBuffer(&buf);
709  return false;
710  }
711 
712  appendPQExpBufferStr(&buf, "ORDER BY 1, 2;");
713 
714  res = PSQLexec(buf.data);
715  termPQExpBuffer(&buf);
716  if (!res)
717  return false;
718 
719  myopt.title = _("List of data types");
720  myopt.translate_header = true;
721 
722  printQuery(res, &myopt, pset.queryFout, false, pset.logfile);
723 
724  PQclear(res);
725  return true;
726 }
727 
728 /*
729  * Map some variant type names accepted by the backend grammar into
730  * canonical type names.
731  *
732  * Helper for \dT and other functions that take typename patterns.
733  * This doesn't completely mask the fact that these names are special;
734  * for example, a pattern of "dec*" won't magically match "numeric".
735  * But it goes a long way to reduce the surprise factor.
736  */
737 static const char *
738 map_typename_pattern(const char *pattern)
739 {
740  static const char *const typename_map[] = {
741  /*
742  * These names are accepted by gram.y, although they are neither the
743  * "real" name seen in pg_type nor the canonical name printed by
744  * format_type().
745  */
746  "decimal", "numeric",
747  "float", "double precision",
748  "int", "integer",
749 
750  /*
751  * We also have to map the array names for cases where the canonical
752  * name is different from what pg_type says.
753  */
754  "bool[]", "boolean[]",
755  "decimal[]", "numeric[]",
756  "float[]", "double precision[]",
757  "float4[]", "real[]",
758  "float8[]", "double precision[]",
759  "int[]", "integer[]",
760  "int2[]", "smallint[]",
761  "int4[]", "integer[]",
762  "int8[]", "bigint[]",
763  "time[]", "time without time zone[]",
764  "timetz[]", "time with time zone[]",
765  "timestamp[]", "timestamp without time zone[]",
766  "timestamptz[]", "timestamp with time zone[]",
767  "varbit[]", "bit varying[]",
768  "varchar[]", "character varying[]",
769  NULL
770  };
771 
772  if (pattern == NULL)
773  return NULL;
774  for (int i = 0; typename_map[i] != NULL; i += 2)
775  {
776  if (pg_strcasecmp(pattern, typename_map[i]) == 0)
777  return typename_map[i + 1];
778  }
779  return pattern;
780 }
781 
782 
783 /*
784  * \do
785  * Describe operators
786  */
787 bool
788 describeOperators(const char *oper_pattern,
789  char **arg_patterns, int num_arg_patterns,
790  bool verbose, bool showSystem)
791 {
792  PQExpBufferData buf;
793  PGresult *res;
794  printQueryOpt myopt = pset.popt;
795 
796  initPQExpBuffer(&buf);
797 
798  /*
799  * Note: before Postgres 9.1, we did not assign comments to any built-in
800  * operators, preferring to let the comment on the underlying function
801  * suffice. The coalesce() on the obj_description() calls below supports
802  * this convention by providing a fallback lookup of a comment on the
803  * operator's function. Since 9.1 there is a policy that every built-in
804  * operator should have a comment; so the coalesce() is no longer
805  * necessary so far as built-in operators are concerned. We keep it
806  * anyway, for now, because third-party modules may still be following the
807  * old convention.
808  *
809  * The support for postfix operators in this query is dead code as of
810  * Postgres 14, but we need to keep it for as long as we support talking
811  * to pre-v14 servers.
812  */
813 
814  printfPQExpBuffer(&buf,
815  "SELECT n.nspname as \"%s\",\n"
816  " o.oprname AS \"%s\",\n"
817  " CASE WHEN o.oprkind='l' THEN NULL ELSE pg_catalog.format_type(o.oprleft, NULL) END AS \"%s\",\n"
818  " CASE WHEN o.oprkind='r' THEN NULL ELSE pg_catalog.format_type(o.oprright, NULL) END AS \"%s\",\n"
819  " pg_catalog.format_type(o.oprresult, NULL) AS \"%s\",\n",
820  gettext_noop("Schema"),
821  gettext_noop("Name"),
822  gettext_noop("Left arg type"),
823  gettext_noop("Right arg type"),
824  gettext_noop("Result type"));
825 
826  if (verbose)
827  appendPQExpBuffer(&buf,
828  " o.oprcode AS \"%s\",\n",
829  gettext_noop("Function"));
830 
831  appendPQExpBuffer(&buf,
832  " coalesce(pg_catalog.obj_description(o.oid, 'pg_operator'),\n"
833  " pg_catalog.obj_description(o.oprcode, 'pg_proc')) AS \"%s\"\n"
834  "FROM pg_catalog.pg_operator o\n"
835  " LEFT JOIN pg_catalog.pg_namespace n ON n.oid = o.oprnamespace\n",
836  gettext_noop("Description"));
837 
838  if (num_arg_patterns >= 2)
839  {
840  num_arg_patterns = 2; /* ignore any additional arguments */
841  appendPQExpBufferStr(&buf,
842  " LEFT JOIN pg_catalog.pg_type t0 ON t0.oid = o.oprleft\n"
843  " LEFT JOIN pg_catalog.pg_namespace nt0 ON nt0.oid = t0.typnamespace\n"
844  " LEFT JOIN pg_catalog.pg_type t1 ON t1.oid = o.oprright\n"
845  " LEFT JOIN pg_catalog.pg_namespace nt1 ON nt1.oid = t1.typnamespace\n");
846  }
847  else if (num_arg_patterns == 1)
848  {
849  appendPQExpBufferStr(&buf,
850  " LEFT JOIN pg_catalog.pg_type t0 ON t0.oid = o.oprright\n"
851  " LEFT JOIN pg_catalog.pg_namespace nt0 ON nt0.oid = t0.typnamespace\n");
852  }
853 
854  if (!showSystem && !oper_pattern)
855  appendPQExpBufferStr(&buf, "WHERE n.nspname <> 'pg_catalog'\n"
856  " AND n.nspname <> 'information_schema'\n");
857 
858  if (!validateSQLNamePattern(&buf, oper_pattern,
859  !showSystem && !oper_pattern, true,
860  "n.nspname", "o.oprname", NULL,
861  "pg_catalog.pg_operator_is_visible(o.oid)",
862  NULL, 3))
863  goto error_return;
864 
865  if (num_arg_patterns == 1)
866  appendPQExpBufferStr(&buf, " AND o.oprleft = 0\n");
867 
868  for (int i = 0; i < num_arg_patterns; i++)
869  {
870  if (strcmp(arg_patterns[i], "-") != 0)
871  {
872  /*
873  * Match type-name patterns against either internal or external
874  * name, like \dT. Unlike \dT, there seems no reason to
875  * discriminate against arrays or composite types.
876  */
877  char nspname[64];
878  char typname[64];
879  char ft[64];
880  char tiv[64];
881 
882  snprintf(nspname, sizeof(nspname), "nt%d.nspname", i);
883  snprintf(typname, sizeof(typname), "t%d.typname", i);
884  snprintf(ft, sizeof(ft),
885  "pg_catalog.format_type(t%d.oid, NULL)", i);
886  snprintf(tiv, sizeof(tiv),
887  "pg_catalog.pg_type_is_visible(t%d.oid)", i);
888  if (!validateSQLNamePattern(&buf,
889  map_typename_pattern(arg_patterns[i]),
890  true, false,
891  nspname, typname, ft, tiv,
892  NULL, 3))
893  goto error_return;
894  }
895  else
896  {
897  /* "-" pattern specifies no such parameter */
898  appendPQExpBuffer(&buf, " AND t%d.typname IS NULL\n", i);
899  }
900  }
901 
902  appendPQExpBufferStr(&buf, "ORDER BY 1, 2, 3, 4;");
903 
904  res = PSQLexec(buf.data);
905  termPQExpBuffer(&buf);
906  if (!res)
907  return false;
908 
909  myopt.title = _("List of operators");
910  myopt.translate_header = true;
911 
912  printQuery(res, &myopt, pset.queryFout, false, pset.logfile);
913 
914  PQclear(res);
915  return true;
916 
917 error_return:
918  termPQExpBuffer(&buf);
919  return false;
920 }
921 
922 
923 /*
924  * listAllDbs
925  *
926  * for \l, \list, and -l switch
927  */
928 bool
929 listAllDbs(const char *pattern, bool verbose)
930 {
931  PGresult *res;
932  PQExpBufferData buf;
933  printQueryOpt myopt = pset.popt;
934 
935  initPQExpBuffer(&buf);
936 
937  printfPQExpBuffer(&buf,
938  "SELECT\n"
939  " d.datname as \"%s\",\n"
940  " pg_catalog.pg_get_userbyid(d.datdba) as \"%s\",\n"
941  " pg_catalog.pg_encoding_to_char(d.encoding) as \"%s\",\n",
942  gettext_noop("Name"),
943  gettext_noop("Owner"),
944  gettext_noop("Encoding"));
945  if (pset.sversion >= 150000)
946  appendPQExpBuffer(&buf,
947  " CASE d.datlocprovider "
948  "WHEN " CppAsString2(COLLPROVIDER_BUILTIN) " THEN 'builtin' "
949  "WHEN " CppAsString2(COLLPROVIDER_LIBC) " THEN 'libc' "
950  "WHEN " CppAsString2(COLLPROVIDER_ICU) " THEN 'icu' "
951  "END AS \"%s\",\n",
952  gettext_noop("Locale Provider"));
953  else
954  appendPQExpBuffer(&buf,
955  " 'libc' AS \"%s\",\n",
956  gettext_noop("Locale Provider"));
957  appendPQExpBuffer(&buf,
958  " d.datcollate as \"%s\",\n"
959  " d.datctype as \"%s\",\n",
960  gettext_noop("Collate"),
961  gettext_noop("Ctype"));
962  if (pset.sversion >= 170000)
963  appendPQExpBuffer(&buf,
964  " d.datlocale as \"%s\",\n",
965  gettext_noop("Locale"));
966  else if (pset.sversion >= 150000)
967  appendPQExpBuffer(&buf,
968  " d.daticulocale as \"%s\",\n",
969  gettext_noop("Locale"));
970  else
971  appendPQExpBuffer(&buf,
972  " NULL as \"%s\",\n",
973  gettext_noop("Locale"));
974  if (pset.sversion >= 160000)
975  appendPQExpBuffer(&buf,
976  " d.daticurules as \"%s\",\n",
977  gettext_noop("ICU Rules"));
978  else
979  appendPQExpBuffer(&buf,
980  " NULL as \"%s\",\n",
981  gettext_noop("ICU Rules"));
982  appendPQExpBufferStr(&buf, " ");
983  printACLColumn(&buf, "d.datacl");
984  if (verbose)
985  appendPQExpBuffer(&buf,
986  ",\n CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT')\n"
987  " THEN pg_catalog.pg_size_pretty(pg_catalog.pg_database_size(d.datname))\n"
988  " ELSE 'No Access'\n"
989  " END as \"%s\""
990  ",\n t.spcname as \"%s\""
991  ",\n pg_catalog.shobj_description(d.oid, 'pg_database') as \"%s\"",
992  gettext_noop("Size"),
993  gettext_noop("Tablespace"),
994  gettext_noop("Description"));
995  appendPQExpBufferStr(&buf,
996  "\nFROM pg_catalog.pg_database d\n");
997  if (verbose)
998  appendPQExpBufferStr(&buf,
999  " JOIN pg_catalog.pg_tablespace t on d.dattablespace = t.oid\n");
1000 
1001  if (pattern)
1002  {
1003  if (!validateSQLNamePattern(&buf, pattern, false, false,
1004  NULL, "d.datname", NULL, NULL,
1005  NULL, 1))
1006  {
1007  termPQExpBuffer(&buf);
1008  return false;
1009  }
1010  }
1011 
1012  appendPQExpBufferStr(&buf, "ORDER BY 1;");
1013  res = PSQLexec(buf.data);
1014  termPQExpBuffer(&buf);
1015  if (!res)
1016  return false;
1017 
1018  myopt.title = _("List of databases");
1019  myopt.translate_header = true;
1020 
1021  printQuery(res, &myopt, pset.queryFout, false, pset.logfile);
1022 
1023  PQclear(res);
1024  return true;
1025 }
1026 
1027 
1028 /*
1029  * List Tables' Grant/Revoke Permissions
1030  * \z (now also \dp -- perhaps more mnemonic)
1031  */
1032 bool
1033 permissionsList(const char *pattern, bool showSystem)
1034 {
1035  PQExpBufferData buf;
1036  PGresult *res;
1037  printQueryOpt myopt = pset.popt;
1038  static const bool translate_columns[] = {false, false, true, false, false, false};
1039 
1040  initPQExpBuffer(&buf);
1041 
1042  /*
1043  * we ignore indexes and toast tables since they have no meaningful rights
1044  */
1045  printfPQExpBuffer(&buf,
1046  "SELECT n.nspname as \"%s\",\n"
1047  " c.relname as \"%s\",\n"
1048  " CASE c.relkind"
1049  " WHEN " CppAsString2(RELKIND_RELATION) " THEN '%s'"
1050  " WHEN " CppAsString2(RELKIND_VIEW) " THEN '%s'"
1051  " WHEN " CppAsString2(RELKIND_MATVIEW) " THEN '%s'"
1052  " WHEN " CppAsString2(RELKIND_SEQUENCE) " THEN '%s'"
1053  " WHEN " CppAsString2(RELKIND_FOREIGN_TABLE) " THEN '%s'"
1054  " WHEN " CppAsString2(RELKIND_PARTITIONED_TABLE) " THEN '%s'"
1055  " END as \"%s\",\n"
1056  " ",
1057  gettext_noop("Schema"),
1058  gettext_noop("Name"),
1059  gettext_noop("table"),
1060  gettext_noop("view"),
1061  gettext_noop("materialized view"),
1062  gettext_noop("sequence"),
1063  gettext_noop("foreign table"),
1064  gettext_noop("partitioned table"),
1065  gettext_noop("Type"));
1066 
1067  printACLColumn(&buf, "c.relacl");
1068 
1069  /*
1070  * The formatting of attacl should match printACLColumn(). However, we
1071  * need no special case for an empty attacl, because the backend always
1072  * optimizes that back to NULL.
1073  */
1074  appendPQExpBuffer(&buf,
1075  ",\n pg_catalog.array_to_string(ARRAY(\n"
1076  " SELECT attname || E':\\n ' || pg_catalog.array_to_string(attacl, E'\\n ')\n"
1077  " FROM pg_catalog.pg_attribute a\n"
1078  " WHERE attrelid = c.oid AND NOT attisdropped AND attacl IS NOT NULL\n"
1079  " ), E'\\n') AS \"%s\"",
1080  gettext_noop("Column privileges"));
1081 
1082  if (pset.sversion >= 90500 && pset.sversion < 100000)
1083  appendPQExpBuffer(&buf,
1084  ",\n pg_catalog.array_to_string(ARRAY(\n"
1085  " SELECT polname\n"
1086  " || CASE WHEN polcmd != '*' THEN\n"
1087  " E' (' || polcmd::pg_catalog.text || E'):'\n"
1088  " ELSE E':'\n"
1089  " END\n"
1090  " || CASE WHEN polqual IS NOT NULL THEN\n"
1091  " E'\\n (u): ' || pg_catalog.pg_get_expr(polqual, polrelid)\n"
1092  " ELSE E''\n"
1093  " END\n"
1094  " || CASE WHEN polwithcheck IS NOT NULL THEN\n"
1095  " E'\\n (c): ' || pg_catalog.pg_get_expr(polwithcheck, polrelid)\n"
1096  " ELSE E''\n"
1097  " END"
1098  " || CASE WHEN polroles <> '{0}' THEN\n"
1099  " E'\\n to: ' || pg_catalog.array_to_string(\n"
1100  " ARRAY(\n"
1101  " SELECT rolname\n"
1102  " FROM pg_catalog.pg_roles\n"
1103  " WHERE oid = ANY (polroles)\n"
1104  " ORDER BY 1\n"
1105  " ), E', ')\n"
1106  " ELSE E''\n"
1107  " END\n"
1108  " FROM pg_catalog.pg_policy pol\n"
1109  " WHERE polrelid = c.oid), E'\\n')\n"
1110  " AS \"%s\"",
1111  gettext_noop("Policies"));
1112 
1113  if (pset.sversion >= 100000)
1114  appendPQExpBuffer(&buf,
1115  ",\n pg_catalog.array_to_string(ARRAY(\n"
1116  " SELECT polname\n"
1117  " || CASE WHEN NOT polpermissive THEN\n"
1118  " E' (RESTRICTIVE)'\n"
1119  " ELSE '' END\n"
1120  " || CASE WHEN polcmd != '*' THEN\n"
1121  " E' (' || polcmd::pg_catalog.text || E'):'\n"
1122  " ELSE E':'\n"
1123  " END\n"
1124  " || CASE WHEN polqual IS NOT NULL THEN\n"
1125  " E'\\n (u): ' || pg_catalog.pg_get_expr(polqual, polrelid)\n"
1126  " ELSE E''\n"
1127  " END\n"
1128  " || CASE WHEN polwithcheck IS NOT NULL THEN\n"
1129  " E'\\n (c): ' || pg_catalog.pg_get_expr(polwithcheck, polrelid)\n"
1130  " ELSE E''\n"
1131  " END"
1132  " || CASE WHEN polroles <> '{0}' THEN\n"
1133  " E'\\n to: ' || pg_catalog.array_to_string(\n"
1134  " ARRAY(\n"
1135  " SELECT rolname\n"
1136  " FROM pg_catalog.pg_roles\n"
1137  " WHERE oid = ANY (polroles)\n"
1138  " ORDER BY 1\n"
1139  " ), E', ')\n"
1140  " ELSE E''\n"
1141  " END\n"
1142  " FROM pg_catalog.pg_policy pol\n"
1143  " WHERE polrelid = c.oid), E'\\n')\n"
1144  " AS \"%s\"",
1145  gettext_noop("Policies"));
1146 
1147  appendPQExpBufferStr(&buf, "\nFROM pg_catalog.pg_class c\n"
1148  " LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace\n"
1149  "WHERE c.relkind IN ("
1150  CppAsString2(RELKIND_RELATION) ","
1151  CppAsString2(RELKIND_VIEW) ","
1152  CppAsString2(RELKIND_MATVIEW) ","
1153  CppAsString2(RELKIND_SEQUENCE) ","
1154  CppAsString2(RELKIND_FOREIGN_TABLE) ","
1155  CppAsString2(RELKIND_PARTITIONED_TABLE) ")\n");
1156 
1157  if (!showSystem && !pattern)
1158  appendPQExpBufferStr(&buf, " AND n.nspname <> 'pg_catalog'\n"
1159  " AND n.nspname <> 'information_schema'\n");
1160 
1161  if (!validateSQLNamePattern(&buf, pattern, true, false,
1162  "n.nspname", "c.relname", NULL,
1163  "pg_catalog.pg_table_is_visible(c.oid)",
1164  NULL, 3))
1165  goto error_return;
1166 
1167  appendPQExpBufferStr(&buf, "ORDER BY 1, 2;");
1168 
1169  res = PSQLexec(buf.data);
1170  if (!res)
1171  goto error_return;
1172 
1173  printfPQExpBuffer(&buf, _("Access privileges"));
1174  myopt.title = buf.data;
1175  myopt.translate_header = true;
1176  myopt.translate_columns = translate_columns;
1177  myopt.n_translate_columns = lengthof(translate_columns);
1178 
1179  printQuery(res, &myopt, pset.queryFout, false, pset.logfile);
1180 
1181  termPQExpBuffer(&buf);
1182  PQclear(res);
1183  return true;
1184 
1185 error_return:
1186  termPQExpBuffer(&buf);
1187  return false;
1188 }
1189 
1190 
1191 /*
1192  * \ddp
1193  *
1194  * List Default ACLs. The pattern can match either schema or role name.
1195  */
1196 bool
1197 listDefaultACLs(const char *pattern)
1198 {
1199  PQExpBufferData buf;
1200  PGresult *res;
1201  printQueryOpt myopt = pset.popt;
1202  static const bool translate_columns[] = {false, false, true, false};
1203 
1204  initPQExpBuffer(&buf);
1205 
1206  printfPQExpBuffer(&buf,
1207  "SELECT pg_catalog.pg_get_userbyid(d.defaclrole) AS \"%s\",\n"
1208  " n.nspname AS \"%s\",\n"
1209  " 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"
1210  " ",
1211  gettext_noop("Owner"),
1212  gettext_noop("Schema"),
1213  DEFACLOBJ_RELATION,
1214  gettext_noop("table"),
1215  DEFACLOBJ_SEQUENCE,
1216  gettext_noop("sequence"),
1217  DEFACLOBJ_FUNCTION,
1218  gettext_noop("function"),
1219  DEFACLOBJ_TYPE,
1220  gettext_noop("type"),
1221  DEFACLOBJ_NAMESPACE,
1222  gettext_noop("schema"),
1223  gettext_noop("Type"));
1224 
1225  printACLColumn(&buf, "d.defaclacl");
1226 
1227  appendPQExpBufferStr(&buf, "\nFROM pg_catalog.pg_default_acl d\n"
1228  " LEFT JOIN pg_catalog.pg_namespace n ON n.oid = d.defaclnamespace\n");
1229 
1230  if (!validateSQLNamePattern(&buf, pattern, false, false,
1231  NULL,
1232  "n.nspname",
1233  "pg_catalog.pg_get_userbyid(d.defaclrole)",
1234  NULL,
1235  NULL, 3))
1236  goto error_return;
1237 
1238  appendPQExpBufferStr(&buf, "ORDER BY 1, 2, 3;");
1239 
1240  res = PSQLexec(buf.data);
1241  if (!res)
1242  goto error_return;
1243 
1244  printfPQExpBuffer(&buf, _("Default access privileges"));
1245  myopt.title = buf.data;
1246  myopt.translate_header = true;
1247  myopt.translate_columns = translate_columns;
1248  myopt.n_translate_columns = lengthof(translate_columns);
1249 
1250  printQuery(res, &myopt, pset.queryFout, false, pset.logfile);
1251 
1252  termPQExpBuffer(&buf);
1253  PQclear(res);
1254  return true;
1255 
1256 error_return:
1257  termPQExpBuffer(&buf);
1258  return false;
1259 }
1260 
1261 
1262 /*
1263  * Get object comments
1264  *
1265  * \dd [foo]
1266  *
1267  * Note: This command only lists comments for object types which do not have
1268  * their comments displayed by their own backslash commands. The following
1269  * types of objects will be displayed: constraint, operator class,
1270  * operator family, rule, and trigger.
1271  *
1272  */
1273 bool
1274 objectDescription(const char *pattern, bool showSystem)
1275 {
1276  PQExpBufferData buf;
1277  PGresult *res;
1278  printQueryOpt myopt = pset.popt;
1279  static const bool translate_columns[] = {false, false, true, false};
1280 
1281  initPQExpBuffer(&buf);
1282 
1283  appendPQExpBuffer(&buf,
1284  "SELECT DISTINCT tt.nspname AS \"%s\", tt.name AS \"%s\", tt.object AS \"%s\", d.description AS \"%s\"\n"
1285  "FROM (\n",
1286  gettext_noop("Schema"),
1287  gettext_noop("Name"),
1288  gettext_noop("Object"),
1289  gettext_noop("Description"));
1290 
1291  /* Table constraint descriptions */
1292  appendPQExpBuffer(&buf,
1293  " SELECT pgc.oid as oid, pgc.tableoid AS tableoid,\n"
1294  " n.nspname as nspname,\n"
1295  " CAST(pgc.conname AS pg_catalog.text) as name,"
1296  " CAST('%s' AS pg_catalog.text) as object\n"
1297  " FROM pg_catalog.pg_constraint pgc\n"
1298  " JOIN pg_catalog.pg_class c "
1299  "ON c.oid = pgc.conrelid\n"
1300  " LEFT JOIN pg_catalog.pg_namespace n "
1301  " ON n.oid = c.relnamespace\n",
1302  gettext_noop("table constraint"));
1303 
1304  if (!showSystem && !pattern)
1305  appendPQExpBufferStr(&buf, "WHERE n.nspname <> 'pg_catalog'\n"
1306  " AND n.nspname <> 'information_schema'\n");
1307 
1308  if (!validateSQLNamePattern(&buf, pattern, !showSystem && !pattern,
1309  false, "n.nspname", "pgc.conname", NULL,
1310  "pg_catalog.pg_table_is_visible(c.oid)",
1311  NULL, 3))
1312  goto error_return;
1313 
1314  /* Domain constraint descriptions */
1315  appendPQExpBuffer(&buf,
1316  "UNION ALL\n"
1317  " SELECT pgc.oid as oid, pgc.tableoid AS tableoid,\n"
1318  " n.nspname as nspname,\n"
1319  " CAST(pgc.conname AS pg_catalog.text) as name,"
1320  " CAST('%s' AS pg_catalog.text) as object\n"
1321  " FROM pg_catalog.pg_constraint pgc\n"
1322  " JOIN pg_catalog.pg_type t "
1323  "ON t.oid = pgc.contypid\n"
1324  " LEFT JOIN pg_catalog.pg_namespace n "
1325  " ON n.oid = t.typnamespace\n",
1326  gettext_noop("domain constraint"));
1327 
1328  if (!showSystem && !pattern)
1329  appendPQExpBufferStr(&buf, "WHERE n.nspname <> 'pg_catalog'\n"
1330  " AND n.nspname <> 'information_schema'\n");
1331 
1332  if (!validateSQLNamePattern(&buf, pattern, !showSystem && !pattern,
1333  false, "n.nspname", "pgc.conname", NULL,
1334  "pg_catalog.pg_type_is_visible(t.oid)",
1335  NULL, 3))
1336  goto error_return;
1337 
1338  /* Operator class descriptions */
1339  appendPQExpBuffer(&buf,
1340  "UNION ALL\n"
1341  " SELECT o.oid as oid, o.tableoid as tableoid,\n"
1342  " n.nspname as nspname,\n"
1343  " CAST(o.opcname AS pg_catalog.text) as name,\n"
1344  " CAST('%s' AS pg_catalog.text) as object\n"
1345  " FROM pg_catalog.pg_opclass o\n"
1346  " JOIN pg_catalog.pg_am am ON "
1347  "o.opcmethod = am.oid\n"
1348  " JOIN pg_catalog.pg_namespace n ON "
1349  "n.oid = o.opcnamespace\n",
1350  gettext_noop("operator class"));
1351 
1352  if (!showSystem && !pattern)
1353  appendPQExpBufferStr(&buf, " AND n.nspname <> 'pg_catalog'\n"
1354  " AND n.nspname <> 'information_schema'\n");
1355 
1356  if (!validateSQLNamePattern(&buf, pattern, true, false,
1357  "n.nspname", "o.opcname", NULL,
1358  "pg_catalog.pg_opclass_is_visible(o.oid)",
1359  NULL, 3))
1360  goto error_return;
1361 
1362  /* Operator family descriptions */
1363  appendPQExpBuffer(&buf,
1364  "UNION ALL\n"
1365  " SELECT opf.oid as oid, opf.tableoid as tableoid,\n"
1366  " n.nspname as nspname,\n"
1367  " CAST(opf.opfname AS pg_catalog.text) AS name,\n"
1368  " CAST('%s' AS pg_catalog.text) as object\n"
1369  " FROM pg_catalog.pg_opfamily opf\n"
1370  " JOIN pg_catalog.pg_am am "
1371  "ON opf.opfmethod = am.oid\n"
1372  " JOIN pg_catalog.pg_namespace n "
1373  "ON opf.opfnamespace = n.oid\n",
1374  gettext_noop("operator family"));
1375 
1376  if (!showSystem && !pattern)
1377  appendPQExpBufferStr(&buf, " AND n.nspname <> 'pg_catalog'\n"
1378  " AND n.nspname <> 'information_schema'\n");
1379 
1380  if (!validateSQLNamePattern(&buf, pattern, true, false,
1381  "n.nspname", "opf.opfname", NULL,
1382  "pg_catalog.pg_opfamily_is_visible(opf.oid)",
1383  NULL, 3))
1384  goto error_return;
1385 
1386  /* Rule descriptions (ignore rules for views) */
1387  appendPQExpBuffer(&buf,
1388  "UNION ALL\n"
1389  " SELECT r.oid as oid, r.tableoid as tableoid,\n"
1390  " n.nspname as nspname,\n"
1391  " CAST(r.rulename AS pg_catalog.text) as name,"
1392  " CAST('%s' AS pg_catalog.text) as object\n"
1393  " FROM pg_catalog.pg_rewrite r\n"
1394  " JOIN pg_catalog.pg_class c ON c.oid = r.ev_class\n"
1395  " LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace\n"
1396  " WHERE r.rulename != '_RETURN'\n",
1397  gettext_noop("rule"));
1398 
1399  if (!showSystem && !pattern)
1400  appendPQExpBufferStr(&buf, " AND n.nspname <> 'pg_catalog'\n"
1401  " AND n.nspname <> 'information_schema'\n");
1402 
1403  if (!validateSQLNamePattern(&buf, pattern, true, false,
1404  "n.nspname", "r.rulename", NULL,
1405  "pg_catalog.pg_table_is_visible(c.oid)",
1406  NULL, 3))
1407  goto error_return;
1408 
1409  /* Trigger descriptions */
1410  appendPQExpBuffer(&buf,
1411  "UNION ALL\n"
1412  " SELECT t.oid as oid, t.tableoid as tableoid,\n"
1413  " n.nspname as nspname,\n"
1414  " CAST(t.tgname AS pg_catalog.text) as name,"
1415  " CAST('%s' AS pg_catalog.text) as object\n"
1416  " FROM pg_catalog.pg_trigger t\n"
1417  " JOIN pg_catalog.pg_class c ON c.oid = t.tgrelid\n"
1418  " LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace\n",
1419  gettext_noop("trigger"));
1420 
1421  if (!showSystem && !pattern)
1422  appendPQExpBufferStr(&buf, "WHERE n.nspname <> 'pg_catalog'\n"
1423  " AND n.nspname <> 'information_schema'\n");
1424 
1425  if (!validateSQLNamePattern(&buf, pattern, !showSystem && !pattern, false,
1426  "n.nspname", "t.tgname", NULL,
1427  "pg_catalog.pg_table_is_visible(c.oid)",
1428  NULL, 3))
1429  goto error_return;
1430 
1431  appendPQExpBufferStr(&buf,
1432  ") AS tt\n"
1433  " JOIN pg_catalog.pg_description d ON (tt.oid = d.objoid AND tt.tableoid = d.classoid AND d.objsubid = 0)\n");
1434 
1435  appendPQExpBufferStr(&buf, "ORDER BY 1, 2, 3;");
1436 
1437  res = PSQLexec(buf.data);
1438  termPQExpBuffer(&buf);
1439  if (!res)
1440  return false;
1441 
1442  myopt.title = _("Object descriptions");
1443  myopt.translate_header = true;
1444  myopt.translate_columns = translate_columns;
1445  myopt.n_translate_columns = lengthof(translate_columns);
1446 
1447  printQuery(res, &myopt, pset.queryFout, false, pset.logfile);
1448 
1449  PQclear(res);
1450  return true;
1451 
1452 error_return:
1453  termPQExpBuffer(&buf);
1454  return false;
1455 }
1456 
1457 
1458 /*
1459  * describeTableDetails (for \d)
1460  *
1461  * This routine finds the tables to be displayed, and calls
1462  * describeOneTableDetails for each one.
1463  *
1464  * verbose: if true, this is \d+
1465  */
1466 bool
1467 describeTableDetails(const char *pattern, bool verbose, bool showSystem)
1468 {
1469  PQExpBufferData buf;
1470  PGresult *res;
1471  int i;
1472 
1473  initPQExpBuffer(&buf);
1474 
1475  printfPQExpBuffer(&buf,
1476  "SELECT c.oid,\n"
1477  " n.nspname,\n"
1478  " c.relname\n"
1479  "FROM pg_catalog.pg_class c\n"
1480  " LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace\n");
1481 
1482  if (!showSystem && !pattern)
1483  appendPQExpBufferStr(&buf, "WHERE n.nspname <> 'pg_catalog'\n"
1484  " AND n.nspname <> 'information_schema'\n");
1485 
1486  if (!validateSQLNamePattern(&buf, pattern, !showSystem && !pattern, false,
1487  "n.nspname", "c.relname", NULL,
1488  "pg_catalog.pg_table_is_visible(c.oid)",
1489  NULL, 3))
1490  {
1491  termPQExpBuffer(&buf);
1492  return false;
1493  }
1494 
1495  appendPQExpBufferStr(&buf, "ORDER BY 2, 3;");
1496 
1497  res = PSQLexec(buf.data);
1498  termPQExpBuffer(&buf);
1499  if (!res)
1500  return false;
1501 
1502  if (PQntuples(res) == 0)
1503  {
1504  if (!pset.quiet)
1505  {
1506  if (pattern)
1507  pg_log_error("Did not find any relation named \"%s\".",
1508  pattern);
1509  else
1510  pg_log_error("Did not find any relations.");
1511  }
1512  PQclear(res);
1513  return false;
1514  }
1515 
1516  for (i = 0; i < PQntuples(res); i++)
1517  {
1518  const char *oid;
1519  const char *nspname;
1520  const char *relname;
1521 
1522  oid = PQgetvalue(res, i, 0);
1523  nspname = PQgetvalue(res, i, 1);
1524  relname = PQgetvalue(res, i, 2);
1525 
1526  if (!describeOneTableDetails(nspname, relname, oid, verbose))
1527  {
1528  PQclear(res);
1529  return false;
1530  }
1531  if (cancel_pressed)
1532  {
1533  PQclear(res);
1534  return false;
1535  }
1536  }
1537 
1538  PQclear(res);
1539  return true;
1540 }
1541 
1542 /*
1543  * describeOneTableDetails (for \d)
1544  *
1545  * Unfortunately, the information presented here is so complicated that it
1546  * cannot be done in a single query. So we have to assemble the printed table
1547  * by hand and pass it to the underlying printTable() function.
1548  */
1549 static bool
1550 describeOneTableDetails(const char *schemaname,
1551  const char *relationname,
1552  const char *oid,
1553  bool verbose)
1554 {
1555  bool retval = false;
1556  PQExpBufferData buf;
1557  PGresult *res = NULL;
1558  printTableOpt myopt = pset.popt.topt;
1559  printTableContent cont;
1560  bool printTableInitialized = false;
1561  int i;
1562  char *view_def = NULL;
1563  char *headers[12];
1564  PQExpBufferData title;
1565  PQExpBufferData tmpbuf;
1566  int cols;
1567  int attname_col = -1, /* column indexes in "res" */
1568  atttype_col = -1,
1569  attrdef_col = -1,
1570  attnotnull_col = -1,
1571  attcoll_col = -1,
1572  attidentity_col = -1,
1573  attgenerated_col = -1,
1574  isindexkey_col = -1,
1575  indexdef_col = -1,
1576  fdwopts_col = -1,
1577  attstorage_col = -1,
1578  attcompression_col = -1,
1579  attstattarget_col = -1,
1580  attdescr_col = -1;
1581  int numrows;
1582  struct
1583  {
1584  int16 checks;
1585  char relkind;
1586  bool hasindex;
1587  bool hasrules;
1588  bool hastriggers;
1589  bool rowsecurity;
1590  bool forcerowsecurity;
1591  bool hasoids;
1592  bool ispartition;
1593  Oid tablespace;
1594  char *reloptions;
1595  char *reloftype;
1596  char relpersistence;
1597  char relreplident;
1598  char *relam;
1599  } tableinfo;
1600  bool show_column_details = false;
1601 
1602  myopt.default_footer = false;
1603  /* This output looks confusing in expanded mode. */
1604  myopt.expanded = false;
1605 
1606  initPQExpBuffer(&buf);
1607  initPQExpBuffer(&title);
1608  initPQExpBuffer(&tmpbuf);
1609 
1610  /* Get general table info */
1611  if (pset.sversion >= 120000)
1612  {
1613  printfPQExpBuffer(&buf,
1614  "SELECT c.relchecks, c.relkind, c.relhasindex, c.relhasrules, "
1615  "c.relhastriggers, c.relrowsecurity, c.relforcerowsecurity, "
1616  "false AS relhasoids, c.relispartition, %s, c.reltablespace, "
1617  "CASE WHEN c.reloftype = 0 THEN '' ELSE c.reloftype::pg_catalog.regtype::pg_catalog.text END, "
1618  "c.relpersistence, c.relreplident, am.amname\n"
1619  "FROM pg_catalog.pg_class c\n "
1620  "LEFT JOIN pg_catalog.pg_class tc ON (c.reltoastrelid = tc.oid)\n"
1621  "LEFT JOIN pg_catalog.pg_am am ON (c.relam = am.oid)\n"
1622  "WHERE c.oid = '%s';",
1623  (verbose ?
1624  "pg_catalog.array_to_string(c.reloptions || "
1625  "array(select 'toast.' || x from pg_catalog.unnest(tc.reloptions) x), ', ')\n"
1626  : "''"),
1627  oid);
1628  }
1629  else if (pset.sversion >= 100000)
1630  {
1631  printfPQExpBuffer(&buf,
1632  "SELECT c.relchecks, c.relkind, c.relhasindex, c.relhasrules, "
1633  "c.relhastriggers, c.relrowsecurity, c.relforcerowsecurity, "
1634  "c.relhasoids, c.relispartition, %s, c.reltablespace, "
1635  "CASE WHEN c.reloftype = 0 THEN '' ELSE c.reloftype::pg_catalog.regtype::pg_catalog.text END, "
1636  "c.relpersistence, c.relreplident\n"
1637  "FROM pg_catalog.pg_class c\n "
1638  "LEFT JOIN pg_catalog.pg_class tc ON (c.reltoastrelid = tc.oid)\n"
1639  "WHERE c.oid = '%s';",
1640  (verbose ?
1641  "pg_catalog.array_to_string(c.reloptions || "
1642  "array(select 'toast.' || x from pg_catalog.unnest(tc.reloptions) x), ', ')\n"
1643  : "''"),
1644  oid);
1645  }
1646  else if (pset.sversion >= 90500)
1647  {
1648  printfPQExpBuffer(&buf,
1649  "SELECT c.relchecks, c.relkind, c.relhasindex, c.relhasrules, "
1650  "c.relhastriggers, c.relrowsecurity, c.relforcerowsecurity, "
1651  "c.relhasoids, false as relispartition, %s, c.reltablespace, "
1652  "CASE WHEN c.reloftype = 0 THEN '' ELSE c.reloftype::pg_catalog.regtype::pg_catalog.text END, "
1653  "c.relpersistence, c.relreplident\n"
1654  "FROM pg_catalog.pg_class c\n "
1655  "LEFT JOIN pg_catalog.pg_class tc ON (c.reltoastrelid = tc.oid)\n"
1656  "WHERE c.oid = '%s';",
1657  (verbose ?
1658  "pg_catalog.array_to_string(c.reloptions || "
1659  "array(select 'toast.' || x from pg_catalog.unnest(tc.reloptions) x), ', ')\n"
1660  : "''"),
1661  oid);
1662  }
1663  else if (pset.sversion >= 90400)
1664  {
1665  printfPQExpBuffer(&buf,
1666  "SELECT c.relchecks, c.relkind, c.relhasindex, c.relhasrules, "
1667  "c.relhastriggers, false, false, c.relhasoids, "
1668  "false as relispartition, %s, c.reltablespace, "
1669  "CASE WHEN c.reloftype = 0 THEN '' ELSE c.reloftype::pg_catalog.regtype::pg_catalog.text END, "
1670  "c.relpersistence, c.relreplident\n"
1671  "FROM pg_catalog.pg_class c\n "
1672  "LEFT JOIN pg_catalog.pg_class tc ON (c.reltoastrelid = tc.oid)\n"
1673  "WHERE c.oid = '%s';",
1674  (verbose ?
1675  "pg_catalog.array_to_string(c.reloptions || "
1676  "array(select 'toast.' || x from pg_catalog.unnest(tc.reloptions) x), ', ')\n"
1677  : "''"),
1678  oid);
1679  }
1680  else
1681  {
1682  printfPQExpBuffer(&buf,
1683  "SELECT c.relchecks, c.relkind, c.relhasindex, c.relhasrules, "
1684  "c.relhastriggers, false, false, c.relhasoids, "
1685  "false as relispartition, %s, c.reltablespace, "
1686  "CASE WHEN c.reloftype = 0 THEN '' ELSE c.reloftype::pg_catalog.regtype::pg_catalog.text END, "
1687  "c.relpersistence\n"
1688  "FROM pg_catalog.pg_class c\n "
1689  "LEFT JOIN pg_catalog.pg_class tc ON (c.reltoastrelid = tc.oid)\n"
1690  "WHERE c.oid = '%s';",
1691  (verbose ?
1692  "pg_catalog.array_to_string(c.reloptions || "
1693  "array(select 'toast.' || x from pg_catalog.unnest(tc.reloptions) x), ', ')\n"
1694  : "''"),
1695  oid);
1696  }
1697 
1698  res = PSQLexec(buf.data);
1699  if (!res)
1700  goto error_return;
1701 
1702  /* Did we get anything? */
1703  if (PQntuples(res) == 0)
1704  {
1705  if (!pset.quiet)
1706  pg_log_error("Did not find any relation with OID %s.", oid);
1707  goto error_return;
1708  }
1709 
1710  tableinfo.checks = atoi(PQgetvalue(res, 0, 0));
1711  tableinfo.relkind = *(PQgetvalue(res, 0, 1));
1712  tableinfo.hasindex = strcmp(PQgetvalue(res, 0, 2), "t") == 0;
1713  tableinfo.hasrules = strcmp(PQgetvalue(res, 0, 3), "t") == 0;
1714  tableinfo.hastriggers = strcmp(PQgetvalue(res, 0, 4), "t") == 0;
1715  tableinfo.rowsecurity = strcmp(PQgetvalue(res, 0, 5), "t") == 0;
1716  tableinfo.forcerowsecurity = strcmp(PQgetvalue(res, 0, 6), "t") == 0;
1717  tableinfo.hasoids = strcmp(PQgetvalue(res, 0, 7), "t") == 0;
1718  tableinfo.ispartition = strcmp(PQgetvalue(res, 0, 8), "t") == 0;
1719  tableinfo.reloptions = pg_strdup(PQgetvalue(res, 0, 9));
1720  tableinfo.tablespace = atooid(PQgetvalue(res, 0, 10));
1721  tableinfo.reloftype = (strcmp(PQgetvalue(res, 0, 11), "") != 0) ?
1722  pg_strdup(PQgetvalue(res, 0, 11)) : NULL;
1723  tableinfo.relpersistence = *(PQgetvalue(res, 0, 12));
1724  tableinfo.relreplident = (pset.sversion >= 90400) ?
1725  *(PQgetvalue(res, 0, 13)) : 'd';
1726  if (pset.sversion >= 120000)
1727  tableinfo.relam = PQgetisnull(res, 0, 14) ?
1728  (char *) NULL : pg_strdup(PQgetvalue(res, 0, 14));
1729  else
1730  tableinfo.relam = NULL;
1731  PQclear(res);
1732  res = NULL;
1733 
1734  /*
1735  * If it's a sequence, deal with it here separately.
1736  */
1737  if (tableinfo.relkind == RELKIND_SEQUENCE)
1738  {
1739  PGresult *result = NULL;
1740  printQueryOpt myopt = pset.popt;
1741  char *footers[2] = {NULL, NULL};
1742 
1743  if (pset.sversion >= 100000)
1744  {
1745  printfPQExpBuffer(&buf,
1746  "SELECT pg_catalog.format_type(seqtypid, NULL) AS \"%s\",\n"
1747  " seqstart AS \"%s\",\n"
1748  " seqmin AS \"%s\",\n"
1749  " seqmax AS \"%s\",\n"
1750  " seqincrement AS \"%s\",\n"
1751  " CASE WHEN seqcycle THEN '%s' ELSE '%s' END AS \"%s\",\n"
1752  " seqcache AS \"%s\"\n",
1753  gettext_noop("Type"),
1754  gettext_noop("Start"),
1755  gettext_noop("Minimum"),
1756  gettext_noop("Maximum"),
1757  gettext_noop("Increment"),
1758  gettext_noop("yes"),
1759  gettext_noop("no"),
1760  gettext_noop("Cycles?"),
1761  gettext_noop("Cache"));
1762  appendPQExpBuffer(&buf,
1763  "FROM pg_catalog.pg_sequence\n"
1764  "WHERE seqrelid = '%s';",
1765  oid);
1766  }
1767  else
1768  {
1769  printfPQExpBuffer(&buf,
1770  "SELECT 'bigint' AS \"%s\",\n"
1771  " start_value AS \"%s\",\n"
1772  " min_value AS \"%s\",\n"
1773  " max_value AS \"%s\",\n"
1774  " increment_by AS \"%s\",\n"
1775  " CASE WHEN is_cycled THEN '%s' ELSE '%s' END AS \"%s\",\n"
1776  " cache_value AS \"%s\"\n",
1777  gettext_noop("Type"),
1778  gettext_noop("Start"),
1779  gettext_noop("Minimum"),
1780  gettext_noop("Maximum"),
1781  gettext_noop("Increment"),
1782  gettext_noop("yes"),
1783  gettext_noop("no"),
1784  gettext_noop("Cycles?"),
1785  gettext_noop("Cache"));
1786  appendPQExpBuffer(&buf, "FROM %s", fmtId(schemaname));
1787  /* must be separate because fmtId isn't reentrant */
1788  appendPQExpBuffer(&buf, ".%s;", fmtId(relationname));
1789  }
1790 
1791  res = PSQLexec(buf.data);
1792  if (!res)
1793  goto error_return;
1794 
1795  /* Get the column that owns this sequence */
1796  printfPQExpBuffer(&buf, "SELECT pg_catalog.quote_ident(nspname) || '.' ||"
1797  "\n pg_catalog.quote_ident(relname) || '.' ||"
1798  "\n pg_catalog.quote_ident(attname),"
1799  "\n d.deptype"
1800  "\nFROM pg_catalog.pg_class c"
1801  "\nINNER JOIN pg_catalog.pg_depend d ON c.oid=d.refobjid"
1802  "\nINNER JOIN pg_catalog.pg_namespace n ON n.oid=c.relnamespace"
1803  "\nINNER JOIN pg_catalog.pg_attribute a ON ("
1804  "\n a.attrelid=c.oid AND"
1805  "\n a.attnum=d.refobjsubid)"
1806  "\nWHERE d.classid='pg_catalog.pg_class'::pg_catalog.regclass"
1807  "\n AND d.refclassid='pg_catalog.pg_class'::pg_catalog.regclass"
1808  "\n AND d.objid='%s'"
1809  "\n AND d.deptype IN ('a', 'i')",
1810  oid);
1811 
1812  result = PSQLexec(buf.data);
1813 
1814  /*
1815  * If we get no rows back, don't show anything (obviously). We should
1816  * never get more than one row back, but if we do, just ignore it and
1817  * don't print anything.
1818  */
1819  if (!result)
1820  goto error_return;
1821  else if (PQntuples(result) == 1)
1822  {
1823  switch (PQgetvalue(result, 0, 1)[0])
1824  {
1825  case 'a':
1826  footers[0] = psprintf(_("Owned by: %s"),
1827  PQgetvalue(result, 0, 0));
1828  break;
1829  case 'i':
1830  footers[0] = psprintf(_("Sequence for identity column: %s"),
1831  PQgetvalue(result, 0, 0));
1832  break;
1833  }
1834  }
1835  PQclear(result);
1836 
1837  if (tableinfo.relpersistence == RELPERSISTENCE_UNLOGGED)
1838  printfPQExpBuffer(&title, _("Unlogged sequence \"%s.%s\""),
1839  schemaname, relationname);
1840  else
1841  printfPQExpBuffer(&title, _("Sequence \"%s.%s\""),
1842  schemaname, relationname);
1843 
1844  myopt.footers = footers;
1845  myopt.topt.default_footer = false;
1846  myopt.title = title.data;
1847  myopt.translate_header = true;
1848 
1849  printQuery(res, &myopt, pset.queryFout, false, pset.logfile);
1850 
1851  free(footers[0]);
1852 
1853  retval = true;
1854  goto error_return; /* not an error, just return early */
1855  }
1856 
1857  /* Identify whether we should print collation, nullable, default vals */
1858  if (tableinfo.relkind == RELKIND_RELATION ||
1859  tableinfo.relkind == RELKIND_VIEW ||
1860  tableinfo.relkind == RELKIND_MATVIEW ||
1861  tableinfo.relkind == RELKIND_FOREIGN_TABLE ||
1862  tableinfo.relkind == RELKIND_COMPOSITE_TYPE ||
1863  tableinfo.relkind == RELKIND_PARTITIONED_TABLE)
1864  show_column_details = true;
1865 
1866  /*
1867  * Get per-column info
1868  *
1869  * Since the set of query columns we need varies depending on relkind and
1870  * server version, we compute all the column numbers on-the-fly. Column
1871  * number variables for columns not fetched are left as -1; this avoids
1872  * duplicative test logic below.
1873  */
1874  cols = 0;
1875  printfPQExpBuffer(&buf, "SELECT a.attname");
1876  attname_col = cols++;
1877  appendPQExpBufferStr(&buf, ",\n pg_catalog.format_type(a.atttypid, a.atttypmod)");
1878  atttype_col = cols++;
1879 
1880  if (show_column_details)
1881  {
1882  /* use "pretty" mode for expression to avoid excessive parentheses */
1883  appendPQExpBufferStr(&buf,
1884  ",\n (SELECT pg_catalog.pg_get_expr(d.adbin, d.adrelid, true)"
1885  "\n FROM pg_catalog.pg_attrdef d"
1886  "\n WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef)"
1887  ",\n a.attnotnull");
1888  attrdef_col = cols++;
1889  attnotnull_col = cols++;
1890  appendPQExpBufferStr(&buf, ",\n (SELECT c.collname FROM pg_catalog.pg_collation c, pg_catalog.pg_type t\n"
1891  " WHERE c.oid = a.attcollation AND t.oid = a.atttypid AND a.attcollation <> t.typcollation) AS attcollation");
1892  attcoll_col = cols++;
1893  if (pset.sversion >= 100000)
1894  appendPQExpBufferStr(&buf, ",\n a.attidentity");
1895  else
1896  appendPQExpBufferStr(&buf, ",\n ''::pg_catalog.char AS attidentity");
1897  attidentity_col = cols++;
1898  if (pset.sversion >= 120000)
1899  appendPQExpBufferStr(&buf, ",\n a.attgenerated");
1900  else
1901  appendPQExpBufferStr(&buf, ",\n ''::pg_catalog.char AS attgenerated");
1902  attgenerated_col = cols++;
1903  }
1904  if (tableinfo.relkind == RELKIND_INDEX ||
1905  tableinfo.relkind == RELKIND_PARTITIONED_INDEX)
1906  {
1907  if (pset.sversion >= 110000)
1908  {
1909  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",
1910  oid,
1911  gettext_noop("yes"),
1912  gettext_noop("no"));
1913  isindexkey_col = cols++;
1914  }
1915  appendPQExpBufferStr(&buf, ",\n pg_catalog.pg_get_indexdef(a.attrelid, a.attnum, TRUE) AS indexdef");
1916  indexdef_col = cols++;
1917  }
1918  /* FDW options for foreign table column */
1919  if (tableinfo.relkind == RELKIND_FOREIGN_TABLE)
1920  {
1921  appendPQExpBufferStr(&buf, ",\n CASE WHEN attfdwoptions IS NULL THEN '' ELSE "
1922  " '(' || pg_catalog.array_to_string(ARRAY(SELECT pg_catalog.quote_ident(option_name) || ' ' || pg_catalog.quote_literal(option_value) FROM "
1923  " pg_catalog.pg_options_to_table(attfdwoptions)), ', ') || ')' END AS attfdwoptions");
1924  fdwopts_col = cols++;
1925  }
1926  if (verbose)
1927  {
1928  appendPQExpBufferStr(&buf, ",\n a.attstorage");
1929  attstorage_col = cols++;
1930 
1931  /* compression info, if relevant to relkind */
1932  if (pset.sversion >= 140000 &&
1933  !pset.hide_compression &&
1934  (tableinfo.relkind == RELKIND_RELATION ||
1935  tableinfo.relkind == RELKIND_PARTITIONED_TABLE ||
1936  tableinfo.relkind == RELKIND_MATVIEW))
1937  {
1938  appendPQExpBufferStr(&buf, ",\n a.attcompression AS attcompression");
1939  attcompression_col = cols++;
1940  }
1941 
1942  /* stats target, if relevant to relkind */
1943  if (tableinfo.relkind == RELKIND_RELATION ||
1944  tableinfo.relkind == RELKIND_INDEX ||
1945  tableinfo.relkind == RELKIND_PARTITIONED_INDEX ||
1946  tableinfo.relkind == RELKIND_MATVIEW ||
1947  tableinfo.relkind == RELKIND_FOREIGN_TABLE ||
1948  tableinfo.relkind == RELKIND_PARTITIONED_TABLE)
1949  {
1950  appendPQExpBufferStr(&buf, ",\n CASE WHEN a.attstattarget=-1 THEN NULL ELSE a.attstattarget END AS attstattarget");
1951  attstattarget_col = cols++;
1952  }
1953 
1954  /*
1955  * In 9.0+, we have column comments for: relations, views, composite
1956  * types, and foreign tables (cf. CommentObject() in comment.c).
1957  */
1958  if (tableinfo.relkind == RELKIND_RELATION ||
1959  tableinfo.relkind == RELKIND_VIEW ||
1960  tableinfo.relkind == RELKIND_MATVIEW ||
1961  tableinfo.relkind == RELKIND_FOREIGN_TABLE ||
1962  tableinfo.relkind == RELKIND_COMPOSITE_TYPE ||
1963  tableinfo.relkind == RELKIND_PARTITIONED_TABLE)
1964  {
1965  appendPQExpBufferStr(&buf, ",\n pg_catalog.col_description(a.attrelid, a.attnum)");
1966  attdescr_col = cols++;
1967  }
1968  }
1969 
1970  appendPQExpBufferStr(&buf, "\nFROM pg_catalog.pg_attribute a");
1971  appendPQExpBuffer(&buf, "\nWHERE a.attrelid = '%s' AND a.attnum > 0 AND NOT a.attisdropped", oid);
1972  appendPQExpBufferStr(&buf, "\nORDER BY a.attnum;");
1973 
1974  res = PSQLexec(buf.data);
1975  if (!res)
1976  goto error_return;
1977  numrows = PQntuples(res);
1978 
1979  /* Make title */
1980  switch (tableinfo.relkind)
1981  {
1982  case RELKIND_RELATION:
1983  if (tableinfo.relpersistence == RELPERSISTENCE_UNLOGGED)
1984  printfPQExpBuffer(&title, _("Unlogged table \"%s.%s\""),
1985  schemaname, relationname);
1986  else
1987  printfPQExpBuffer(&title, _("Table \"%s.%s\""),
1988  schemaname, relationname);
1989  break;
1990  case RELKIND_VIEW:
1991  printfPQExpBuffer(&title, _("View \"%s.%s\""),
1992  schemaname, relationname);
1993  break;
1994  case RELKIND_MATVIEW:
1995  printfPQExpBuffer(&title, _("Materialized view \"%s.%s\""),
1996  schemaname, relationname);
1997  break;
1998  case RELKIND_INDEX:
1999  if (tableinfo.relpersistence == RELPERSISTENCE_UNLOGGED)
2000  printfPQExpBuffer(&title, _("Unlogged index \"%s.%s\""),
2001  schemaname, relationname);
2002  else
2003  printfPQExpBuffer(&title, _("Index \"%s.%s\""),
2004  schemaname, relationname);
2005  break;
2006  case RELKIND_PARTITIONED_INDEX:
2007  if (tableinfo.relpersistence == RELPERSISTENCE_UNLOGGED)
2008  printfPQExpBuffer(&title, _("Unlogged partitioned index \"%s.%s\""),
2009  schemaname, relationname);
2010  else
2011  printfPQExpBuffer(&title, _("Partitioned index \"%s.%s\""),
2012  schemaname, relationname);
2013  break;
2014  case RELKIND_TOASTVALUE:
2015  printfPQExpBuffer(&title, _("TOAST table \"%s.%s\""),
2016  schemaname, relationname);
2017  break;
2018  case RELKIND_COMPOSITE_TYPE:
2019  printfPQExpBuffer(&title, _("Composite type \"%s.%s\""),
2020  schemaname, relationname);
2021  break;
2022  case RELKIND_FOREIGN_TABLE:
2023  printfPQExpBuffer(&title, _("Foreign table \"%s.%s\""),
2024  schemaname, relationname);
2025  break;
2026  case RELKIND_PARTITIONED_TABLE:
2027  if (tableinfo.relpersistence == RELPERSISTENCE_UNLOGGED)
2028  printfPQExpBuffer(&title, _("Unlogged partitioned table \"%s.%s\""),
2029  schemaname, relationname);
2030  else
2031  printfPQExpBuffer(&title, _("Partitioned table \"%s.%s\""),
2032  schemaname, relationname);
2033  break;
2034  default:
2035  /* untranslated unknown relkind */
2036  printfPQExpBuffer(&title, "?%c? \"%s.%s\"",
2037  tableinfo.relkind, schemaname, relationname);
2038  break;
2039  }
2040 
2041  /* Fill headers[] with the names of the columns we will output */
2042  cols = 0;
2043  headers[cols++] = gettext_noop("Column");
2044  headers[cols++] = gettext_noop("Type");
2045  if (show_column_details)
2046  {
2047  headers[cols++] = gettext_noop("Collation");
2048  headers[cols++] = gettext_noop("Nullable");
2049  headers[cols++] = gettext_noop("Default");
2050  }
2051  if (isindexkey_col >= 0)
2052  headers[cols++] = gettext_noop("Key?");
2053  if (indexdef_col >= 0)
2054  headers[cols++] = gettext_noop("Definition");
2055  if (fdwopts_col >= 0)
2056  headers[cols++] = gettext_noop("FDW options");
2057  if (attstorage_col >= 0)
2058  headers[cols++] = gettext_noop("Storage");
2059  if (attcompression_col >= 0)
2060  headers[cols++] = gettext_noop("Compression");
2061  if (attstattarget_col >= 0)
2062  headers[cols++] = gettext_noop("Stats target");
2063  if (attdescr_col >= 0)
2064  headers[cols++] = gettext_noop("Description");
2065 
2066  Assert(cols <= lengthof(headers));
2067 
2068  printTableInit(&cont, &myopt, title.data, cols, numrows);
2069  printTableInitialized = true;
2070 
2071  for (i = 0; i < cols; i++)
2072  printTableAddHeader(&cont, headers[i], true, 'l');
2073 
2074  /* Generate table cells to be printed */
2075  for (i = 0; i < numrows; i++)
2076  {
2077  /* Column */
2078  printTableAddCell(&cont, PQgetvalue(res, i, attname_col), false, false);
2079 
2080  /* Type */
2081  printTableAddCell(&cont, PQgetvalue(res, i, atttype_col), false, false);
2082 
2083  /* Collation, Nullable, Default */
2084  if (show_column_details)
2085  {
2086  char *identity;
2087  char *generated;
2088  char *default_str;
2089  bool mustfree = false;
2090 
2091  printTableAddCell(&cont, PQgetvalue(res, i, attcoll_col), false, false);
2092 
2093  printTableAddCell(&cont,
2094  strcmp(PQgetvalue(res, i, attnotnull_col), "t") == 0 ? "not null" : "",
2095  false, false);
2096 
2097  identity = PQgetvalue(res, i, attidentity_col);
2098  generated = PQgetvalue(res, i, attgenerated_col);
2099 
2100  if (identity[0] == ATTRIBUTE_IDENTITY_ALWAYS)
2101  default_str = "generated always as identity";
2102  else if (identity[0] == ATTRIBUTE_IDENTITY_BY_DEFAULT)
2103  default_str = "generated by default as identity";
2104  else if (generated[0] == ATTRIBUTE_GENERATED_STORED)
2105  {
2106  default_str = psprintf("generated always as (%s) stored",
2107  PQgetvalue(res, i, attrdef_col));
2108  mustfree = true;
2109  }
2110  else
2111  default_str = PQgetvalue(res, i, attrdef_col);
2112 
2113  printTableAddCell(&cont, default_str, false, mustfree);
2114  }
2115 
2116  /* Info for index columns */
2117  if (isindexkey_col >= 0)
2118  printTableAddCell(&cont, PQgetvalue(res, i, isindexkey_col), true, false);
2119  if (indexdef_col >= 0)
2120  printTableAddCell(&cont, PQgetvalue(res, i, indexdef_col), false, false);
2121 
2122  /* FDW options for foreign table columns */
2123  if (fdwopts_col >= 0)
2124  printTableAddCell(&cont, PQgetvalue(res, i, fdwopts_col), false, false);
2125 
2126  /* Storage mode, if relevant */
2127  if (attstorage_col >= 0)
2128  {
2129  char *storage = PQgetvalue(res, i, attstorage_col);
2130 
2131  /* these strings are literal in our syntax, so not translated. */
2132  printTableAddCell(&cont, (storage[0] == TYPSTORAGE_PLAIN ? "plain" :
2133  (storage[0] == TYPSTORAGE_MAIN ? "main" :
2134  (storage[0] == TYPSTORAGE_EXTENDED ? "extended" :
2135  (storage[0] == TYPSTORAGE_EXTERNAL ? "external" :
2136  "???")))),
2137  false, false);
2138  }
2139 
2140  /* Column compression, if relevant */
2141  if (attcompression_col >= 0)
2142  {
2143  char *compression = PQgetvalue(res, i, attcompression_col);
2144 
2145  /* these strings are literal in our syntax, so not translated. */
2146  printTableAddCell(&cont, (compression[0] == 'p' ? "pglz" :
2147  (compression[0] == 'l' ? "lz4" :
2148  (compression[0] == '\0' ? "" :
2149  "???"))),
2150  false, false);
2151  }
2152 
2153  /* Statistics target, if the relkind supports this feature */
2154  if (attstattarget_col >= 0)
2155  printTableAddCell(&cont, PQgetvalue(res, i, attstattarget_col),
2156  false, false);
2157 
2158  /* Column comments, if the relkind supports this feature */
2159  if (attdescr_col >= 0)
2160  printTableAddCell(&cont, PQgetvalue(res, i, attdescr_col),
2161  false, false);
2162  }
2163 
2164  /* Make footers */
2165 
2166  if (tableinfo.ispartition)
2167  {
2168  /* Footer information for a partition child table */
2169  PGresult *result;
2170 
2171  printfPQExpBuffer(&buf,
2172  "SELECT inhparent::pg_catalog.regclass,\n"
2173  " pg_catalog.pg_get_expr(c.relpartbound, c.oid),\n ");
2174 
2175  appendPQExpBufferStr(&buf,
2176  pset.sversion >= 140000 ? "inhdetachpending" :
2177  "false as inhdetachpending");
2178 
2179  /* If verbose, also request the partition constraint definition */
2180  if (verbose)
2181  appendPQExpBufferStr(&buf,
2182  ",\n pg_catalog.pg_get_partition_constraintdef(c.oid)");
2183  appendPQExpBuffer(&buf,
2184  "\nFROM pg_catalog.pg_class c"
2185  " JOIN pg_catalog.pg_inherits i"
2186  " ON c.oid = inhrelid"
2187  "\nWHERE c.oid = '%s';", oid);
2188  result = PSQLexec(buf.data);
2189  if (!result)
2190  goto error_return;
2191 
2192  if (PQntuples(result) > 0)
2193  {
2194  char *parent_name = PQgetvalue(result, 0, 0);
2195  char *partdef = PQgetvalue(result, 0, 1);
2196  char *detached = PQgetvalue(result, 0, 2);
2197 
2198  printfPQExpBuffer(&tmpbuf, _("Partition of: %s %s%s"), parent_name,
2199  partdef,
2200  strcmp(detached, "t") == 0 ? " DETACH PENDING" : "");
2201  printTableAddFooter(&cont, tmpbuf.data);
2202 
2203  if (verbose)
2204  {
2205  char *partconstraintdef = NULL;
2206 
2207  if (!PQgetisnull(result, 0, 3))
2208  partconstraintdef = PQgetvalue(result, 0, 3);
2209  /* If there isn't any constraint, show that explicitly */
2210  if (partconstraintdef == NULL || partconstraintdef[0] == '\0')
2211  printfPQExpBuffer(&tmpbuf, _("No partition constraint"));
2212  else
2213  printfPQExpBuffer(&tmpbuf, _("Partition constraint: %s"),
2214  partconstraintdef);
2215  printTableAddFooter(&cont, tmpbuf.data);
2216  }
2217  }
2218  PQclear(result);
2219  }
2220 
2221  if (tableinfo.relkind == RELKIND_PARTITIONED_TABLE)
2222  {
2223  /* Footer information for a partitioned table (partitioning parent) */
2224  PGresult *result;
2225 
2226  printfPQExpBuffer(&buf,
2227  "SELECT pg_catalog.pg_get_partkeydef('%s'::pg_catalog.oid);",
2228  oid);
2229  result = PSQLexec(buf.data);
2230  if (!result)
2231  goto error_return;
2232 
2233  if (PQntuples(result) == 1)
2234  {
2235  char *partkeydef = PQgetvalue(result, 0, 0);
2236 
2237  printfPQExpBuffer(&tmpbuf, _("Partition key: %s"), partkeydef);
2238  printTableAddFooter(&cont, tmpbuf.data);
2239  }
2240  PQclear(result);
2241  }
2242 
2243  if (tableinfo.relkind == RELKIND_TOASTVALUE)
2244  {
2245  /* For a TOAST table, print name of owning table */
2246  PGresult *result;
2247 
2248  printfPQExpBuffer(&buf,
2249  "SELECT n.nspname, c.relname\n"
2250  "FROM pg_catalog.pg_class c"
2251  " JOIN pg_catalog.pg_namespace n"
2252  " ON n.oid = c.relnamespace\n"
2253  "WHERE reltoastrelid = '%s';", oid);
2254  result = PSQLexec(buf.data);
2255  if (!result)
2256  goto error_return;
2257 
2258  if (PQntuples(result) == 1)
2259  {
2260  char *schemaname = PQgetvalue(result, 0, 0);
2261  char *relname = PQgetvalue(result, 0, 1);
2262 
2263  printfPQExpBuffer(&tmpbuf, _("Owning table: \"%s.%s\""),
2264  schemaname, relname);
2265  printTableAddFooter(&cont, tmpbuf.data);
2266  }
2267  PQclear(result);
2268  }
2269 
2270  if (tableinfo.relkind == RELKIND_INDEX ||
2271  tableinfo.relkind == RELKIND_PARTITIONED_INDEX)
2272  {
2273  /* Footer information about an index */
2274  PGresult *result;
2275 
2276  printfPQExpBuffer(&buf,
2277  "SELECT i.indisunique, i.indisprimary, i.indisclustered, "
2278  "i.indisvalid,\n"
2279  " (NOT i.indimmediate) AND "
2280  "EXISTS (SELECT 1 FROM pg_catalog.pg_constraint "
2281  "WHERE conrelid = i.indrelid AND "
2282  "conindid = i.indexrelid AND "
2283  "contype IN (" CppAsString2(CONSTRAINT_PRIMARY) ","
2284  CppAsString2(CONSTRAINT_UNIQUE) ","
2285  CppAsString2(CONSTRAINT_EXCLUSION) ") AND "
2286  "condeferrable) AS condeferrable,\n"
2287  " (NOT i.indimmediate) AND "
2288  "EXISTS (SELECT 1 FROM pg_catalog.pg_constraint "
2289  "WHERE conrelid = i.indrelid AND "
2290  "conindid = i.indexrelid AND "
2291  "contype IN (" CppAsString2(CONSTRAINT_PRIMARY) ","
2292  CppAsString2(CONSTRAINT_UNIQUE) ","
2293  CppAsString2(CONSTRAINT_EXCLUSION) ") AND "
2294  "condeferred) AS condeferred,\n");
2295 
2296  if (pset.sversion >= 90400)
2297  appendPQExpBufferStr(&buf, "i.indisreplident,\n");
2298  else
2299  appendPQExpBufferStr(&buf, "false AS indisreplident,\n");
2300 
2301  if (pset.sversion >= 150000)
2302  appendPQExpBufferStr(&buf, "i.indnullsnotdistinct,\n");
2303  else
2304  appendPQExpBufferStr(&buf, "false AS indnullsnotdistinct,\n");
2305 
2306  appendPQExpBuffer(&buf, " a.amname, c2.relname, "
2307  "pg_catalog.pg_get_expr(i.indpred, i.indrelid, true)\n"
2308  "FROM pg_catalog.pg_index i, pg_catalog.pg_class c, pg_catalog.pg_class c2, pg_catalog.pg_am a\n"
2309  "WHERE i.indexrelid = c.oid AND c.oid = '%s' AND c.relam = a.oid\n"
2310  "AND i.indrelid = c2.oid;",
2311  oid);
2312 
2313  result = PSQLexec(buf.data);
2314  if (!result)
2315  goto error_return;
2316  else if (PQntuples(result) != 1)
2317  {
2318  PQclear(result);
2319  goto error_return;
2320  }
2321  else
2322  {
2323  char *indisunique = PQgetvalue(result, 0, 0);
2324  char *indisprimary = PQgetvalue(result, 0, 1);
2325  char *indisclustered = PQgetvalue(result, 0, 2);
2326  char *indisvalid = PQgetvalue(result, 0, 3);
2327  char *deferrable = PQgetvalue(result, 0, 4);
2328  char *deferred = PQgetvalue(result, 0, 5);
2329  char *indisreplident = PQgetvalue(result, 0, 6);
2330  char *indnullsnotdistinct = PQgetvalue(result, 0, 7);
2331  char *indamname = PQgetvalue(result, 0, 8);
2332  char *indtable = PQgetvalue(result, 0, 9);
2333  char *indpred = PQgetvalue(result, 0, 10);
2334 
2335  if (strcmp(indisprimary, "t") == 0)
2336  printfPQExpBuffer(&tmpbuf, _("primary key, "));
2337  else if (strcmp(indisunique, "t") == 0)
2338  {
2339  printfPQExpBuffer(&tmpbuf, _("unique"));
2340  if (strcmp(indnullsnotdistinct, "t") == 0)
2341  appendPQExpBufferStr(&tmpbuf, _(" nulls not distinct"));
2342  appendPQExpBufferStr(&tmpbuf, _(", "));
2343  }
2344  else
2345  resetPQExpBuffer(&tmpbuf);
2346  appendPQExpBuffer(&tmpbuf, "%s, ", indamname);
2347 
2348  /* we assume here that index and table are in same schema */
2349  appendPQExpBuffer(&tmpbuf, _("for table \"%s.%s\""),
2350  schemaname, indtable);
2351 
2352  if (strlen(indpred))
2353  appendPQExpBuffer(&tmpbuf, _(", predicate (%s)"), indpred);
2354 
2355  if (strcmp(indisclustered, "t") == 0)
2356  appendPQExpBufferStr(&tmpbuf, _(", clustered"));
2357 
2358  if (strcmp(indisvalid, "t") != 0)
2359  appendPQExpBufferStr(&tmpbuf, _(", invalid"));
2360 
2361  if (strcmp(deferrable, "t") == 0)
2362  appendPQExpBufferStr(&tmpbuf, _(", deferrable"));
2363 
2364  if (strcmp(deferred, "t") == 0)
2365  appendPQExpBufferStr(&tmpbuf, _(", initially deferred"));
2366 
2367  if (strcmp(indisreplident, "t") == 0)
2368  appendPQExpBufferStr(&tmpbuf, _(", replica identity"));
2369 
2370  printTableAddFooter(&cont, tmpbuf.data);
2371 
2372  /*
2373  * If it's a partitioned index, we'll print the tablespace below
2374  */
2375  if (tableinfo.relkind == RELKIND_INDEX)
2376  add_tablespace_footer(&cont, tableinfo.relkind,
2377  tableinfo.tablespace, true);
2378  }
2379 
2380  PQclear(result);
2381  }
2382  /* If you add relkinds here, see also "Finish printing..." stanza below */
2383  else if (tableinfo.relkind == RELKIND_RELATION ||
2384  tableinfo.relkind == RELKIND_MATVIEW ||
2385  tableinfo.relkind == RELKIND_FOREIGN_TABLE ||
2386  tableinfo.relkind == RELKIND_PARTITIONED_TABLE ||
2387  tableinfo.relkind == RELKIND_PARTITIONED_INDEX ||
2388  tableinfo.relkind == RELKIND_TOASTVALUE)
2389  {
2390  /* Footer information about a table */
2391  PGresult *result = NULL;
2392  int tuples = 0;
2393 
2394  /* print indexes */
2395  if (tableinfo.hasindex)
2396  {
2397  printfPQExpBuffer(&buf,
2398  "SELECT c2.relname, i.indisprimary, i.indisunique, "
2399  "i.indisclustered, i.indisvalid, "
2400  "pg_catalog.pg_get_indexdef(i.indexrelid, 0, true),\n "
2401  "pg_catalog.pg_get_constraintdef(con.oid, true), "
2402  "contype, condeferrable, condeferred");
2403  if (pset.sversion >= 90400)
2404  appendPQExpBufferStr(&buf, ", i.indisreplident");
2405  else
2406  appendPQExpBufferStr(&buf, ", false AS indisreplident");
2407  appendPQExpBufferStr(&buf, ", c2.reltablespace");
2408  if (pset.sversion >= 180000)
2409  appendPQExpBufferStr(&buf, ", con.conperiod");
2410  else
2411  appendPQExpBufferStr(&buf, ", false AS conperiod");
2412  appendPQExpBuffer(&buf,
2413  "\nFROM pg_catalog.pg_class c, pg_catalog.pg_class c2, pg_catalog.pg_index i\n"
2414  " LEFT JOIN pg_catalog.pg_constraint con ON (conrelid = i.indrelid AND conindid = i.indexrelid AND contype IN ("
2415  CppAsString2(CONSTRAINT_PRIMARY) ","
2416  CppAsString2(CONSTRAINT_UNIQUE) ","
2417  CppAsString2(CONSTRAINT_EXCLUSION) "))\n"
2418  "WHERE c.oid = '%s' AND c.oid = i.indrelid AND i.indexrelid = c2.oid\n"
2419  "ORDER BY i.indisprimary DESC, c2.relname;",
2420  oid);
2421  result = PSQLexec(buf.data);
2422  if (!result)
2423  goto error_return;
2424  else
2425  tuples = PQntuples(result);
2426 
2427  if (tuples > 0)
2428  {
2429  printTableAddFooter(&cont, _("Indexes:"));
2430  for (i = 0; i < tuples; i++)
2431  {
2432  /* untranslated index name */
2433  printfPQExpBuffer(&buf, " \"%s\"",
2434  PQgetvalue(result, i, 0));
2435 
2436  /*
2437  * If exclusion constraint or PK/UNIQUE constraint WITHOUT
2438  * OVERLAPS, print the constraintdef
2439  */
2440  if (strcmp(PQgetvalue(result, i, 7), "x") == 0 ||
2441  strcmp(PQgetvalue(result, i, 12), "t") == 0)
2442  {
2443  appendPQExpBuffer(&buf, " %s",
2444  PQgetvalue(result, i, 6));
2445  }
2446  else
2447  {
2448  const char *indexdef;
2449  const char *usingpos;
2450 
2451  /* Label as primary key or unique (but not both) */
2452  if (strcmp(PQgetvalue(result, i, 1), "t") == 0)
2453  appendPQExpBufferStr(&buf, " PRIMARY KEY,");
2454  else if (strcmp(PQgetvalue(result, i, 2), "t") == 0)
2455  {
2456  if (strcmp(PQgetvalue(result, i, 7), "u") == 0)
2457  appendPQExpBufferStr(&buf, " UNIQUE CONSTRAINT,");
2458  else
2459  appendPQExpBufferStr(&buf, " UNIQUE,");
2460  }
2461 
2462  /* Everything after "USING" is echoed verbatim */
2463  indexdef = PQgetvalue(result, i, 5);
2464  usingpos = strstr(indexdef, " USING ");
2465  if (usingpos)
2466  indexdef = usingpos + 7;
2467  appendPQExpBuffer(&buf, " %s", indexdef);
2468 
2469  /* Need these for deferrable PK/UNIQUE indexes */
2470  if (strcmp(PQgetvalue(result, i, 8), "t") == 0)
2471  appendPQExpBufferStr(&buf, " DEFERRABLE");
2472 
2473  if (strcmp(PQgetvalue(result, i, 9), "t") == 0)
2474  appendPQExpBufferStr(&buf, " INITIALLY DEFERRED");
2475  }
2476 
2477  /* Add these for all cases */
2478  if (strcmp(PQgetvalue(result, i, 3), "t") == 0)
2479  appendPQExpBufferStr(&buf, " CLUSTER");
2480 
2481  if (strcmp(PQgetvalue(result, i, 4), "t") != 0)
2482  appendPQExpBufferStr(&buf, " INVALID");
2483 
2484  if (strcmp(PQgetvalue(result, i, 10), "t") == 0)
2485  appendPQExpBufferStr(&buf, " REPLICA IDENTITY");
2486 
2487  printTableAddFooter(&cont, buf.data);
2488 
2489  /* Print tablespace of the index on the same line */
2490  add_tablespace_footer(&cont, RELKIND_INDEX,
2491  atooid(PQgetvalue(result, i, 11)),
2492  false);
2493  }
2494  }
2495  PQclear(result);
2496  }
2497 
2498  /* print table (and column) check constraints */
2499  if (tableinfo.checks)
2500  {
2501  printfPQExpBuffer(&buf,
2502  "SELECT r.conname, "
2503  "pg_catalog.pg_get_constraintdef(r.oid, true)\n"
2504  "FROM pg_catalog.pg_constraint r\n"
2505  "WHERE r.conrelid = '%s' "
2506  "AND r.contype = " CppAsString2(CONSTRAINT_CHECK) "\n"
2507  "ORDER BY 1;",
2508  oid);
2509  result = PSQLexec(buf.data);
2510  if (!result)
2511  goto error_return;
2512  else
2513  tuples = PQntuples(result);
2514 
2515  if (tuples > 0)
2516  {
2517  printTableAddFooter(&cont, _("Check constraints:"));
2518  for (i = 0; i < tuples; i++)
2519  {
2520  /* untranslated constraint name and def */
2521  printfPQExpBuffer(&buf, " \"%s\" %s",
2522  PQgetvalue(result, i, 0),
2523  PQgetvalue(result, i, 1));
2524 
2525  printTableAddFooter(&cont, buf.data);
2526  }
2527  }
2528  PQclear(result);
2529  }
2530 
2531  /*
2532  * Print foreign-key constraints (there are none if no triggers,
2533  * except if the table is partitioned, in which case the triggers
2534  * appear in the partitions)
2535  */
2536  if (tableinfo.hastriggers ||
2537  tableinfo.relkind == RELKIND_PARTITIONED_TABLE)
2538  {
2539  if (pset.sversion >= 120000 &&
2540  (tableinfo.ispartition || tableinfo.relkind == RELKIND_PARTITIONED_TABLE))
2541  {
2542  /*
2543  * Put the constraints defined in this table first, followed
2544  * by the constraints defined in ancestor partitioned tables.
2545  */
2546  printfPQExpBuffer(&buf,
2547  "SELECT conrelid = '%s'::pg_catalog.regclass AS sametable,\n"
2548  " conname,\n"
2549  " pg_catalog.pg_get_constraintdef(oid, true) AS condef,\n"
2550  " conrelid::pg_catalog.regclass AS ontable\n"
2551  " FROM pg_catalog.pg_constraint,\n"
2552  " pg_catalog.pg_partition_ancestors('%s')\n"
2553  " WHERE conrelid = relid AND contype = " CppAsString2(CONSTRAINT_FOREIGN) " AND conparentid = 0\n"
2554  "ORDER BY sametable DESC, conname;",
2555  oid, oid);
2556  }
2557  else
2558  {
2559  printfPQExpBuffer(&buf,
2560  "SELECT true as sametable, conname,\n"
2561  " pg_catalog.pg_get_constraintdef(r.oid, true) as condef,\n"
2562  " conrelid::pg_catalog.regclass AS ontable\n"
2563  "FROM pg_catalog.pg_constraint r\n"
2564  "WHERE r.conrelid = '%s' AND r.contype = " CppAsString2(CONSTRAINT_FOREIGN) "\n",
2565  oid);
2566 
2567  if (pset.sversion >= 120000)
2568  appendPQExpBufferStr(&buf, " AND conparentid = 0\n");
2569  appendPQExpBufferStr(&buf, "ORDER BY conname");
2570  }
2571 
2572  result = PSQLexec(buf.data);
2573  if (!result)
2574  goto error_return;
2575  else
2576  tuples = PQntuples(result);
2577 
2578  if (tuples > 0)
2579  {
2580  int i_sametable = PQfnumber(result, "sametable"),
2581  i_conname = PQfnumber(result, "conname"),
2582  i_condef = PQfnumber(result, "condef"),
2583  i_ontable = PQfnumber(result, "ontable");
2584 
2585  printTableAddFooter(&cont, _("Foreign-key constraints:"));
2586  for (i = 0; i < tuples; i++)
2587  {
2588  /*
2589  * Print untranslated constraint name and definition. Use
2590  * a "TABLE tab" prefix when the constraint is defined in
2591  * a parent partitioned table.
2592  */
2593  if (strcmp(PQgetvalue(result, i, i_sametable), "f") == 0)
2594  printfPQExpBuffer(&buf, " TABLE \"%s\" CONSTRAINT \"%s\" %s",
2595  PQgetvalue(result, i, i_ontable),
2596  PQgetvalue(result, i, i_conname),
2597  PQgetvalue(result, i, i_condef));
2598  else
2599  printfPQExpBuffer(&buf, " \"%s\" %s",
2600  PQgetvalue(result, i, i_conname),
2601  PQgetvalue(result, i, i_condef));
2602 
2603  printTableAddFooter(&cont, buf.data);
2604  }
2605  }
2606  PQclear(result);
2607  }
2608 
2609  /* print incoming foreign-key references */
2610  if (tableinfo.hastriggers ||
2611  tableinfo.relkind == RELKIND_PARTITIONED_TABLE)
2612  {
2613  if (pset.sversion >= 120000)
2614  {
2615  printfPQExpBuffer(&buf,
2616  "SELECT conname, conrelid::pg_catalog.regclass AS ontable,\n"
2617  " pg_catalog.pg_get_constraintdef(oid, true) AS condef\n"
2618  " FROM pg_catalog.pg_constraint c\n"
2619  " WHERE confrelid IN (SELECT pg_catalog.pg_partition_ancestors('%s')\n"
2620  " UNION ALL VALUES ('%s'::pg_catalog.regclass))\n"
2621  " AND contype = " CppAsString2(CONSTRAINT_FOREIGN) " AND conparentid = 0\n"
2622  "ORDER BY conname;",
2623  oid, oid);
2624  }
2625  else
2626  {
2627  printfPQExpBuffer(&buf,
2628  "SELECT conname, conrelid::pg_catalog.regclass AS ontable,\n"
2629  " pg_catalog.pg_get_constraintdef(oid, true) AS condef\n"
2630  " FROM pg_catalog.pg_constraint\n"
2631  " WHERE confrelid = %s AND contype = " CppAsString2(CONSTRAINT_FOREIGN) "\n"
2632  "ORDER BY conname;",
2633  oid);
2634  }
2635 
2636  result = PSQLexec(buf.data);
2637  if (!result)
2638  goto error_return;
2639  else
2640  tuples = PQntuples(result);
2641 
2642  if (tuples > 0)
2643  {
2644  int i_conname = PQfnumber(result, "conname"),
2645  i_ontable = PQfnumber(result, "ontable"),
2646  i_condef = PQfnumber(result, "condef");
2647 
2648  printTableAddFooter(&cont, _("Referenced by:"));
2649  for (i = 0; i < tuples; i++)
2650  {
2651  printfPQExpBuffer(&buf, " TABLE \"%s\" CONSTRAINT \"%s\" %s",
2652  PQgetvalue(result, i, i_ontable),
2653  PQgetvalue(result, i, i_conname),
2654  PQgetvalue(result, i, i_condef));
2655 
2656  printTableAddFooter(&cont, buf.data);
2657  }
2658  }
2659  PQclear(result);
2660  }
2661 
2662  /* print any row-level policies */
2663  if (pset.sversion >= 90500)
2664  {
2665  printfPQExpBuffer(&buf, "SELECT pol.polname,");
2666  if (pset.sversion >= 100000)
2667  appendPQExpBufferStr(&buf,
2668  " pol.polpermissive,\n");
2669  else
2670  appendPQExpBufferStr(&buf,
2671  " 't' as polpermissive,\n");
2672  appendPQExpBuffer(&buf,
2673  " 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"
2674  " pg_catalog.pg_get_expr(pol.polqual, pol.polrelid),\n"
2675  " pg_catalog.pg_get_expr(pol.polwithcheck, pol.polrelid),\n"
2676  " CASE pol.polcmd\n"
2677  " WHEN 'r' THEN 'SELECT'\n"
2678  " WHEN 'a' THEN 'INSERT'\n"
2679  " WHEN 'w' THEN 'UPDATE'\n"
2680  " WHEN 'd' THEN 'DELETE'\n"
2681  " END AS cmd\n"
2682  "FROM pg_catalog.pg_policy pol\n"
2683  "WHERE pol.polrelid = '%s' ORDER BY 1;",
2684  oid);
2685 
2686  result = PSQLexec(buf.data);
2687  if (!result)
2688  goto error_return;
2689  else
2690  tuples = PQntuples(result);
2691 
2692  /*
2693  * Handle cases where RLS is enabled and there are policies, or
2694  * there aren't policies, or RLS isn't enabled but there are
2695  * policies
2696  */
2697  if (tableinfo.rowsecurity && !tableinfo.forcerowsecurity && tuples > 0)
2698  printTableAddFooter(&cont, _("Policies:"));
2699 
2700  if (tableinfo.rowsecurity && tableinfo.forcerowsecurity && tuples > 0)
2701  printTableAddFooter(&cont, _("Policies (forced row security enabled):"));
2702 
2703  if (tableinfo.rowsecurity && !tableinfo.forcerowsecurity && tuples == 0)
2704  printTableAddFooter(&cont, _("Policies (row security enabled): (none)"));
2705 
2706  if (tableinfo.rowsecurity && tableinfo.forcerowsecurity && tuples == 0)
2707  printTableAddFooter(&cont, _("Policies (forced row security enabled): (none)"));
2708 
2709  if (!tableinfo.rowsecurity && tuples > 0)
2710  printTableAddFooter(&cont, _("Policies (row security disabled):"));
2711 
2712  /* Might be an empty set - that's ok */
2713  for (i = 0; i < tuples; i++)
2714  {
2715  printfPQExpBuffer(&buf, " POLICY \"%s\"",
2716  PQgetvalue(result, i, 0));
2717 
2718  if (*(PQgetvalue(result, i, 1)) == 'f')
2719  appendPQExpBufferStr(&buf, " AS RESTRICTIVE");
2720 
2721  if (!PQgetisnull(result, i, 5))
2722  appendPQExpBuffer(&buf, " FOR %s",
2723  PQgetvalue(result, i, 5));
2724 
2725  if (!PQgetisnull(result, i, 2))
2726  {
2727  appendPQExpBuffer(&buf, "\n TO %s",
2728  PQgetvalue(result, i, 2));
2729  }
2730 
2731  if (!PQgetisnull(result, i, 3))
2732  appendPQExpBuffer(&buf, "\n USING (%s)",
2733  PQgetvalue(result, i, 3));
2734 
2735  if (!PQgetisnull(result, i, 4))
2736  appendPQExpBuffer(&buf, "\n WITH CHECK (%s)",
2737  PQgetvalue(result, i, 4));
2738 
2739  printTableAddFooter(&cont, buf.data);
2740  }
2741  PQclear(result);
2742  }
2743 
2744  /* print any extended statistics */
2745  if (pset.sversion >= 140000)
2746  {
2747  printfPQExpBuffer(&buf,
2748  "SELECT oid, "
2749  "stxrelid::pg_catalog.regclass, "
2750  "stxnamespace::pg_catalog.regnamespace::pg_catalog.text AS nsp, "
2751  "stxname,\n"
2752  "pg_catalog.pg_get_statisticsobjdef_columns(oid) AS columns,\n"
2753  " " CppAsString2(STATS_EXT_NDISTINCT) " = any(stxkind) AS ndist_enabled,\n"
2754  " " CppAsString2(STATS_EXT_DEPENDENCIES) " = any(stxkind) AS deps_enabled,\n"
2755  " " CppAsString2(STATS_EXT_MCV) " = any(stxkind) AS mcv_enabled,\n"
2756  "stxstattarget\n"
2757  "FROM pg_catalog.pg_statistic_ext\n"
2758  "WHERE stxrelid = '%s'\n"
2759  "ORDER BY nsp, stxname;",
2760  oid);
2761 
2762  result = PSQLexec(buf.data);
2763  if (!result)
2764  goto error_return;
2765  else
2766  tuples = PQntuples(result);
2767 
2768  if (tuples > 0)
2769  {
2770  printTableAddFooter(&cont, _("Statistics objects:"));
2771 
2772  for (i = 0; i < tuples; i++)
2773  {
2774  bool gotone = false;
2775  bool has_ndistinct;
2776  bool has_dependencies;
2777  bool has_mcv;
2778  bool has_all;
2779  bool has_some;
2780 
2781  has_ndistinct = (strcmp(PQgetvalue(result, i, 5), "t") == 0);
2782  has_dependencies = (strcmp(PQgetvalue(result, i, 6), "t") == 0);
2783  has_mcv = (strcmp(PQgetvalue(result, i, 7), "t") == 0);
2784 
2785  printfPQExpBuffer(&buf, " ");
2786 
2787  /* statistics object name (qualified with namespace) */
2788  appendPQExpBuffer(&buf, "\"%s.%s\"",
2789  PQgetvalue(result, i, 2),
2790  PQgetvalue(result, i, 3));
2791 
2792  /*
2793  * When printing kinds we ignore expression statistics,
2794  * which are used only internally and can't be specified
2795  * by user. We don't print the kinds when none are
2796  * specified (in which case it has to be statistics on a
2797  * single expr) or when all are specified (in which case
2798  * we assume it's expanded by CREATE STATISTICS).
2799  */
2800  has_all = (has_ndistinct && has_dependencies && has_mcv);
2801  has_some = (has_ndistinct || has_dependencies || has_mcv);
2802 
2803  if (has_some && !has_all)
2804  {
2805  appendPQExpBufferStr(&buf, " (");
2806 
2807  /* options */
2808  if (has_ndistinct)
2809  {
2810  appendPQExpBufferStr(&buf, "ndistinct");
2811  gotone = true;
2812  }
2813 
2814  if (has_dependencies)
2815  {
2816  appendPQExpBuffer(&buf, "%sdependencies", gotone ? ", " : "");
2817  gotone = true;
2818  }
2819 
2820  if (has_mcv)
2821  {
2822  appendPQExpBuffer(&buf, "%smcv", gotone ? ", " : "");
2823  }
2824 
2825  appendPQExpBufferChar(&buf, ')');
2826  }
2827 
2828  appendPQExpBuffer(&buf, " ON %s FROM %s",
2829  PQgetvalue(result, i, 4),
2830  PQgetvalue(result, i, 1));
2831 
2832  /* Show the stats target if it's not default */
2833  if (!PQgetisnull(result, i, 8) &&
2834  strcmp(PQgetvalue(result, i, 8), "-1") != 0)
2835  appendPQExpBuffer(&buf, "; STATISTICS %s",
2836  PQgetvalue(result, i, 8));
2837 
2838  printTableAddFooter(&cont, buf.data);
2839  }
2840  }
2841  PQclear(result);
2842  }
2843  else if (pset.sversion >= 100000)
2844  {
2845  printfPQExpBuffer(&buf,
2846  "SELECT oid, "
2847  "stxrelid::pg_catalog.regclass, "
2848  "stxnamespace::pg_catalog.regnamespace AS nsp, "
2849  "stxname,\n"
2850  " (SELECT pg_catalog.string_agg(pg_catalog.quote_ident(attname),', ')\n"
2851  " FROM pg_catalog.unnest(stxkeys) s(attnum)\n"
2852  " JOIN pg_catalog.pg_attribute a ON (stxrelid = a.attrelid AND\n"
2853  " a.attnum = s.attnum AND NOT attisdropped)) AS columns,\n"
2854  " " CppAsString2(STATS_EXT_NDISTINCT) " = any(stxkind) AS ndist_enabled,\n"
2855  " " CppAsString2(STATS_EXT_DEPENDENCIES) " = any(stxkind) AS deps_enabled,\n"
2856  " " CppAsString2(STATS_EXT_MCV) " = any(stxkind) AS mcv_enabled,\n");
2857 
2858  if (pset.sversion >= 130000)
2859  appendPQExpBufferStr(&buf, " stxstattarget\n");
2860  else
2861  appendPQExpBufferStr(&buf, " -1 AS stxstattarget\n");
2862  appendPQExpBuffer(&buf, "FROM pg_catalog.pg_statistic_ext\n"
2863  "WHERE stxrelid = '%s'\n"
2864  "ORDER BY 1;",
2865  oid);
2866 
2867  result = PSQLexec(buf.data);
2868  if (!result)
2869  goto error_return;
2870  else
2871  tuples = PQntuples(result);
2872 
2873  if (tuples > 0)
2874  {
2875  printTableAddFooter(&cont, _("Statistics objects:"));
2876 
2877  for (i = 0; i < tuples; i++)
2878  {
2879  bool gotone = false;
2880 
2881  printfPQExpBuffer(&buf, " ");
2882 
2883  /* statistics object name (qualified with namespace) */
2884  appendPQExpBuffer(&buf, "\"%s.%s\" (",
2885  PQgetvalue(result, i, 2),
2886  PQgetvalue(result, i, 3));
2887 
2888  /* options */
2889  if (strcmp(PQgetvalue(result, i, 5), "t") == 0)
2890  {
2891  appendPQExpBufferStr(&buf, "ndistinct");
2892  gotone = true;
2893  }
2894 
2895  if (strcmp(PQgetvalue(result, i, 6), "t") == 0)
2896  {
2897  appendPQExpBuffer(&buf, "%sdependencies", gotone ? ", " : "");
2898  gotone = true;
2899  }
2900 
2901  if (strcmp(PQgetvalue(result, i, 7), "t") == 0)
2902  {
2903  appendPQExpBuffer(&buf, "%smcv", gotone ? ", " : "");
2904  }
2905 
2906  appendPQExpBuffer(&buf, ") ON %s FROM %s",
2907  PQgetvalue(result, i, 4),
2908  PQgetvalue(result, i, 1));
2909 
2910  /* Show the stats target if it's not default */
2911  if (strcmp(PQgetvalue(result, i, 8), "-1") != 0)
2912  appendPQExpBuffer(&buf, "; STATISTICS %s",
2913  PQgetvalue(result, i, 8));
2914 
2915  printTableAddFooter(&cont, buf.data);
2916  }
2917  }
2918  PQclear(result);
2919  }
2920 
2921  /* print rules */
2922  if (tableinfo.hasrules && tableinfo.relkind != RELKIND_MATVIEW)
2923  {
2924  printfPQExpBuffer(&buf,
2925  "SELECT r.rulename, trim(trailing ';' from pg_catalog.pg_get_ruledef(r.oid, true)), "
2926  "ev_enabled\n"
2927  "FROM pg_catalog.pg_rewrite r\n"
2928  "WHERE r.ev_class = '%s' ORDER BY 1;",
2929  oid);
2930  result = PSQLexec(buf.data);
2931  if (!result)
2932  goto error_return;
2933  else
2934  tuples = PQntuples(result);
2935 
2936  if (tuples > 0)
2937  {
2938  bool have_heading;
2939  int category;
2940 
2941  for (category = 0; category < 4; category++)
2942  {
2943  have_heading = false;
2944 
2945  for (i = 0; i < tuples; i++)
2946  {
2947  const char *ruledef;
2948  bool list_rule = false;
2949 
2950  switch (category)
2951  {
2952  case 0:
2953  if (*PQgetvalue(result, i, 2) == 'O')
2954  list_rule = true;
2955  break;
2956  case 1:
2957  if (*PQgetvalue(result, i, 2) == 'D')
2958  list_rule = true;
2959  break;
2960  case 2:
2961  if (*PQgetvalue(result, i, 2) == 'A')
2962  list_rule = true;
2963  break;
2964  case 3:
2965  if (*PQgetvalue(result, i, 2) == 'R')
2966  list_rule = true;
2967  break;
2968  }
2969  if (!list_rule)
2970  continue;
2971 
2972  if (!have_heading)
2973  {
2974  switch (category)
2975  {
2976  case 0:
2977  printfPQExpBuffer(&buf, _("Rules:"));
2978  break;
2979  case 1:
2980  printfPQExpBuffer(&buf, _("Disabled rules:"));
2981  break;
2982  case 2:
2983  printfPQExpBuffer(&buf, _("Rules firing always:"));
2984  break;
2985  case 3:
2986  printfPQExpBuffer(&buf, _("Rules firing on replica only:"));
2987  break;
2988  }
2989  printTableAddFooter(&cont, buf.data);
2990  have_heading = true;
2991  }
2992 
2993  /* Everything after "CREATE RULE" is echoed verbatim */
2994  ruledef = PQgetvalue(result, i, 1);
2995  ruledef += 12;
2996  printfPQExpBuffer(&buf, " %s", ruledef);
2997  printTableAddFooter(&cont, buf.data);
2998  }
2999  }
3000  }
3001  PQclear(result);
3002  }
3003 
3004  /* print any publications */
3005  if (pset.sversion >= 100000)
3006  {
3007  if (pset.sversion >= 150000)
3008  {
3009  printfPQExpBuffer(&buf,
3010  "SELECT pubname\n"
3011  " , NULL\n"
3012  " , NULL\n"
3013  "FROM pg_catalog.pg_publication p\n"
3014  " JOIN pg_catalog.pg_publication_namespace pn ON p.oid = pn.pnpubid\n"
3015  " JOIN pg_catalog.pg_class pc ON pc.relnamespace = pn.pnnspid\n"
3016  "WHERE pc.oid ='%s' and pg_catalog.pg_relation_is_publishable('%s')\n"
3017  "UNION\n"
3018  "SELECT pubname\n"
3019  " , pg_get_expr(pr.prqual, c.oid)\n"
3020  " , (CASE WHEN pr.prattrs IS NOT NULL THEN\n"
3021  " (SELECT string_agg(attname, ', ')\n"
3022  " FROM pg_catalog.generate_series(0, pg_catalog.array_upper(pr.prattrs::pg_catalog.int2[], 1)) s,\n"
3023  " pg_catalog.pg_attribute\n"
3024  " WHERE attrelid = pr.prrelid AND attnum = prattrs[s])\n"
3025  " ELSE NULL END) "
3026  "FROM pg_catalog.pg_publication p\n"
3027  " JOIN pg_catalog.pg_publication_rel pr ON p.oid = pr.prpubid\n"
3028  " JOIN pg_catalog.pg_class c ON c.oid = pr.prrelid\n"
3029  "WHERE pr.prrelid = '%s'\n"
3030  "UNION\n"
3031  "SELECT pubname\n"
3032  " , NULL\n"
3033  " , NULL\n"
3034  "FROM pg_catalog.pg_publication p\n"
3035  "WHERE p.puballtables AND pg_catalog.pg_relation_is_publishable('%s')\n"
3036  "ORDER BY 1;",
3037  oid, oid, oid, oid);
3038  }
3039  else
3040  {
3041  printfPQExpBuffer(&buf,
3042  "SELECT pubname\n"
3043  " , NULL\n"
3044  " , NULL\n"
3045  "FROM pg_catalog.pg_publication p\n"
3046  "JOIN pg_catalog.pg_publication_rel pr ON p.oid = pr.prpubid\n"
3047  "WHERE pr.prrelid = '%s'\n"
3048  "UNION ALL\n"
3049  "SELECT pubname\n"
3050  " , NULL\n"
3051  " , NULL\n"
3052  "FROM pg_catalog.pg_publication p\n"
3053  "WHERE p.puballtables AND pg_catalog.pg_relation_is_publishable('%s')\n"
3054  "ORDER BY 1;",
3055  oid, oid);
3056  }
3057 
3058  result = PSQLexec(buf.data);
3059  if (!result)
3060  goto error_return;
3061  else
3062  tuples = PQntuples(result);
3063 
3064  if (tuples > 0)
3065  printTableAddFooter(&cont, _("Publications:"));
3066 
3067  /* Might be an empty set - that's ok */
3068  for (i = 0; i < tuples; i++)
3069  {
3070  printfPQExpBuffer(&buf, " \"%s\"",
3071  PQgetvalue(result, i, 0));
3072 
3073  /* column list (if any) */
3074  if (!PQgetisnull(result, i, 2))
3075  appendPQExpBuffer(&buf, " (%s)",
3076  PQgetvalue(result, i, 2));
3077 
3078  /* row filter (if any) */
3079  if (!PQgetisnull(result, i, 1))
3080  appendPQExpBuffer(&buf, " WHERE %s",
3081  PQgetvalue(result, i, 1));
3082 
3083  printTableAddFooter(&cont, buf.data);
3084  }
3085  PQclear(result);
3086  }
3087 
3088  /*
3089  * If verbose, print NOT NULL constraints.
3090  */
3091  if (verbose)
3092  {
3093  printfPQExpBuffer(&buf,
3094  "SELECT c.conname, a.attname, c.connoinherit,\n"
3095  " c.conislocal, c.coninhcount <> 0\n"
3096  "FROM pg_catalog.pg_constraint c JOIN\n"
3097  " pg_catalog.pg_attribute a ON\n"
3098  " (a.attrelid = c.conrelid AND a.attnum = c.conkey[1])\n"
3099  "WHERE c.contype = " CppAsString2(CONSTRAINT_NOTNULL) " AND\n"
3100  " c.conrelid = '%s'::pg_catalog.regclass\n"
3101  "ORDER BY a.attnum",
3102  oid);
3103 
3104  result = PSQLexec(buf.data);
3105  if (!result)
3106  goto error_return;
3107  else
3108  tuples = PQntuples(result);
3109 
3110  if (tuples > 0)
3111  printTableAddFooter(&cont, _("Not-null constraints:"));
3112 
3113  /* Might be an empty set - that's ok */
3114  for (i = 0; i < tuples; i++)
3115  {
3116  bool islocal = PQgetvalue(result, i, 3)[0] == 't';
3117  bool inherited = PQgetvalue(result, i, 4)[0] == 't';
3118 
3119  printfPQExpBuffer(&buf, " \"%s\" NOT NULL \"%s\"%s",
3120  PQgetvalue(result, i, 0),
3121  PQgetvalue(result, i, 1),
3122  PQgetvalue(result, i, 2)[0] == 't' ?
3123  " NO INHERIT" :
3124  islocal && inherited ? _(" (local, inherited)") :
3125  inherited ? _(" (inherited)") : "");
3126 
3127  printTableAddFooter(&cont, buf.data);
3128  }
3129  PQclear(result);
3130  }
3131  }
3132 
3133  /* Get view_def if table is a view or materialized view */
3134  if ((tableinfo.relkind == RELKIND_VIEW ||
3135  tableinfo.relkind == RELKIND_MATVIEW) && verbose)
3136  {
3137  PGresult *result;
3138 
3139  printfPQExpBuffer(&buf,
3140  "SELECT pg_catalog.pg_get_viewdef('%s'::pg_catalog.oid, true);",
3141  oid);
3142  result = PSQLexec(buf.data);
3143  if (!result)
3144  goto error_return;
3145 
3146  if (PQntuples(result) > 0)
3147  view_def = pg_strdup(PQgetvalue(result, 0, 0));
3148 
3149  PQclear(result);
3150  }
3151 
3152  if (view_def)
3153  {
3154  PGresult *result = NULL;
3155 
3156  /* Footer information about a view */
3157  printTableAddFooter(&cont, _("View definition:"));
3158  printTableAddFooter(&cont, view_def);
3159 
3160  /* print rules */
3161  if (tableinfo.hasrules)
3162  {
3163  printfPQExpBuffer(&buf,
3164  "SELECT r.rulename, trim(trailing ';' from pg_catalog.pg_get_ruledef(r.oid, true))\n"
3165  "FROM pg_catalog.pg_rewrite r\n"
3166  "WHERE r.ev_class = '%s' AND r.rulename != '_RETURN' ORDER BY 1;",
3167  oid);
3168  result = PSQLexec(buf.data);
3169  if (!result)
3170  goto error_return;
3171 
3172  if (PQntuples(result) > 0)
3173  {
3174  printTableAddFooter(&cont, _("Rules:"));
3175  for (i = 0; i < PQntuples(result); i++)
3176  {
3177  const char *ruledef;
3178 
3179  /* Everything after "CREATE RULE" is echoed verbatim */
3180  ruledef = PQgetvalue(result, i, 1);
3181  ruledef += 12;
3182 
3183  printfPQExpBuffer(&buf, " %s", ruledef);
3184  printTableAddFooter(&cont, buf.data);
3185  }
3186  }
3187  PQclear(result);
3188  }
3189  }
3190 
3191  /*
3192  * Print triggers next, if any (but only user-defined triggers). This
3193  * could apply to either a table or a view.
3194  */
3195  if (tableinfo.hastriggers)
3196  {
3197  PGresult *result;
3198  int tuples;
3199 
3200  printfPQExpBuffer(&buf,
3201  "SELECT t.tgname, "
3202  "pg_catalog.pg_get_triggerdef(t.oid, true), "
3203  "t.tgenabled, t.tgisinternal,\n");
3204 
3205  /*
3206  * Detect whether each trigger is inherited, and if so, get the name
3207  * of the topmost table it's inherited from. We have no easy way to
3208  * do that pre-v13, for lack of the tgparentid column. Even with
3209  * tgparentid, a straightforward search for the topmost parent would
3210  * require a recursive CTE, which seems unduly expensive. We cheat a
3211  * bit by assuming parent triggers will match by tgname; then, joining
3212  * with pg_partition_ancestors() allows the planner to make use of
3213  * pg_trigger_tgrelid_tgname_index if it wishes. We ensure we find
3214  * the correct topmost parent by stopping at the first-in-partition-
3215  * ancestry-order trigger that has tgparentid = 0. (There might be
3216  * unrelated, non-inherited triggers with the same name further up the
3217  * stack, so this is important.)
3218  */
3219  if (pset.sversion >= 130000)
3220  appendPQExpBufferStr(&buf,
3221  " CASE WHEN t.tgparentid != 0 THEN\n"
3222  " (SELECT u.tgrelid::pg_catalog.regclass\n"
3223  " FROM pg_catalog.pg_trigger AS u,\n"
3224  " pg_catalog.pg_partition_ancestors(t.tgrelid) WITH ORDINALITY AS a(relid, depth)\n"
3225  " WHERE u.tgname = t.tgname AND u.tgrelid = a.relid\n"
3226  " AND u.tgparentid = 0\n"
3227  " ORDER BY a.depth LIMIT 1)\n"
3228  " END AS parent\n");
3229  else
3230  appendPQExpBufferStr(&buf, " NULL AS parent\n");
3231 
3232  appendPQExpBuffer(&buf,
3233  "FROM pg_catalog.pg_trigger t\n"
3234  "WHERE t.tgrelid = '%s' AND ",
3235  oid);
3236 
3237  /*
3238  * tgisinternal is set true for inherited triggers of partitions in
3239  * servers between v11 and v14, though these must still be shown to
3240  * the user. So we use another property that is true for such
3241  * inherited triggers to avoid them being hidden, which is their
3242  * dependence on another trigger.
3243  */
3244  if (pset.sversion >= 110000 && pset.sversion < 150000)
3245  appendPQExpBufferStr(&buf, "(NOT t.tgisinternal OR (t.tgisinternal AND t.tgenabled = 'D') \n"
3246  " OR EXISTS (SELECT 1 FROM pg_catalog.pg_depend WHERE objid = t.oid \n"
3247  " AND refclassid = 'pg_catalog.pg_trigger'::pg_catalog.regclass))");
3248  else
3249  /* display/warn about disabled internal triggers */
3250  appendPQExpBufferStr(&buf, "(NOT t.tgisinternal OR (t.tgisinternal AND t.tgenabled = 'D'))");
3251  appendPQExpBufferStr(&buf, "\nORDER BY 1;");
3252 
3253  result = PSQLexec(buf.data);
3254  if (!result)
3255  goto error_return;
3256  else
3257  tuples = PQntuples(result);
3258 
3259  if (tuples > 0)
3260  {
3261  bool have_heading;
3262  int category;
3263 
3264  /*
3265  * split the output into 4 different categories. Enabled triggers,
3266  * disabled triggers and the two special ALWAYS and REPLICA
3267  * configurations.
3268  */
3269  for (category = 0; category <= 4; category++)
3270  {
3271  have_heading = false;
3272  for (i = 0; i < tuples; i++)
3273  {
3274  bool list_trigger;
3275  const char *tgdef;
3276  const char *usingpos;
3277  const char *tgenabled;
3278  const char *tgisinternal;
3279 
3280  /*
3281  * Check if this trigger falls into the current category
3282  */
3283  tgenabled = PQgetvalue(result, i, 2);
3284  tgisinternal = PQgetvalue(result, i, 3);
3285  list_trigger = false;
3286  switch (category)
3287  {
3288  case 0:
3289  if (*tgenabled == 'O' || *tgenabled == 't')
3290  list_trigger = true;
3291  break;
3292  case 1:
3293  if ((*tgenabled == 'D' || *tgenabled == 'f') &&
3294  *tgisinternal == 'f')
3295  list_trigger = true;
3296  break;
3297  case 2:
3298  if ((*tgenabled == 'D' || *tgenabled == 'f') &&
3299  *tgisinternal == 't')
3300  list_trigger = true;
3301  break;
3302  case 3:
3303  if (*tgenabled == 'A')
3304  list_trigger = true;
3305  break;
3306  case 4:
3307  if (*tgenabled == 'R')
3308  list_trigger = true;
3309  break;
3310  }
3311  if (list_trigger == false)
3312  continue;
3313 
3314  /* Print the category heading once */
3315  if (have_heading == false)
3316  {
3317  switch (category)
3318  {
3319  case 0:
3320  printfPQExpBuffer(&buf, _("Triggers:"));
3321  break;
3322  case 1:
3323  printfPQExpBuffer(&buf, _("Disabled user triggers:"));
3324  break;
3325  case 2:
3326  printfPQExpBuffer(&buf, _("Disabled internal triggers:"));
3327  break;
3328  case 3:
3329  printfPQExpBuffer(&buf, _("Triggers firing always:"));
3330  break;
3331  case 4:
3332  printfPQExpBuffer(&buf, _("Triggers firing on replica only:"));
3333  break;
3334  }
3335  printTableAddFooter(&cont, buf.data);
3336  have_heading = true;
3337  }
3338 
3339  /* Everything after "TRIGGER" is echoed verbatim */
3340  tgdef = PQgetvalue(result, i, 1);
3341  usingpos = strstr(tgdef, " TRIGGER ");
3342  if (usingpos)
3343  tgdef = usingpos + 9;
3344 
3345  printfPQExpBuffer(&buf, " %s", tgdef);
3346 
3347  /* Visually distinguish inherited triggers */
3348  if (!PQgetisnull(result, i, 4))
3349  appendPQExpBuffer(&buf, ", ON TABLE %s",
3350  PQgetvalue(result, i, 4));
3351 
3352  printTableAddFooter(&cont, buf.data);
3353  }
3354  }
3355  }
3356  PQclear(result);
3357  }
3358 
3359  /*
3360  * Finish printing the footer information about a table.
3361  */
3362  if (tableinfo.relkind == RELKIND_RELATION ||
3363  tableinfo.relkind == RELKIND_MATVIEW ||
3364  tableinfo.relkind == RELKIND_FOREIGN_TABLE ||
3365  tableinfo.relkind == RELKIND_PARTITIONED_TABLE ||
3366  tableinfo.relkind == RELKIND_PARTITIONED_INDEX ||
3367  tableinfo.relkind == RELKIND_TOASTVALUE)
3368  {
3369  bool is_partitioned;
3370  PGresult *result;
3371  int tuples;
3372 
3373  /* simplify some repeated tests below */
3374  is_partitioned = (tableinfo.relkind == RELKIND_PARTITIONED_TABLE ||
3375  tableinfo.relkind == RELKIND_PARTITIONED_INDEX);
3376 
3377  /* print foreign server name */
3378  if (tableinfo.relkind == RELKIND_FOREIGN_TABLE)
3379  {
3380  char *ftoptions;
3381 
3382  /* Footer information about foreign table */
3383  printfPQExpBuffer(&buf,
3384  "SELECT s.srvname,\n"
3385  " pg_catalog.array_to_string(ARRAY(\n"
3386  " SELECT pg_catalog.quote_ident(option_name)"
3387  " || ' ' || pg_catalog.quote_literal(option_value)\n"
3388  " FROM pg_catalog.pg_options_to_table(ftoptions)), ', ')\n"
3389  "FROM pg_catalog.pg_foreign_table f,\n"
3390  " pg_catalog.pg_foreign_server s\n"
3391  "WHERE f.ftrelid = '%s' AND s.oid = f.ftserver;",
3392  oid);
3393  result = PSQLexec(buf.data);
3394  if (!result)
3395  goto error_return;
3396  else if (PQntuples(result) != 1)
3397  {
3398  PQclear(result);
3399  goto error_return;
3400  }
3401 
3402  /* Print server name */
3403  printfPQExpBuffer(&buf, _("Server: %s"),
3404  PQgetvalue(result, 0, 0));
3405  printTableAddFooter(&cont, buf.data);
3406 
3407  /* Print per-table FDW options, if any */
3408  ftoptions = PQgetvalue(result, 0, 1);
3409  if (ftoptions && ftoptions[0] != '\0')
3410  {
3411  printfPQExpBuffer(&buf, _("FDW options: (%s)"), ftoptions);
3412  printTableAddFooter(&cont, buf.data);
3413  }
3414  PQclear(result);
3415  }
3416 
3417  /* print tables inherited from (exclude partitioned parents) */
3418  printfPQExpBuffer(&buf,
3419  "SELECT c.oid::pg_catalog.regclass\n"
3420  "FROM pg_catalog.pg_class c, pg_catalog.pg_inherits i\n"
3421  "WHERE c.oid = i.inhparent AND i.inhrelid = '%s'\n"
3422  " AND c.relkind != " CppAsString2(RELKIND_PARTITIONED_TABLE)
3423  " AND c.relkind != " CppAsString2(RELKIND_PARTITIONED_INDEX)
3424  "\nORDER BY inhseqno;",
3425  oid);
3426 
3427  result = PSQLexec(buf.data);
3428  if (!result)
3429  goto error_return;
3430  else
3431  {
3432  const char *s = _("Inherits");
3433  int sw = pg_wcswidth(s, strlen(s), pset.encoding);
3434 
3435  tuples = PQntuples(result);
3436 
3437  for (i = 0; i < tuples; i++)
3438  {
3439  if (i == 0)
3440  printfPQExpBuffer(&buf, "%s: %s",
3441  s, PQgetvalue(result, i, 0));
3442  else
3443  printfPQExpBuffer(&buf, "%*s %s",
3444  sw, "", PQgetvalue(result, i, 0));
3445  if (i < tuples - 1)
3446  appendPQExpBufferChar(&buf, ',');
3447 
3448  printTableAddFooter(&cont, buf.data);
3449  }
3450 
3451  PQclear(result);
3452  }
3453 
3454  /* print child tables (with additional info if partitions) */
3455  if (pset.sversion >= 140000)
3456  printfPQExpBuffer(&buf,
3457  "SELECT c.oid::pg_catalog.regclass, c.relkind,"
3458  " inhdetachpending,"
3459  " pg_catalog.pg_get_expr(c.relpartbound, c.oid)\n"
3460  "FROM pg_catalog.pg_class c, pg_catalog.pg_inherits i\n"
3461  "WHERE c.oid = i.inhrelid AND i.inhparent = '%s'\n"
3462  "ORDER BY pg_catalog.pg_get_expr(c.relpartbound, c.oid) = 'DEFAULT',"
3463  " c.oid::pg_catalog.regclass::pg_catalog.text;",
3464  oid);
3465  else if (pset.sversion >= 100000)
3466  printfPQExpBuffer(&buf,
3467  "SELECT c.oid::pg_catalog.regclass, c.relkind,"
3468  " false AS inhdetachpending,"
3469  " pg_catalog.pg_get_expr(c.relpartbound, c.oid)\n"
3470  "FROM pg_catalog.pg_class c, pg_catalog.pg_inherits i\n"
3471  "WHERE c.oid = i.inhrelid AND i.inhparent = '%s'\n"
3472  "ORDER BY pg_catalog.pg_get_expr(c.relpartbound, c.oid) = 'DEFAULT',"
3473  " c.oid::pg_catalog.regclass::pg_catalog.text;",
3474  oid);
3475  else
3476  printfPQExpBuffer(&buf,
3477  "SELECT c.oid::pg_catalog.regclass, c.relkind,"
3478  " false AS inhdetachpending, NULL\n"
3479  "FROM pg_catalog.pg_class c, pg_catalog.pg_inherits i\n"
3480  "WHERE c.oid = i.inhrelid AND i.inhparent = '%s'\n"
3481  "ORDER BY c.oid::pg_catalog.regclass::pg_catalog.text;",
3482  oid);
3483 
3484  result = PSQLexec(buf.data);
3485  if (!result)
3486  goto error_return;
3487  tuples = PQntuples(result);
3488 
3489  /*
3490  * For a partitioned table with no partitions, always print the number
3491  * of partitions as zero, even when verbose output is expected.
3492  * Otherwise, we will not print "Partitions" section for a partitioned
3493  * table without any partitions.
3494  */
3495  if (is_partitioned && tuples == 0)
3496  {
3497  printfPQExpBuffer(&buf, _("Number of partitions: %d"), tuples);
3498  printTableAddFooter(&cont, buf.data);
3499  }
3500  else if (!verbose)
3501  {
3502  /* print the number of child tables, if any */
3503  if (tuples > 0)
3504  {
3505  if (is_partitioned)
3506  printfPQExpBuffer(&buf, _("Number of partitions: %d (Use \\d+ to list them.)"), tuples);
3507  else
3508  printfPQExpBuffer(&buf, _("Number of child tables: %d (Use \\d+ to list them.)"), tuples);
3509  printTableAddFooter(&cont, buf.data);
3510  }
3511  }
3512  else
3513  {
3514  /* display the list of child tables */
3515  const char *ct = is_partitioned ? _("Partitions") : _("Child tables");
3516  int ctw = pg_wcswidth(ct, strlen(ct), pset.encoding);
3517 
3518  for (i = 0; i < tuples; i++)
3519  {
3520  char child_relkind = *PQgetvalue(result, i, 1);
3521 
3522  if (i == 0)
3523  printfPQExpBuffer(&buf, "%s: %s",
3524  ct, PQgetvalue(result, i, 0));
3525  else
3526  printfPQExpBuffer(&buf, "%*s %s",
3527  ctw, "", PQgetvalue(result, i, 0));
3528  if (!PQgetisnull(result, i, 3))
3529  appendPQExpBuffer(&buf, " %s", PQgetvalue(result, i, 3));
3530  if (child_relkind == RELKIND_PARTITIONED_TABLE ||
3531  child_relkind == RELKIND_PARTITIONED_INDEX)
3532  appendPQExpBufferStr(&buf, ", PARTITIONED");
3533  else if (child_relkind == RELKIND_FOREIGN_TABLE)
3534  appendPQExpBufferStr(&buf, ", FOREIGN");
3535  if (strcmp(PQgetvalue(result, i, 2), "t") == 0)
3536  appendPQExpBufferStr(&buf, " (DETACH PENDING)");
3537  if (i < tuples - 1)
3538  appendPQExpBufferChar(&buf, ',');
3539 
3540  printTableAddFooter(&cont, buf.data);
3541  }
3542  }
3543  PQclear(result);
3544 
3545  /* Table type */
3546  if (tableinfo.reloftype)
3547  {
3548  printfPQExpBuffer(&buf, _("Typed table of type: %s"), tableinfo.reloftype);
3549  printTableAddFooter(&cont, buf.data);
3550  }
3551 
3552  if (verbose &&
3553  (tableinfo.relkind == RELKIND_RELATION ||
3554  tableinfo.relkind == RELKIND_MATVIEW) &&
3555 
3556  /*
3557  * No need to display default values; we already display a REPLICA
3558  * IDENTITY marker on indexes.
3559  */
3560  tableinfo.relreplident != REPLICA_IDENTITY_INDEX &&
3561  ((strcmp(schemaname, "pg_catalog") != 0 &&
3562  tableinfo.relreplident != REPLICA_IDENTITY_DEFAULT) ||
3563  (strcmp(schemaname, "pg_catalog") == 0 &&
3564  tableinfo.relreplident != REPLICA_IDENTITY_NOTHING)))
3565  {
3566  const char *s = _("Replica Identity");
3567 
3568  printfPQExpBuffer(&buf, "%s: %s",
3569  s,
3570  tableinfo.relreplident == REPLICA_IDENTITY_FULL ? "FULL" :
3571  tableinfo.relreplident == REPLICA_IDENTITY_DEFAULT ? "NOTHING" :
3572  "???");
3573 
3574  printTableAddFooter(&cont, buf.data);
3575  }
3576 
3577  /* OIDs, if verbose and not a materialized view */
3578  if (verbose && tableinfo.relkind != RELKIND_MATVIEW && tableinfo.hasoids)
3579  printTableAddFooter(&cont, _("Has OIDs: yes"));
3580 
3581  /* Tablespace info */
3582  add_tablespace_footer(&cont, tableinfo.relkind, tableinfo.tablespace,
3583  true);
3584 
3585  /* Access method info */
3586  if (verbose && tableinfo.relam != NULL && !pset.hide_tableam)
3587  {
3588  printfPQExpBuffer(&buf, _("Access method: %s"), tableinfo.relam);
3589  printTableAddFooter(&cont, buf.data);
3590  }
3591  }
3592 
3593  /* reloptions, if verbose */
3594  if (verbose &&
3595  tableinfo.reloptions && tableinfo.reloptions[0] != '\0')
3596  {
3597  const char *t = _("Options");
3598 
3599  printfPQExpBuffer(&buf, "%s: %s", t, tableinfo.reloptions);
3600  printTableAddFooter(&cont, buf.data);
3601  }
3602 
3603  printTable(&cont, pset.queryFout, false, pset.logfile);
3604 
3605  retval = true;
3606 
3607 error_return:
3608 
3609  /* clean up */
3610  if (printTableInitialized)
3611  printTableCleanup(&cont);
3612  termPQExpBuffer(&buf);
3613  termPQExpBuffer(&title);
3614  termPQExpBuffer(&tmpbuf);
3615 
3616  free(view_def);
3617 
3618  PQclear(res);
3619 
3620  return retval;
3621 }
3622 
3623 /*
3624  * Add a tablespace description to a footer. If 'newline' is true, it is added
3625  * in a new line; otherwise it's appended to the current value of the last
3626  * footer.
3627  */
3628 static void
3629 add_tablespace_footer(printTableContent *const cont, char relkind,
3630  Oid tablespace, const bool newline)
3631 {
3632  /* relkinds for which we support tablespaces */
3633  if (relkind == RELKIND_RELATION ||
3634  relkind == RELKIND_MATVIEW ||
3635  relkind == RELKIND_INDEX ||
3636  relkind == RELKIND_PARTITIONED_TABLE ||
3637  relkind == RELKIND_PARTITIONED_INDEX ||
3638  relkind == RELKIND_TOASTVALUE)
3639  {
3640  /*
3641  * We ignore the database default tablespace so that users not using
3642  * tablespaces don't need to know about them.
3643  */
3644  if (tablespace != 0)
3645  {
3646  PGresult *result = NULL;
3647  PQExpBufferData buf;
3648 
3649  initPQExpBuffer(&buf);
3650  printfPQExpBuffer(&buf,
3651  "SELECT spcname FROM pg_catalog.pg_tablespace\n"
3652  "WHERE oid = '%u';", tablespace);
3653  result = PSQLexec(buf.data);
3654  if (!result)
3655  {
3656  termPQExpBuffer(&buf);
3657  return;
3658  }
3659  /* Should always be the case, but.... */
3660  if (PQntuples(result) > 0)
3661  {
3662  if (newline)
3663  {
3664  /* Add the tablespace as a new footer */
3665  printfPQExpBuffer(&buf, _("Tablespace: \"%s\""),
3666  PQgetvalue(result, 0, 0));
3667  printTableAddFooter(cont, buf.data);
3668  }
3669  else
3670  {
3671  /* Append the tablespace to the latest footer */
3672  printfPQExpBuffer(&buf, "%s", cont->footer->data);
3673 
3674  /*-------
3675  translator: before this string there's an index description like
3676  '"foo_pkey" PRIMARY KEY, btree (a)' */
3677  appendPQExpBuffer(&buf, _(", tablespace \"%s\""),
3678  PQgetvalue(result, 0, 0));
3679  printTableSetFooter(cont, buf.data);
3680  }
3681  }
3682  PQclear(result);
3683  termPQExpBuffer(&buf);
3684  }
3685  }
3686 }
3687 
3688 /*
3689  * \du or \dg
3690  *
3691  * Describes roles. Any schema portion of the pattern is ignored.
3692  */
3693 bool
3694 describeRoles(const char *pattern, bool verbose, bool showSystem)
3695 {
3696  PQExpBufferData buf;
3697  PGresult *res;
3698  printTableContent cont;
3699  printTableOpt myopt = pset.popt.topt;
3700  int ncols = 2;
3701  int nrows = 0;
3702  int i;
3703  int conns;
3704  const char align = 'l';
3705  char **attr;
3706 
3707  myopt.default_footer = false;
3708 
3709  initPQExpBuffer(&buf);
3710 
3711  printfPQExpBuffer(&buf,
3712  "SELECT r.rolname, r.rolsuper, r.rolinherit,\n"
3713  " r.rolcreaterole, r.rolcreatedb, r.rolcanlogin,\n"
3714  " r.rolconnlimit, r.rolvaliduntil");
3715 
3716  if (verbose)
3717  {
3718  appendPQExpBufferStr(&buf, "\n, pg_catalog.shobj_description(r.oid, 'pg_authid') AS description");
3719  ncols++;
3720  }
3721  appendPQExpBufferStr(&buf, "\n, r.rolreplication");
3722 
3723  if (pset.sversion >= 90500)
3724  {
3725  appendPQExpBufferStr(&buf, "\n, r.rolbypassrls");
3726  }
3727 
3728  appendPQExpBufferStr(&buf, "\nFROM pg_catalog.pg_roles r\n");
3729 
3730  if (!showSystem && !pattern)
3731  appendPQExpBufferStr(&buf, "WHERE r.rolname !~ '^pg_'\n");
3732 
3733  if (!validateSQLNamePattern(&buf, pattern, false, false,
3734  NULL, "r.rolname", NULL, NULL,
3735  NULL, 1))
3736  {
3737  termPQExpBuffer(&buf);
3738  return false;
3739  }
3740 
3741  appendPQExpBufferStr(&buf, "ORDER BY 1;");
3742 
3743  res = PSQLexec(buf.data);
3744  if (!res)
3745  return false;
3746 
3747  nrows = PQntuples(res);
3748  attr = pg_malloc0((nrows + 1) * sizeof(*attr));
3749 
3750  printTableInit(&cont, &myopt, _("List of roles"), ncols, nrows);
3751 
3752  printTableAddHeader(&cont, gettext_noop("Role name"), true, align);
3753  printTableAddHeader(&cont, gettext_noop("Attributes"), true, align);
3754 
3755  if (verbose)
3756  printTableAddHeader(&cont, gettext_noop("Description"), true, align);
3757 
3758  for (i = 0; i < nrows; i++)
3759  {
3760  printTableAddCell(&cont, PQgetvalue(res, i, 0), false, false);
3761 
3762  resetPQExpBuffer(&buf);
3763  if (strcmp(PQgetvalue(res, i, 1), "t") == 0)
3764  add_role_attribute(&buf, _("Superuser"));
3765 
3766  if (strcmp(PQgetvalue(res, i, 2), "t") != 0)
3767  add_role_attribute(&buf, _("No inheritance"));
3768 
3769  if (strcmp(PQgetvalue(res, i, 3), "t") == 0)
3770  add_role_attribute(&buf, _("Create role"));
3771 
3772  if (strcmp(PQgetvalue(res, i, 4), "t") == 0)
3773  add_role_attribute(&buf, _("Create DB"));
3774 
3775  if (strcmp(PQgetvalue(res, i, 5), "t") != 0)
3776  add_role_attribute(&buf, _("Cannot login"));
3777 
3778  if (strcmp(PQgetvalue(res, i, (verbose ? 9 : 8)), "t") == 0)
3779  add_role_attribute(&buf, _("Replication"));
3780 
3781  if (pset.sversion >= 90500)
3782  if (strcmp(PQgetvalue(res, i, (verbose ? 10 : 9)), "t") == 0)
3783  add_role_attribute(&buf, _("Bypass RLS"));
3784 
3785  conns = atoi(PQgetvalue(res, i, 6));
3786  if (conns >= 0)
3787  {
3788  if (buf.len > 0)
3789  appendPQExpBufferChar(&buf, '\n');
3790 
3791  if (conns == 0)
3792  appendPQExpBufferStr(&buf, _("No connections"));
3793  else
3794  appendPQExpBuffer(&buf, ngettext("%d connection",
3795  "%d connections",
3796  conns),
3797  conns);
3798  }
3799 
3800  if (strcmp(PQgetvalue(res, i, 7), "") != 0)
3801  {
3802  if (buf.len > 0)
3803  appendPQExpBufferChar(&buf, '\n');
3804  appendPQExpBufferStr(&buf, _("Password valid until "));
3805  appendPQExpBufferStr(&buf, PQgetvalue(res, i, 7));
3806  }
3807 
3808  attr[i] = pg_strdup(buf.data);
3809 
3810  printTableAddCell(&cont, attr[i], false, false);
3811 
3812  if (verbose)
3813  printTableAddCell(&cont, PQgetvalue(res, i, 8), false, false);
3814  }
3815  termPQExpBuffer(&buf);
3816 
3817  printTable(&cont, pset.queryFout, false, pset.logfile);
3818  printTableCleanup(&cont);
3819 
3820  for (i = 0; i < nrows; i++)
3821  free(attr[i]);
3822  free(attr);
3823 
3824  PQclear(res);
3825  return true;
3826 }
3827 
3828 static void
3829 add_role_attribute(PQExpBuffer buf, const char *const str)
3830 {
3831  if (buf->len > 0)
3832  appendPQExpBufferStr(buf, ", ");
3833 
3834  appendPQExpBufferStr(buf, str);
3835 }
3836 
3837 /*
3838  * \drds
3839  */
3840 bool
3841 listDbRoleSettings(const char *pattern, const char *pattern2)
3842 {
3843  PQExpBufferData buf;
3844  PGresult *res;
3845  printQueryOpt myopt = pset.popt;
3846  bool havewhere;
3847 
3848  initPQExpBuffer(&buf);
3849 
3850  printfPQExpBuffer(&buf, "SELECT rolname AS \"%s\", datname AS \"%s\",\n"
3851  "pg_catalog.array_to_string(setconfig, E'\\n') AS \"%s\"\n"
3852  "FROM pg_catalog.pg_db_role_setting s\n"
3853  "LEFT JOIN pg_catalog.pg_database d ON d.oid = setdatabase\n"
3854  "LEFT JOIN pg_catalog.pg_roles r ON r.oid = setrole\n",
3855  gettext_noop("Role"),
3856  gettext_noop("Database"),
3857  gettext_noop("Settings"));
3858  if (!validateSQLNamePattern(&buf, pattern, false, false,
3859  NULL, "r.rolname", NULL, NULL, &havewhere, 1))
3860  goto error_return;
3861  if (!validateSQLNamePattern(&buf, pattern2, havewhere, false,
3862  NULL, "d.datname", NULL, NULL,
3863  NULL, 1))
3864  goto error_return;
3865  appendPQExpBufferStr(&buf, "ORDER BY 1, 2;");
3866 
3867  res = PSQLexec(buf.data);
3868  termPQExpBuffer(&buf);
3869  if (!res)
3870  return false;
3871 
3872  /*
3873  * Most functions in this file are content to print an empty table when
3874  * there are no matching objects. We intentionally deviate from that
3875  * here, but only in !quiet mode, because of the possibility that the user
3876  * is confused about what the two pattern arguments mean.
3877  */
3878  if (PQntuples(res) == 0 && !pset.quiet)
3879  {
3880  if (pattern && pattern2)
3881  pg_log_error("Did not find any settings for role \"%s\" and database \"%s\".",
3882  pattern, pattern2);
3883  else if (pattern)
3884  pg_log_error("Did not find any settings for role \"%s\".",
3885  pattern);
3886  else
3887  pg_log_error("Did not find any settings.");
3888  }
3889  else
3890  {
3891  myopt.title = _("List of settings");
3892  myopt.translate_header = true;
3893 
3894  printQuery(res, &myopt, pset.queryFout, false, pset.logfile);
3895  }
3896 
3897  PQclear(res);
3898  return true;
3899 
3900 error_return:
3901  termPQExpBuffer(&buf);
3902  return false;
3903 }
3904 
3905 /*
3906  * \drg
3907  * Describes role grants.
3908  */
3909 bool
3910 describeRoleGrants(const char *pattern, bool showSystem)
3911 {
3912  PQExpBufferData buf;
3913  PGresult *res;
3914  printQueryOpt myopt = pset.popt;
3915 
3916  initPQExpBuffer(&buf);
3917  printfPQExpBuffer(&buf,
3918  "SELECT m.rolname AS \"%s\", r.rolname AS \"%s\",\n"
3919  " pg_catalog.concat_ws(', ',\n",
3920  gettext_noop("Role name"),
3921  gettext_noop("Member of"));
3922 
3923  if (pset.sversion >= 160000)
3924  appendPQExpBufferStr(&buf,
3925  " CASE WHEN pam.admin_option THEN 'ADMIN' END,\n"
3926  " CASE WHEN pam.inherit_option THEN 'INHERIT' END,\n"
3927  " CASE WHEN pam.set_option THEN 'SET' END\n");
3928  else
3929  appendPQExpBufferStr(&buf,
3930  " CASE WHEN pam.admin_option THEN 'ADMIN' END,\n"
3931  " CASE WHEN m.rolinherit THEN 'INHERIT' END,\n"
3932  " 'SET'\n");
3933 
3934  appendPQExpBuffer(&buf,
3935  " ) AS \"%s\",\n"
3936  " g.rolname AS \"%s\"\n",
3937  gettext_noop("Options"),
3938  gettext_noop("Grantor"));
3939 
3940  appendPQExpBufferStr(&buf,
3941  "FROM pg_catalog.pg_roles m\n"
3942  " JOIN pg_catalog.pg_auth_members pam ON (pam.member = m.oid)\n"
3943  " LEFT JOIN pg_catalog.pg_roles r ON (pam.roleid = r.oid)\n"
3944  " LEFT JOIN pg_catalog.pg_roles g ON (pam.grantor = g.oid)\n");
3945 
3946  if (!showSystem && !pattern)
3947  appendPQExpBufferStr(&buf, "WHERE m.rolname !~ '^pg_'\n");
3948 
3949  if (!validateSQLNamePattern(&buf, pattern, false, false,
3950  NULL, "m.rolname", NULL, NULL,
3951  NULL, 1))
3952  {
3953  termPQExpBuffer(&buf);
3954  return false;
3955  }
3956 
3957  appendPQExpBufferStr(&buf, "ORDER BY 1, 2, 4;\n");
3958 
3959  res = PSQLexec(buf.data);
3960  termPQExpBuffer(&buf);
3961  if (!res)
3962  return false;
3963 
3964  myopt.title = _("List of role grants");
3965  myopt.translate_header = true;
3966 
3967  printQuery(res, &myopt, pset.queryFout, false, pset.logfile);
3968 
3969  PQclear(res);
3970  return true;
3971 }
3972 
3973 
3974 /*
3975  * listTables()
3976  *
3977  * handler for \dt, \di, etc.
3978  *
3979  * tabtypes is an array of characters, specifying what info is desired:
3980  * t - tables
3981  * i - indexes
3982  * v - views
3983  * m - materialized views
3984  * s - sequences
3985  * E - foreign table (Note: different from 'f', the relkind value)
3986  * (any order of the above is fine)
3987  */
3988 bool
3989 listTables(const char *tabtypes, const char *pattern, bool verbose, bool showSystem)
3990 {
3991  bool showTables = strchr(tabtypes, 't') != NULL;
3992  bool showIndexes = strchr(tabtypes, 'i') != NULL;
3993  bool showViews = strchr(tabtypes, 'v') != NULL;
3994  bool showMatViews = strchr(tabtypes, 'm') != NULL;
3995  bool showSeq = strchr(tabtypes, 's') != NULL;
3996  bool showForeign = strchr(tabtypes, 'E') != NULL;
3997 
3998  PQExpBufferData buf;
3999  PGresult *res;
4000  printQueryOpt myopt = pset.popt;
4001  int cols_so_far;
4002  bool translate_columns[] = {false, false, true, false, false, false, false, false, false};
4003 
4004  /* If tabtypes is empty, we default to \dtvmsE (but see also command.c) */
4005  if (!(showTables || showIndexes || showViews || showMatViews || showSeq || showForeign))
4006  showTables = showViews = showMatViews = showSeq = showForeign = true;
4007 
4008  initPQExpBuffer(&buf);
4009 
4010  printfPQExpBuffer(&buf,
4011  "SELECT n.nspname as \"%s\",\n"
4012  " c.relname as \"%s\",\n"
4013  " CASE c.relkind"
4014  " WHEN " CppAsString2(RELKIND_RELATION) " THEN '%s'"
4015  " WHEN " CppAsString2(RELKIND_VIEW) " THEN '%s'"
4016  " WHEN " CppAsString2(RELKIND_MATVIEW) " THEN '%s'"
4017  " WHEN " CppAsString2(RELKIND_INDEX) " THEN '%s'"
4018  " WHEN " CppAsString2(RELKIND_SEQUENCE) " THEN '%s'"
4019  " WHEN " CppAsString2(RELKIND_TOASTVALUE) " THEN '%s'"
4020  " WHEN " CppAsString2(RELKIND_FOREIGN_TABLE) " THEN '%s'"
4021  " WHEN " CppAsString2(RELKIND_PARTITIONED_TABLE) " THEN '%s'"
4022  " WHEN " CppAsString2(RELKIND_PARTITIONED_INDEX) " THEN '%s'"
4023  " END as \"%s\",\n"
4024  " pg_catalog.pg_get_userbyid(c.relowner) as \"%s\"",
4025  gettext_noop("Schema"),
4026  gettext_noop("Name"),
4027  gettext_noop("table"),
4028  gettext_noop("view"),
4029  gettext_noop("materialized view"),
4030  gettext_noop("index"),
4031  gettext_noop("sequence"),
4032  gettext_noop("TOAST table"),
4033  gettext_noop("foreign table"),
4034  gettext_noop("partitioned table"),
4035  gettext_noop("partitioned index"),
4036  gettext_noop("Type"),
4037  gettext_noop("Owner"));
4038  cols_so_far = 4;
4039 
4040  if (showIndexes)
4041  {
4042  appendPQExpBuffer(&buf,
4043  ",\n c2.relname as \"%s\"",
4044  gettext_noop("Table"));
4045  cols_so_far++;
4046  }
4047 
4048  if (verbose)
4049  {
4050  /*
4051  * Show whether a relation is permanent, temporary, or unlogged.
4052  */
4053  appendPQExpBuffer(&buf,
4054  ",\n CASE c.relpersistence "
4055  "WHEN " CppAsString2(RELPERSISTENCE_PERMANENT) " THEN '%s' "
4056  "WHEN " CppAsString2(RELPERSISTENCE_TEMP) " THEN '%s' "
4057  "WHEN " CppAsString2(RELPERSISTENCE_UNLOGGED) " THEN '%s' "
4058  "END as \"%s\"",
4059  gettext_noop("permanent"),
4060  gettext_noop("temporary"),
4061  gettext_noop("unlogged"),
4062  gettext_noop("Persistence"));
4063  translate_columns[cols_so_far] = true;
4064 
4065  /*
4066  * We don't bother to count cols_so_far below here, as there's no need
4067  * to; this might change with future additions to the output columns.
4068  */
4069 
4070  /*
4071  * Access methods exist for tables, materialized views and indexes.
4072  * This has been introduced in PostgreSQL 12 for tables.
4073  */
4074  if (pset.sversion >= 120000 && !pset.hide_tableam &&
4075  (showTables || showMatViews || showIndexes))
4076  appendPQExpBuffer(&buf,
4077  ",\n am.amname as \"%s\"",
4078  gettext_noop("Access method"));
4079 
4080  appendPQExpBuffer(&buf,
4081  ",\n pg_catalog.pg_size_pretty(pg_catalog.pg_table_size(c.oid)) as \"%s\""
4082  ",\n pg_catalog.obj_description(c.oid, 'pg_class') as \"%s\"",
4083  gettext_noop("Size"),
4084  gettext_noop("Description"));
4085  }
4086 
4087  appendPQExpBufferStr(&buf,
4088  "\nFROM pg_catalog.pg_class c"
4089  "\n LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace");
4090 
4091  if (pset.sversion >= 120000 && !pset.hide_tableam &&
4092  (showTables || showMatViews || showIndexes))
4093  appendPQExpBufferStr(&buf,
4094  "\n LEFT JOIN pg_catalog.pg_am am ON am.oid = c.relam");
4095 
4096  if (showIndexes)
4097  appendPQExpBufferStr(&buf,
4098  "\n LEFT JOIN pg_catalog.pg_index i ON i.indexrelid = c.oid"
4099  "\n LEFT JOIN pg_catalog.pg_class c2 ON i.indrelid = c2.oid");
4100 
4101  appendPQExpBufferStr(&buf, "\nWHERE c.relkind IN (");
4102  if (showTables)
4103  {
4104  appendPQExpBufferStr(&buf, CppAsString2(RELKIND_RELATION) ","
4105  CppAsString2(RELKIND_PARTITIONED_TABLE) ",");
4106  /* with 'S' or a pattern, allow 't' to match TOAST tables too */
4107  if (showSystem || pattern)
4108  appendPQExpBufferStr(&buf, CppAsString2(RELKIND_TOASTVALUE) ",");
4109  }
4110  if (showViews)
4111  appendPQExpBufferStr(&buf, CppAsString2(RELKIND_VIEW) ",");
4112  if (showMatViews)
4113  appendPQExpBufferStr(&buf, CppAsString2(RELKIND_MATVIEW) ",");
4114  if (showIndexes)
4115  appendPQExpBufferStr(&buf, CppAsString2(RELKIND_INDEX) ","
4116  CppAsString2(RELKIND_PARTITIONED_INDEX) ",");
4117  if (showSeq)
4118  appendPQExpBufferStr(&buf, CppAsString2(RELKIND_SEQUENCE) ",");
4119  if (showSystem || pattern)
4120  appendPQExpBufferStr(&buf, "'s',"); /* was RELKIND_SPECIAL */
4121  if (showForeign)
4122  appendPQExpBufferStr(&buf, CppAsString2(RELKIND_FOREIGN_TABLE) ",");
4123 
4124  appendPQExpBufferStr(&buf, "''"); /* dummy */
4125  appendPQExpBufferStr(&buf, ")\n");
4126 
4127  if (!showSystem && !pattern)
4128  appendPQExpBufferStr(&buf, " AND n.nspname <> 'pg_catalog'\n"
4129  " AND n.nspname !~ '^pg_toast'\n"
4130  " AND n.nspname <> 'information_schema'\n");
4131 
4132  if (!validateSQLNamePattern(&buf, pattern, true, false,
4133  "n.nspname", "c.relname", NULL,
4134  "pg_catalog.pg_table_is_visible(c.oid)",
4135  NULL, 3))
4136  {
4137  termPQExpBuffer(&buf);
4138  return false;
4139  }
4140 
4141  appendPQExpBufferStr(&buf, "ORDER BY 1,2;");
4142 
4143  res = PSQLexec(buf.data);
4144  termPQExpBuffer(&buf);
4145  if (!res)
4146  return false;
4147 
4148  /*
4149  * Most functions in this file are content to print an empty table when
4150  * there are no matching objects. We intentionally deviate from that
4151  * here, but only in !quiet mode, for historical reasons.
4152  */
4153  if (PQntuples(res) == 0 && !pset.quiet)
4154  {
4155  if (pattern)
4156  pg_log_error("Did not find any relation named \"%s\".",
4157  pattern);
4158  else
4159  pg_log_error("Did not find any relations.");
4160  }
4161  else
4162  {
4163  myopt.title = _("List of relations");
4164  myopt.translate_header = true;
4165  myopt.translate_columns = translate_columns;
4166  myopt.n_translate_columns = lengthof(translate_columns);
4167 
4168  printQuery(res, &myopt, pset.queryFout, false, pset.logfile);
4169  }
4170 
4171  PQclear(res);
4172  return true;
4173 }
4174 
4175 /*
4176  * \dP
4177  * Takes an optional regexp to select particular relations
4178  *
4179  * As with \d, you can specify the kinds of relations you want:
4180  *
4181  * t for tables
4182  * i for indexes
4183  *
4184  * And there's additional flags:
4185  *
4186  * n to list non-leaf partitioned tables
4187  *
4188  * and you can mix and match these in any order.
4189  */
4190 bool
4191 listPartitionedTables(const char *reltypes, const char *pattern, bool verbose)
4192 {
4193  bool showTables = strchr(reltypes, 't') != NULL;
4194  bool showIndexes = strchr(reltypes, 'i') != NULL;
4195  bool showNested = strchr(reltypes, 'n') != NULL;
4196  PQExpBufferData buf;
4197  PQExpBufferData title;
4198  PGresult *res;
4199  printQueryOpt myopt = pset.popt;
4200  bool translate_columns[] = {false, false, false, false, false, false, false, false, false, false};
4201  const char *tabletitle;
4202  bool mixed_output = false;
4203 
4204  /*
4205  * Note: Declarative table partitioning is only supported as of Pg 10.0.
4206  */
4207  if (pset.sversion < 100000)
4208  {
4209  char sverbuf[32];
4210 
4211  pg_log_error("The server (version %s) does not support declarative table partitioning.",
4212  formatPGVersionNumber(pset.sversion, false,
4213  sverbuf, sizeof(sverbuf)));
4214  return true;
4215  }
4216 
4217  /* If no relation kind was selected, show them all */
4218  if (!showTables && !showIndexes)
4219  showTables = showIndexes = true;
4220 
4221  if (showIndexes && !showTables)
4222  tabletitle = _("List of partitioned indexes"); /* \dPi */
4223  else if (showTables && !showIndexes)
4224  tabletitle = _("List of partitioned tables"); /* \dPt */
4225  else
4226  {
4227  /* show all kinds */
4228  tabletitle = _("List of partitioned relations");
4229  mixed_output = true;
4230  }
4231 
4232  initPQExpBuffer(&buf);
4233 
4234  printfPQExpBuffer(&buf,
4235  "SELECT n.nspname as \"%s\",\n"
4236  " c.relname as \"%s\",\n"
4237  " pg_catalog.pg_get_userbyid(c.relowner) as \"%s\"",
4238  gettext_noop("Schema"),
4239  gettext_noop("Name"),
4240  gettext_noop("Owner"));
4241 
4242  if (mixed_output)
4243  {
4244  appendPQExpBuffer(&buf,
4245  ",\n CASE c.relkind"
4246  " WHEN " CppAsString2(RELKIND_PARTITIONED_TABLE) " THEN '%s'"
4247  " WHEN " CppAsString2(RELKIND_PARTITIONED_INDEX) " THEN '%s'"
4248  " END as \"%s\"",
4249  gettext_noop("partitioned table"),
4250  gettext_noop("partitioned index"),
4251  gettext_noop("Type"));
4252 
4253  translate_columns[3] = true;
4254  }
4255 
4256  if (showNested || pattern)
4257  appendPQExpBuffer(&buf,
4258  ",\n inh.inhparent::pg_catalog.regclass as \"%s\"",
4259  gettext_noop("Parent name"));
4260 
4261  if (showIndexes)
4262  appendPQExpBuffer(&buf,
4263  ",\n c2.oid::pg_catalog.regclass as \"%s\"",
4264  gettext_noop("Table"));
4265 
4266  if (verbose)
4267  {
4268  /*
4269  * Table access methods were introduced in v12, and can be set on
4270  * partitioned tables since v17.
4271  */
4272  appendPQExpBuffer(&buf, ",\n am.amname as \"%s\"",
4273  gettext_noop("Access method"));
4274 
4275  if (showNested)
4276  {
4277  appendPQExpBuffer(&buf,
4278  ",\n s.dps as \"%s\"",
4279  gettext_noop("Leaf partition size"));
4280  appendPQExpBuffer(&buf,
4281  ",\n s.tps as \"%s\"",
4282  gettext_noop("Total size"));
4283  }
4284  else
4285  /* Sizes of all partitions are considered in this case. */
4286  appendPQExpBuffer(&buf,
4287  ",\n s.tps as \"%s\"",
4288  gettext_noop("Total size"));
4289 
4290  appendPQExpBuffer(&buf,
4291  ",\n pg_catalog.obj_description(c.oid, 'pg_class') as \"%s\"",
4292  gettext_noop("Description"));
4293  }
4294 
4295  appendPQExpBufferStr(&buf,
4296  "\nFROM pg_catalog.pg_class c"
4297  "\n LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace");
4298 
4299  if (showIndexes)
4300  appendPQExpBufferStr(&buf,
4301  "\n LEFT JOIN pg_catalog.pg_index i ON i.indexrelid = c.oid"
4302  "\n LEFT JOIN pg_catalog.pg_class c2 ON i.indrelid = c2.oid");
4303 
4304  if (showNested || pattern)
4305  appendPQExpBufferStr(&buf,
4306  "\n LEFT JOIN pg_catalog.pg_inherits inh ON c.oid = inh.inhrelid");
4307 
4308  if (verbose)
4309  {
4310  appendPQExpBufferStr(&buf,
4311  "\n LEFT JOIN pg_catalog.pg_am am ON c.relam = am.oid");
4312 
4313  if (pset.sversion < 120000)
4314  {
4315  appendPQExpBufferStr(&buf,
4316  ",\n LATERAL (WITH RECURSIVE d\n"
4317  " AS (SELECT inhrelid AS oid, 1 AS level\n"
4318  " FROM pg_catalog.pg_inherits\n"
4319  " WHERE inhparent = c.oid\n"
4320  " UNION ALL\n"
4321  " SELECT inhrelid, level + 1\n"
4322  " FROM pg_catalog.pg_inherits i\n"
4323  " JOIN d ON i.inhparent = d.oid)\n"
4324  " SELECT pg_catalog.pg_size_pretty(sum(pg_catalog.pg_table_size("
4325  "d.oid))) AS tps,\n"
4326  " pg_catalog.pg_size_pretty(sum("
4327  "\n CASE WHEN d.level = 1"
4328  " THEN pg_catalog.pg_table_size(d.oid) ELSE 0 END)) AS dps\n"
4329  " FROM d) s");
4330  }
4331  else
4332  {
4333  /* PostgreSQL 12 has pg_partition_tree function */
4334  appendPQExpBufferStr(&buf,
4335  ",\n LATERAL (SELECT pg_catalog.pg_size_pretty(sum("
4336  "\n CASE WHEN ppt.isleaf AND ppt.level = 1"
4337  "\n THEN pg_catalog.pg_table_size(ppt.relid)"
4338  " ELSE 0 END)) AS dps"
4339  ",\n pg_catalog.pg_size_pretty(sum("
4340  "pg_catalog.pg_table_size(ppt.relid))) AS tps"
4341  "\n FROM pg_catalog.pg_partition_tree(c.oid) ppt) s");
4342  }
4343  }
4344 
4345  appendPQExpBufferStr(&buf, "\nWHERE c.relkind IN (");
4346  if (showTables)
4347  appendPQExpBufferStr(&buf, CppAsString2(RELKIND_PARTITIONED_TABLE) ",");
4348  if (showIndexes)
4349  appendPQExpBufferStr(&buf, CppAsString2(RELKIND_PARTITIONED_INDEX) ",");
4350  appendPQExpBufferStr(&buf, "''"); /* dummy */
4351  appendPQExpBufferStr(&buf, ")\n");
4352 
4353  appendPQExpBufferStr(&buf, !showNested && !pattern ?
4354  " AND NOT c.relispartition\n" : "");
4355 
4356  if (!pattern)
4357  appendPQExpBufferStr(&buf, " AND n.nspname <> 'pg_catalog'\n"
4358  " AND n.nspname !~ '^pg_toast'\n"
4359  " AND n.nspname <> 'information_schema'\n");
4360 
4361  if (!validateSQLNamePattern(&buf, pattern, true, false,
4362  "n.nspname", "c.relname", NULL,
4363  "pg_catalog.pg_table_is_visible(c.oid)",
4364  NULL, 3))
4365  {
4366  termPQExpBuffer(&buf);
4367  return false;
4368  }
4369 
4370  appendPQExpBuffer(&buf, "ORDER BY \"Schema\", %s%s\"Name\";",
4371  mixed_output ? "\"Type\" DESC, " : "",
4372  showNested || pattern ? "\"Parent name\" NULLS FIRST, " : "");
4373 
4374  res = PSQLexec(buf.data);
4375  termPQExpBuffer(&buf);
4376  if (!res)
4377  return false;
4378 
4379  initPQExpBuffer(&title);
4380  appendPQExpBufferStr(&title, tabletitle);
4381 
4382  myopt.title = title.data;
4383  myopt.translate_header = true;
4384  myopt.translate_columns = translate_columns;
4385  myopt.n_translate_columns = lengthof(translate_columns);
4386 
4387  printQuery(res, &myopt, pset.queryFout, false, pset.logfile);
4388 
4389  termPQExpBuffer(&title);
4390 
4391  PQclear(res);
4392  return true;
4393 }
4394 
4395 /*
4396  * \dL
4397  *
4398  * Describes languages.
4399  */
4400 bool
4401 listLanguages(const char *pattern, bool verbose, bool showSystem)
4402 {
4403  PQExpBufferData buf;
4404  PGresult *res;
4405  printQueryOpt myopt = pset.popt;
4406 
4407  initPQExpBuffer(&buf);
4408 
4409  printfPQExpBuffer(&buf,
4410  "SELECT l.lanname AS \"%s\",\n"
4411  " pg_catalog.pg_get_userbyid(l.lanowner) as \"%s\",\n"
4412  " l.lanpltrusted AS \"%s\"",
4413  gettext_noop("Name"),
4414  gettext_noop("Owner"),
4415  gettext_noop("Trusted"));
4416 
4417  if (verbose)
4418  {
4419  appendPQExpBuffer(&buf,
4420  ",\n NOT l.lanispl AS \"%s\",\n"
4421  " l.lanplcallfoid::pg_catalog.regprocedure AS \"%s\",\n"
4422  " l.lanvalidator::pg_catalog.regprocedure AS \"%s\",\n "
4423  "l.laninline::pg_catalog.regprocedure AS \"%s\",\n ",
4424  gettext_noop("Internal language"),
4425  gettext_noop("Call handler"),
4426  gettext_noop("Validator"),
4427  gettext_noop("Inline handler"));
4428  printACLColumn(&buf, "l.lanacl");
4429  }
4430 
4431  appendPQExpBuffer(&buf,
4432  ",\n d.description AS \"%s\""
4433  "\nFROM pg_catalog.pg_language l\n"
4434  "LEFT JOIN pg_catalog.pg_description d\n"
4435  " ON d.classoid = l.tableoid AND d.objoid = l.oid\n"
4436  " AND d.objsubid = 0\n",
4437  gettext_noop("Description"));
4438 
4439  if (pattern)
4440  {
4441  if (!validateSQLNamePattern(&buf, pattern, false, false,
4442  NULL, "l.lanname", NULL, NULL,
4443  NULL, 2))
4444  {
4445  termPQExpBuffer(&buf);
4446  return false;
4447  }
4448  }
4449 
4450  if (!showSystem && !pattern)
4451  appendPQExpBufferStr(&buf, "WHERE l.lanplcallfoid != 0\n");
4452 
4453 
4454  appendPQExpBufferStr(&buf, "ORDER BY 1;");
4455 
4456  res = PSQLexec(buf.data);
4457  termPQExpBuffer(&buf);
4458  if (!res)
4459  return false;
4460 
4461  myopt.title = _("List of languages");
4462  myopt.translate_header = true;
4463 
4464  printQuery(res, &myopt, pset.queryFout, false, pset.logfile);
4465 
4466  PQclear(res);
4467  return true;
4468 }
4469 
4470 
4471 /*
4472  * \dD
4473  *
4474  * Describes domains.
4475  */
4476 bool
4477 listDomains(const char *pattern, bool verbose, bool showSystem)
4478 {
4479  PQExpBufferData buf;
4480  PGresult *res;
4481  printQueryOpt myopt = pset.popt;
4482 
4483  initPQExpBuffer(&buf);
4484 
4485  printfPQExpBuffer(&buf,
4486  "SELECT n.nspname as \"%s\",\n"
4487  " t.typname as \"%s\",\n"
4488  " pg_catalog.format_type(t.typbasetype, t.typtypmod) as \"%s\",\n"
4489  " (SELECT c.collname FROM pg_catalog.pg_collation c, pg_catalog.pg_type bt\n"
4490  " WHERE c.oid = t.typcollation AND bt.oid = t.typbasetype AND t.typcollation <> bt.typcollation) as \"%s\",\n"
4491  " CASE WHEN t.typnotnull THEN 'not null' END as \"%s\",\n"
4492  " t.typdefault as \"%s\",\n"
4493  " pg_catalog.array_to_string(ARRAY(\n"
4494  " SELECT pg_catalog.pg_get_constraintdef(r.oid, true) FROM pg_catalog.pg_constraint r WHERE t.oid = r.contypid AND r.contype = " CppAsString2(CONSTRAINT_CHECK) " ORDER BY r.conname\n"
4495  " ), ' ') as \"%s\"",
4496  gettext_noop("Schema"),
4497  gettext_noop("Name"),
4498  gettext_noop("Type"),
4499  gettext_noop("Collation"),
4500  gettext_noop("Nullable"),
4501  gettext_noop("Default"),
4502  gettext_noop("Check"));
4503 
4504  if (verbose)
4505  {
4506  appendPQExpBufferStr(&buf, ",\n ");
4507  printACLColumn(&buf, "t.typacl");
4508  appendPQExpBuffer(&buf,
4509  ",\n d.description as \"%s\"",
4510  gettext_noop("Description"));
4511  }
4512 
4513  appendPQExpBufferStr(&buf,
4514  "\nFROM pg_catalog.pg_type t\n"
4515  " LEFT JOIN pg_catalog.pg_namespace n ON n.oid = t.typnamespace\n");
4516 
4517  if (verbose)
4518  appendPQExpBufferStr(&buf,
4519  " LEFT JOIN pg_catalog.pg_description d "
4520  "ON d.classoid = t.tableoid AND d.objoid = t.oid "
4521  "AND d.objsubid = 0\n");
4522 
4523  appendPQExpBufferStr(&buf, "WHERE t.typtype = 'd'\n");
4524 
4525  if (!showSystem && !pattern)
4526  appendPQExpBufferStr(&buf, " AND n.nspname <> 'pg_catalog'\n"
4527  " AND n.nspname <> 'information_schema'\n");
4528 
4529  if (!validateSQLNamePattern(&buf, pattern, true, false,
4530  "n.nspname", "t.typname", NULL,
4531  "pg_catalog.pg_type_is_visible(t.oid)",
4532  NULL, 3))
4533  {
4534  termPQExpBuffer(&buf);
4535  return false;
4536  }
4537 
4538  appendPQExpBufferStr(&buf, "ORDER BY 1, 2;");
4539 
4540  res = PSQLexec(buf.data);
4541  termPQExpBuffer(&buf);
4542  if (!res)
4543  return false;
4544 
4545  myopt.title = _("List of domains");
4546  myopt.translate_header = true;
4547 
4548  printQuery(res, &myopt, pset.queryFout, false, pset.logfile);
4549 
4550  PQclear(res);
4551  return true;
4552 }
4553 
4554 /*
4555  * \dc
4556  *
4557  * Describes conversions.
4558  */
4559 bool
4560 listConversions(const char *pattern, bool verbose, bool showSystem)
4561 {
4562  PQExpBufferData buf;
4563  PGresult *res;
4564  printQueryOpt myopt = pset.popt;
4565  static const bool translate_columns[] =
4566  {false, false, false, false, true, false};
4567 
4568  initPQExpBuffer(&buf);
4569 
4570  printfPQExpBuffer(&buf,
4571  "SELECT n.nspname AS \"%s\",\n"
4572  " c.conname AS \"%s\",\n"
4573  " pg_catalog.pg_encoding_to_char(c.conforencoding) AS \"%s\",\n"
4574  " pg_catalog.pg_encoding_to_char(c.contoencoding) AS \"%s\",\n"
4575  " CASE WHEN c.condefault THEN '%s'\n"
4576  " ELSE '%s' END AS \"%s\"",
4577  gettext_noop("Schema"),
4578  gettext_noop("Name"),
4579  gettext_noop("Source"),
4580  gettext_noop("Destination"),
4581  gettext_noop("yes"), gettext_noop("no"),
4582  gettext_noop("Default?"));
4583 
4584  if (verbose)
4585  appendPQExpBuffer(&buf,
4586  ",\n d.description AS \"%s\"",
4587  gettext_noop("Description"));
4588 
4589  appendPQExpBufferStr(&buf,
4590  "\nFROM pg_catalog.pg_conversion c\n"
4591  " JOIN pg_catalog.pg_namespace n "
4592  "ON n.oid = c.connamespace\n");
4593 
4594  if (verbose)
4595  appendPQExpBufferStr(&buf,
4596  "LEFT JOIN pg_catalog.pg_description d "
4597  "ON d.classoid = c.tableoid\n"
4598  " AND d.objoid = c.oid "
4599  "AND d.objsubid = 0\n");
4600 
4601  appendPQExpBufferStr(&buf, "WHERE true\n");
4602 
4603  if (!showSystem && !pattern)
4604  appendPQExpBufferStr(&buf, " AND n.nspname <> 'pg_catalog'\n"
4605  " AND n.nspname <> 'information_schema'\n");
4606 
4607  if (!validateSQLNamePattern(&buf, pattern, true, false,
4608  "n.nspname", "c.conname", NULL,
4609  "pg_catalog.pg_conversion_is_visible(c.oid)",
4610  NULL, 3))
4611  {
4612  termPQExpBuffer(&buf);
4613  return false;
4614  }
4615 
4616  appendPQExpBufferStr(&buf, "ORDER BY 1, 2;");
4617 
4618  res = PSQLexec(buf.data);
4619  termPQExpBuffer(&buf);
4620  if (!res)
4621  return false;
4622 
4623  myopt.title = _("List of conversions");
4624  myopt.translate_header = true;
4625  myopt.translate_columns = translate_columns;
4626  myopt.n_translate_columns = lengthof(translate_columns);
4627 
4628  printQuery(res, &myopt, pset.queryFout, false, pset.logfile);
4629 
4630  PQclear(res);
4631  return true;
4632 }
4633 
4634 /*
4635  * \dconfig
4636  *
4637  * Describes configuration parameters.
4638  */
4639 bool
4640 describeConfigurationParameters(const char *pattern, bool verbose,
4641  bool showSystem)
4642 {
4643  PQExpBufferData buf;
4644  PGresult *res;
4645  printQueryOpt myopt = pset.popt;
4646 
4647  initPQExpBuffer(&buf);
4648  printfPQExpBuffer(&buf,
4649  "SELECT s.name AS \"%s\", "
4650  "pg_catalog.current_setting(s.name) AS \"%s\"",
4651  gettext_noop("Parameter"),
4652  gettext_noop("Value"));
4653 
4654  if (verbose)
4655  {
4656  appendPQExpBuffer(&buf,
4657  ", s.vartype AS \"%s\", s.context AS \"%s\", ",
4658  gettext_noop("Type"),
4659  gettext_noop("Context"));
4660  if (pset.sversion >= 150000)
4661  printACLColumn(&buf, "p.paracl");
4662  else
4663  appendPQExpBuffer(&buf, "NULL AS \"%s\"",
4664  gettext_noop("Access privileges"));
4665  }
4666 
4667  appendPQExpBufferStr(&buf, "\nFROM pg_catalog.pg_settings s\n");
4668 
4669  if (verbose && pset.sversion >= 150000)
4670  appendPQExpBufferStr(&buf,
4671  " LEFT JOIN pg_catalog.pg_parameter_acl p\n"
4672  " ON pg_catalog.lower(s.name) = p.parname\n");
4673 
4674  if (pattern)
4675  processSQLNamePattern(pset.db, &buf, pattern,
4676  false, false,
4677  NULL, "pg_catalog.lower(s.name)", NULL,
4678  NULL, NULL, NULL);
4679  else
4680  appendPQExpBufferStr(&buf, "WHERE s.source <> 'default' AND\n"
4681  " s.setting IS DISTINCT FROM s.boot_val\n");
4682 
4683  appendPQExpBufferStr(&buf, "ORDER BY 1;");
4684 
4685  res = PSQLexec(buf.data);
4686  termPQExpBuffer(&buf);
4687  if (!res)
4688  return false;
4689 
4690  if (pattern)
4691  myopt.title = _("List of configuration parameters");
4692  else
4693  myopt.title = _("List of non-default configuration parameters");
4694  myopt.translate_header = true;
4695 
4696  printQuery(res, &myopt, pset.queryFout, false, pset.logfile);
4697 
4698  PQclear(res);
4699  return true;
4700 }
4701 
4702 /*
4703  * \dy
4704  *
4705  * Describes Event Triggers.
4706  */
4707 bool
4708 listEventTriggers(const char *pattern, bool verbose)
4709 {
4710  PQExpBufferData buf;
4711  PGresult *res;
4712  printQueryOpt myopt = pset.popt;
4713  static const bool translate_columns[] =
4714  {false, false, false, true, false, false, false};
4715 
4716  if (pset.sversion < 90300)
4717  {
4718  char sverbuf[32];
4719 
4720  pg_log_error("The server (version %s) does not support event triggers.",
4721  formatPGVersionNumber(pset.sversion, false,
4722  sverbuf, sizeof(sverbuf)));
4723  return true;
4724  }
4725 
4726  initPQExpBuffer(&buf);
4727 
4728  printfPQExpBuffer(&buf,
4729  "SELECT evtname as \"%s\", "
4730  "evtevent as \"%s\", "
4731  "pg_catalog.pg_get_userbyid(e.evtowner) as \"%s\",\n"
4732  " case evtenabled when 'O' then '%s'"
4733  " when 'R' then '%s'"
4734  " when 'A' then '%s'"
4735  " when 'D' then '%s' end as \"%s\",\n"
4736  " e.evtfoid::pg_catalog.regproc as \"%s\", "
4737  "pg_catalog.array_to_string(array(select x"
4738  " from pg_catalog.unnest(evttags) as t(x)), ', ') as \"%s\"",
4739  gettext_noop("Name"),
4740  gettext_noop("Event"),
4741  gettext_noop("Owner"),
4742  gettext_noop("enabled"),
4743  gettext_noop("replica"),
4744  gettext_noop("always"),
4745  gettext_noop("disabled"),
4746  gettext_noop("Enabled"),
4747  gettext_noop("Function"),
4748  gettext_noop("Tags"));
4749  if (verbose)
4750  appendPQExpBuffer(&buf,
4751  ",\npg_catalog.obj_description(e.oid, 'pg_event_trigger') as \"%s\"",
4752  gettext_noop("Description"));
4753  appendPQExpBufferStr(&buf,
4754  "\nFROM pg_catalog.pg_event_trigger e ");
4755 
4756  if (!validateSQLNamePattern(&buf, pattern, false, false,
4757  NULL, "evtname", NULL, NULL,
4758  NULL, 1))
4759  {
4760  termPQExpBuffer(&buf);
4761  return false;
4762  }
4763 
4764  appendPQExpBufferStr(&buf, "ORDER BY 1");
4765 
4766  res = PSQLexec(buf.data);
4767  termPQExpBuffer(&buf);
4768  if (!res)
4769  return false;
4770 
4771  myopt.title = _("List of event triggers");
4772  myopt.translate_header = true;
4773  myopt.translate_columns = translate_columns;
4774  myopt.n_translate_columns = lengthof(translate_columns);
4775 
4776  printQuery(res, &myopt, pset.queryFout, false, pset.logfile);
4777 
4778  PQclear(res);
4779  return true;
4780 }
4781 
4782 /*
4783  * \dX
4784  *
4785  * Describes extended statistics.
4786  */
4787 bool
4788 listExtendedStats(const char *pattern)
4789 {
4790  PQExpBufferData buf;
4791  PGresult *res;
4792  printQueryOpt myopt = pset.popt;
4793 
4794  if (pset.sversion < 100000)
4795  {
4796  char sverbuf[32];
4797 
4798  pg_log_error("The server (version %s) does not support extended statistics.",
4799  formatPGVersionNumber(pset.sversion, false,
4800  sverbuf, sizeof(sverbuf)));
4801  return true;
4802  }
4803 
4804  initPQExpBuffer(&buf);
4805  printfPQExpBuffer(&buf,
4806  "SELECT \n"
4807  "es.stxnamespace::pg_catalog.regnamespace::pg_catalog.text AS \"%s\", \n"
4808  "es.stxname AS \"%s\", \n",
4809  gettext_noop("Schema"),
4810  gettext_noop("Name"));
4811 
4812  if (pset.sversion >= 140000)
4813  appendPQExpBuffer(&buf,
4814  "pg_catalog.format('%%s FROM %%s', \n"
4815  " pg_catalog.pg_get_statisticsobjdef_columns(es.oid), \n"
4816  " es.stxrelid::pg_catalog.regclass) AS \"%s\"",
4817  gettext_noop("Definition"));
4818  else
4819  appendPQExpBuffer(&buf,
4820  "pg_catalog.format('%%s FROM %%s', \n"
4821  " (SELECT pg_catalog.string_agg(pg_catalog.quote_ident(a.attname),', ') \n"
4822  " FROM pg_catalog.unnest(es.stxkeys) s(attnum) \n"
4823  " JOIN pg_catalog.pg_attribute a \n"
4824  " ON (es.stxrelid = a.attrelid \n"
4825  " AND a.attnum = s.attnum \n"
4826  " AND NOT a.attisdropped)), \n"
4827  "es.stxrelid::pg_catalog.regclass) AS \"%s\"",
4828  gettext_noop("Definition"));
4829 
4830  appendPQExpBuffer(&buf,
4831  ",\nCASE WHEN " CppAsString2(STATS_EXT_NDISTINCT) " = any(es.stxkind) THEN 'defined' \n"
4832  "END AS \"%s\", \n"
4833  "CASE WHEN " CppAsString2(STATS_EXT_DEPENDENCIES) " = any(es.stxkind) THEN 'defined' \n"
4834  "END AS \"%s\"",
4835  gettext_noop("Ndistinct"),
4836  gettext_noop("Dependencies"));
4837 
4838  /*
4839  * Include the MCV statistics kind.
4840  */
4841  if (pset.sversion >= 120000)
4842  {
4843  appendPQExpBuffer(&buf,
4844  ",\nCASE WHEN " CppAsString2(STATS_EXT_MCV) " = any(es.stxkind) THEN 'defined' \n"
4845  "END AS \"%s\" ",
4846  gettext_noop("MCV"));
4847  }
4848 
4849  appendPQExpBufferStr(&buf,
4850  " \nFROM pg_catalog.pg_statistic_ext es \n");
4851 
4852  if (!validateSQLNamePattern(&buf, pattern,
4853  false, false,
4854  "es.stxnamespace::pg_catalog.regnamespace::pg_catalog.text", "es.stxname",
4855  NULL, "pg_catalog.pg_statistics_obj_is_visible(es.oid)",
4856  NULL, 3))
4857  {
4858  termPQExpBuffer(&buf);
4859  return false;
4860  }
4861 
4862  appendPQExpBufferStr(&buf, "ORDER BY 1, 2;");
4863 
4864  res = PSQLexec(buf.data);
4865  termPQExpBuffer(&buf);
4866  if (!res)
4867  return false;
4868 
4869  myopt.title = _("List of extended statistics");
4870  myopt.translate_header = true;
4871 
4872  printQuery(res, &myopt, pset.queryFout, false, pset.logfile);
4873 
4874  PQclear(res);
4875  return true;
4876 }
4877 
4878 /*
4879  * \dC
4880  *
4881  * Describes casts.
4882  */
4883 bool
4884 listCasts(const char *pattern, bool verbose)
4885 {
4886  PQExpBufferData buf;
4887  PGresult *res;
4888  printQueryOpt myopt = pset.popt;
4889  static const bool translate_columns[] = {false, false, false, true, false};
4890 
4891  initPQExpBuffer(&buf);
4892 
4893  printfPQExpBuffer(&buf,
4894  "SELECT pg_catalog.format_type(castsource, NULL) AS \"%s\",\n"
4895  " pg_catalog.format_type(casttarget, NULL) AS \"%s\",\n",
4896  gettext_noop("Source type"),
4897  gettext_noop("Target type"));
4898 
4899  /*
4900  * We don't attempt to localize '(binary coercible)' or '(with inout)',
4901  * because there's too much risk of gettext translating a function name
4902  * that happens to match some string in the PO database.
4903  */
4904  appendPQExpBuffer(&buf,
4905  " CASE WHEN c.castmethod = '%c' THEN '(binary coercible)'\n"
4906  " WHEN c.castmethod = '%c' THEN '(with inout)'\n"
4907  " ELSE p.proname\n"
4908  " END AS \"%s\",\n",
4909  COERCION_METHOD_BINARY,
4910  COERCION_METHOD_INOUT,
4911  gettext_noop("Function"));
4912 
4913  appendPQExpBuffer(&buf,
4914  " CASE WHEN c.castcontext = '%c' THEN '%s'\n"
4915  " WHEN c.castcontext = '%c' THEN '%s'\n"
4916  " ELSE '%s'\n"
4917  " END AS \"%s\"",
4918  COERCION_CODE_EXPLICIT,
4919  gettext_noop("no"),
4920  COERCION_CODE_ASSIGNMENT,
4921  gettext_noop("in assignment"),
4922  gettext_noop("yes"),
4923  gettext_noop("Implicit?"));
4924 
4925  if (verbose)
4926  appendPQExpBuffer(&buf,
4927  ",\n d.description AS \"%s\"",
4928  gettext_noop("Description"));
4929 
4930  /*
4931  * We need a left join to pg_proc for binary casts; the others are just
4932  * paranoia.
4933  */
4934  appendPQExpBufferStr(&buf,
4935  "\nFROM pg_catalog.pg_cast c LEFT JOIN pg_catalog.pg_proc p\n"
4936  " ON c.castfunc = p.oid\n"
4937  " LEFT JOIN pg_catalog.pg_type ts\n"
4938  " ON c.castsource = ts.oid\n"
4939  " LEFT JOIN pg_catalog.pg_namespace ns\n"
4940  " ON ns.oid = ts.typnamespace\n"
4941  " LEFT JOIN pg_catalog.pg_type tt\n"
4942  " ON c.casttarget = tt.oid\n"
4943  " LEFT JOIN pg_catalog.pg_namespace nt\n"
4944  " ON nt.oid = tt.typnamespace\n");
4945 
4946  if (verbose)
4947  appendPQExpBufferStr(&buf,
4948  " LEFT JOIN pg_catalog.pg_description d\n"
4949  " ON d.classoid = c.tableoid AND d.objoid = "
4950  "c.oid AND d.objsubid = 0\n");
4951 
4952  appendPQExpBufferStr(&buf, "WHERE ( (true");
4953 
4954  /*
4955  * Match name pattern against either internal or external name of either
4956  * castsource or casttarget
4957  */
4958  if (!validateSQLNamePattern(&buf, pattern, true, false,
4959  "ns.nspname", "ts.typname",
4960  "pg_catalog.format_type(ts.oid, NULL)",
4961  "pg_catalog.pg_type_is_visible(ts.oid)",
4962  NULL, 3))
4963  goto error_return;
4964 
4965  appendPQExpBufferStr(&buf, ") OR (true");
4966 
4967  if (!validateSQLNamePattern(&buf, pattern, true, false,
4968  "nt.nspname", "tt.typname",
4969  "pg_catalog.format_type(tt.oid, NULL)",
4970  "pg_catalog.pg_type_is_visible(tt.oid)",
4971  NULL, 3))
4972  goto error_return;
4973 
4974  appendPQExpBufferStr(&buf, ") )\nORDER BY 1, 2;");
4975 
4976  res = PSQLexec(buf.data);
4977  termPQExpBuffer(&buf);
4978  if (!res)
4979  return false;
4980 
4981  myopt.title = _("List of casts");
4982  myopt.translate_header = true;
4983  myopt.translate_columns = translate_columns;
4984  myopt.n_translate_columns = lengthof(translate_columns);
4985 
4986  printQuery(res, &myopt, pset.queryFout, false, pset.logfile);
4987 
4988  PQclear(res);
4989  return true;
4990 
4991 error_return:
4992  termPQExpBuffer(&buf);
4993  return false;
4994 }
4995 
4996 /*
4997  * \dO
4998  *
4999  * Describes collations.
5000  */
5001 bool
5002 listCollations(const char *pattern, bool verbose, bool showSystem)
5003 {
5004  PQExpBufferData buf;
5005  PGresult *res;
5006  printQueryOpt myopt = pset.popt;
5007  static const bool translate_columns[] = {false, false, false, false, false, false, false, true, false};
5008 
5009  initPQExpBuffer(&buf);
5010 
5011  printfPQExpBuffer(&buf,
5012  "SELECT\n"
5013  " n.nspname AS \"%s\",\n"
5014  " c.collname AS \"%s\",\n",
5015  gettext_noop("Schema"),
5016  gettext_noop("Name"));
5017 
5018  if (pset.sversion >= 100000)
5019  appendPQExpBuffer(&buf,
5020  " CASE c.collprovider "
5021  "WHEN " CppAsString2(COLLPROVIDER_DEFAULT) " THEN 'default' "
5022  "WHEN " CppAsString2(COLLPROVIDER_BUILTIN) " THEN 'builtin' "
5023  "WHEN " CppAsString2(COLLPROVIDER_LIBC) " THEN 'libc' "
5024  "WHEN " CppAsString2(COLLPROVIDER_ICU) " THEN 'icu' "
5025  "END AS \"%s\",\n",
5026  gettext_noop("Provider"));
5027  else
5028  appendPQExpBuffer(&buf,
5029  " 'libc' AS \"%s\",\n",
5030  gettext_noop("Provider"));
5031 
5032  appendPQExpBuffer(&buf,
5033  " c.collcollate AS \"%s\",\n"
5034  " c.collctype AS \"%s\",\n",
5035  gettext_noop("Collate"),
5036  gettext_noop("Ctype"));
5037 
5038  if (pset.sversion >= 170000)
5039  appendPQExpBuffer(&buf,
5040  " c.colllocale AS \"%s\",\n",
5041  gettext_noop("Locale"));
5042  else if (pset.sversion >= 150000)
5043  appendPQExpBuffer(&buf,
5044  " c.colliculocale AS \"%s\",\n",
5045  gettext_noop("Locale"));
5046  else
5047  appendPQExpBuffer(&buf,
5048  " c.collcollate AS \"%s\",\n",
5049  gettext_noop("Locale"));
5050 
5051  if (pset.sversion >= 160000)
5052  appendPQExpBuffer(&buf,
5053  " c.collicurules AS \"%s\",\n",
5054  gettext_noop("ICU Rules"));
5055  else
5056  appendPQExpBuffer(&buf,
5057  " NULL AS \"%s\",\n",
5058  gettext_noop("ICU Rules"));
5059 
5060  if (pset.sversion >= 120000)
5061  appendPQExpBuffer(&buf,
5062  " CASE WHEN c.collisdeterministic THEN '%s' ELSE '%s' END AS \"%s\"",
5063  gettext_noop("yes"), gettext_noop("no"),
5064  gettext_noop("Deterministic?"));
5065  else
5066  appendPQExpBuffer(&buf,
5067  " '%s' AS \"%s\"",
5068  gettext_noop("yes"),
5069  gettext_noop("Deterministic?"));
5070 
5071  if (verbose)
5072  appendPQExpBuffer(&buf,
5073  ",\n pg_catalog.obj_description(c.oid, 'pg_collation') AS \"%s\"",
5074  gettext_noop("Description"));
5075 
5076  appendPQExpBufferStr(&buf,
5077  "\nFROM pg_catalog.pg_collation c, pg_catalog.pg_namespace n\n"
5078  "WHERE n.oid = c.collnamespace\n");
5079 
5080  if (!showSystem && !pattern)
5081  appendPQExpBufferStr(&buf, " AND n.nspname <> 'pg_catalog'\n"
5082  " AND n.nspname <> 'information_schema'\n");
5083 
5084  /*
5085  * Hide collations that aren't usable in the current database's encoding.
5086  * If you think to change this, note that pg_collation_is_visible rejects
5087  * unusable collations, so you will need to hack name pattern processing
5088  * somehow to avoid inconsistent behavior.
5089  */
5090  appendPQExpBufferStr(&buf, " AND c.collencoding IN (-1, pg_catalog.pg_char_to_encoding(pg_catalog.getdatabaseencoding()))\n");
5091 
5092  if (!validateSQLNamePattern(&buf, pattern, true, false,
5093  "n.nspname", "c.collname", NULL,
5094  "pg_catalog.pg_collation_is_visible(c.oid)",
5095  NULL, 3))
5096  {
5097  termPQExpBuffer(&buf);
5098  return false;
5099  }
5100 
5101  appendPQExpBufferStr(&buf, "ORDER BY 1, 2;");
5102 
5103  res = PSQLexec(buf.data);
5104  termPQExpBuffer(&buf);
5105  if (!res)
5106  return false;
5107 
5108  myopt.title = _("List of collations");
5109  myopt.translate_header = true;
5110  myopt.translate_columns = translate_columns;
5111  myopt.n_translate_columns = lengthof(translate_columns);
5112 
5113  printQuery(res, &myopt, pset.queryFout, false, pset.logfile);
5114 
5115  PQclear(res);
5116  return true;
5117 }
5118 
5119 /*
5120  * \dn
5121  *
5122  * Describes schemas (namespaces)
5123  */
5124 bool
5125 listSchemas(const char *pattern, bool verbose, bool showSystem)
5126 {
5127  PQExpBufferData buf;
5128  PGresult *res;
5129  printQueryOpt myopt = pset.popt;
5130  int pub_schema_tuples = 0;
5131  char **footers = NULL;
5132 
5133  initPQExpBuffer(&buf);
5134  printfPQExpBuffer(&buf,
5135  "SELECT n.nspname AS \"%s\",\n"