2 * psql - the PostgreSQL interactive terminal
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.
9 * Copyright (c) 2000-2016, PostgreSQL Global Development Group
11 * src/bin/psql/describe.c
13 #include "postgres_fe.h"
17 #include "catalog/pg_default_acl.h"
18 #include "fe_utils/string_utils.h"
22 #include "fe_utils/mbprint.h"
23 #include "fe_utils/print.h"
25 #include "variables.h"
28 static bool describeOneTableDetails(const char *schemaname,
29 const char *relationname,
32 static void add_tablespace_footer(printTableContent *const cont, char relkind,
33 Oid tablespace, const bool newline);
34 static void add_role_attribute(PQExpBuffer buf, const char *const str);
35 static bool listTSParsersVerbose(const char *pattern);
36 static bool describeOneTSParser(const char *oid, const char *nspname,
38 static bool listTSConfigsVerbose(const char *pattern);
39 static bool describeOneTSConfig(const char *oid, const char *nspname,
41 const char *pnspname, const char *prsname);
42 static void printACLColumn(PQExpBuffer buf, const char *colname);
43 static bool listOneExtensionContents(const char *extname, const char *oid);
47 * Handlers for various slash commands displaying some sort of list
48 * of things in the database.
50 * Note: try to format the queries to look nice in -E output.
56 * Takes an optional regexp to select particular aggregates
59 describeAggregates(const char *pattern, bool verbose, bool showSystem)
63 printQueryOpt myopt = pset.popt;
65 initPQExpBuffer(&buf);
67 printfPQExpBuffer(&buf,
68 "SELECT n.nspname as \"%s\",\n"
69 " p.proname AS \"%s\",\n"
70 " pg_catalog.format_type(p.prorettype, NULL) AS \"%s\",\n",
71 gettext_noop("Schema"),
73 gettext_noop("Result data type"));
75 if (pset.sversion >= 80400)
76 appendPQExpBuffer(&buf,
77 " CASE WHEN p.pronargs = 0\n"
78 " THEN CAST('*' AS pg_catalog.text)\n"
79 " ELSE pg_catalog.pg_get_function_arguments(p.oid)\n"
81 gettext_noop("Argument data types"));
82 else if (pset.sversion >= 80200)
83 appendPQExpBuffer(&buf,
84 " CASE WHEN p.pronargs = 0\n"
85 " THEN CAST('*' AS pg_catalog.text)\n"
87 " pg_catalog.array_to_string(ARRAY(\n"
89 " pg_catalog.format_type(p.proargtypes[s.i], NULL)\n"
91 " pg_catalog.generate_series(0, pg_catalog.array_upper(p.proargtypes, 1)) AS s(i)\n"
94 gettext_noop("Argument data types"));
96 appendPQExpBuffer(&buf,
97 " pg_catalog.format_type(p.proargtypes[0], NULL) AS \"%s\",\n",
98 gettext_noop("Argument data types"));
100 appendPQExpBuffer(&buf,
101 " pg_catalog.obj_description(p.oid, 'pg_proc') as \"%s\"\n"
102 "FROM pg_catalog.pg_proc p\n"
103 " LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace\n"
104 "WHERE p.proisagg\n",
105 gettext_noop("Description"));
107 if (!showSystem && !pattern)
108 appendPQExpBufferStr(&buf, " AND n.nspname <> 'pg_catalog'\n"
109 " AND n.nspname <> 'information_schema'\n");
111 processSQLNamePattern(pset.db, &buf, pattern, true, false,
112 "n.nspname", "p.proname", NULL,
113 "pg_catalog.pg_function_is_visible(p.oid)");
115 appendPQExpBufferStr(&buf, "ORDER BY 1, 2, 4;");
117 res = PSQLexec(buf.data);
118 termPQExpBuffer(&buf);
122 myopt.nullPrint = NULL;
123 myopt.title = _("List of aggregate functions");
124 myopt.translate_header = true;
126 printQuery(res, &myopt, pset.queryFout, false, pset.logfile);
133 * Takes an optional regexp to select particular access methods
136 describeAccessMethods(const char *pattern, bool verbose)
140 printQueryOpt myopt = pset.popt;
141 static const bool translate_columns[] = {false, true, false, false};
143 if (pset.sversion < 90600)
145 psql_error("The server (version %d.%d) does not support access methods.\n",
146 pset.sversion / 10000, (pset.sversion / 100) % 100);
150 initPQExpBuffer(&buf);
152 printfPQExpBuffer(&buf,
153 "SELECT amname AS \"%s\",\n"
155 " WHEN 'i' THEN '%s'"
157 gettext_noop("Name"),
158 gettext_noop("Index"),
159 gettext_noop("Type"));
163 appendPQExpBuffer(&buf,
164 ",\n amhandler AS \"%s\",\n"
165 " pg_catalog.obj_description(oid, 'pg_am') AS \"%s\"",
166 gettext_noop("Handler"),
167 gettext_noop("Description"));
170 appendPQExpBufferStr(&buf,
171 "\nFROM pg_catalog.pg_am\n");
173 processSQLNamePattern(pset.db, &buf, pattern, false, false,
174 NULL, "amname", NULL,
177 appendPQExpBufferStr(&buf, "ORDER BY 1;");
179 res = PSQLexec(buf.data);
180 termPQExpBuffer(&buf);
184 myopt.nullPrint = NULL;
185 myopt.title = _("List of access methods");
186 myopt.translate_header = true;
187 myopt.translate_columns = translate_columns;
188 myopt.n_translate_columns = lengthof(translate_columns);
190 printQuery(res, &myopt, pset.queryFout, false, pset.logfile);
197 * Takes an optional regexp to select particular tablespaces
200 describeTablespaces(const char *pattern, bool verbose)
204 printQueryOpt myopt = pset.popt;
206 if (pset.sversion < 80000)
208 psql_error("The server (version %d.%d) does not support tablespaces.\n",
209 pset.sversion / 10000, (pset.sversion / 100) % 100);
213 initPQExpBuffer(&buf);
215 if (pset.sversion >= 90200)
216 printfPQExpBuffer(&buf,
217 "SELECT spcname AS \"%s\",\n"
218 " pg_catalog.pg_get_userbyid(spcowner) AS \"%s\",\n"
219 " pg_catalog.pg_tablespace_location(oid) AS \"%s\"",
220 gettext_noop("Name"),
221 gettext_noop("Owner"),
222 gettext_noop("Location"));
224 printfPQExpBuffer(&buf,
225 "SELECT spcname AS \"%s\",\n"
226 " pg_catalog.pg_get_userbyid(spcowner) AS \"%s\",\n"
227 " spclocation AS \"%s\"",
228 gettext_noop("Name"),
229 gettext_noop("Owner"),
230 gettext_noop("Location"));
234 appendPQExpBufferStr(&buf, ",\n ");
235 printACLColumn(&buf, "spcacl");
238 if (verbose && pset.sversion >= 90000)
239 appendPQExpBuffer(&buf,
240 ",\n spcoptions AS \"%s\"",
241 gettext_noop("Options"));
243 if (verbose && pset.sversion >= 90200)
244 appendPQExpBuffer(&buf,
245 ",\n pg_catalog.pg_size_pretty(pg_catalog.pg_tablespace_size(oid)) AS \"%s\"",
246 gettext_noop("Size"));
248 if (verbose && pset.sversion >= 80200)
249 appendPQExpBuffer(&buf,
250 ",\n pg_catalog.shobj_description(oid, 'pg_tablespace') AS \"%s\"",
251 gettext_noop("Description"));
253 appendPQExpBufferStr(&buf,
254 "\nFROM pg_catalog.pg_tablespace\n");
256 processSQLNamePattern(pset.db, &buf, pattern, false, false,
257 NULL, "spcname", NULL,
260 appendPQExpBufferStr(&buf, "ORDER BY 1;");
262 res = PSQLexec(buf.data);
263 termPQExpBuffer(&buf);
267 myopt.nullPrint = NULL;
268 myopt.title = _("List of tablespaces");
269 myopt.translate_header = true;
271 printQuery(res, &myopt, pset.queryFout, false, pset.logfile);
279 * Takes an optional regexp to select particular functions.
281 * As with \d, you can specify the kinds of functions you want:
288 * and you can mix and match these in any order.
291 describeFunctions(const char *functypes, const char *pattern, bool verbose, bool showSystem)
293 bool showAggregate = strchr(functypes, 'a') != NULL;
294 bool showNormal = strchr(functypes, 'n') != NULL;
295 bool showTrigger = strchr(functypes, 't') != NULL;
296 bool showWindow = strchr(functypes, 'w') != NULL;
300 printQueryOpt myopt = pset.popt;
301 static const bool translate_columns[] = {false, false, false, false, true, true, true, false, true, false, false, false, false};
303 /* No "Parallel" column before 9.6 */
304 static const bool translate_columns_pre_96[] = {false, false, false, false, true, true, false, true, false, false, false, false};
306 if (strlen(functypes) != strspn(functypes, "antwS+"))
308 psql_error("\\df only takes [antwS+] as options\n");
312 if (showWindow && pset.sversion < 80400)
314 psql_error("\\df does not take a \"w\" option with server version %d.%d\n",
315 pset.sversion / 10000, (pset.sversion / 100) % 100);
319 if (!showAggregate && !showNormal && !showTrigger && !showWindow)
321 showAggregate = showNormal = showTrigger = true;
322 if (pset.sversion >= 80400)
326 initPQExpBuffer(&buf);
328 printfPQExpBuffer(&buf,
329 "SELECT n.nspname as \"%s\",\n"
330 " p.proname as \"%s\",\n",
331 gettext_noop("Schema"),
332 gettext_noop("Name"));
334 if (pset.sversion >= 80400)
335 appendPQExpBuffer(&buf,
336 " pg_catalog.pg_get_function_result(p.oid) as \"%s\",\n"
337 " pg_catalog.pg_get_function_arguments(p.oid) as \"%s\",\n"
339 " WHEN p.proisagg THEN '%s'\n"
340 " WHEN p.proiswindow THEN '%s'\n"
341 " WHEN p.prorettype = 'pg_catalog.trigger'::pg_catalog.regtype THEN '%s'\n"
344 gettext_noop("Result data type"),
345 gettext_noop("Argument data types"),
346 /* translator: "agg" is short for "aggregate" */
348 gettext_noop("window"),
349 gettext_noop("trigger"),
350 gettext_noop("normal"),
351 gettext_noop("Type"));
352 else if (pset.sversion >= 80100)
353 appendPQExpBuffer(&buf,
354 " CASE WHEN p.proretset THEN 'SETOF ' ELSE '' END ||\n"
355 " pg_catalog.format_type(p.prorettype, NULL) as \"%s\",\n"
356 " CASE WHEN proallargtypes IS NOT NULL THEN\n"
357 " pg_catalog.array_to_string(ARRAY(\n"
360 " WHEN p.proargmodes[s.i] = 'i' THEN ''\n"
361 " WHEN p.proargmodes[s.i] = 'o' THEN 'OUT '\n"
362 " WHEN p.proargmodes[s.i] = 'b' THEN 'INOUT '\n"
363 " WHEN p.proargmodes[s.i] = 'v' THEN 'VARIADIC '\n"
366 " WHEN COALESCE(p.proargnames[s.i], '') = '' THEN ''\n"
367 " ELSE p.proargnames[s.i] || ' ' \n"
369 " pg_catalog.format_type(p.proallargtypes[s.i], NULL)\n"
371 " pg_catalog.generate_series(1, pg_catalog.array_upper(p.proallargtypes, 1)) AS s(i)\n"
374 " pg_catalog.array_to_string(ARRAY(\n"
377 " WHEN COALESCE(p.proargnames[s.i+1], '') = '' THEN ''\n"
378 " ELSE p.proargnames[s.i+1] || ' '\n"
380 " pg_catalog.format_type(p.proargtypes[s.i], NULL)\n"
382 " pg_catalog.generate_series(0, pg_catalog.array_upper(p.proargtypes, 1)) AS s(i)\n"
386 " WHEN p.proisagg THEN '%s'\n"
387 " WHEN p.prorettype = 'pg_catalog.trigger'::pg_catalog.regtype THEN '%s'\n"
390 gettext_noop("Result data type"),
391 gettext_noop("Argument data types"),
392 /* translator: "agg" is short for "aggregate" */
394 gettext_noop("trigger"),
395 gettext_noop("normal"),
396 gettext_noop("Type"));
398 appendPQExpBuffer(&buf,
399 " CASE WHEN p.proretset THEN 'SETOF ' ELSE '' END ||\n"
400 " pg_catalog.format_type(p.prorettype, NULL) as \"%s\",\n"
401 " pg_catalog.oidvectortypes(p.proargtypes) as \"%s\",\n"
403 " WHEN p.proisagg THEN '%s'\n"
404 " WHEN p.prorettype = 'pg_catalog.trigger'::pg_catalog.regtype THEN '%s'\n"
407 gettext_noop("Result data type"),
408 gettext_noop("Argument data types"),
409 /* translator: "agg" is short for "aggregate" */
411 gettext_noop("trigger"),
412 gettext_noop("normal"),
413 gettext_noop("Type"));
417 appendPQExpBuffer(&buf,
419 " WHEN p.provolatile = 'i' THEN '%s'\n"
420 " WHEN p.provolatile = 's' THEN '%s'\n"
421 " WHEN p.provolatile = 'v' THEN '%s'\n"
423 gettext_noop("immutable"),
424 gettext_noop("stable"),
425 gettext_noop("volatile"),
426 gettext_noop("Volatility"));
427 if (pset.sversion >= 90600)
428 appendPQExpBuffer(&buf,
430 " WHEN p.proparallel = 'r' THEN '%s'\n"
431 " WHEN p.proparallel = 's' THEN '%s'\n"
432 " WHEN p.proparallel = 'u' THEN '%s'\n"
434 gettext_noop("restricted"),
435 gettext_noop("safe"),
436 gettext_noop("unsafe"),
437 gettext_noop("Parallel"));
438 appendPQExpBuffer(&buf,
439 ",\n pg_catalog.pg_get_userbyid(p.proowner) as \"%s\""
440 ",\n CASE WHEN prosecdef THEN '%s' ELSE '%s' END AS \"%s\"",
441 gettext_noop("Owner"),
442 gettext_noop("definer"),
443 gettext_noop("invoker"),
444 gettext_noop("Security"));
445 appendPQExpBufferStr(&buf, ",\n ");
446 printACLColumn(&buf, "p.proacl");
447 appendPQExpBuffer(&buf,
448 ",\n l.lanname as \"%s\""
449 ",\n p.prosrc as \"%s\""
450 ",\n pg_catalog.obj_description(p.oid, 'pg_proc') as \"%s\"",
451 gettext_noop("Language"),
452 gettext_noop("Source code"),
453 gettext_noop("Description"));
456 appendPQExpBufferStr(&buf,
457 "\nFROM pg_catalog.pg_proc p"
458 "\n LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace\n");
461 appendPQExpBufferStr(&buf,
462 " LEFT JOIN pg_catalog.pg_language l ON l.oid = p.prolang\n");
466 /* filter by function type, if requested */
467 if (showNormal && showAggregate && showTrigger && showWindow)
474 appendPQExpBufferStr(&buf, " AND ");
477 appendPQExpBufferStr(&buf, "WHERE ");
480 appendPQExpBufferStr(&buf, "NOT p.proisagg\n");
485 appendPQExpBufferStr(&buf, " AND ");
488 appendPQExpBufferStr(&buf, "WHERE ");
491 appendPQExpBufferStr(&buf, "p.prorettype <> 'pg_catalog.trigger'::pg_catalog.regtype\n");
493 if (!showWindow && pset.sversion >= 80400)
496 appendPQExpBufferStr(&buf, " AND ");
499 appendPQExpBufferStr(&buf, "WHERE ");
502 appendPQExpBufferStr(&buf, "NOT p.proiswindow\n");
507 bool needs_or = false;
509 appendPQExpBufferStr(&buf, "WHERE (\n ");
511 /* Note: at least one of these must be true ... */
514 appendPQExpBufferStr(&buf, "p.proisagg\n");
520 appendPQExpBufferStr(&buf, " OR ");
521 appendPQExpBufferStr(&buf,
522 "p.prorettype = 'pg_catalog.trigger'::pg_catalog.regtype\n");
528 appendPQExpBufferStr(&buf, " OR ");
529 appendPQExpBufferStr(&buf, "p.proiswindow\n");
532 appendPQExpBufferStr(&buf, " )\n");
535 processSQLNamePattern(pset.db, &buf, pattern, have_where, false,
536 "n.nspname", "p.proname", NULL,
537 "pg_catalog.pg_function_is_visible(p.oid)");
539 if (!showSystem && !pattern)
540 appendPQExpBufferStr(&buf, " AND n.nspname <> 'pg_catalog'\n"
541 " AND n.nspname <> 'information_schema'\n");
543 appendPQExpBufferStr(&buf, "ORDER BY 1, 2, 4;");
545 res = PSQLexec(buf.data);
546 termPQExpBuffer(&buf);
550 myopt.nullPrint = NULL;
551 myopt.title = _("List of functions");
552 myopt.translate_header = true;
553 if (pset.sversion >= 90600)
555 myopt.translate_columns = translate_columns;
556 myopt.n_translate_columns = lengthof(translate_columns);
560 myopt.translate_columns = translate_columns_pre_96;
561 myopt.n_translate_columns = lengthof(translate_columns_pre_96);
564 printQuery(res, &myopt, pset.queryFout, false, pset.logfile);
577 describeTypes(const char *pattern, bool verbose, bool showSystem)
581 printQueryOpt myopt = pset.popt;
583 initPQExpBuffer(&buf);
585 printfPQExpBuffer(&buf,
586 "SELECT n.nspname as \"%s\",\n"
587 " pg_catalog.format_type(t.oid, NULL) AS \"%s\",\n",
588 gettext_noop("Schema"),
589 gettext_noop("Name"));
591 appendPQExpBuffer(&buf,
592 " t.typname AS \"%s\",\n"
593 " CASE WHEN t.typrelid != 0\n"
594 " THEN CAST('tuple' AS pg_catalog.text)\n"
595 " WHEN t.typlen < 0\n"
596 " THEN CAST('var' AS pg_catalog.text)\n"
597 " ELSE CAST(t.typlen AS pg_catalog.text)\n"
599 gettext_noop("Internal name"),
600 gettext_noop("Size"));
601 if (verbose && pset.sversion >= 80300)
603 appendPQExpBufferStr(&buf,
604 " pg_catalog.array_to_string(\n"
606 " SELECT e.enumlabel\n"
607 " FROM pg_catalog.pg_enum e\n"
608 " WHERE e.enumtypid = t.oid\n");
610 if (pset.sversion >= 90100)
611 appendPQExpBufferStr(&buf,
612 " ORDER BY e.enumsortorder\n");
614 appendPQExpBufferStr(&buf,
615 " ORDER BY e.oid\n");
617 appendPQExpBuffer(&buf,
621 gettext_noop("Elements"));
625 appendPQExpBuffer(&buf,
626 " pg_catalog.pg_get_userbyid(t.typowner) AS \"%s\",\n",
627 gettext_noop("Owner"));
629 if (verbose && pset.sversion >= 90200)
631 printACLColumn(&buf, "t.typacl");
632 appendPQExpBufferStr(&buf, ",\n ");
635 appendPQExpBuffer(&buf,
636 " pg_catalog.obj_description(t.oid, 'pg_type') as \"%s\"\n",
637 gettext_noop("Description"));
639 appendPQExpBufferStr(&buf, "FROM pg_catalog.pg_type t\n"
640 " LEFT JOIN pg_catalog.pg_namespace n ON n.oid = t.typnamespace\n");
643 * do not include complex types (typrelid!=0) unless they are standalone
646 appendPQExpBufferStr(&buf, "WHERE (t.typrelid = 0 ");
647 appendPQExpBufferStr(&buf, "OR (SELECT c.relkind = 'c' FROM pg_catalog.pg_class c "
648 "WHERE c.oid = t.typrelid))\n");
651 * do not include array types (before 8.3 we have to use the assumption
652 * that their names start with underscore)
654 if (pset.sversion >= 80300)
655 appendPQExpBufferStr(&buf, " AND NOT EXISTS(SELECT 1 FROM pg_catalog.pg_type el WHERE el.oid = t.typelem AND el.typarray = t.oid)\n");
657 appendPQExpBufferStr(&buf, " AND t.typname !~ '^_'\n");
659 if (!showSystem && !pattern)
660 appendPQExpBufferStr(&buf, " AND n.nspname <> 'pg_catalog'\n"
661 " AND n.nspname <> 'information_schema'\n");
663 /* Match name pattern against either internal or external name */
664 processSQLNamePattern(pset.db, &buf, pattern, true, false,
665 "n.nspname", "t.typname",
666 "pg_catalog.format_type(t.oid, NULL)",
667 "pg_catalog.pg_type_is_visible(t.oid)");
669 appendPQExpBufferStr(&buf, "ORDER BY 1, 2;");
671 res = PSQLexec(buf.data);
672 termPQExpBuffer(&buf);
676 myopt.nullPrint = NULL;
677 myopt.title = _("List of data types");
678 myopt.translate_header = true;
680 printQuery(res, &myopt, pset.queryFout, false, pset.logfile);
691 describeOperators(const char *pattern, bool verbose, bool showSystem)
695 printQueryOpt myopt = pset.popt;
697 initPQExpBuffer(&buf);
700 * Note: before Postgres 9.1, we did not assign comments to any built-in
701 * operators, preferring to let the comment on the underlying function
702 * suffice. The coalesce() on the obj_description() calls below supports
703 * this convention by providing a fallback lookup of a comment on the
704 * operator's function. As of 9.1 there is a policy that every built-in
705 * operator should have a comment; so the coalesce() is no longer
706 * necessary so far as built-in operators are concerned. We keep it
707 * anyway, for now, because (1) third-party modules may still be following
708 * the old convention, and (2) we'd need to do it anyway when talking to a
712 printfPQExpBuffer(&buf,
713 "SELECT n.nspname as \"%s\",\n"
714 " o.oprname AS \"%s\",\n"
715 " CASE WHEN o.oprkind='l' THEN NULL ELSE pg_catalog.format_type(o.oprleft, NULL) END AS \"%s\",\n"
716 " CASE WHEN o.oprkind='r' THEN NULL ELSE pg_catalog.format_type(o.oprright, NULL) END AS \"%s\",\n"
717 " pg_catalog.format_type(o.oprresult, NULL) AS \"%s\",\n",
718 gettext_noop("Schema"),
719 gettext_noop("Name"),
720 gettext_noop("Left arg type"),
721 gettext_noop("Right arg type"),
722 gettext_noop("Result type"));
725 appendPQExpBuffer(&buf,
726 " o.oprcode AS \"%s\",\n",
727 gettext_noop("Function"));
729 appendPQExpBuffer(&buf,
730 " coalesce(pg_catalog.obj_description(o.oid, 'pg_operator'),\n"
731 " pg_catalog.obj_description(o.oprcode, 'pg_proc')) AS \"%s\"\n"
732 "FROM pg_catalog.pg_operator o\n"
733 " LEFT JOIN pg_catalog.pg_namespace n ON n.oid = o.oprnamespace\n",
734 gettext_noop("Description"));
736 if (!showSystem && !pattern)
737 appendPQExpBufferStr(&buf, "WHERE n.nspname <> 'pg_catalog'\n"
738 " AND n.nspname <> 'information_schema'\n");
740 processSQLNamePattern(pset.db, &buf, pattern, !showSystem && !pattern, true,
741 "n.nspname", "o.oprname", NULL,
742 "pg_catalog.pg_operator_is_visible(o.oid)");
744 appendPQExpBufferStr(&buf, "ORDER BY 1, 2, 3, 4;");
746 res = PSQLexec(buf.data);
747 termPQExpBuffer(&buf);
751 myopt.nullPrint = NULL;
752 myopt.title = _("List of operators");
753 myopt.translate_header = true;
755 printQuery(res, &myopt, pset.queryFout, false, pset.logfile);
765 * for \l, \list, and -l switch
768 listAllDbs(const char *pattern, bool verbose)
772 printQueryOpt myopt = pset.popt;
774 initPQExpBuffer(&buf);
776 printfPQExpBuffer(&buf,
777 "SELECT d.datname as \"%s\",\n"
778 " pg_catalog.pg_get_userbyid(d.datdba) as \"%s\",\n"
779 " pg_catalog.pg_encoding_to_char(d.encoding) as \"%s\",\n",
780 gettext_noop("Name"),
781 gettext_noop("Owner"),
782 gettext_noop("Encoding"));
783 if (pset.sversion >= 80400)
784 appendPQExpBuffer(&buf,
785 " d.datcollate as \"%s\",\n"
786 " d.datctype as \"%s\",\n",
787 gettext_noop("Collate"),
788 gettext_noop("Ctype"));
789 appendPQExpBufferStr(&buf, " ");
790 printACLColumn(&buf, "d.datacl");
791 if (verbose && pset.sversion >= 80200)
792 appendPQExpBuffer(&buf,
793 ",\n CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT')\n"
794 " THEN pg_catalog.pg_size_pretty(pg_catalog.pg_database_size(d.datname))\n"
795 " ELSE 'No Access'\n"
797 gettext_noop("Size"));
798 if (verbose && pset.sversion >= 80000)
799 appendPQExpBuffer(&buf,
800 ",\n t.spcname as \"%s\"",
801 gettext_noop("Tablespace"));
802 if (verbose && pset.sversion >= 80200)
803 appendPQExpBuffer(&buf,
804 ",\n pg_catalog.shobj_description(d.oid, 'pg_database') as \"%s\"",
805 gettext_noop("Description"));
806 appendPQExpBufferStr(&buf,
807 "\nFROM pg_catalog.pg_database d\n");
808 if (verbose && pset.sversion >= 80000)
809 appendPQExpBufferStr(&buf,
810 " JOIN pg_catalog.pg_tablespace t on d.dattablespace = t.oid\n");
813 processSQLNamePattern(pset.db, &buf, pattern, false, false,
814 NULL, "d.datname", NULL, NULL);
816 appendPQExpBufferStr(&buf, "ORDER BY 1;");
817 res = PSQLexec(buf.data);
818 termPQExpBuffer(&buf);
822 myopt.nullPrint = NULL;
823 myopt.title = _("List of databases");
824 myopt.translate_header = true;
826 printQuery(res, &myopt, pset.queryFout, false, pset.logfile);
834 * List Tables' Grant/Revoke Permissions
835 * \z (now also \dp -- perhaps more mnemonic)
838 permissionsList(const char *pattern)
842 printQueryOpt myopt = pset.popt;
843 static const bool translate_columns[] = {false, false, true, false, false, false};
845 initPQExpBuffer(&buf);
848 * we ignore indexes and toast tables since they have no meaningful rights
850 printfPQExpBuffer(&buf,
851 "SELECT n.nspname as \"%s\",\n"
852 " c.relname as \"%s\",\n"
854 " WHEN 'r' THEN '%s'"
855 " WHEN 'v' THEN '%s'"
856 " WHEN 'm' THEN '%s'"
857 " WHEN 'S' THEN '%s'"
858 " WHEN 'f' THEN '%s'"
861 gettext_noop("Schema"),
862 gettext_noop("Name"),
863 gettext_noop("table"),
864 gettext_noop("view"),
865 gettext_noop("materialized view"),
866 gettext_noop("sequence"),
867 gettext_noop("foreign table"),
868 gettext_noop("Type"));
870 printACLColumn(&buf, "c.relacl");
872 if (pset.sversion >= 80400)
873 appendPQExpBuffer(&buf,
874 ",\n pg_catalog.array_to_string(ARRAY(\n"
875 " SELECT attname || E':\\n ' || pg_catalog.array_to_string(attacl, E'\\n ')\n"
876 " FROM pg_catalog.pg_attribute a\n"
877 " WHERE attrelid = c.oid AND NOT attisdropped AND attacl IS NOT NULL\n"
878 " ), E'\\n') AS \"%s\"",
879 gettext_noop("Column privileges"));
881 if (pset.sversion >= 90500)
882 appendPQExpBuffer(&buf,
883 ",\n pg_catalog.array_to_string(ARRAY(\n"
885 " || CASE WHEN polcmd != '*' THEN\n"
886 " E' (' || polcmd || E'):'\n"
889 " || CASE WHEN polqual IS NOT NULL THEN\n"
890 " E'\\n (u): ' || pg_catalog.pg_get_expr(polqual, polrelid)\n"
893 " || CASE WHEN polwithcheck IS NOT NULL THEN\n"
894 " E'\\n (c): ' || pg_catalog.pg_get_expr(polwithcheck, polrelid)\n"
897 " || CASE WHEN polroles <> '{0}' THEN\n"
898 " E'\\n to: ' || pg_catalog.array_to_string(\n"
901 " FROM pg_catalog.pg_roles\n"
902 " WHERE oid = ANY (polroles)\n"
907 " FROM pg_catalog.pg_policy pol\n"
908 " WHERE polrelid = c.oid), E'\\n')\n"
910 gettext_noop("Policies"));
912 appendPQExpBufferStr(&buf, "\nFROM pg_catalog.pg_class c\n"
913 " LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace\n"
914 "WHERE c.relkind IN ('r', 'v', 'm', 'S', 'f')\n");
917 * Unless a schema pattern is specified, we suppress system and temp
918 * tables, since they normally aren't very interesting from a permissions
919 * point of view. You can see 'em by explicit request though, eg with \z
922 processSQLNamePattern(pset.db, &buf, pattern, true, false,
923 "n.nspname", "c.relname", NULL,
924 "n.nspname !~ '^pg_' AND pg_catalog.pg_table_is_visible(c.oid)");
926 appendPQExpBufferStr(&buf, "ORDER BY 1, 2;");
928 res = PSQLexec(buf.data);
931 termPQExpBuffer(&buf);
935 myopt.nullPrint = NULL;
936 printfPQExpBuffer(&buf, _("Access privileges"));
937 myopt.title = buf.data;
938 myopt.translate_header = true;
939 myopt.translate_columns = translate_columns;
940 myopt.n_translate_columns = lengthof(translate_columns);
942 printQuery(res, &myopt, pset.queryFout, false, pset.logfile);
944 termPQExpBuffer(&buf);
953 * List Default ACLs. The pattern can match either schema or role name.
956 listDefaultACLs(const char *pattern)
960 printQueryOpt myopt = pset.popt;
961 static const bool translate_columns[] = {false, false, true, false};
963 if (pset.sversion < 90000)
965 psql_error("The server (version %d.%d) does not support altering default privileges.\n",
966 pset.sversion / 10000, (pset.sversion / 100) % 100);
970 initPQExpBuffer(&buf);
972 printfPQExpBuffer(&buf,
973 "SELECT pg_catalog.pg_get_userbyid(d.defaclrole) AS \"%s\",\n"
974 " n.nspname AS \"%s\",\n"
975 " CASE d.defaclobjtype WHEN '%c' THEN '%s' WHEN '%c' THEN '%s' WHEN '%c' THEN '%s' WHEN '%c' THEN '%s' END AS \"%s\",\n"
977 gettext_noop("Owner"),
978 gettext_noop("Schema"),
980 gettext_noop("table"),
982 gettext_noop("sequence"),
984 gettext_noop("function"),
986 gettext_noop("type"),
987 gettext_noop("Type"));
989 printACLColumn(&buf, "d.defaclacl");
991 appendPQExpBufferStr(&buf, "\nFROM pg_catalog.pg_default_acl d\n"
992 " LEFT JOIN pg_catalog.pg_namespace n ON n.oid = d.defaclnamespace\n");
994 processSQLNamePattern(pset.db, &buf, pattern, false, false,
997 "pg_catalog.pg_get_userbyid(d.defaclrole)",
1000 appendPQExpBufferStr(&buf, "ORDER BY 1, 2, 3;");
1002 res = PSQLexec(buf.data);
1005 termPQExpBuffer(&buf);
1009 myopt.nullPrint = NULL;
1010 printfPQExpBuffer(&buf, _("Default access privileges"));
1011 myopt.title = buf.data;
1012 myopt.translate_header = true;
1013 myopt.translate_columns = translate_columns;
1014 myopt.n_translate_columns = lengthof(translate_columns);
1016 printQuery(res, &myopt, pset.queryFout, false, pset.logfile);
1018 termPQExpBuffer(&buf);
1025 * Get object comments
1029 * Note: This command only lists comments for object types which do not have
1030 * their comments displayed by their own backslash commands. The following
1031 * types of objects will be displayed: constraint, operator class,
1032 * operator family, rule, and trigger.
1036 objectDescription(const char *pattern, bool showSystem)
1038 PQExpBufferData buf;
1040 printQueryOpt myopt = pset.popt;
1041 static const bool translate_columns[] = {false, false, true, false};
1043 initPQExpBuffer(&buf);
1045 appendPQExpBuffer(&buf,
1046 "SELECT DISTINCT tt.nspname AS \"%s\", tt.name AS \"%s\", tt.object AS \"%s\", d.description AS \"%s\"\n"
1048 gettext_noop("Schema"),
1049 gettext_noop("Name"),
1050 gettext_noop("Object"),
1051 gettext_noop("Description"));
1053 /* Table constraint descriptions */
1054 appendPQExpBuffer(&buf,
1055 " SELECT pgc.oid as oid, pgc.tableoid AS tableoid,\n"
1056 " n.nspname as nspname,\n"
1057 " CAST(pgc.conname AS pg_catalog.text) as name,"
1058 " CAST('%s' AS pg_catalog.text) as object\n"
1059 " FROM pg_catalog.pg_constraint pgc\n"
1060 " JOIN pg_catalog.pg_class c "
1061 "ON c.oid = pgc.conrelid\n"
1062 " LEFT JOIN pg_catalog.pg_namespace n "
1063 " ON n.oid = c.relnamespace\n",
1064 gettext_noop("table constraint"));
1066 if (!showSystem && !pattern)
1067 appendPQExpBufferStr(&buf, "WHERE n.nspname <> 'pg_catalog'\n"
1068 " AND n.nspname <> 'information_schema'\n");
1070 processSQLNamePattern(pset.db, &buf, pattern, !showSystem && !pattern,
1071 false, "n.nspname", "pgc.conname", NULL,
1072 "pg_catalog.pg_table_is_visible(c.oid)");
1074 /* Domain constraint descriptions */
1075 appendPQExpBuffer(&buf,
1077 " SELECT pgc.oid as oid, pgc.tableoid AS tableoid,\n"
1078 " n.nspname as nspname,\n"
1079 " CAST(pgc.conname AS pg_catalog.text) as name,"
1080 " CAST('%s' AS pg_catalog.text) as object\n"
1081 " FROM pg_catalog.pg_constraint pgc\n"
1082 " JOIN pg_catalog.pg_type t "
1083 "ON t.oid = pgc.contypid\n"
1084 " LEFT JOIN pg_catalog.pg_namespace n "
1085 " ON n.oid = t.typnamespace\n",
1086 gettext_noop("domain constraint"));
1088 if (!showSystem && !pattern)
1089 appendPQExpBufferStr(&buf, "WHERE n.nspname <> 'pg_catalog'\n"
1090 " AND n.nspname <> 'information_schema'\n");
1092 processSQLNamePattern(pset.db, &buf, pattern, !showSystem && !pattern,
1093 false, "n.nspname", "pgc.conname", NULL,
1094 "pg_catalog.pg_type_is_visible(t.oid)");
1098 * pg_opclass.opcmethod only available in 8.3+
1100 if (pset.sversion >= 80300)
1102 /* Operator class descriptions */
1103 appendPQExpBuffer(&buf,
1105 " SELECT o.oid as oid, o.tableoid as tableoid,\n"
1106 " n.nspname as nspname,\n"
1107 " CAST(o.opcname AS pg_catalog.text) as name,\n"
1108 " CAST('%s' AS pg_catalog.text) as object\n"
1109 " FROM pg_catalog.pg_opclass o\n"
1110 " JOIN pg_catalog.pg_am am ON "
1111 "o.opcmethod = am.oid\n"
1112 " JOIN pg_catalog.pg_namespace n ON "
1113 "n.oid = o.opcnamespace\n",
1114 gettext_noop("operator class"));
1116 if (!showSystem && !pattern)
1117 appendPQExpBufferStr(&buf, " AND n.nspname <> 'pg_catalog'\n"
1118 " AND n.nspname <> 'information_schema'\n");
1120 processSQLNamePattern(pset.db, &buf, pattern, true, false,
1121 "n.nspname", "o.opcname", NULL,
1122 "pg_catalog.pg_opclass_is_visible(o.oid)");
1126 * although operator family comments have been around since 8.3,
1127 * pg_opfamily_is_visible is only available in 9.2+
1129 if (pset.sversion >= 90200)
1131 /* Operator family descriptions */
1132 appendPQExpBuffer(&buf,
1134 " SELECT opf.oid as oid, opf.tableoid as tableoid,\n"
1135 " n.nspname as nspname,\n"
1136 " CAST(opf.opfname AS pg_catalog.text) AS name,\n"
1137 " CAST('%s' AS pg_catalog.text) as object\n"
1138 " FROM pg_catalog.pg_opfamily opf\n"
1139 " JOIN pg_catalog.pg_am am "
1140 "ON opf.opfmethod = am.oid\n"
1141 " JOIN pg_catalog.pg_namespace n "
1142 "ON opf.opfnamespace = n.oid\n",
1143 gettext_noop("operator family"));
1145 if (!showSystem && !pattern)
1146 appendPQExpBufferStr(&buf, " AND n.nspname <> 'pg_catalog'\n"
1147 " AND n.nspname <> 'information_schema'\n");
1149 processSQLNamePattern(pset.db, &buf, pattern, true, false,
1150 "n.nspname", "opf.opfname", NULL,
1151 "pg_catalog.pg_opfamily_is_visible(opf.oid)");
1154 /* Rule descriptions (ignore rules for views) */
1155 appendPQExpBuffer(&buf,
1157 " SELECT r.oid as oid, r.tableoid as tableoid,\n"
1158 " n.nspname as nspname,\n"
1159 " CAST(r.rulename AS pg_catalog.text) as name,"
1160 " CAST('%s' AS pg_catalog.text) as object\n"
1161 " FROM pg_catalog.pg_rewrite r\n"
1162 " JOIN pg_catalog.pg_class c ON c.oid = r.ev_class\n"
1163 " LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace\n"
1164 " WHERE r.rulename != '_RETURN'\n",
1165 gettext_noop("rule"));
1167 if (!showSystem && !pattern)
1168 appendPQExpBufferStr(&buf, " AND n.nspname <> 'pg_catalog'\n"
1169 " AND n.nspname <> 'information_schema'\n");
1171 processSQLNamePattern(pset.db, &buf, pattern, true, false,
1172 "n.nspname", "r.rulename", NULL,
1173 "pg_catalog.pg_table_is_visible(c.oid)");
1175 /* Trigger descriptions */
1176 appendPQExpBuffer(&buf,
1178 " SELECT t.oid as oid, t.tableoid as tableoid,\n"
1179 " n.nspname as nspname,\n"
1180 " CAST(t.tgname AS pg_catalog.text) as name,"
1181 " CAST('%s' AS pg_catalog.text) as object\n"
1182 " FROM pg_catalog.pg_trigger t\n"
1183 " JOIN pg_catalog.pg_class c ON c.oid = t.tgrelid\n"
1184 " LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace\n",
1185 gettext_noop("trigger"));
1187 if (!showSystem && !pattern)
1188 appendPQExpBufferStr(&buf, "WHERE n.nspname <> 'pg_catalog'\n"
1189 " AND n.nspname <> 'information_schema'\n");
1191 processSQLNamePattern(pset.db, &buf, pattern, !showSystem && !pattern, false,
1192 "n.nspname", "t.tgname", NULL,
1193 "pg_catalog.pg_table_is_visible(c.oid)");
1195 appendPQExpBufferStr(&buf,
1197 " JOIN pg_catalog.pg_description d ON (tt.oid = d.objoid AND tt.tableoid = d.classoid AND d.objsubid = 0)\n");
1199 appendPQExpBufferStr(&buf, "ORDER BY 1, 2, 3;");
1201 res = PSQLexec(buf.data);
1202 termPQExpBuffer(&buf);
1206 myopt.nullPrint = NULL;
1207 myopt.title = _("Object descriptions");
1208 myopt.translate_header = true;
1209 myopt.translate_columns = translate_columns;
1210 myopt.n_translate_columns = lengthof(translate_columns);
1212 printQuery(res, &myopt, pset.queryFout, false, pset.logfile);
1220 * describeTableDetails (for \d)
1222 * This routine finds the tables to be displayed, and calls
1223 * describeOneTableDetails for each one.
1225 * verbose: if true, this is \d+
1228 describeTableDetails(const char *pattern, bool verbose, bool showSystem)
1230 PQExpBufferData buf;
1234 initPQExpBuffer(&buf);
1236 printfPQExpBuffer(&buf,
1240 "FROM pg_catalog.pg_class c\n"
1241 " LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace\n");
1243 if (!showSystem && !pattern)
1244 appendPQExpBufferStr(&buf, "WHERE n.nspname <> 'pg_catalog'\n"
1245 " AND n.nspname <> 'information_schema'\n");
1247 processSQLNamePattern(pset.db, &buf, pattern, !showSystem && !pattern, false,
1248 "n.nspname", "c.relname", NULL,
1249 "pg_catalog.pg_table_is_visible(c.oid)");
1251 appendPQExpBufferStr(&buf, "ORDER BY 2, 3;");
1253 res = PSQLexec(buf.data);
1254 termPQExpBuffer(&buf);
1258 if (PQntuples(res) == 0)
1261 psql_error("Did not find any relation named \"%s\".\n",
1267 for (i = 0; i < PQntuples(res); i++)
1270 const char *nspname;
1271 const char *relname;
1273 oid = PQgetvalue(res, i, 0);
1274 nspname = PQgetvalue(res, i, 1);
1275 relname = PQgetvalue(res, i, 2);
1277 if (!describeOneTableDetails(nspname, relname, oid, verbose))
1294 * describeOneTableDetails (for \d)
1296 * Unfortunately, the information presented here is so complicated that it
1297 * cannot be done in a single query. So we have to assemble the printed table
1298 * by hand and pass it to the underlying printTable() function.
1301 describeOneTableDetails(const char *schemaname,
1302 const char *relationname,
1306 PQExpBufferData buf;
1307 PGresult *res = NULL;
1308 printTableOpt myopt = pset.popt.topt;
1309 printTableContent cont;
1310 bool printTableInitialized = false;
1312 char *view_def = NULL;
1314 char **seq_values = NULL;
1315 char **modifiers = NULL;
1317 PQExpBufferData title;
1318 PQExpBufferData tmpbuf;
1329 bool forcerowsecurity;
1334 char relpersistence;
1337 bool show_modifiers = false;
1342 myopt.default_footer = false;
1343 /* This output looks confusing in expanded mode. */
1344 myopt.expanded = false;
1346 initPQExpBuffer(&buf);
1347 initPQExpBuffer(&title);
1348 initPQExpBuffer(&tmpbuf);
1350 /* Get general table info */
1351 if (pset.sversion >= 90500)
1353 printfPQExpBuffer(&buf,
1354 "SELECT c.relchecks, c.relkind, c.relhasindex, c.relhasrules, "
1355 "c.relhastriggers, c.relrowsecurity, c.relforcerowsecurity, "
1356 "c.relhasoids, %s, c.reltablespace, "
1357 "CASE WHEN c.reloftype = 0 THEN '' ELSE c.reloftype::pg_catalog.regtype::pg_catalog.text END, "
1358 "c.relpersistence, c.relreplident\n"
1359 "FROM pg_catalog.pg_class c\n "
1360 "LEFT JOIN pg_catalog.pg_class tc ON (c.reltoastrelid = tc.oid)\n"
1361 "WHERE c.oid = '%s';",
1363 "pg_catalog.array_to_string(c.reloptions || "
1364 "array(select 'toast.' || x from pg_catalog.unnest(tc.reloptions) x), ', ')\n"
1368 else if (pset.sversion >= 90400)
1370 printfPQExpBuffer(&buf,
1371 "SELECT c.relchecks, c.relkind, c.relhasindex, c.relhasrules, "
1372 "c.relhastriggers, false, false, c.relhasoids, "
1373 "%s, c.reltablespace, "
1374 "CASE WHEN c.reloftype = 0 THEN '' ELSE c.reloftype::pg_catalog.regtype::pg_catalog.text END, "
1375 "c.relpersistence, c.relreplident\n"
1376 "FROM pg_catalog.pg_class c\n "
1377 "LEFT JOIN pg_catalog.pg_class tc ON (c.reltoastrelid = tc.oid)\n"
1378 "WHERE c.oid = '%s';",
1380 "pg_catalog.array_to_string(c.reloptions || "
1381 "array(select 'toast.' || x from pg_catalog.unnest(tc.reloptions) x), ', ')\n"
1385 else if (pset.sversion >= 90100)
1387 printfPQExpBuffer(&buf,
1388 "SELECT c.relchecks, c.relkind, c.relhasindex, c.relhasrules, "
1389 "c.relhastriggers, false, false, c.relhasoids, "
1390 "%s, c.reltablespace, "
1391 "CASE WHEN c.reloftype = 0 THEN '' ELSE c.reloftype::pg_catalog.regtype::pg_catalog.text END, "
1392 "c.relpersistence\n"
1393 "FROM pg_catalog.pg_class c\n "
1394 "LEFT JOIN pg_catalog.pg_class tc ON (c.reltoastrelid = tc.oid)\n"
1395 "WHERE c.oid = '%s';",
1397 "pg_catalog.array_to_string(c.reloptions || "
1398 "array(select 'toast.' || x from pg_catalog.unnest(tc.reloptions) x), ', ')\n"
1402 else if (pset.sversion >= 90000)
1404 printfPQExpBuffer(&buf,
1405 "SELECT c.relchecks, c.relkind, c.relhasindex, c.relhasrules, "
1406 "c.relhastriggers, false, false, c.relhasoids, "
1407 "%s, c.reltablespace, "
1408 "CASE WHEN c.reloftype = 0 THEN '' ELSE c.reloftype::pg_catalog.regtype::pg_catalog.text END\n"
1409 "FROM pg_catalog.pg_class c\n "
1410 "LEFT JOIN pg_catalog.pg_class tc ON (c.reltoastrelid = tc.oid)\n"
1411 "WHERE c.oid = '%s';",
1413 "pg_catalog.array_to_string(c.reloptions || "
1414 "array(select 'toast.' || x from pg_catalog.unnest(tc.reloptions) x), ', ')\n"
1418 else if (pset.sversion >= 80400)
1420 printfPQExpBuffer(&buf,
1421 "SELECT c.relchecks, c.relkind, c.relhasindex, c.relhasrules, "
1422 "c.relhastriggers, false, false, c.relhasoids, "
1423 "%s, c.reltablespace\n"
1424 "FROM pg_catalog.pg_class c\n "
1425 "LEFT JOIN pg_catalog.pg_class tc ON (c.reltoastrelid = tc.oid)\n"
1426 "WHERE c.oid = '%s';",
1428 "pg_catalog.array_to_string(c.reloptions || "
1429 "array(select 'toast.' || x from pg_catalog.unnest(tc.reloptions) x), ', ')\n"
1433 else if (pset.sversion >= 80200)
1435 printfPQExpBuffer(&buf,
1436 "SELECT relchecks, relkind, relhasindex, relhasrules, "
1437 "reltriggers <> 0, false, false, relhasoids, "
1438 "%s, reltablespace\n"
1439 "FROM pg_catalog.pg_class WHERE oid = '%s';",
1441 "pg_catalog.array_to_string(reloptions, E', ')" : "''"),
1444 else if (pset.sversion >= 80000)
1446 printfPQExpBuffer(&buf,
1447 "SELECT relchecks, relkind, relhasindex, relhasrules, "
1448 "reltriggers <> 0, false, false, relhasoids, "
1449 "'', reltablespace\n"
1450 "FROM pg_catalog.pg_class WHERE oid = '%s';",
1455 printfPQExpBuffer(&buf,
1456 "SELECT relchecks, relkind, relhasindex, relhasrules, "
1457 "reltriggers <> 0, false, false, relhasoids, "
1459 "FROM pg_catalog.pg_class WHERE oid = '%s';",
1463 res = PSQLexec(buf.data);
1467 /* Did we get anything? */
1468 if (PQntuples(res) == 0)
1471 psql_error("Did not find any relation with OID %s.\n", oid);
1475 tableinfo.checks = atoi(PQgetvalue(res, 0, 0));
1476 tableinfo.relkind = *(PQgetvalue(res, 0, 1));
1477 tableinfo.hasindex = strcmp(PQgetvalue(res, 0, 2), "t") == 0;
1478 tableinfo.hasrules = strcmp(PQgetvalue(res, 0, 3), "t") == 0;
1479 tableinfo.hastriggers = strcmp(PQgetvalue(res, 0, 4), "t") == 0;
1480 tableinfo.rowsecurity = strcmp(PQgetvalue(res, 0, 5), "t") == 0;
1481 tableinfo.forcerowsecurity = strcmp(PQgetvalue(res, 0, 6), "t") == 0;
1482 tableinfo.hasoids = strcmp(PQgetvalue(res, 0, 7), "t") == 0;
1483 tableinfo.reloptions = (pset.sversion >= 80200) ?
1484 pg_strdup(PQgetvalue(res, 0, 8)) : NULL;
1485 tableinfo.tablespace = (pset.sversion >= 80000) ?
1486 atooid(PQgetvalue(res, 0, 9)) : 0;
1487 tableinfo.reloftype = (pset.sversion >= 90000 &&
1488 strcmp(PQgetvalue(res, 0, 10), "") != 0) ?
1489 pg_strdup(PQgetvalue(res, 0, 10)) : NULL;
1490 tableinfo.relpersistence = (pset.sversion >= 90100) ?
1491 *(PQgetvalue(res, 0, 11)) : 0;
1492 tableinfo.relreplident = (pset.sversion >= 90400) ?
1493 *(PQgetvalue(res, 0, 12)) : 'd';
1498 * If it's a sequence, fetch its values and store into an array that will
1501 if (tableinfo.relkind == 'S')
1503 printfPQExpBuffer(&buf, "SELECT * FROM %s", fmtId(schemaname));
1504 /* must be separate because fmtId isn't reentrant */
1505 appendPQExpBuffer(&buf, ".%s;", fmtId(relationname));
1507 res = PSQLexec(buf.data);
1511 seq_values = pg_malloc((PQnfields(res) + 1) * sizeof(*seq_values));
1513 for (i = 0; i < PQnfields(res); i++)
1514 seq_values[i] = pg_strdup(PQgetvalue(res, 0, i));
1515 seq_values[i] = NULL;
1524 * You need to modify value of "firstvcol" which will be defined below if
1525 * you are adding column(s) preceding to verbose-only columns.
1527 printfPQExpBuffer(&buf, "SELECT a.attname,");
1528 appendPQExpBufferStr(&buf, "\n pg_catalog.format_type(a.atttypid, a.atttypmod),"
1529 "\n (SELECT substring(pg_catalog.pg_get_expr(d.adbin, d.adrelid) for 128)"
1530 "\n FROM pg_catalog.pg_attrdef d"
1531 "\n WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef),"
1532 "\n a.attnotnull, a.attnum,");
1533 if (pset.sversion >= 90100)
1534 appendPQExpBufferStr(&buf, "\n (SELECT c.collname FROM pg_catalog.pg_collation c, pg_catalog.pg_type t\n"
1535 " WHERE c.oid = a.attcollation AND t.oid = a.atttypid AND a.attcollation <> t.typcollation) AS attcollation");
1537 appendPQExpBufferStr(&buf, "\n NULL AS attcollation");
1538 if (tableinfo.relkind == 'i')
1539 appendPQExpBufferStr(&buf, ",\n pg_catalog.pg_get_indexdef(a.attrelid, a.attnum, TRUE) AS indexdef");
1541 appendPQExpBufferStr(&buf, ",\n NULL AS indexdef");
1542 if (tableinfo.relkind == 'f' && pset.sversion >= 90200)
1543 appendPQExpBufferStr(&buf, ",\n CASE WHEN attfdwoptions IS NULL THEN '' ELSE "
1544 " '(' || array_to_string(ARRAY(SELECT quote_ident(option_name) || ' ' || quote_literal(option_value) FROM "
1545 " pg_options_to_table(attfdwoptions)), ', ') || ')' END AS attfdwoptions");
1547 appendPQExpBufferStr(&buf, ",\n NULL AS attfdwoptions");
1550 appendPQExpBufferStr(&buf, ",\n a.attstorage");
1551 appendPQExpBufferStr(&buf, ",\n CASE WHEN a.attstattarget=-1 THEN NULL ELSE a.attstattarget END AS attstattarget");
1554 * In 9.0+, we have column comments for: relations, views, composite
1555 * types, and foreign tables (c.f. CommentObject() in comment.c).
1557 if (tableinfo.relkind == 'r' || tableinfo.relkind == 'v' ||
1558 tableinfo.relkind == 'm' ||
1559 tableinfo.relkind == 'f' || tableinfo.relkind == 'c')
1560 appendPQExpBufferStr(&buf, ", pg_catalog.col_description(a.attrelid, a.attnum)");
1563 appendPQExpBufferStr(&buf, "\nFROM pg_catalog.pg_attribute a");
1564 appendPQExpBuffer(&buf, "\nWHERE a.attrelid = '%s' AND a.attnum > 0 AND NOT a.attisdropped", oid);
1565 appendPQExpBufferStr(&buf, "\nORDER BY a.attnum;");
1567 res = PSQLexec(buf.data);
1570 numrows = PQntuples(res);
1573 switch (tableinfo.relkind)
1576 if (tableinfo.relpersistence == 'u')
1577 printfPQExpBuffer(&title, _("Unlogged table \"%s.%s\""),
1578 schemaname, relationname);
1580 printfPQExpBuffer(&title, _("Table \"%s.%s\""),
1581 schemaname, relationname);
1584 printfPQExpBuffer(&title, _("View \"%s.%s\""),
1585 schemaname, relationname);
1588 if (tableinfo.relpersistence == 'u')
1589 printfPQExpBuffer(&title, _("Unlogged materialized view \"%s.%s\""),
1590 schemaname, relationname);
1592 printfPQExpBuffer(&title, _("Materialized view \"%s.%s\""),
1593 schemaname, relationname);
1596 printfPQExpBuffer(&title, _("Sequence \"%s.%s\""),
1597 schemaname, relationname);
1600 if (tableinfo.relpersistence == 'u')
1601 printfPQExpBuffer(&title, _("Unlogged index \"%s.%s\""),
1602 schemaname, relationname);
1604 printfPQExpBuffer(&title, _("Index \"%s.%s\""),
1605 schemaname, relationname);
1608 /* not used as of 8.2, but keep it for backwards compatibility */
1609 printfPQExpBuffer(&title, _("Special relation \"%s.%s\""),
1610 schemaname, relationname);
1613 printfPQExpBuffer(&title, _("TOAST table \"%s.%s\""),
1614 schemaname, relationname);
1617 printfPQExpBuffer(&title, _("Composite type \"%s.%s\""),
1618 schemaname, relationname);
1621 printfPQExpBuffer(&title, _("Foreign table \"%s.%s\""),
1622 schemaname, relationname);
1625 /* untranslated unknown relkind */
1626 printfPQExpBuffer(&title, "?%c? \"%s.%s\"",
1627 tableinfo.relkind, schemaname, relationname);
1631 /* Set the number of columns, and their names */
1632 headers[0] = gettext_noop("Column");
1633 headers[1] = gettext_noop("Type");
1636 if (tableinfo.relkind == 'r' || tableinfo.relkind == 'v' ||
1637 tableinfo.relkind == 'm' ||
1638 tableinfo.relkind == 'f' || tableinfo.relkind == 'c')
1640 show_modifiers = true;
1641 headers[cols++] = gettext_noop("Modifiers");
1642 modifiers = pg_malloc0((numrows + 1) * sizeof(*modifiers));
1645 if (tableinfo.relkind == 'S')
1646 headers[cols++] = gettext_noop("Value");
1648 if (tableinfo.relkind == 'i')
1649 headers[cols++] = gettext_noop("Definition");
1651 if (tableinfo.relkind == 'f' && pset.sversion >= 90200)
1652 headers[cols++] = gettext_noop("FDW Options");
1656 headers[cols++] = gettext_noop("Storage");
1657 if (tableinfo.relkind == 'r' || tableinfo.relkind == 'm' ||
1658 tableinfo.relkind == 'f')
1659 headers[cols++] = gettext_noop("Stats target");
1660 /* Column comments, if the relkind supports this feature. */
1661 if (tableinfo.relkind == 'r' || tableinfo.relkind == 'v' ||
1662 tableinfo.relkind == 'm' ||
1663 tableinfo.relkind == 'c' || tableinfo.relkind == 'f')
1664 headers[cols++] = gettext_noop("Description");
1667 printTableInit(&cont, &myopt, title.data, cols, numrows);
1668 printTableInitialized = true;
1670 for (i = 0; i < cols; i++)
1671 printTableAddHeader(&cont, headers[i], true, 'l');
1673 /* Check if table is a view or materialized view */
1674 if ((tableinfo.relkind == 'v' || tableinfo.relkind == 'm') && verbose)
1678 printfPQExpBuffer(&buf,
1679 "SELECT pg_catalog.pg_get_viewdef('%s'::pg_catalog.oid, true);",
1681 result = PSQLexec(buf.data);
1685 if (PQntuples(result) > 0)
1686 view_def = pg_strdup(PQgetvalue(result, 0, 0));
1691 /* Generate table cells to be printed */
1692 for (i = 0; i < numrows; i++)
1695 printTableAddCell(&cont, PQgetvalue(res, i, 0), false, false);
1698 printTableAddCell(&cont, PQgetvalue(res, i, 1), false, false);
1700 /* Modifiers: collate, not null, default */
1703 resetPQExpBuffer(&tmpbuf);
1705 if (!PQgetisnull(res, i, 5))
1708 appendPQExpBufferChar(&tmpbuf, ' ');
1709 appendPQExpBuffer(&tmpbuf, _("collate %s"),
1710 PQgetvalue(res, i, 5));
1713 if (strcmp(PQgetvalue(res, i, 3), "t") == 0)
1716 appendPQExpBufferChar(&tmpbuf, ' ');
1717 appendPQExpBufferStr(&tmpbuf, _("not null"));
1720 /* handle "default" here */
1721 /* (note: above we cut off the 'default' string at 128) */
1722 if (strlen(PQgetvalue(res, i, 2)) != 0)
1725 appendPQExpBufferChar(&tmpbuf, ' ');
1726 /* translator: default values of column definitions */
1727 appendPQExpBuffer(&tmpbuf, _("default %s"),
1728 PQgetvalue(res, i, 2));
1731 modifiers[i] = pg_strdup(tmpbuf.data);
1732 printTableAddCell(&cont, modifiers[i], false, false);
1735 /* Value: for sequences only */
1736 if (tableinfo.relkind == 'S')
1737 printTableAddCell(&cont, seq_values[i], false, false);
1739 /* Expression for index column */
1740 if (tableinfo.relkind == 'i')
1741 printTableAddCell(&cont, PQgetvalue(res, i, 6), false, false);
1743 /* FDW options for foreign table column, only for 9.2 or later */
1744 if (tableinfo.relkind == 'f' && pset.sversion >= 90200)
1745 printTableAddCell(&cont, PQgetvalue(res, i, 7), false, false);
1747 /* Storage and Description */
1751 char *storage = PQgetvalue(res, i, firstvcol);
1753 /* these strings are literal in our syntax, so not translated. */
1754 printTableAddCell(&cont, (storage[0] == 'p' ? "plain" :
1755 (storage[0] == 'm' ? "main" :
1756 (storage[0] == 'x' ? "extended" :
1757 (storage[0] == 'e' ? "external" :
1761 /* Statistics target, if the relkind supports this feature */
1762 if (tableinfo.relkind == 'r' || tableinfo.relkind == 'm' ||
1763 tableinfo.relkind == 'f')
1765 printTableAddCell(&cont, PQgetvalue(res, i, firstvcol + 1),
1769 /* Column comments, if the relkind supports this feature. */
1770 if (tableinfo.relkind == 'r' || tableinfo.relkind == 'v' ||
1771 tableinfo.relkind == 'm' ||
1772 tableinfo.relkind == 'c' || tableinfo.relkind == 'f')
1773 printTableAddCell(&cont, PQgetvalue(res, i, firstvcol + 2),
1779 if (tableinfo.relkind == 'i')
1781 /* Footer information about an index */
1784 printfPQExpBuffer(&buf,
1785 "SELECT i.indisunique, i.indisprimary, i.indisclustered, ");
1786 if (pset.sversion >= 80200)
1787 appendPQExpBufferStr(&buf, "i.indisvalid,\n");
1789 appendPQExpBufferStr(&buf, "true AS indisvalid,\n");
1790 if (pset.sversion >= 90000)
1791 appendPQExpBufferStr(&buf,
1792 " (NOT i.indimmediate) AND "
1793 "EXISTS (SELECT 1 FROM pg_catalog.pg_constraint "
1794 "WHERE conrelid = i.indrelid AND "
1795 "conindid = i.indexrelid AND "
1796 "contype IN ('p','u','x') AND "
1797 "condeferrable) AS condeferrable,\n"
1798 " (NOT i.indimmediate) AND "
1799 "EXISTS (SELECT 1 FROM pg_catalog.pg_constraint "
1800 "WHERE conrelid = i.indrelid AND "
1801 "conindid = i.indexrelid AND "
1802 "contype IN ('p','u','x') AND "
1803 "condeferred) AS condeferred,\n");
1805 appendPQExpBufferStr(&buf,
1806 " false AS condeferrable, false AS condeferred,\n");
1808 if (pset.sversion >= 90400)
1809 appendPQExpBuffer(&buf, "i.indisreplident,\n");
1811 appendPQExpBuffer(&buf, "false AS indisreplident,\n");
1813 appendPQExpBuffer(&buf, " a.amname, c2.relname, "
1814 "pg_catalog.pg_get_expr(i.indpred, i.indrelid, true)\n"
1815 "FROM pg_catalog.pg_index i, pg_catalog.pg_class c, pg_catalog.pg_class c2, pg_catalog.pg_am a\n"
1816 "WHERE i.indexrelid = c.oid AND c.oid = '%s' AND c.relam = a.oid\n"
1817 "AND i.indrelid = c2.oid;",
1820 result = PSQLexec(buf.data);
1823 else if (PQntuples(result) != 1)
1830 char *indisunique = PQgetvalue(result, 0, 0);
1831 char *indisprimary = PQgetvalue(result, 0, 1);
1832 char *indisclustered = PQgetvalue(result, 0, 2);
1833 char *indisvalid = PQgetvalue(result, 0, 3);
1834 char *deferrable = PQgetvalue(result, 0, 4);
1835 char *deferred = PQgetvalue(result, 0, 5);
1836 char *indisreplident = PQgetvalue(result, 0, 6);
1837 char *indamname = PQgetvalue(result, 0, 7);
1838 char *indtable = PQgetvalue(result, 0, 8);
1839 char *indpred = PQgetvalue(result, 0, 9);
1841 if (strcmp(indisprimary, "t") == 0)
1842 printfPQExpBuffer(&tmpbuf, _("primary key, "));
1843 else if (strcmp(indisunique, "t") == 0)
1844 printfPQExpBuffer(&tmpbuf, _("unique, "));
1846 resetPQExpBuffer(&tmpbuf);
1847 appendPQExpBuffer(&tmpbuf, "%s, ", indamname);
1849 /* we assume here that index and table are in same schema */
1850 appendPQExpBuffer(&tmpbuf, _("for table \"%s.%s\""),
1851 schemaname, indtable);
1853 if (strlen(indpred))
1854 appendPQExpBuffer(&tmpbuf, _(", predicate (%s)"), indpred);
1856 if (strcmp(indisclustered, "t") == 0)
1857 appendPQExpBufferStr(&tmpbuf, _(", clustered"));
1859 if (strcmp(indisvalid, "t") != 0)
1860 appendPQExpBufferStr(&tmpbuf, _(", invalid"));
1862 if (strcmp(deferrable, "t") == 0)
1863 appendPQExpBufferStr(&tmpbuf, _(", deferrable"));
1865 if (strcmp(deferred, "t") == 0)
1866 appendPQExpBufferStr(&tmpbuf, _(", initially deferred"));
1868 if (strcmp(indisreplident, "t") == 0)
1869 appendPQExpBuffer(&tmpbuf, _(", replica identity"));
1871 printTableAddFooter(&cont, tmpbuf.data);
1872 add_tablespace_footer(&cont, tableinfo.relkind,
1873 tableinfo.tablespace, true);
1878 else if (tableinfo.relkind == 'S')
1880 /* Footer information about a sequence */
1881 PGresult *result = NULL;
1883 /* Get the column that owns this sequence */
1884 printfPQExpBuffer(&buf, "SELECT pg_catalog.quote_ident(nspname) || '.' ||"
1885 "\n pg_catalog.quote_ident(relname) || '.' ||"
1886 "\n pg_catalog.quote_ident(attname)"
1887 "\nFROM pg_catalog.pg_class c"
1888 "\nINNER JOIN pg_catalog.pg_depend d ON c.oid=d.refobjid"
1889 "\nINNER JOIN pg_catalog.pg_namespace n ON n.oid=c.relnamespace"
1890 "\nINNER JOIN pg_catalog.pg_attribute a ON ("
1891 "\n a.attrelid=c.oid AND"
1892 "\n a.attnum=d.refobjsubid)"
1893 "\nWHERE d.classid='pg_catalog.pg_class'::pg_catalog.regclass"
1894 "\n AND d.refclassid='pg_catalog.pg_class'::pg_catalog.regclass"
1896 "\n AND d.deptype='a'",
1899 result = PSQLexec(buf.data);
1902 else if (PQntuples(result) == 1)
1904 printfPQExpBuffer(&buf, _("Owned by: %s"),
1905 PQgetvalue(result, 0, 0));
1906 printTableAddFooter(&cont, buf.data);
1910 * If we get no rows back, don't show anything (obviously). We should
1911 * never get more than one row back, but if we do, just ignore it and
1912 * don't print anything.
1916 else if (tableinfo.relkind == 'r' || tableinfo.relkind == 'm' ||
1917 tableinfo.relkind == 'f')
1919 /* Footer information about a table */
1920 PGresult *result = NULL;
1924 if (tableinfo.hasindex)
1926 printfPQExpBuffer(&buf,
1927 "SELECT c2.relname, i.indisprimary, i.indisunique, i.indisclustered, ");
1928 if (pset.sversion >= 80200)
1929 appendPQExpBufferStr(&buf, "i.indisvalid, ");
1931 appendPQExpBufferStr(&buf, "true as indisvalid, ");
1932 appendPQExpBufferStr(&buf, "pg_catalog.pg_get_indexdef(i.indexrelid, 0, true),\n ");
1933 if (pset.sversion >= 90000)
1934 appendPQExpBufferStr(&buf,
1935 "pg_catalog.pg_get_constraintdef(con.oid, true), "
1936 "contype, condeferrable, condeferred");
1938 appendPQExpBufferStr(&buf,
1939 "null AS constraintdef, null AS contype, "
1940 "false AS condeferrable, false AS condeferred");
1941 if (pset.sversion >= 90400)
1942 appendPQExpBufferStr(&buf, ", i.indisreplident");
1944 appendPQExpBufferStr(&buf, ", false AS indisreplident");
1945 if (pset.sversion >= 80000)
1946 appendPQExpBufferStr(&buf, ", c2.reltablespace");
1947 appendPQExpBufferStr(&buf,
1948 "\nFROM pg_catalog.pg_class c, pg_catalog.pg_class c2, pg_catalog.pg_index i\n");
1949 if (pset.sversion >= 90000)
1950 appendPQExpBufferStr(&buf,
1951 " LEFT JOIN pg_catalog.pg_constraint con ON (conrelid = i.indrelid AND conindid = i.indexrelid AND contype IN ('p','u','x'))\n");
1952 appendPQExpBuffer(&buf,
1953 "WHERE c.oid = '%s' AND c.oid = i.indrelid AND i.indexrelid = c2.oid\n"
1954 "ORDER BY i.indisprimary DESC, i.indisunique DESC, c2.relname;",
1956 result = PSQLexec(buf.data);
1960 tuples = PQntuples(result);
1964 printTableAddFooter(&cont, _("Indexes:"));
1965 for (i = 0; i < tuples; i++)
1967 /* untranslated index name */
1968 printfPQExpBuffer(&buf, " \"%s\"",
1969 PQgetvalue(result, i, 0));
1971 /* If exclusion constraint, print the constraintdef */
1972 if (strcmp(PQgetvalue(result, i, 7), "x") == 0)
1974 appendPQExpBuffer(&buf, " %s",
1975 PQgetvalue(result, i, 6));
1979 const char *indexdef;
1980 const char *usingpos;
1982 /* Label as primary key or unique (but not both) */
1983 if (strcmp(PQgetvalue(result, i, 1), "t") == 0)
1984 appendPQExpBufferStr(&buf, " PRIMARY KEY,");
1985 else if (strcmp(PQgetvalue(result, i, 2), "t") == 0)
1987 if (strcmp(PQgetvalue(result, i, 7), "u") == 0)
1988 appendPQExpBufferStr(&buf, " UNIQUE CONSTRAINT,");
1990 appendPQExpBufferStr(&buf, " UNIQUE,");
1993 /* Everything after "USING" is echoed verbatim */
1994 indexdef = PQgetvalue(result, i, 5);
1995 usingpos = strstr(indexdef, " USING ");
1997 indexdef = usingpos + 7;
1998 appendPQExpBuffer(&buf, " %s", indexdef);
2000 /* Need these for deferrable PK/UNIQUE indexes */
2001 if (strcmp(PQgetvalue(result, i, 8), "t") == 0)
2002 appendPQExpBufferStr(&buf, " DEFERRABLE");
2004 if (strcmp(PQgetvalue(result, i, 9), "t") == 0)
2005 appendPQExpBufferStr(&buf, " INITIALLY DEFERRED");
2008 /* Add these for all cases */
2009 if (strcmp(PQgetvalue(result, i, 3), "t") == 0)
2010 appendPQExpBufferStr(&buf, " CLUSTER");
2012 if (strcmp(PQgetvalue(result, i, 4), "t") != 0)
2013 appendPQExpBufferStr(&buf, " INVALID");
2015 if (strcmp(PQgetvalue(result, i, 10), "t") == 0)
2016 appendPQExpBuffer(&buf, " REPLICA IDENTITY");
2018 printTableAddFooter(&cont, buf.data);
2020 /* Print tablespace of the index on the same line */
2021 if (pset.sversion >= 80000)
2022 add_tablespace_footer(&cont, 'i',
2023 atooid(PQgetvalue(result, i, 11)),
2030 /* print table (and column) check constraints */
2031 if (tableinfo.checks)
2033 printfPQExpBuffer(&buf,
2034 "SELECT r.conname, "
2035 "pg_catalog.pg_get_constraintdef(r.oid, true)\n"
2036 "FROM pg_catalog.pg_constraint r\n"
2037 "WHERE r.conrelid = '%s' AND r.contype = 'c'\n"
2040 result = PSQLexec(buf.data);
2044 tuples = PQntuples(result);
2048 printTableAddFooter(&cont, _("Check constraints:"));
2049 for (i = 0; i < tuples; i++)
2051 /* untranslated contraint name and def */
2052 printfPQExpBuffer(&buf, " \"%s\" %s",
2053 PQgetvalue(result, i, 0),
2054 PQgetvalue(result, i, 1));
2056 printTableAddFooter(&cont, buf.data);
2062 /* print foreign-key constraints (there are none if no triggers) */
2063 if (tableinfo.hastriggers)
2065 printfPQExpBuffer(&buf,
2067 " pg_catalog.pg_get_constraintdef(r.oid, true) as condef\n"
2068 "FROM pg_catalog.pg_constraint r\n"
2069 "WHERE r.conrelid = '%s' AND r.contype = 'f' ORDER BY 1;",
2071 result = PSQLexec(buf.data);
2075 tuples = PQntuples(result);
2079 printTableAddFooter(&cont, _("Foreign-key constraints:"));
2080 for (i = 0; i < tuples; i++)
2082 /* untranslated constraint name and def */
2083 printfPQExpBuffer(&buf, " \"%s\" %s",
2084 PQgetvalue(result, i, 0),
2085 PQgetvalue(result, i, 1));
2087 printTableAddFooter(&cont, buf.data);
2093 /* print incoming foreign-key references (none if no triggers) */
2094 if (tableinfo.hastriggers)
2096 printfPQExpBuffer(&buf,
2097 "SELECT conname, conrelid::pg_catalog.regclass,\n"
2098 " pg_catalog.pg_get_constraintdef(c.oid, true) as condef\n"
2099 "FROM pg_catalog.pg_constraint c\n"
2100 "WHERE c.confrelid = '%s' AND c.contype = 'f' ORDER BY 1;",
2102 result = PSQLexec(buf.data);
2106 tuples = PQntuples(result);
2110 printTableAddFooter(&cont, _("Referenced by:"));
2111 for (i = 0; i < tuples; i++)
2113 printfPQExpBuffer(&buf, " TABLE \"%s\" CONSTRAINT \"%s\" %s",
2114 PQgetvalue(result, i, 1),
2115 PQgetvalue(result, i, 0),
2116 PQgetvalue(result, i, 2));
2118 printTableAddFooter(&cont, buf.data);
2124 /* print any row-level policies */
2125 if (pset.sversion >= 90500)
2127 printfPQExpBuffer(&buf,
2128 "SELECT pol.polname,\n"
2129 "CASE WHEN pol.polroles = '{0}' THEN NULL ELSE array_to_string(array(select rolname from pg_roles where oid = any (pol.polroles) order by 1),',') END,\n"
2130 "pg_catalog.pg_get_expr(pol.polqual, pol.polrelid),\n"
2131 "pg_catalog.pg_get_expr(pol.polwithcheck, pol.polrelid),\n"
2132 "CASE pol.polcmd \n"
2133 "WHEN 'r' THEN 'SELECT'\n"
2134 "WHEN 'a' THEN 'INSERT'\n"
2135 "WHEN 'w' THEN 'UPDATE'\n"
2136 "WHEN 'd' THEN 'DELETE'\n"
2137 "WHEN '*' THEN 'ALL'\n"
2139 "FROM pg_catalog.pg_policy pol\n"
2140 "WHERE pol.polrelid = '%s' ORDER BY 1;",
2143 result = PSQLexec(buf.data);
2147 tuples = PQntuples(result);
2150 * Handle cases where RLS is enabled and there are policies, or
2151 * there aren't policies, or RLS isn't enabled but there are
2154 if (tableinfo.rowsecurity && !tableinfo.forcerowsecurity && tuples > 0)
2155 printTableAddFooter(&cont, _("Policies:"));
2157 if (tableinfo.rowsecurity && tableinfo.forcerowsecurity && tuples > 0)
2158 printTableAddFooter(&cont, _("Policies (forced row security enabled):"));
2160 if (tableinfo.rowsecurity && !tableinfo.forcerowsecurity && tuples == 0)
2161 printTableAddFooter(&cont, _("Policies (row security enabled): (none)"));
2163 if (tableinfo.rowsecurity && tableinfo.forcerowsecurity && tuples == 0)
2164 printTableAddFooter(&cont, _("Policies (forced row security enabled): (none)"));
2166 if (!tableinfo.rowsecurity && tuples > 0)
2167 printTableAddFooter(&cont, _("Policies (row security disabled):"));
2169 /* Might be an empty set - that's ok */
2170 for (i = 0; i < tuples; i++)
2172 printfPQExpBuffer(&buf, " POLICY \"%s\"",
2173 PQgetvalue(result, i, 0));
2175 if (!PQgetisnull(result, i, 4))
2176 appendPQExpBuffer(&buf, " FOR %s",
2177 PQgetvalue(result, i, 4));
2179 if (!PQgetisnull(result, i, 1))
2181 appendPQExpBuffer(&buf, "\n TO %s",
2182 PQgetvalue(result, i, 1));
2185 if (!PQgetisnull(result, i, 2))
2186 appendPQExpBuffer(&buf, "\n USING (%s)",
2187 PQgetvalue(result, i, 2));
2189 if (!PQgetisnull(result, i, 3))
2190 appendPQExpBuffer(&buf, "\n WITH CHECK (%s)",
2191 PQgetvalue(result, i, 3));
2193 printTableAddFooter(&cont, buf.data);
2200 if (tableinfo.hasrules && tableinfo.relkind != 'm')
2202 if (pset.sversion >= 80300)
2204 printfPQExpBuffer(&buf,
2205 "SELECT r.rulename, trim(trailing ';' from pg_catalog.pg_get_ruledef(r.oid, true)), "
2207 "FROM pg_catalog.pg_rewrite r\n"
2208 "WHERE r.ev_class = '%s' ORDER BY 1;",
2213 printfPQExpBuffer(&buf,
2214 "SELECT r.rulename, trim(trailing ';' from pg_catalog.pg_get_ruledef(r.oid, true)), "
2215 "'O'::char AS ev_enabled\n"
2216 "FROM pg_catalog.pg_rewrite r\n"
2217 "WHERE r.ev_class = '%s' ORDER BY 1;",
2220 result = PSQLexec(buf.data);
2224 tuples = PQntuples(result);
2231 for (category = 0; category < 4; category++)
2233 have_heading = false;
2235 for (i = 0; i < tuples; i++)
2237 const char *ruledef;
2238 bool list_rule = false;
2243 if (*PQgetvalue(result, i, 2) == 'O')
2247 if (*PQgetvalue(result, i, 2) == 'D')
2251 if (*PQgetvalue(result, i, 2) == 'A')
2255 if (*PQgetvalue(result, i, 2) == 'R')
2267 printfPQExpBuffer(&buf, _("Rules:"));
2270 printfPQExpBuffer(&buf, _("Disabled rules:"));
2273 printfPQExpBuffer(&buf, _("Rules firing always:"));
2276 printfPQExpBuffer(&buf, _("Rules firing on replica only:"));
2279 printTableAddFooter(&cont, buf.data);
2280 have_heading = true;
2283 /* Everything after "CREATE RULE" is echoed verbatim */
2284 ruledef = PQgetvalue(result, i, 1);
2286 printfPQExpBuffer(&buf, " %s", ruledef);
2287 printTableAddFooter(&cont, buf.data);
2297 PGresult *result = NULL;
2299 /* Footer information about a view */
2300 printTableAddFooter(&cont, _("View definition:"));
2301 printTableAddFooter(&cont, view_def);
2304 if (tableinfo.hasrules)
2306 printfPQExpBuffer(&buf,
2307 "SELECT r.rulename, trim(trailing ';' from pg_catalog.pg_get_ruledef(r.oid, true))\n"
2308 "FROM pg_catalog.pg_rewrite r\n"
2309 "WHERE r.ev_class = '%s' AND r.rulename != '_RETURN' ORDER BY 1;",
2311 result = PSQLexec(buf.data);
2315 if (PQntuples(result) > 0)
2317 printTableAddFooter(&cont, _("Rules:"));
2318 for (i = 0; i < PQntuples(result); i++)
2320 const char *ruledef;
2322 /* Everything after "CREATE RULE" is echoed verbatim */
2323 ruledef = PQgetvalue(result, i, 1);
2326 printfPQExpBuffer(&buf, " %s", ruledef);
2327 printTableAddFooter(&cont, buf.data);
2335 * Print triggers next, if any (but only user-defined triggers). This
2336 * could apply to either a table or a view.
2338 if (tableinfo.hastriggers)
2343 printfPQExpBuffer(&buf,
2345 "pg_catalog.pg_get_triggerdef(t.oid%s), "
2347 "FROM pg_catalog.pg_trigger t\n"
2348 "WHERE t.tgrelid = '%s' AND ",
2349 (pset.sversion >= 90000 ? ", true" : ""),
2350 (pset.sversion >= 90000 ? "t.tgisinternal" :
2351 pset.sversion >= 80300 ?
2352 "t.tgconstraint <> 0 AS tgisinternal" :
2353 "false AS tgisinternal"), oid);
2354 if (pset.sversion >= 90000)
2355 /* display/warn about disabled internal triggers */
2356 appendPQExpBuffer(&buf, "(NOT t.tgisinternal OR (t.tgisinternal AND t.tgenabled = 'D'))");
2357 else if (pset.sversion >= 80300)
2358 appendPQExpBufferStr(&buf, "(t.tgconstraint = 0 OR (t.tgconstraint <> 0 AND t.tgenabled = 'D'))");
2360 appendPQExpBufferStr(&buf,
2361 "(NOT tgisconstraint "
2363 " (SELECT 1 FROM pg_catalog.pg_depend d "
2364 " JOIN pg_catalog.pg_constraint c ON (d.refclassid = c.tableoid AND d.refobjid = c.oid) "
2365 " WHERE d.classid = t.tableoid AND d.objid = t.oid AND d.deptype = 'i' AND c.contype = 'f'))");
2366 appendPQExpBufferStr(&buf, "\nORDER BY 1;");
2368 result = PSQLexec(buf.data);
2372 tuples = PQntuples(result);
2380 * split the output into 4 different categories. Enabled triggers,
2381 * disabled triggers and the two special ALWAYS and REPLICA
2384 for (category = 0; category <= 4; category++)
2386 have_heading = false;
2387 for (i = 0; i < tuples; i++)
2391 const char *usingpos;
2392 const char *tgenabled;
2393 const char *tgisinternal;
2396 * Check if this trigger falls into the current category
2398 tgenabled = PQgetvalue(result, i, 2);
2399 tgisinternal = PQgetvalue(result, i, 3);
2400 list_trigger = false;
2404 if (*tgenabled == 'O' || *tgenabled == 't')
2405 list_trigger = true;
2408 if ((*tgenabled == 'D' || *tgenabled == 'f') &&
2409 *tgisinternal == 'f')
2410 list_trigger = true;
2413 if ((*tgenabled == 'D' || *tgenabled == 'f') &&
2414 *tgisinternal == 't')
2415 list_trigger = true;
2418 if (*tgenabled == 'A')
2419 list_trigger = true;
2422 if (*tgenabled == 'R')
2423 list_trigger = true;
2426 if (list_trigger == false)
2429 /* Print the category heading once */
2430 if (have_heading == false)
2435 printfPQExpBuffer(&buf, _("Triggers:"));
2438 if (pset.sversion >= 80300)
2439 printfPQExpBuffer(&buf, _("Disabled user triggers:"));
2441 printfPQExpBuffer(&buf, _("Disabled triggers:"));
2444 printfPQExpBuffer(&buf, _("Disabled internal triggers:"));
2447 printfPQExpBuffer(&buf, _("Triggers firing always:"));
2450 printfPQExpBuffer(&buf, _("Triggers firing on replica only:"));
2454 printTableAddFooter(&cont, buf.data);
2455 have_heading = true;
2458 /* Everything after "TRIGGER" is echoed verbatim */
2459 tgdef = PQgetvalue(result, i, 1);
2460 usingpos = strstr(tgdef, " TRIGGER ");
2462 tgdef = usingpos + 9;
2464 printfPQExpBuffer(&buf, " %s", tgdef);
2465 printTableAddFooter(&cont, buf.data);
2473 * Finish printing the footer information about a table.
2475 if (tableinfo.relkind == 'r' || tableinfo.relkind == 'm' ||
2476 tableinfo.relkind == 'f')
2481 /* print foreign server name */
2482 if (tableinfo.relkind == 'f')
2486 /* Footer information about foreign table */
2487 printfPQExpBuffer(&buf,
2488 "SELECT s.srvname,\n"
2489 " array_to_string(ARRAY(SELECT "
2490 " quote_ident(option_name) || ' ' || "
2491 " quote_literal(option_value) FROM "
2492 " pg_options_to_table(ftoptions)), ', ') "
2493 "FROM pg_catalog.pg_foreign_table f,\n"
2494 " pg_catalog.pg_foreign_server s\n"
2495 "WHERE f.ftrelid = %s AND s.oid = f.ftserver;",
2497 result = PSQLexec(buf.data);
2500 else if (PQntuples(result) != 1)
2506 /* Print server name */
2507 printfPQExpBuffer(&buf, "Server: %s",
2508 PQgetvalue(result, 0, 0));
2509 printTableAddFooter(&cont, buf.data);
2511 /* Print per-table FDW options, if any */
2512 ftoptions = PQgetvalue(result, 0, 1);
2513 if (ftoptions && ftoptions[0] != '\0')
2515 printfPQExpBuffer(&buf, "FDW Options: (%s)", ftoptions);
2516 printTableAddFooter(&cont, buf.data);
2521 /* print inherited tables */
2522 printfPQExpBuffer(&buf, "SELECT c.oid::pg_catalog.regclass FROM pg_catalog.pg_class c, pg_catalog.pg_inherits i WHERE c.oid=i.inhparent AND i.inhrelid = '%s' ORDER BY inhseqno;", oid);
2524 result = PSQLexec(buf.data);
2529 const char *s = _("Inherits");
2530 int sw = pg_wcswidth(s, strlen(s), pset.encoding);
2532 tuples = PQntuples(result);
2534 for (i = 0; i < tuples; i++)
2537 printfPQExpBuffer(&buf, "%s: %s",
2538 s, PQgetvalue(result, i, 0));
2540 printfPQExpBuffer(&buf, "%*s %s",
2541 sw, "", PQgetvalue(result, i, 0));
2543 appendPQExpBufferChar(&buf, ',');
2545 printTableAddFooter(&cont, buf.data);
2551 /* print child tables */
2552 if (pset.sversion >= 80300)
2553 printfPQExpBuffer(&buf, "SELECT c.oid::pg_catalog.regclass FROM pg_catalog.pg_class c, pg_catalog.pg_inherits i WHERE c.oid=i.inhrelid AND i.inhparent = '%s' ORDER BY c.oid::pg_catalog.regclass::pg_catalog.text;", oid);
2555 printfPQExpBuffer(&buf, "SELECT c.oid::pg_catalog.regclass FROM pg_catalog.pg_class c, pg_catalog.pg_inherits i WHERE c.oid=i.inhrelid AND i.inhparent = '%s' ORDER BY c.relname;", oid);
2557 result = PSQLexec(buf.data);
2561 tuples = PQntuples(result);
2565 /* print the number of child tables, if any */
2568 printfPQExpBuffer(&buf, _("Number of child tables: %d (Use \\d+ to list them.)"), tuples);
2569 printTableAddFooter(&cont, buf.data);
2574 /* display the list of child tables */
2575 const char *ct = _("Child tables");
2576 int ctw = pg_wcswidth(ct, strlen(ct), pset.encoding);
2578 for (i = 0; i < tuples; i++)
2581 printfPQExpBuffer(&buf, "%s: %s",
2582 ct, PQgetvalue(result, i, 0));
2584 printfPQExpBuffer(&buf, "%*s %s",
2585 ctw, "", PQgetvalue(result, i, 0));
2587 appendPQExpBufferChar(&buf, ',');
2589 printTableAddFooter(&cont, buf.data);
2595 if (tableinfo.reloftype)
2597 printfPQExpBuffer(&buf, _("Typed table of type: %s"), tableinfo.reloftype);
2598 printTableAddFooter(&cont, buf.data);
2601 if (verbose && (tableinfo.relkind == 'r' || tableinfo.relkind == 'm') &&
2604 * No need to display default values; we already display a REPLICA
2605 * IDENTITY marker on indexes.
2607 tableinfo.relreplident != 'i' &&
2608 ((strcmp(schemaname, "pg_catalog") != 0 && tableinfo.relreplident != 'd') ||
2609 (strcmp(schemaname, "pg_catalog") == 0 && tableinfo.relreplident != 'n')))
2611 const char *s = _("Replica Identity");
2613 printfPQExpBuffer(&buf, "%s: %s",
2615 tableinfo.relreplident == 'f' ? "FULL" :
2616 tableinfo.relreplident == 'n' ? "NOTHING" :
2619 printTableAddFooter(&cont, buf.data);
2622 /* OIDs, if verbose and not a materialized view */
2623 if (verbose && tableinfo.relkind != 'm' && tableinfo.hasoids)
2624 printTableAddFooter(&cont, _("Has OIDs: yes"));
2626 /* Tablespace info */
2627 add_tablespace_footer(&cont, tableinfo.relkind, tableinfo.tablespace,
2631 /* reloptions, if verbose */
2633 tableinfo.reloptions && tableinfo.reloptions[0] != '\0')
2635 const char *t = _("Options");
2637 printfPQExpBuffer(&buf, "%s: %s", t, tableinfo.reloptions);
2638 printTableAddFooter(&cont, buf.data);
2641 printTable(&cont, pset.queryFout, false, pset.logfile);
2648 if (printTableInitialized)
2649 printTableCleanup(&cont);
2650 termPQExpBuffer(&buf);
2651 termPQExpBuffer(&title);
2652 termPQExpBuffer(&tmpbuf);
2656 for (ptr = seq_values; *ptr; ptr++)
2663 for (ptr = modifiers; *ptr; ptr++)
2678 * Add a tablespace description to a footer. If 'newline' is true, it is added
2679 * in a new line; otherwise it's appended to the current value of the last
2683 add_tablespace_footer(printTableContent *const cont, char relkind,
2684 Oid tablespace, const bool newline)
2686 /* relkinds for which we support tablespaces */
2687 if (relkind == 'r' || relkind == 'm' || relkind == 'i')
2690 * We ignore the database default tablespace so that users not using
2691 * tablespaces don't need to know about them. This case also covers
2692 * pre-8.0 servers, for which tablespace will always be 0.
2694 if (tablespace != 0)
2696 PGresult *result = NULL;
2697 PQExpBufferData buf;
2699 initPQExpBuffer(&buf);
2700 printfPQExpBuffer(&buf,
2701 "SELECT spcname FROM pg_catalog.pg_tablespace\n"
2702 "WHERE oid = '%u';", tablespace);
2703 result = PSQLexec(buf.data);
2706 /* Should always be the case, but.... */
2707 if (PQntuples(result) > 0)
2711 /* Add the tablespace as a new footer */
2712 printfPQExpBuffer(&buf, _("Tablespace: \"%s\""),
2713 PQgetvalue(result, 0, 0));
2714 printTableAddFooter(cont, buf.data);
2718 /* Append the tablespace to the latest footer */
2719 printfPQExpBuffer(&buf, "%s", cont->footer->data);
2722 translator: before this string there's an index description like
2723 '"foo_pkey" PRIMARY KEY, btree (a)' */
2724 appendPQExpBuffer(&buf, _(", tablespace \"%s\""),
2725 PQgetvalue(result, 0, 0));
2726 printTableSetFooter(cont, buf.data);
2730 termPQExpBuffer(&buf);
2738 * Describes roles. Any schema portion of the pattern is ignored.
2741 describeRoles(const char *pattern, bool verbose, bool showSystem)
2743 PQExpBufferData buf;
2745 printTableContent cont;
2746 printTableOpt myopt = pset.popt.topt;
2751 const char align = 'l';
2754 myopt.default_footer = false;
2756 initPQExpBuffer(&buf);
2758 if (pset.sversion >= 80100)
2760 printfPQExpBuffer(&buf,
2761 "SELECT r.rolname, r.rolsuper, r.rolinherit,\n"
2762 " r.rolcreaterole, r.rolcreatedb, r.rolcanlogin,\n"
2763 " r.rolconnlimit, r.rolvaliduntil,\n"
2764 " ARRAY(SELECT b.rolname\n"
2765 " FROM pg_catalog.pg_auth_members m\n"
2766 " JOIN pg_catalog.pg_roles b ON (m.roleid = b.oid)\n"
2767 " WHERE m.member = r.oid) as memberof");
2769 if (verbose && pset.sversion >= 80200)
2771 appendPQExpBufferStr(&buf, "\n, pg_catalog.shobj_description(r.oid, 'pg_authid') AS description");
2774 if (pset.sversion >= 90100)
2776 appendPQExpBufferStr(&buf, "\n, r.rolreplication");
2779 if (pset.sversion >= 90500)
2781 appendPQExpBufferStr(&buf, "\n, r.rolbypassrls");
2784 appendPQExpBufferStr(&buf, "\nFROM pg_catalog.pg_roles r\n");
2786 if (!showSystem && !pattern)
2787 appendPQExpBufferStr(&buf, "WHERE r.rolname !~ '^pg_'\n");
2789 processSQLNamePattern(pset.db, &buf, pattern, false, false,
2790 NULL, "r.rolname", NULL, NULL);
2794 printfPQExpBuffer(&buf,
2795 "SELECT u.usename AS rolname,\n"
2796 " u.usesuper AS rolsuper,\n"
2797 " true AS rolinherit, false AS rolcreaterole,\n"
2798 " u.usecreatedb AS rolcreatedb, true AS rolcanlogin,\n"
2799 " -1 AS rolconnlimit,"
2800 " u.valuntil as rolvaliduntil,\n"
2801 " ARRAY(SELECT g.groname FROM pg_catalog.pg_group g WHERE u.usesysid = ANY(g.grolist)) as memberof"
2802 "\nFROM pg_catalog.pg_user u\n");
2804 processSQLNamePattern(pset.db, &buf, pattern, false, false,
2805 NULL, "u.usename", NULL, NULL);
2808 appendPQExpBufferStr(&buf, "ORDER BY 1;");
2810 res = PSQLexec(buf.data);
2814 nrows = PQntuples(res);
2815 attr = pg_malloc0((nrows + 1) * sizeof(*attr));
2817 printTableInit(&cont, &myopt, _("List of roles"), ncols, nrows);
2819 printTableAddHeader(&cont, gettext_noop("Role name"), true, align);
2820 printTableAddHeader(&cont, gettext_noop("Attributes"), true, align);
2821 printTableAddHeader(&cont, gettext_noop("Member of"), true, align);
2823 if (verbose && pset.sversion >= 80200)
2824 printTableAddHeader(&cont, gettext_noop("Description"), true, align);
2826 for (i = 0; i < nrows; i++)
2828 printTableAddCell(&cont, PQgetvalue(res, i, 0), false, false);
2830 resetPQExpBuffer(&buf);
2831 if (strcmp(PQgetvalue(res, i, 1), "t") == 0)
2832 add_role_attribute(&buf, _("Superuser"));
2834 if (strcmp(PQgetvalue(res, i, 2), "t") != 0)
2835 add_role_attribute(&buf, _("No inheritance"));
2837 if (strcmp(PQgetvalue(res, i, 3), "t") == 0)
2838 add_role_attribute(&buf, _("Create role"));
2840 if (strcmp(PQgetvalue(res, i, 4), "t") == 0)
2841 add_role_attribute(&buf, _("Create DB"));
2843 if (strcmp(PQgetvalue(res, i, 5), "t") != 0)
2844 add_role_attribute(&buf, _("Cannot login"));
2846 if (pset.sversion >= 90100)
2847 if (strcmp(PQgetvalue(res, i, (verbose ? 10 : 9)), "t") == 0)
2848 add_role_attribute(&buf, _("Replication"));
2850 if (pset.sversion >= 90500)
2851 if (strcmp(PQgetvalue(res, i, (verbose ? 11 : 10)), "t") == 0)
2852 add_role_attribute(&buf, _("Bypass RLS"));
2854 conns = atoi(PQgetvalue(res, i, 6));
2858 appendPQExpBufferChar(&buf, '\n');
2861 appendPQExpBufferStr(&buf, _("No connections"));
2863 appendPQExpBuffer(&buf, ngettext("%d connection",
2869 if (strcmp(PQgetvalue(res, i, 7), "") != 0)
2872 appendPQExpBufferStr(&buf, "\n");
2873 appendPQExpBufferStr(&buf, _("Password valid until "));
2874 appendPQExpBufferStr(&buf, PQgetvalue(res, i, 7));
2877 attr[i] = pg_strdup(buf.data);
2879 printTableAddCell(&cont, attr[i], false, false);
2881 printTableAddCell(&cont, PQgetvalue(res, i, 8), false, false);
2883 if (verbose && pset.sversion >= 80200)
2884 printTableAddCell(&cont, PQgetvalue(res, i, 9), false, false);
2886 termPQExpBuffer(&buf);
2888 printTable(&cont, pset.queryFout, false, pset.logfile);
2889 printTableCleanup(&cont);
2891 for (i = 0; i < nrows; i++)
2900 add_role_attribute(PQExpBuffer buf, const char *const str)
2903 appendPQExpBufferStr(buf, ", ");
2905 appendPQExpBufferStr(buf, str);
2912 listDbRoleSettings(const char *pattern, const char *pattern2)
2914 PQExpBufferData buf;
2916 printQueryOpt myopt = pset.popt;
2918 initPQExpBuffer(&buf);
2920 if (pset.sversion >= 90000)
2924 printfPQExpBuffer(&buf, "SELECT rolname AS \"%s\", datname AS \"%s\",\n"
2925 "pg_catalog.array_to_string(setconfig, E'\\n') AS \"%s\"\n"
2926 "FROM pg_db_role_setting AS s\n"
2927 "LEFT JOIN pg_database ON pg_database.oid = setdatabase\n"
2928 "LEFT JOIN pg_roles ON pg_roles.oid = setrole\n",
2929 gettext_noop("Role"),
2930 gettext_noop("Database"),
2931 gettext_noop("Settings"));
2932 havewhere = processSQLNamePattern(pset.db, &buf, pattern, false, false,
2933 NULL, "pg_roles.rolname", NULL, NULL);
2934 processSQLNamePattern(pset.db, &buf, pattern2, havewhere, false,
2935 NULL, "pg_database.datname", NULL, NULL);
2936 appendPQExpBufferStr(&buf, "ORDER BY 1, 2;");
2940 fprintf(pset.queryFout,
2941 _("No per-database role settings support in this server version.\n"));
2945 res = PSQLexec(buf.data);
2949 if (PQntuples(res) == 0 && !pset.quiet)
2952 fprintf(pset.queryFout, _("No matching settings found.\n"));
2954 fprintf(pset.queryFout, _("No settings found.\n"));
2958 myopt.nullPrint = NULL;
2959 myopt.title = _("List of settings");
2960 myopt.translate_header = true;
2962 printQuery(res, &myopt, pset.queryFout, false, pset.logfile);
2966 resetPQExpBuffer(&buf);
2974 * handler for \dt, \di, etc.
2976 * tabtypes is an array of characters, specifying what info is desired:
2980 * m - materialized views
2982 * E - foreign table (Note: different from 'f', the relkind value)
2983 * (any order of the above is fine)
2984 * If tabtypes is empty, we default to \dtvsE.
2987 listTables(const char *tabtypes, const char *pattern, bool verbose, bool showSystem)
2989 bool showTables = strchr(tabtypes, 't') != NULL;
2990 bool showIndexes = strchr(tabtypes, 'i') != NULL;
2991 bool showViews = strchr(tabtypes, 'v') != NULL;
2992 bool showMatViews = strchr(tabtypes, 'm') != NULL;
2993 bool showSeq = strchr(tabtypes, 's') != NULL;
2994 bool showForeign = strchr(tabtypes, 'E') != NULL;
2996 PQExpBufferData buf;
2998 printQueryOpt myopt = pset.popt;
2999 static const bool translate_columns[] = {false, false, true, false, false, false, false};
3001 if (!(showTables || showIndexes || showViews || showMatViews || showSeq || showForeign))
3002 showTables = showViews = showMatViews = showSeq = showForeign = true;
3004 initPQExpBuffer(&buf);
3007 * Note: as of Pg 8.2, we no longer use relkind 's', but we keep it here
3008 * for backwards compatibility.
3010 printfPQExpBuffer(&buf,
3011 "SELECT n.nspname as \"%s\",\n"
3012 " c.relname as \"%s\",\n"
3014 " WHEN 'r' THEN '%s'"
3015 " WHEN 'v' THEN '%s'"
3016 " WHEN 'm' THEN '%s'"
3017 " WHEN 'i' THEN '%s'"
3018 " WHEN 'S' THEN '%s'"
3019 " WHEN 's' THEN '%s'"
3020 " WHEN 'f' THEN '%s'"
3022 " pg_catalog.pg_get_userbyid(c.relowner) as \"%s\"",
3023 gettext_noop("Schema"),
3024 gettext_noop("Name"),
3025 gettext_noop("table"),
3026 gettext_noop("view"),
3027 gettext_noop("materialized view"),
3028 gettext_noop("index"),
3029 gettext_noop("sequence"),
3030 gettext_noop("special"),
3031 gettext_noop("foreign table"),
3032 gettext_noop("Type"),
3033 gettext_noop("Owner"));
3036 appendPQExpBuffer(&buf,
3037 ",\n c2.relname as \"%s\"",
3038 gettext_noop("Table"));
3043 * As of PostgreSQL 9.0, use pg_table_size() to show a more acurate
3044 * size of a table, including FSM, VM and TOAST tables.
3046 if (pset.sversion >= 90000)
3047 appendPQExpBuffer(&buf,
3048 ",\n pg_catalog.pg_size_pretty(pg_catalog.pg_table_size(c.oid)) as \"%s\"",
3049 gettext_noop("Size"));
3050 else if (pset.sversion >= 80100)
3051 appendPQExpBuffer(&buf,
3052 ",\n pg_catalog.pg_size_pretty(pg_catalog.pg_relation_size(c.oid)) as \"%s\"",
3053 gettext_noop("Size"));
3055 appendPQExpBuffer(&buf,
3056 ",\n pg_catalog.obj_description(c.oid, 'pg_class') as \"%s\"",
3057 gettext_noop("Description"));
3060 appendPQExpBufferStr(&buf,
3061 "\nFROM pg_catalog.pg_class c"
3062 "\n LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace");
3064 appendPQExpBufferStr(&buf,
3065 "\n LEFT JOIN pg_catalog.pg_index i ON i.indexrelid = c.oid"
3066 "\n LEFT JOIN pg_catalog.pg_class c2 ON i.indrelid = c2.oid");
3068 appendPQExpBufferStr(&buf, "\nWHERE c.relkind IN (");
3070 appendPQExpBufferStr(&buf, "'r',");
3072 appendPQExpBufferStr(&buf, "'v',");
3074 appendPQExpBufferStr(&buf, "'m',");
3076 appendPQExpBufferStr(&buf, "'i',");
3078 appendPQExpBufferStr(&buf, "'S',");
3079 if (showSystem || pattern)
3080 appendPQExpBufferStr(&buf, "'s',"); /* was RELKIND_SPECIAL in <=
3083 appendPQExpBufferStr(&buf, "'f',");
3085 appendPQExpBufferStr(&buf, "''"); /* dummy */
3086 appendPQExpBufferStr(&buf, ")\n");
3088 if (!showSystem && !pattern)
3089 appendPQExpBufferStr(&buf, " AND n.nspname <> 'pg_catalog'\n"
3090 " AND n.nspname <> 'information_schema'\n");
3093 * TOAST objects are suppressed unconditionally. Since we don't provide
3094 * any way to select relkind 't' above, we would never show toast tables
3095 * in any case; it seems a bit confusing to allow their indexes to be
3096 * shown. Use plain \d if you really need to look at a TOAST table/index.
3098 appendPQExpBufferStr(&buf, " AND n.nspname !~ '^pg_toast'\n");
3100 processSQLNamePattern(pset.db, &buf, pattern, true, false,
3101 "n.nspname", "c.relname", NULL,
3102 "pg_catalog.pg_table_is_visible(c.oid)");
3104 appendPQExpBufferStr(&buf, "ORDER BY 1,2;");
3106 res = PSQLexec(buf.data);
3107 termPQExpBuffer(&buf);
3111 if (PQntuples(res) == 0 && !pset.quiet)
3114 fprintf(pset.queryFout, _("No matching relations found.\n"));
3116 fprintf(pset.queryFout, _("No relations found.\n"));
3120 myopt.nullPrint = NULL;
3121 myopt.title = _("List of relations");
3122 myopt.translate_header = true;
3123 myopt.translate_columns = translate_columns;
3124 myopt.n_translate_columns = lengthof(translate_columns);
3126 printQuery(res, &myopt, pset.queryFout, false, pset.logfile);
3137 * Describes languages.
3140 listLanguages(const char *pattern, bool verbose, bool showSystem)
3142 PQExpBufferData buf;
3144 printQueryOpt myopt = pset.popt;
3146 initPQExpBuffer(&buf);
3148 printfPQExpBuffer(&buf,
3149 "SELECT l.lanname AS \"%s\",\n",
3150 gettext_noop("Name"));
3151 if (pset.sversion >= 80300)
3152 appendPQExpBuffer(&buf,
3153 " pg_catalog.pg_get_userbyid(l.lanowner) as \"%s\",\n",
3154 gettext_noop("Owner"));
3156 appendPQExpBuffer(&buf,
3157 " l.lanpltrusted AS \"%s\"",
3158 gettext_noop("Trusted"));
3162 appendPQExpBuffer(&buf,
3163 ",\n NOT l.lanispl AS \"%s\",\n"
3164 " l.lanplcallfoid::regprocedure AS \"%s\",\n"
3165 " l.lanvalidator::regprocedure AS \"%s\",\n ",
3166 gettext_noop("Internal Language"),
3167 gettext_noop("Call Handler"),
3168 gettext_noop("Validator"));
3169 if (pset.sversion >= 90000)
3170 appendPQExpBuffer(&buf, "l.laninline::regprocedure AS \"%s\",\n ",
3171 gettext_noop("Inline Handler"));
3172 printACLColumn(&buf, "l.lanacl");
3175 appendPQExpBuffer(&buf,
3176 ",\n d.description AS \"%s\""
3177 "\nFROM pg_catalog.pg_language l\n"
3178 "LEFT JOIN pg_catalog.pg_description d\n"
3179 " ON d.classoid = l.tableoid AND d.objoid = l.oid\n"
3180 " AND d.objsubid = 0\n",
3181 gettext_noop("Description"));
3184 processSQLNamePattern(pset.db, &buf, pattern, false, false,
3185 NULL, "l.lanname", NULL, NULL);
3187 if (!showSystem && !pattern)
3188 appendPQExpBufferStr(&buf, "WHERE l.lanplcallfoid != 0\n");
3191 appendPQExpBufferStr(&buf, "ORDER BY 1;");
3193 res = PSQLexec(buf.data);
3194 termPQExpBuffer(&buf);
3198 myopt.nullPrint = NULL;
3199 myopt.title = _("List of languages");
3200 myopt.translate_header = true;
3202 printQuery(res, &myopt, pset.queryFout, false, pset.logfile);
3212 * Describes domains.
3215 listDomains(const char *pattern, bool verbose, bool showSystem)
3217 PQExpBufferData buf;
3219 printQueryOpt myopt = pset.popt;
3221 initPQExpBuffer(&buf);
3223 printfPQExpBuffer(&buf,
3224 "SELECT n.nspname as \"%s\",\n"
3225 " t.typname as \"%s\",\n"
3226 " pg_catalog.format_type(t.typbasetype, t.typtypmod) as \"%s\",\n"
3228 gettext_noop("Schema"),
3229 gettext_noop("Name"),
3230 gettext_noop("Type"));
3232 if (pset.sversion >= 90100)
3233 appendPQExpBufferStr(&buf,
3234 " COALESCE((SELECT ' collate ' || c.collname FROM pg_catalog.pg_collation c, pg_catalog.pg_type bt\n"
3235 " WHERE c.oid = t.typcollation AND bt.oid = t.typbasetype AND t.typcollation <> bt.typcollation), '') ||\n");
3236 appendPQExpBuffer(&buf,
3237 " CASE WHEN t.typnotnull THEN ' not null' ELSE '' END ||\n"
3238 " CASE WHEN t.typdefault IS NOT NULL THEN ' default ' || t.typdefault ELSE '' END\n"
3240 " pg_catalog.array_to_string(ARRAY(\n"
3241 " SELECT pg_catalog.pg_get_constraintdef(r.oid, true) FROM pg_catalog.pg_constraint r WHERE t.oid = r.contypid\n"
3242 " ), ' ') as \"%s\"",
3243 gettext_noop("Modifier"),
3244 gettext_noop("Check"));
3248 if (pset.sversion >= 90200)
3250 appendPQExpBufferStr(&buf, ",\n ");
3251 printACLColumn(&buf, "t.typacl");
3253 appendPQExpBuffer(&buf,
3254 ",\n d.description as \"%s\"",
3255 gettext_noop("Description"));
3258 appendPQExpBufferStr(&buf,
3259 "\nFROM pg_catalog.pg_type t\n"
3260 " LEFT JOIN pg_catalog.pg_namespace n ON n.oid = t.typnamespace\n");
3263 appendPQExpBufferStr(&buf,
3264 " LEFT JOIN pg_catalog.pg_description d "
3265 "ON d.classoid = t.tableoid AND d.objoid = t.oid "
3266 "AND d.objsubid = 0\n");
3268 appendPQExpBufferStr(&buf, "WHERE t.typtype = 'd'\n");
3270 if (!showSystem && !pattern)
3271 appendPQExpBufferStr(&buf, " AND n.nspname <> 'pg_catalog'\n"
3272 " AND n.nspname <> 'information_schema'\n");
3274 processSQLNamePattern(pset.db, &buf, pattern, true, false,
3275 "n.nspname", "t.typname", NULL,
3276 "pg_catalog.pg_type_is_visible(t.oid)");
3278 appendPQExpBufferStr(&buf, "ORDER BY 1, 2;");
3280 res = PSQLexec(buf.data);
3281 termPQExpBuffer(&buf);
3285 myopt.nullPrint = NULL;
3286 myopt.title = _("List of domains");
3287 myopt.translate_header = true;
3289 printQuery(res, &myopt, pset.queryFout, false, pset.logfile);
3298 * Describes conversions.
3301 listConversions(const char *pattern, bool verbose, bool showSystem)
3303 PQExpBufferData buf;
3305 printQueryOpt myopt = pset.popt;
3306 static const bool translate_columns[] =
3307 {false, false, false, false, true, false};
3309 initPQExpBuffer(&buf);
3311 printfPQExpBuffer(&buf,
3312 "SELECT n.nspname AS \"%s\",\n"
3313 " c.conname AS \"%s\",\n"
3314 " pg_catalog.pg_encoding_to_char(c.conforencoding) AS \"%s\",\n"
3315 " pg_catalog.pg_encoding_to_char(c.contoencoding) AS \"%s\",\n"
3316 " CASE WHEN c.condefault THEN '%s'\n"
3317 " ELSE '%s' END AS \"%s\"",
3318 gettext_noop("Schema"),
3319 gettext_noop("Name"),
3320 gettext_noop("Source"),
3321 gettext_noop("Destination"),
3322 gettext_noop("yes"), gettext_noop("no"),
3323 gettext_noop("Default?"));
3326 appendPQExpBuffer(&buf,
3327 ",\n d.description AS \"%s\"",
3328 gettext_noop("Description"));
3330 appendPQExpBufferStr(&buf,
3331 "\nFROM pg_catalog.pg_conversion c\n"
3332 " JOIN pg_catalog.pg_namespace n "
3333 "ON n.oid = c.connamespace\n");
3336 appendPQExpBufferStr(&buf,
3337 "LEFT JOIN pg_catalog.pg_description d "
3338 "ON d.classoid = c.tableoid\n"
3339 " AND d.objoid = c.oid "
3340 "AND d.objsubid = 0\n");
3342 appendPQExpBufferStr(&buf, "WHERE true\n");
3344 if (!showSystem && !pattern)
3345 appendPQExpBufferStr(&buf, " AND n.nspname <> 'pg_catalog'\n"
3346 " AND n.nspname <> 'information_schema'\n");
3348 processSQLNamePattern(pset.db, &buf, pattern, true, false,
3349 "n.nspname", "c.conname", NULL,
3350 "pg_catalog.pg_conversion_is_visible(c.oid)");
3352 appendPQExpBufferStr(&buf, "ORDER BY 1, 2;");
3354 res = PSQLexec(buf.data);
3355 termPQExpBuffer(&buf);
3359 myopt.nullPrint = NULL;
3360 myopt.title = _("List of conversions");
3361 myopt.translate_header = true;
3362 myopt.translate_columns = translate_columns;
3363 myopt.n_translate_columns = lengthof(translate_columns);
3365 printQuery(res, &myopt, pset.queryFout, false, pset.logfile);
3374 * Describes Event Triggers.
3377 listEventTriggers(const char *pattern, bool verbose)
3379 PQExpBufferData buf;
3381 printQueryOpt myopt = pset.popt;
3382 static const bool translate_columns[] =
3383 {false, false, false, true, false, false, false};
3385 initPQExpBuffer(&buf);
3387 printfPQExpBuffer(&buf,
3388 "SELECT evtname as \"%s\", "
3389 "evtevent as \"%s\", "
3390 "pg_catalog.pg_get_userbyid(e.evtowner) as \"%s\",\n"
3391 " case evtenabled when 'O' then '%s'"
3392 " when 'R' then '%s'"
3393 " when 'A' then '%s'"
3394 " when 'D' then '%s' end as \"%s\",\n"
3395 " e.evtfoid::pg_catalog.regproc as \"%s\", "
3396 "pg_catalog.array_to_string(array(select x"
3397 " from pg_catalog.unnest(evttags) as t(x)), ', ') as \"%s\"",
3398 gettext_noop("Name"),
3399 gettext_noop("Event"),
3400 gettext_noop("Owner"),
3401 gettext_noop("enabled"),
3402 gettext_noop("replica"),
3403 gettext_noop("always"),
3404 gettext_noop("disabled"),
3405 gettext_noop("Enabled"),
3406 gettext_noop("Procedure"),
3407 gettext_noop("Tags"));
3409 appendPQExpBuffer(&buf,
3410 ",\npg_catalog.obj_description(e.oid, 'pg_event_trigger') as \"%s\"",
3411 gettext_noop("Description"));
3412 appendPQExpBufferStr(&buf,
3413 "\nFROM pg_catalog.pg_event_trigger e ");
3415 processSQLNamePattern(pset.db, &buf, pattern, false, false,
3416 NULL, "evtname", NULL, NULL);
3418 appendPQExpBufferStr(&buf, "ORDER BY 1");
3420 res = PSQLexec(buf.data);
3421 termPQExpBuffer(&buf);
3425 myopt.nullPrint = NULL;
3426 myopt.title = _("List of event triggers");
3427 myopt.translate_header = true;
3428 myopt.translate_columns = translate_columns;
3429 myopt.n_translate_columns = lengthof(translate_columns);
3431 printQuery(res, &myopt, pset.queryFout, false, pset.logfile);
3443 listCasts(const char *pattern, bool verbose)
3445 PQExpBufferData buf;
3447 printQueryOpt myopt = pset.popt;
3448 static const bool translate_columns[] = {false, false, false, true, false};
3450 initPQExpBuffer(&buf);
3453 * We need a left join to pg_proc for binary casts; the others are just
3454 * paranoia. Also note that we don't attempt to localize '(binary
3455 * coercible)', because there's too much risk of gettext translating a
3456 * function name that happens to match some string in the PO database.
3458 printfPQExpBuffer(&buf,
3459 "SELECT pg_catalog.format_type(castsource, NULL) AS \"%s\",\n"
3460 " pg_catalog.format_type(casttarget, NULL) AS \"%s\",\n"
3461 " CASE WHEN castfunc = 0 THEN '(binary coercible)'\n"
3464 " CASE WHEN c.castcontext = 'e' THEN '%s'\n"
3465 " WHEN c.castcontext = 'a' THEN '%s'\n"
3468 gettext_noop("Source type"),
3469 gettext_noop("Target type"),
3470 gettext_noop("Function"),
3472 gettext_noop("in assignment"),
3473 gettext_noop("yes"),
3474 gettext_noop("Implicit?"));
3477 appendPQExpBuffer(&buf,
3478 ",\n d.description AS \"%s\"\n",
3479 gettext_noop("Description"));
3481 appendPQExpBufferStr(&buf,
3482 "FROM pg_catalog.pg_cast c LEFT JOIN pg_catalog.pg_proc p\n"
3483 " ON c.castfunc = p.oid\n"
3484 " LEFT JOIN pg_catalog.pg_type ts\n"
3485 " ON c.castsource = ts.oid\n"
3486 " LEFT JOIN pg_catalog.pg_namespace ns\n"
3487 " ON ns.oid = ts.typnamespace\n"
3488 " LEFT JOIN pg_catalog.pg_type tt\n"
3489 " ON c.casttarget = tt.oid\n"
3490 " LEFT JOIN pg_catalog.pg_namespace nt\n"
3491 " ON nt.oid = tt.typnamespace\n");
3494 appendPQExpBufferStr(&buf,
3495 " LEFT JOIN pg_catalog.pg_description d\n"
3496 " ON d.classoid = c.tableoid AND d.objoid = "
3497 "c.oid AND d.objsubid = 0\n");
3499 appendPQExpBufferStr(&buf, "WHERE ( (true");
3502 * Match name pattern against either internal or external name of either
3503 * castsource or casttarget
3505 processSQLNamePattern(pset.db, &buf, pattern, true, false,
3506 "ns.nspname", "ts.typname",
3507 "pg_catalog.format_type(ts.oid, NULL)",
3508 "pg_catalog.pg_type_is_visible(ts.oid)");
3510 appendPQExpBufferStr(&buf, ") OR (true");
3512 processSQLNamePattern(pset.db, &buf, pattern, true, false,
3513 "nt.nspname", "tt.typname",
3514 "pg_catalog.format_type(tt.oid, NULL)",
3515 "pg_catalog.pg_type_is_visible(tt.oid)");
3517 appendPQExpBufferStr(&buf, ") )\nORDER BY 1, 2;");
3519 res = PSQLexec(buf.data);
3520 termPQExpBuffer(&buf);
3524 myopt.nullPrint = NULL;
3525 myopt.title = _("List of casts");
3526 myopt.translate_header = true;
3527 myopt.translate_columns = translate_columns;
3528 myopt.n_translate_columns = lengthof(translate_columns);
3530 printQuery(res, &myopt, pset.queryFout, false, pset.logfile);
3539 * Describes collations.
3542 listCollations(const char *pattern, bool verbose, bool showSystem)
3544 PQExpBufferData buf;
3546 printQueryOpt myopt = pset.popt;
3547 static const bool translate_columns[] = {false, false, false, false, false};
3549 if (pset.sversion < 90100)
3551 psql_error("The server (version %d.%d) does not support collations.\n",
3552 pset.sversion / 10000, (pset.sversion / 100) % 100);
3556 initPQExpBuffer(&buf);
3558 printfPQExpBuffer(&buf,
3559 "SELECT n.nspname AS \"%s\",\n"
3560 " c.collname AS \"%s\",\n"
3561 " c.collcollate AS \"%s\",\n"
3562 " c.collctype AS \"%s\"",
3563 gettext_noop("Schema"),
3564 gettext_noop("Name"),
3565 gettext_noop("Collate"),
3566 gettext_noop("Ctype"));
3569 appendPQExpBuffer(&buf,
3570 ",\n pg_catalog.obj_description(c.oid, 'pg_collation') AS \"%s\"",
3571 gettext_noop("Description"));
3573 appendPQExpBufferStr(&buf,
3574 "\nFROM pg_catalog.pg_collation c, pg_catalog.pg_namespace n\n"
3575 "WHERE n.oid = c.collnamespace\n");
3577 if (!showSystem && !pattern)
3578 appendPQExpBufferStr(&buf, " AND n.nspname <> 'pg_catalog'\n"
3579 " AND n.nspname <> 'information_schema'\n");
3582 * Hide collations that aren't usable in the current database's encoding.
3583 * If you think to change this, note that pg_collation_is_visible rejects
3584 * unusable collations, so you will need to hack name pattern processing
3585 * somehow to avoid inconsistent behavior.
3587 appendPQExpBufferStr(&buf, " AND c.collencoding IN (-1, pg_catalog.pg_char_to_encoding(pg_catalog.getdatabaseencoding()))\n");
3589 processSQLNamePattern(pset.db, &buf, pattern, true, false,
3590 "n.nspname", "c.collname", NULL,
3591 "pg_catalog.pg_collation_is_visible(c.oid)");
3593 appendPQExpBufferStr(&buf, "ORDER BY 1, 2;");
3595 res = PSQLexec(buf.data);
3596 termPQExpBuffer(&buf);
3600 myopt.nullPrint = NULL;
3601 myopt.title = _("List of collations");
3602 myopt.translate_header = true;
3603 myopt.translate_columns = translate_columns;
3604 myopt.n_translate_columns = lengthof(translate_columns);
3606 printQuery(res, &myopt, pset.queryFout, false, pset.logfile);
3615 * Describes schemas (namespaces)
3618 listSchemas(const char *pattern, bool verbose, bool showSystem)
3620 PQExpBufferData buf;
3622 printQueryOpt myopt = pset.popt;
3624 initPQExpBuffer(&buf);
3625 printfPQExpBuffer(&buf,
3626 "SELECT n.nspname AS \"%s\",\n"
3627 " pg_catalog.pg_get_userbyid(n.nspowner) AS \"%s\"",
3628 gettext_noop("Name"),
3629 gettext_noop("Owner"));
3633 appendPQExpBufferStr(&buf, ",\n ");
3634 printACLColumn(&buf, "n.nspacl");
3635 appendPQExpBuffer(&buf,
3636 ",\n pg_catalog.obj_description(n.oid, 'pg_namespace') AS \"%s\"",
3637 gettext_noop("Description"));
3640 appendPQExpBuffer(&buf,
3641 "\nFROM pg_catalog.pg_namespace n\n");
3643 if (!showSystem && !pattern)
3644 appendPQExpBufferStr(&buf,
3645 "WHERE n.nspname !~ '^pg_' AND n.nspname <> 'information_schema'\n");
3647 processSQLNamePattern(pset.db, &buf, pattern,
3648 !showSystem && !pattern, false,
3649 NULL, "n.nspname", NULL,
3652 appendPQExpBufferStr(&buf, "ORDER BY 1;");
3654 res = PSQLexec(buf.data);
3655 termPQExpBuffer(&buf);
3659 myopt.nullPrint = NULL;
3660 myopt.title = _("List of schemas");
3661 myopt.translate_header = true;
3663 printQuery(res, &myopt, pset.queryFout, false, pset.logfile);
3672 * list text search parsers
3675 listTSParsers(const char *pattern, bool verbose)
3677 PQExpBufferData buf;
3679 printQueryOpt myopt = pset.popt;
3681 if (pset.sversion < 80300)
3683 psql_error("The server (version %d.%d) does not support full text search.\n",
3684 pset.sversion / 10000, (pset.sversion / 100) % 100);
3689 return listTSParsersVerbose(pattern);
3691 initPQExpBuffer(&buf);
3693 printfPQExpBuffer(&buf,
3695 " n.nspname as \"%s\",\n"
3696 " p.prsname as \"%s\",\n"
3697 " pg_catalog.obj_description(p.oid, 'pg_ts_parser') as \"%s\"\n"
3698 "FROM pg_catalog.pg_ts_parser p \n"
3699 "LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.prsnamespace\n",
3700 gettext_noop("Schema"),
3701 gettext_noop("Name"),
3702 gettext_noop("Description")
3705 processSQLNamePattern(pset.db, &buf, pattern, false, false,
3706 "n.nspname", "p.prsname", NULL,
3707 "pg_catalog.pg_ts_parser_is_visible(p.oid)");
3709 appendPQExpBufferStr(&buf, "ORDER BY 1, 2;");
3711 res = PSQLexec(buf.data);
3712 termPQExpBuffer(&buf);
3716 myopt.nullPrint = NULL;
3717 myopt.title = _("List of text search parsers");
3718 myopt.translate_header = true;
3720 printQuery(res, &myopt, pset.queryFout, false, pset.logfile);
3727 * full description of parsers
3730 listTSParsersVerbose(const char *pattern)
3732 PQExpBufferData buf;
3736 initPQExpBuffer(&buf);
3738 printfPQExpBuffer(&buf,
3742 "FROM pg_catalog.pg_ts_parser p\n"
3743 "LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.prsnamespace\n"
3746 processSQLNamePattern(pset.db, &buf, pattern, false, false,
3747 "n.nspname", "p.prsname", NULL,
3748 "pg_catalog.pg_ts_parser_is_visible(p.oid)");
3750 appendPQExpBufferStr(&buf, "ORDER BY 1, 2;");
3752 res = PSQLexec(buf.data);
3753 termPQExpBuffer(&buf);
3757 if (PQntuples(res) == 0)
3760 psql_error("Did not find any text search parser named \"%s\".\n",
3766 for (i = 0; i < PQntuples(res); i++)
3769 const char *nspname = NULL;
3770 const char *prsname;
3772 oid = PQgetvalue(res, i, 0);
3773 if (!PQgetisnull(res, i, 1))
3774 nspname = PQgetvalue(res, i, 1);
3775 prsname = PQgetvalue(res, i, 2);
3777 if (!describeOneTSParser(oid, nspname, prsname))
3795 describeOneTSParser(const char *oid, const char *nspname, const char *prsname)
3797 PQExpBufferData buf;
3800 printQueryOpt myopt = pset.popt;
3801 static const bool translate_columns[] = {true, false, false};
3803 initPQExpBuffer(&buf);
3805 printfPQExpBuffer(&buf,
3806 "SELECT '%s' AS \"%s\", \n"
3807 " p.prsstart::pg_catalog.regproc AS \"%s\", \n"
3808 " pg_catalog.obj_description(p.prsstart, 'pg_proc') as \"%s\" \n"
3809 " FROM pg_catalog.pg_ts_parser p \n"
3810 " WHERE p.oid = '%s' \n"
3813 " p.prstoken::pg_catalog.regproc, \n"
3814 " pg_catalog.obj_description(p.prstoken, 'pg_proc') \n"
3815 " FROM pg_catalog.pg_ts_parser p \n"
3816 " WHERE p.oid = '%s' \n"
3819 " p.prsend::pg_catalog.regproc, \n"
3820 " pg_catalog.obj_description(p.prsend, 'pg_proc') \n"
3821 " FROM pg_catalog.pg_ts_parser p \n"
3822 " WHERE p.oid = '%s' \n"
3825 " p.prsheadline::pg_catalog.regproc, \n"
3826 " pg_catalog.obj_description(p.prsheadline, 'pg_proc') \n"
3827 " FROM pg_catalog.pg_ts_parser p \n"
3828 " WHERE p.oid = '%s' \n"
3831 " p.prslextype::pg_catalog.regproc, \n"
3832 " pg_catalog.obj_description(p.prslextype, 'pg_proc') \n"
3833 " FROM pg_catalog.pg_ts_parser p \n"
3834 " WHERE p.oid = '%s';",
3835 gettext_noop("Start parse"),
3836 gettext_noop("Method"),
3837 gettext_noop("Function"),
3838 gettext_noop("Description"),
3840 gettext_noop("Get next token"),
3842 gettext_noop("End parse"),
3844 gettext_noop("Get headline"),
3846 gettext_noop("Get token types"),
3849 res = PSQLexec(buf.data);
3850 termPQExpBuffer(&buf);
3854 myopt.nullPrint = NULL;
3856 sprintf(title, _("Text search parser \"%s.%s\""), nspname, prsname);
3858 sprintf(title, _("Text search parser \"%s\""), prsname);
3859 myopt.title = title;
3860 myopt.footers = NULL;
3861 myopt.topt.default_footer = false;
3862 myopt.translate_header = true;
3863 myopt.translate_columns = translate_columns;
3864 myopt.n_translate_columns = lengthof(translate_columns);
3866 printQuery(res, &myopt, pset.queryFout, false, pset.logfile);
3870 initPQExpBuffer(&buf);
3872 printfPQExpBuffer(&buf,
3873 "SELECT t.alias as \"%s\", \n"
3874 " t.description as \"%s\" \n"
3875 "FROM pg_catalog.ts_token_type( '%s'::pg_catalog.oid ) as t \n"
3877 gettext_noop("Token name"),
3878 gettext_noop("Description"),
3881 res = PSQLexec(buf.data);
3882 termPQExpBuffer(&buf);
3886 myopt.nullPrint = NULL;
3888 sprintf(title, _("Token types for parser \"%s.%s\""), nspname, prsname);
3890 sprintf(title, _("Token types for parser \"%s\""), prsname);
3891 myopt.title = title;
3892 myopt.footers = NULL;
3893 myopt.topt.default_footer = true;
3894 myopt.translate_header = true;
3895 myopt.translate_columns = NULL;
3896 myopt.n_translate_columns = 0;
3898 printQuery(res, &myopt, pset.queryFout, false, pset.logfile);
3907 * list text search dictionaries
3910 listTSDictionaries(const char *pattern, bool verbose)
3912 PQExpBufferData buf;
3914 printQueryOpt myopt = pset.popt;
3916 if (pset.sversion < 80300)
3918 psql_error("The server (version %d.%d) does not support full text search.\n",
3919 pset.sversion / 10000, (pset.sversion / 100) % 100);
3923 initPQExpBuffer(&buf);
3925 printfPQExpBuffer(&buf,
3927 " n.nspname as \"%s\",\n"
3928 " d.dictname as \"%s\",\n",
3929 gettext_noop("Schema"),
3930 gettext_noop("Name"));
3934 appendPQExpBuffer(&buf,
3935 " ( SELECT COALESCE(nt.nspname, '(null)')::pg_catalog.text || '.' || t.tmplname FROM \n"
3936 " pg_catalog.pg_ts_template t \n"
3937 " LEFT JOIN pg_catalog.pg_namespace nt ON nt.oid = t.tmplnamespace \n"
3938 " WHERE d.dicttemplate = t.oid ) AS \"%s\", \n"
3939 " d.dictinitoption as \"%s\", \n",
3940 gettext_noop("Template"),
3941 gettext_noop("Init options"));
3944 appendPQExpBuffer(&buf,
3945 " pg_catalog.obj_description(d.oid, 'pg_ts_dict') as \"%s\"\n",
3946 gettext_noop("Description"));
3948 appendPQExpBufferStr(&buf, "FROM pg_catalog.pg_ts_dict d\n"
3949 "LEFT JOIN pg_catalog.pg_namespace n ON n.oid = d.dictnamespace\n");
3951 processSQLNamePattern(pset.db, &buf, pattern, false, false,
3952 "n.nspname", "d.dictname", NULL,
3953 "pg_catalog.pg_ts_dict_is_visible(d.oid)");
3955 appendPQExpBufferStr(&buf, "ORDER BY 1, 2;");
3957 res = PSQLexec(buf.data);
3958 termPQExpBuffer(&buf);
3962 myopt.nullPrint = NULL;
3963 myopt.title = _("List of text search dictionaries");
3964 myopt.translate_header = true;
3966 printQuery(res, &myopt, pset.queryFout, false, pset.logfile);
3975 * list text search templates
3978 listTSTemplates(const char *pattern, bool verbose)
3980 PQExpBufferData buf;
3982 printQueryOpt myopt = pset.popt;
3984 if (pset.sversion < 80300)
3986 psql_error("The server (version %d.%d) does not support full text search.\n",
3987 pset.sversion / 10000, (pset.sversion / 100) % 100);
3991 initPQExpBuffer(&buf);
3994 printfPQExpBuffer(&buf,
3996 " n.nspname AS \"%s\",\n"
3997 " t.tmplname AS \"%s\",\n"
3998 " t.tmplinit::pg_catalog.regproc AS \"%s\",\n"
3999 " t.tmpllexize::pg_catalog.regproc AS \"%s\",\n"
4000 " pg_catalog.obj_description(t.oid, 'pg_ts_template') AS \"%s\"\n",
4001 gettext_noop("Schema"),
4002 gettext_noop("Name"),
4003 gettext_noop("Init"),
4004 gettext_noop("Lexize"),
4005 gettext_noop("Description"));
4007 printfPQExpBuffer(&buf,
4009 " n.nspname AS \"%s\",\n"
4010 " t.tmplname AS \"%s\",\n"
4011 " pg_catalog.obj_description(t.oid, 'pg_ts_template') AS \"%s\"\n",
4012 gettext_noop("Schema"),
4013 gettext_noop("Name"),
4014 gettext_noop("Description"));
4016 appendPQExpBufferStr(&buf, "FROM pg_catalog.pg_ts_template t\n"
4017 "LEFT JOIN pg_catalog.pg_namespace n ON n.oid = t.tmplnamespace\n");
4019 processSQLNamePattern(pset.db, &buf, pattern, false, false,
4020 "n.nspname", "t.tmplname", NULL,
4021 "pg_catalog.pg_ts_template_is_visible(t.oid)");
4023 appendPQExpBufferStr(&buf, "ORDER BY 1, 2;");
4025 res = PSQLexec(buf.data);
4026 termPQExpBuffer(&buf);
4030 myopt.nullPrint = NULL;
4031 myopt.title = _("List of text search templates");
4032 myopt.translate_header = true;
4034 printQuery(res, &myopt, pset.queryFout, false, pset.logfile);
4043 * list text search configurations
4046 listTSConfigs(const char *pattern, bool verbose)
4048 PQExpBufferData buf;
4050 printQueryOpt myopt = pset.popt;
4052 if (pset.sversion < 80300)
4054 psql_error("The server (version %d.%d) does not support full text search.\n",
4055 pset.sversion / 10000, (pset.sversion / 100) % 100);
4060 return listTSConfigsVerbose(pattern);
4062 initPQExpBuffer(&buf);
4064 printfPQExpBuffer(&buf,
4066 " n.nspname as \"%s\",\n"
4067 " c.cfgname as \"%s\",\n"
4068 " pg_catalog.obj_description(c.oid, 'pg_ts_config') as \"%s\"\n"
4069 "FROM pg_catalog.pg_ts_config c\n"
4070 "LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.cfgnamespace \n",
4071 gettext_noop("Schema"),
4072 gettext_noop("Name"),
4073 gettext_noop("Description")
4076 processSQLNamePattern(pset.db, &buf, pattern, false, false,
4077 "n.nspname", "c.cfgname", NULL,
4078 "pg_catalog.pg_ts_config_is_visible(c.oid)");
4080 appendPQExpBufferStr(&buf, "ORDER BY 1, 2;");
4082 res = PSQLexec(buf.data);
4083 termPQExpBuffer(&buf);
4087 myopt.nullPrint = NULL;
4088 myopt.title = _("List of text search configurations");
4089 myopt.translate_header = true;
4091 printQuery(res, &myopt, pset.queryFout, false, pset.logfile);
4098 listTSConfigsVerbose(const char *pattern)
4100 PQExpBufferData buf;
4104 initPQExpBuffer(&buf);
4106 printfPQExpBuffer(&buf,
4107 "SELECT c.oid, c.cfgname,\n"
4110 " np.nspname as pnspname \n"
4111 "FROM pg_catalog.pg_ts_config c \n"
4112 " LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.cfgnamespace, \n"
4113 " pg_catalog.pg_ts_parser p \n"
4114 " LEFT JOIN pg_catalog.pg_namespace np ON np.oid = p.prsnamespace \n"
4115 "WHERE p.oid = c.cfgparser\n"
4118 processSQLNamePattern(pset.db, &buf, pattern, true, false,
4119 "n.nspname", "c.cfgname", NULL,
4120 "pg_catalog.pg_ts_config_is_visible(c.oid)");
4122 appendPQExpBufferStr(&buf, "ORDER BY 3, 2;");
4124 res = PSQLexec(buf.data);
4125 termPQExpBuffer(&buf);
4129 if (PQntuples(res) == 0)
4132 psql_error("Did not find any text search configuration named \"%s\".\n",
4138 for (i = 0; i < PQntuples(res); i++)
4141 const char *cfgname;
4142 const char *nspname = NULL;
4143 const char *prsname;
4144 const char *pnspname = NULL;
4146 oid = PQgetvalue(res, i, 0);
4147 cfgname = PQgetvalue(res, i, 1);
4148 if (!PQgetisnull(res, i, 2))
4149 nspname = PQgetvalue(res, i, 2);
4150 prsname = PQgetvalue(res, i, 3);
4151 if (!PQgetisnull(res, i, 4))
4152 pnspname = PQgetvalue(res, i, 4);
4154 if (!describeOneTSConfig(oid, nspname, cfgname, pnspname, prsname))
4172 describeOneTSConfig(const char *oid, const char *nspname, const char *cfgname,
4173 const char *pnspname, const char *prsname)
4175 PQExpBufferData buf,
4178 printQueryOpt myopt = pset.popt;
4180 initPQExpBuffer(&buf);
4182 printfPQExpBuffer(&buf,
4184 " ( SELECT t.alias FROM \n"
4185 " pg_catalog.ts_token_type(c.cfgparser) AS t \n"
4186 " WHERE t.tokid = m.maptokentype ) AS \"%s\", \n"
4187 " pg_catalog.btrim( \n"
4188 " ARRAY( SELECT mm.mapdict::pg_catalog.regdictionary \n"
4189 " FROM pg_catalog.pg_ts_config_map AS mm \n"
4190 " WHERE mm.mapcfg = m.mapcfg AND mm.maptokentype = m.maptokentype \n"
4191 " ORDER BY mapcfg, maptokentype, mapseqno \n"
4192 " ) :: pg_catalog.text , \n"
4193 " '{}') AS \"%s\" \n"
4194 "FROM pg_catalog.pg_ts_config AS c, pg_catalog.pg_ts_config_map AS m \n"
4195 "WHERE c.oid = '%s' AND m.mapcfg = c.oid \n"
4196 "GROUP BY m.mapcfg, m.maptokentype, c.cfgparser \n"
4198 gettext_noop("Token"),
4199 gettext_noop("Dictionaries"),
4202 res = PSQLexec(buf.data);
4203 termPQExpBuffer(&buf);
4207 initPQExpBuffer(&title);
4210 appendPQExpBuffer(&title, _("Text search configuration \"%s.%s\""),
4213 appendPQExpBuffer(&title, _("Text search configuration \"%s\""),
4217 appendPQExpBuffer(&title, _("\nParser: \"%s.%s\""),
4220 appendPQExpBuffer(&title, _("\nParser: \"%s\""),
4223 myopt.nullPrint = NULL;
4224 myopt.title = title.data;
4225 myopt.footers = NULL;
4226 myopt.topt.default_footer = false;
4227 myopt.translate_header = true;
4229 printQuery(res, &myopt, pset.queryFout, false, pset.logfile);
4231 termPQExpBuffer(&title);
4241 * Describes foreign-data wrappers
4244 listForeignDataWrappers(const char *pattern, bool verbose)
4246 PQExpBufferData buf;
4248 printQueryOpt myopt = pset.popt;
4250 if (pset.sversion < 80400)
4252 psql_error("The server (version %d.%d) does not support foreign-data wrappers.\n",
4253 pset.sversion / 10000, (pset.sversion / 100) % 100);
4257 initPQExpBuffer(&buf);
4258 printfPQExpBuffer(&buf,
4259 "SELECT fdw.fdwname AS \"%s\",\n"
4260 " pg_catalog.pg_get_userbyid(fdw.fdwowner) AS \"%s\",\n",
4261 gettext_noop("Name"),
4262 gettext_noop("Owner"));
4263 if (pset.sversion >= 90100)
4264 appendPQExpBuffer(&buf,
4265 " fdw.fdwhandler::pg_catalog.regproc AS \"%s\",\n",
4266 gettext_noop("Handler"));
4267 appendPQExpBuffer(&buf,
4268 " fdw.fdwvalidator::pg_catalog.regproc AS \"%s\"",
4269 gettext_noop("Validator"));
4273 appendPQExpBufferStr(&buf, ",\n ");
4274 printACLColumn(&buf, "fdwacl");
4275 appendPQExpBuffer(&buf,
4276 ",\n CASE WHEN fdwoptions IS NULL THEN '' ELSE "
4277 " '(' || array_to_string(ARRAY(SELECT "
4278 " quote_ident(option_name) || ' ' || "
4279 " quote_literal(option_value) FROM "
4280 " pg_options_to_table(fdwoptions)), ', ') || ')' "
4282 gettext_noop("FDW Options"));
4284 if (pset.sversion >= 90100)
4285 appendPQExpBuffer(&buf,
4286 ",\n d.description AS \"%s\" ",
4287 gettext_noop("Description"));
4290 appendPQExpBufferStr(&buf, "\nFROM pg_catalog.pg_foreign_data_wrapper fdw\n");
4292 if (verbose && pset.sversion >= 90100)
4293 appendPQExpBufferStr(&buf,
4294 "LEFT JOIN pg_catalog.pg_description d\n"
4295 " ON d.classoid = fdw.tableoid "
4296 "AND d.objoid = fdw.oid AND d.objsubid = 0\n");
4298 processSQLNamePattern(pset.db, &buf, pattern, false, false,
4299 NULL, "fdwname", NULL, NULL);
4301 appendPQExpBufferStr(&buf, "ORDER BY 1;");
4303 res = PSQLexec(buf.data);
4304 termPQExpBuffer(&buf);
4308 myopt.nullPrint = NULL;
4309 myopt.title = _("List of foreign-data wrappers");
4310 myopt.translate_header = true;
4312 printQuery(res, &myopt, pset.queryFout, false, pset.logfile);
4321 * Describes foreign servers.
4324 listForeignServers(const char *pattern, bool verbose)
4326 PQExpBufferData buf;
4328 printQueryOpt myopt = pset.popt;
4330 if (pset.sversion < 80400)
4332 psql_error("The server (version %d.%d) does not support foreign servers.\n",
4333 pset.sversion / 10000, (pset.sversion / 100) % 100);
4337 initPQExpBuffer(&buf);
4338 printfPQExpBuffer(&buf,
4339 "SELECT s.srvname AS \"%s\",\n"
4340 " pg_catalog.pg_get_userbyid(s.srvowner) AS \"%s\",\n"
4341 " f.fdwname AS \"%s\"",
4342 gettext_noop("Name"),
4343 gettext_noop("Owner"),
4344 gettext_noop("Foreign-data wrapper"));
4348 appendPQExpBufferStr(&buf, ",\n ");
4349 printACLColumn(&buf, "s.srvacl");
4350 appendPQExpBuffer(&buf,
4352 " s.srvtype AS \"%s\",\n"
4353 " s.srvversion AS \"%s\",\n"
4354 " CASE WHEN srvoptions IS NULL THEN '' ELSE "
4355 " '(' || array_to_string(ARRAY(SELECT "
4356 " quote_ident(option_name) || ' ' || "
4357 " quote_literal(option_value) FROM "
4358 " pg_options_to_table(srvoptions)), ', ') || ')' "
4360 " d.description AS \"%s\"",
4361 gettext_noop("Type"),
4362 gettext_noop("Version"),
4363 gettext_noop("FDW Options"),
4364 gettext_noop("Description"));
4367 appendPQExpBufferStr(&buf,
4368 "\nFROM pg_catalog.pg_foreign_server s\n"
4369 " JOIN pg_catalog.pg_foreign_data_wrapper f ON f.oid=s.srvfdw\n");
4372 appendPQExpBufferStr(&buf,
4373 "LEFT JOIN pg_description d\n "
4374 "ON d.classoid = s.tableoid AND d.objoid = s.oid "
4375 "AND d.objsubid = 0\n");
4377 processSQLNamePattern(pset.db, &buf, pattern, false, false,
4378 NULL, "s.srvname", NULL, NULL);
4380 appendPQExpBufferStr(&buf, "ORDER BY 1;");
4382 res = PSQLexec(buf.data);
4383 termPQExpBuffer(&buf);
4387 myopt.nullPrint = NULL;
4388 myopt.title = _("List of foreign servers");
4389 myopt.translate_header = true;
4391 printQuery(res, &myopt, pset.queryFout, false, pset.logfile);
4400 * Describes user mappings.
4403 listUserMappings(const char *pattern, bool verbose)
4405 PQExpBufferData buf;
4407 printQueryOpt myopt = pset.popt;
4409 if (pset.sversion < 80400)
4411 psql_error("The server (version %d.%d) does not support user mappings.\n",
4412 pset.sversion / 10000, (pset.sversion / 100) % 100);
4416 initPQExpBuffer(&buf);
4417 printfPQExpBuffer(&buf,
4418 "SELECT um.srvname AS \"%s\",\n"
4419 " um.usename AS \"%s\"",
4420 gettext_noop("Server"),
4421 gettext_noop("User name"));
4424 appendPQExpBuffer(&buf,
4425 ",\n CASE WHEN umoptions IS NULL THEN '' ELSE "
4426 " '(' || array_to_string(ARRAY(SELECT "
4427 " quote_ident(option_name) || ' ' || "
4428 " quote_literal(option_value) FROM "
4429 " pg_options_to_table(umoptions)), ', ') || ')' "
4431 gettext_noop("FDW Options"));
4433 appendPQExpBufferStr(&buf, "\nFROM pg_catalog.pg_user_mappings um\n");
4435 processSQLNamePattern(pset.db, &buf, pattern, false, false,
4436 NULL, "um.srvname", "um.usename", NULL);
4438 appendPQExpBufferStr(&buf, "ORDER BY 1, 2;");
4440 res = PSQLexec(buf.data);
4441 termPQExpBuffer(&buf);
4445 myopt.nullPrint = NULL;
4446 myopt.title = _("List of user mappings");
4447 myopt.translate_header = true;
4449 printQuery(res, &myopt, pset.queryFout, false, pset.logfile);
4458 * Describes foreign tables.
4461 listForeignTables(const char *pattern, bool verbose)
4463 PQExpBufferData buf;
4465 printQueryOpt myopt = pset.popt;
4467 if (pset.sversion < 90100)
4469 psql_error("The server (version %d.%d) does not support foreign tables.\n",
4470 pset.sversion / 10000, (pset.sversion / 100) % 100);
4474 initPQExpBuffer(&buf);
4475 printfPQExpBuffer(&buf,
4476 "SELECT n.nspname AS \"%s\",\n"
4477 " c.relname AS \"%s\",\n"
4478 " s.srvname AS \"%s\"",
4479 gettext_noop("Schema"),
4480 gettext_noop("Table"),
4481 gettext_noop("Server"));
4484 appendPQExpBuffer(&buf,
4485 ",\n CASE WHEN ftoptions IS NULL THEN '' ELSE "
4486 " '(' || array_to_string(ARRAY(SELECT "
4487 " quote_ident(option_name) || ' ' || "
4488 " quote_literal(option_value) FROM "
4489 " pg_options_to_table(ftoptions)), ', ') || ')' "
4491 " d.description AS \"%s\"",
4492 gettext_noop("FDW Options"),
4493 gettext_noop("Description"));
4495 appendPQExpBufferStr(&buf,
4496 "\nFROM pg_catalog.pg_foreign_table ft\n"
4497 " INNER JOIN pg_catalog.pg_class c"
4498 " ON c.oid = ft.ftrelid\n"
4499 " INNER JOIN pg_catalog.pg_namespace n"
4500 " ON n.oid = c.relnamespace\n"
4501 " INNER JOIN pg_catalog.pg_foreign_server s"
4502 " ON s.oid = ft.ftserver\n");
4504 appendPQExpBufferStr(&buf,
4505 " LEFT JOIN pg_catalog.pg_description d\n"
4506 " ON d.classoid = c.tableoid AND "
4507 "d.objoid = c.oid AND d.objsubid = 0\n");
4509 processSQLNamePattern(pset.db, &buf, pattern, false, false,
4510 "n.nspname", "c.relname", NULL,
4511 "pg_catalog.pg_table_is_visible(c.oid)");
4513 appendPQExpBufferStr(&buf, "ORDER BY 1, 2;");
4515 res = PSQLexec(buf.data);
4516 termPQExpBuffer(&buf);
4520 myopt.nullPrint = NULL;
4521 myopt.title = _("List of foreign tables");
4522 myopt.translate_header = true;
4524 printQuery(res, &myopt, pset.queryFout, false, pset.logfile);
4533 * Briefly describes installed extensions.
4536 listExtensions(const char *pattern)
4538 PQExpBufferData buf;
4540 printQueryOpt myopt = pset.popt;
4542 if (pset.sversion < 90100)
4544 psql_error("The server (version %d.%d) does not support extensions.\n",
4545 pset.sversion / 10000, (pset.sversion / 100) % 100);
4549 initPQExpBuffer(&buf);
4550 printfPQExpBuffer(&buf,
4551 "SELECT e.extname AS \"%s\", "
4552 "e.extversion AS \"%s\", n.nspname AS \"%s\", c.description AS \"%s\"\n"
4553 "FROM pg_catalog.pg_extension e "
4554 "LEFT JOIN pg_catalog.pg_namespace n ON n.oid = e.extnamespace "
4555 "LEFT JOIN pg_catalog.pg_description c ON c.objoid = e.oid "
4556 "AND c.classoid = 'pg_catalog.pg_extension'::pg_catalog.regclass\n",
4557 gettext_noop("Name"),
4558 gettext_noop("Version"),
4559 gettext_noop("Schema"),
4560 gettext_noop("Description"));
4562 processSQLNamePattern(pset.db, &buf, pattern,
4564 NULL, "e.extname", NULL,
4567 appendPQExpBufferStr(&buf, "ORDER BY 1;");
4569 res = PSQLexec(buf.data);
4570 termPQExpBuffer(&buf);
4574 myopt.nullPrint = NULL;
4575 myopt.title = _("List of installed extensions");
4576 myopt.translate_header = true;
4578 printQuery(res, &myopt, pset.queryFout, false, pset.logfile);
4587 * List contents of installed extensions.
4590 listExtensionContents(const char *pattern)
4592 PQExpBufferData buf;
4596 if (pset.sversion < 90100)
4598 psql_error("The server (version %d.%d) does not support extensions.\n",
4599 pset.sversion / 10000, (pset.sversion / 100) % 100);
4603 initPQExpBuffer(&buf);
4604 printfPQExpBuffer(&buf,
4605 "SELECT e.extname, e.oid\n"
4606 "FROM pg_catalog.pg_extension e\n");
4608 processSQLNamePattern(pset.db, &buf, pattern,
4610 NULL, "e.extname", NULL,
4613 appendPQExpBufferStr(&buf, "ORDER BY 1;");
4615 res = PSQLexec(buf.data);
4616 termPQExpBuffer(&buf);
4620 if (PQntuples(res) == 0)
4625 psql_error("Did not find any extension named \"%s\".\n",
4628 psql_error("Did not find any extensions.\n");
4634 for (i = 0; i < PQntuples(res); i++)
4636 const char *extname;
4639 extname = PQgetvalue(res, i, 0);
4640 oid = PQgetvalue(res, i, 1);
4642 if (!listOneExtensionContents(extname, oid))
4659 listOneExtensionContents(const char *extname, const char *oid)
4661 PQExpBufferData buf;
4664 printQueryOpt myopt = pset.popt;
4666 initPQExpBuffer(&buf);
4667 printfPQExpBuffer(&buf,
4668 "SELECT pg_catalog.pg_describe_object(classid, objid, 0) AS \"%s\"\n"
4669 "FROM pg_catalog.pg_depend\n"
4670 "WHERE refclassid = 'pg_catalog.pg_extension'::pg_catalog.regclass AND refobjid = '%s' AND deptype = 'e'\n"
4672 gettext_noop("Object Description"),
4675 res = PSQLexec(buf.data);
4676 termPQExpBuffer(&buf);
4680 myopt.nullPrint = NULL;
4681 snprintf(title, sizeof(title), _("Objects in extension \"%s\""), extname);
4682 myopt.title = title;
4683 myopt.translate_header = true;
4685 printQuery(res, &myopt, pset.queryFout, false, pset.logfile);
4694 * Helper function for consistently formatting ACL (privilege) columns.
4695 * The proper targetlist entry is appended to buf. Note lack of any
4696 * whitespace or comma decoration.
4699 printACLColumn(PQExpBuffer buf, const char *colname)
4701 if (pset.sversion >= 80100)
4702 appendPQExpBuffer(buf,
4703 "pg_catalog.array_to_string(%s, E'\\n') AS \"%s\"",
4704 colname, gettext_noop("Access privileges"));
4706 appendPQExpBuffer(buf,
4707 "pg_catalog.array_to_string(%s, '\\n') AS \"%s\"",
4708 colname, gettext_noop("Access privileges"));