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