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