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