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