?  search:
summary | log | graphiclog1 | graphiclog2 | commit | commitdiff | tree | refs | edit | fork
blame | history | raw | HEAD
Rework some code handling pg_subscription data in psql and pg_dump
[pgsql.git] / src / bin / psql / describe.c
blob2657abdc72dd2049feecf58a1b36f39e02f06ccd
1 /*
2 * psql - the PostgreSQL interactive terminal
3 *
4 * Support for the various \d ("describe") commands. Note that the current
5 * expectation is that all functions in this file will succeed when working
6 * with servers of versions 9.2 and up. It's okay to omit irrelevant
7 * information for an old server, but not to fail outright. (But failing
8 * against a pre-9.2 server is allowed.)
9 *
10 * Copyright (c) 2000-2024, PostgreSQL Global Development Group
11 *
12 * src/bin/psql/describe.c
13 */
14 #include "postgres_fe.h"
15
16 #include <ctype.h>
17
18 #include "catalog/pg_am_d.h"
19 #include "catalog/pg_amop_d.h"
20 #include "catalog/pg_attribute_d.h"
21 #include "catalog/pg_cast_d.h"
22 #include "catalog/pg_class_d.h"
23 #include "catalog/pg_collation_d.h"
24 #include "catalog/pg_constraint_d.h"
25 #include "catalog/pg_default_acl_d.h"
26 #include "catalog/pg_proc_d.h"
27 #include "catalog/pg_statistic_ext_d.h"
28 #include "catalog/pg_subscription_d.h"
29 #include "catalog/pg_type_d.h"
30 #include "common.h"
31 #include "common/logging.h"
32 #include "describe.h"
33 #include "fe_utils/mbprint.h"
34 #include "fe_utils/print.h"
35 #include "fe_utils/string_utils.h"
36 #include "settings.h"
37
38 static const char *map_typename_pattern(const char *pattern);
39 static bool describeOneTableDetails(const char *schemaname,
40 const char *relationname,
41 const char *oid,
42 bool verbose);
43 static void add_tablespace_footer(printTableContent *const cont, char relkind,
44 Oid tablespace, const bool newline);
45 static void add_role_attribute(PQExpBuffer buf, const char *const str);
46 static bool listTSParsersVerbose(const char *pattern);
47 static bool describeOneTSParser(const char *oid, const char *nspname,
48 const char *prsname);
49 static bool listTSConfigsVerbose(const char *pattern);
50 static bool describeOneTSConfig(const char *oid, const char *nspname,
51 const char *cfgname,
52 const char *pnspname, const char *prsname);
53 static void printACLColumn(PQExpBuffer buf, const char *colname);
54 static bool listOneExtensionContents(const char *extname, const char *oid);
55 static bool validateSQLNamePattern(PQExpBuffer buf, const char *pattern,
56 bool have_where, bool force_escape,
57 const char *schemavar, const char *namevar,
58 const char *altnamevar,
59 const char *visibilityrule,
60 bool *added_clause, int maxparts);
61
62
63 /*----------------
64 * Handlers for various slash commands displaying some sort of list
65 * of things in the database.
66 *
67 * Note: try to format the queries to look nice in -E output.
68 *----------------
69 */
70
71
72 /*
73 * \da
74 * Takes an optional regexp to select particular aggregates
75 */
76 bool
77 describeAggregates(const char *pattern, bool verbose, bool showSystem)
78 {
79 PQExpBufferData buf;
80 PGresult *res;
81 printQueryOpt myopt = pset.popt;
82
83 initPQExpBuffer(&buf);
84
85 printfPQExpBuffer(&buf,
86 "SELECT n.nspname as \"%s\",\n"
87 " p.proname AS \"%s\",\n"
88 " pg_catalog.format_type(p.prorettype, NULL) AS \"%s\",\n"
89 " CASE WHEN p.pronargs = 0\n"
90 " THEN CAST('*' AS pg_catalog.text)\n"
91 " ELSE pg_catalog.pg_get_function_arguments(p.oid)\n"
92 " END AS \"%s\",\n",
93 gettext_noop("Schema"),
94 gettext_noop("Name"),
95 gettext_noop("Result data type"),
96 gettext_noop("Argument data types"));
97
98 if (pset.sversion >= 110000)
99 appendPQExpBuffer(&buf,
100 " pg_catalog.obj_description(p.oid, 'pg_proc') as \"%s\"\n"
101 "FROM pg_catalog.pg_proc p\n"
102 " LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace\n"
103 "WHERE p.prokind = " CppAsString2(PROKIND_AGGREGATE) "\n",
104 gettext_noop("Description"));
105 else
106 appendPQExpBuffer(&buf,
107 " pg_catalog.obj_description(p.oid, 'pg_proc') as \"%s\"\n"
108 "FROM pg_catalog.pg_proc p\n"
109 " LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace\n"
110 "WHERE p.proisagg\n",
111 gettext_noop("Description"));
112
113 if (!showSystem && !pattern)
114 appendPQExpBufferStr(&buf, " AND n.nspname <> 'pg_catalog'\n"
115 " AND n.nspname <> 'information_schema'\n");
116
117 if (!validateSQLNamePattern(&buf, pattern, true, false,
118 "n.nspname", "p.proname", NULL,
119 "pg_catalog.pg_function_is_visible(p.oid)",
120 NULL, 3))
121 {
122 termPQExpBuffer(&buf);
123 return false;
124 }
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.title = _("List of aggregate functions");
134 myopt.translate_header = true;
135
136 printQuery(res, &myopt, pset.queryFout, false, pset.logfile);
137
138 PQclear(res);
139 return true;
140 }
141
142 /*
143 * \dA
144 * Takes an optional regexp to select particular access methods
145 */
146 bool
147 describeAccessMethods(const char *pattern, bool verbose)
148 {
149 PQExpBufferData buf;
150 PGresult *res;
151 printQueryOpt myopt = pset.popt;
152 static const bool translate_columns[] = {false, true, false, false};
153
154 if (pset.sversion < 90600)
155 {
156 char sverbuf[32];
157
158 pg_log_error("The server (version %s) does not support access methods.",
159 formatPGVersionNumber(pset.sversion, false,
160 sverbuf, sizeof(sverbuf)));
161 return true;
162 }
163
164 initPQExpBuffer(&buf);
165
166 printfPQExpBuffer(&buf,
167 "SELECT amname AS \"%s\",\n"
168 " CASE amtype"
169 " WHEN " CppAsString2(AMTYPE_INDEX) " THEN '%s'"
170 " WHEN " CppAsString2(AMTYPE_TABLE) " THEN '%s'"
171 " END AS \"%s\"",
172 gettext_noop("Name"),
173 gettext_noop("Index"),
174 gettext_noop("Table"),
175 gettext_noop("Type"));
176
177 if (verbose)
178 {
179 appendPQExpBuffer(&buf,
180 ",\n amhandler AS \"%s\",\n"
181 " pg_catalog.obj_description(oid, 'pg_am') AS \"%s\"",
182 gettext_noop("Handler"),
183 gettext_noop("Description"));
184 }
185
186 appendPQExpBufferStr(&buf,
187 "\nFROM pg_catalog.pg_am\n");
188
189 if (!validateSQLNamePattern(&buf, pattern, false, false,
190 NULL, "amname", NULL,
191 NULL,
192 NULL, 1))
193 {
194 termPQExpBuffer(&buf);
195 return false;
196 }
197
198 appendPQExpBufferStr(&buf, "ORDER BY 1;");
199
200 res = PSQLexec(buf.data);
201 termPQExpBuffer(&buf);
202 if (!res)
203 return false;
204
205 myopt.title = _("List of access methods");
206 myopt.translate_header = true;
207 myopt.translate_columns = translate_columns;
208 myopt.n_translate_columns = lengthof(translate_columns);
209
210 printQuery(res, &myopt, pset.queryFout, false, pset.logfile);
211
212 PQclear(res);
213 return true;
214 }
215
216 /*
217 * \db
218 * Takes an optional regexp to select particular tablespaces
219 */
220 bool
221 describeTablespaces(const char *pattern, bool verbose)
222 {
223 PQExpBufferData buf;
224 PGresult *res;
225 printQueryOpt myopt = pset.popt;
226
227 initPQExpBuffer(&buf);
228
229 printfPQExpBuffer(&buf,
230 "SELECT spcname AS \"%s\",\n"
231 " pg_catalog.pg_get_userbyid(spcowner) AS \"%s\",\n"
232 " pg_catalog.pg_tablespace_location(oid) AS \"%s\"",
233 gettext_noop("Name"),
234 gettext_noop("Owner"),
235 gettext_noop("Location"));
236
237 if (verbose)
238 {
239 appendPQExpBufferStr(&buf, ",\n ");
240 printACLColumn(&buf, "spcacl");
241 appendPQExpBuffer(&buf,
242 ",\n spcoptions AS \"%s\""
243 ",\n pg_catalog.pg_size_pretty(pg_catalog.pg_tablespace_size(oid)) AS \"%s\""
244 ",\n pg_catalog.shobj_description(oid, 'pg_tablespace') AS \"%s\"",
245 gettext_noop("Options"),
246 gettext_noop("Size"),
247 gettext_noop("Description"));
248 }
249
250 appendPQExpBufferStr(&buf,
251 "\nFROM pg_catalog.pg_tablespace\n");
252
253 if (!validateSQLNamePattern(&buf, pattern, false, false,
254 NULL, "spcname", NULL,
255 NULL,
256 NULL, 1))
257 {
258 termPQExpBuffer(&buf);
259 return false;
260 }
261
262 appendPQExpBufferStr(&buf, "ORDER BY 1;");
263
264 res = PSQLexec(buf.data);
265 termPQExpBuffer(&buf);
266 if (!res)
267 return false;
268
269 myopt.title = _("List of tablespaces");
270 myopt.translate_header = true;
271
272 printQuery(res, &myopt, pset.queryFout, false, pset.logfile);
273
274 PQclear(res);
275 return true;
276 }
277
278
279 /*
280 * \df
281 * Takes an optional regexp to select particular functions.
282 *
283 * As with \d, you can specify the kinds of functions you want:
284 *
285 * a for aggregates
286 * n for normal
287 * p for procedure
288 * t for trigger
289 * w for window
290 *
291 * and you can mix and match these in any order.
292 */
293 bool
294 describeFunctions(const char *functypes, const char *func_pattern,
295 char **arg_patterns, int num_arg_patterns,
296 bool verbose, bool showSystem)
297 {
298 bool showAggregate = strchr(functypes, 'a') != NULL;
299 bool showNormal = strchr(functypes, 'n') != NULL;
300 bool showProcedure = strchr(functypes, 'p') != NULL;
301 bool showTrigger = strchr(functypes, 't') != NULL;
302 bool showWindow = strchr(functypes, 'w') != NULL;
303 bool have_where;
304 PQExpBufferData buf;
305 PGresult *res;
306 printQueryOpt myopt = pset.popt;
307 static const bool translate_columns[] = {false, false, false, false, true, true, true, false, true, false, false, false, false};
308
309 /* No "Parallel" column before 9.6 */
310 static const bool translate_columns_pre_96[] = {false, false, false, false, true, true, false, true, false, false, false, false};
311
312 if (strlen(functypes) != strspn(functypes, "anptwS+"))
313 {
314 pg_log_error("\\df only takes [anptwS+] as options");
315 return true;
316 }
317
318 if (showProcedure && pset.sversion < 110000)
319 {
320 char sverbuf[32];
321
322 pg_log_error("\\df does not take a \"%c\" option with server version %s",
323 'p',
324 formatPGVersionNumber(pset.sversion, false,
325 sverbuf, sizeof(sverbuf)));
326 return true;
327 }
328
329 if (!showAggregate && !showNormal && !showProcedure && !showTrigger && !showWindow)
330 {
331 showAggregate = showNormal = showTrigger = showWindow = true;
332 if (pset.sversion >= 110000)
333 showProcedure = true;
334 }
335
336 initPQExpBuffer(&buf);
337
338 printfPQExpBuffer(&buf,
339 "SELECT n.nspname as \"%s\",\n"
340 " p.proname as \"%s\",\n",
341 gettext_noop("Schema"),
342 gettext_noop("Name"));
343
344 if (pset.sversion >= 110000)
345 appendPQExpBuffer(&buf,
346 " pg_catalog.pg_get_function_result(p.oid) as \"%s\",\n"
347 " pg_catalog.pg_get_function_arguments(p.oid) as \"%s\",\n"
348 " CASE p.prokind\n"
349 " WHEN " CppAsString2(PROKIND_AGGREGATE) " THEN '%s'\n"
350 " WHEN " CppAsString2(PROKIND_WINDOW) " THEN '%s'\n"
351 " WHEN " CppAsString2(PROKIND_PROCEDURE) " THEN '%s'\n"
352 " ELSE '%s'\n"
353 " END as \"%s\"",
354 gettext_noop("Result data type"),
355 gettext_noop("Argument data types"),
356 /* translator: "agg" is short for "aggregate" */
357 gettext_noop("agg"),
358 gettext_noop("window"),
359 gettext_noop("proc"),
360 gettext_noop("func"),
361 gettext_noop("Type"));
362 else
363 appendPQExpBuffer(&buf,
364 " pg_catalog.pg_get_function_result(p.oid) as \"%s\",\n"
365 " pg_catalog.pg_get_function_arguments(p.oid) as \"%s\",\n"
366 " CASE\n"
367 " WHEN p.proisagg THEN '%s'\n"
368 " WHEN p.proiswindow THEN '%s'\n"
369 " WHEN p.prorettype = 'pg_catalog.trigger'::pg_catalog.regtype THEN '%s'\n"
370 " ELSE '%s'\n"
371 " END as \"%s\"",
372 gettext_noop("Result data type"),
373 gettext_noop("Argument data types"),
374 /* translator: "agg" is short for "aggregate" */
375 gettext_noop("agg"),
376 gettext_noop("window"),
377 gettext_noop("trigger"),
378 gettext_noop("func"),
379 gettext_noop("Type"));
380
381 if (verbose)
382 {
383 appendPQExpBuffer(&buf,
384 ",\n CASE\n"
385 " WHEN p.provolatile = "
386 CppAsString2(PROVOLATILE_IMMUTABLE) " THEN '%s'\n"
387 " WHEN p.provolatile = "
388 CppAsString2(PROVOLATILE_STABLE) " THEN '%s'\n"
389 " WHEN p.provolatile = "
390 CppAsString2(PROVOLATILE_VOLATILE) " THEN '%s'\n"
391 " END as \"%s\"",
392 gettext_noop("immutable"),
393 gettext_noop("stable"),
394 gettext_noop("volatile"),
395 gettext_noop("Volatility"));
396 if (pset.sversion >= 90600)
397 appendPQExpBuffer(&buf,
398 ",\n CASE\n"
399 " WHEN p.proparallel = "
400 CppAsString2(PROPARALLEL_RESTRICTED) " THEN '%s'\n"
401 " WHEN p.proparallel = "
402 CppAsString2(PROPARALLEL_SAFE) " THEN '%s'\n"
403 " WHEN p.proparallel = "
404 CppAsString2(PROPARALLEL_UNSAFE) " THEN '%s'\n"
405 " END as \"%s\"",
406 gettext_noop("restricted"),
407 gettext_noop("safe"),
408 gettext_noop("unsafe"),
409 gettext_noop("Parallel"));
410 appendPQExpBuffer(&buf,
411 ",\n pg_catalog.pg_get_userbyid(p.proowner) as \"%s\""
412 ",\n CASE WHEN prosecdef THEN '%s' ELSE '%s' END AS \"%s\"",
413 gettext_noop("Owner"),
414 gettext_noop("definer"),
415 gettext_noop("invoker"),
416 gettext_noop("Security"));
417 appendPQExpBufferStr(&buf, ",\n ");
418 printACLColumn(&buf, "p.proacl");
419 appendPQExpBuffer(&buf,
420 ",\n l.lanname as \"%s\"",
421 gettext_noop("Language"));
422 appendPQExpBuffer(&buf,
423 ",\n CASE WHEN l.lanname IN ('internal', 'c') THEN p.prosrc END as \"%s\"",
424 gettext_noop("Internal name"));
425 appendPQExpBuffer(&buf,
426 ",\n pg_catalog.obj_description(p.oid, 'pg_proc') as \"%s\"",
427 gettext_noop("Description"));
428 }
429
430 appendPQExpBufferStr(&buf,
431 "\nFROM pg_catalog.pg_proc p"
432 "\n LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace\n");
433
434 for (int i = 0; i < num_arg_patterns; i++)
435 {
436 appendPQExpBuffer(&buf,
437 " LEFT JOIN pg_catalog.pg_type t%d ON t%d.oid = p.proargtypes[%d]\n"
438 " LEFT JOIN pg_catalog.pg_namespace nt%d ON nt%d.oid = t%d.typnamespace\n",
439 i, i, i, i, i, i);
440 }
441
442 if (verbose)
443 appendPQExpBufferStr(&buf,
444 " LEFT JOIN pg_catalog.pg_language l ON l.oid = p.prolang\n");
445
446 have_where = false;
447
448 /* filter by function type, if requested */
449 if (showNormal && showAggregate && showProcedure && showTrigger && showWindow)
450 /* Do nothing */ ;
451 else if (showNormal)
452 {
453 if (!showAggregate)
454 {
455 if (have_where)
456 appendPQExpBufferStr(&buf, " AND ");
457 else
458 {
459 appendPQExpBufferStr(&buf, "WHERE ");
460 have_where = true;
461 }
462 if (pset.sversion >= 110000)
463 appendPQExpBufferStr(&buf, "p.prokind <> "
464 CppAsString2(PROKIND_AGGREGATE) "\n");
465 else
466 appendPQExpBufferStr(&buf, "NOT p.proisagg\n");
467 }
468 if (!showProcedure && pset.sversion >= 110000)
469 {
470 if (have_where)
471 appendPQExpBufferStr(&buf, " AND ");
472 else
473 {
474 appendPQExpBufferStr(&buf, "WHERE ");
475 have_where = true;
476 }
477 appendPQExpBufferStr(&buf, "p.prokind <> "
478 CppAsString2(PROKIND_PROCEDURE) "\n");
479 }
480 if (!showTrigger)
481 {
482 if (have_where)
483 appendPQExpBufferStr(&buf, " AND ");
484 else
485 {
486 appendPQExpBufferStr(&buf, "WHERE ");
487 have_where = true;
488 }
489 appendPQExpBufferStr(&buf, "p.prorettype <> 'pg_catalog.trigger'::pg_catalog.regtype\n");
490 }
491 if (!showWindow)
492 {
493 if (have_where)
494 appendPQExpBufferStr(&buf, " AND ");
495 else
496 {
497 appendPQExpBufferStr(&buf, "WHERE ");
498 have_where = true;
499 }
500 if (pset.sversion >= 110000)
501 appendPQExpBufferStr(&buf, "p.prokind <> "
502 CppAsString2(PROKIND_WINDOW) "\n");
503 else
504 appendPQExpBufferStr(&buf, "NOT p.proiswindow\n");
505 }
506 }
507 else
508 {
509 bool needs_or = false;
510
511 appendPQExpBufferStr(&buf, "WHERE (\n ");
512 have_where = true;
513 /* Note: at least one of these must be true ... */
514 if (showAggregate)
515 {
516 if (pset.sversion >= 110000)
517 appendPQExpBufferStr(&buf, "p.prokind = "
518 CppAsString2(PROKIND_AGGREGATE) "\n");
519 else
520 appendPQExpBufferStr(&buf, "p.proisagg\n");
521 needs_or = true;
522 }
523 if (showTrigger)
524 {
525 if (needs_or)
526 appendPQExpBufferStr(&buf, " OR ");
527 appendPQExpBufferStr(&buf,
528 "p.prorettype = 'pg_catalog.trigger'::pg_catalog.regtype\n");
529 needs_or = true;
530 }
531 if (showProcedure)
532 {
533 if (needs_or)
534 appendPQExpBufferStr(&buf, " OR ");
535 appendPQExpBufferStr(&buf, "p.prokind = "
536 CppAsString2(PROKIND_PROCEDURE) "\n");
537 needs_or = true;
538 }
539 if (showWindow)
540 {
541 if (needs_or)
542 appendPQExpBufferStr(&buf, " OR ");
543 if (pset.sversion >= 110000)
544 appendPQExpBufferStr(&buf, "p.prokind = "
545 CppAsString2(PROKIND_WINDOW) "\n");
546 else
547 appendPQExpBufferStr(&buf, "p.proiswindow\n");
548 }
549 appendPQExpBufferStr(&buf, " )\n");
550 }
551
552 if (!validateSQLNamePattern(&buf, func_pattern, have_where, false,
553 "n.nspname", "p.proname", NULL,
554 "pg_catalog.pg_function_is_visible(p.oid)",
555 NULL, 3))
556 goto error_return;
557
558 for (int i = 0; i < num_arg_patterns; i++)
559 {
560 if (strcmp(arg_patterns[i], "-") != 0)
561 {
562 /*
563 * Match type-name patterns against either internal or external
564 * name, like \dT. Unlike \dT, there seems no reason to
565 * discriminate against arrays or composite types.
566 */
567 char nspname[64];
568 char typname[64];
569 char ft[64];
570 char tiv[64];
571
572 snprintf(nspname, sizeof(nspname), "nt%d.nspname", i);
573 snprintf(typname, sizeof(typname), "t%d.typname", i);
574 snprintf(ft, sizeof(ft),
575 "pg_catalog.format_type(t%d.oid, NULL)", i);
576 snprintf(tiv, sizeof(tiv),
577 "pg_catalog.pg_type_is_visible(t%d.oid)", i);
578 if (!validateSQLNamePattern(&buf,
579 map_typename_pattern(arg_patterns[i]),
580 true, false,
581 nspname, typname, ft, tiv,
582 NULL, 3))
583 goto error_return;
584 }
585 else
586 {
587 /* "-" pattern specifies no such parameter */
588 appendPQExpBuffer(&buf, " AND t%d.typname IS NULL\n", i);
589 }
590 }
591
592 if (!showSystem && !func_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.title = _("List of functions");
604 myopt.translate_header = true;
605 if (pset.sversion >= 90600)
606 {
607 myopt.translate_columns = translate_columns;
608 myopt.n_translate_columns = lengthof(translate_columns);
609 }
610 else
611 {
612 myopt.translate_columns = translate_columns_pre_96;
613 myopt.n_translate_columns = lengthof(translate_columns_pre_96);
614 }
615
616 printQuery(res, &myopt, pset.queryFout, false, pset.logfile);
617
618 PQclear(res);
619 return true;
620
621 error_return:
622 termPQExpBuffer(&buf);
623 return false;
624 }
625
626
627
628 /*
629 * \dT
630 * describe types
631 */
632 bool
633 describeTypes(const char *pattern, bool verbose, bool showSystem)
634 {
635 PQExpBufferData buf;
636 PGresult *res;
637 printQueryOpt myopt = pset.popt;
638
639 initPQExpBuffer(&buf);
640
641 printfPQExpBuffer(&buf,
642 "SELECT n.nspname as \"%s\",\n"
643 " pg_catalog.format_type(t.oid, NULL) AS \"%s\",\n",
644 gettext_noop("Schema"),
645 gettext_noop("Name"));
646 if (verbose)
647 {
648 appendPQExpBuffer(&buf,
649 " t.typname AS \"%s\",\n"
650 " CASE WHEN t.typrelid != 0\n"
651 " THEN CAST('tuple' AS pg_catalog.text)\n"
652 " WHEN t.typlen < 0\n"
653 " THEN CAST('var' AS pg_catalog.text)\n"
654 " ELSE CAST(t.typlen AS pg_catalog.text)\n"
655 " END AS \"%s\",\n"
656 " pg_catalog.array_to_string(\n"
657 " ARRAY(\n"
658 " SELECT e.enumlabel\n"
659 " FROM pg_catalog.pg_enum e\n"
660 " WHERE e.enumtypid = t.oid\n"
661 " ORDER BY e.enumsortorder\n"
662 " ),\n"
663 " E'\\n'\n"
664 " ) AS \"%s\",\n"
665 " pg_catalog.pg_get_userbyid(t.typowner) AS \"%s\",\n",
666 gettext_noop("Internal name"),
667 gettext_noop("Size"),
668 gettext_noop("Elements"),
669 gettext_noop("Owner"));
670 printACLColumn(&buf, "t.typacl");
671 appendPQExpBufferStr(&buf, ",\n ");
672 }
673
674 appendPQExpBuffer(&buf,
675 " pg_catalog.obj_description(t.oid, 'pg_type') as \"%s\"\n",
676 gettext_noop("Description"));
677
678 appendPQExpBufferStr(&buf, "FROM pg_catalog.pg_type t\n"
679 " LEFT JOIN pg_catalog.pg_namespace n ON n.oid = t.typnamespace\n");
680
681 /*
682 * do not include complex types (typrelid!=0) unless they are standalone
683 * composite types
684 */
685 appendPQExpBufferStr(&buf, "WHERE (t.typrelid = 0 ");
686 appendPQExpBufferStr(&buf, "OR (SELECT c.relkind = " CppAsString2(RELKIND_COMPOSITE_TYPE)
687 " FROM pg_catalog.pg_class c "
688 "WHERE c.oid = t.typrelid))\n");
689
690 /*
691 * do not include array types unless the pattern contains []
692 */
693 if (pattern == NULL || strstr(pattern, "[]") == NULL)
694 appendPQExpBufferStr(&buf, " AND NOT EXISTS(SELECT 1 FROM pg_catalog.pg_type el WHERE el.oid = t.typelem AND el.typarray = t.oid)\n");
695
696 if (!showSystem && !pattern)
697 appendPQExpBufferStr(&buf, " AND n.nspname <> 'pg_catalog'\n"
698 " AND n.nspname <> 'information_schema'\n");
699
700 /* Match name pattern against either internal or external name */
701 if (!validateSQLNamePattern(&buf, map_typename_pattern(pattern),
702 true, false,
703 "n.nspname", "t.typname",
704 "pg_catalog.format_type(t.oid, NULL)",
705 "pg_catalog.pg_type_is_visible(t.oid)",
706 NULL, 3))
707 {
708 termPQExpBuffer(&buf);
709 return false;
710 }
711
712 appendPQExpBufferStr(&buf, "ORDER BY 1, 2;");
713
714 res = PSQLexec(buf.data);
715 termPQExpBuffer(&buf);
716 if (!res)
717 return false;
718
719 myopt.title = _("List of data types");
720 myopt.translate_header = true;
721
722 printQuery(res, &myopt, pset.queryFout, false, pset.logfile);
723
724 PQclear(res);
725 return true;
726 }
727
728 /*
729 * Map some variant type names accepted by the backend grammar into
730 * canonical type names.
731 *
732 * Helper for \dT and other functions that take typename patterns.
733 * This doesn't completely mask the fact that these names are special;
734 * for example, a pattern of "dec*" won't magically match "numeric".
735 * But it goes a long way to reduce the surprise factor.
736 */
737 static const char *
738 map_typename_pattern(const char *pattern)
739 {
740 static const char *const typename_map[] = {
741 /*
742 * These names are accepted by gram.y, although they are neither the
743 * "real" name seen in pg_type nor the canonical name printed by
744 * format_type().
745 */
746 "decimal", "numeric",
747 "float", "double precision",
748 "int", "integer",
749
750 /*
751 * We also have to map the array names for cases where the canonical
752 * name is different from what pg_type says.
753 */
754 "bool[]", "boolean[]",
755 "decimal[]", "numeric[]",
756 "float[]", "double precision[]",
757 "float4[]", "real[]",
758 "float8[]", "double precision[]",
759 "int[]", "integer[]",
760 "int2[]", "smallint[]",
761 "int4[]", "integer[]",
762 "int8[]", "bigint[]",
763 "time[]", "time without time zone[]",
764 "timetz[]", "time with time zone[]",
765 "timestamp[]", "timestamp without time zone[]",
766 "timestamptz[]", "timestamp with time zone[]",
767 "varbit[]", "bit varying[]",
768 "varchar[]", "character varying[]",
769 NULL
770 };
771
772 if (pattern == NULL)
773 return NULL;
774 for (int i = 0; typename_map[i] != NULL; i += 2)
775 {
776 if (pg_strcasecmp(pattern, typename_map[i]) == 0)
777 return typename_map[i + 1];
778 }
779 return pattern;
780 }
781
782
783 /*
784 * \do
785 * Describe operators
786 */
787 bool
788 describeOperators(const char *oper_pattern,
789 char **arg_patterns, int num_arg_patterns,
790 bool verbose, bool showSystem)
791 {
792 PQExpBufferData buf;
793 PGresult *res;
794 printQueryOpt myopt = pset.popt;
795
796 initPQExpBuffer(&buf);
797
798 /*
799 * Note: before Postgres 9.1, we did not assign comments to any built-in
800 * operators, preferring to let the comment on the underlying function
801 * suffice. The coalesce() on the obj_description() calls below supports
802 * this convention by providing a fallback lookup of a comment on the
803 * operator's function. Since 9.1 there is a policy that every built-in
804 * operator should have a comment; so the coalesce() is no longer
805 * necessary so far as built-in operators are concerned. We keep it
806 * anyway, for now, because third-party modules may still be following the
807 * old convention.
808 *
809 * The support for postfix operators in this query is dead code as of
810 * Postgres 14, but we need to keep it for as long as we support talking
811 * to pre-v14 servers.
812 */
813
814 printfPQExpBuffer(&buf,
815 "SELECT n.nspname as \"%s\",\n"
816 " o.oprname AS \"%s\",\n"
817 " CASE WHEN o.oprkind='l' THEN NULL ELSE pg_catalog.format_type(o.oprleft, NULL) END AS \"%s\",\n"
818 " CASE WHEN o.oprkind='r' THEN NULL ELSE pg_catalog.format_type(o.oprright, NULL) END AS \"%s\",\n"
819 " pg_catalog.format_type(o.oprresult, NULL) AS \"%s\",\n",
820 gettext_noop("Schema"),
821 gettext_noop("Name"),
822 gettext_noop("Left arg type"),
823 gettext_noop("Right arg type"),
824 gettext_noop("Result type"));
825
826 if (verbose)
827 appendPQExpBuffer(&buf,
828 " o.oprcode AS \"%s\",\n",
829 gettext_noop("Function"));
830
831 appendPQExpBuffer(&buf,
832 " coalesce(pg_catalog.obj_description(o.oid, 'pg_operator'),\n"
833 " pg_catalog.obj_description(o.oprcode, 'pg_proc')) AS \"%s\"\n"
834 "FROM pg_catalog.pg_operator o\n"
835 " LEFT JOIN pg_catalog.pg_namespace n ON n.oid = o.oprnamespace\n",
836 gettext_noop("Description"));
837
838 if (num_arg_patterns >= 2)
839 {
840 num_arg_patterns = 2; /* ignore any additional arguments */
841 appendPQExpBufferStr(&buf,
842 " LEFT JOIN pg_catalog.pg_type t0 ON t0.oid = o.oprleft\n"
843 " LEFT JOIN pg_catalog.pg_namespace nt0 ON nt0.oid = t0.typnamespace\n"
844 " LEFT JOIN pg_catalog.pg_type t1 ON t1.oid = o.oprright\n"
845 " LEFT JOIN pg_catalog.pg_namespace nt1 ON nt1.oid = t1.typnamespace\n");
846 }
847 else if (num_arg_patterns == 1)
848 {
849 appendPQExpBufferStr(&buf,
850 " LEFT JOIN pg_catalog.pg_type t0 ON t0.oid = o.oprright\n"
851 " LEFT JOIN pg_catalog.pg_namespace nt0 ON nt0.oid = t0.typnamespace\n");
852 }
853
854 if (!showSystem && !oper_pattern)
855 appendPQExpBufferStr(&buf, "WHERE n.nspname <> 'pg_catalog'\n"
856 " AND n.nspname <> 'information_schema'\n");
857
858 if (!validateSQLNamePattern(&buf, oper_pattern,
859 !showSystem && !oper_pattern, true,
860 "n.nspname", "o.oprname", NULL,
861 "pg_catalog.pg_operator_is_visible(o.oid)",
862 NULL, 3))
863 goto error_return;
864
865 if (num_arg_patterns == 1)
866 appendPQExpBufferStr(&buf, " AND o.oprleft = 0\n");
867
868 for (int i = 0; i < num_arg_patterns; i++)
869 {
870 if (strcmp(arg_patterns[i], "-") != 0)
871 {
872 /*
873 * Match type-name patterns against either internal or external
874 * name, like \dT. Unlike \dT, there seems no reason to
875 * discriminate against arrays or composite types.
876 */
877 char nspname[64];
878 char typname[64];
879 char ft[64];
880 char tiv[64];
881
882 snprintf(nspname, sizeof(nspname), "nt%d.nspname", i);
883 snprintf(typname, sizeof(typname), "t%d.typname", i);
884 snprintf(ft, sizeof(ft),
885 "pg_catalog.format_type(t%d.oid, NULL)", i);
886 snprintf(tiv, sizeof(tiv),
887 "pg_catalog.pg_type_is_visible(t%d.oid)", i);
888 if (!validateSQLNamePattern(&buf,
889 map_typename_pattern(arg_patterns[i]),
890 true, false,
891 nspname, typname, ft, tiv,
892 NULL, 3))
893 goto error_return;
894 }
895 else
896 {
897 /* "-" pattern specifies no such parameter */
898 appendPQExpBuffer(&buf, " AND t%d.typname IS NULL\n", i);
899 }
900 }
901
902 appendPQExpBufferStr(&buf, "ORDER BY 1, 2, 3, 4;");
903
904 res = PSQLexec(buf.data);
905 termPQExpBuffer(&buf);
906 if (!res)
907 return false;
908
909 myopt.title = _("List of operators");
910 myopt.translate_header = true;
911
912 printQuery(res, &myopt, pset.queryFout, false, pset.logfile);
913
914 PQclear(res);
915 return true;
916
917 error_return:
918 termPQExpBuffer(&buf);
919 return false;
920 }
921
922
923 /*
924 * listAllDbs
925 *
926 * for \l, \list, and -l switch
927 */
928 bool
929 listAllDbs(const char *pattern, bool verbose)
930 {
931 PGresult *res;
932 PQExpBufferData buf;
933 printQueryOpt myopt = pset.popt;
934
935 initPQExpBuffer(&buf);
936
937 printfPQExpBuffer(&buf,
938 "SELECT\n"
939 " d.datname as \"%s\",\n"
940 " pg_catalog.pg_get_userbyid(d.datdba) as \"%s\",\n"
941 " pg_catalog.pg_encoding_to_char(d.encoding) as \"%s\",\n",
942 gettext_noop("Name"),
943 gettext_noop("Owner"),
944 gettext_noop("Encoding"));
945 if (pset.sversion >= 150000)
946 appendPQExpBuffer(&buf,
947 " CASE d.datlocprovider "
948 "WHEN " CppAsString2(COLLPROVIDER_BUILTIN) " THEN 'builtin' "
949 "WHEN " CppAsString2(COLLPROVIDER_LIBC) " THEN 'libc' "
950 "WHEN " CppAsString2(COLLPROVIDER_ICU) " THEN 'icu' "
951 "END AS \"%s\",\n",
952 gettext_noop("Locale Provider"));
953 else
954 appendPQExpBuffer(&buf,
955 " 'libc' AS \"%s\",\n",
956 gettext_noop("Locale Provider"));
957 appendPQExpBuffer(&buf,
958 " d.datcollate as \"%s\",\n"
959 " d.datctype as \"%s\",\n",
960 gettext_noop("Collate"),
961 gettext_noop("Ctype"));
962 if (pset.sversion >= 170000)
963 appendPQExpBuffer(&buf,
964 " d.datlocale as \"%s\",\n",
965 gettext_noop("Locale"));
966 else if (pset.sversion >= 150000)
967 appendPQExpBuffer(&buf,
968 " d.daticulocale as \"%s\",\n",
969 gettext_noop("Locale"));
970 else
971 appendPQExpBuffer(&buf,
972 " NULL as \"%s\",\n",
973 gettext_noop("Locale"));
974 if (pset.sversion >= 160000)
975 appendPQExpBuffer(&buf,
976 " d.daticurules as \"%s\",\n",
977 gettext_noop("ICU Rules"));
978 else
979 appendPQExpBuffer(&buf,
980 " NULL as \"%s\",\n",
981 gettext_noop("ICU Rules"));
982 appendPQExpBufferStr(&buf, " ");
983 printACLColumn(&buf, "d.datacl");
984 if (verbose)
985 appendPQExpBuffer(&buf,
986 ",\n CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT')\n"
987 " THEN pg_catalog.pg_size_pretty(pg_catalog.pg_database_size(d.datname))\n"
988 " ELSE 'No Access'\n"
989 " END as \"%s\""
990 ",\n t.spcname as \"%s\""
991 ",\n pg_catalog.shobj_description(d.oid, 'pg_database') as \"%s\"",
992 gettext_noop("Size"),
993 gettext_noop("Tablespace"),
994 gettext_noop("Description"));
995 appendPQExpBufferStr(&buf,
996 "\nFROM pg_catalog.pg_database d\n");
997 if (verbose)
998 appendPQExpBufferStr(&buf,
999 " JOIN pg_catalog.pg_tablespace t on d.dattablespace = t.oid\n");
1000
1001 if (pattern)
1002 {
1003 if (!validateSQLNamePattern(&buf, pattern, false, false,
1004 NULL, "d.datname", NULL, NULL,
1005 NULL, 1))
1006 {
1007 termPQExpBuffer(&buf);
1008 return false;
1009 }
1010 }
1011
1012 appendPQExpBufferStr(&buf, "ORDER BY 1;");
1013 res = PSQLexec(buf.data);
1014 termPQExpBuffer(&buf);
1015 if (!res)
1016 return false;
1017
1018 myopt.title = _("List of databases");
1019 myopt.translate_header = true;
1020
1021 printQuery(res, &myopt, pset.queryFout, false, pset.logfile);
1022
1023 PQclear(res);
1024 return true;
1025 }
1026
1027
1028 /*
1029 * List Tables' Grant/Revoke Permissions
1030 * \z (now also \dp -- perhaps more mnemonic)
1031 */
1032 bool
1033 permissionsList(const char *pattern, bool showSystem)
1034 {
1035 PQExpBufferData buf;
1036 PGresult *res;
1037 printQueryOpt myopt = pset.popt;
1038 static const bool translate_columns[] = {false, false, true, false, false, false};
1039
1040 initPQExpBuffer(&buf);
1041
1042 /*
1043 * we ignore indexes and toast tables since they have no meaningful rights
1044 */
1045 printfPQExpBuffer(&buf,
1046 "SELECT n.nspname as \"%s\",\n"
1047 " c.relname as \"%s\",\n"
1048 " CASE c.relkind"
1049 " WHEN " CppAsString2(RELKIND_RELATION) " THEN '%s'"
1050 " WHEN " CppAsString2(RELKIND_VIEW) " THEN '%s'"
1051 " WHEN " CppAsString2(RELKIND_MATVIEW) " THEN '%s'"
1052 " WHEN " CppAsString2(RELKIND_SEQUENCE) " THEN '%s'"
1053 " WHEN " CppAsString2(RELKIND_FOREIGN_TABLE) " THEN '%s'"
1054 " WHEN " CppAsString2(RELKIND_PARTITIONED_TABLE) " THEN '%s'"
1055 " END as \"%s\",\n"
1056 " ",
1057 gettext_noop("Schema"),
1058 gettext_noop("Name"),
1059 gettext_noop("table"),
1060 gettext_noop("view"),
1061 gettext_noop("materialized view"),
1062 gettext_noop("sequence"),
1063 gettext_noop("foreign table"),
1064 gettext_noop("partitioned table"),
1065 gettext_noop("Type"));
1066
1067 printACLColumn(&buf, "c.relacl");
1068
1069 /*
1070 * The formatting of attacl should match printACLColumn(). However, we
1071 * need no special case for an empty attacl, because the backend always
1072 * optimizes that back to NULL.
1073 */
1074 appendPQExpBuffer(&buf,
1075 ",\n pg_catalog.array_to_string(ARRAY(\n"
1076 " SELECT attname || E':\\n ' || pg_catalog.array_to_string(attacl, E'\\n ')\n"
1077 " FROM pg_catalog.pg_attribute a\n"
1078 " WHERE attrelid = c.oid AND NOT attisdropped AND attacl IS NOT NULL\n"
1079 " ), E'\\n') AS \"%s\"",
1080 gettext_noop("Column privileges"));
1081
1082 if (pset.sversion >= 90500 && pset.sversion < 100000)
1083 appendPQExpBuffer(&buf,
1084 ",\n pg_catalog.array_to_string(ARRAY(\n"
1085 " SELECT polname\n"
1086 " || CASE WHEN polcmd != '*' THEN\n"
1087 " E' (' || polcmd::pg_catalog.text || E'):'\n"
1088 " ELSE E':'\n"
1089 " END\n"
1090 " || CASE WHEN polqual IS NOT NULL THEN\n"
1091 " E'\\n (u): ' || pg_catalog.pg_get_expr(polqual, polrelid)\n"
1092 " ELSE E''\n"
1093 " END\n"
1094 " || CASE WHEN polwithcheck IS NOT NULL THEN\n"
1095 " E'\\n (c): ' || pg_catalog.pg_get_expr(polwithcheck, polrelid)\n"
1096 " ELSE E''\n"
1097 " END"
1098 " || CASE WHEN polroles <> '{0}' THEN\n"
1099 " E'\\n to: ' || pg_catalog.array_to_string(\n"
1100 " ARRAY(\n"
1101 " SELECT rolname\n"
1102 " FROM pg_catalog.pg_roles\n"
1103 " WHERE oid = ANY (polroles)\n"
1104 " ORDER BY 1\n"
1105 " ), E', ')\n"
1106 " ELSE E''\n"
1107 " END\n"
1108 " FROM pg_catalog.pg_policy pol\n"
1109 " WHERE polrelid = c.oid), E'\\n')\n"
1110 " AS \"%s\"",
1111 gettext_noop("Policies"));
1112
1113 if (pset.sversion >= 100000)
1114 appendPQExpBuffer(&buf,
1115 ",\n pg_catalog.array_to_string(ARRAY(\n"
1116 " SELECT polname\n"
1117 " || CASE WHEN NOT polpermissive THEN\n"
1118 " E' (RESTRICTIVE)'\n"
1119 " ELSE '' END\n"
1120 " || CASE WHEN polcmd != '*' THEN\n"
1121 " E' (' || polcmd::pg_catalog.text || E'):'\n"
1122 " ELSE E':'\n"
1123 " END\n"
1124 " || CASE WHEN polqual IS NOT NULL THEN\n"
1125 " E'\\n (u): ' || pg_catalog.pg_get_expr(polqual, polrelid)\n"
1126 " ELSE E''\n"
1127 " END\n"
1128 " || CASE WHEN polwithcheck IS NOT NULL THEN\n"
1129 " E'\\n (c): ' || pg_catalog.pg_get_expr(polwithcheck, polrelid)\n"
1130 " ELSE E''\n"
1131 " END"
1132 " || CASE WHEN polroles <> '{0}' THEN\n"
1133 " E'\\n to: ' || pg_catalog.array_to_string(\n"
1134 " ARRAY(\n"
1135 " SELECT rolname\n"
1136 " FROM pg_catalog.pg_roles\n"
1137 " WHERE oid = ANY (polroles)\n"
1138 " ORDER BY 1\n"
1139 " ), E', ')\n"
1140 " ELSE E''\n"
1141 " END\n"
1142 " FROM pg_catalog.pg_policy pol\n"
1143 " WHERE polrelid = c.oid), E'\\n')\n"
1144 " AS \"%s\"",
1145 gettext_noop("Policies"));
1146
1147 appendPQExpBufferStr(&buf, "\nFROM pg_catalog.pg_class c\n"
1148 " LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace\n"
1149 "WHERE c.relkind IN ("
1150 CppAsString2(RELKIND_RELATION) ","
1151 CppAsString2(RELKIND_VIEW) ","
1152 CppAsString2(RELKIND_MATVIEW) ","
1153 CppAsString2(RELKIND_SEQUENCE) ","
1154 CppAsString2(RELKIND_FOREIGN_TABLE) ","
1155 CppAsString2(RELKIND_PARTITIONED_TABLE) ")\n");
1156
1157 if (!showSystem && !pattern)
1158 appendPQExpBufferStr(&buf, " AND n.nspname <> 'pg_catalog'\n"
1159 " AND n.nspname <> 'information_schema'\n");
1160
1161 if (!validateSQLNamePattern(&buf, pattern, true, false,
1162 "n.nspname", "c.relname", NULL,
1163 "pg_catalog.pg_table_is_visible(c.oid)",
1164 NULL, 3))
1165 goto error_return;
1166
1167 appendPQExpBufferStr(&buf, "ORDER BY 1, 2;");
1168
1169 res = PSQLexec(buf.data);
1170 if (!res)
1171 goto error_return;
1172
1173 printfPQExpBuffer(&buf, _("Access privileges"));
1174 myopt.title = buf.data;
1175 myopt.translate_header = true;
1176 myopt.translate_columns = translate_columns;
1177 myopt.n_translate_columns = lengthof(translate_columns);
1178
1179 printQuery(res, &myopt, pset.queryFout, false, pset.logfile);
1180
1181 termPQExpBuffer(&buf);
1182 PQclear(res);
1183 return true;
1184
1185 error_return:
1186 termPQExpBuffer(&buf);
1187 return false;
1188 }
1189
1190
1191 /*
1192 * \ddp
1193 *
1194 * List Default ACLs. The pattern can match either schema or role name.
1195 */
1196 bool
1197 listDefaultACLs(const char *pattern)
1198 {
1199 PQExpBufferData buf;
1200 PGresult *res;
1201 printQueryOpt myopt = pset.popt;
1202 static const bool translate_columns[] = {false, false, true, false};
1203
1204 initPQExpBuffer(&buf);
1205
1206 printfPQExpBuffer(&buf,
1207 "SELECT pg_catalog.pg_get_userbyid(d.defaclrole) AS \"%s\",\n"
1208 " n.nspname AS \"%s\",\n"
1209 " 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"
1210 " ",
1211 gettext_noop("Owner"),
1212 gettext_noop("Schema"),
1213 DEFACLOBJ_RELATION,
1214 gettext_noop("table"),
1215 DEFACLOBJ_SEQUENCE,
1216 gettext_noop("sequence"),
1217 DEFACLOBJ_FUNCTION,
1218 gettext_noop("function"),
1219 DEFACLOBJ_TYPE,
1220 gettext_noop("type"),
1221 DEFACLOBJ_NAMESPACE,
1222 gettext_noop("schema"),
1223 gettext_noop("Type"));
1224
1225 printACLColumn(&buf, "d.defaclacl");
1226
1227 appendPQExpBufferStr(&buf, "\nFROM pg_catalog.pg_default_acl d\n"
1228 " LEFT JOIN pg_catalog.pg_namespace n ON n.oid = d.defaclnamespace\n");
1229
1230 if (!validateSQLNamePattern(&buf, pattern, false, false,
1231 NULL,
1232 "n.nspname",
1233 "pg_catalog.pg_get_userbyid(d.defaclrole)",
1234 NULL,
1235 NULL, 3))
1236 goto error_return;
1237
1238 appendPQExpBufferStr(&buf, "ORDER BY 1, 2, 3;");
1239
1240 res = PSQLexec(buf.data);
1241 if (!res)
1242 goto error_return;
1243
1244 printfPQExpBuffer(&buf, _("Default access privileges"));
1245 myopt.title = buf.data;
1246 myopt.translate_header = true;
1247 myopt.translate_columns = translate_columns;
1248 myopt.n_translate_columns = lengthof(translate_columns);
1249
1250 printQuery(res, &myopt, pset.queryFout, false, pset.logfile);
1251
1252 termPQExpBuffer(&buf);
1253 PQclear(res);
1254 return true;
1255
1256 error_return:
1257 termPQExpBuffer(&buf);
1258 return false;
1259 }
1260
1261
1262 /*
1263 * Get object comments
1264 *
1265 * \dd [foo]
1266 *
1267 * Note: This command only lists comments for object types which do not have
1268 * their comments displayed by their own backslash commands. The following
1269 * types of objects will be displayed: constraint, operator class,
1270 * operator family, rule, and trigger.
1271 *
1272 */
1273 bool
1274 objectDescription(const char *pattern, bool showSystem)
1275 {
1276 PQExpBufferData buf;
1277 PGresult *res;
1278 printQueryOpt myopt = pset.popt;
1279 static const bool translate_columns[] = {false, false, true, false};
1280
1281 initPQExpBuffer(&buf);
1282
1283 appendPQExpBuffer(&buf,
1284 "SELECT DISTINCT tt.nspname AS \"%s\", tt.name AS \"%s\", tt.object AS \"%s\", d.description AS \"%s\"\n"
1285 "FROM (\n",
1286 gettext_noop("Schema"),
1287 gettext_noop("Name"),
1288 gettext_noop("Object"),
1289 gettext_noop("Description"));
1290
1291 /* Table constraint descriptions */
1292 appendPQExpBuffer(&buf,
1293 " SELECT pgc.oid as oid, pgc.tableoid AS tableoid,\n"
1294 " n.nspname as nspname,\n"
1295 " CAST(pgc.conname AS pg_catalog.text) as name,"
1296 " CAST('%s' AS pg_catalog.text) as object\n"
1297 " FROM pg_catalog.pg_constraint pgc\n"
1298 " JOIN pg_catalog.pg_class c "
1299 "ON c.oid = pgc.conrelid\n"
1300 " LEFT JOIN pg_catalog.pg_namespace n "
1301 " ON n.oid = c.relnamespace\n",
1302 gettext_noop("table constraint"));
1303
1304 if (!showSystem && !pattern)
1305 appendPQExpBufferStr(&buf, "WHERE n.nspname <> 'pg_catalog'\n"
1306 " AND n.nspname <> 'information_schema'\n");
1307
1308 if (!validateSQLNamePattern(&buf, pattern, !showSystem && !pattern,
1309 false, "n.nspname", "pgc.conname", NULL,
1310 "pg_catalog.pg_table_is_visible(c.oid)",
1311 NULL, 3))
1312 goto error_return;
1313
1314 /* Domain constraint descriptions */
1315 appendPQExpBuffer(&buf,
1316 "UNION ALL\n"
1317 " SELECT pgc.oid as oid, pgc.tableoid AS tableoid,\n"
1318 " n.nspname as nspname,\n"
1319 " CAST(pgc.conname AS pg_catalog.text) as name,"
1320 " CAST('%s' AS pg_catalog.text) as object\n"
1321 " FROM pg_catalog.pg_constraint pgc\n"
1322 " JOIN pg_catalog.pg_type t "
1323 "ON t.oid = pgc.contypid\n"
1324 " LEFT JOIN pg_catalog.pg_namespace n "
1325 " ON n.oid = t.typnamespace\n",
1326 gettext_noop("domain constraint"));
1327
1328 if (!showSystem && !pattern)
1329 appendPQExpBufferStr(&buf, "WHERE n.nspname <> 'pg_catalog'\n"
1330 " AND n.nspname <> 'information_schema'\n");
1331
1332 if (!validateSQLNamePattern(&buf, pattern, !showSystem && !pattern,
1333 false, "n.nspname", "pgc.conname", NULL,
1334 "pg_catalog.pg_type_is_visible(t.oid)",
1335 NULL, 3))
1336 goto error_return;
1337
1338 /* Operator class descriptions */
1339 appendPQExpBuffer(&buf,
1340 "UNION ALL\n"
1341 " SELECT o.oid as oid, o.tableoid as tableoid,\n"
1342 " n.nspname as nspname,\n"
1343 " CAST(o.opcname AS pg_catalog.text) as name,\n"
1344 " CAST('%s' AS pg_catalog.text) as object\n"
1345 " FROM pg_catalog.pg_opclass o\n"
1346 " JOIN pg_catalog.pg_am am ON "
1347 "o.opcmethod = am.oid\n"
1348 " JOIN pg_catalog.pg_namespace n ON "
1349 "n.oid = o.opcnamespace\n",
1350 gettext_noop("operator class"));
1351
1352 if (!showSystem && !pattern)
1353 appendPQExpBufferStr(&buf, " AND n.nspname <> 'pg_catalog'\n"
1354 " AND n.nspname <> 'information_schema'\n");
1355
1356 if (!validateSQLNamePattern(&buf, pattern, true, false,
1357 "n.nspname", "o.opcname", NULL,
1358 "pg_catalog.pg_opclass_is_visible(o.oid)",
1359 NULL, 3))
1360 goto error_return;
1361
1362 /* Operator family descriptions */
1363 appendPQExpBuffer(&buf,
1364 "UNION ALL\n"
1365 " SELECT opf.oid as oid, opf.tableoid as tableoid,\n"
1366 " n.nspname as nspname,\n"
1367 " CAST(opf.opfname AS pg_catalog.text) AS name,\n"
1368 " CAST('%s' AS pg_catalog.text) as object\n"
1369 " FROM pg_catalog.pg_opfamily opf\n"
1370 " JOIN pg_catalog.pg_am am "
1371 "ON opf.opfmethod = am.oid\n"
1372 " JOIN pg_catalog.pg_namespace n "
1373 "ON opf.opfnamespace = n.oid\n",
1374 gettext_noop("operator family"));
1375
1376 if (!showSystem && !pattern)
1377 appendPQExpBufferStr(&buf, " AND n.nspname <> 'pg_catalog'\n"
1378 " AND n.nspname <> 'information_schema'\n");
1379
1380 if (!validateSQLNamePattern(&buf, pattern, true, false,
1381 "n.nspname", "opf.opfname", NULL,
1382 "pg_catalog.pg_opfamily_is_visible(opf.oid)",
1383 NULL, 3))
1384 goto error_return;
1385
1386 /* Rule descriptions (ignore rules for views) */
1387 appendPQExpBuffer(&buf,
1388 "UNION ALL\n"
1389 " SELECT r.oid as oid, r.tableoid as tableoid,\n"
1390 " n.nspname as nspname,\n"
1391 " CAST(r.rulename AS pg_catalog.text) as name,"
1392 " CAST('%s' AS pg_catalog.text) as object\n"
1393 " FROM pg_catalog.pg_rewrite r\n"
1394 " JOIN pg_catalog.pg_class c ON c.oid = r.ev_class\n"
1395 " LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace\n"
1396 " WHERE r.rulename != '_RETURN'\n",
1397 gettext_noop("rule"));
1398
1399 if (!showSystem && !pattern)
1400 appendPQExpBufferStr(&buf, " AND n.nspname <> 'pg_catalog'\n"
1401 " AND n.nspname <> 'information_schema'\n");
1402
1403 if (!validateSQLNamePattern(&buf, pattern, true, false,
1404 "n.nspname", "r.rulename", NULL,
1405 "pg_catalog.pg_table_is_visible(c.oid)",
1406 NULL, 3))
1407 goto error_return;
1408
1409 /* Trigger descriptions */
1410 appendPQExpBuffer(&buf,
1411 "UNION ALL\n"
1412 " SELECT t.oid as oid, t.tableoid as tableoid,\n"
1413 " n.nspname as nspname,\n"
1414 " CAST(t.tgname AS pg_catalog.text) as name,"
1415 " CAST('%s' AS pg_catalog.text) as object\n"
1416 " FROM pg_catalog.pg_trigger t\n"
1417 " JOIN pg_catalog.pg_class c ON c.oid = t.tgrelid\n"
1418 " LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace\n",
1419 gettext_noop("trigger"));
1420
1421 if (!showSystem && !pattern)
1422 appendPQExpBufferStr(&buf, "WHERE n.nspname <> 'pg_catalog'\n"
1423 " AND n.nspname <> 'information_schema'\n");
1424
1425 if (!validateSQLNamePattern(&buf, pattern, !showSystem && !pattern, false,
1426 "n.nspname", "t.tgname", NULL,
1427 "pg_catalog.pg_table_is_visible(c.oid)",
1428 NULL, 3))
1429 goto error_return;
1430
1431 appendPQExpBufferStr(&buf,
1432 ") AS tt\n"
1433 " JOIN pg_catalog.pg_description d ON (tt.oid = d.objoid AND tt.tableoid = d.classoid AND d.objsubid = 0)\n");
1434
1435 appendPQExpBufferStr(&buf, "ORDER BY 1, 2, 3;");
1436
1437 res = PSQLexec(buf.data);
1438 termPQExpBuffer(&buf);
1439 if (!res)
1440 return false;
1441
1442 myopt.title = _("Object descriptions");
1443 myopt.translate_header = true;
1444 myopt.translate_columns = translate_columns;
1445 myopt.n_translate_columns = lengthof(translate_columns);
1446
1447 printQuery(res, &myopt, pset.queryFout, false, pset.logfile);
1448
1449 PQclear(res);
1450 return true;
1451
1452 error_return:
1453 termPQExpBuffer(&buf);
1454 return false;
1455 }
1456
1457
1458 /*
1459 * describeTableDetails (for \d)
1460 *
1461 * This routine finds the tables to be displayed, and calls
1462 * describeOneTableDetails for each one.
1463 *
1464 * verbose: if true, this is \d+
1465 */
1466 bool
1467 describeTableDetails(const char *pattern, bool verbose, bool showSystem)
1468 {
1469 PQExpBufferData buf;
1470 PGresult *res;
1471 int i;
1472
1473 initPQExpBuffer(&buf);
1474
1475 printfPQExpBuffer(&buf,
1476 "SELECT c.oid,\n"
1477 " n.nspname,\n"
1478 " c.relname\n"
1479 "FROM pg_catalog.pg_class c\n"
1480 " LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace\n");
1481
1482 if (!showSystem && !pattern)
1483 appendPQExpBufferStr(&buf, "WHERE n.nspname <> 'pg_catalog'\n"
1484 " AND n.nspname <> 'information_schema'\n");
1485
1486 if (!validateSQLNamePattern(&buf, pattern, !showSystem && !pattern, false,
1487 "n.nspname", "c.relname", NULL,
1488 "pg_catalog.pg_table_is_visible(c.oid)",
1489 NULL, 3))
1490 {
1491 termPQExpBuffer(&buf);
1492 return false;
1493 }
1494
1495 appendPQExpBufferStr(&buf, "ORDER BY 2, 3;");
1496
1497 res = PSQLexec(buf.data);
1498 termPQExpBuffer(&buf);
1499 if (!res)
1500 return false;
1501
1502 if (PQntuples(res) == 0)
1503 {
1504 if (!pset.quiet)
1505 {
1506 if (pattern)
1507 pg_log_error("Did not find any relation named \"%s\".",
1508 pattern);
1509 else
1510 pg_log_error("Did not find any relations.");
1511 }
1512 PQclear(res);
1513 return false;
1514 }
1515
1516 for (i = 0; i < PQntuples(res); i++)
1517 {
1518 const char *oid;
1519 const char *nspname;
1520 const char *relname;
1521
1522 oid = PQgetvalue(res, i, 0);
1523 nspname = PQgetvalue(res, i, 1);
1524 relname = PQgetvalue(res, i, 2);
1525
1526 if (!describeOneTableDetails(nspname, relname, oid, verbose))
1527 {
1528 PQclear(res);
1529 return false;
1530 }
1531 if (cancel_pressed)
1532 {
1533 PQclear(res);
1534 return false;
1535 }
1536 }
1537
1538 PQclear(res);
1539 return true;
1540 }
1541
1542 /*
1543 * describeOneTableDetails (for \d)
1544 *
1545 * Unfortunately, the information presented here is so complicated that it
1546 * cannot be done in a single query. So we have to assemble the printed table
1547 * by hand and pass it to the underlying printTable() function.
1548 */
1549 static bool
1550 describeOneTableDetails(const char *schemaname,
1551 const char *relationname,
1552 const char *oid,
1553 bool verbose)
1554 {
1555 bool retval = false;
1556 PQExpBufferData buf;
1557 PGresult *res = NULL;
1558 printTableOpt myopt = pset.popt.topt;
1559 printTableContent cont;
1560 bool printTableInitialized = false;
1561 int i;
1562 char *view_def = NULL;
1563 char *headers[12];
1564 PQExpBufferData title;
1565 PQExpBufferData tmpbuf;
1566 int cols;
1567 int attname_col = -1, /* column indexes in "res" */
1568 atttype_col = -1,
1569 attrdef_col = -1,
1570 attnotnull_col = -1,
1571 attcoll_col = -1,
1572 attidentity_col = -1,
1573 attgenerated_col = -1,
1574 isindexkey_col = -1,
1575 indexdef_col = -1,
1576 fdwopts_col = -1,
1577 attstorage_col = -1,
1578 attcompression_col = -1,
1579 attstattarget_col = -1,
1580 attdescr_col = -1;
1581 int numrows;
1582 struct
1583 {
1584 int16 checks;
1585 char relkind;
1586 bool hasindex;
1587 bool hasrules;
1588 bool hastriggers;
1589 bool rowsecurity;
1590 bool forcerowsecurity;
1591 bool hasoids;
1592 bool ispartition;
1593 Oid tablespace;
1594 char *reloptions;
1595 char *reloftype;
1596 char relpersistence;
1597 char relreplident;
1598 char *relam;
1599 } tableinfo;
1600 bool show_column_details = false;
1601
1602 myopt.default_footer = false;
1603 /* This output looks confusing in expanded mode. */
1604 myopt.expanded = false;
1605
1606 initPQExpBuffer(&buf);
1607 initPQExpBuffer(&title);
1608 initPQExpBuffer(&tmpbuf);
1609
1610 /* Get general table info */
1611 if (pset.sversion >= 120000)
1612 {
1613 printfPQExpBuffer(&buf,
1614 "SELECT c.relchecks, c.relkind, c.relhasindex, c.relhasrules, "
1615 "c.relhastriggers, c.relrowsecurity, c.relforcerowsecurity, "
1616 "false AS relhasoids, c.relispartition, %s, c.reltablespace, "
1617 "CASE WHEN c.reloftype = 0 THEN '' ELSE c.reloftype::pg_catalog.regtype::pg_catalog.text END, "
1618 "c.relpersistence, c.relreplident, am.amname\n"
1619 "FROM pg_catalog.pg_class c\n "
1620 "LEFT JOIN pg_catalog.pg_class tc ON (c.reltoastrelid = tc.oid)\n"
1621 "LEFT JOIN pg_catalog.pg_am am ON (c.relam = am.oid)\n"
1622 "WHERE c.oid = '%s';",
1623 (verbose ?
1624 "pg_catalog.array_to_string(c.reloptions || "
1625 "array(select 'toast.' || x from pg_catalog.unnest(tc.reloptions) x), ', ')\n"
1626 : "''"),
1627 oid);
1628 }
1629 else if (pset.sversion >= 100000)
1630 {
1631 printfPQExpBuffer(&buf,
1632 "SELECT c.relchecks, c.relkind, c.relhasindex, c.relhasrules, "
1633 "c.relhastriggers, c.relrowsecurity, c.relforcerowsecurity, "
1634 "c.relhasoids, c.relispartition, %s, c.reltablespace, "
1635 "CASE WHEN c.reloftype = 0 THEN '' ELSE c.reloftype::pg_catalog.regtype::pg_catalog.text END, "
1636 "c.relpersistence, c.relreplident\n"
1637 "FROM pg_catalog.pg_class c\n "
1638 "LEFT JOIN pg_catalog.pg_class tc ON (c.reltoastrelid = tc.oid)\n"
1639 "WHERE c.oid = '%s';",
1640 (verbose ?
1641 "pg_catalog.array_to_string(c.reloptions || "
1642 "array(select 'toast.' || x from pg_catalog.unnest(tc.reloptions) x), ', ')\n"
1643 : "''"),
1644 oid);
1645 }
1646 else if (pset.sversion >= 90500)
1647 {
1648 printfPQExpBuffer(&buf,
1649 "SELECT c.relchecks, c.relkind, c.relhasindex, c.relhasrules, "
1650 "c.relhastriggers, c.relrowsecurity, c.relforcerowsecurity, "
1651 "c.relhasoids, false as relispartition, %s, c.reltablespace, "
1652 "CASE WHEN c.reloftype = 0 THEN '' ELSE c.reloftype::pg_catalog.regtype::pg_catalog.text END, "
1653 "c.relpersistence, c.relreplident\n"
1654 "FROM pg_catalog.pg_class c\n "
1655 "LEFT JOIN pg_catalog.pg_class tc ON (c.reltoastrelid = tc.oid)\n"
1656 "WHERE c.oid = '%s';",
1657 (verbose ?
1658 "pg_catalog.array_to_string(c.reloptions || "
1659 "array(select 'toast.' || x from pg_catalog.unnest(tc.reloptions) x), ', ')\n"
1660 : "''"),
1661 oid);
1662 }
1663 else if (pset.sversion >= 90400)
1664 {
1665 printfPQExpBuffer(&buf,
1666 "SELECT c.relchecks, c.relkind, c.relhasindex, c.relhasrules, "
1667 "c.relhastriggers, false, false, c.relhasoids, "
1668 "false as relispartition, %s, c.reltablespace, "
1669 "CASE WHEN c.reloftype = 0 THEN '' ELSE c.reloftype::pg_catalog.regtype::pg_catalog.text END, "
1670 "c.relpersistence, c.relreplident\n"
1671 "FROM pg_catalog.pg_class c\n "
1672 "LEFT JOIN pg_catalog.pg_class tc ON (c.reltoastrelid = tc.oid)\n"
1673 "WHERE c.oid = '%s';",
1674 (verbose ?
1675 "pg_catalog.array_to_string(c.reloptions || "
1676 "array(select 'toast.' || x from pg_catalog.unnest(tc.reloptions) x), ', ')\n"
1677 : "''"),
1678 oid);
1679 }
1680 else
1681 {
1682 printfPQExpBuffer(&buf,
1683 "SELECT c.relchecks, c.relkind, c.relhasindex, c.relhasrules, "
1684 "c.relhastriggers, false, false, c.relhasoids, "
1685 "false as relispartition, %s, c.reltablespace, "
1686 "CASE WHEN c.reloftype = 0 THEN '' ELSE c.reloftype::pg_catalog.regtype::pg_catalog.text END, "
1687 "c.relpersistence\n"
1688 "FROM pg_catalog.pg_class c\n "
1689 "LEFT JOIN pg_catalog.pg_class tc ON (c.reltoastrelid = tc.oid)\n"
1690 "WHERE c.oid = '%s';",
1691 (verbose ?
1692 "pg_catalog.array_to_string(c.reloptions || "
1693 "array(select 'toast.' || x from pg_catalog.unnest(tc.reloptions) x), ', ')\n"
1694 : "''"),
1695 oid);
1696 }
1697
1698 res = PSQLexec(buf.data);
1699 if (!res)
1700 goto error_return;
1701
1702 /* Did we get anything? */
1703 if (PQntuples(res) == 0)
1704 {
1705 if (!pset.quiet)
1706 pg_log_error("Did not find any relation with OID %s.", oid);
1707 goto error_return;
1708 }
1709
1710 tableinfo.checks = atoi(PQgetvalue(res, 0, 0));
1711 tableinfo.relkind = *(PQgetvalue(res, 0, 1));
1712 tableinfo.hasindex = strcmp(PQgetvalue(res, 0, 2), "t") == 0;
1713 tableinfo.hasrules = strcmp(PQgetvalue(res, 0, 3), "t") == 0;
1714 tableinfo.hastriggers = strcmp(PQgetvalue(res, 0, 4), "t") == 0;
1715 tableinfo.rowsecurity = strcmp(PQgetvalue(res, 0, 5), "t") == 0;
1716 tableinfo.forcerowsecurity = strcmp(PQgetvalue(res, 0, 6), "t") == 0;
1717 tableinfo.hasoids = strcmp(PQgetvalue(res, 0, 7), "t") == 0;
1718 tableinfo.ispartition = strcmp(PQgetvalue(res, 0, 8), "t") == 0;
1719 tableinfo.reloptions = pg_strdup(PQgetvalue(res, 0, 9));
1720 tableinfo.tablespace = atooid(PQgetvalue(res, 0, 10));
1721 tableinfo.reloftype = (strcmp(PQgetvalue(res, 0, 11), "") != 0) ?
1722 pg_strdup(PQgetvalue(res, 0, 11)) : NULL;
1723 tableinfo.relpersistence = *(PQgetvalue(res, 0, 12));
1724 tableinfo.relreplident = (pset.sversion >= 90400) ?
1725 *(PQgetvalue(res, 0, 13)) : 'd';
1726 if (pset.sversion >= 120000)
1727 tableinfo.relam = PQgetisnull(res, 0, 14) ?
1728 (char *) NULL : pg_strdup(PQgetvalue(res, 0, 14));
1729 else
1730 tableinfo.relam = NULL;
1731 PQclear(res);
1732 res = NULL;
1733
1734 /*
1735 * If it's a sequence, deal with it here separately.
1736 */
1737 if (tableinfo.relkind == RELKIND_SEQUENCE)
1738 {
1739 PGresult *result = NULL;
1740 printQueryOpt myopt = pset.popt;
1741 char *footers[2] = {NULL, NULL};
1742
1743 if (pset.sversion >= 100000)
1744 {
1745 printfPQExpBuffer(&buf,
1746 "SELECT pg_catalog.format_type(seqtypid, NULL) AS \"%s\",\n"
1747 " seqstart AS \"%s\",\n"
1748 " seqmin AS \"%s\",\n"
1749 " seqmax AS \"%s\",\n"
1750 " seqincrement AS \"%s\",\n"
1751 " CASE WHEN seqcycle THEN '%s' ELSE '%s' END AS \"%s\",\n"
1752 " seqcache AS \"%s\"\n",
1753 gettext_noop("Type"),
1754 gettext_noop("Start"),
1755 gettext_noop("Minimum"),
1756 gettext_noop("Maximum"),
1757 gettext_noop("Increment"),
1758 gettext_noop("yes"),
1759 gettext_noop("no"),
1760 gettext_noop("Cycles?"),
1761 gettext_noop("Cache"));
1762 appendPQExpBuffer(&buf,
1763 "FROM pg_catalog.pg_sequence\n"
1764 "WHERE seqrelid = '%s';",
1765 oid);
1766 }
1767 else
1768 {
1769 printfPQExpBuffer(&buf,
1770 "SELECT 'bigint' AS \"%s\",\n"
1771 " start_value AS \"%s\",\n"
1772 " min_value AS \"%s\",\n"
1773 " max_value AS \"%s\",\n"
1774 " increment_by AS \"%s\",\n"
1775 " CASE WHEN is_cycled THEN '%s' ELSE '%s' END AS \"%s\",\n"
1776 " cache_value AS \"%s\"\n",
1777 gettext_noop("Type"),
1778 gettext_noop("Start"),
1779 gettext_noop("Minimum"),
1780 gettext_noop("Maximum"),
1781 gettext_noop("Increment"),
1782 gettext_noop("yes"),
1783 gettext_noop("no"),
1784 gettext_noop("Cycles?"),
1785 gettext_noop("Cache"));
1786 appendPQExpBuffer(&buf, "FROM %s", fmtId(schemaname));
1787 /* must be separate because fmtId isn't reentrant */
1788 appendPQExpBuffer(&buf, ".%s;", fmtId(relationname));
1789 }
1790
1791 res = PSQLexec(buf.data);
1792 if (!res)
1793 goto error_return;
1794
1795 /* Get the column that owns this sequence */
1796 printfPQExpBuffer(&buf, "SELECT pg_catalog.quote_ident(nspname) || '.' ||"
1797 "\n pg_catalog.quote_ident(relname) || '.' ||"
1798 "\n pg_catalog.quote_ident(attname),"
1799 "\n d.deptype"
1800 "\nFROM pg_catalog.pg_class c"
1801 "\nINNER JOIN pg_catalog.pg_depend d ON c.oid=d.refobjid"
1802 "\nINNER JOIN pg_catalog.pg_namespace n ON n.oid=c.relnamespace"
1803 "\nINNER JOIN pg_catalog.pg_attribute a ON ("
1804 "\n a.attrelid=c.oid AND"
1805 "\n a.attnum=d.refobjsubid)"
1806 "\nWHERE d.classid='pg_catalog.pg_class'::pg_catalog.regclass"
1807 "\n AND d.refclassid='pg_catalog.pg_class'::pg_catalog.regclass"
1808 "\n AND d.objid='%s'"
1809 "\n AND d.deptype IN ('a', 'i')",
1810 oid);
1811
1812 result = PSQLexec(buf.data);
1813
1814 /*
1815 * If we get no rows back, don't show anything (obviously). We should
1816 * never get more than one row back, but if we do, just ignore it and
1817 * don't print anything.
1818 */
1819 if (!result)
1820 goto error_return;
1821 else if (PQntuples(result) == 1)
1822 {
1823 switch (PQgetvalue(result, 0, 1)[0])
1824 {
1825 case 'a':
1826 footers[0] = psprintf(_("Owned by: %s"),
1827 PQgetvalue(result, 0, 0));
1828 break;
1829 case 'i':
1830 footers[0] = psprintf(_("Sequence for identity column: %s"),
1831 PQgetvalue(result, 0, 0));
1832 break;
1833 }
1834 }
1835 PQclear(result);
1836
1837 if (tableinfo.relpersistence == RELPERSISTENCE_UNLOGGED)
1838 printfPQExpBuffer(&title, _("Unlogged sequence \"%s.%s\""),
1839 schemaname, relationname);
1840 else
1841 printfPQExpBuffer(&title, _("Sequence \"%s.%s\""),
1842 schemaname, relationname);
1843
1844 myopt.footers = footers;
1845 myopt.topt.default_footer = false;
1846 myopt.title = title.data;
1847 myopt.translate_header = true;
1848
1849 printQuery(res, &myopt, pset.queryFout, false, pset.logfile);
1850
1851 free(footers[0]);
1852
1853 retval = true;
1854 goto error_return; /* not an error, just return early */
1855 }
1856
1857 /* Identify whether we should print collation, nullable, default vals */
1858 if (tableinfo.relkind == RELKIND_RELATION ||
1859 tableinfo.relkind == RELKIND_VIEW ||
1860 tableinfo.relkind == RELKIND_MATVIEW ||
1861 tableinfo.relkind == RELKIND_FOREIGN_TABLE ||
1862 tableinfo.relkind == RELKIND_COMPOSITE_TYPE ||
1863 tableinfo.relkind == RELKIND_PARTITIONED_TABLE)
1864 show_column_details = true;
1865
1866 /*
1867 * Get per-column info
1868 *
1869 * Since the set of query columns we need varies depending on relkind and
1870 * server version, we compute all the column numbers on-the-fly. Column
1871 * number variables for columns not fetched are left as -1; this avoids
1872 * duplicative test logic below.
1873 */
1874 cols = 0;
1875 printfPQExpBuffer(&buf, "SELECT a.attname");
1876 attname_col = cols++;
1877 appendPQExpBufferStr(&buf, ",\n pg_catalog.format_type(a.atttypid, a.atttypmod)");
1878 atttype_col = cols++;
1879
1880 if (show_column_details)
1881 {
1882 /* use "pretty" mode for expression to avoid excessive parentheses */
1883 appendPQExpBufferStr(&buf,
1884 ",\n (SELECT pg_catalog.pg_get_expr(d.adbin, d.adrelid, true)"
1885 "\n FROM pg_catalog.pg_attrdef d"
1886 "\n WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef)"
1887 ",\n a.attnotnull");
1888 attrdef_col = cols++;
1889 attnotnull_col = cols++;
1890 appendPQExpBufferStr(&buf, ",\n (SELECT c.collname FROM pg_catalog.pg_collation c, pg_catalog.pg_type t\n"
1891 " WHERE c.oid = a.attcollation AND t.oid = a.atttypid AND a.attcollation <> t.typcollation) AS attcollation");
1892 attcoll_col = cols++;
1893 if (pset.sversion >= 100000)
1894 appendPQExpBufferStr(&buf, ",\n a.attidentity");
1895 else
1896 appendPQExpBufferStr(&buf, ",\n ''::pg_catalog.char AS attidentity");
1897 attidentity_col = cols++;
1898 if (pset.sversion >= 120000)
1899 appendPQExpBufferStr(&buf, ",\n a.attgenerated");
1900 else
1901 appendPQExpBufferStr(&buf, ",\n ''::pg_catalog.char AS attgenerated");
1902 attgenerated_col = cols++;
1903 }
1904 if (tableinfo.relkind == RELKIND_INDEX ||
1905 tableinfo.relkind == RELKIND_PARTITIONED_INDEX)
1906 {
1907 if (pset.sversion >= 110000)
1908 {
1909 appendPQExpBuffer(&buf, ",\n CASE WHEN a.attnum <= (SELECT i.indnkeyatts FROM pg_catalog.pg_index i WHERE i.indexrelid = '%s') THEN '%s' ELSE '%s' END AS is_key",
1910 oid,
1911 gettext_noop("yes"),
1912 gettext_noop("no"));
1913 isindexkey_col = cols++;
1914 }
1915 appendPQExpBufferStr(&buf, ",\n pg_catalog.pg_get_indexdef(a.attrelid, a.attnum, TRUE) AS indexdef");
1916 indexdef_col = cols++;
1917 }
1918 /* FDW options for foreign table column */
1919 if (tableinfo.relkind == RELKIND_FOREIGN_TABLE)
1920 {
1921 appendPQExpBufferStr(&buf, ",\n CASE WHEN attfdwoptions IS NULL THEN '' ELSE "
1922 " '(' || pg_catalog.array_to_string(ARRAY(SELECT pg_catalog.quote_ident(option_name) || ' ' || pg_catalog.quote_literal(option_value) FROM "
1923 " pg_catalog.pg_options_to_table(attfdwoptions)), ', ') || ')' END AS attfdwoptions");
1924 fdwopts_col = cols++;
1925 }
1926 if (verbose)
1927 {
1928 appendPQExpBufferStr(&buf, ",\n a.attstorage");
1929 attstorage_col = cols++;
1930
1931 /* compression info, if relevant to relkind */
1932 if (pset.sversion >= 140000 &&
1933 !pset.hide_compression &&
1934 (tableinfo.relkind == RELKIND_RELATION ||
1935 tableinfo.relkind == RELKIND_PARTITIONED_TABLE ||
1936 tableinfo.relkind == RELKIND_MATVIEW))
1937 {
1938 appendPQExpBufferStr(&buf, ",\n a.attcompression AS attcompression");
1939 attcompression_col = cols++;
1940 }
1941
1942 /* stats target, if relevant to relkind */
1943 if (tableinfo.relkind == RELKIND_RELATION ||
1944 tableinfo.relkind == RELKIND_INDEX ||
1945 tableinfo.relkind == RELKIND_PARTITIONED_INDEX ||
1946 tableinfo.relkind == RELKIND_MATVIEW ||
1947 tableinfo.relkind == RELKIND_FOREIGN_TABLE ||
1948 tableinfo.relkind == RELKIND_PARTITIONED_TABLE)
1949 {
1950 appendPQExpBufferStr(&buf, ",\n CASE WHEN a.attstattarget=-1 THEN NULL ELSE a.attstattarget END AS attstattarget");
1951 attstattarget_col = cols++;
1952 }
1953
1954 /*
1955 * In 9.0+, we have column comments for: relations, views, composite
1956 * types, and foreign tables (cf. CommentObject() in comment.c).
1957 */
1958 if (tableinfo.relkind == RELKIND_RELATION ||
1959 tableinfo.relkind == RELKIND_VIEW ||
1960 tableinfo.relkind == RELKIND_MATVIEW ||
1961 tableinfo.relkind == RELKIND_FOREIGN_TABLE ||
1962 tableinfo.relkind == RELKIND_COMPOSITE_TYPE ||
1963 tableinfo.relkind == RELKIND_PARTITIONED_TABLE)
1964 {
1965 appendPQExpBufferStr(&buf, ",\n pg_catalog.col_description(a.attrelid, a.attnum)");
1966 attdescr_col = cols++;
1967 }
1968 }
1969
1970 appendPQExpBufferStr(&buf, "\nFROM pg_catalog.pg_attribute a");
1971 appendPQExpBuffer(&buf, "\nWHERE a.attrelid = '%s' AND a.attnum > 0 AND NOT a.attisdropped", oid);
1972 appendPQExpBufferStr(&buf, "\nORDER BY a.attnum;");
1973
1974 res = PSQLexec(buf.data);
1975 if (!res)
1976 goto error_return;
1977 numrows = PQntuples(res);
1978
1979 /* Make title */
1980 switch (tableinfo.relkind)
1981 {
1982 case RELKIND_RELATION:
1983 if (tableinfo.relpersistence == RELPERSISTENCE_UNLOGGED)
1984 printfPQExpBuffer(&title, _("Unlogged table \"%s.%s\""),
1985 schemaname, relationname);
1986 else
1987 printfPQExpBuffer(&title, _("Table \"%s.%s\""),
1988 schemaname, relationname);
1989 break;
1990 case RELKIND_VIEW:
1991 printfPQExpBuffer(&title, _("View \"%s.%s\""),
1992 schemaname, relationname);
1993 break;
1994 case RELKIND_MATVIEW:
1995 printfPQExpBuffer(&title, _("Materialized view \"%s.%s\""),
1996 schemaname, relationname);
1997 break;
1998 case RELKIND_INDEX:
1999 if (tableinfo.relpersistence == RELPERSISTENCE_UNLOGGED)
2000 printfPQExpBuffer(&title, _("Unlogged index \"%s.%s\""),
2001 schemaname, relationname);
2002 else
2003 printfPQExpBuffer(&title, _("Index \"%s.%s\""),
2004 schemaname, relationname);
2005 break;
2006 case RELKIND_PARTITIONED_INDEX:
2007 if (tableinfo.relpersistence == RELPERSISTENCE_UNLOGGED)
2008 printfPQExpBuffer(&title, _("Unlogged partitioned index \"%s.%s\""),
2009 schemaname, relationname);
2010 else
2011 printfPQExpBuffer(&title, _("Partitioned index \"%s.%s\""),
2012 schemaname, relationname);
2013 break;
2014 case RELKIND_TOASTVALUE:
2015 printfPQExpBuffer(&title, _("TOAST table \"%s.%s\""),
2016 schemaname, relationname);
2017 break;
2018 case RELKIND_COMPOSITE_TYPE:
2019 printfPQExpBuffer(&title, _("Composite type \"%s.%s\""),
2020 schemaname, relationname);
2021 break;
2022 case RELKIND_FOREIGN_TABLE:
2023 printfPQExpBuffer(&title, _("Foreign table \"%s.%s\""),
2024 schemaname, relationname);
2025 break;
2026 case RELKIND_PARTITIONED_TABLE:
2027 if (tableinfo.relpersistence == RELPERSISTENCE_UNLOGGED)
2028 printfPQExpBuffer(&title, _("Unlogged partitioned table \"%s.%s\""),
2029 schemaname, relationname);
2030 else
2031 printfPQExpBuffer(&title, _("Partitioned table \"%s.%s\""),
2032 schemaname, relationname);
2033 break;
2034 default:
2035 /* untranslated unknown relkind */
2036 printfPQExpBuffer(&title, "?%c? \"%s.%s\"",
2037 tableinfo.relkind, schemaname, relationname);
2038 break;
2039 }
2040
2041 /* Fill headers[] with the names of the columns we will output */
2042 cols = 0;
2043 headers[cols++] = gettext_noop("Column");
2044 headers[cols++] = gettext_noop("Type");
2045 if (show_column_details)
2046 {
2047 headers[cols++] = gettext_noop("Collation");
2048 headers[cols++] = gettext_noop("Nullable");
2049 headers[cols++] = gettext_noop("Default");
2050 }
2051 if (isindexkey_col >= 0)
2052 headers[cols++] = gettext_noop("Key?");
2053 if (indexdef_col >= 0)
2054 headers[cols++] = gettext_noop("Definition");
2055 if (fdwopts_col >= 0)
2056 headers[cols++] = gettext_noop("FDW options");
2057 if (attstorage_col >= 0)
2058 headers[cols++] = gettext_noop("Storage");
2059 if (attcompression_col >= 0)
2060 headers[cols++] = gettext_noop("Compression");
2061 if (attstattarget_col >= 0)
2062 headers[cols++] = gettext_noop("Stats target");
2063 if (attdescr_col >= 0)
2064 headers[cols++] = gettext_noop("Description");
2065
2066 Assert(cols <= lengthof(headers));
2067
2068 printTableInit(&cont, &myopt, title.data, cols, numrows);
2069 printTableInitialized = true;
2070
2071 for (i = 0; i < cols; i++)
2072 printTableAddHeader(&cont, headers[i], true, 'l');
2073
2074 /* Generate table cells to be printed */
2075 for (i = 0; i < numrows; i++)
2076 {
2077 /* Column */
2078 printTableAddCell(&cont, PQgetvalue(res, i, attname_col), false, false);
2079
2080 /* Type */
2081 printTableAddCell(&cont, PQgetvalue(res, i, atttype_col), false, false);
2082
2083 /* Collation, Nullable, Default */
2084 if (show_column_details)
2085 {
2086 char *identity;
2087 char *generated;
2088 char *default_str;
2089 bool mustfree = false;
2090
2091 printTableAddCell(&cont, PQgetvalue(res, i, attcoll_col), false, false);
2092
2093 printTableAddCell(&cont,
2094 strcmp(PQgetvalue(res, i, attnotnull_col), "t") == 0 ? "not null" : "",
2095 false, false);
2096
2097 identity = PQgetvalue(res, i, attidentity_col);
2098 generated = PQgetvalue(res, i, attgenerated_col);
2099
2100 if (identity[0] == ATTRIBUTE_IDENTITY_ALWAYS)
2101 default_str = "generated always as identity";
2102 else if (identity[0] == ATTRIBUTE_IDENTITY_BY_DEFAULT)
2103 default_str = "generated by default as identity";
2104 else if (generated[0] == ATTRIBUTE_GENERATED_STORED)
2105 {
2106 default_str = psprintf("generated always as (%s) stored",
2107 PQgetvalue(res, i, attrdef_col));
2108 mustfree = true;
2109 }
2110 else
2111 default_str = PQgetvalue(res, i, attrdef_col);
2112
2113 printTableAddCell(&cont, default_str, false, mustfree);
2114 }
2115
2116 /* Info for index columns */
2117 if (isindexkey_col >= 0)
2118 printTableAddCell(&cont, PQgetvalue(res, i, isindexkey_col), true, false);
2119 if (indexdef_col >= 0)
2120 printTableAddCell(&cont, PQgetvalue(res, i, indexdef_col), false, false);
2121
2122 /* FDW options for foreign table columns */
2123 if (fdwopts_col >= 0)
2124 printTableAddCell(&cont, PQgetvalue(res, i, fdwopts_col), false, false);
2125
2126 /* Storage mode, if relevant */
2127 if (attstorage_col >= 0)
2128 {
2129 char *storage = PQgetvalue(res, i, attstorage_col);
2130
2131 /* these strings are literal in our syntax, so not translated. */
2132 printTableAddCell(&cont, (storage[0] == TYPSTORAGE_PLAIN ? "plain" :
2133 (storage[0] == TYPSTORAGE_MAIN ? "main" :
2134 (storage[0] == TYPSTORAGE_EXTENDED ? "extended" :
2135 (storage[0] == TYPSTORAGE_EXTERNAL ? "external" :
2136 "???")))),
2137 false, false);
2138 }
2139
2140 /* Column compression, if relevant */
2141 if (attcompression_col >= 0)
2142 {
2143 char *compression = PQgetvalue(res, i, attcompression_col);
2144
2145 /* these strings are literal in our syntax, so not translated. */
2146 printTableAddCell(&cont, (compression[0] == 'p' ? "pglz" :
2147 (compression[0] == 'l' ? "lz4" :
2148 (compression[0] == '\0' ? "" :
2149 "???"))),
2150 false, false);
2151 }
2152
2153 /* Statistics target, if the relkind supports this feature */
2154 if (attstattarget_col >= 0)
2155 printTableAddCell(&cont, PQgetvalue(res, i, attstattarget_col),
2156 false, false);
2157
2158 /* Column comments, if the relkind supports this feature */
2159 if (attdescr_col >= 0)
2160 printTableAddCell(&cont, PQgetvalue(res, i, attdescr_col),
2161 false, false);
2162 }
2163
2164 /* Make footers */
2165
2166 if (tableinfo.ispartition)
2167 {
2168 /* Footer information for a partition child table */
2169 PGresult *result;
2170
2171 printfPQExpBuffer(&buf,
2172 "SELECT inhparent::pg_catalog.regclass,\n"
2173 " pg_catalog.pg_get_expr(c.relpartbound, c.oid),\n ");
2174
2175 appendPQExpBufferStr(&buf,
2176 pset.sversion >= 140000 ? "inhdetachpending" :
2177 "false as inhdetachpending");
2178
2179 /* If verbose, also request the partition constraint definition */
2180 if (verbose)
2181 appendPQExpBufferStr(&buf,
2182 ",\n pg_catalog.pg_get_partition_constraintdef(c.oid)");
2183 appendPQExpBuffer(&buf,
2184 "\nFROM pg_catalog.pg_class c"
2185 " JOIN pg_catalog.pg_inherits i"
2186 " ON c.oid = inhrelid"
2187 "\nWHERE c.oid = '%s';", oid);
2188 result = PSQLexec(buf.data);
2189 if (!result)
2190 goto error_return;
2191
2192 if (PQntuples(result) > 0)
2193 {
2194 char *parent_name = PQgetvalue(result, 0, 0);
2195 char *partdef = PQgetvalue(result, 0, 1);
2196 char *detached = PQgetvalue(result, 0, 2);
2197
2198 printfPQExpBuffer(&tmpbuf, _("Partition of: %s %s%s"), parent_name,
2199 partdef,
2200 strcmp(detached, "t") == 0 ? " DETACH PENDING" : "");
2201 printTableAddFooter(&cont, tmpbuf.data);
2202
2203 if (verbose)
2204 {
2205 char *partconstraintdef = NULL;
2206
2207 if (!PQgetisnull(result, 0, 3))
2208 partconstraintdef = PQgetvalue(result, 0, 3);
2209 /* If there isn't any constraint, show that explicitly */
2210 if (partconstraintdef == NULL || partconstraintdef[0] == '\0')
2211 printfPQExpBuffer(&tmpbuf, _("No partition constraint"));
2212 else
2213 printfPQExpBuffer(&tmpbuf, _("Partition constraint: %s"),
2214 partconstraintdef);
2215 printTableAddFooter(&cont, tmpbuf.data);
2216 }
2217 }
2218 PQclear(result);
2219 }
2220
2221 if (tableinfo.relkind == RELKIND_PARTITIONED_TABLE)
2222 {
2223 /* Footer information for a partitioned table (partitioning parent) */
2224 PGresult *result;
2225
2226 printfPQExpBuffer(&buf,
2227 "SELECT pg_catalog.pg_get_partkeydef('%s'::pg_catalog.oid);",
2228 oid);
2229 result = PSQLexec(buf.data);
2230 if (!result)
2231 goto error_return;
2232
2233 if (PQntuples(result) == 1)
2234 {
2235 char *partkeydef = PQgetvalue(result, 0, 0);
2236
2237 printfPQExpBuffer(&tmpbuf, _("Partition key: %s"), partkeydef);
2238 printTableAddFooter(&cont, tmpbuf.data);
2239 }
2240 PQclear(result);
2241 }
2242
2243 if (tableinfo.relkind == RELKIND_TOASTVALUE)
2244 {
2245 /* For a TOAST table, print name of owning table */
2246 PGresult *result;
2247
2248 printfPQExpBuffer(&buf,
2249 "SELECT n.nspname, c.relname\n"
2250 "FROM pg_catalog.pg_class c"
2251 " JOIN pg_catalog.pg_namespace n"
2252 " ON n.oid = c.relnamespace\n"
2253 "WHERE reltoastrelid = '%s';", oid);
2254 result = PSQLexec(buf.data);
2255 if (!result)
2256 goto error_return;
2257
2258 if (PQntuples(result) == 1)
2259 {
2260 char *schemaname = PQgetvalue(result, 0, 0);
2261 char *relname = PQgetvalue(result, 0, 1);
2262
2263 printfPQExpBuffer(&tmpbuf, _("Owning table: \"%s.%s\""),
2264 schemaname, relname);
2265 printTableAddFooter(&cont, tmpbuf.data);
2266 }
2267 PQclear(result);
2268 }
2269
2270 if (tableinfo.relkind == RELKIND_INDEX ||
2271 tableinfo.relkind == RELKIND_PARTITIONED_INDEX)
2272 {
2273 /* Footer information about an index */
2274 PGresult *result;
2275
2276 printfPQExpBuffer(&buf,
2277 "SELECT i.indisunique, i.indisprimary, i.indisclustered, "
2278 "i.indisvalid,\n"
2279 " (NOT i.indimmediate) AND "
2280 "EXISTS (SELECT 1 FROM pg_catalog.pg_constraint "
2281 "WHERE conrelid = i.indrelid AND "
2282 "conindid = i.indexrelid AND "
2283 "contype IN (" CppAsString2(CONSTRAINT_PRIMARY) ","
2284 CppAsString2(CONSTRAINT_UNIQUE) ","
2285 CppAsString2(CONSTRAINT_EXCLUSION) ") AND "
2286 "condeferrable) AS condeferrable,\n"
2287 " (NOT i.indimmediate) AND "
2288 "EXISTS (SELECT 1 FROM pg_catalog.pg_constraint "
2289 "WHERE conrelid = i.indrelid AND "
2290 "conindid = i.indexrelid AND "
2291 "contype IN (" CppAsString2(CONSTRAINT_PRIMARY) ","
2292 CppAsString2(CONSTRAINT_UNIQUE) ","
2293 CppAsString2(CONSTRAINT_EXCLUSION) ") AND "
2294 "condeferred) AS condeferred,\n");
2295
2296 if (pset.sversion >= 90400)
2297 appendPQExpBufferStr(&buf, "i.indisreplident,\n");
2298 else
2299 appendPQExpBufferStr(&buf, "false AS indisreplident,\n");
2300
2301 if (pset.sversion >= 150000)
2302 appendPQExpBufferStr(&buf, "i.indnullsnotdistinct,\n");
2303 else
2304 appendPQExpBufferStr(&buf, "false AS indnullsnotdistinct,\n");
2305
2306 appendPQExpBuffer(&buf, " a.amname, c2.relname, "
2307 "pg_catalog.pg_get_expr(i.indpred, i.indrelid, true)\n"
2308 "FROM pg_catalog.pg_index i, pg_catalog.pg_class c, pg_catalog.pg_class c2, pg_catalog.pg_am a\n"
2309 "WHERE i.indexrelid = c.oid AND c.oid = '%s' AND c.relam = a.oid\n"
2310 "AND i.indrelid = c2.oid;",
2311 oid);
2312
2313 result = PSQLexec(buf.data);
2314 if (!result)
2315 goto error_return;
2316 else if (PQntuples(result) != 1)
2317 {
2318 PQclear(result);
2319 goto error_return;
2320 }
2321 else
2322 {
2323 char *indisunique = PQgetvalue(result, 0, 0);
2324 char *indisprimary = PQgetvalue(result, 0, 1);
2325 char *indisclustered = PQgetvalue(result, 0, 2);
2326 char *indisvalid = PQgetvalue(result, 0, 3);
2327 char *deferrable = PQgetvalue(result, 0, 4);
2328 char *deferred = PQgetvalue(result, 0, 5);
2329 char *indisreplident = PQgetvalue(result, 0, 6);
2330 char *indnullsnotdistinct = PQgetvalue(result, 0, 7);
2331 char *indamname = PQgetvalue(result, 0, 8);
2332 char *indtable = PQgetvalue(result, 0, 9);
2333 char *indpred = PQgetvalue(result, 0, 10);
2334
2335 if (strcmp(indisprimary, "t") == 0)
2336 printfPQExpBuffer(&tmpbuf, _("primary key, "));
2337 else if (strcmp(indisunique, "t") == 0)
2338 {
2339 printfPQExpBuffer(&tmpbuf, _("unique"));
2340 if (strcmp(indnullsnotdistinct, "t") == 0)
2341 appendPQExpBufferStr(&tmpbuf, _(" nulls not distinct"));
2342 appendPQExpBufferStr(&tmpbuf, _(", "));
2343 }
2344 else
2345 resetPQExpBuffer(&tmpbuf);
2346 appendPQExpBuffer(&tmpbuf, "%s, ", indamname);
2347
2348 /* we assume here that index and table are in same schema */
2349 appendPQExpBuffer(&tmpbuf, _("for table \"%s.%s\""),
2350 schemaname, indtable);
2351
2352 if (strlen(indpred))
2353 appendPQExpBuffer(&tmpbuf, _(", predicate (%s)"), indpred);
2354
2355 if (strcmp(indisclustered, "t") == 0)
2356 appendPQExpBufferStr(&tmpbuf, _(", clustered"));
2357
2358 if (strcmp(indisvalid, "t") != 0)
2359 appendPQExpBufferStr(&tmpbuf, _(", invalid"));
2360
2361 if (strcmp(deferrable, "t") == 0)
2362 appendPQExpBufferStr(&tmpbuf, _(", deferrable"));
2363
2364 if (strcmp(deferred, "t") == 0)
2365 appendPQExpBufferStr(&tmpbuf, _(", initially deferred"));
2366
2367 if (strcmp(indisreplident, "t") == 0)
2368 appendPQExpBufferStr(&tmpbuf, _(", replica identity"));
2369
2370 printTableAddFooter(&cont, tmpbuf.data);
2371
2372 /*
2373 * If it's a partitioned index, we'll print the tablespace below
2374 */
2375 if (tableinfo.relkind == RELKIND_INDEX)
2376 add_tablespace_footer(&cont, tableinfo.relkind,
2377 tableinfo.tablespace, true);
2378 }
2379
2380 PQclear(result);
2381 }
2382 /* If you add relkinds here, see also "Finish printing..." stanza below */
2383 else if (tableinfo.relkind == RELKIND_RELATION ||
2384 tableinfo.relkind == RELKIND_MATVIEW ||
2385 tableinfo.relkind == RELKIND_FOREIGN_TABLE ||
2386 tableinfo.relkind == RELKIND_PARTITIONED_TABLE ||
2387 tableinfo.relkind == RELKIND_PARTITIONED_INDEX ||
2388 tableinfo.relkind == RELKIND_TOASTVALUE)
2389 {
2390 /* Footer information about a table */
2391 PGresult *result = NULL;
2392 int tuples = 0;
2393
2394 /* print indexes */
2395 if (tableinfo.hasindex)
2396 {
2397 printfPQExpBuffer(&buf,
2398 "SELECT c2.relname, i.indisprimary, i.indisunique, "
2399 "i.indisclustered, i.indisvalid, "
2400 "pg_catalog.pg_get_indexdef(i.indexrelid, 0, true),\n "
2401 "pg_catalog.pg_get_constraintdef(con.oid, true), "
2402 "contype, condeferrable, condeferred");
2403 if (pset.sversion >= 90400)
2404 appendPQExpBufferStr(&buf, ", i.indisreplident");
2405 else
2406 appendPQExpBufferStr(&buf, ", false AS indisreplident");
2407 appendPQExpBufferStr(&buf, ", c2.reltablespace");
2408 if (pset.sversion >= 180000)
2409 appendPQExpBufferStr(&buf, ", con.conperiod");
2410 else
2411 appendPQExpBufferStr(&buf, ", false AS conperiod");
2412 appendPQExpBuffer(&buf,
2413 "\nFROM pg_catalog.pg_class c, pg_catalog.pg_class c2, pg_catalog.pg_index i\n"
2414 " LEFT JOIN pg_catalog.pg_constraint con ON (conrelid = i.indrelid AND conindid = i.indexrelid AND contype IN ("
2415 CppAsString2(CONSTRAINT_PRIMARY) ","
2416 CppAsString2(CONSTRAINT_UNIQUE) ","
2417 CppAsString2(CONSTRAINT_EXCLUSION) "))\n"
2418 "WHERE c.oid = '%s' AND c.oid = i.indrelid AND i.indexrelid = c2.oid\n"
2419 "ORDER BY i.indisprimary DESC, c2.relname;",
2420 oid);
2421 result = PSQLexec(buf.data);
2422 if (!result)
2423 goto error_return;
2424 else
2425 tuples = PQntuples(result);
2426
2427 if (tuples > 0)
2428 {
2429 printTableAddFooter(&cont, _("Indexes:"));
2430 for (i = 0; i < tuples; i++)
2431 {
2432 /* untranslated index name */
2433 printfPQExpBuffer(&buf, " \"%s\"",
2434 PQgetvalue(result, i, 0));
2435
2436 /*
2437 * If exclusion constraint or PK/UNIQUE constraint WITHOUT
2438 * OVERLAPS, print the constraintdef
2439 */
2440 if (strcmp(PQgetvalue(result, i, 7), "x") == 0 ||
2441 strcmp(PQgetvalue(result, i, 12), "t") == 0)
2442 {
2443 appendPQExpBuffer(&buf, " %s",
2444 PQgetvalue(result, i, 6));
2445 }
2446 else
2447 {
2448 const char *indexdef;
2449 const char *usingpos;
2450
2451 /* Label as primary key or unique (but not both) */
2452 if (strcmp(PQgetvalue(result, i, 1), "t") == 0)
2453 appendPQExpBufferStr(&buf, " PRIMARY KEY,");
2454 else if (strcmp(PQgetvalue(result, i, 2), "t") == 0)
2455 {
2456 if (strcmp(PQgetvalue(result, i, 7), "u") == 0)
2457 appendPQExpBufferStr(&buf, " UNIQUE CONSTRAINT,");
2458 else
2459 appendPQExpBufferStr(&buf, " UNIQUE,");
2460 }
2461
2462 /* Everything after "USING" is echoed verbatim */
2463 indexdef = PQgetvalue(result, i, 5);
2464 usingpos = strstr(indexdef, " USING ");
2465 if (usingpos)
2466 indexdef = usingpos + 7;
2467 appendPQExpBuffer(&buf, " %s", indexdef);
2468
2469 /* Need these for deferrable PK/UNIQUE indexes */
2470 if (strcmp(PQgetvalue(result, i, 8), "t") == 0)
2471 appendPQExpBufferStr(&buf, " DEFERRABLE");
2472
2473 if (strcmp(PQgetvalue(result, i, 9), "t") == 0)
2474 appendPQExpBufferStr(&buf, " INITIALLY DEFERRED");
2475 }
2476
2477 /* Add these for all cases */
2478 if (strcmp(PQgetvalue(result, i, 3), "t") == 0)
2479 appendPQExpBufferStr(&buf, " CLUSTER");
2480
2481 if (strcmp(PQgetvalue(result, i, 4), "t") != 0)
2482 appendPQExpBufferStr(&buf, " INVALID");
2483
2484 if (strcmp(PQgetvalue(result, i, 10), "t") == 0)
2485 appendPQExpBufferStr(&buf, " REPLICA IDENTITY");
2486
2487 printTableAddFooter(&cont, buf.data);
2488
2489 /* Print tablespace of the index on the same line */
2490 add_tablespace_footer(&cont, RELKIND_INDEX,
2491 atooid(PQgetvalue(result, i, 11)),
2492 false);
2493 }
2494 }
2495 PQclear(result);
2496 }
2497
2498 /* print table (and column) check constraints */
2499 if (tableinfo.checks)
2500 {
2501 printfPQExpBuffer(&buf,
2502 "SELECT r.conname, "
2503 "pg_catalog.pg_get_constraintdef(r.oid, true)\n"
2504 "FROM pg_catalog.pg_constraint r\n"
2505 "WHERE r.conrelid = '%s' "
2506 "AND r.contype = " CppAsString2(CONSTRAINT_CHECK) "\n"
2507 "ORDER BY 1;",
2508 oid);
2509 result = PSQLexec(buf.data);
2510 if (!result)
2511 goto error_return;
2512 else
2513 tuples = PQntuples(result);
2514
2515 if (tuples > 0)
2516 {
2517 printTableAddFooter(&cont, _("Check constraints:"));
2518 for (i = 0; i < tuples; i++)
2519 {
2520 /* untranslated constraint name and def */
2521 printfPQExpBuffer(&buf, " \"%s\" %s",
2522 PQgetvalue(result, i, 0),
2523 PQgetvalue(result, i, 1));
2524
2525 printTableAddFooter(&cont, buf.data);
2526 }
2527 }
2528 PQclear(result);
2529 }
2530
2531 /*
2532 * Print foreign-key constraints (there are none if no triggers,
2533 * except if the table is partitioned, in which case the triggers
2534 * appear in the partitions)
2535 */
2536 if (tableinfo.hastriggers ||
2537 tableinfo.relkind == RELKIND_PARTITIONED_TABLE)
2538 {
2539 if (pset.sversion >= 120000 &&
2540 (tableinfo.ispartition || tableinfo.relkind == RELKIND_PARTITIONED_TABLE))
2541 {
2542 /*
2543 * Put the constraints defined in this table first, followed
2544 * by the constraints defined in ancestor partitioned tables.
2545 */
2546 printfPQExpBuffer(&buf,
2547 "SELECT conrelid = '%s'::pg_catalog.regclass AS sametable,\n"
2548 " conname,\n"
2549 " pg_catalog.pg_get_constraintdef(oid, true) AS condef,\n"
2550 " conrelid::pg_catalog.regclass AS ontable\n"
2551 " FROM pg_catalog.pg_constraint,\n"
2552 " pg_catalog.pg_partition_ancestors('%s')\n"
2553 " WHERE conrelid = relid AND contype = " CppAsString2(CONSTRAINT_FOREIGN) " AND conparentid = 0\n"
2554 "ORDER BY sametable DESC, conname;",
2555 oid, oid);
2556 }
2557 else
2558 {
2559 printfPQExpBuffer(&buf,
2560 "SELECT true as sametable, conname,\n"
2561 " pg_catalog.pg_get_constraintdef(r.oid, true) as condef,\n"
2562 " conrelid::pg_catalog.regclass AS ontable\n"
2563 "FROM pg_catalog.pg_constraint r\n"
2564 "WHERE r.conrelid = '%s' AND r.contype = " CppAsString2(CONSTRAINT_FOREIGN) "\n",
2565 oid);
2566
2567 if (pset.sversion >= 120000)
2568 appendPQExpBufferStr(&buf, " AND conparentid = 0\n");
2569 appendPQExpBufferStr(&buf, "ORDER BY conname");
2570 }
2571
2572 result = PSQLexec(buf.data);
2573 if (!result)
2574 goto error_return;
2575 else
2576 tuples = PQntuples(result);
2577
2578 if (tuples > 0)
2579 {
2580 int i_sametable = PQfnumber(result, "sametable"),
2581 i_conname = PQfnumber(result, "conname"),
2582 i_condef = PQfnumber(result, "condef"),
2583 i_ontable = PQfnumber(result, "ontable");
2584
2585 printTableAddFooter(&cont, _("Foreign-key constraints:"));
2586 for (i = 0; i < tuples; i++)
2587 {
2588 /*
2589 * Print untranslated constraint name and definition. Use
2590 * a "TABLE tab" prefix when the constraint is defined in
2591 * a parent partitioned table.
2592 */
2593 if (strcmp(PQgetvalue(result, i, i_sametable), "f") == 0)
2594 printfPQExpBuffer(&buf, " TABLE \"%s\" CONSTRAINT \"%s\" %s",
2595 PQgetvalue(result, i, i_ontable),
2596 PQgetvalue(result, i, i_conname),
2597 PQgetvalue(result, i, i_condef));
2598 else
2599 printfPQExpBuffer(&buf, " \"%s\" %s",
2600 PQgetvalue(result, i, i_conname),
2601 PQgetvalue(result, i, i_condef));
2602
2603 printTableAddFooter(&cont, buf.data);
2604 }
2605 }
2606 PQclear(result);
2607 }
2608
2609 /* print incoming foreign-key references */
2610 if (tableinfo.hastriggers ||
2611 tableinfo.relkind == RELKIND_PARTITIONED_TABLE)
2612 {
2613 if (pset.sversion >= 120000)
2614 {
2615 printfPQExpBuffer(&buf,
2616 "SELECT conname, conrelid::pg_catalog.regclass AS ontable,\n"
2617 " pg_catalog.pg_get_constraintdef(oid, true) AS condef\n"
2618 " FROM pg_catalog.pg_constraint c\n"
2619 " WHERE confrelid IN (SELECT pg_catalog.pg_partition_ancestors('%s')\n"
2620 " UNION ALL VALUES ('%s'::pg_catalog.regclass))\n"
2621 " AND contype = " CppAsString2(CONSTRAINT_FOREIGN) " AND conparentid = 0\n"
2622 "ORDER BY conname;",
2623 oid, oid);
2624 }
2625 else
2626 {
2627 printfPQExpBuffer(&buf,
2628 "SELECT conname, conrelid::pg_catalog.regclass AS ontable,\n"
2629 " pg_catalog.pg_get_constraintdef(oid, true) AS condef\n"
2630 " FROM pg_catalog.pg_constraint\n"
2631 " WHERE confrelid = %s AND contype = " CppAsString2(CONSTRAINT_FOREIGN) "\n"
2632 "ORDER BY conname;",
2633 oid);
2634 }
2635
2636 result = PSQLexec(buf.data);
2637 if (!result)
2638 goto error_return;
2639 else
2640 tuples = PQntuples(result);
2641
2642 if (tuples > 0)
2643 {
2644 int i_conname = PQfnumber(result, "conname"),
2645 i_ontable = PQfnumber(result, "ontable"),
2646 i_condef = PQfnumber(result, "condef");
2647
2648 printTableAddFooter(&cont, _("Referenced by:"));
2649 for (i = 0; i < tuples; i++)
2650 {
2651 printfPQExpBuffer(&buf, " TABLE \"%s\" CONSTRAINT \"%s\" %s",
2652 PQgetvalue(result, i, i_ontable),
2653 PQgetvalue(result, i, i_conname),
2654 PQgetvalue(result, i, i_condef));
2655
2656 printTableAddFooter(&cont, buf.data);
2657 }
2658 }
2659 PQclear(result);
2660 }
2661
2662 /* print any row-level policies */
2663 if (pset.sversion >= 90500)
2664 {
2665 printfPQExpBuffer(&buf, "SELECT pol.polname,");
2666 if (pset.sversion >= 100000)
2667 appendPQExpBufferStr(&buf,
2668 " pol.polpermissive,\n");
2669 else
2670 appendPQExpBufferStr(&buf,
2671 " 't' as polpermissive,\n");
2672 appendPQExpBuffer(&buf,
2673 " 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"
2674 " pg_catalog.pg_get_expr(pol.polqual, pol.polrelid),\n"
2675 " pg_catalog.pg_get_expr(pol.polwithcheck, pol.polrelid),\n"
2676 " CASE pol.polcmd\n"
2677 " WHEN 'r' THEN 'SELECT'\n"
2678 " WHEN 'a' THEN 'INSERT'\n"
2679 " WHEN 'w' THEN 'UPDATE'\n"
2680 " WHEN 'd' THEN 'DELETE'\n"
2681 " END AS cmd\n"
2682 "FROM pg_catalog.pg_policy pol\n"
2683 "WHERE pol.polrelid = '%s' ORDER BY 1;",
2684 oid);
2685
2686 result = PSQLexec(buf.data);
2687 if (!result)
2688 goto error_return;
2689 else
2690 tuples = PQntuples(result);
2691
2692 /*
2693 * Handle cases where RLS is enabled and there are policies, or
2694 * there aren't policies, or RLS isn't enabled but there are
2695 * policies
2696 */
2697 if (tableinfo.rowsecurity && !tableinfo.forcerowsecurity && tuples > 0)
2698 printTableAddFooter(&cont, _("Policies:"));
2699
2700 if (tableinfo.rowsecurity && tableinfo.forcerowsecurity && tuples > 0)
2701 printTableAddFooter(&cont, _("Policies (forced row security enabled):"));
2702
2703 if (tableinfo.rowsecurity && !tableinfo.forcerowsecurity && tuples == 0)
2704 printTableAddFooter(&cont, _("Policies (row security enabled): (none)"));
2705
2706 if (tableinfo.rowsecurity && tableinfo.forcerowsecurity && tuples == 0)
2707 printTableAddFooter(&cont, _("Policies (forced row security enabled): (none)"));
2708
2709 if (!tableinfo.rowsecurity && tuples > 0)
2710 printTableAddFooter(&cont, _("Policies (row security disabled):"));
2711
2712 /* Might be an empty set - that's ok */
2713 for (i = 0; i < tuples; i++)
2714 {
2715 printfPQExpBuffer(&buf, " POLICY \"%s\"",
2716 PQgetvalue(result, i, 0));
2717
2718 if (*(PQgetvalue(result, i, 1)) == 'f')
2719 appendPQExpBufferStr(&buf, " AS RESTRICTIVE");
2720
2721 if (!PQgetisnull(result, i, 5))
2722 appendPQExpBuffer(&buf, " FOR %s",
2723 PQgetvalue(result, i, 5));
2724
2725 if (!PQgetisnull(result, i, 2))
2726 {
2727 appendPQExpBuffer(&buf, "\n TO %s",
2728 PQgetvalue(result, i, 2));
2729 }
2730
2731 if (!PQgetisnull(result, i, 3))
2732 appendPQExpBuffer(&buf, "\n USING (%s)",
2733 PQgetvalue(result, i, 3));
2734
2735 if (!PQgetisnull(result, i, 4))
2736 appendPQExpBuffer(&buf, "\n WITH CHECK (%s)",
2737 PQgetvalue(result, i, 4));
2738
2739 printTableAddFooter(&cont, buf.data);
2740 }
2741 PQclear(result);
2742 }
2743
2744 /* print any extended statistics */
2745 if (pset.sversion >= 140000)
2746 {
2747 printfPQExpBuffer(&buf,
2748 "SELECT oid, "
2749 "stxrelid::pg_catalog.regclass, "
2750 "stxnamespace::pg_catalog.regnamespace::pg_catalog.text AS nsp, "
2751 "stxname,\n"
2752 "pg_catalog.pg_get_statisticsobjdef_columns(oid) AS columns,\n"
2753 " " CppAsString2(STATS_EXT_NDISTINCT) " = any(stxkind) AS ndist_enabled,\n"
2754 " " CppAsString2(STATS_EXT_DEPENDENCIES) " = any(stxkind) AS deps_enabled,\n"
2755 " " CppAsString2(STATS_EXT_MCV) " = any(stxkind) AS mcv_enabled,\n"
2756 "stxstattarget\n"
2757 "FROM pg_catalog.pg_statistic_ext\n"
2758 "WHERE stxrelid = '%s'\n"
2759 "ORDER BY nsp, stxname;",
2760 oid);
2761
2762 result = PSQLexec(buf.data);
2763 if (!result)
2764 goto error_return;
2765 else
2766 tuples = PQntuples(result);
2767
2768 if (tuples > 0)
2769 {
2770 printTableAddFooter(&cont, _("Statistics objects:"));
2771
2772 for (i = 0; i < tuples; i++)
2773 {
2774 bool gotone = false;
2775 bool has_ndistinct;
2776 bool has_dependencies;
2777 bool has_mcv;
2778 bool has_all;
2779 bool has_some;
2780
2781 has_ndistinct = (strcmp(PQgetvalue(result, i, 5), "t") == 0);
2782 has_dependencies = (strcmp(PQgetvalue(result, i, 6), "t") == 0);
2783 has_mcv = (strcmp(PQgetvalue(result, i, 7), "t") == 0);
2784
2785 printfPQExpBuffer(&buf, " ");
2786
2787 /* statistics object name (qualified with namespace) */
2788 appendPQExpBuffer(&buf, "\"%s.%s\"",
2789 PQgetvalue(result, i, 2),
2790 PQgetvalue(result, i, 3));
2791
2792 /*
2793 * When printing kinds we ignore expression statistics,
2794 * which are used only internally and can't be specified
2795 * by user. We don't print the kinds when none are
2796 * specified (in which case it has to be statistics on a
2797 * single expr) or when all are specified (in which case
2798 * we assume it's expanded by CREATE STATISTICS).
2799 */
2800 has_all = (has_ndistinct && has_dependencies && has_mcv);
2801 has_some = (has_ndistinct || has_dependencies || has_mcv);
2802
2803 if (has_some && !has_all)
2804 {
2805 appendPQExpBufferStr(&buf, " (");
2806
2807 /* options */
2808 if (has_ndistinct)
2809 {
2810 appendPQExpBufferStr(&buf, "ndistinct");
2811 gotone = true;
2812 }
2813
2814 if (has_dependencies)
2815 {
2816 appendPQExpBuffer(&buf, "%sdependencies", gotone ? ", " : "");
2817 gotone = true;
2818 }
2819
2820 if (has_mcv)
2821 {
2822 appendPQExpBuffer(&buf, "%smcv", gotone ? ", " : "");
2823 }
2824
2825 appendPQExpBufferChar(&buf, ')');
2826 }
2827
2828 appendPQExpBuffer(&buf, " ON %s FROM %s",
2829 PQgetvalue(result, i, 4),
2830 PQgetvalue(result, i, 1));
2831
2832 /* Show the stats target if it's not default */
2833 if (!PQgetisnull(result, i, 8) &&
2834 strcmp(PQgetvalue(result, i, 8), "-1") != 0)
2835 appendPQExpBuffer(&buf, "; STATISTICS %s",
2836 PQgetvalue(result, i, 8));
2837
2838 printTableAddFooter(&cont, buf.data);
2839 }
2840 }
2841 PQclear(result);
2842 }
2843 else if (pset.sversion >= 100000)
2844 {
2845 printfPQExpBuffer(&buf,
2846 "SELECT oid, "
2847 "stxrelid::pg_catalog.regclass, "
2848 "stxnamespace::pg_catalog.regnamespace AS nsp, "
2849 "stxname,\n"
2850 " (SELECT pg_catalog.string_agg(pg_catalog.quote_ident(attname),', ')\n"
2851 " FROM pg_catalog.unnest(stxkeys) s(attnum)\n"
2852 " JOIN pg_catalog.pg_attribute a ON (stxrelid = a.attrelid AND\n"
2853 " a.attnum = s.attnum AND NOT attisdropped)) AS columns,\n"
2854 " " CppAsString2(STATS_EXT_NDISTINCT) " = any(stxkind) AS ndist_enabled,\n"
2855 " " CppAsString2(STATS_EXT_DEPENDENCIES) " = any(stxkind) AS deps_enabled,\n"
2856 " " CppAsString2(STATS_EXT_MCV) " = any(stxkind) AS mcv_enabled,\n");
2857
2858 if (pset.sversion >= 130000)
2859 appendPQExpBufferStr(&buf, " stxstattarget\n");
2860 else
2861 appendPQExpBufferStr(&buf, " -1 AS stxstattarget\n");
2862 appendPQExpBuffer(&buf, "FROM pg_catalog.pg_statistic_ext\n"
2863 "WHERE stxrelid = '%s'\n"
2864 "ORDER BY 1;",
2865 oid);
2866
2867 result = PSQLexec(buf.data);
2868 if (!result)
2869 goto error_return;
2870 else
2871 tuples = PQntuples(result);
2872
2873 if (tuples > 0)
2874 {
2875 printTableAddFooter(&cont, _("Statistics objects:"));
2876
2877 for (i = 0; i < tuples; i++)
2878 {
2879 bool gotone = false;
2880
2881 printfPQExpBuffer(&buf, " ");
2882
2883 /* statistics object name (qualified with namespace) */
2884 appendPQExpBuffer(&buf, "\"%s.%s\" (",
2885 PQgetvalue(result, i, 2),
2886 PQgetvalue(result, i, 3));
2887
2888 /* options */
2889 if (strcmp(PQgetvalue(result, i, 5), "t") == 0)
2890 {
2891 appendPQExpBufferStr(&buf, "ndistinct");
2892 gotone = true;
2893 }
2894
2895 if (strcmp(PQgetvalue(result, i, 6), "t") == 0)
2896 {
2897 appendPQExpBuffer(&buf, "%sdependencies", gotone ? ", " : "");
2898 gotone = true;
2899 }
2900
2901 if (strcmp(PQgetvalue(result, i, 7), "t") == 0)
2902 {
2903 appendPQExpBuffer(&buf, "%smcv", gotone ? ", " : "");
2904 }
2905
2906 appendPQExpBuffer(&buf, ") ON %s FROM %s",
2907 PQgetvalue(result, i, 4),
2908 PQgetvalue(result, i, 1));
2909
2910 /* Show the stats target if it's not default */
2911 if (strcmp(PQgetvalue(result, i, 8), "-1") != 0)
2912 appendPQExpBuffer(&buf, "; STATISTICS %s",
2913 PQgetvalue(result, i, 8));
2914
2915 printTableAddFooter(&cont, buf.data);
2916 }
2917 }
2918 PQclear(result);
2919 }
2920
2921 /* print rules */
2922 if (tableinfo.hasrules && tableinfo.relkind != RELKIND_MATVIEW)
2923 {
2924 printfPQExpBuffer(&buf,
2925 "SELECT r.rulename, trim(trailing ';' from pg_catalog.pg_get_ruledef(r.oid, true)), "
2926 "ev_enabled\n"
2927 "FROM pg_catalog.pg_rewrite r\n"
2928 "WHERE r.ev_class = '%s' ORDER BY 1;",
2929 oid);
2930 result = PSQLexec(buf.data);
2931 if (!result)
2932 goto error_return;
2933 else
2934 tuples = PQntuples(result);
2935
2936 if (tuples > 0)
2937 {
2938 bool have_heading;
2939 int category;
2940
2941 for (category = 0; category < 4; category++)
2942 {
2943 have_heading = false;
2944
2945 for (i = 0; i < tuples; i++)
2946 {
2947 const char *ruledef;
2948 bool list_rule = false;
2949
2950 switch (category)
2951 {
2952 case 0:
2953 if (*PQgetvalue(result, i, 2) == 'O')
2954 list_rule = true;
2955 break;
2956 case 1:
2957 if (*PQgetvalue(result, i, 2) == 'D')
2958 list_rule = true;
2959 break;
2960 case 2:
2961 if (*PQgetvalue(result, i, 2) == 'A')
2962 list_rule = true;
2963 break;
2964 case 3:
2965 if (*PQgetvalue(result, i, 2) == 'R')
2966 list_rule = true;
2967 break;
2968 }
2969 if (!list_rule)
2970 continue;
2971
2972 if (!have_heading)
2973 {
2974 switch (category)
2975 {
2976 case 0:
2977 printfPQExpBuffer(&buf, _("Rules:"));
2978 break;
2979 case 1:
2980 printfPQExpBuffer(&buf, _("Disabled rules:"));
2981 break;
2982 case 2:
2983 printfPQExpBuffer(&buf, _("Rules firing always:"));
2984 break;
2985 case 3:
2986 printfPQExpBuffer(&buf, _("Rules firing on replica only:"));
2987 break;
2988 }
2989 printTableAddFooter(&cont, buf.data);
2990 have_heading = true;
2991 }
2992
2993 /* Everything after "CREATE RULE" is echoed verbatim */
2994 ruledef = PQgetvalue(result, i, 1);
2995 ruledef += 12;
2996 printfPQExpBuffer(&buf, " %s", ruledef);
2997 printTableAddFooter(&cont, buf.data);
2998 }
2999 }
3000 }
3001 PQclear(result);
3002 }
3003
3004 /* print any publications */
3005 if (pset.sversion >= 100000)
3006 {
3007 if (pset.sversion >= 150000)
3008 {
3009 printfPQExpBuffer(&buf,
3010 "SELECT pubname\n"
3011 " , NULL\n"
3012 " , NULL\n"
3013 "FROM pg_catalog.pg_publication p\n"
3014 " JOIN pg_catalog.pg_publication_namespace pn ON p.oid = pn.pnpubid\n"
3015 " JOIN pg_catalog.pg_class pc ON pc.relnamespace = pn.pnnspid\n"
3016 "WHERE pc.oid ='%s' and pg_catalog.pg_relation_is_publishable('%s')\n"
3017 "UNION\n"
3018 "SELECT pubname\n"
3019 " , pg_get_expr(pr.prqual, c.oid)\n"
3020 " , (CASE WHEN pr.prattrs IS NOT NULL THEN\n"
3021 " (SELECT string_agg(attname, ', ')\n"
3022 " FROM pg_catalog.generate_series(0, pg_catalog.array_upper(pr.prattrs::pg_catalog.int2[], 1)) s,\n"
3023 " pg_catalog.pg_attribute\n"
3024 " WHERE attrelid = pr.prrelid AND attnum = prattrs[s])\n"
3025 " ELSE NULL END) "
3026 "FROM pg_catalog.pg_publication p\n"
3027 " JOIN pg_catalog.pg_publication_rel pr ON p.oid = pr.prpubid\n"
3028 " JOIN pg_catalog.pg_class c ON c.oid = pr.prrelid\n"
3029 "WHERE pr.prrelid = '%s'\n"
3030 "UNION\n"
3031 "SELECT pubname\n"
3032 " , NULL\n"
3033 " , NULL\n"
3034 "FROM pg_catalog.pg_publication p\n"
3035 "WHERE p.puballtables AND pg_catalog.pg_relation_is_publishable('%s')\n"
3036 "ORDER BY 1;",
3037 oid, oid, oid, oid);
3038 }
3039 else
3040 {
3041 printfPQExpBuffer(&buf,
3042 "SELECT pubname\n"
3043 " , NULL\n"
3044 " , NULL\n"
3045 "FROM pg_catalog.pg_publication p\n"
3046 "JOIN pg_catalog.pg_publication_rel pr ON p.oid = pr.prpubid\n"
3047 "WHERE pr.prrelid = '%s'\n"
3048 "UNION ALL\n"
3049 "SELECT pubname\n"
3050 " , NULL\n"
3051 " , NULL\n"
3052 "FROM pg_catalog.pg_publication p\n"
3053 "WHERE p.puballtables AND pg_catalog.pg_relation_is_publishable('%s')\n"
3054 "ORDER BY 1;",
3055 oid, oid);
3056 }
3057
3058 result = PSQLexec(buf.data);
3059 if (!result)
3060 goto error_return;
3061 else
3062 tuples = PQntuples(result);
3063
3064 if (tuples > 0)
3065 printTableAddFooter(&cont, _("Publications:"));
3066
3067 /* Might be an empty set - that's ok */
3068 for (i = 0; i < tuples; i++)
3069 {
3070 printfPQExpBuffer(&buf, " \"%s\"",
3071 PQgetvalue(result, i, 0));
3072
3073 /* column list (if any) */
3074 if (!PQgetisnull(result, i, 2))
3075 appendPQExpBuffer(&buf, " (%s)",
3076 PQgetvalue(result, i, 2));
3077
3078 /* row filter (if any) */
3079 if (!PQgetisnull(result, i, 1))
3080 appendPQExpBuffer(&buf, " WHERE %s",
3081 PQgetvalue(result, i, 1));
3082
3083 printTableAddFooter(&cont, buf.data);
3084 }
3085 PQclear(result);
3086 }
3087
3088 /*
3089 * If verbose, print NOT NULL constraints.
3090 */
3091 if (verbose)
3092 {
3093 printfPQExpBuffer(&buf,
3094 "SELECT c.conname, a.attname, c.connoinherit,\n"
3095 " c.conislocal, c.coninhcount <> 0\n"
3096 "FROM pg_catalog.pg_constraint c JOIN\n"
3097 " pg_catalog.pg_attribute a ON\n"
3098 " (a.attrelid = c.conrelid AND a.attnum = c.conkey[1])\n"
3099 "WHERE c.contype = " CppAsString2(CONSTRAINT_NOTNULL) " AND\n"
3100 " c.conrelid = '%s'::pg_catalog.regclass\n"
3101 "ORDER BY a.attnum",
3102 oid);
3103
3104 result = PSQLexec(buf.data);
3105 if (!result)
3106 goto error_return;
3107 else
3108 tuples = PQntuples(result);
3109
3110 if (tuples > 0)
3111 printTableAddFooter(&cont, _("Not-null constraints:"));
3112
3113 /* Might be an empty set - that's ok */
3114 for (i = 0; i < tuples; i++)
3115 {
3116 bool islocal = PQgetvalue(result, i, 3)[0] == 't';
3117 bool inherited = PQgetvalue(result, i, 4)[0] == 't';
3118
3119 printfPQExpBuffer(&buf, " \"%s\" NOT NULL \"%s\"%s",
3120 PQgetvalue(result, i, 0),
3121 PQgetvalue(result, i, 1),
3122 PQgetvalue(result, i, 2)[0] == 't' ?
3123 " NO INHERIT" :
3124 islocal && inherited ? _(" (local, inherited)") :
3125 inherited ? _(" (inherited)") : "");
3126
3127 printTableAddFooter(&cont, buf.data);
3128 }
3129 PQclear(result);
3130 }
3131 }
3132
3133 /* Get view_def if table is a view or materialized view */
3134 if ((tableinfo.relkind == RELKIND_VIEW ||
3135 tableinfo.relkind == RELKIND_MATVIEW) && verbose)
3136 {
3137 PGresult *result;
3138
3139 printfPQExpBuffer(&buf,
3140 "SELECT pg_catalog.pg_get_viewdef('%s'::pg_catalog.oid, true);",
3141 oid);
3142 result = PSQLexec(buf.data);
3143 if (!result)
3144 goto error_return;
3145
3146 if (PQntuples(result) > 0)
3147 view_def = pg_strdup(PQgetvalue(result, 0, 0));
3148
3149 PQclear(result);
3150 }
3151
3152 if (view_def)
3153 {
3154 PGresult *result = NULL;
3155
3156 /* Footer information about a view */
3157 printTableAddFooter(&cont, _("View definition:"));
3158 printTableAddFooter(&cont, view_def);
3159
3160 /* print rules */
3161 if (tableinfo.hasrules)
3162 {
3163 printfPQExpBuffer(&buf,
3164 "SELECT r.rulename, trim(trailing ';' from pg_catalog.pg_get_ruledef(r.oid, true))\n"
3165 "FROM pg_catalog.pg_rewrite r\n"
3166 "WHERE r.ev_class = '%s' AND r.rulename != '_RETURN' ORDER BY 1;",
3167 oid);
3168 result = PSQLexec(buf.data);
3169 if (!result)
3170 goto error_return;
3171
3172 if (PQntuples(result) > 0)
3173 {
3174 printTableAddFooter(&cont, _("Rules:"));
3175 for (i = 0; i < PQntuples(result); i++)
3176 {
3177 const char *ruledef;
3178
3179 /* Everything after "CREATE RULE" is echoed verbatim */
3180 ruledef = PQgetvalue(result, i, 1);
3181 ruledef += 12;
3182
3183 printfPQExpBuffer(&buf, " %s", ruledef);
3184 printTableAddFooter(&cont, buf.data);
3185 }
3186 }
3187 PQclear(result);
3188 }
3189 }
3190
3191 /*
3192 * Print triggers next, if any (but only user-defined triggers). This
3193 * could apply to either a table or a view.
3194 */
3195 if (tableinfo.hastriggers)
3196 {
3197 PGresult *result;
3198 int tuples;
3199
3200 printfPQExpBuffer(&buf,
3201 "SELECT t.tgname, "
3202 "pg_catalog.pg_get_triggerdef(t.oid, true), "
3203 "t.tgenabled, t.tgisinternal,\n");
3204
3205 /*
3206 * Detect whether each trigger is inherited, and if so, get the name
3207 * of the topmost table it's inherited from. We have no easy way to
3208 * do that pre-v13, for lack of the tgparentid column. Even with
3209 * tgparentid, a straightforward search for the topmost parent would
3210 * require a recursive CTE, which seems unduly expensive. We cheat a
3211 * bit by assuming parent triggers will match by tgname; then, joining
3212 * with pg_partition_ancestors() allows the planner to make use of
3213 * pg_trigger_tgrelid_tgname_index if it wishes. We ensure we find
3214 * the correct topmost parent by stopping at the first-in-partition-
3215 * ancestry-order trigger that has tgparentid = 0. (There might be
3216 * unrelated, non-inherited triggers with the same name further up the
3217 * stack, so this is important.)
3218 */
3219 if (pset.sversion >= 130000)
3220 appendPQExpBufferStr(&buf,
3221 " CASE WHEN t.tgparentid != 0 THEN\n"
3222 " (SELECT u.tgrelid::pg_catalog.regclass\n"
3223 " FROM pg_catalog.pg_trigger AS u,\n"
3224 " pg_catalog.pg_partition_ancestors(t.tgrelid) WITH ORDINALITY AS a(relid, depth)\n"
3225 " WHERE u.tgname = t.tgname AND u.tgrelid = a.relid\n"
3226 " AND u.tgparentid = 0\n"
3227 " ORDER BY a.depth LIMIT 1)\n"
3228 " END AS parent\n");
3229 else
3230 appendPQExpBufferStr(&buf, " NULL AS parent\n");
3231
3232 appendPQExpBuffer(&buf,
3233 "FROM pg_catalog.pg_trigger t\n"
3234 "WHERE t.tgrelid = '%s' AND ",
3235 oid);
3236
3237 /*
3238 * tgisinternal is set true for inherited triggers of partitions in
3239 * servers between v11 and v14, though these must still be shown to
3240 * the user. So we use another property that is true for such
3241 * inherited triggers to avoid them being hidden, which is their
3242 * dependence on another trigger.
3243 */
3244 if (pset.sversion >= 110000 && pset.sversion < 150000)
3245 appendPQExpBufferStr(&buf, "(NOT t.tgisinternal OR (t.tgisinternal AND t.tgenabled = 'D') \n"
3246 " OR EXISTS (SELECT 1 FROM pg_catalog.pg_depend WHERE objid = t.oid \n"
3247 " AND refclassid = 'pg_catalog.pg_trigger'::pg_catalog.regclass))");
3248 else
3249 /* display/warn about disabled internal triggers */
3250 appendPQExpBufferStr(&buf, "(NOT t.tgisinternal OR (t.tgisinternal AND t.tgenabled = 'D'))");
3251 appendPQExpBufferStr(&buf, "\nORDER BY 1;");
3252
3253 result = PSQLexec(buf.data);
3254 if (!result)
3255 goto error_return;
3256 else
3257 tuples = PQntuples(result);
3258
3259 if (tuples > 0)
3260 {
3261 bool have_heading;
3262 int category;
3263
3264 /*
3265 * split the output into 4 different categories. Enabled triggers,
3266 * disabled triggers and the two special ALWAYS and REPLICA
3267 * configurations.
3268 */
3269 for (category = 0; category <= 4; category++)
3270 {
3271 have_heading = false;
3272 for (i = 0; i < tuples; i++)
3273 {
3274 bool list_trigger;
3275 const char *tgdef;
3276 const char *usingpos;
3277 const char *tgenabled;
3278 const char *tgisinternal;
3279
3280 /*
3281 * Check if this trigger falls into the current category
3282 */
3283 tgenabled = PQgetvalue(result, i, 2);
3284 tgisinternal = PQgetvalue(result, i, 3);
3285 list_trigger = false;
3286 switch (category)
3287 {
3288 case 0:
3289 if (*tgenabled == 'O' || *tgenabled == 't')
3290 list_trigger = true;
3291 break;
3292 case 1:
3293 if ((*tgenabled == 'D' || *tgenabled == 'f') &&
3294 *tgisinternal == 'f')
3295 list_trigger = true;
3296 break;
3297 case 2:
3298 if ((*tgenabled == 'D' || *tgenabled == 'f') &&
3299 *tgisinternal == 't')
3300 list_trigger = true;
3301 break;
3302 case 3:
3303 if (*tgenabled == 'A')
3304 list_trigger = true;
3305 break;
3306 case 4:
3307 if (*tgenabled == 'R')
3308 list_trigger = true;
3309 break;
3310 }
3311 if (list_trigger == false)
3312 continue;
3313
3314 /* Print the category heading once */
3315 if (have_heading == false)
3316 {
3317 switch (category)
3318 {
3319 case 0:
3320 printfPQExpBuffer(&buf, _("Triggers:"));
3321 break;
3322 case 1:
3323 printfPQExpBuffer(&buf, _("Disabled user triggers:"));
3324 break;
3325 case 2:
3326 printfPQExpBuffer(&buf, _("Disabled internal triggers:"));
3327 break;
3328 case 3:
3329 printfPQExpBuffer(&buf, _("Triggers firing always:"));
3330 break;
3331 case 4:
3332 printfPQExpBuffer(&buf, _("Triggers firing on replica only:"));
3333 break;
3334 }
3335 printTableAddFooter(&cont, buf.data);
3336 have_heading = true;
3337 }
3338
3339 /* Everything after "TRIGGER" is echoed verbatim */
3340 tgdef = PQgetvalue(result, i, 1);
3341 usingpos = strstr(tgdef, " TRIGGER ");
3342 if (usingpos)
3343 tgdef = usingpos + 9;
3344
3345 printfPQExpBuffer(&buf, " %s", tgdef);
3346
3347 /* Visually distinguish inherited triggers */
3348 if (!PQgetisnull(result, i, 4))
3349 appendPQExpBuffer(&buf, ", ON TABLE %s",
3350 PQgetvalue(result, i, 4));
3351
3352 printTableAddFooter(&cont, buf.data);
3353 }
3354 }
3355 }
3356 PQclear(result);
3357 }
3358
3359 /*
3360 * Finish printing the footer information about a table.
3361 */
3362 if (tableinfo.relkind == RELKIND_RELATION ||
3363 tableinfo.relkind == RELKIND_MATVIEW ||
3364 tableinfo.relkind == RELKIND_FOREIGN_TABLE ||
3365 tableinfo.relkind == RELKIND_PARTITIONED_TABLE ||
3366 tableinfo.relkind == RELKIND_PARTITIONED_INDEX ||
3367 tableinfo.relkind == RELKIND_TOASTVALUE)
3368 {
3369 bool is_partitioned;
3370 PGresult *result;
3371 int tuples;
3372
3373 /* simplify some repeated tests below */
3374 is_partitioned = (tableinfo.relkind == RELKIND_PARTITIONED_TABLE ||
3375 tableinfo.relkind == RELKIND_PARTITIONED_INDEX);
3376
3377 /* print foreign server name */
3378 if (tableinfo.relkind == RELKIND_FOREIGN_TABLE)
3379 {
3380 char *ftoptions;
3381
3382 /* Footer information about foreign table */
3383 printfPQExpBuffer(&buf,
3384 "SELECT s.srvname,\n"
3385 " pg_catalog.array_to_string(ARRAY(\n"
3386 " SELECT pg_catalog.quote_ident(option_name)"
3387 " || ' ' || pg_catalog.quote_literal(option_value)\n"
3388 " FROM pg_catalog.pg_options_to_table(ftoptions)), ', ')\n"
3389 "FROM pg_catalog.pg_foreign_table f,\n"
3390 " pg_catalog.pg_foreign_server s\n"
3391 "WHERE f.ftrelid = '%s' AND s.oid = f.ftserver;",
3392 oid);
3393 result = PSQLexec(buf.data);
3394 if (!result)
3395 goto error_return;
3396 else if (PQntuples(result) != 1)
3397 {
3398 PQclear(result);
3399 goto error_return;
3400 }
3401
3402 /* Print server name */
3403 printfPQExpBuffer(&buf, _("Server: %s"),
3404 PQgetvalue(result, 0, 0));
3405 printTableAddFooter(&cont, buf.data);
3406
3407 /* Print per-table FDW options, if any */
3408 ftoptions = PQgetvalue(result, 0, 1);
3409 if (ftoptions && ftoptions[0] != '\0')
3410 {
3411 printfPQExpBuffer(&buf, _("FDW options: (%s)"), ftoptions);
3412 printTableAddFooter(&cont, buf.data);
3413 }
3414 PQclear(result);
3415 }
3416
3417 /* print tables inherited from (exclude partitioned parents) */
3418 printfPQExpBuffer(&buf,
3419 "SELECT c.oid::pg_catalog.regclass\n"
3420 "FROM pg_catalog.pg_class c, pg_catalog.pg_inherits i\n"
3421 "WHERE c.oid = i.inhparent AND i.inhrelid = '%s'\n"
3422 " AND c.relkind != " CppAsString2(RELKIND_PARTITIONED_TABLE)
3423 " AND c.relkind != " CppAsString2(RELKIND_PARTITIONED_INDEX)
3424 "\nORDER BY inhseqno;",
3425 oid);
3426
3427 result = PSQLexec(buf.data);
3428 if (!result)
3429 goto error_return;
3430 else
3431 {
3432 const char *s = _("Inherits");
3433 int sw = pg_wcswidth(s, strlen(s), pset.encoding);
3434
3435 tuples = PQntuples(result);
3436
3437 for (i = 0; i < tuples; i++)
3438 {
3439 if (i == 0)
3440 printfPQExpBuffer(&buf, "%s: %s",
3441 s, PQgetvalue(result, i, 0));
3442 else
3443 printfPQExpBuffer(&buf, "%*s %s",
3444 sw, "", PQgetvalue(result, i, 0));
3445 if (i < tuples - 1)
3446 appendPQExpBufferChar(&buf, ',');
3447
3448 printTableAddFooter(&cont, buf.data);
3449 }
3450
3451 PQclear(result);
3452 }
3453
3454 /* print child tables (with additional info if partitions) */
3455 if (pset.sversion >= 140000)
3456 printfPQExpBuffer(&buf,
3457 "SELECT c.oid::pg_catalog.regclass, c.relkind,"
3458 " inhdetachpending,"
3459 " pg_catalog.pg_get_expr(c.relpartbound, c.oid)\n"
3460 "FROM pg_catalog.pg_class c, pg_catalog.pg_inherits i\n"
3461 "WHERE c.oid = i.inhrelid AND i.inhparent = '%s'\n"
3462 "ORDER BY pg_catalog.pg_get_expr(c.relpartbound, c.oid) = 'DEFAULT',"
3463 " c.oid::pg_catalog.regclass::pg_catalog.text;",
3464 oid);
3465 else if (pset.sversion >= 100000)
3466 printfPQExpBuffer(&buf,
3467 "SELECT c.oid::pg_catalog.regclass, c.relkind,"
3468 " false AS inhdetachpending,"
3469 " pg_catalog.pg_get_expr(c.relpartbound, c.oid)\n"
3470 "FROM pg_catalog.pg_class c, pg_catalog.pg_inherits i\n"
3471 "WHERE c.oid = i.inhrelid AND i.inhparent = '%s'\n"
3472 "ORDER BY pg_catalog.pg_get_expr(c.relpartbound, c.oid) = 'DEFAULT',"
3473 " c.oid::pg_catalog.regclass::pg_catalog.text;",
3474 oid);
3475 else
3476 printfPQExpBuffer(&buf,
3477 "SELECT c.oid::pg_catalog.regclass, c.relkind,"
3478 " false AS inhdetachpending, NULL\n"
3479 "FROM pg_catalog.pg_class c, pg_catalog.pg_inherits i\n"
3480 "WHERE c.oid = i.inhrelid AND i.inhparent = '%s'\n"
3481 "ORDER BY c.oid::pg_catalog.regclass::pg_catalog.text;",
3482 oid);
3483
3484 result = PSQLexec(buf.data);
3485 if (!result)
3486 goto error_return;
3487 tuples = PQntuples(result);
3488
3489 /*
3490 * For a partitioned table with no partitions, always print the number
3491 * of partitions as zero, even when verbose output is expected.
3492 * Otherwise, we will not print "Partitions" section for a partitioned
3493 * table without any partitions.
3494 */
3495 if (is_partitioned && tuples == 0)
3496 {
3497 printfPQExpBuffer(&buf, _("Number of partitions: %d"), tuples);
3498 printTableAddFooter(&cont, buf.data);
3499 }
3500 else if (!verbose)
3501 {
3502 /* print the number of child tables, if any */
3503 if (tuples > 0)
3504 {
3505 if (is_partitioned)
3506 printfPQExpBuffer(&buf, _("Number of partitions: %d (Use \\d+ to list them.)"), tuples);
3507 else
3508 printfPQExpBuffer(&buf, _("Number of child tables: %d (Use \\d+ to list them.)"), tuples);
3509 printTableAddFooter(&cont, buf.data);
3510 }
3511 }
3512 else
3513 {
3514 /* display the list of child tables */
3515 const char *ct = is_partitioned ? _("Partitions") : _("Child tables");
3516 int ctw = pg_wcswidth(ct, strlen(ct), pset.encoding);
3517
3518 for (i = 0; i < tuples; i++)
3519 {
3520 char child_relkind = *PQgetvalue(result, i, 1);
3521
3522 if (i == 0)
3523 printfPQExpBuffer(&buf, "%s: %s",
3524 ct, PQgetvalue(result, i, 0));
3525 else
3526 printfPQExpBuffer(&buf, "%*s %s",
3527 ctw, "", PQgetvalue(result, i, 0));
3528 if (!PQgetisnull(result, i, 3))
3529 appendPQExpBuffer(&buf, " %s", PQgetvalue(result, i, 3));
3530 if (child_relkind == RELKIND_PARTITIONED_TABLE ||
3531 child_relkind == RELKIND_PARTITIONED_INDEX)
3532 appendPQExpBufferStr(&buf, ", PARTITIONED");
3533 else if (child_relkind == RELKIND_FOREIGN_TABLE)
3534 appendPQExpBufferStr(&buf, ", FOREIGN");
3535 if (strcmp(PQgetvalue(result, i, 2), "t") == 0)
3536 appendPQExpBufferStr(&buf, " (DETACH PENDING)");
3537 if (i < tuples - 1)
3538 appendPQExpBufferChar(&buf, ',');
3539
3540 printTableAddFooter(&cont, buf.data);
3541 }
3542 }
3543 PQclear(result);
3544
3545 /* Table type */
3546 if (tableinfo.reloftype)
3547 {
3548 printfPQExpBuffer(&buf, _("Typed table of type: %s"), tableinfo.reloftype);
3549 printTableAddFooter(&cont, buf.data);
3550 }
3551
3552 if (verbose &&
3553 (tableinfo.relkind == RELKIND_RELATION ||
3554 tableinfo.relkind == RELKIND_MATVIEW) &&
3555
3556 /*
3557 * No need to display default values; we already display a REPLICA
3558 * IDENTITY marker on indexes.
3559 */
3560 tableinfo.relreplident != REPLICA_IDENTITY_INDEX &&
3561 ((strcmp(schemaname, "pg_catalog") != 0 &&
3562 tableinfo.relreplident != REPLICA_IDENTITY_DEFAULT) ||
3563 (strcmp(schemaname, "pg_catalog") == 0 &&
3564 tableinfo.relreplident != REPLICA_IDENTITY_NOTHING)))
3565 {
3566 const char *s = _("Replica Identity");
3567
3568 printfPQExpBuffer(&buf, "%s: %s",
3569 s,
3570 tableinfo.relreplident == REPLICA_IDENTITY_FULL ? "FULL" :
3571 tableinfo.relreplident == REPLICA_IDENTITY_DEFAULT ? "NOTHING" :
3572 "???");
3573
3574 printTableAddFooter(&cont, buf.data);
3575 }
3576
3577 /* OIDs, if verbose and not a materialized view */
3578 if (verbose && tableinfo.relkind != RELKIND_MATVIEW && tableinfo.hasoids)
3579 printTableAddFooter(&cont, _("Has OIDs: yes"));
3580
3581 /* Tablespace info */
3582 add_tablespace_footer(&cont, tableinfo.relkind, tableinfo.tablespace,
3583 true);
3584
3585 /* Access method info */
3586 if (verbose && tableinfo.relam != NULL && !pset.hide_tableam)
3587 {
3588 printfPQExpBuffer(&buf, _("Access method: %s"), tableinfo.relam);
3589 printTableAddFooter(&cont, buf.data);
3590 }
3591 }
3592
3593 /* reloptions, if verbose */
3594 if (verbose &&
3595 tableinfo.reloptions && tableinfo.reloptions[0] != '\0')
3596 {
3597 const char *t = _("Options");
3598
3599 printfPQExpBuffer(&buf, "%s: %s", t, tableinfo.reloptions);
3600 printTableAddFooter(&cont, buf.data);
3601 }
3602
3603 printTable(&cont, pset.queryFout, false, pset.logfile);
3604
3605 retval = true;
3606
3607 error_return:
3608
3609 /* clean up */
3610 if (printTableInitialized)
3611 printTableCleanup(&cont);
3612 termPQExpBuffer(&buf);
3613 termPQExpBuffer(&title);
3614 termPQExpBuffer(&tmpbuf);
3615
3616 free(view_def);
3617
3618 PQclear(res);
3619
3620 return retval;
3621 }
3622
3623 /*
3624 * Add a tablespace description to a footer. If 'newline' is true, it is added
3625 * in a new line; otherwise it's appended to the current value of the last
3626 * footer.
3627 */
3628 static void
3629 add_tablespace_footer(printTableContent *const cont, char relkind,
3630 Oid tablespace, const bool newline)
3631 {
3632 /* relkinds for which we support tablespaces */
3633 if (relkind == RELKIND_RELATION ||
3634 relkind == RELKIND_MATVIEW ||
3635 relkind == RELKIND_INDEX ||
3636 relkind == RELKIND_PARTITIONED_TABLE ||
3637 relkind == RELKIND_PARTITIONED_INDEX ||
3638 relkind == RELKIND_TOASTVALUE)
3639 {
3640 /*
3641 * We ignore the database default tablespace so that users not using
3642 * tablespaces don't need to know about them.
3643 */
3644 if (tablespace != 0)
3645 {
3646 PGresult *result = NULL;
3647 PQExpBufferData buf;
3648
3649 initPQExpBuffer(&buf);
3650 printfPQExpBuffer(&buf,
3651 "SELECT spcname FROM pg_catalog.pg_tablespace\n"
3652 "WHERE oid = '%u';", tablespace);
3653 result = PSQLexec(buf.data);
3654 if (!result)
3655 {
3656 termPQExpBuffer(&buf);
3657 return;
3658 }
3659 /* Should always be the case, but.... */
3660 if (PQntuples(result) > 0)
3661 {
3662 if (newline)
3663 {
3664 /* Add the tablespace as a new footer */
3665 printfPQExpBuffer(&buf, _("Tablespace: \"%s\""),
3666 PQgetvalue(result, 0, 0));
3667 printTableAddFooter(cont, buf.data);
3668 }
3669 else
3670 {
3671 /* Append the tablespace to the latest footer */
3672 printfPQExpBuffer(&buf, "%s", cont->footer->data);
3673
3674 /*-------
3675 translator: before this string there's an index description like
3676 '"foo_pkey" PRIMARY KEY, btree (a)' */
3677 appendPQExpBuffer(&buf, _(", tablespace \"%s\""),
3678 PQgetvalue(result, 0, 0));
3679 printTableSetFooter(cont, buf.data);
3680 }
3681 }
3682 PQclear(result);
3683 termPQExpBuffer(&buf);
3684 }
3685 }
3686 }
3687
3688 /*
3689 * \du or \dg
3690 *
3691 * Describes roles. Any schema portion of the pattern is ignored.
3692 */
3693 bool
3694 describeRoles(const char *pattern, bool verbose, bool showSystem)
3695 {
3696 PQExpBufferData buf;
3697 PGresult *res;
3698 printTableContent cont;
3699 printTableOpt myopt = pset.popt.topt;
3700 int ncols = 2;
3701 int nrows = 0;
3702 int i;
3703 int conns;
3704 const char align = 'l';
3705 char **attr;
3706
3707 myopt.default_footer = false;
3708
3709 initPQExpBuffer(&buf);
3710
3711 printfPQExpBuffer(&buf,
3712 "SELECT r.rolname, r.rolsuper, r.rolinherit,\n"
3713 " r.rolcreaterole, r.rolcreatedb, r.rolcanlogin,\n"
3714 " r.rolconnlimit, r.rolvaliduntil");
3715
3716 if (verbose)
3717 {
3718 appendPQExpBufferStr(&buf, "\n, pg_catalog.shobj_description(r.oid, 'pg_authid') AS description");
3719 ncols++;
3720 }
3721 appendPQExpBufferStr(&buf, "\n, r.rolreplication");
3722
3723 if (pset.sversion >= 90500)
3724 {
3725 appendPQExpBufferStr(&buf, "\n, r.rolbypassrls");
3726 }
3727
3728 appendPQExpBufferStr(&buf, "\nFROM pg_catalog.pg_roles r\n");
3729
3730 if (!showSystem && !pattern)
3731 appendPQExpBufferStr(&buf, "WHERE r.rolname !~ '^pg_'\n");
3732
3733 if (!validateSQLNamePattern(&buf, pattern, false, false,
3734 NULL, "r.rolname", NULL, NULL,
3735 NULL, 1))
3736 {
3737 termPQExpBuffer(&buf);
3738 return false;
3739 }
3740
3741 appendPQExpBufferStr(&buf, "ORDER BY 1;");
3742
3743 res = PSQLexec(buf.data);
3744 if (!res)
3745 return false;
3746
3747 nrows = PQntuples(res);
3748 attr = pg_malloc0((nrows + 1) * sizeof(*attr));
3749
3750 printTableInit(&cont, &myopt, _("List of roles"), ncols, nrows);
3751
3752 printTableAddHeader(&cont, gettext_noop("Role name"), true, align);
3753 printTableAddHeader(&cont, gettext_noop("Attributes"), true, align);
3754
3755 if (verbose)
3756 printTableAddHeader(&cont, gettext_noop("Description"), true, align);
3757
3758 for (i = 0; i < nrows; i++)
3759 {
3760 printTableAddCell(&cont, PQgetvalue(res, i, 0), false, false);
3761
3762 resetPQExpBuffer(&buf);
3763 if (strcmp(PQgetvalue(res, i, 1), "t") == 0)
3764 add_role_attribute(&buf, _("Superuser"));
3765
3766 if (strcmp(PQgetvalue(res, i, 2), "t") != 0)
3767 add_role_attribute(&buf, _("No inheritance"));
3768
3769 if (strcmp(PQgetvalue(res, i, 3), "t") == 0)
3770 add_role_attribute(&buf, _("Create role"));
3771
3772 if (strcmp(PQgetvalue(res, i, 4), "t") == 0)
3773 add_role_attribute(&buf, _("Create DB"));
3774
3775 if (strcmp(PQgetvalue(res, i, 5), "t") != 0)
3776 add_role_attribute(&buf, _("Cannot login"));
3777
3778 if (strcmp(PQgetvalue(res, i, (verbose ? 9 : 8)), "t") == 0)
3779 add_role_attribute(&buf, _("Replication"));
3780
3781 if (pset.sversion >= 90500)
3782 if (strcmp(PQgetvalue(res, i, (verbose ? 10 : 9)), "t") == 0)
3783 add_role_attribute(&buf, _("Bypass RLS"));
3784
3785 conns = atoi(PQgetvalue(res, i, 6));
3786 if (conns >= 0)
3787 {
3788 if (buf.len > 0)
3789 appendPQExpBufferChar(&buf, '\n');
3790
3791 if (conns == 0)
3792 appendPQExpBufferStr(&buf, _("No connections"));
3793 else
3794 appendPQExpBuffer(&buf, ngettext("%d connection",
3795 "%d connections",
3796 conns),
3797 conns);
3798 }
3799
3800 if (strcmp(PQgetvalue(res, i, 7), "") != 0)
3801 {
3802 if (buf.len > 0)
3803 appendPQExpBufferChar(&buf, '\n');
3804 appendPQExpBufferStr(&buf, _("Password valid until "));
3805 appendPQExpBufferStr(&buf, PQgetvalue(res, i, 7));
3806 }
3807
3808 attr[i] = pg_strdup(buf.data);
3809
3810 printTableAddCell(&cont, attr[i], false, false);
3811
3812 if (verbose)
3813 printTableAddCell(&cont, PQgetvalue(res, i, 8), false, false);
3814 }
3815 termPQExpBuffer(&buf);
3816
3817 printTable(&cont, pset.queryFout, false, pset.logfile);
3818 printTableCleanup(&cont);
3819
3820 for (i = 0; i < nrows; i++)
3821 free(attr[i]);
3822 free(attr);
3823
3824 PQclear(res);
3825 return true;
3826 }
3827
3828 static void
3829 add_role_attribute(PQExpBuffer buf, const char *const str)
3830 {
3831 if (buf->len > 0)
3832 appendPQExpBufferStr(buf, ", ");
3833
3834 appendPQExpBufferStr(buf, str);
3835 }
3836
3837 /*
3838 * \drds
3839 */
3840 bool
3841 listDbRoleSettings(const char *pattern, const char *pattern2)
3842 {
3843 PQExpBufferData buf;
3844 PGresult *res;
3845 printQueryOpt myopt = pset.popt;
3846 bool havewhere;
3847
3848 initPQExpBuffer(&buf);
3849
3850 printfPQExpBuffer(&buf, "SELECT rolname AS \"%s\", datname AS \"%s\",\n"
3851 "pg_catalog.array_to_string(setconfig, E'\\n') AS \"%s\"\n"
3852 "FROM pg_catalog.pg_db_role_setting s\n"
3853 "LEFT JOIN pg_catalog.pg_database d ON d.oid = setdatabase\n"
3854 "LEFT JOIN pg_catalog.pg_roles r ON r.oid = setrole\n",
3855 gettext_noop("Role"),
3856 gettext_noop("Database"),
3857 gettext_noop("Settings"));
3858 if (!validateSQLNamePattern(&buf, pattern, false, false,
3859 NULL, "r.rolname", NULL, NULL, &havewhere, 1))
3860 goto error_return;
3861 if (!validateSQLNamePattern(&buf, pattern2, havewhere, false,
3862 NULL, "d.datname", NULL, NULL,
3863 NULL, 1))
3864 goto error_return;
3865 appendPQExpBufferStr(&buf, "ORDER BY 1, 2;");
3866
3867 res = PSQLexec(buf.data);
3868 termPQExpBuffer(&buf);
3869 if (!res)
3870 return false;
3871
3872 /*
3873 * Most functions in this file are content to print an empty table when
3874 * there are no matching objects. We intentionally deviate from that
3875 * here, but only in !quiet mode, because of the possibility that the user
3876 * is confused about what the two pattern arguments mean.
3877 */
3878 if (PQntuples(res) == 0 && !pset.quiet)
3879 {
3880 if (pattern && pattern2)
3881 pg_log_error("Did not find any settings for role \"%s\" and database \"%s\".",
3882 pattern, pattern2);
3883 else if (pattern)
3884 pg_log_error("Did not find any settings for role \"%s\".",
3885 pattern);
3886 else
3887 pg_log_error("Did not find any settings.");
3888 }
3889 else
3890 {
3891 myopt.title = _("List of settings");
3892 myopt.translate_header = true;
3893
3894 printQuery(res, &myopt, pset.queryFout, false, pset.logfile);
3895 }
3896
3897 PQclear(res);
3898 return true;
3899
3900 error_return:
3901 termPQExpBuffer(&buf);
3902 return false;
3903 }
3904
3905 /*
3906 * \drg
3907 * Describes role grants.
3908 */
3909 bool
3910 describeRoleGrants(const char *pattern, bool showSystem)
3911 {
3912 PQExpBufferData buf;
3913 PGresult *res;
3914 printQueryOpt myopt = pset.popt;
3915
3916 initPQExpBuffer(&buf);
3917 printfPQExpBuffer(&buf,
3918 "SELECT m.rolname AS \"%s\", r.rolname AS \"%s\",\n"
3919 " pg_catalog.concat_ws(', ',\n",
3920 gettext_noop("Role name"),
3921 gettext_noop("Member of"));
3922
3923 if (pset.sversion >= 160000)
3924 appendPQExpBufferStr(&buf,
3925 " CASE WHEN pam.admin_option THEN 'ADMIN' END,\n"
3926 " CASE WHEN pam.inherit_option THEN 'INHERIT' END,\n"
3927 " CASE WHEN pam.set_option THEN 'SET' END\n");
3928 else
3929 appendPQExpBufferStr(&buf,
3930 " CASE WHEN pam.admin_option THEN 'ADMIN' END,\n"
3931 " CASE WHEN m.rolinherit THEN 'INHERIT' END,\n"
3932 " 'SET'\n");
3933
3934 appendPQExpBuffer(&buf,
3935 " ) AS \"%s\",\n"
3936 " g.rolname AS \"%s\"\n",
3937 gettext_noop("Options"),
3938 gettext_noop("Grantor"));
3939
3940 appendPQExpBufferStr(&buf,
3941 "FROM pg_catalog.pg_roles m\n"
3942 " JOIN pg_catalog.pg_auth_members pam ON (pam.member = m.oid)\n"
3943 " LEFT JOIN pg_catalog.pg_roles r ON (pam.roleid = r.oid)\n"
3944 " LEFT JOIN pg_catalog.pg_roles g ON (pam.grantor = g.oid)\n");
3945
3946 if (!showSystem && !pattern)
3947 appendPQExpBufferStr(&buf, "WHERE m.rolname !~ '^pg_'\n");
3948
3949 if (!validateSQLNamePattern(&buf, pattern, false, false,
3950 NULL, "m.rolname", NULL, NULL,
3951 NULL, 1))
3952 {
3953 termPQExpBuffer(&buf);
3954 return false;
3955 }
3956
3957 appendPQExpBufferStr(&buf, "ORDER BY 1, 2, 4;\n");
3958
3959 res = PSQLexec(buf.data);
3960 termPQExpBuffer(&buf);
3961 if (!res)
3962 return false;
3963
3964 myopt.title = _("List of role grants");
3965 myopt.translate_header = true;
3966
3967 printQuery(res, &myopt, pset.queryFout, false, pset.logfile);
3968
3969 PQclear(res);
3970 return true;
3971 }
3972
3973
3974 /*
3975 * listTables()
3976 *
3977 * handler for \dt, \di, etc.
3978 *
3979 * tabtypes is an array of characters, specifying what info is desired:
3980 * t - tables
3981 * i - indexes
3982 * v - views
3983 * m - materialized views
3984 * s - sequences
3985 * E - foreign table (Note: different from 'f', the relkind value)
3986 * (any order of the above is fine)
3987 */
3988 bool
3989 listTables(const char *tabtypes, const char *pattern, bool verbose, bool showSystem)
3990 {
3991 bool showTables = strchr(tabtypes, 't') != NULL;
3992 bool showIndexes = strchr(tabtypes, 'i') != NULL;
3993 bool showViews = strchr(tabtypes, 'v') != NULL;
3994 bool showMatViews = strchr(tabtypes, 'm') != NULL;
3995 bool showSeq = strchr(tabtypes, 's') != NULL;
3996 bool showForeign = strchr(tabtypes, 'E') != NULL;
3997
3998 PQExpBufferData buf;
3999 PGresult *res;
4000 printQueryOpt myopt = pset.popt;
4001 int cols_so_far;
4002 bool translate_columns[] = {false, false, true, false, false, false, false, false, false};
4003
4004 /* If tabtypes is empty, we default to \dtvmsE (but see also command.c) */
4005 if (!(showTables || showIndexes || showViews || showMatViews || showSeq || showForeign))
4006 showTables = showViews = showMatViews = showSeq = showForeign = true;
4007
4008 initPQExpBuffer(&buf);
4009
4010 printfPQExpBuffer(&buf,
4011 "SELECT n.nspname as \"%s\",\n"
4012 " c.relname as \"%s\",\n"
4013 " CASE c.relkind"
4014 " WHEN " CppAsString2(RELKIND_RELATION) " THEN '%s'"
4015 " WHEN " CppAsString2(RELKIND_VIEW) " THEN '%s'"
4016 " WHEN " CppAsString2(RELKIND_MATVIEW) " THEN '%s'"
4017 " WHEN " CppAsString2(RELKIND_INDEX) " THEN '%s'"
4018 " WHEN " CppAsString2(RELKIND_SEQUENCE) " THEN '%s'"
4019 " WHEN " CppAsString2(RELKIND_TOASTVALUE) " THEN '%s'"
4020 " WHEN " CppAsString2(RELKIND_FOREIGN_TABLE) " THEN '%s'"
4021 " WHEN " CppAsString2(RELKIND_PARTITIONED_TABLE) " THEN '%s'"
4022 " WHEN " CppAsString2(RELKIND_PARTITIONED_INDEX) " THEN '%s'"
4023 " END as \"%s\",\n"
4024 " pg_catalog.pg_get_userbyid(c.relowner) as \"%s\"",
4025 gettext_noop("Schema"),
4026 gettext_noop("Name"),
4027 gettext_noop("table"),
4028 gettext_noop("view"),
4029 gettext_noop("materialized view"),
4030 gettext_noop("index"),
4031 gettext_noop("sequence"),
4032 gettext_noop("TOAST table"),
4033 gettext_noop("foreign table"),
4034 gettext_noop("partitioned table"),
4035 gettext_noop("partitioned index"),
4036 gettext_noop("Type"),
4037 gettext_noop("Owner"));
4038 cols_so_far = 4;
4039
4040 if (showIndexes)
4041 {
4042 appendPQExpBuffer(&buf,
4043 ",\n c2.relname as \"%s\"",
4044 gettext_noop("Table"));
4045 cols_so_far++;
4046 }
4047
4048 if (verbose)
4049 {
4050 /*
4051 * Show whether a relation is permanent, temporary, or unlogged.
4052 */
4053 appendPQExpBuffer(&buf,
4054 ",\n CASE c.relpersistence "
4055 "WHEN " CppAsString2(RELPERSISTENCE_PERMANENT) " THEN '%s' "
4056 "WHEN " CppAsString2(RELPERSISTENCE_TEMP) " THEN '%s' "
4057 "WHEN " CppAsString2(RELPERSISTENCE_UNLOGGED) " THEN '%s' "
4058 "END as \"%s\"",
4059 gettext_noop("permanent"),
4060 gettext_noop("temporary"),
4061 gettext_noop("unlogged"),
4062 gettext_noop("Persistence"));
4063 translate_columns[cols_so_far] = true;
4064
4065 /*
4066 * We don't bother to count cols_so_far below here, as there's no need
4067 * to; this might change with future additions to the output columns.
4068 */
4069
4070 /*
4071 * Access methods exist for tables, materialized views and indexes.
4072 * This has been introduced in PostgreSQL 12 for tables.
4073 */
4074 if (pset.sversion >= 120000 && !pset.hide_tableam &&
4075 (showTables || showMatViews || showIndexes))
4076 appendPQExpBuffer(&buf,
4077 ",\n am.amname as \"%s\"",
4078 gettext_noop("Access method"));
4079
4080 appendPQExpBuffer(&buf,
4081 ",\n pg_catalog.pg_size_pretty(pg_catalog.pg_table_size(c.oid)) as \"%s\""
4082 ",\n pg_catalog.obj_description(c.oid, 'pg_class') as \"%s\"",
4083 gettext_noop("Size"),
4084 gettext_noop("Description"));
4085 }
4086
4087 appendPQExpBufferStr(&buf,
4088 "\nFROM pg_catalog.pg_class c"
4089 "\n LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace");
4090
4091 if (pset.sversion >= 120000 && !pset.hide_tableam &&
4092 (showTables || showMatViews || showIndexes))
4093 appendPQExpBufferStr(&buf,
4094 "\n LEFT JOIN pg_catalog.pg_am am ON am.oid = c.relam");
4095
4096 if (showIndexes)
4097 appendPQExpBufferStr(&buf,
4098 "\n LEFT JOIN pg_catalog.pg_index i ON i.indexrelid = c.oid"
4099 "\n LEFT JOIN pg_catalog.pg_class c2 ON i.indrelid = c2.oid");
4100
4101 appendPQExpBufferStr(&buf, "\nWHERE c.relkind IN (");
4102 if (showTables)
4103 {
4104 appendPQExpBufferStr(&buf, CppAsString2(RELKIND_RELATION) ","
4105 CppAsString2(RELKIND_PARTITIONED_TABLE) ",");
4106 /* with 'S' or a pattern, allow 't' to match TOAST tables too */
4107 if (showSystem || pattern)
4108 appendPQExpBufferStr(&buf, CppAsString2(RELKIND_TOASTVALUE) ",");
4109 }
4110 if (showViews)
4111 appendPQExpBufferStr(&buf, CppAsString2(RELKIND_VIEW) ",");
4112 if (showMatViews)
4113 appendPQExpBufferStr(&buf, CppAsString2(RELKIND_MATVIEW) ",");
4114 if (showIndexes)
4115 appendPQExpBufferStr(&buf, CppAsString2(RELKIND_INDEX) ","
4116 CppAsString2(RELKIND_PARTITIONED_INDEX) ",");
4117 if (showSeq)
4118 appendPQExpBufferStr(&buf, CppAsString2(RELKIND_SEQUENCE) ",");
4119 if (showSystem || pattern)
4120 appendPQExpBufferStr(&buf, "'s',"); /* was RELKIND_SPECIAL */
4121 if (showForeign)
4122 appendPQExpBufferStr(&buf, CppAsString2(RELKIND_FOREIGN_TABLE) ",");
4123
4124 appendPQExpBufferStr(&buf, "''"); /* dummy */
4125 appendPQExpBufferStr(&buf, ")\n");
4126
4127 if (!showSystem && !pattern)
4128 appendPQExpBufferStr(&buf, " AND n.nspname <> 'pg_catalog'\n"
4129 " AND n.nspname !~ '^pg_toast'\n"
4130 " AND n.nspname <> 'information_schema'\n");
4131
4132 if (!validateSQLNamePattern(&buf, pattern, true, false,
4133 "n.nspname", "c.relname", NULL,
4134 "pg_catalog.pg_table_is_visible(c.oid)",
4135 NULL, 3))
4136 {
4137 termPQExpBuffer(&buf);
4138 return false;
4139 }
4140
4141 appendPQExpBufferStr(&buf, "ORDER BY 1,2;");
4142
4143 res = PSQLexec(buf.data);
4144 termPQExpBuffer(&buf);
4145 if (!res)
4146 return false;
4147
4148 /*
4149 * Most functions in this file are content to print an empty table when
4150 * there are no matching objects. We intentionally deviate from that
4151 * here, but only in !quiet mode, for historical reasons.
4152 */
4153 if (PQntuples(res) == 0 && !pset.quiet)
4154 {
4155 if (pattern)
4156 pg_log_error("Did not find any relation named \"%s\".",
4157 pattern);
4158 else
4159 pg_log_error("Did not find any relations.");
4160 }
4161 else
4162 {
4163 myopt.title = _("List of relations");
4164 myopt.translate_header = true;
4165 myopt.translate_columns = translate_columns;
4166 myopt.n_translate_columns = lengthof(translate_columns);
4167
4168 printQuery(res, &myopt, pset.queryFout, false, pset.logfile);
4169 }
4170
4171 PQclear(res);
4172 return true;
4173 }
4174
4175 /*
4176 * \dP
4177 * Takes an optional regexp to select particular relations
4178 *
4179 * As with \d, you can specify the kinds of relations you want:
4180 *
4181 * t for tables
4182 * i for indexes
4183 *
4184 * And there's additional flags:
4185 *
4186 * n to list non-leaf partitioned tables
4187 *
4188 * and you can mix and match these in any order.
4189 */
4190 bool
4191 listPartitionedTables(const char *reltypes, const char *pattern, bool verbose)
4192 {
4193 bool showTables = strchr(reltypes, 't') != NULL;
4194 bool showIndexes = strchr(reltypes, 'i') != NULL;
4195 bool showNested = strchr(reltypes, 'n') != NULL;
4196 PQExpBufferData buf;
4197 PQExpBufferData title;
4198 PGresult *res;
4199 printQueryOpt myopt = pset.popt;
4200 bool translate_columns[] = {false, false, false, false, false, false, false, false, false, false};
4201 const char *tabletitle;
4202 bool mixed_output = false;
4203
4204 /*
4205 * Note: Declarative table partitioning is only supported as of Pg 10.0.
4206 */
4207 if (pset.sversion < 100000)
4208 {
4209 char sverbuf[32];
4210
4211 pg_log_error("The server (version %s) does not support declarative table partitioning.",
4212 formatPGVersionNumber(pset.sversion, false,
4213 sverbuf, sizeof(sverbuf)));
4214 return true;
4215 }
4216
4217 /* If no relation kind was selected, show them all */
4218 if (!showTables && !showIndexes)
4219 showTables = showIndexes = true;
4220
4221 if (showIndexes && !showTables)
4222 tabletitle = _("List of partitioned indexes"); /* \dPi */
4223 else if (showTables && !showIndexes)
4224 tabletitle = _("List of partitioned tables"); /* \dPt */
4225 else
4226 {
4227 /* show all kinds */
4228 tabletitle = _("List of partitioned relations");
4229 mixed_output = true;
4230 }
4231
4232 initPQExpBuffer(&buf);
4233
4234 printfPQExpBuffer(&buf,
4235 "SELECT n.nspname as \"%s\",\n"
4236 " c.relname as \"%s\",\n"
4237 " pg_catalog.pg_get_userbyid(c.relowner) as \"%s\"",
4238 gettext_noop("Schema"),
4239 gettext_noop("Name"),
4240 gettext_noop("Owner"));
4241
4242 if (mixed_output)
4243 {
4244 appendPQExpBuffer(&buf,
4245 ",\n CASE c.relkind"
4246 " WHEN " CppAsString2(RELKIND_PARTITIONED_TABLE) " THEN '%s'"
4247 " WHEN " CppAsString2(RELKIND_PARTITIONED_INDEX) " THEN '%s'"
4248 " END as \"%s\"",
4249 gettext_noop("partitioned table"),
4250 gettext_noop("partitioned index"),
4251 gettext_noop("Type"));
4252
4253 translate_columns[3] = true;
4254 }
4255
4256 if (showNested || pattern)
4257 appendPQExpBuffer(&buf,
4258 ",\n inh.inhparent::pg_catalog.regclass as \"%s\"",
4259 gettext_noop("Parent name"));
4260
4261 if (showIndexes)
4262 appendPQExpBuffer(&buf,
4263 ",\n c2.oid::pg_catalog.regclass as \"%s\"",
4264 gettext_noop("Table"));
4265
4266 if (verbose)
4267 {
4268 /*
4269 * Table access methods were introduced in v12, and can be set on
4270 * partitioned tables since v17.
4271 */
4272 appendPQExpBuffer(&buf, ",\n am.amname as \"%s\"",
4273 gettext_noop("Access method"));
4274
4275 if (showNested)
4276 {
4277 appendPQExpBuffer(&buf,
4278 ",\n s.dps as \"%s\"",
4279 gettext_noop("Leaf partition size"));
4280 appendPQExpBuffer(&buf,
4281 ",\n s.tps as \"%s\"",
4282 gettext_noop("Total size"));
4283 }
4284 else
4285 /* Sizes of all partitions are considered in this case. */
4286 appendPQExpBuffer(&buf,
4287 ",\n s.tps as \"%s\"",
4288 gettext_noop("Total size"));
4289
4290 appendPQExpBuffer(&buf,
4291 ",\n pg_catalog.obj_description(c.oid, 'pg_class') as \"%s\"",
4292 gettext_noop("Description"));
4293 }
4294
4295 appendPQExpBufferStr(&buf,
4296 "\nFROM pg_catalog.pg_class c"
4297 "\n LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace");
4298
4299 if (showIndexes)
4300 appendPQExpBufferStr(&buf,
4301 "\n LEFT JOIN pg_catalog.pg_index i ON i.indexrelid = c.oid"
4302 "\n LEFT JOIN pg_catalog.pg_class c2 ON i.indrelid = c2.oid");
4303
4304 if (showNested || pattern)
4305 appendPQExpBufferStr(&buf,
4306 "\n LEFT JOIN pg_catalog.pg_inherits inh ON c.oid = inh.inhrelid");
4307
4308 if (verbose)
4309 {
4310 appendPQExpBufferStr(&buf,
4311 "\n LEFT JOIN pg_catalog.pg_am am ON c.relam = am.oid");
4312
4313 if (pset.sversion < 120000)
4314 {
4315 appendPQExpBufferStr(&buf,
4316 ",\n LATERAL (WITH RECURSIVE d\n"
4317 " AS (SELECT inhrelid AS oid, 1 AS level\n"
4318 " FROM pg_catalog.pg_inherits\n"
4319 " WHERE inhparent = c.oid\n"
4320 " UNION ALL\n"
4321 " SELECT inhrelid, level + 1\n"
4322 " FROM pg_catalog.pg_inherits i\n"
4323 " JOIN d ON i.inhparent = d.oid)\n"
4324 " SELECT pg_catalog.pg_size_pretty(sum(pg_catalog.pg_table_size("
4325 "d.oid))) AS tps,\n"
4326 " pg_catalog.pg_size_pretty(sum("
4327 "\n CASE WHEN d.level = 1"
4328 " THEN pg_catalog.pg_table_size(d.oid) ELSE 0 END)) AS dps\n"
4329 " FROM d) s");
4330 }
4331 else
4332 {
4333 /* PostgreSQL 12 has pg_partition_tree function */
4334 appendPQExpBufferStr(&buf,
4335 ",\n LATERAL (SELECT pg_catalog.pg_size_pretty(sum("
4336 "\n CASE WHEN ppt.isleaf AND ppt.level = 1"
4337 "\n THEN pg_catalog.pg_table_size(ppt.relid)"
4338 " ELSE 0 END)) AS dps"
4339 ",\n pg_catalog.pg_size_pretty(sum("
4340 "pg_catalog.pg_table_size(ppt.relid))) AS tps"
4341 "\n FROM pg_catalog.pg_partition_tree(c.oid) ppt) s");
4342 }
4343 }
4344
4345 appendPQExpBufferStr(&buf, "\nWHERE c.relkind IN (");
4346 if (showTables)
4347 appendPQExpBufferStr(&buf, CppAsString2(RELKIND_PARTITIONED_TABLE) ",");
4348 if (showIndexes)
4349 appendPQExpBufferStr(&buf, CppAsString2(RELKIND_PARTITIONED_INDEX) ",");
4350 appendPQExpBufferStr(&buf, "''"); /* dummy */
4351 appendPQExpBufferStr(&buf, ")\n");
4352
4353 appendPQExpBufferStr(&buf, !showNested && !pattern ?
4354 " AND NOT c.relispartition\n" : "");
4355
4356 if (!pattern)
4357 appendPQExpBufferStr(&buf, " AND n.nspname <> 'pg_catalog'\n"
4358 " AND n.nspname !~ '^pg_toast'\n"
4359 " AND n.nspname <> 'information_schema'\n");
4360
4361 if (!validateSQLNamePattern(&buf, pattern, true, false,
4362 "n.nspname", "c.relname", NULL,
4363 "pg_catalog.pg_table_is_visible(c.oid)",
4364 NULL, 3))
4365 {
4366 termPQExpBuffer(&buf);
4367 return false;
4368 }
4369
4370 appendPQExpBuffer(&buf, "ORDER BY \"Schema\", %s%s\"Name\";",
4371 mixed_output ? "\"Type\" DESC, " : "",
4372 showNested || pattern ? "\"Parent name\" NULLS FIRST, " : "");
4373
4374 res = PSQLexec(buf.data);
4375 termPQExpBuffer(&buf);
4376 if (!res)
4377 return false;
4378
4379 initPQExpBuffer(&title);
4380 appendPQExpBufferStr(&title, tabletitle);
4381
4382 myopt.title = title.data;
4383 myopt.translate_header = true;
4384 myopt.translate_columns = translate_columns;
4385 myopt.n_translate_columns = lengthof(translate_columns);
4386
4387 printQuery(res, &myopt, pset.queryFout, false, pset.logfile);
4388
4389 termPQExpBuffer(&title);
4390
4391 PQclear(res);
4392 return true;
4393 }
4394
4395 /*
4396 * \dL
4397 *
4398 * Describes languages.
4399 */
4400 bool
4401 listLanguages(const char *pattern, bool verbose, bool showSystem)
4402 {
4403 PQExpBufferData buf;
4404 PGresult *res;
4405 printQueryOpt myopt = pset.popt;
4406
4407 initPQExpBuffer(&buf);
4408
4409 printfPQExpBuffer(&buf,
4410 "SELECT l.lanname AS \"%s\",\n"
4411 " pg_catalog.pg_get_userbyid(l.lanowner) as \"%s\",\n"
4412 " l.lanpltrusted AS \"%s\"",
4413 gettext_noop("Name"),
4414 gettext_noop("Owner"),
4415 gettext_noop("Trusted"));
4416
4417 if (verbose)
4418 {
4419 appendPQExpBuffer(&buf,
4420 ",\n NOT l.lanispl AS \"%s\",\n"
4421 " l.lanplcallfoid::pg_catalog.regprocedure AS \"%s\",\n"
4422 " l.lanvalidator::pg_catalog.regprocedure AS \"%s\",\n "
4423 "l.laninline::pg_catalog.regprocedure AS \"%s\",\n ",
4424 gettext_noop("Internal language"),
4425 gettext_noop("Call handler"),
4426 gettext_noop("Validator"),
4427 gettext_noop("Inline handler"));
4428 printACLColumn(&buf, "l.lanacl");
4429 }
4430
4431 appendPQExpBuffer(&buf,
4432 ",\n d.description AS \"%s\""
4433 "\nFROM pg_catalog.pg_language l\n"
4434 "LEFT JOIN pg_catalog.pg_description d\n"
4435 " ON d.classoid = l.tableoid AND d.objoid = l.oid\n"
4436 " AND d.objsubid = 0\n",
4437 gettext_noop("Description"));
4438
4439 if (pattern)
4440 {
4441 if (!validateSQLNamePattern(&buf, pattern, false, false,
4442 NULL, "l.lanname", NULL, NULL,
4443 NULL, 2))
4444 {
4445 termPQExpBuffer(&buf);
4446 return false;
4447 }
4448 }
4449
4450 if (!showSystem && !pattern)
4451 appendPQExpBufferStr(&buf, "WHERE l.lanplcallfoid != 0\n");
4452
4453
4454 appendPQExpBufferStr(&buf, "ORDER BY 1;");
4455
4456 res = PSQLexec(buf.data);
4457 termPQExpBuffer(&buf);
4458 if (!res)
4459 return false;
4460
4461 myopt.title = _("List of languages");
4462 myopt.translate_header = true;
4463
4464 printQuery(res, &myopt, pset.queryFout, false, pset.logfile);
4465
4466 PQclear(res);
4467 return true;
4468 }
4469
4470
4471 /*
4472 * \dD
4473 *
4474 * Describes domains.
4475 */
4476 bool
4477 listDomains(const char *pattern, bool verbose, bool showSystem)
4478 {
4479 PQExpBufferData buf;
4480 PGresult *res;
4481 printQueryOpt myopt = pset.popt;
4482
4483 initPQExpBuffer(&buf);
4484
4485 printfPQExpBuffer(&buf,
4486 "SELECT n.nspname as \"%s\",\n"
4487 " t.typname as \"%s\",\n"
4488 " pg_catalog.format_type(t.typbasetype, t.typtypmod) as \"%s\",\n"
4489 " (SELECT c.collname FROM pg_catalog.pg_collation c, pg_catalog.pg_type bt\n"
4490 " WHERE c.oid = t.typcollation AND bt.oid = t.typbasetype AND t.typcollation <> bt.typcollation) as \"%s\",\n"
4491 " CASE WHEN t.typnotnull THEN 'not null' END as \"%s\",\n"
4492 " t.typdefault as \"%s\",\n"
4493 " pg_catalog.array_to_string(ARRAY(\n"
4494 " SELECT pg_catalog.pg_get_constraintdef(r.oid, true) FROM pg_catalog.pg_constraint r WHERE t.oid = r.contypid AND r.contype = " CppAsString2(CONSTRAINT_CHECK) " ORDER BY r.conname\n"
4495 " ), ' ') as \"%s\"",
4496 gettext_noop("Schema"),
4497 gettext_noop("Name"),
4498 gettext_noop("Type"),
4499 gettext_noop("Collation"),
4500 gettext_noop("Nullable"),
4501 gettext_noop("Default"),
4502 gettext_noop("Check"));
4503
4504 if (verbose)
4505 {
4506 appendPQExpBufferStr(&buf, ",\n ");
4507 printACLColumn(&buf, "t.typacl");
4508 appendPQExpBuffer(&buf,
4509 ",\n d.description as \"%s\"",
4510 gettext_noop("Description"));
4511 }
4512
4513 appendPQExpBufferStr(&buf,
4514 "\nFROM pg_catalog.pg_type t\n"
4515 " LEFT JOIN pg_catalog.pg_namespace n ON n.oid = t.typnamespace\n");
4516
4517 if (verbose)
4518 appendPQExpBufferStr(&buf,
4519 " LEFT JOIN pg_catalog.pg_description d "
4520 "ON d.classoid = t.tableoid AND d.objoid = t.oid "
4521 "AND d.objsubid = 0\n");
4522
4523 appendPQExpBufferStr(&buf, "WHERE t.typtype = 'd'\n");
4524
4525 if (!showSystem && !pattern)
4526 appendPQExpBufferStr(&buf, " AND n.nspname <> 'pg_catalog'\n"
4527 " AND n.nspname <> 'information_schema'\n");
4528
4529 if (!validateSQLNamePattern(&buf, pattern, true, false,
4530 "n.nspname", "t.typname", NULL,
4531 "pg_catalog.pg_type_is_visible(t.oid)",
4532 NULL, 3))
4533 {
4534 termPQExpBuffer(&buf);
4535 return false;
4536 }
4537
4538 appendPQExpBufferStr(&buf, "ORDER BY 1, 2;");
4539
4540 res = PSQLexec(buf.data);
4541 termPQExpBuffer(&buf);
4542 if (!res)
4543 return false;
4544
4545 myopt.title = _("List of domains");
4546 myopt.translate_header = true;
4547
4548 printQuery(res, &myopt, pset.queryFout, false, pset.logfile);
4549
4550 PQclear(res);
4551 return true;
4552 }
4553
4554 /*
4555 * \dc
4556 *
4557 * Describes conversions.
4558 */
4559 bool
4560 listConversions(const char *pattern, bool verbose, bool showSystem)
4561 {
4562 PQExpBufferData buf;
4563 PGresult *res;
4564 printQueryOpt myopt = pset.popt;
4565 static const bool translate_columns[] =
4566 {false, false, false, false, true, false};
4567
4568 initPQExpBuffer(&buf);
4569
4570 printfPQExpBuffer(&buf,
4571 "SELECT n.nspname AS \"%s\",\n"
4572 " c.conname AS \"%s\",\n"
4573 " pg_catalog.pg_encoding_to_char(c.conforencoding) AS \"%s\",\n"
4574 " pg_catalog.pg_encoding_to_char(c.contoencoding) AS \"%s\",\n"
4575 " CASE WHEN c.condefault THEN '%s'\n"
4576 " ELSE '%s' END AS \"%s\"",
4577 gettext_noop("Schema"),
4578 gettext_noop("Name"),
4579 gettext_noop("Source"),
4580 gettext_noop("Destination"),
4581 gettext_noop("yes"), gettext_noop("no"),
4582 gettext_noop("Default?"));
4583
4584 if (verbose)
4585 appendPQExpBuffer(&buf,
4586 ",\n d.description AS \"%s\"",
4587 gettext_noop("Description"));
4588
4589 appendPQExpBufferStr(&buf,
4590 "\nFROM pg_catalog.pg_conversion c\n"
4591 " JOIN pg_catalog.pg_namespace n "
4592 "ON n.oid = c.connamespace\n");
4593
4594 if (verbose)
4595 appendPQExpBufferStr(&buf,
4596 "LEFT JOIN pg_catalog.pg_description d "
4597 "ON d.classoid = c.tableoid\n"
4598 " AND d.objoid = c.oid "
4599 "AND d.objsubid = 0\n");
4600
4601 appendPQExpBufferStr(&buf, "WHERE true\n");
4602
4603 if (!showSystem && !pattern)
4604 appendPQExpBufferStr(&buf, " AND n.nspname <> 'pg_catalog'\n"
4605 " AND n.nspname <> 'information_schema'\n");
4606
4607 if (!validateSQLNamePattern(&buf, pattern, true, false,
4608 "n.nspname", "c.conname", NULL,
4609 "pg_catalog.pg_conversion_is_visible(c.oid)",
4610 NULL, 3))
4611 {
4612 termPQExpBuffer(&buf);
4613 return false;
4614 }
4615
4616 appendPQExpBufferStr(&buf, "ORDER BY 1, 2;");
4617
4618 res = PSQLexec(buf.data);
4619 termPQExpBuffer(&buf);
4620 if (!res)
4621 return false;
4622
4623 myopt.title = _("List of conversions");
4624 myopt.translate_header = true;
4625 myopt.translate_columns = translate_columns;
4626 myopt.n_translate_columns = lengthof(translate_columns);
4627
4628 printQuery(res, &myopt, pset.queryFout, false, pset.logfile);
4629
4630 PQclear(res);
4631 return true;
4632 }
4633
4634 /*
4635 * \dconfig
4636 *
4637 * Describes configuration parameters.
4638 */
4639 bool
4640 describeConfigurationParameters(const char *pattern, bool verbose,
4641 bool showSystem)
4642 {
4643 PQExpBufferData buf;
4644 PGresult *res;
4645 printQueryOpt myopt = pset.popt;
4646
4647 initPQExpBuffer(&buf);
4648 printfPQExpBuffer(&buf,
4649 "SELECT s.name AS \"%s\", "
4650 "pg_catalog.current_setting(s.name) AS \"%s\"",
4651 gettext_noop("Parameter"),
4652 gettext_noop("Value"));
4653
4654 if (verbose)
4655 {
4656 appendPQExpBuffer(&buf,
4657 ", s.vartype AS \"%s\", s.context AS \"%s\", ",
4658 gettext_noop("Type"),
4659 gettext_noop("Context"));
4660 if (pset.sversion >= 150000)
4661 printACLColumn(&buf, "p.paracl");
4662 else
4663 appendPQExpBuffer(&buf, "NULL AS \"%s\"",
4664 gettext_noop("Access privileges"));
4665 }
4666
4667 appendPQExpBufferStr(&buf, "\nFROM pg_catalog.pg_settings s\n");
4668
4669 if (verbose && pset.sversion >= 150000)
4670 appendPQExpBufferStr(&buf,
4671 " LEFT JOIN pg_catalog.pg_parameter_acl p\n"
4672 " ON pg_catalog.lower(s.name) = p.parname\n");
4673
4674 if (pattern)
4675 processSQLNamePattern(pset.db, &buf, pattern,
4676 false, false,
4677 NULL, "pg_catalog.lower(s.name)", NULL,
4678 NULL, NULL, NULL);
4679 else
4680 appendPQExpBufferStr(&buf, "WHERE s.source <> 'default' AND\n"
4681 " s.setting IS DISTINCT FROM s.boot_val\n");
4682
4683 appendPQExpBufferStr(&buf, "ORDER BY 1;");
4684
4685 res = PSQLexec(buf.data);
4686 termPQExpBuffer(&buf);
4687 if (!res)
4688 return false;
4689
4690 if (pattern)
4691 myopt.title = _("List of configuration parameters");
4692 else
4693 myopt.title = _("List of non-default configuration parameters");
4694 myopt.translate_header = true;
4695
4696 printQuery(res, &myopt, pset.queryFout, false, pset.logfile);
4697
4698 PQclear(res);
4699 return true;
4700 }
4701
4702 /*
4703 * \dy
4704 *
4705 * Describes Event Triggers.
4706 */
4707 bool
4708 listEventTriggers(const char *pattern, bool verbose)
4709 {
4710 PQExpBufferData buf;
4711 PGresult *res;
4712 printQueryOpt myopt = pset.popt;
4713 static const bool translate_columns[] =
4714 {false, false, false, true, false, false, false};
4715
4716 if (pset.sversion < 90300)
4717 {
4718 char sverbuf[32];
4719
4720 pg_log_error("The server (version %s) does not support event triggers.",
4721 formatPGVersionNumber(pset.sversion, false,
4722 sverbuf, sizeof(sverbuf)));
4723 return true;
4724 }
4725
4726 initPQExpBuffer(&buf);
4727
4728 printfPQExpBuffer(&buf,
4729 "SELECT evtname as \"%s\", "
4730 "evtevent as \"%s\", "
4731 "pg_catalog.pg_get_userbyid(e.evtowner) as \"%s\",\n"
4732 " case evtenabled when 'O' then '%s'"
4733 " when 'R' then '%s'"
4734 " when 'A' then '%s'"
4735 " when 'D' then '%s' end as \"%s\",\n"
4736 " e.evtfoid::pg_catalog.regproc as \"%s\", "
4737 "pg_catalog.array_to_string(array(select x"
4738 " from pg_catalog.unnest(evttags) as t(x)), ', ') as \"%s\"",
4739 gettext_noop("Name"),
4740 gettext_noop("Event"),
4741 gettext_noop("Owner"),
4742 gettext_noop("enabled"),
4743 gettext_noop("replica"),
4744 gettext_noop("always"),
4745 gettext_noop("disabled"),
4746 gettext_noop("Enabled"),
4747 gettext_noop("Function"),
4748 gettext_noop("Tags"));
4749 if (verbose)
4750 appendPQExpBuffer(&buf,
4751 ",\npg_catalog.obj_description(e.oid, 'pg_event_trigger') as \"%s\"",
4752 gettext_noop("Description"));
4753 appendPQExpBufferStr(&buf,
4754 "\nFROM pg_catalog.pg_event_trigger e ");
4755
4756 if (!validateSQLNamePattern(&buf, pattern, false, false,
4757 NULL, "evtname", NULL, NULL,
4758 NULL, 1))
4759 {
4760 termPQExpBuffer(&buf);
4761 return false;
4762 }
4763
4764 appendPQExpBufferStr(&buf, "ORDER BY 1");
4765
4766 res = PSQLexec(buf.data);
4767 termPQExpBuffer(&buf);
4768 if (!res)
4769 return false;
4770
4771 myopt.title = _("List of event triggers");
4772 myopt.translate_header = true;
4773 myopt.translate_columns = translate_columns;
4774 myopt.n_translate_columns = lengthof(translate_columns);
4775
4776 printQuery(res, &myopt, pset.queryFout, false, pset.logfile);
4777
4778 PQclear(res);
4779 return true;
4780 }
4781
4782 /*
4783 * \dX
4784 *
4785 * Describes extended statistics.
4786 */
4787 bool
4788 listExtendedStats(const char *pattern)
4789 {
4790 PQExpBufferData buf;
4791 PGresult *res;
4792 printQueryOpt myopt = pset.popt;
4793
4794 if (pset.sversion < 100000)
4795 {
4796 char sverbuf[32];
4797
4798 pg_log_error("The server (version %s) does not support extended statistics.",
4799 formatPGVersionNumber(pset.sversion, false,
4800 sverbuf, sizeof(sverbuf)));
4801 return true;
4802 }
4803
4804 initPQExpBuffer(&buf);
4805 printfPQExpBuffer(&buf,
4806 "SELECT \n"
4807 "es.stxnamespace::pg_catalog.regnamespace::pg_catalog.text AS \"%s\", \n"
4808 "es.stxname AS \"%s\", \n",
4809 gettext_noop("Schema"),
4810 gettext_noop("Name"));
4811
4812 if (pset.sversion >= 140000)
4813 appendPQExpBuffer(&buf,
4814 "pg_catalog.format('%%s FROM %%s', \n"
4815 " pg_catalog.pg_get_statisticsobjdef_columns(es.oid), \n"
4816 " es.stxrelid::pg_catalog.regclass) AS \"%s\"",
4817 gettext_noop("Definition"));
4818 else
4819 appendPQExpBuffer(&buf,
4820 "pg_catalog.format('%%s FROM %%s', \n"
4821 " (SELECT pg_catalog.string_agg(pg_catalog.quote_ident(a.attname),', ') \n"
4822 " FROM pg_catalog.unnest(es.stxkeys) s(attnum) \n"
4823 " JOIN pg_catalog.pg_attribute a \n"
4824 " ON (es.stxrelid = a.attrelid \n"
4825 " AND a.attnum = s.attnum \n"
4826 " AND NOT a.attisdropped)), \n"
4827 "es.stxrelid::pg_catalog.regclass) AS \"%s\"",
4828 gettext_noop("Definition"));
4829
4830 appendPQExpBuffer(&buf,
4831 ",\nCASE WHEN " CppAsString2(STATS_EXT_NDISTINCT) " = any(es.stxkind) THEN 'defined' \n"
4832 "END AS \"%s\", \n"
4833 "CASE WHEN " CppAsString2(STATS_EXT_DEPENDENCIES) " = any(es.stxkind) THEN 'defined' \n"
4834 "END AS \"%s\"",
4835 gettext_noop("Ndistinct"),
4836 gettext_noop("Dependencies"));
4837
4838 /*
4839 * Include the MCV statistics kind.
4840 */
4841 if (pset.sversion >= 120000)
4842 {
4843 appendPQExpBuffer(&buf,
4844 ",\nCASE WHEN " CppAsString2(STATS_EXT_MCV) " = any(es.stxkind) THEN 'defined' \n"
4845 "END AS \"%s\" ",
4846 gettext_noop("MCV"));
4847 }
4848
4849 appendPQExpBufferStr(&buf,
4850 " \nFROM pg_catalog.pg_statistic_ext es \n");
4851
4852 if (!validateSQLNamePattern(&buf, pattern,
4853 false, false,
4854 "es.stxnamespace::pg_catalog.regnamespace::pg_catalog.text", "es.stxname",
4855 NULL, "pg_catalog.pg_statistics_obj_is_visible(es.oid)",
4856 NULL, 3))
4857 {
4858 termPQExpBuffer(&buf);
4859 return false;
4860 }
4861
4862 appendPQExpBufferStr(&buf, "ORDER BY 1, 2;");
4863
4864 res = PSQLexec(buf.data);
4865 termPQExpBuffer(&buf);
4866 if (!res)
4867 return false;
4868
4869 myopt.title = _("List of extended statistics");
4870 myopt.translate_header = true;
4871
4872 printQuery(res, &myopt, pset.queryFout, false, pset.logfile);
4873
4874 PQclear(res);
4875 return true;
4876 }
4877
4878 /*
4879 * \dC
4880 *
4881 * Describes casts.
4882 */
4883 bool
4884 listCasts(const char *pattern, bool verbose)
4885 {
4886 PQExpBufferData buf;
4887 PGresult *res;
4888 printQueryOpt myopt = pset.popt;
4889 static const bool translate_columns[] = {false, false, false, true, false};
4890
4891 initPQExpBuffer(&buf);
4892
4893 printfPQExpBuffer(&buf,
4894 "SELECT pg_catalog.format_type(castsource, NULL) AS \"%s\",\n"
4895 " pg_catalog.format_type(casttarget, NULL) AS \"%s\",\n",
4896 gettext_noop("Source type"),
4897 gettext_noop("Target type"));
4898
4899 /*
4900 * We don't attempt to localize '(binary coercible)' or '(with inout)',
4901 * because there's too much risk of gettext translating a function name
4902 * that happens to match some string in the PO database.
4903 */
4904 appendPQExpBuffer(&buf,
4905 " CASE WHEN c.castmethod = '%c' THEN '(binary coercible)'\n"
4906 " WHEN c.castmethod = '%c' THEN '(with inout)'\n"
4907 " ELSE p.proname\n"
4908 " END AS \"%s\",\n",
4909 COERCION_METHOD_BINARY,
4910 COERCION_METHOD_INOUT,
4911 gettext_noop("Function"));
4912
4913 appendPQExpBuffer(&buf,
4914 " CASE WHEN c.castcontext = '%c' THEN '%s'\n"
4915 " WHEN c.castcontext = '%c' THEN '%s'\n"
4916 " ELSE '%s'\n"
4917 " END AS \"%s\"",
4918 COERCION_CODE_EXPLICIT,
4919 gettext_noop("no"),
4920 COERCION_CODE_ASSIGNMENT,
4921 gettext_noop("in assignment"),
4922 gettext_noop("yes"),
4923 gettext_noop("Implicit?"));
4924
4925 if (verbose)
4926 appendPQExpBuffer(&buf,
4927 ",\n d.description AS \"%s\"",
4928 gettext_noop("Description"));
4929
4930 /*
4931 * We need a left join to pg_proc for binary casts; the others are just
4932 * paranoia.
4933 */
4934 appendPQExpBufferStr(&buf,
4935 "\nFROM pg_catalog.pg_cast c LEFT JOIN pg_catalog.pg_proc p\n"
4936 " ON c.castfunc = p.oid\n"
4937 " LEFT JOIN pg_catalog.pg_type ts\n"
4938 " ON c.castsource = ts.oid\n"
4939 " LEFT JOIN pg_catalog.pg_namespace ns\n"
4940 " ON ns.oid = ts.typnamespace\n"
4941 " LEFT JOIN pg_catalog.pg_type tt\n"
4942 " ON c.casttarget = tt.oid\n"
4943 " LEFT JOIN pg_catalog.pg_namespace nt\n"
4944 " ON nt.oid = tt.typnamespace\n");
4945
4946 if (verbose)
4947 appendPQExpBufferStr(&buf,
4948 " LEFT JOIN pg_catalog.pg_description d\n"
4949 " ON d.classoid = c.tableoid AND d.objoid = "
4950 "c.oid AND d.objsubid = 0\n");
4951
4952 appendPQExpBufferStr(&buf, "WHERE ( (true");
4953
4954 /*
4955 * Match name pattern against either internal or external name of either
4956 * castsource or casttarget
4957 */
4958 if (!validateSQLNamePattern(&buf, pattern, true, false,
4959 "ns.nspname", "ts.typname",
4960 "pg_catalog.format_type(ts.oid, NULL)",
4961 "pg_catalog.pg_type_is_visible(ts.oid)",
4962 NULL, 3))
4963 goto error_return;
4964
4965 appendPQExpBufferStr(&buf, ") OR (true");
4966
4967 if (!validateSQLNamePattern(&buf, pattern, true, false,
4968 "nt.nspname", "tt.typname",
4969 "pg_catalog.format_type(tt.oid, NULL)",
4970 "pg_catalog.pg_type_is_visible(tt.oid)",
4971 NULL, 3))
4972 goto error_return;
4973
4974 appendPQExpBufferStr(&buf, ") )\nORDER BY 1, 2;");
4975
4976 res = PSQLexec(buf.data);
4977 termPQExpBuffer(&buf);
4978 if (!res)
4979 return false;
4980
4981 myopt.title = _("List of casts");
4982 myopt.translate_header = true;
4983 myopt.translate_columns = translate_columns;
4984 myopt.n_translate_columns = lengthof(translate_columns);
4985
4986 printQuery(res, &myopt, pset.queryFout, false, pset.logfile);
4987
4988 PQclear(res);
4989 return true;
4990
4991 error_return:
4992 termPQExpBuffer(&buf);
4993 return false;
4994 }
4995
4996 /*
4997 * \dO
4998 *
4999 * Describes collations.
5000 */
5001 bool
5002 listCollations(const char *pattern, bool verbose, bool showSystem)
5003 {
5004 PQExpBufferData buf;
5005 PGresult *res;
5006 printQueryOpt myopt = pset.popt;
5007 static const bool translate_columns[] = {false, false, false, false, false, false, false, true, false};
5008
5009 initPQExpBuffer(&buf);
5010
5011 printfPQExpBuffer(&buf,
5012 "SELECT\n"
5013 " n.nspname AS \"%s\",\n"
5014 " c.collname AS \"%s\",\n",
5015 gettext_noop("Schema"),
5016 gettext_noop("Name"));
5017
5018 if (pset.sversion >= 100000)
5019 appendPQExpBuffer(&buf,
5020 " CASE c.collprovider "
5021 "WHEN " CppAsString2(COLLPROVIDER_DEFAULT) " THEN 'default' "
5022 "WHEN " CppAsString2(COLLPROVIDER_BUILTIN) " THEN 'builtin' "
5023 "WHEN " CppAsString2(COLLPROVIDER_LIBC) " THEN 'libc' "
5024 "WHEN " CppAsString2(COLLPROVIDER_ICU) " THEN 'icu' "
5025 "END AS \"%s\",\n",
5026 gettext_noop("Provider"));
5027 else
5028 appendPQExpBuffer(&buf,
5029 " 'libc' AS \"%s\",\n",
5030 gettext_noop("Provider"));
5031
5032 appendPQExpBuffer(&buf,
5033 " c.collcollate AS \"%s\",\n"
5034 " c.collctype AS \"%s\",\n",
5035 gettext_noop("Collate"),
5036 gettext_noop("Ctype"));
5037
5038 if (pset.sversion >= 170000)
5039 appendPQExpBuffer(&buf,
5040 " c.colllocale AS \"%s\",\n",
5041 gettext_noop("Locale"));
5042 else if (pset.sversion >= 150000)
5043 appendPQExpBuffer(&buf,
5044 " c.colliculocale AS \"%s\",\n",
5045 gettext_noop("Locale"));
5046 else
5047 appendPQExpBuffer(&buf,
5048 " c.collcollate AS \"%s\",\n",
5049 gettext_noop("Locale"));
5050
5051 if (pset.sversion >= 160000)
5052 appendPQExpBuffer(&buf,
5053 " c.collicurules AS \"%s\",\n",
5054 gettext_noop("ICU Rules"));
5055 else
5056 appendPQExpBuffer(&buf,
5057 " NULL AS \"%s\",\n",
5058 gettext_noop("ICU Rules"));
5059
5060 if (pset.sversion >= 120000)
5061 appendPQExpBuffer(&buf,
5062 " CASE WHEN c.collisdeterministic THEN '%s' ELSE '%s' END AS \"%s\"",
5063 gettext_noop("yes"), gettext_noop("no"),
5064 gettext_noop("Deterministic?"));
5065 else
5066 appendPQExpBuffer(&buf,
5067 " '%s' AS \"%s\"",
5068 gettext_noop("yes"),
5069 gettext_noop("Deterministic?"));
5070
5071 if (verbose)
5072 appendPQExpBuffer(&buf,
5073 ",\n pg_catalog.obj_description(c.oid, 'pg_collation') AS \"%s\"",
5074 gettext_noop("Description"));
5075
5076 appendPQExpBufferStr(&buf,
5077 "\nFROM pg_catalog.pg_collation c, pg_catalog.pg_namespace n\n"
5078 "WHERE n.oid = c.collnamespace\n");
5079
5080 if (!showSystem && !pattern)
5081 appendPQExpBufferStr(&buf, " AND n.nspname <> 'pg_catalog'\n"
5082 " AND n.nspname <> 'information_schema'\n");
5083
5084 /*
5085 * Hide collations that aren't usable in the current database's encoding.
5086 * If you think to change this, note that pg_collation_is_visible rejects
5087 * unusable collations, so you will need to hack name pattern processing
5088 * somehow to avoid inconsistent behavior.
5089 */
5090 appendPQExpBufferStr(&buf, " AND c.collencoding IN (-1, pg_catalog.pg_char_to_encoding(pg_catalog.getdatabaseencoding()))\n");
5091
5092 if (!validateSQLNamePattern(&buf, pattern, true, false,
5093 "n.nspname", "c.collname", NULL,
5094 "pg_catalog.pg_collation_is_visible(c.oid)",
5095 NULL, 3))
5096 {
5097 termPQExpBuffer(&buf);
5098 return false;
5099 }
5100
5101 appendPQExpBufferStr(&buf, "ORDER BY 1, 2;");
5102
5103 res = PSQLexec(buf.data);
5104 termPQExpBuffer(&buf);
5105 if (!res)
5106 return false;
5107
5108 myopt.title = _("List of collations");
5109 myopt.translate_header = true;
5110 myopt.translate_columns = translate_columns;
5111 myopt.n_translate_columns = lengthof(translate_columns);
5112
5113 printQuery(res, &myopt, pset.queryFout, false, pset.logfile);
5114
5115 PQclear(res);
5116 return true;
5117 }
5118
5119 /*
5120 * \dn
5121 *
5122 * Describes schemas (namespaces)
5123 */
5124 bool
5125 listSchemas(const char *pattern, bool verbose, bool showSystem)
5126 {
5127 PQExpBufferData buf;
5128 PGresult *res;
5129 printQueryOpt myopt = pset.popt;
5130 int pub_schema_tuples = 0;
5131 char **footers = NULL;
5132
5133 initPQExpBuffer(&buf);
5134 printfPQExpBuffer(&buf,
5135 "SELECT n.nspname AS \"%s\",\n"
5136 " pg_catalog.pg_get_userbyid(n.nspowner) AS \"%s\"",
5137 gettext_noop("Name"),
5138 gettext_noop("Owner"));
5139
5140 if (verbose)
5141 {
5142 appendPQExpBufferStr(&buf, ",\n ");
5143 printACLColumn(&buf, "n.nspacl");
5144 appendPQExpBuffer(&buf,
5145 ",\n pg_catalog.obj_description(n.oid, 'pg_namespace') AS \"%s\"",
5146 gettext_noop("Description"));
5147 }
5148
5149 appendPQExpBufferStr(&buf,
5150 "\nFROM pg_catalog.pg_namespace n\n");
5151
5152 if (!showSystem && !pattern)
5153 appendPQExpBufferStr(&buf,
5154 "WHERE n.nspname !~ '^pg_' AND n.nspname <> 'information_schema'\n");
5155
5156 if (!validateSQLNamePattern(&buf, pattern,
5157 !showSystem && !pattern, false,
5158 NULL, "n.nspname", NULL,
5159 NULL,
5160 NULL, 2))
5161 goto error_return;
5162
5163 appendPQExpBufferStr(&buf, "ORDER BY 1;");
5164
5165 res = PSQLexec(buf.data);
5166 if (!res)
5167 goto error_return;
5168
5169 myopt.title = _("List of schemas");
5170 myopt.translate_header = true;
5171
5172 if (pattern && pset.sversion >= 150000)
5173 {
5174 PGresult *result;
5175 int i;
5176
5177 printfPQExpBuffer(&buf,
5178 "SELECT pubname \n"
5179 "FROM pg_catalog.pg_publication p\n"
5180 " JOIN pg_catalog.pg_publication_namespace pn ON p.oid = pn.pnpubid\n"
5181 " JOIN pg_catalog.pg_namespace n ON n.oid = pn.pnnspid \n"
5182 "WHERE n.nspname = '%s'\n"
5183 "ORDER BY 1",
5184 pattern);
5185 result = PSQLexec(buf.data);
5186 if (!result)
5187 goto error_return;
5188 else
5189 pub_schema_tuples = PQntuples(result);
5190
5191 if (pub_schema_tuples > 0)
5192 {
5193 /*
5194 * Allocate memory for footers. Size of footers will be 1 (for
5195 * storing "Publications:" string) + publication schema mapping
5196 * count + 1 (for storing NULL).
5197 */
5198 footers = (char **) pg_malloc((1 + pub_schema_tuples + 1) * sizeof(char *));
5199 footers[0] = pg_strdup(_("Publications:"));
5200
5201 /* Might be an empty set - that's ok */
5202 for (i = 0; i < pub_schema_tuples; i++)
5203 {
5204 printfPQExpBuffer(&buf, " \"%s\"",
5205 PQgetvalue(result, i, 0));
5206
5207 footers[i + 1] = pg_strdup(buf.data);
5208 }
5209
5210 footers[i + 1] = NULL;
5211 myopt.footers = footers;
5212 }
5213
5214 PQclear(result);
5215 }
5216
5217 printQuery(res, &myopt, pset.queryFout, false, pset.logfile);
5218
5219 termPQExpBuffer(&buf);
5220 PQclear(res);
5221
5222 /* Free the memory allocated for the footer */
5223 if (footers)
5224 {
5225 char **footer = NULL;
5226
5227 for (footer = footers; *footer; footer++)
5228 pg_free(*footer);
5229
5230 pg_free(footers);
5231 }
5232
5233 return true;
5234
5235 error_return:
5236 termPQExpBuffer(&buf);
5237 return false;
5238 }
5239
5240
5241 /*
5242 * \dFp
5243 * list text search parsers
5244 */
5245 bool
5246 listTSParsers(const char *pattern, bool verbose)
5247 {
5248 PQExpBufferData buf;
5249 PGresult *res;
5250 printQueryOpt myopt = pset.popt;
5251
5252 if (verbose)
5253 return listTSParsersVerbose(pattern);
5254
5255 initPQExpBuffer(&buf);
5256
5257 printfPQExpBuffer(&buf,
5258 "SELECT\n"
5259 " n.nspname as \"%s\",\n"
5260 " p.prsname as \"%s\",\n"
5261 " pg_catalog.obj_description(p.oid, 'pg_ts_parser') as \"%s\"\n"
5262 "FROM pg_catalog.pg_ts_parser p\n"
5263 "LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.prsnamespace\n",
5264 gettext_noop("Schema"),
5265 gettext_noop("Name"),
5266 gettext_noop("Description")
5267 );
5268
5269 if (!validateSQLNamePattern(&buf, pattern, false, false,
5270 "n.nspname", "p.prsname", NULL,
5271 "pg_catalog.pg_ts_parser_is_visible(p.oid)",
5272 NULL, 3))
5273 {
5274 termPQExpBuffer(&buf);
5275 return false;
5276 }
5277
5278 appendPQExpBufferStr(&buf, "ORDER BY 1, 2;");
5279
5280 res = PSQLexec(buf.data);
5281 termPQExpBuffer(&buf);
5282 if (!res)
5283 return false;
5284
5285 myopt.title = _("List of text search parsers");
5286 myopt.translate_header = true;
5287
5288 printQuery(res, &myopt, pset.queryFout, false, pset.logfile);
5289
5290 PQclear(res);
5291 return true;
5292 }
5293
5294 /*
5295 * full description of parsers
5296 */
5297 static bool
5298 listTSParsersVerbose(const char *pattern)
5299 {
5300 PQExpBufferData buf;
5301 PGresult *res;
5302 int i;
5303
5304 initPQExpBuffer(&buf);
5305
5306 printfPQExpBuffer(&buf,
5307 "SELECT p.oid,\n"
5308 " n.nspname,\n"
5309 " p.prsname\n"
5310 "FROM pg_catalog.pg_ts_parser p\n"
5311 "LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.prsnamespace\n"
5312 );
5313
5314 if (!validateSQLNamePattern(&buf, pattern, false, false,
5315 "n.nspname", "p.prsname", NULL,
5316 "pg_catalog.pg_ts_parser_is_visible(p.oid)",
5317 NULL, 3))
5318 {
5319 termPQExpBuffer(&buf);
5320 return false;
5321 }
5322
5323 appendPQExpBufferStr(&buf, "ORDER BY 1, 2;");
5324
5325 res = PSQLexec(buf.data);
5326 termPQExpBuffer(&buf);
5327 if (!res)
5328 return false;
5329
5330 if (PQntuples(res) == 0)
5331 {
5332 if (!pset.quiet)
5333 {
5334 if (pattern)
5335 pg_log_error("Did not find any text search parser named \"%s\".",
5336 pattern);
5337 else
5338 pg_log_error("Did not find any text search parsers.");
5339 }
5340 PQclear(res);
5341 return false;
5342 }
5343
5344 for (i = 0; i < PQntuples(res); i++)
5345 {
5346 const char *oid;
5347 const char *nspname = NULL;
5348 const char *prsname;
5349
5350 oid = PQgetvalue(res, i, 0);
5351 if (!PQgetisnull(res, i, 1))
5352 nspname = PQgetvalue(res, i, 1);
5353 prsname = PQgetvalue(res, i, 2);
5354
5355 if (!describeOneTSParser(oid, nspname, prsname))
5356 {
5357 PQclear(res);
5358 return false;
5359 }
5360
5361 if (cancel_pressed)
5362 {
5363 PQclear(res);
5364 return false;
5365 }
5366 }
5367
5368 PQclear(res);
5369 return true;
5370 }
5371
5372 static bool
5373 describeOneTSParser(const char *oid, const char *nspname, const char *prsname)
5374 {
5375 PQExpBufferData buf;
5376 PGresult *res;
5377 PQExpBufferData title;
5378 printQueryOpt myopt = pset.popt;
5379 static const bool translate_columns[] = {true, false, false};
5380
5381 initPQExpBuffer(&buf);
5382
5383 printfPQExpBuffer(&buf,
5384 "SELECT '%s' AS \"%s\",\n"
5385 " p.prsstart::pg_catalog.regproc AS \"%s\",\n"
5386 " pg_catalog.obj_description(p.prsstart, 'pg_proc') as \"%s\"\n"
5387 " FROM pg_catalog.pg_ts_parser p\n"
5388 " WHERE p.oid = '%s'\n"
5389 "UNION ALL\n"
5390 "SELECT '%s',\n"
5391 " p.prstoken::pg_catalog.regproc,\n"
5392 " pg_catalog.obj_description(p.prstoken, 'pg_proc')\n"
5393 " FROM pg_catalog.pg_ts_parser p\n"
5394 " WHERE p.oid = '%s'\n"
5395 "UNION ALL\n"
5396 "SELECT '%s',\n"
5397 " p.prsend::pg_catalog.regproc,\n"
5398 " pg_catalog.obj_description(p.prsend, 'pg_proc')\n"
5399 " FROM pg_catalog.pg_ts_parser p\n"
5400 " WHERE p.oid = '%s'\n"
5401 "UNION ALL\n"
5402 "SELECT '%s',\n"
5403 " p.prsheadline::pg_catalog.regproc,\n"
5404 " pg_catalog.obj_description(p.prsheadline, 'pg_proc')\n"
5405 " FROM pg_catalog.pg_ts_parser p\n"
5406 " WHERE p.oid = '%s'\n"
5407 "UNION ALL\n"
5408 "SELECT '%s',\n"
5409 " p.prslextype::pg_catalog.regproc,\n"
5410 " pg_catalog.obj_description(p.prslextype, 'pg_proc')\n"
5411 " FROM pg_catalog.pg_ts_parser p\n"
5412 " WHERE p.oid = '%s';",
5413 gettext_noop("Start parse"),
5414 gettext_noop("Method"),
5415 gettext_noop("Function"),
5416 gettext_noop("Description"),
5417 oid,
5418 gettext_noop("Get next token"),
5419 oid,
5420 gettext_noop("End parse"),
5421 oid,
5422 gettext_noop("Get headline"),
5423 oid,
5424 gettext_noop("Get token types"),
5425 oid);
5426
5427 res = PSQLexec(buf.data);
5428 termPQExpBuffer(&buf);
5429 if (!res)
5430 return false;
5431
5432 initPQExpBuffer(&title);
5433 if (nspname)
5434 printfPQExpBuffer(&title, _("Text search parser \"%s.%s\""),
5435 nspname, prsname);
5436 else
5437 printfPQExpBuffer(&title, _("Text search parser \"%s\""), prsname);
5438 myopt.title = title.data;
5439 myopt.footers = NULL;
5440 myopt.topt.default_footer = false;
5441 myopt.translate_header = true;
5442 myopt.translate_columns = translate_columns;
5443 myopt.n_translate_columns = lengthof(translate_columns);
5444
5445 printQuery(res, &myopt, pset.queryFout, false, pset.logfile);
5446
5447 PQclear(res);
5448
5449 initPQExpBuffer(&buf);
5450
5451 printfPQExpBuffer(&buf,
5452 "SELECT t.alias as \"%s\",\n"
5453 " t.description as \"%s\"\n"
5454 "FROM pg_catalog.ts_token_type( '%s'::pg_catalog.oid ) as t\n"
5455 "ORDER BY 1;",
5456 gettext_noop("Token name"),
5457 gettext_noop("Description"),
5458 oid);
5459
5460 res = PSQLexec(buf.data);
5461 termPQExpBuffer(&buf);
5462 if (!res)
5463 {
5464 termPQExpBuffer(&title);
5465 return false;
5466 }
5467
5468 if (nspname)
5469 printfPQExpBuffer(&title, _("Token types for parser \"%s.%s\""),
5470 nspname, prsname);
5471 else
5472 printfPQExpBuffer(&title, _("Token types for parser \"%s\""), prsname);
5473 myopt.title = title.data;
5474 myopt.footers = NULL;
5475 myopt.topt.default_footer = true;
5476 myopt.translate_header = true;
5477 myopt.translate_columns = NULL;
5478 myopt.n_translate_columns = 0;
5479
5480 printQuery(res, &myopt, pset.queryFout, false, pset.logfile);
5481
5482 termPQExpBuffer(&title);
5483 PQclear(res);
5484 return true;
5485 }
5486
5487
5488 /*
5489 * \dFd
5490 * list text search dictionaries
5491 */
5492 bool
5493 listTSDictionaries(const char *pattern, bool verbose)
5494 {
5495 PQExpBufferData buf;
5496 PGresult *res;
5497 printQueryOpt myopt = pset.popt;
5498
5499 initPQExpBuffer(&buf);
5500
5501 printfPQExpBuffer(&buf,
5502 "SELECT\n"
5503 " n.nspname as \"%s\",\n"
5504 " d.dictname as \"%s\",\n",
5505 gettext_noop("Schema"),
5506 gettext_noop("Name"));
5507
5508 if (verbose)
5509 {
5510 appendPQExpBuffer(&buf,
5511 " ( SELECT COALESCE(nt.nspname, '(null)')::pg_catalog.text || '.' || t.tmplname FROM\n"
5512 " pg_catalog.pg_ts_template t\n"
5513 " LEFT JOIN pg_catalog.pg_namespace nt ON nt.oid = t.tmplnamespace\n"
5514 " WHERE d.dicttemplate = t.oid ) AS \"%s\",\n"
5515 " d.dictinitoption as \"%s\",\n",
5516 gettext_noop("Template"),
5517 gettext_noop("Init options"));
5518 }
5519
5520 appendPQExpBuffer(&buf,
5521 " pg_catalog.obj_description(d.oid, 'pg_ts_dict') as \"%s\"\n",
5522 gettext_noop("Description"));
5523
5524 appendPQExpBufferStr(&buf, "FROM pg_catalog.pg_ts_dict d\n"
5525 "LEFT JOIN pg_catalog.pg_namespace n ON n.oid = d.dictnamespace\n");
5526
5527 if (!validateSQLNamePattern(&buf, pattern, false, false,
5528 "n.nspname", "d.dictname", NULL,
5529 "pg_catalog.pg_ts_dict_is_visible(d.oid)",
5530 NULL, 3))
5531 {
5532 termPQExpBuffer(&buf);
5533 return false;
5534 }
5535
5536 appendPQExpBufferStr(&buf, "ORDER BY 1, 2;");
5537
5538 res = PSQLexec(buf.data);
5539 termPQExpBuffer(&buf);
5540 if (!res)
5541 return false;
5542
5543 myopt.title = _("List of text search dictionaries");
5544 myopt.translate_header = true;
5545
5546 printQuery(res, &myopt, pset.queryFout, false, pset.logfile);
5547
5548 PQclear(res);
5549 return true;
5550 }
5551
5552
5553 /*
5554 * \dFt
5555 * list text search templates
5556 */
5557 bool
5558 listTSTemplates(const char *pattern, bool verbose)
5559 {
5560 PQExpBufferData buf;
5561 PGresult *res;
5562 printQueryOpt myopt = pset.popt;
5563
5564 initPQExpBuffer(&buf);
5565
5566 if (verbose)
5567 printfPQExpBuffer(&buf,
5568 "SELECT\n"
5569 " n.nspname AS \"%s\",\n"
5570 " t.tmplname AS \"%s\",\n"
5571 " t.tmplinit::pg_catalog.regproc AS \"%s\",\n"
5572 " t.tmpllexize::pg_catalog.regproc AS \"%s\",\n"
5573 " pg_catalog.obj_description(t.oid, 'pg_ts_template') AS \"%s\"\n",
5574 gettext_noop("Schema"),
5575 gettext_noop("Name"),
5576 gettext_noop("Init"),
5577 gettext_noop("Lexize"),
5578 gettext_noop("Description"));
5579 else
5580 printfPQExpBuffer(&buf,
5581 "SELECT\n"
5582 " n.nspname AS \"%s\",\n"
5583 " t.tmplname AS \"%s\",\n"
5584 " pg_catalog.obj_description(t.oid, 'pg_ts_template') AS \"%s\"\n",
5585 gettext_noop("Schema"),
5586 gettext_noop("Name"),
5587 gettext_noop("Description"));
5588
5589 appendPQExpBufferStr(&buf, "FROM pg_catalog.pg_ts_template t\n"
5590 "LEFT JOIN pg_catalog.pg_namespace n ON n.oid = t.tmplnamespace\n");
5591
5592 if (!validateSQLNamePattern(&buf, pattern, false, false,
5593 "n.nspname", "t.tmplname", NULL,
5594 "pg_catalog.pg_ts_template_is_visible(t.oid)",
5595 NULL, 3))
5596 {
5597 termPQExpBuffer(&buf);
5598 return false;
5599 }
5600
5601 appendPQExpBufferStr(&buf, "ORDER BY 1, 2;");
5602
5603 res = PSQLexec(buf.data);
5604 termPQExpBuffer(&buf);
5605 if (!res)
5606 return false;
5607
5608 myopt.title = _("List of text search templates");
5609 myopt.translate_header = true;
5610
5611 printQuery(res, &myopt, pset.queryFout, false, pset.logfile);
5612
5613 PQclear(res);
5614 return true;
5615 }
5616
5617
5618 /*
5619 * \dF
5620 * list text search configurations
5621 */
5622 bool
5623 listTSConfigs(const char *pattern, bool verbose)
5624 {
5625 PQExpBufferData buf;
5626 PGresult *res;
5627 printQueryOpt myopt = pset.popt;
5628
5629 if (verbose)
5630 return listTSConfigsVerbose(pattern);
5631
5632 initPQExpBuffer(&buf);
5633
5634 printfPQExpBuffer(&buf,
5635 "SELECT\n"
5636 " n.nspname as \"%s\",\n"
5637 " c.cfgname as \"%s\",\n"
5638 " pg_catalog.obj_description(c.oid, 'pg_ts_config') as \"%s\"\n"
5639 "FROM pg_catalog.pg_ts_config c\n"
5640 "LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.cfgnamespace\n",
5641 gettext_noop("Schema"),
5642 gettext_noop("Name"),
5643 gettext_noop("Description")
5644 );
5645
5646 if (!validateSQLNamePattern(&buf, pattern, false, false,
5647 "n.nspname", "c.cfgname", NULL,
5648 "pg_catalog.pg_ts_config_is_visible(c.oid)",
5649 NULL, 3))
5650 {
5651 termPQExpBuffer(&buf);
5652 return false;
5653 }
5654
5655 appendPQExpBufferStr(&buf, "ORDER BY 1, 2;");
5656
5657 res = PSQLexec(buf.data);
5658 termPQExpBuffer(&buf);
5659 if (!res)
5660 return false;
5661
5662 myopt.title = _("List of text search configurations");
5663 myopt.translate_header = true;
5664
5665 printQuery(res, &myopt, pset.queryFout, false, pset.logfile);
5666
5667 PQclear(res);
5668 return true;
5669 }
5670
5671 static bool
5672 listTSConfigsVerbose(const char *pattern)
5673 {
5674 PQExpBufferData buf;
5675 PGresult *res;
5676 int i;
5677
5678 initPQExpBuffer(&buf);
5679
5680 printfPQExpBuffer(&buf,
5681 "SELECT c.oid, c.cfgname,\n"
5682 " n.nspname,\n"
5683 " p.prsname,\n"
5684 " np.nspname as pnspname\n"
5685 "FROM pg_catalog.pg_ts_config c\n"
5686 " LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.cfgnamespace,\n"
5687 " pg_catalog.pg_ts_parser p\n"
5688 " LEFT JOIN pg_catalog.pg_namespace np ON np.oid = p.prsnamespace\n"
5689 "WHERE p.oid = c.cfgparser\n"
5690 );
5691
5692 if (!validateSQLNamePattern(&buf, pattern, true, false,
5693 "n.nspname", "c.cfgname", NULL,
5694 "pg_catalog.pg_ts_config_is_visible(c.oid)",
5695 NULL, 3))
5696 {
5697 termPQExpBuffer(&buf);
5698 return false;
5699 }
5700
5701 appendPQExpBufferStr(&buf, "ORDER BY 3, 2;");
5702
5703 res = PSQLexec(buf.data);
5704 termPQExpBuffer(&buf);
5705 if (!res)
5706 return false;
5707
5708 if (PQntuples(res) == 0)
5709 {
5710 if (!pset.quiet)
5711 {
5712 if (pattern)
5713 pg_log_error("Did not find any text search configuration named \"%s\".",
5714 pattern);
5715 else
5716 pg_log_error("Did not find any text search configurations.");
5717 }
5718 PQclear(res);
5719 return false;
5720 }
5721
5722 for (i = 0; i < PQntuples(res); i++)
5723 {
5724 const char *oid;
5725 const char *cfgname;
5726 const char *nspname = NULL;
5727 const char *prsname;
5728 const char *pnspname = NULL;
5729
5730 oid = PQgetvalue(res, i, 0);
5731 cfgname = PQgetvalue(res, i, 1);
5732 if (!PQgetisnull(res, i, 2))
5733 nspname = PQgetvalue(res, i, 2);
5734 prsname = PQgetvalue(res, i, 3);
5735 if (!PQgetisnull(res, i, 4))
5736 pnspname = PQgetvalue(res, i, 4);
5737
5738 if (!describeOneTSConfig(oid, nspname, cfgname, pnspname, prsname))
5739 {
5740 PQclear(res);
5741 return false;
5742 }
5743
5744 if (cancel_pressed)
5745 {
5746 PQclear(res);
5747 return false;
5748 }
5749 }
5750
5751 PQclear(res);
5752 return true;
5753 }
5754
5755 static bool
5756 describeOneTSConfig(const char *oid, const char *nspname, const char *cfgname,
5757 const char *pnspname, const char *prsname)
5758 {
5759 PQExpBufferData buf,
5760 title;
5761 PGresult *res;
5762 printQueryOpt myopt = pset.popt;
5763
5764 initPQExpBuffer(&buf);
5765
5766 printfPQExpBuffer(&buf,
5767 "SELECT\n"
5768 " ( SELECT t.alias FROM\n"
5769 " pg_catalog.ts_token_type(c.cfgparser) AS t\n"
5770 " WHERE t.tokid = m.maptokentype ) AS \"%s\",\n"
5771 " pg_catalog.btrim(\n"
5772 " ARRAY( SELECT mm.mapdict::pg_catalog.regdictionary\n"
5773 " FROM pg_catalog.pg_ts_config_map AS mm\n"
5774 " WHERE mm.mapcfg = m.mapcfg AND mm.maptokentype = m.maptokentype\n"
5775 " ORDER BY mapcfg, maptokentype, mapseqno\n"
5776 " ) :: pg_catalog.text,\n"
5777 " '{}') AS \"%s\"\n"
5778 "FROM pg_catalog.pg_ts_config AS c, pg_catalog.pg_ts_config_map AS m\n"
5779 "WHERE c.oid = '%s' AND m.mapcfg = c.oid\n"
5780 "GROUP BY m.mapcfg, m.maptokentype, c.cfgparser\n"
5781 "ORDER BY 1;",
5782 gettext_noop("Token"),
5783 gettext_noop("Dictionaries"),
5784 oid);
5785
5786 res = PSQLexec(buf.data);
5787 termPQExpBuffer(&buf);
5788 if (!res)
5789 return false;
5790
5791 initPQExpBuffer(&title);
5792
5793 if (nspname)
5794 appendPQExpBuffer(&title, _("Text search configuration \"%s.%s\""),
5795 nspname, cfgname);
5796 else
5797 appendPQExpBuffer(&title, _("Text search configuration \"%s\""),
5798 cfgname);
5799
5800 if (pnspname)
5801 appendPQExpBuffer(&title, _("\nParser: \"%s.%s\""),
5802 pnspname, prsname);
5803 else
5804 appendPQExpBuffer(&title, _("\nParser: \"%s\""),
5805 prsname);
5806
5807 myopt.title = title.data;
5808 myopt.footers = NULL;
5809 myopt.topt.default_footer = false;
5810 myopt.translate_header = true;
5811
5812 printQuery(res, &myopt, pset.queryFout, false, pset.logfile);
5813
5814 termPQExpBuffer(&title);
5815
5816 PQclear(res);
5817 return true;
5818 }
5819
5820
5821 /*
5822 * \dew
5823 *
5824 * Describes foreign-data wrappers
5825 */
5826 bool
5827 listForeignDataWrappers(const char *pattern, bool verbose)
5828 {
5829 PQExpBufferData buf;
5830 PGresult *res;
5831 printQueryOpt myopt = pset.popt;
5832
5833 initPQExpBuffer(&buf);
5834 printfPQExpBuffer(&buf,
5835 "SELECT fdw.fdwname AS \"%s\",\n"
5836 " pg_catalog.pg_get_userbyid(fdw.fdwowner) AS \"%s\",\n"
5837 " fdw.fdwhandler::pg_catalog.regproc AS \"%s\",\n"
5838 " fdw.fdwvalidator::pg_catalog.regproc AS \"%s\"",
5839 gettext_noop("Name"),
5840 gettext_noop("Owner"),
5841 gettext_noop("Handler"),
5842 gettext_noop("Validator"));
5843
5844 if (verbose)
5845 {
5846 appendPQExpBufferStr(&buf, ",\n ");
5847 printACLColumn(&buf, "fdwacl");
5848 appendPQExpBuffer(&buf,
5849 ",\n CASE WHEN fdwoptions IS NULL THEN '' ELSE "
5850 " '(' || pg_catalog.array_to_string(ARRAY(SELECT "
5851 " pg_catalog.quote_ident(option_name) || ' ' || "
5852 " pg_catalog.quote_literal(option_value) FROM "
5853 " pg_catalog.pg_options_to_table(fdwoptions)), ', ') || ')' "
5854 " END AS \"%s\""
5855 ",\n d.description AS \"%s\" ",
5856 gettext_noop("FDW options"),
5857 gettext_noop("Description"));
5858 }
5859
5860 appendPQExpBufferStr(&buf, "\nFROM pg_catalog.pg_foreign_data_wrapper fdw\n");
5861
5862 if (verbose)
5863 appendPQExpBufferStr(&buf,
5864 "LEFT JOIN pg_catalog.pg_description d\n"
5865 " ON d.classoid = fdw.tableoid "
5866 "AND d.objoid = fdw.oid AND d.objsubid = 0\n");
5867
5868 if (!validateSQLNamePattern(&buf, pattern, false, false,
5869 NULL, "fdwname", NULL, NULL,
5870 NULL, 1))
5871 {
5872 termPQExpBuffer(&buf);
5873 return false;
5874 }
5875
5876 appendPQExpBufferStr(&buf, "ORDER BY 1;");
5877
5878 res = PSQLexec(buf.data);
5879 termPQExpBuffer(&buf);
5880 if (!res)
5881 return false;
5882
5883 myopt.title = _("List of foreign-data wrappers");
5884 myopt.translate_header = true;
5885
5886 printQuery(res, &myopt, pset.queryFout, false, pset.logfile);
5887
5888 PQclear(res);
5889 return true;
5890 }
5891
5892 /*
5893 * \des
5894 *
5895 * Describes foreign servers.
5896 */
5897 bool
5898 listForeignServers(const char *pattern, bool verbose)
5899 {
5900 PQExpBufferData buf;
5901 PGresult *res;
5902 printQueryOpt myopt = pset.popt;
5903
5904 initPQExpBuffer(&buf);
5905 printfPQExpBuffer(&buf,
5906 "SELECT s.srvname AS \"%s\",\n"
5907 " pg_catalog.pg_get_userbyid(s.srvowner) AS \"%s\",\n"
5908 " f.fdwname AS \"%s\"",
5909 gettext_noop("Name"),
5910 gettext_noop("Owner"),
5911 gettext_noop("Foreign-data wrapper"));
5912
5913 if (verbose)
5914 {
5915 appendPQExpBufferStr(&buf, ",\n ");
5916 printACLColumn(&buf, "s.srvacl");
5917 appendPQExpBuffer(&buf,
5918 ",\n"
5919 " s.srvtype AS \"%s\",\n"
5920 " s.srvversion AS \"%s\",\n"
5921 " CASE WHEN srvoptions IS NULL THEN '' ELSE "
5922 " '(' || pg_catalog.array_to_string(ARRAY(SELECT "
5923 " pg_catalog.quote_ident(option_name) || ' ' || "
5924 " pg_catalog.quote_literal(option_value) FROM "
5925 " pg_catalog.pg_options_to_table(srvoptions)), ', ') || ')' "
5926 " END AS \"%s\",\n"
5927 " d.description AS \"%s\"",
5928 gettext_noop("Type"),
5929 gettext_noop("Version"),
5930 gettext_noop("FDW options"),
5931 gettext_noop("Description"));
5932 }
5933
5934 appendPQExpBufferStr(&buf,
5935 "\nFROM pg_catalog.pg_foreign_server s\n"
5936 " JOIN pg_catalog.pg_foreign_data_wrapper f ON f.oid=s.srvfdw\n");
5937
5938 if (verbose)
5939 appendPQExpBufferStr(&buf,
5940 "LEFT JOIN pg_catalog.pg_description d\n "
5941 "ON d.classoid = s.tableoid AND d.objoid = s.oid "
5942 "AND d.objsubid = 0\n");
5943
5944 if (!validateSQLNamePattern(&buf, pattern, false, false,
5945 NULL, "s.srvname", NULL, NULL,
5946 NULL, 1))
5947 {
5948 termPQExpBuffer(&buf);
5949 return false;
5950 }
5951
5952 appendPQExpBufferStr(&buf, "ORDER BY 1;");
5953
5954 res = PSQLexec(buf.data);
5955 termPQExpBuffer(&buf);
5956 if (!res)
5957 return false;
5958
5959 myopt.title = _("List of foreign servers");
5960 myopt.translate_header = true;
5961
5962 printQuery(res, &myopt, pset.queryFout, false, pset.logfile);
5963
5964 PQclear(res);
5965 return true;
5966 }
5967
5968 /*
5969 * \deu
5970 *
5971 * Describes user mappings.
5972 */
5973 bool
5974 listUserMappings(const char *pattern, bool verbose)
5975 {
5976 PQExpBufferData buf;
5977 PGresult *res;
5978 printQueryOpt myopt = pset.popt;
5979
5980 initPQExpBuffer(&buf);
5981 printfPQExpBuffer(&buf,
5982 "SELECT um.srvname AS \"%s\",\n"
5983 " um.usename AS \"%s\"",
5984 gettext_noop("Server"),
5985 gettext_noop("User name"));
5986
5987 if (verbose)
5988 appendPQExpBuffer(&buf,
5989 ",\n CASE WHEN umoptions IS NULL THEN '' ELSE "
5990 " '(' || pg_catalog.array_to_string(ARRAY(SELECT "
5991 " pg_catalog.quote_ident(option_name) || ' ' || "
5992 " pg_catalog.quote_literal(option_value) FROM "
5993 " pg_catalog.pg_options_to_table(umoptions)), ', ') || ')' "
5994 " END AS \"%s\"",
5995 gettext_noop("FDW options"));
5996
5997 appendPQExpBufferStr(&buf, "\nFROM pg_catalog.pg_user_mappings um\n");
5998
5999 if (!validateSQLNamePattern(&buf, pattern, false, false,
6000 NULL, "um.srvname", "um.usename", NULL,
6001 NULL, 1))
6002 {
6003 termPQExpBuffer(&buf);
6004 return false;
6005 }
6006
6007 appendPQExpBufferStr(&buf, "ORDER BY 1, 2;");
6008
6009 res = PSQLexec(buf.data);
6010 termPQExpBuffer(&buf);
6011 if (!res)
6012 return false;
6013
6014 myopt.title = _("List of user mappings");
6015 myopt.translate_header = true;
6016
6017 printQuery(res, &myopt, pset.queryFout, false, pset.logfile);
6018
6019 PQclear(res);
6020 return true;
6021 }
6022
6023 /*
6024 * \det
6025 *
6026 * Describes foreign tables.
6027 */
6028 bool
6029 listForeignTables(const char *pattern, bool verbose)
6030 {
6031 PQExpBufferData buf;
6032 PGresult *res;
6033 printQueryOpt myopt = pset.popt;
6034
6035 initPQExpBuffer(&buf);
6036 printfPQExpBuffer(&buf,
6037 "SELECT n.nspname AS \"%s\",\n"
6038 " c.relname AS \"%s\",\n"
6039 " s.srvname AS \"%s\"",
6040 gettext_noop("Schema"),
6041 gettext_noop("Table"),
6042 gettext_noop("Server"));
6043
6044 if (verbose)
6045 appendPQExpBuffer(&buf,
6046 ",\n CASE WHEN ftoptions IS NULL THEN '' ELSE "
6047 " '(' || pg_catalog.array_to_string(ARRAY(SELECT "
6048 " pg_catalog.quote_ident(option_name) || ' ' || "
6049 " pg_catalog.quote_literal(option_value) FROM "
6050 " pg_catalog.pg_options_to_table(ftoptions)), ', ') || ')' "
6051 " END AS \"%s\",\n"
6052 " d.description AS \"%s\"",
6053 gettext_noop("FDW options"),
6054 gettext_noop("Description"));
6055
6056 appendPQExpBufferStr(&buf,
6057 "\nFROM pg_catalog.pg_foreign_table ft\n"
6058 " INNER JOIN pg_catalog.pg_class c"
6059 " ON c.oid = ft.ftrelid\n"
6060 " INNER JOIN pg_catalog.pg_namespace n"
6061 " ON n.oid = c.relnamespace\n"
6062 " INNER JOIN pg_catalog.pg_foreign_server s"
6063 " ON s.oid = ft.ftserver\n");
6064 if (verbose)
6065 appendPQExpBufferStr(&buf,
6066 " LEFT JOIN pg_catalog.pg_description d\n"
6067 " ON d.classoid = c.tableoid AND "
6068 "d.objoid = c.oid AND d.objsubid = 0\n");
6069
6070 if (!validateSQLNamePattern(&buf, pattern, false, false,
6071 "n.nspname", "c.relname", NULL,
6072 "pg_catalog.pg_table_is_visible(c.oid)",
6073 NULL, 3))
6074 {
6075 termPQExpBuffer(&buf);
6076 return false;
6077 }
6078
6079 appendPQExpBufferStr(&buf, "ORDER BY 1, 2;");
6080
6081 res = PSQLexec(buf.data);
6082 termPQExpBuffer(&buf);
6083 if (!res)
6084 return false;
6085
6086 myopt.title = _("List of foreign tables");
6087 myopt.translate_header = true;
6088
6089 printQuery(res, &myopt, pset.queryFout, false, pset.logfile);
6090
6091 PQclear(res);
6092 return true;
6093 }
6094
6095 /*
6096 * \dx
6097 *
6098 * Briefly describes installed extensions.
6099 */
6100 bool
6101 listExtensions(const char *pattern)
6102 {
6103 PQExpBufferData buf;
6104 PGresult *res;
6105 printQueryOpt myopt = pset.popt;
6106
6107 initPQExpBuffer(&buf);
6108 printfPQExpBuffer(&buf,
6109 "SELECT e.extname AS \"%s\", "
6110 "e.extversion AS \"%s\", n.nspname AS \"%s\", c.description AS \"%s\"\n"
6111 "FROM pg_catalog.pg_extension e "
6112 "LEFT JOIN pg_catalog.pg_namespace n ON n.oid = e.extnamespace "
6113 "LEFT JOIN pg_catalog.pg_description c ON c.objoid = e.oid "
6114 "AND c.classoid = 'pg_catalog.pg_extension'::pg_catalog.regclass\n",
6115 gettext_noop("Name"),
6116 gettext_noop("Version"),
6117 gettext_noop("Schema"),
6118 gettext_noop("Description"));
6119
6120 if (!validateSQLNamePattern(&buf, pattern,
6121 false, false,
6122 NULL, "e.extname", NULL,
6123 NULL,
6124 NULL, 1))
6125 {
6126 termPQExpBuffer(&buf);
6127 return false;
6128 }
6129
6130 appendPQExpBufferStr(&buf, "ORDER BY 1;");
6131
6132 res = PSQLexec(buf.data);
6133 termPQExpBuffer(&buf);
6134 if (!res)
6135 return false;
6136
6137 myopt.title = _("List of installed extensions");
6138 myopt.translate_header = true;
6139
6140 printQuery(res, &myopt, pset.queryFout, false, pset.logfile);
6141
6142 PQclear(res);
6143 return true;
6144 }
6145
6146 /*
6147 * \dx+
6148 *
6149 * List contents of installed extensions.
6150 */
6151 bool
6152 listExtensionContents(const char *pattern)
6153 {
6154 PQExpBufferData buf;
6155 PGresult *res;
6156 int i;
6157
6158 initPQExpBuffer(&buf);
6159 printfPQExpBuffer(&buf,
6160 "SELECT e.extname, e.oid\n"
6161 "FROM pg_catalog.pg_extension e\n");
6162
6163 if (!validateSQLNamePattern(&buf, pattern,
6164 false, false,
6165 NULL, "e.extname", NULL,
6166 NULL,
6167 NULL, 1))
6168 {
6169 termPQExpBuffer(&buf);
6170 return false;
6171 }
6172
6173 appendPQExpBufferStr(&buf, "ORDER BY 1;");
6174
6175 res = PSQLexec(buf.data);
6176 termPQExpBuffer(&buf);
6177 if (!res)
6178 return false;
6179
6180 if (PQntuples(res) == 0)
6181 {
6182 if (!pset.quiet)
6183 {
6184 if (pattern)
6185 pg_log_error("Did not find any extension named \"%s\".",
6186 pattern);
6187 else
6188 pg_log_error("Did not find any extensions.");
6189 }
6190 PQclear(res);
6191 return false;
6192 }
6193
6194 for (i = 0; i < PQntuples(res); i++)
6195 {
6196 const char *extname;
6197 const char *oid;
6198
6199 extname = PQgetvalue(res, i, 0);
6200 oid = PQgetvalue(res, i, 1);
6201
6202 if (!listOneExtensionContents(extname, oid))
6203 {
6204 PQclear(res);
6205 return false;
6206 }
6207 if (cancel_pressed)
6208 {
6209 PQclear(res);
6210 return false;
6211 }
6212 }
6213
6214 PQclear(res);
6215 return true;
6216 }
6217
6218 static bool
6219 listOneExtensionContents(const char *extname, const char *oid)
6220 {
6221 PQExpBufferData buf;
6222 PGresult *res;
6223 PQExpBufferData title;
6224 printQueryOpt myopt = pset.popt;
6225
6226 initPQExpBuffer(&buf);
6227 printfPQExpBuffer(&buf,
6228 "SELECT pg_catalog.pg_describe_object(classid, objid, 0) AS \"%s\"\n"
6229 "FROM pg_catalog.pg_depend\n"
6230 "WHERE refclassid = 'pg_catalog.pg_extension'::pg_catalog.regclass AND refobjid = '%s' AND deptype = 'e'\n"
6231 "ORDER BY 1;",
6232 gettext_noop("Object description"),
6233 oid);
6234
6235 res = PSQLexec(buf.data);
6236 termPQExpBuffer(&buf);
6237 if (!res)
6238 return false;
6239
6240 initPQExpBuffer(&title);
6241 printfPQExpBuffer(&title, _("Objects in extension \"%s\""), extname);
6242 myopt.title = title.data;
6243 myopt.translate_header = true;
6244
6245 printQuery(res, &myopt, pset.queryFout, false, pset.logfile);
6246
6247 termPQExpBuffer(&title);
6248 PQclear(res);
6249 return true;
6250 }
6251
6252 /*
6253 * validateSQLNamePattern
6254 *
6255 * Wrapper around string_utils's processSQLNamePattern which also checks the
6256 * pattern's validity. In addition to that function's parameters, takes a
6257 * 'maxparts' parameter specifying the maximum number of dotted names the
6258 * pattern is allowed to have, and a 'added_clause' parameter that returns by
6259 * reference whether a clause was added to 'buf'. Returns whether the pattern
6260 * passed validation, after logging any errors.
6261 */
6262 static bool
6263 validateSQLNamePattern(PQExpBuffer buf, const char *pattern, bool have_where,
6264 bool force_escape, const char *schemavar,
6265 const char *namevar, const char *altnamevar,
6266 const char *visibilityrule, bool *added_clause,
6267 int maxparts)
6268 {
6269 PQExpBufferData dbbuf;
6270 int dotcnt;
6271 bool added;
6272
6273 initPQExpBuffer(&dbbuf);
6274 added = processSQLNamePattern(pset.db, buf, pattern, have_where, force_escape,
6275 schemavar, namevar, altnamevar,
6276 visibilityrule, &dbbuf, &dotcnt);
6277 if (added_clause != NULL)
6278 *added_clause = added;
6279
6280 if (dotcnt >= maxparts)
6281 {
6282 pg_log_error("improper qualified name (too many dotted names): %s",
6283 pattern);
6284 goto error_return;
6285 }
6286
6287 if (maxparts > 1 && dotcnt == maxparts - 1)
6288 {
6289 if (PQdb(pset.db) == NULL)
6290 {
6291 pg_log_error("You are currently not connected to a database.");
6292 goto error_return;
6293 }
6294 if (strcmp(PQdb(pset.db), dbbuf.data) != 0)
6295 {
6296 pg_log_error("cross-database references are not implemented: %s",
6297 pattern);
6298 goto error_return;
6299 }
6300 }
6301 termPQExpBuffer(&dbbuf);
6302 return true;
6303
6304 error_return:
6305 termPQExpBuffer(&dbbuf);
6306 return false;
6307 }
6308
6309 /*
6310 * \dRp
6311 * Lists publications.
6312 *
6313 * Takes an optional regexp to select particular publications
6314 */
6315 bool
6316 listPublications(const char *pattern)
6317 {
6318 PQExpBufferData buf;
6319 PGresult *res;
6320 printQueryOpt myopt = pset.popt;
6321 static const bool translate_columns[] = {false, false, false, false, false, false, false, false, false};
6322
6323 if (pset.sversion < 100000)
6324 {
6325 char sverbuf[32];
6326
6327 pg_log_error("The server (version %s) does not support publications.",
6328 formatPGVersionNumber(pset.sversion, false,
6329 sverbuf, sizeof(sverbuf)));
6330 return true;
6331 }
6332
6333 initPQExpBuffer(&buf);
6334
6335 printfPQExpBuffer(&buf,
6336 "SELECT pubname AS \"%s\",\n"
6337 " pg_catalog.pg_get_userbyid(pubowner) AS \"%s\",\n"
6338 " puballtables AS \"%s\",\n"
6339 " pubinsert AS \"%s\",\n"
6340 " pubupdate AS \"%s\",\n"
6341 " pubdelete AS \"%s\"",
6342 gettext_noop("Name"),
6343 gettext_noop("Owner"),
6344 gettext_noop("All tables"),
6345 gettext_noop("Inserts"),
6346 gettext_noop("Updates"),
6347 gettext_noop("Deletes"));
6348 if (pset.sversion >= 110000)
6349 appendPQExpBuffer(&buf,
6350 ",\n pubtruncate AS \"%s\"",
6351 gettext_noop("Truncates"));
6352 if (pset.sversion >= 180000)
6353 appendPQExpBuffer(&buf,
6354 ",\n pubgencols AS \"%s\"",
6355 gettext_noop("Generated columns"));
6356 if (pset.sversion >= 130000)
6357 appendPQExpBuffer(&buf,
6358 ",\n pubviaroot AS \"%s\"",
6359 gettext_noop("Via root"));
6360
6361 appendPQExpBufferStr(&buf,
6362 "\nFROM pg_catalog.pg_publication\n");
6363
6364 if (!validateSQLNamePattern(&buf, pattern, false, false,
6365 NULL, "pubname", NULL,
6366 NULL,
6367 NULL, 1))
6368 {
6369 termPQExpBuffer(&buf);
6370 return false;
6371 }
6372
6373 appendPQExpBufferStr(&buf, "ORDER BY 1;");
6374
6375 res = PSQLexec(buf.data);
6376 termPQExpBuffer(&buf);
6377 if (!res)
6378 return false;
6379
6380 myopt.title = _("List of publications");
6381 myopt.translate_header = true;
6382 myopt.translate_columns = translate_columns;
6383 myopt.n_translate_columns = lengthof(translate_columns);
6384
6385 printQuery(res, &myopt, pset.queryFout, false, pset.logfile);
6386
6387 PQclear(res);
6388
6389 return true;
6390 }
6391
6392 /*
6393 * Add footer to publication description.
6394 */
6395 static bool
6396 addFooterToPublicationDesc(PQExpBuffer buf, const char *footermsg,
6397 bool as_schema, printTableContent *const cont)
6398 {
6399 PGresult *res;
6400 int count = 0;
6401 int i = 0;
6402
6403 res = PSQLexec(buf->data);
6404 if (!res)
6405 return false;
6406 else
6407 count = PQntuples(res);
6408
6409 if (count > 0)
6410 printTableAddFooter(cont, footermsg);
6411
6412 for (i = 0; i < count; i++)
6413 {
6414 if (as_schema)
6415 printfPQExpBuffer(buf, " \"%s\"", PQgetvalue(res, i, 0));
6416 else
6417 {
6418 printfPQExpBuffer(buf, " \"%s.%s\"", PQgetvalue(res, i, 0),
6419 PQgetvalue(res, i, 1));
6420
6421 if (!PQgetisnull(res, i, 3))
6422 appendPQExpBuffer(buf, " (%s)", PQgetvalue(res, i, 3));
6423
6424 if (!PQgetisnull(res, i, 2))
6425 appendPQExpBuffer(buf, " WHERE %s", PQgetvalue(res, i, 2));
6426 }
6427
6428 printTableAddFooter(cont, buf->data);
6429 }
6430
6431 PQclear(res);
6432 return true;
6433 }
6434
6435 /*
6436 * \dRp+
6437 * Describes publications including the contents.
6438 *
6439 * Takes an optional regexp to select particular publications
6440 */
6441 bool
6442 describePublications(const char *pattern)
6443 {
6444 PQExpBufferData buf;
6445 int i;
6446 PGresult *res;
6447 bool has_pubtruncate;
6448 bool has_pubgencols;
6449 bool has_pubviaroot;
6450
6451 PQExpBufferData title;
6452 printTableContent cont;
6453
6454 if (pset.sversion < 100000)
6455 {
6456 char sverbuf[32];
6457
6458 pg_log_error("The server (version %s) does not support publications.",
6459 formatPGVersionNumber(pset.sversion, false,
6460 sverbuf, sizeof(sverbuf)));
6461 return true;
6462 }
6463
6464 has_pubtruncate = (pset.sversion >= 110000);
6465 has_pubgencols = (pset.sversion >= 180000);
6466 has_pubviaroot = (pset.sversion >= 130000);
6467
6468 initPQExpBuffer(&buf);
6469
6470 printfPQExpBuffer(&buf,
6471 "SELECT oid, pubname,\n"
6472 " pg_catalog.pg_get_userbyid(pubowner) AS owner,\n"
6473 " puballtables, pubinsert, pubupdate, pubdelete");
6474 if (has_pubtruncate)
6475 appendPQExpBufferStr(&buf,
6476 ", pubtruncate");
6477 if (has_pubgencols)
6478 appendPQExpBufferStr(&buf,
6479 ", pubgencols");
6480 if (has_pubviaroot)
6481 appendPQExpBufferStr(&buf,
6482 ", pubviaroot");
6483
6484 appendPQExpBufferStr(&buf,
6485 "\nFROM pg_catalog.pg_publication\n");
6486
6487 if (!validateSQLNamePattern(&buf, pattern, false, false,
6488 NULL, "pubname", NULL,
6489 NULL,
6490 NULL, 1))
6491 {
6492 termPQExpBuffer(&buf);
6493 return false;
6494 }
6495
6496 appendPQExpBufferStr(&buf, "ORDER BY 2;");
6497
6498 res = PSQLexec(buf.data);
6499 if (!res)
6500 {
6501 termPQExpBuffer(&buf);
6502 return false;
6503 }
6504
6505 if (PQntuples(res) == 0)
6506 {
6507 if (!pset.quiet)
6508 {
6509 if (pattern)
6510 pg_log_error("Did not find any publication named \"%s\".",
6511 pattern);
6512 else
6513 pg_log_error("Did not find any publications.");
6514 }
6515
6516 termPQExpBuffer(&buf);
6517 PQclear(res);
6518 return false;
6519 }
6520
6521 for (i = 0; i < PQntuples(res); i++)
6522 {
6523 const char align = 'l';
6524 int ncols = 5;
6525 int nrows = 1;
6526 char *pubid = PQgetvalue(res, i, 0);
6527 char *pubname = PQgetvalue(res, i, 1);
6528 bool puballtables = strcmp(PQgetvalue(res, i, 3), "t") == 0;
6529 printTableOpt myopt = pset.popt.topt;
6530
6531 if (has_pubtruncate)
6532 ncols++;
6533 if (has_pubgencols)
6534 ncols++;
6535 if (has_pubviaroot)
6536 ncols++;
6537
6538 initPQExpBuffer(&title);
6539 printfPQExpBuffer(&title, _("Publication %s"), pubname);
6540 printTableInit(&cont, &myopt, title.data, ncols, nrows);
6541
6542 printTableAddHeader(&cont, gettext_noop("Owner"), true, align);
6543 printTableAddHeader(&cont, gettext_noop("All tables"), true, align);
6544 printTableAddHeader(&cont, gettext_noop("Inserts"), true, align);
6545 printTableAddHeader(&cont, gettext_noop("Updates"), true, align);
6546 printTableAddHeader(&cont, gettext_noop("Deletes"), true, align);
6547 if (has_pubtruncate)
6548 printTableAddHeader(&cont, gettext_noop("Truncates"), true, align);
6549 if (has_pubgencols)
6550 printTableAddHeader(&cont, gettext_noop("Generated columns"), true, align);
6551 if (has_pubviaroot)
6552 printTableAddHeader(&cont, gettext_noop("Via root"), true, align);
6553
6554 printTableAddCell(&cont, PQgetvalue(res, i, 2), false, false);
6555 printTableAddCell(&cont, PQgetvalue(res, i, 3), false, false);
6556 printTableAddCell(&cont, PQgetvalue(res, i, 4), false, false);
6557 printTableAddCell(&cont, PQgetvalue(res, i, 5), false, false);
6558 printTableAddCell(&cont, PQgetvalue(res, i, 6), false, false);
6559 if (has_pubtruncate)
6560 printTableAddCell(&cont, PQgetvalue(res, i, 7), false, false);
6561 if (has_pubgencols)
6562 printTableAddCell(&cont, PQgetvalue(res, i, 8), false, false);
6563 if (has_pubviaroot)
6564 printTableAddCell(&cont, PQgetvalue(res, i, 9), false, false);
6565
6566 if (!puballtables)
6567 {
6568 /* Get the tables for the specified publication */
6569 printfPQExpBuffer(&buf,
6570 "SELECT n.nspname, c.relname");
6571 if (pset.sversion >= 150000)
6572 {
6573 appendPQExpBufferStr(&buf,
6574 ", pg_get_expr(pr.prqual, c.oid)");
6575 appendPQExpBufferStr(&buf,
6576 ", (CASE WHEN pr.prattrs IS NOT NULL THEN\n"
6577 " pg_catalog.array_to_string("
6578 " ARRAY(SELECT attname\n"
6579 " FROM\n"
6580 " pg_catalog.generate_series(0, pg_catalog.array_upper(pr.prattrs::pg_catalog.int2[], 1)) s,\n"
6581 " pg_catalog.pg_attribute\n"
6582 " WHERE attrelid = c.oid AND attnum = prattrs[s]), ', ')\n"
6583 " ELSE NULL END)");
6584 }
6585 else
6586 appendPQExpBufferStr(&buf,
6587 ", NULL, NULL");
6588 appendPQExpBuffer(&buf,
6589 "\nFROM pg_catalog.pg_class c,\n"
6590 " pg_catalog.pg_namespace n,\n"
6591 " pg_catalog.pg_publication_rel pr\n"
6592 "WHERE c.relnamespace = n.oid\n"
6593 " AND c.oid = pr.prrelid\n"
6594 " AND pr.prpubid = '%s'\n"
6595 "ORDER BY 1,2", pubid);
6596 if (!addFooterToPublicationDesc(&buf, _("Tables:"), false, &cont))
6597 goto error_return;
6598
6599 if (pset.sversion >= 150000)
6600 {
6601 /* Get the schemas for the specified publication */
6602 printfPQExpBuffer(&buf,
6603 "SELECT n.nspname\n"
6604 "FROM pg_catalog.pg_namespace n\n"
6605 " JOIN pg_catalog.pg_publication_namespace pn ON n.oid = pn.pnnspid\n"
6606 "WHERE pn.pnpubid = '%s'\n"
6607 "ORDER BY 1", pubid);
6608 if (!addFooterToPublicationDesc(&buf, _("Tables from schemas:"),
6609 true, &cont))
6610 goto error_return;
6611 }
6612 }
6613
6614 printTable(&cont, pset.queryFout, false, pset.logfile);
6615 printTableCleanup(&cont);
6616
6617 termPQExpBuffer(&title);
6618 }
6619
6620 termPQExpBuffer(&buf);
6621 PQclear(res);
6622
6623 return true;
6624
6625 error_return:
6626 printTableCleanup(&cont);
6627 PQclear(res);
6628 termPQExpBuffer(&buf);
6629 termPQExpBuffer(&title);
6630 return false;
6631 }
6632
6633 /*
6634 * \dRs
6635 * Describes subscriptions.
6636 *
6637 * Takes an optional regexp to select particular subscriptions
6638 */
6639 bool
6640 describeSubscriptions(const char *pattern, bool verbose)
6641 {
6642 PQExpBufferData buf;
6643 PGresult *res;
6644 printQueryOpt myopt = pset.popt;
6645 static const bool translate_columns[] = {false, false, false, false,
6646 false, false, false, false, false, false, false, false, false, false,
6647 false};
6648
6649 if (pset.sversion < 100000)
6650 {
6651 char sverbuf[32];
6652
6653 pg_log_error("The server (version %s) does not support subscriptions.",
6654 formatPGVersionNumber(pset.sversion, false,
6655 sverbuf, sizeof(sverbuf)));
6656 return true;
6657 }
6658
6659 initPQExpBuffer(&buf);
6660
6661 printfPQExpBuffer(&buf,
6662 "SELECT subname AS \"%s\"\n"
6663 ", pg_catalog.pg_get_userbyid(subowner) AS \"%s\"\n"
6664 ", subenabled AS \"%s\"\n"
6665 ", subpublications AS \"%s\"\n",
6666 gettext_noop("Name"),
6667 gettext_noop("Owner"),
6668 gettext_noop("Enabled"),
6669 gettext_noop("Publication"));
6670
6671 if (verbose)
6672 {
6673 /* Binary mode and streaming are only supported in v14 and higher */
6674 if (pset.sversion >= 140000)
6675 {
6676 appendPQExpBuffer(&buf,
6677 ", subbinary AS \"%s\"\n",
6678 gettext_noop("Binary"));
6679
6680 if (pset.sversion >= 160000)
6681 appendPQExpBuffer(&buf,
6682 ", (CASE substream\n"
6683 " WHEN " CppAsString2(LOGICALREP_STREAM_OFF) " THEN 'off'\n"
6684 " WHEN " CppAsString2(LOGICALREP_STREAM_ON) " THEN 'on'\n"
6685 " WHEN " CppAsString2(LOGICALREP_STREAM_PARALLEL) " THEN 'parallel'\n"
6686 " END) AS \"%s\"\n",
6687 gettext_noop("Streaming"));
6688 else
6689 appendPQExpBuffer(&buf,
6690 ", substream AS \"%s\"\n",
6691 gettext_noop("Streaming"));
6692 }
6693
6694 /* Two_phase and disable_on_error are only supported in v15 and higher */
6695 if (pset.sversion >= 150000)
6696 appendPQExpBuffer(&buf,
6697 ", subtwophasestate AS \"%s\"\n"
6698 ", subdisableonerr AS \"%s\"\n",
6699 gettext_noop("Two-phase commit"),
6700 gettext_noop("Disable on error"));
6701
6702 if (pset.sversion >= 160000)
6703 appendPQExpBuffer(&buf,
6704 ", suborigin AS \"%s\"\n"
6705 ", subpasswordrequired AS \"%s\"\n"
6706 ", subrunasowner AS \"%s\"\n",
6707 gettext_noop("Origin"),
6708 gettext_noop("Password required"),
6709 gettext_noop("Run as owner?"));
6710
6711 if (pset.sversion >= 170000)
6712 appendPQExpBuffer(&buf,
6713 ", subfailover AS \"%s\"\n",
6714 gettext_noop("Failover"));
6715
6716 appendPQExpBuffer(&buf,
6717 ", subsynccommit AS \"%s\"\n"
6718 ", subconninfo AS \"%s\"\n",
6719 gettext_noop("Synchronous commit"),
6720 gettext_noop("Conninfo"));
6721
6722 /* Skip LSN is only supported in v15 and higher */
6723 if (pset.sversion >= 150000)
6724 appendPQExpBuffer(&buf,
6725 ", subskiplsn AS \"%s\"\n",
6726 gettext_noop("Skip LSN"));
6727 }
6728
6729 /* Only display subscriptions in current database. */
6730 appendPQExpBufferStr(&buf,
6731 "FROM pg_catalog.pg_subscription\n"
6732 "WHERE subdbid = (SELECT oid\n"
6733 " FROM pg_catalog.pg_database\n"
6734 " WHERE datname = pg_catalog.current_database())");
6735
6736 if (!validateSQLNamePattern(&buf, pattern, true, false,
6737 NULL, "subname", NULL,
6738 NULL,
6739 NULL, 1))
6740 {
6741 termPQExpBuffer(&buf);
6742 return false;
6743 }
6744
6745 appendPQExpBufferStr(&buf, "ORDER BY 1;");
6746
6747 res = PSQLexec(buf.data);
6748 termPQExpBuffer(&buf);
6749 if (!res)
6750 return false;
6751
6752 myopt.title = _("List of subscriptions");
6753 myopt.translate_header = true;
6754 myopt.translate_columns = translate_columns;
6755 myopt.n_translate_columns = lengthof(translate_columns);
6756
6757 printQuery(res, &myopt, pset.queryFout, false, pset.logfile);
6758
6759 PQclear(res);
6760 return true;
6761 }
6762
6763 /*
6764 * printACLColumn
6765 *
6766 * Helper function for consistently formatting ACL (privilege) columns.
6767 * The proper targetlist entry is appended to buf. Note lack of any
6768 * whitespace or comma decoration.
6769 *
6770 * If you change this, see also the handling of attacl in permissionsList(),
6771 * which can't conveniently use this code.
6772 */
6773 static void
6774 printACLColumn(PQExpBuffer buf, const char *colname)
6775 {
6776 appendPQExpBuffer(buf,
6777 "CASE"
6778 " WHEN pg_catalog.array_length(%s, 1) = 0 THEN '%s'"
6779 " ELSE pg_catalog.array_to_string(%s, E'\\n')"
6780 " END AS \"%s\"",
6781 colname, gettext_noop("(none)"),
6782 colname, gettext_noop("Access privileges"));
6783 }
6784
6785 /*
6786 * \dAc
6787 * Lists operator classes
6788 *
6789 * Takes optional regexps to filter by index access method and input data type.
6790 */
6791 bool
6792 listOperatorClasses(const char *access_method_pattern,
6793 const char *type_pattern, bool verbose)
6794 {
6795 PQExpBufferData buf;
6796 PGresult *res;
6797 printQueryOpt myopt = pset.popt;
6798 bool have_where = false;
6799 static const bool translate_columns[] = {false, false, false, false, false, false, false};
6800
6801 initPQExpBuffer(&buf);
6802
6803 printfPQExpBuffer(&buf,
6804 "SELECT\n"
6805 " am.amname AS \"%s\",\n"
6806 " pg_catalog.format_type(c.opcintype, NULL) AS \"%s\",\n"
6807 " CASE\n"
6808 " WHEN c.opckeytype <> 0 AND c.opckeytype <> c.opcintype\n"
6809 " THEN pg_catalog.format_type(c.opckeytype, NULL)\n"
6810 " ELSE NULL\n"
6811 " END AS \"%s\",\n"
6812 " CASE\n"
6813 " WHEN pg_catalog.pg_opclass_is_visible(c.oid)\n"
6814 " THEN pg_catalog.format('%%I', c.opcname)\n"
6815 " ELSE pg_catalog.format('%%I.%%I', n.nspname, c.opcname)\n"
6816 " END AS \"%s\",\n"
6817 " (CASE WHEN c.opcdefault\n"
6818 " THEN '%s'\n"
6819 " ELSE '%s'\n"
6820 " END) AS \"%s\"",
6821 gettext_noop("AM"),
6822 gettext_noop("Input type"),
6823 gettext_noop("Storage type"),
6824 gettext_noop("Operator class"),
6825 gettext_noop("yes"),
6826 gettext_noop("no"),
6827 gettext_noop("Default?"));
6828 if (verbose)
6829 appendPQExpBuffer(&buf,
6830 ",\n CASE\n"
6831 " WHEN pg_catalog.pg_opfamily_is_visible(of.oid)\n"
6832 " THEN pg_catalog.format('%%I', of.opfname)\n"
6833 " ELSE pg_catalog.format('%%I.%%I', ofn.nspname, of.opfname)\n"
6834 " END AS \"%s\",\n"
6835 " pg_catalog.pg_get_userbyid(c.opcowner) AS \"%s\"\n",
6836 gettext_noop("Operator family"),
6837 gettext_noop("Owner"));
6838 appendPQExpBufferStr(&buf,
6839 "\nFROM pg_catalog.pg_opclass c\n"
6840 " LEFT JOIN pg_catalog.pg_am am on am.oid = c.opcmethod\n"
6841 " LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.opcnamespace\n"
6842 " LEFT JOIN pg_catalog.pg_type t ON t.oid = c.opcintype\n"
6843 " LEFT JOIN pg_catalog.pg_namespace tn ON tn.oid = t.typnamespace\n");
6844 if (verbose)
6845 appendPQExpBufferStr(&buf,
6846 " LEFT JOIN pg_catalog.pg_opfamily of ON of.oid = c.opcfamily\n"
6847 " LEFT JOIN pg_catalog.pg_namespace ofn ON ofn.oid = of.opfnamespace\n");
6848
6849 if (access_method_pattern)
6850 if (!validateSQLNamePattern(&buf, access_method_pattern,
6851 false, false, NULL, "am.amname", NULL, NULL,
6852 &have_where, 1))
6853 goto error_return;
6854 if (type_pattern)
6855 {
6856 /* Match type name pattern against either internal or external name */
6857 if (!validateSQLNamePattern(&buf, type_pattern, have_where, false,
6858 "tn.nspname", "t.typname",
6859 "pg_catalog.format_type(t.oid, NULL)",
6860 "pg_catalog.pg_type_is_visible(t.oid)",
6861 NULL, 3))
6862 goto error_return;
6863 }
6864
6865 appendPQExpBufferStr(&buf, "ORDER BY 1, 2, 4;");
6866 res = PSQLexec(buf.data);
6867 termPQExpBuffer(&buf);
6868 if (!res)
6869 return false;
6870
6871 myopt.title = _("List of operator classes");
6872 myopt.translate_header = true;
6873 myopt.translate_columns = translate_columns;
6874 myopt.n_translate_columns = lengthof(translate_columns);
6875
6876 printQuery(res, &myopt, pset.queryFout, false, pset.logfile);
6877
6878 PQclear(res);
6879 return true;
6880
6881 error_return:
6882 termPQExpBuffer(&buf);
6883 return false;
6884 }
6885
6886 /*
6887 * \dAf
6888 * Lists operator families
6889 *
6890 * Takes optional regexps to filter by index access method and input data type.
6891 */
6892 bool
6893 listOperatorFamilies(const char *access_method_pattern,
6894 const char *type_pattern, bool verbose)
6895 {
6896 PQExpBufferData buf;
6897 PGresult *res;
6898 printQueryOpt myopt = pset.popt;
6899 bool have_where = false;
6900 static const bool translate_columns[] = {false, false, false, false};
6901
6902 initPQExpBuffer(&buf);
6903
6904 printfPQExpBuffer(&buf,
6905 "SELECT\n"
6906 " am.amname AS \"%s\",\n"
6907 " CASE\n"
6908 " WHEN pg_catalog.pg_opfamily_is_visible(f.oid)\n"
6909 " THEN pg_catalog.format('%%I', f.opfname)\n"
6910 " ELSE pg_catalog.format('%%I.%%I', n.nspname, f.opfname)\n"
6911 " END AS \"%s\",\n"
6912 " (SELECT\n"
6913 " pg_catalog.string_agg(pg_catalog.format_type(oc.opcintype, NULL), ', ')\n"
6914 " FROM pg_catalog.pg_opclass oc\n"
6915 " WHERE oc.opcfamily = f.oid) \"%s\"",
6916 gettext_noop("AM"),
6917 gettext_noop("Operator family"),
6918 gettext_noop("Applicable types"));
6919 if (verbose)
6920 appendPQExpBuffer(&buf,
6921 ",\n pg_catalog.pg_get_userbyid(f.opfowner) AS \"%s\"\n",
6922 gettext_noop("Owner"));
6923 appendPQExpBufferStr(&buf,
6924 "\nFROM pg_catalog.pg_opfamily f\n"
6925 " LEFT JOIN pg_catalog.pg_am am on am.oid = f.opfmethod\n"
6926 " LEFT JOIN pg_catalog.pg_namespace n ON n.oid = f.opfnamespace\n");
6927
6928 if (access_method_pattern)
6929 if (!validateSQLNamePattern(&buf, access_method_pattern,
6930 false, false, NULL, "am.amname", NULL, NULL,
6931 &have_where, 1))
6932 goto error_return;
6933 if (type_pattern)
6934 {
6935 appendPQExpBuffer(&buf,
6936 " %s EXISTS (\n"
6937 " SELECT 1\n"
6938 " FROM pg_catalog.pg_type t\n"
6939 " JOIN pg_catalog.pg_opclass oc ON oc.opcintype = t.oid\n"
6940 " LEFT JOIN pg_catalog.pg_namespace tn ON tn.oid = t.typnamespace\n"
6941 " WHERE oc.opcfamily = f.oid\n",
6942 have_where ? "AND" : "WHERE");
6943 /* Match type name pattern against either internal or external name */
6944 if (!validateSQLNamePattern(&buf, type_pattern, true, false,
6945 "tn.nspname", "t.typname",
6946 "pg_catalog.format_type(t.oid, NULL)",
6947 "pg_catalog.pg_type_is_visible(t.oid)",
6948 NULL, 3))
6949 goto error_return;
6950 appendPQExpBufferStr(&buf, " )\n");
6951 }
6952
6953 appendPQExpBufferStr(&buf, "ORDER BY 1, 2;");
6954 res = PSQLexec(buf.data);
6955 termPQExpBuffer(&buf);
6956 if (!res)
6957 return false;
6958
6959 myopt.title = _("List of operator families");
6960 myopt.translate_header = true;
6961 myopt.translate_columns = translate_columns;
6962 myopt.n_translate_columns = lengthof(translate_columns);
6963
6964 printQuery(res, &myopt, pset.queryFout, false, pset.logfile);
6965
6966 PQclear(res);
6967 return true;
6968
6969 error_return:
6970 termPQExpBuffer(&buf);
6971 return false;
6972 }
6973
6974 /*
6975 * \dAo
6976 * Lists operators of operator families
6977 *
6978 * Takes optional regexps to filter by index access method and operator
6979 * family.
6980 */
6981 bool
6982 listOpFamilyOperators(const char *access_method_pattern,
6983 const char *family_pattern, bool verbose)
6984 {
6985 PQExpBufferData buf;
6986 PGresult *res;
6987 printQueryOpt myopt = pset.popt;
6988 bool have_where = false;
6989
6990 static const bool translate_columns[] = {false, false, false, false, false, false};
6991
6992 initPQExpBuffer(&buf);
6993
6994 printfPQExpBuffer(&buf,
6995 "SELECT\n"
6996 " am.amname AS \"%s\",\n"
6997 " CASE\n"
6998 " WHEN pg_catalog.pg_opfamily_is_visible(of.oid)\n"
6999 " THEN pg_catalog.format('%%I', of.opfname)\n"
7000 " ELSE pg_catalog.format('%%I.%%I', nsf.nspname, of.opfname)\n"
7001 " END AS \"%s\",\n"
7002 " o.amopopr::pg_catalog.regoperator AS \"%s\"\n,"
7003 " o.amopstrategy AS \"%s\",\n"
7004 " CASE o.amoppurpose\n"
7005 " WHEN " CppAsString2(AMOP_ORDER) " THEN '%s'\n"
7006 " WHEN " CppAsString2(AMOP_SEARCH) " THEN '%s'\n"
7007 " END AS \"%s\"\n",
7008 gettext_noop("AM"),
7009 gettext_noop("Operator family"),
7010 gettext_noop("Operator"),
7011 gettext_noop("Strategy"),
7012 gettext_noop("ordering"),
7013 gettext_noop("search"),
7014 gettext_noop("Purpose"));
7015
7016 if (verbose)
7017 appendPQExpBuffer(&buf,
7018 ", ofs.opfname AS \"%s\"\n",
7019 gettext_noop("Sort opfamily"));
7020 appendPQExpBufferStr(&buf,
7021 "FROM pg_catalog.pg_amop o\n"
7022 " LEFT JOIN pg_catalog.pg_opfamily of ON of.oid = o.amopfamily\n"
7023 " LEFT JOIN pg_catalog.pg_am am ON am.oid = of.opfmethod AND am.oid = o.amopmethod\n"
7024 " LEFT JOIN pg_catalog.pg_namespace nsf ON of.opfnamespace = nsf.oid\n");
7025 if (verbose)
7026 appendPQExpBufferStr(&buf,
7027 " LEFT JOIN pg_catalog.pg_opfamily ofs ON ofs.oid = o.amopsortfamily\n");
7028
7029 if (access_method_pattern)
7030 {
7031 if (!validateSQLNamePattern(&buf, access_method_pattern,
7032 false, false, NULL, "am.amname",
7033 NULL, NULL,
7034 &have_where, 1))
7035 goto error_return;
7036 }
7037
7038 if (family_pattern)
7039 {
7040 if (!validateSQLNamePattern(&buf, family_pattern, have_where, false,
7041 "nsf.nspname", "of.opfname", NULL, NULL,
7042 NULL, 3))
7043 goto error_return;
7044 }
7045
7046 appendPQExpBufferStr(&buf, "ORDER BY 1, 2,\n"
7047 " o.amoplefttype = o.amoprighttype DESC,\n"
7048 " pg_catalog.format_type(o.amoplefttype, NULL),\n"
7049 " pg_catalog.format_type(o.amoprighttype, NULL),\n"
7050 " o.amopstrategy;");
7051
7052 res = PSQLexec(buf.data);
7053 termPQExpBuffer(&buf);
7054 if (!res)
7055 return false;
7056
7057 myopt.title = _("List of operators of operator families");
7058 myopt.translate_header = true;
7059 myopt.translate_columns = translate_columns;
7060 myopt.n_translate_columns = lengthof(translate_columns);
7061
7062 printQuery(res, &myopt, pset.queryFout, false, pset.logfile);
7063
7064 PQclear(res);
7065 return true;
7066
7067 error_return:
7068 termPQExpBuffer(&buf);
7069 return false;
7070 }
7071
7072 /*
7073 * \dAp
7074 * Lists support functions of operator families
7075 *
7076 * Takes optional regexps to filter by index access method and operator
7077 * family.
7078 */
7079 bool
7080 listOpFamilyFunctions(const char *access_method_pattern,
7081 const char *family_pattern, bool verbose)
7082 {
7083 PQExpBufferData buf;
7084 PGresult *res;
7085 printQueryOpt myopt = pset.popt;
7086 bool have_where = false;
7087 static const bool translate_columns[] = {false, false, false, false, false, false};
7088
7089 initPQExpBuffer(&buf);
7090
7091 printfPQExpBuffer(&buf,
7092 "SELECT\n"
7093 " am.amname AS \"%s\",\n"
7094 " CASE\n"
7095 " WHEN pg_catalog.pg_opfamily_is_visible(of.oid)\n"
7096 " THEN pg_catalog.format('%%I', of.opfname)\n"
7097 " ELSE pg_catalog.format('%%I.%%I', ns.nspname, of.opfname)\n"
7098 " END AS \"%s\",\n"
7099 " pg_catalog.format_type(ap.amproclefttype, NULL) AS \"%s\",\n"
7100 " pg_catalog.format_type(ap.amprocrighttype, NULL) AS \"%s\",\n"
7101 " ap.amprocnum AS \"%s\"\n",
7102 gettext_noop("AM"),
7103 gettext_noop("Operator family"),
7104 gettext_noop("Registered left type"),
7105 gettext_noop("Registered right type"),
7106 gettext_noop("Number"));
7107
7108 if (!verbose)
7109 appendPQExpBuffer(&buf,
7110 ", p.proname AS \"%s\"\n",
7111 gettext_noop("Function"));
7112 else
7113 appendPQExpBuffer(&buf,
7114 ", ap.amproc::pg_catalog.regprocedure AS \"%s\"\n",
7115 gettext_noop("Function"));
7116
7117 appendPQExpBufferStr(&buf,
7118 "FROM pg_catalog.pg_amproc ap\n"
7119 " LEFT JOIN pg_catalog.pg_opfamily of ON of.oid = ap.amprocfamily\n"
7120 " LEFT JOIN pg_catalog.pg_am am ON am.oid = of.opfmethod\n"
7121 " LEFT JOIN pg_catalog.pg_namespace ns ON of.opfnamespace = ns.oid\n"
7122 " LEFT JOIN pg_catalog.pg_proc p ON ap.amproc = p.oid\n");
7123
7124 if (access_method_pattern)
7125 {
7126 if (!validateSQLNamePattern(&buf, access_method_pattern,
7127 false, false, NULL, "am.amname",
7128 NULL, NULL,
7129 &have_where, 1))
7130 goto error_return;
7131 }
7132 if (family_pattern)
7133 {
7134 if (!validateSQLNamePattern(&buf, family_pattern, have_where, false,
7135 "ns.nspname", "of.opfname", NULL, NULL,
7136 NULL, 3))
7137 goto error_return;
7138 }
7139
7140 appendPQExpBufferStr(&buf, "ORDER BY 1, 2,\n"
7141 " ap.amproclefttype = ap.amprocrighttype DESC,\n"
7142 " 3, 4, 5;");
7143
7144 res = PSQLexec(buf.data);
7145 termPQExpBuffer(&buf);
7146 if (!res)
7147 return false;
7148
7149 myopt.title = _("List of support functions of operator families");
7150 myopt.translate_header = true;
7151 myopt.translate_columns = translate_columns;
7152 myopt.n_translate_columns = lengthof(translate_columns);
7153
7154 printQuery(res, &myopt, pset.queryFout, false, pset.logfile);
7155
7156 PQclear(res);
7157 return true;
7158
7159 error_return:
7160 termPQExpBuffer(&buf);
7161 return false;
7162 }
7163
7164 /*
7165 * \dl or \lo_list
7166 * Lists large objects
7167 */
7168 bool
7169 listLargeObjects(bool verbose)
7170 {
7171 PQExpBufferData buf;
7172 PGresult *res;
7173 printQueryOpt myopt = pset.popt;
7174
7175 initPQExpBuffer(&buf);
7176
7177 printfPQExpBuffer(&buf,
7178 "SELECT oid as \"%s\",\n"
7179 " pg_catalog.pg_get_userbyid(lomowner) as \"%s\",\n ",
7180 gettext_noop("ID"),
7181 gettext_noop("Owner"));
7182
7183 if (verbose)
7184 {
7185 printACLColumn(&buf, "lomacl");
7186 appendPQExpBufferStr(&buf, ",\n ");
7187 }
7188
7189 appendPQExpBuffer(&buf,
7190 "pg_catalog.obj_description(oid, 'pg_largeobject') as \"%s\"\n"
7191 "FROM pg_catalog.pg_largeobject_metadata\n"
7192 "ORDER BY oid",
7193 gettext_noop("Description"));
7194
7195 res = PSQLexec(buf.data);
7196 termPQExpBuffer(&buf);
7197 if (!res)
7198 return false;
7199
7200 myopt.title = _("Large objects");
7201 myopt.translate_header = true;
7202
7203 printQuery(res, &myopt, pset.queryFout, false, pset.logfile);
7204
7205 PQclear(res);
7206 return true;
7207 }
Postgresql Clone from git://git.postgresql.org/git/postgresql.git