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