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