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