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