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