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