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-2010, PostgreSQL Global Development Group
11 * $PostgreSQL: pgsql/src/bin/psql/describe.c,v 1.242 2010/07/06 19:18:59 momjian Exp $
13 #include "postgres_fe.h"
19 #include "dumputils.h"
23 #include "variables.h"
26 static bool describeOneTableDetails(const char *schemaname,
27 const char *relationname,
30 static void add_tablespace_footer(printTableContent *const cont, char relkind,
31 Oid tablespace, const bool newline);
32 static void add_role_attribute(PQExpBuffer buf, const char *const str);
33 static bool listTSParsersVerbose(const char *pattern);
34 static bool describeOneTSParser(const char *oid, const char *nspname,
36 static bool listTSConfigsVerbose(const char *pattern);
37 static bool describeOneTSConfig(const char *oid, const char *nspname,
39 const char *pnspname, const char *prsname);
40 static void printACLColumn(PQExpBuffer buf, const char *colname);
44 * Handlers for various slash commands displaying some sort of list
45 * of things in the database.
47 * Note: try to format the queries to look nice in -E output.
53 * Takes an optional regexp to select particular aggregates
56 describeAggregates(const char *pattern, bool verbose, bool showSystem)
60 printQueryOpt myopt = pset.popt;
62 initPQExpBuffer(&buf);
64 printfPQExpBuffer(&buf,
65 "SELECT n.nspname as \"%s\",\n"
66 " p.proname AS \"%s\",\n"
67 " pg_catalog.format_type(p.prorettype, NULL) AS \"%s\",\n",
68 gettext_noop("Schema"),
70 gettext_noop("Result data type"));
72 if (pset.sversion >= 80200)
73 appendPQExpBuffer(&buf,
74 " CASE WHEN p.pronargs = 0\n"
75 " THEN CAST('*' AS pg_catalog.text)\n"
77 " pg_catalog.array_to_string(ARRAY(\n"
79 " pg_catalog.format_type(p.proargtypes[s.i], NULL)\n"
81 " pg_catalog.generate_series(0, pg_catalog.array_upper(p.proargtypes, 1)) AS s(i)\n"
84 gettext_noop("Argument data types"));
86 appendPQExpBuffer(&buf,
87 " pg_catalog.format_type(p.proargtypes[0], NULL) AS \"%s\",\n",
88 gettext_noop("Argument data types"));
90 appendPQExpBuffer(&buf,
91 " pg_catalog.obj_description(p.oid, 'pg_proc') as \"%s\"\n"
92 "FROM pg_catalog.pg_proc p\n"
93 " LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace\n"
95 gettext_noop("Description"));
97 if (!showSystem && !pattern)
98 appendPQExpBuffer(&buf, " AND n.nspname <> 'pg_catalog'\n"
99 " AND n.nspname <> 'information_schema'\n");
101 processSQLNamePattern(pset.db, &buf, pattern, true, false,
102 "n.nspname", "p.proname", NULL,
103 "pg_catalog.pg_function_is_visible(p.oid)");
105 appendPQExpBuffer(&buf, "ORDER BY 1, 2, 4;");
107 res = PSQLexec(buf.data, false);
108 termPQExpBuffer(&buf);
112 myopt.nullPrint = NULL;
113 myopt.title = _("List of aggregate functions");
114 myopt.translate_header = true;
116 printQuery(res, &myopt, pset.queryFout, pset.logfile);
123 * Takes an optional regexp to select particular tablespaces
126 describeTablespaces(const char *pattern, bool verbose)
130 printQueryOpt myopt = pset.popt;
132 if (pset.sversion < 80000)
134 fprintf(stderr, _("The server (version %d.%d) does not support tablespaces.\n"),
135 pset.sversion / 10000, (pset.sversion / 100) % 100);
139 initPQExpBuffer(&buf);
141 printfPQExpBuffer(&buf,
142 "SELECT spcname AS \"%s\",\n"
143 " pg_catalog.pg_get_userbyid(spcowner) AS \"%s\",\n"
144 " spclocation AS \"%s\"",
145 gettext_noop("Name"),
146 gettext_noop("Owner"),
147 gettext_noop("Location"));
151 appendPQExpBuffer(&buf, ",\n ");
152 printACLColumn(&buf, "spcacl");
155 if (verbose && pset.sversion >= 80200)
156 appendPQExpBuffer(&buf,
157 ",\n pg_catalog.shobj_description(oid, 'pg_tablespace') AS \"%s\"",
158 gettext_noop("Description"));
160 appendPQExpBuffer(&buf,
161 "\nFROM pg_catalog.pg_tablespace\n");
163 processSQLNamePattern(pset.db, &buf, pattern, false, false,
164 NULL, "spcname", NULL,
167 appendPQExpBuffer(&buf, "ORDER BY 1;");
169 res = PSQLexec(buf.data, false);
170 termPQExpBuffer(&buf);
174 myopt.nullPrint = NULL;
175 myopt.title = _("List of tablespaces");
176 myopt.translate_header = true;
178 printQuery(res, &myopt, pset.queryFout, pset.logfile);
186 * Takes an optional regexp to select particular functions.
188 * As with \d, you can specify the kinds of functions you want:
195 * and you can mix and match these in any order.
198 describeFunctions(const char *functypes, const char *pattern, bool verbose, bool showSystem)
200 bool showAggregate = strchr(functypes, 'a') != NULL;
201 bool showNormal = strchr(functypes, 'n') != NULL;
202 bool showTrigger = strchr(functypes, 't') != NULL;
203 bool showWindow = strchr(functypes, 'w') != NULL;
207 printQueryOpt myopt = pset.popt;
208 static const bool translate_columns[] = {false, false, false, false, true, true, false, false, false, false};
210 if (strlen(functypes) != strspn(functypes, "antwS+"))
212 fprintf(stderr, _("\\df only takes [antwS+] as options\n"));
216 if (showWindow && pset.sversion < 80400)
218 fprintf(stderr, _("\\df does not take a \"w\" option with server version %d.%d\n"),
219 pset.sversion / 10000, (pset.sversion / 100) % 100);
223 if (!showAggregate && !showNormal && !showTrigger && !showWindow)
225 showAggregate = showNormal = showTrigger = true;
226 if (pset.sversion >= 80400)
230 initPQExpBuffer(&buf);
232 printfPQExpBuffer(&buf,
233 "SELECT n.nspname as \"%s\",\n"
234 " p.proname as \"%s\",\n",
235 gettext_noop("Schema"),
236 gettext_noop("Name"));
238 if (pset.sversion >= 80400)
239 appendPQExpBuffer(&buf,
240 " pg_catalog.pg_get_function_result(p.oid) as \"%s\",\n"
241 " pg_catalog.pg_get_function_arguments(p.oid) as \"%s\",\n"
243 " WHEN p.proisagg THEN '%s'\n"
244 " WHEN p.proiswindow THEN '%s'\n"
245 " WHEN p.prorettype = 'pg_catalog.trigger'::pg_catalog.regtype THEN '%s'\n"
248 gettext_noop("Result data type"),
249 gettext_noop("Argument data types"),
250 /* translator: "agg" is short for "aggregate" */
252 gettext_noop("window"),
253 gettext_noop("trigger"),
254 gettext_noop("normal"),
255 gettext_noop("Type"));
256 else if (pset.sversion >= 80100)
257 appendPQExpBuffer(&buf,
258 " CASE WHEN p.proretset THEN 'SETOF ' ELSE '' END ||\n"
259 " pg_catalog.format_type(p.prorettype, NULL) as \"%s\",\n"
260 " CASE WHEN proallargtypes IS NOT NULL THEN\n"
261 " pg_catalog.array_to_string(ARRAY(\n"
264 " WHEN p.proargmodes[s.i] = 'i' THEN ''\n"
265 " WHEN p.proargmodes[s.i] = 'o' THEN 'OUT '\n"
266 " WHEN p.proargmodes[s.i] = 'b' THEN 'INOUT '\n"
267 " WHEN p.proargmodes[s.i] = 'v' THEN 'VARIADIC '\n"
270 " WHEN COALESCE(p.proargnames[s.i], '') = '' THEN ''\n"
271 " ELSE p.proargnames[s.i] || ' ' \n"
273 " pg_catalog.format_type(p.proallargtypes[s.i], NULL)\n"
275 " pg_catalog.generate_series(1, pg_catalog.array_upper(p.proallargtypes, 1)) AS s(i)\n"
278 " pg_catalog.array_to_string(ARRAY(\n"
281 " WHEN COALESCE(p.proargnames[s.i+1], '') = '' THEN ''\n"
282 " ELSE p.proargnames[s.i+1] || ' '\n"
284 " pg_catalog.format_type(p.proargtypes[s.i], NULL)\n"
286 " pg_catalog.generate_series(0, pg_catalog.array_upper(p.proargtypes, 1)) AS s(i)\n"
290 " WHEN p.proisagg THEN '%s'\n"
291 " WHEN p.prorettype = 'pg_catalog.trigger'::pg_catalog.regtype THEN '%s'\n"
294 gettext_noop("Result data type"),
295 gettext_noop("Argument data types"),
296 /* translator: "agg" is short for "aggregate" */
298 gettext_noop("trigger"),
299 gettext_noop("normal"),
300 gettext_noop("Type"));
302 appendPQExpBuffer(&buf,
303 " CASE WHEN p.proretset THEN 'SETOF ' ELSE '' END ||\n"
304 " pg_catalog.format_type(p.prorettype, NULL) as \"%s\",\n"
305 " pg_catalog.oidvectortypes(p.proargtypes) as \"%s\",\n"
307 " WHEN p.proisagg THEN '%s'\n"
308 " WHEN p.prorettype = 'pg_catalog.trigger'::pg_catalog.regtype THEN '%s'\n"
311 gettext_noop("Result data type"),
312 gettext_noop("Argument data types"),
313 /* translator: "agg" is short for "aggregate" */
315 gettext_noop("trigger"),
316 gettext_noop("normal"),
317 gettext_noop("Type"));
320 appendPQExpBuffer(&buf,
322 " WHEN p.provolatile = 'i' THEN '%s'\n"
323 " WHEN p.provolatile = 's' THEN '%s'\n"
324 " WHEN p.provolatile = 'v' THEN '%s'\n"
326 ",\n pg_catalog.pg_get_userbyid(p.proowner) as \"%s\",\n"
327 " l.lanname as \"%s\",\n"
328 " p.prosrc as \"%s\",\n"
329 " pg_catalog.obj_description(p.oid, 'pg_proc') as \"%s\"",
330 gettext_noop("immutable"),
331 gettext_noop("stable"),
332 gettext_noop("volatile"),
333 gettext_noop("Volatility"),
334 gettext_noop("Owner"),
335 gettext_noop("Language"),
336 gettext_noop("Source code"),
337 gettext_noop("Description"));
339 appendPQExpBuffer(&buf,
340 "\nFROM pg_catalog.pg_proc p"
341 "\n LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace\n");
344 appendPQExpBuffer(&buf,
345 " LEFT JOIN pg_catalog.pg_language l ON l.oid = p.prolang\n");
349 /* filter by function type, if requested */
350 if (showNormal && showAggregate && showTrigger && showWindow)
357 appendPQExpBuffer(&buf, " AND ");
360 appendPQExpBuffer(&buf, "WHERE ");
363 appendPQExpBuffer(&buf, "NOT p.proisagg\n");
368 appendPQExpBuffer(&buf, " AND ");
371 appendPQExpBuffer(&buf, "WHERE ");
374 appendPQExpBuffer(&buf, "p.prorettype <> 'pg_catalog.trigger'::pg_catalog.regtype\n");
376 if (!showWindow && pset.sversion >= 80400)
379 appendPQExpBuffer(&buf, " AND ");
382 appendPQExpBuffer(&buf, "WHERE ");
385 appendPQExpBuffer(&buf, "NOT p.proiswindow\n");
390 bool needs_or = false;
392 appendPQExpBuffer(&buf, "WHERE (\n ");
394 /* Note: at least one of these must be true ... */
397 appendPQExpBuffer(&buf, "p.proisagg\n");
403 appendPQExpBuffer(&buf, " OR ");
404 appendPQExpBuffer(&buf,
405 "p.prorettype = 'pg_catalog.trigger'::pg_catalog.regtype\n");
411 appendPQExpBuffer(&buf, " OR ");
412 appendPQExpBuffer(&buf, "p.proiswindow\n");
415 appendPQExpBuffer(&buf, " )\n");
418 processSQLNamePattern(pset.db, &buf, pattern, have_where, false,
419 "n.nspname", "p.proname", NULL,
420 "pg_catalog.pg_function_is_visible(p.oid)");
422 if (!showSystem && !pattern)
423 appendPQExpBuffer(&buf, " AND n.nspname <> 'pg_catalog'\n"
424 " AND n.nspname <> 'information_schema'\n");
426 appendPQExpBuffer(&buf, "ORDER BY 1, 2, 4;");
428 res = PSQLexec(buf.data, false);
429 termPQExpBuffer(&buf);
433 myopt.nullPrint = NULL;
434 myopt.title = _("List of functions");
435 myopt.translate_header = true;
436 myopt.translate_columns = translate_columns;
438 printQuery(res, &myopt, pset.queryFout, pset.logfile);
451 describeTypes(const char *pattern, bool verbose, bool showSystem)
455 printQueryOpt myopt = pset.popt;
457 initPQExpBuffer(&buf);
459 printfPQExpBuffer(&buf,
460 "SELECT n.nspname as \"%s\",\n"
461 " pg_catalog.format_type(t.oid, NULL) AS \"%s\",\n",
462 gettext_noop("Schema"),
463 gettext_noop("Name"));
465 appendPQExpBuffer(&buf,
466 " t.typname AS \"%s\",\n"
467 " CASE WHEN t.typrelid != 0\n"
468 " THEN CAST('tuple' AS pg_catalog.text)\n"
469 " WHEN t.typlen < 0\n"
470 " THEN CAST('var' AS pg_catalog.text)\n"
471 " ELSE CAST(t.typlen AS pg_catalog.text)\n"
473 gettext_noop("Internal name"),
474 gettext_noop("Size"));
475 if (verbose && pset.sversion >= 80300)
476 appendPQExpBuffer(&buf,
477 " pg_catalog.array_to_string(\n"
479 " SELECT e.enumlabel\n"
480 " FROM pg_catalog.pg_enum e\n"
481 " WHERE e.enumtypid = t.oid\n"
486 gettext_noop("Elements"));
488 appendPQExpBuffer(&buf,
489 " pg_catalog.obj_description(t.oid, 'pg_type') as \"%s\"\n",
490 gettext_noop("Description"));
492 appendPQExpBuffer(&buf, "FROM pg_catalog.pg_type t\n"
493 " LEFT JOIN pg_catalog.pg_namespace n ON n.oid = t.typnamespace\n");
496 * do not include complex types (typrelid!=0) unless they are standalone
499 appendPQExpBuffer(&buf, "WHERE (t.typrelid = 0 ");
500 appendPQExpBuffer(&buf, "OR (SELECT c.relkind = 'c' FROM pg_catalog.pg_class c "
501 "WHERE c.oid = t.typrelid))\n");
504 * do not include array types (before 8.3 we have to use the assumption
505 * that their names start with underscore)
507 if (pset.sversion >= 80300)
508 appendPQExpBuffer(&buf, " AND NOT EXISTS(SELECT 1 FROM pg_catalog.pg_type el WHERE el.oid = t.typelem AND el.typarray = t.oid)\n");
510 appendPQExpBuffer(&buf, " AND t.typname !~ '^_'\n");
512 if (!showSystem && !pattern)
513 appendPQExpBuffer(&buf, " AND n.nspname <> 'pg_catalog'\n"
514 " AND n.nspname <> 'information_schema'\n");
516 /* Match name pattern against either internal or external name */
517 processSQLNamePattern(pset.db, &buf, pattern, true, false,
518 "n.nspname", "t.typname",
519 "pg_catalog.format_type(t.oid, NULL)",
520 "pg_catalog.pg_type_is_visible(t.oid)");
522 appendPQExpBuffer(&buf, "ORDER BY 1, 2;");
524 res = PSQLexec(buf.data, false);
525 termPQExpBuffer(&buf);
529 myopt.nullPrint = NULL;
530 myopt.title = _("List of data types");
531 myopt.translate_header = true;
533 printQuery(res, &myopt, pset.queryFout, pset.logfile);
543 describeOperators(const char *pattern, bool showSystem)
547 printQueryOpt myopt = pset.popt;
549 initPQExpBuffer(&buf);
551 printfPQExpBuffer(&buf,
552 "SELECT n.nspname as \"%s\",\n"
553 " o.oprname AS \"%s\",\n"
554 " CASE WHEN o.oprkind='l' THEN NULL ELSE pg_catalog.format_type(o.oprleft, NULL) END AS \"%s\",\n"
555 " CASE WHEN o.oprkind='r' THEN NULL ELSE pg_catalog.format_type(o.oprright, NULL) END AS \"%s\",\n"
556 " pg_catalog.format_type(o.oprresult, NULL) AS \"%s\",\n"
557 " coalesce(pg_catalog.obj_description(o.oid, 'pg_operator'),\n"
558 " pg_catalog.obj_description(o.oprcode, 'pg_proc')) AS \"%s\"\n"
559 "FROM pg_catalog.pg_operator o\n"
560 " LEFT JOIN pg_catalog.pg_namespace n ON n.oid = o.oprnamespace\n",
561 gettext_noop("Schema"),
562 gettext_noop("Name"),
563 gettext_noop("Left arg type"),
564 gettext_noop("Right arg type"),
565 gettext_noop("Result type"),
566 gettext_noop("Description"));
568 if (!showSystem && !pattern)
569 appendPQExpBuffer(&buf, "WHERE n.nspname <> 'pg_catalog'\n"
570 " AND n.nspname <> 'information_schema'\n");
572 processSQLNamePattern(pset.db, &buf, pattern, !showSystem && !pattern, true,
573 "n.nspname", "o.oprname", NULL,
574 "pg_catalog.pg_operator_is_visible(o.oid)");
576 appendPQExpBuffer(&buf, "ORDER BY 1, 2, 3, 4;");
578 res = PSQLexec(buf.data, false);
579 termPQExpBuffer(&buf);
583 myopt.nullPrint = NULL;
584 myopt.title = _("List of operators");
585 myopt.translate_header = true;
587 printQuery(res, &myopt, pset.queryFout, pset.logfile);
597 * for \l, \list, and -l switch
600 listAllDbs(bool verbose)
604 printQueryOpt myopt = pset.popt;
606 initPQExpBuffer(&buf);
608 printfPQExpBuffer(&buf,
609 "SELECT d.datname as \"%s\",\n"
610 " pg_catalog.pg_get_userbyid(d.datdba) as \"%s\",\n"
611 " pg_catalog.pg_encoding_to_char(d.encoding) as \"%s\",\n",
612 gettext_noop("Name"),
613 gettext_noop("Owner"),
614 gettext_noop("Encoding"));
615 if (pset.sversion >= 80400)
616 appendPQExpBuffer(&buf,
617 " d.datcollate as \"%s\",\n"
618 " d.datctype as \"%s\",\n",
619 gettext_noop("Collation"),
620 gettext_noop("Ctype"));
621 appendPQExpBuffer(&buf, " ");
622 printACLColumn(&buf, "d.datacl");
623 if (verbose && pset.sversion >= 80200)
624 appendPQExpBuffer(&buf,
625 ",\n CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT')\n"
626 " THEN pg_catalog.pg_size_pretty(pg_catalog.pg_database_size(d.datname))\n"
627 " ELSE 'No Access'\n"
629 gettext_noop("Size"));
630 if (verbose && pset.sversion >= 80000)
631 appendPQExpBuffer(&buf,
632 ",\n t.spcname as \"%s\"",
633 gettext_noop("Tablespace"));
634 if (verbose && pset.sversion >= 80200)
635 appendPQExpBuffer(&buf,
636 ",\n pg_catalog.shobj_description(d.oid, 'pg_database') as \"%s\"",
637 gettext_noop("Description"));
638 appendPQExpBuffer(&buf,
639 "\nFROM pg_catalog.pg_database d\n");
640 if (verbose && pset.sversion >= 80000)
641 appendPQExpBuffer(&buf,
642 " JOIN pg_catalog.pg_tablespace t on d.dattablespace = t.oid\n");
643 appendPQExpBuffer(&buf, "ORDER BY 1;");
644 res = PSQLexec(buf.data, false);
645 termPQExpBuffer(&buf);
649 myopt.nullPrint = NULL;
650 myopt.title = _("List of databases");
651 myopt.translate_header = true;
653 printQuery(res, &myopt, pset.queryFout, pset.logfile);
661 * List Tables' Grant/Revoke Permissions
662 * \z (now also \dp -- perhaps more mnemonic)
665 permissionsList(const char *pattern)
669 printQueryOpt myopt = pset.popt;
670 static const bool translate_columns[] = {false, false, true, false, false};
672 initPQExpBuffer(&buf);
675 * we ignore indexes and toast tables since they have no meaningful rights
677 printfPQExpBuffer(&buf,
678 "SELECT n.nspname as \"%s\",\n"
679 " c.relname as \"%s\",\n"
680 " CASE c.relkind WHEN 'r' THEN '%s' WHEN 'v' THEN '%s' WHEN 'S' THEN '%s' END as \"%s\",\n"
682 gettext_noop("Schema"),
683 gettext_noop("Name"),
684 gettext_noop("table"), gettext_noop("view"), gettext_noop("sequence"),
685 gettext_noop("Type"));
687 printACLColumn(&buf, "c.relacl");
689 if (pset.sversion >= 80400)
690 appendPQExpBuffer(&buf,
691 ",\n pg_catalog.array_to_string(ARRAY(\n"
692 " SELECT attname || E':\\n ' || pg_catalog.array_to_string(attacl, E'\\n ')\n"
693 " FROM pg_catalog.pg_attribute a\n"
694 " WHERE attrelid = c.oid AND NOT attisdropped AND attacl IS NOT NULL\n"
695 " ), E'\\n') AS \"%s\"",
696 gettext_noop("Column access privileges"));
698 appendPQExpBuffer(&buf, "\nFROM pg_catalog.pg_class c\n"
699 " LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace\n"
700 "WHERE c.relkind IN ('r', 'v', 'S')\n");
703 * Unless a schema pattern is specified, we suppress system and temp
704 * tables, since they normally aren't very interesting from a permissions
705 * point of view. You can see 'em by explicit request though, eg with \z
708 processSQLNamePattern(pset.db, &buf, pattern, true, false,
709 "n.nspname", "c.relname", NULL,
710 "n.nspname !~ '^pg_' AND pg_catalog.pg_table_is_visible(c.oid)");
712 appendPQExpBuffer(&buf, "ORDER BY 1, 2;");
714 res = PSQLexec(buf.data, false);
717 termPQExpBuffer(&buf);
721 myopt.nullPrint = NULL;
722 printfPQExpBuffer(&buf, _("Access privileges"));
723 myopt.title = buf.data;
724 myopt.translate_header = true;
725 myopt.translate_columns = translate_columns;
727 printQuery(res, &myopt, pset.queryFout, pset.logfile);
729 termPQExpBuffer(&buf);
738 * List DefaultACLs. The pattern can match either schema or role name.
741 listDefaultACLs(const char *pattern)
745 printQueryOpt myopt = pset.popt;
746 static const bool translate_columns[] = {false, false, true, false};
748 if (pset.sversion < 90000)
750 fprintf(stderr, _("The server (version %d.%d) does not support altering default privileges.\n"),
751 pset.sversion / 10000, (pset.sversion / 100) % 100);
755 initPQExpBuffer(&buf);
757 printfPQExpBuffer(&buf,
758 "SELECT pg_catalog.pg_get_userbyid(d.defaclrole) AS \"%s\",\n"
759 " n.nspname AS \"%s\",\n"
760 " CASE d.defaclobjtype WHEN 'r' THEN '%s' WHEN 'S' THEN '%s' WHEN 'f' THEN '%s' END AS \"%s\",\n"
762 gettext_noop("Owner"),
763 gettext_noop("Schema"),
764 gettext_noop("table"),
765 gettext_noop("sequence"),
766 gettext_noop("function"),
767 gettext_noop("Type"));
769 printACLColumn(&buf, "d.defaclacl");
771 appendPQExpBuffer(&buf, "\nFROM pg_catalog.pg_default_acl d\n"
772 " LEFT JOIN pg_catalog.pg_namespace n ON n.oid = d.defaclnamespace\n");
774 processSQLNamePattern(pset.db, &buf, pattern, false, false,
777 "pg_catalog.pg_get_userbyid(d.defaclrole)",
780 appendPQExpBuffer(&buf, "ORDER BY 1, 2, 3;");
782 res = PSQLexec(buf.data, false);
785 termPQExpBuffer(&buf);
789 myopt.nullPrint = NULL;
790 printfPQExpBuffer(&buf, _("Default access privileges"));
791 myopt.title = buf.data;
792 myopt.translate_header = true;
793 myopt.translate_columns = translate_columns;
795 printQuery(res, &myopt, pset.queryFout, pset.logfile);
797 termPQExpBuffer(&buf);
804 * Get object comments
808 * Note: This only lists things that actually have a description. For complete
809 * lists of things, there are other \d? commands.
812 objectDescription(const char *pattern, bool showSystem)
816 printQueryOpt myopt = pset.popt;
817 static const bool translate_columns[] = {false, false, true, false};
819 initPQExpBuffer(&buf);
821 appendPQExpBuffer(&buf,
822 "SELECT DISTINCT tt.nspname AS \"%s\", tt.name AS \"%s\", tt.object AS \"%s\", d.description AS \"%s\"\n"
824 gettext_noop("Schema"),
825 gettext_noop("Name"),
826 gettext_noop("Object"),
827 gettext_noop("Description"));
829 /* Aggregate descriptions */
830 appendPQExpBuffer(&buf,
831 " SELECT p.oid as oid, p.tableoid as tableoid,\n"
832 " n.nspname as nspname,\n"
833 " CAST(p.proname AS pg_catalog.text) as name,"
834 " CAST('%s' AS pg_catalog.text) as object\n"
835 " FROM pg_catalog.pg_proc p\n"
836 " LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace\n"
837 " WHERE p.proisagg\n",
838 gettext_noop("aggregate"));
840 if (!showSystem && !pattern)
841 appendPQExpBuffer(&buf, " AND n.nspname <> 'pg_catalog'\n"
842 " AND n.nspname <> 'information_schema'\n");
844 processSQLNamePattern(pset.db, &buf, pattern, true, false,
845 "n.nspname", "p.proname", NULL,
846 "pg_catalog.pg_function_is_visible(p.oid)");
848 /* Function descriptions */
849 appendPQExpBuffer(&buf,
851 " SELECT p.oid as oid, p.tableoid as tableoid,\n"
852 " n.nspname as nspname,\n"
853 " CAST(p.proname AS pg_catalog.text) as name,"
854 " CAST('%s' AS pg_catalog.text) as object\n"
855 " FROM pg_catalog.pg_proc p\n"
856 " LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace\n"
857 " WHERE NOT p.proisagg\n",
858 gettext_noop("function"));
860 if (!showSystem && !pattern)
861 appendPQExpBuffer(&buf, " AND n.nspname <> 'pg_catalog'\n"
862 " AND n.nspname <> 'information_schema'\n");
864 processSQLNamePattern(pset.db, &buf, pattern, true, false,
865 "n.nspname", "p.proname", NULL,
866 "pg_catalog.pg_function_is_visible(p.oid)");
868 /* Operator descriptions (only if operator has its own comment) */
869 appendPQExpBuffer(&buf,
871 " SELECT o.oid as oid, o.tableoid as tableoid,\n"
872 " n.nspname as nspname,\n"
873 " CAST(o.oprname AS pg_catalog.text) as name,"
874 " CAST('%s' AS pg_catalog.text) as object\n"
875 " FROM pg_catalog.pg_operator o\n"
876 " LEFT JOIN pg_catalog.pg_namespace n ON n.oid = o.oprnamespace\n",
877 gettext_noop("operator"));
879 if (!showSystem && !pattern)
880 appendPQExpBuffer(&buf, "WHERE n.nspname <> 'pg_catalog'\n"
881 " AND n.nspname <> 'information_schema'\n");
883 processSQLNamePattern(pset.db, &buf, pattern, !showSystem && !pattern, false,
884 "n.nspname", "o.oprname", NULL,
885 "pg_catalog.pg_operator_is_visible(o.oid)");
887 /* Type description */
888 appendPQExpBuffer(&buf,
890 " SELECT t.oid as oid, t.tableoid as tableoid,\n"
891 " n.nspname as nspname,\n"
892 " pg_catalog.format_type(t.oid, NULL) as name,"
893 " CAST('%s' AS pg_catalog.text) as object\n"
894 " FROM pg_catalog.pg_type t\n"
895 " LEFT JOIN pg_catalog.pg_namespace n ON n.oid = t.typnamespace\n",
896 gettext_noop("data type"));
898 if (!showSystem && !pattern)
899 appendPQExpBuffer(&buf, "WHERE n.nspname <> 'pg_catalog'\n"
900 " AND n.nspname <> 'information_schema'\n");
902 processSQLNamePattern(pset.db, &buf, pattern, !showSystem && !pattern, false,
903 "n.nspname", "pg_catalog.format_type(t.oid, NULL)",
905 "pg_catalog.pg_type_is_visible(t.oid)");
907 /* Relation (tables, views, indexes, sequences) descriptions */
908 appendPQExpBuffer(&buf,
910 " SELECT c.oid as oid, c.tableoid as tableoid,\n"
911 " n.nspname as nspname,\n"
912 " CAST(c.relname AS pg_catalog.text) as name,\n"
914 " CASE c.relkind WHEN 'r' THEN '%s' WHEN 'v' THEN '%s' WHEN 'i' THEN '%s' WHEN 'S' THEN '%s' END"
915 " AS pg_catalog.text) as object\n"
916 " FROM pg_catalog.pg_class c\n"
917 " LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace\n"
918 " WHERE c.relkind IN ('r', 'v', 'i', 'S')\n",
919 gettext_noop("table"),
920 gettext_noop("view"),
921 gettext_noop("index"),
922 gettext_noop("sequence"));
924 if (!showSystem && !pattern)
925 appendPQExpBuffer(&buf, " AND n.nspname <> 'pg_catalog'\n"
926 " AND n.nspname <> 'information_schema'\n");
928 processSQLNamePattern(pset.db, &buf, pattern, true, false,
929 "n.nspname", "c.relname", NULL,
930 "pg_catalog.pg_table_is_visible(c.oid)");
932 /* Rule description (ignore rules for views) */
933 appendPQExpBuffer(&buf,
935 " SELECT r.oid as oid, r.tableoid as tableoid,\n"
936 " n.nspname as nspname,\n"
937 " CAST(r.rulename AS pg_catalog.text) as name,"
938 " CAST('%s' AS pg_catalog.text) as object\n"
939 " FROM pg_catalog.pg_rewrite r\n"
940 " JOIN pg_catalog.pg_class c ON c.oid = r.ev_class\n"
941 " LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace\n"
942 " WHERE r.rulename != '_RETURN'\n",
943 gettext_noop("rule"));
945 if (!showSystem && !pattern)
946 appendPQExpBuffer(&buf, " AND n.nspname <> 'pg_catalog'\n"
947 " AND n.nspname <> 'information_schema'\n");
949 /* XXX not sure what to do about visibility rule here? */
950 processSQLNamePattern(pset.db, &buf, pattern, true, false,
951 "n.nspname", "r.rulename", NULL,
952 "pg_catalog.pg_table_is_visible(c.oid)");
954 /* Trigger description */
955 appendPQExpBuffer(&buf,
957 " SELECT t.oid as oid, t.tableoid as tableoid,\n"
958 " n.nspname as nspname,\n"
959 " CAST(t.tgname AS pg_catalog.text) as name,"
960 " CAST('%s' AS pg_catalog.text) as object\n"
961 " FROM pg_catalog.pg_trigger t\n"
962 " JOIN pg_catalog.pg_class c ON c.oid = t.tgrelid\n"
963 " LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace\n",
964 gettext_noop("trigger"));
966 if (!showSystem && !pattern)
967 appendPQExpBuffer(&buf, "WHERE n.nspname <> 'pg_catalog'\n"
968 " AND n.nspname <> 'information_schema'\n");
970 /* XXX not sure what to do about visibility rule here? */
971 processSQLNamePattern(pset.db, &buf, pattern, !showSystem && !pattern, false,
972 "n.nspname", "t.tgname", NULL,
973 "pg_catalog.pg_table_is_visible(c.oid)");
975 appendPQExpBuffer(&buf,
977 " JOIN pg_catalog.pg_description d ON (tt.oid = d.objoid AND tt.tableoid = d.classoid AND d.objsubid = 0)\n");
979 appendPQExpBuffer(&buf, "ORDER BY 1, 2, 3;");
981 res = PSQLexec(buf.data, false);
982 termPQExpBuffer(&buf);
986 myopt.nullPrint = NULL;
987 myopt.title = _("Object descriptions");
988 myopt.translate_header = true;
989 myopt.translate_columns = translate_columns;
991 printQuery(res, &myopt, pset.queryFout, pset.logfile);
999 * describeTableDetails (for \d)
1001 * This routine finds the tables to be displayed, and calls
1002 * describeOneTableDetails for each one.
1004 * verbose: if true, this is \d+
1007 describeTableDetails(const char *pattern, bool verbose, bool showSystem)
1009 PQExpBufferData buf;
1013 initPQExpBuffer(&buf);
1015 printfPQExpBuffer(&buf,
1019 "FROM pg_catalog.pg_class c\n"
1020 " LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace\n");
1022 if (!showSystem && !pattern)
1023 appendPQExpBuffer(&buf, "WHERE n.nspname <> 'pg_catalog'\n"
1024 " AND n.nspname <> 'information_schema'\n");
1026 processSQLNamePattern(pset.db, &buf, pattern, !showSystem && !pattern, false,
1027 "n.nspname", "c.relname", NULL,
1028 "pg_catalog.pg_table_is_visible(c.oid)");
1030 appendPQExpBuffer(&buf, "ORDER BY 2, 3;");
1032 res = PSQLexec(buf.data, false);
1033 termPQExpBuffer(&buf);
1037 if (PQntuples(res) == 0)
1040 fprintf(stderr, _("Did not find any relation named \"%s\".\n"),
1046 for (i = 0; i < PQntuples(res); i++)
1049 const char *nspname;
1050 const char *relname;
1052 oid = PQgetvalue(res, i, 0);
1053 nspname = PQgetvalue(res, i, 1);
1054 relname = PQgetvalue(res, i, 2);
1056 if (!describeOneTableDetails(nspname, relname, oid, verbose))
1073 * describeOneTableDetails (for \d)
1075 * Unfortunately, the information presented here is so complicated that it
1076 * cannot be done in a single query. So we have to assemble the printed table
1077 * by hand and pass it to the underlying printTable() function.
1080 describeOneTableDetails(const char *schemaname,
1081 const char *relationname,
1085 PQExpBufferData buf;
1086 PGresult *res = NULL;
1087 printTableOpt myopt = pset.popt.topt;
1088 printTableContent cont;
1089 bool printTableInitialized = false;
1091 char *view_def = NULL;
1093 char **seq_values = NULL;
1094 char **modifiers = NULL;
1096 PQExpBufferData title;
1097 PQExpBufferData tmpbuf;
1112 bool show_modifiers = false;
1117 /* This output looks confusing in expanded mode. */
1118 myopt.expanded = false;
1120 initPQExpBuffer(&buf);
1121 initPQExpBuffer(&title);
1122 initPQExpBuffer(&tmpbuf);
1124 /* Get general table info */
1125 if (pset.sversion >= 90000)
1127 printfPQExpBuffer(&buf,
1128 "SELECT c.relchecks, c.relkind, c.relhasindex, c.relhasrules, "
1129 "c.relhastriggers, c.relhasoids, "
1130 "%s, c.reltablespace, "
1131 "CASE WHEN c.reloftype = 0 THEN '' ELSE c.reloftype::pg_catalog.regtype::pg_catalog.text END\n"
1132 "FROM pg_catalog.pg_class c\n "
1133 "LEFT JOIN pg_catalog.pg_class tc ON (c.reltoastrelid = tc.oid)\n"
1134 "WHERE c.oid = '%s'\n",
1136 "pg_catalog.array_to_string(c.reloptions || "
1137 "array(select 'toast.' || x from pg_catalog.unnest(tc.reloptions) x), ', ')\n"
1141 else if (pset.sversion >= 80400)
1143 printfPQExpBuffer(&buf,
1144 "SELECT c.relchecks, c.relkind, c.relhasindex, c.relhasrules, "
1145 "c.relhastriggers, c.relhasoids, "
1146 "%s, c.reltablespace\n"
1147 "FROM pg_catalog.pg_class c\n "
1148 "LEFT JOIN pg_catalog.pg_class tc ON (c.reltoastrelid = tc.oid)\n"
1149 "WHERE c.oid = '%s'\n",
1151 "pg_catalog.array_to_string(c.reloptions || "
1152 "array(select 'toast.' || x from pg_catalog.unnest(tc.reloptions) x), ', ')\n"
1156 else if (pset.sversion >= 80200)
1158 printfPQExpBuffer(&buf,
1159 "SELECT relchecks, relkind, relhasindex, relhasrules, "
1160 "reltriggers <> 0, relhasoids, "
1161 "%s, reltablespace\n"
1162 "FROM pg_catalog.pg_class WHERE oid = '%s'",
1164 "pg_catalog.array_to_string(reloptions, E', ')" : "''"),
1167 else if (pset.sversion >= 80000)
1169 printfPQExpBuffer(&buf,
1170 "SELECT relchecks, relkind, relhasindex, relhasrules, "
1171 "reltriggers <> 0, relhasoids, "
1172 "'', reltablespace\n"
1173 "FROM pg_catalog.pg_class WHERE oid = '%s'",
1178 printfPQExpBuffer(&buf,
1179 "SELECT relchecks, relkind, relhasindex, relhasrules, "
1180 "reltriggers <> 0, relhasoids, "
1182 "FROM pg_catalog.pg_class WHERE oid = '%s'",
1186 res = PSQLexec(buf.data, false);
1190 /* Did we get anything? */
1191 if (PQntuples(res) == 0)
1194 fprintf(stderr, _("Did not find any relation with OID %s.\n"),
1199 tableinfo.checks = atoi(PQgetvalue(res, 0, 0));
1200 tableinfo.relkind = *(PQgetvalue(res, 0, 1));
1201 tableinfo.hasindex = strcmp(PQgetvalue(res, 0, 2), "t") == 0;
1202 tableinfo.hasrules = strcmp(PQgetvalue(res, 0, 3), "t") == 0;
1203 tableinfo.hastriggers = strcmp(PQgetvalue(res, 0, 4), "t") == 0;
1204 tableinfo.hasoids = strcmp(PQgetvalue(res, 0, 5), "t") == 0;
1205 tableinfo.reloptions = (pset.sversion >= 80200) ?
1206 strdup(PQgetvalue(res, 0, 6)) : 0;
1207 tableinfo.tablespace = (pset.sversion >= 80000) ?
1208 atooid(PQgetvalue(res, 0, 7)) : 0;
1209 tableinfo.reloftype = (pset.sversion >= 90000 && strcmp(PQgetvalue(res, 0, 8), "") != 0) ?
1210 strdup(PQgetvalue(res, 0, 8)) : 0;
1215 * If it's a sequence, fetch its values and store into an array that will
1218 if (tableinfo.relkind == 'S')
1220 printfPQExpBuffer(&buf, "SELECT * FROM %s", fmtId(schemaname));
1221 /* must be separate because fmtId isn't reentrant */
1222 appendPQExpBuffer(&buf, ".%s", fmtId(relationname));
1224 res = PSQLexec(buf.data, false);
1228 seq_values = pg_malloc((PQnfields(res) + 1) * sizeof(*seq_values));
1230 for (i = 0; i < PQnfields(res); i++)
1231 seq_values[i] = pg_strdup(PQgetvalue(res, 0, i));
1232 seq_values[i] = NULL;
1238 /* Get column info */
1239 printfPQExpBuffer(&buf, "SELECT a.attname,");
1240 appendPQExpBuffer(&buf, "\n pg_catalog.format_type(a.atttypid, a.atttypmod),"
1241 "\n (SELECT substring(pg_catalog.pg_get_expr(d.adbin, d.adrelid) for 128)"
1242 "\n FROM pg_catalog.pg_attrdef d"
1243 "\n WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef),"
1244 "\n a.attnotnull, a.attnum");
1245 if (tableinfo.relkind == 'i')
1246 appendPQExpBuffer(&buf, ",\n pg_catalog.pg_get_indexdef(a.attrelid, a.attnum, TRUE) AS indexdef");
1248 appendPQExpBuffer(&buf, ",\n a.attstorage, pg_catalog.col_description(a.attrelid, a.attnum)");
1249 appendPQExpBuffer(&buf, "\nFROM pg_catalog.pg_attribute a");
1250 appendPQExpBuffer(&buf, "\nWHERE a.attrelid = '%s' AND a.attnum > 0 AND NOT a.attisdropped", oid);
1251 appendPQExpBuffer(&buf, "\nORDER BY a.attnum");
1253 res = PSQLexec(buf.data, false);
1256 numrows = PQntuples(res);
1259 switch (tableinfo.relkind)
1262 printfPQExpBuffer(&title, _("Table \"%s.%s\""),
1263 schemaname, relationname);
1266 printfPQExpBuffer(&title, _("View \"%s.%s\""),
1267 schemaname, relationname);
1270 printfPQExpBuffer(&title, _("Sequence \"%s.%s\""),
1271 schemaname, relationname);
1274 printfPQExpBuffer(&title, _("Index \"%s.%s\""),
1275 schemaname, relationname);
1278 /* not used as of 8.2, but keep it for backwards compatibility */
1279 printfPQExpBuffer(&title, _("Special relation \"%s.%s\""),
1280 schemaname, relationname);
1283 printfPQExpBuffer(&title, _("TOAST table \"%s.%s\""),
1284 schemaname, relationname);
1287 printfPQExpBuffer(&title, _("Composite type \"%s.%s\""),
1288 schemaname, relationname);
1291 /* untranslated unknown relkind */
1292 printfPQExpBuffer(&title, "?%c? \"%s.%s\"",
1293 tableinfo.relkind, schemaname, relationname);
1297 /* Set the number of columns, and their names */
1298 headers[0] = gettext_noop("Column");
1299 headers[1] = gettext_noop("Type");
1302 if (tableinfo.relkind == 'r' || tableinfo.relkind == 'v')
1304 show_modifiers = true;
1305 headers[cols++] = gettext_noop("Modifiers");
1306 modifiers = pg_malloc_zero((numrows + 1) * sizeof(*modifiers));
1309 if (tableinfo.relkind == 'S')
1310 headers[cols++] = gettext_noop("Value");
1312 if (tableinfo.relkind == 'i')
1313 headers[cols++] = gettext_noop("Definition");
1317 headers[cols++] = gettext_noop("Storage");
1318 headers[cols++] = gettext_noop("Description");
1321 printTableInit(&cont, &myopt, title.data, cols, numrows);
1322 printTableInitialized = true;
1324 for (i = 0; i < cols; i++)
1325 printTableAddHeader(&cont, headers[i], true, 'l');
1327 /* Check if table is a view */
1328 if (tableinfo.relkind == 'v' && verbose)
1332 printfPQExpBuffer(&buf,
1333 "SELECT pg_catalog.pg_get_viewdef('%s'::pg_catalog.oid, true)",
1335 result = PSQLexec(buf.data, false);
1339 if (PQntuples(result) > 0)
1340 view_def = pg_strdup(PQgetvalue(result, 0, 0));
1345 /* Generate table cells to be printed */
1346 for (i = 0; i < numrows; i++)
1349 printTableAddCell(&cont, PQgetvalue(res, i, 0), false, false);
1352 printTableAddCell(&cont, PQgetvalue(res, i, 1), false, false);
1354 /* Modifiers: not null and default */
1357 resetPQExpBuffer(&tmpbuf);
1358 if (strcmp(PQgetvalue(res, i, 3), "t") == 0)
1359 appendPQExpBufferStr(&tmpbuf, _("not null"));
1361 /* handle "default" here */
1362 /* (note: above we cut off the 'default' string at 128) */
1363 if (strlen(PQgetvalue(res, i, 2)) != 0)
1366 appendPQExpBufferStr(&tmpbuf, " ");
1367 /* translator: default values of column definitions */
1368 appendPQExpBuffer(&tmpbuf, _("default %s"),
1369 PQgetvalue(res, i, 2));
1372 modifiers[i] = pg_strdup(tmpbuf.data);
1373 printTableAddCell(&cont, modifiers[i], false, false);
1376 /* Value: for sequences only */
1377 if (tableinfo.relkind == 'S')
1378 printTableAddCell(&cont, seq_values[i], false, false);
1380 /* Expression for index column */
1381 if (tableinfo.relkind == 'i')
1382 printTableAddCell(&cont, PQgetvalue(res, i, 5), false, false);
1384 /* Storage and Description */
1387 int firstvcol = (tableinfo.relkind == 'i' ? 6 : 5);
1388 char *storage = PQgetvalue(res, i, firstvcol);
1390 /* these strings are literal in our syntax, so not translated. */
1391 printTableAddCell(&cont, (storage[0] == 'p' ? "plain" :
1392 (storage[0] == 'm' ? "main" :
1393 (storage[0] == 'x' ? "extended" :
1394 (storage[0] == 'e' ? "external" :
1397 printTableAddCell(&cont, PQgetvalue(res, i, firstvcol + 1),
1403 if (tableinfo.relkind == 'i')
1405 /* Footer information about an index */
1408 printfPQExpBuffer(&buf,
1409 "SELECT i.indisunique, i.indisprimary, i.indisclustered, ");
1410 if (pset.sversion >= 80200)
1411 appendPQExpBuffer(&buf, "i.indisvalid,\n");
1413 appendPQExpBuffer(&buf, "true AS indisvalid,\n");
1414 if (pset.sversion >= 90000)
1415 appendPQExpBuffer(&buf,
1416 " (NOT i.indimmediate) AND "
1417 "EXISTS (SELECT 1 FROM pg_catalog.pg_constraint "
1418 "WHERE conrelid = i.indrelid AND "
1419 "conindid = i.indexrelid AND "
1420 "contype IN ('p','u','x') AND "
1421 "condeferrable) AS condeferrable,\n"
1422 " (NOT i.indimmediate) AND "
1423 "EXISTS (SELECT 1 FROM pg_catalog.pg_constraint "
1424 "WHERE conrelid = i.indrelid AND "
1425 "conindid = i.indexrelid AND "
1426 "contype IN ('p','u','x') AND "
1427 "condeferred) AS condeferred,\n");
1429 appendPQExpBuffer(&buf,
1430 " false AS condeferrable, false AS condeferred,\n");
1431 appendPQExpBuffer(&buf, " a.amname, c2.relname, "
1432 "pg_catalog.pg_get_expr(i.indpred, i.indrelid, true)\n"
1433 "FROM pg_catalog.pg_index i, pg_catalog.pg_class c, pg_catalog.pg_class c2, pg_catalog.pg_am a\n"
1434 "WHERE i.indexrelid = c.oid AND c.oid = '%s' AND c.relam = a.oid\n"
1435 "AND i.indrelid = c2.oid",
1438 result = PSQLexec(buf.data, false);
1441 else if (PQntuples(result) != 1)
1448 char *indisunique = PQgetvalue(result, 0, 0);
1449 char *indisprimary = PQgetvalue(result, 0, 1);
1450 char *indisclustered = PQgetvalue(result, 0, 2);
1451 char *indisvalid = PQgetvalue(result, 0, 3);
1452 char *deferrable = PQgetvalue(result, 0, 4);
1453 char *deferred = PQgetvalue(result, 0, 5);
1454 char *indamname = PQgetvalue(result, 0, 6);
1455 char *indtable = PQgetvalue(result, 0, 7);
1456 char *indpred = PQgetvalue(result, 0, 8);
1458 if (strcmp(indisprimary, "t") == 0)
1459 printfPQExpBuffer(&tmpbuf, _("primary key, "));
1460 else if (strcmp(indisunique, "t") == 0)
1461 printfPQExpBuffer(&tmpbuf, _("unique, "));
1463 resetPQExpBuffer(&tmpbuf);
1464 appendPQExpBuffer(&tmpbuf, "%s, ", indamname);
1466 /* we assume here that index and table are in same schema */
1467 appendPQExpBuffer(&tmpbuf, _("for table \"%s.%s\""),
1468 schemaname, indtable);
1470 if (strlen(indpred))
1471 appendPQExpBuffer(&tmpbuf, _(", predicate (%s)"), indpred);
1473 if (strcmp(indisclustered, "t") == 0)
1474 appendPQExpBuffer(&tmpbuf, _(", clustered"));
1476 if (strcmp(indisvalid, "t") != 0)
1477 appendPQExpBuffer(&tmpbuf, _(", invalid"));
1479 if (strcmp(deferrable, "t") == 0)
1480 appendPQExpBuffer(&tmpbuf, _(", deferrable"));
1482 if (strcmp(deferred, "t") == 0)
1483 appendPQExpBuffer(&tmpbuf, _(", initially deferred"));
1485 printTableAddFooter(&cont, tmpbuf.data);
1486 add_tablespace_footer(&cont, tableinfo.relkind,
1487 tableinfo.tablespace, true);
1494 PGresult *result = NULL;
1496 /* Footer information about a view */
1497 printTableAddFooter(&cont, _("View definition:"));
1498 printTableAddFooter(&cont, view_def);
1501 if (tableinfo.hasrules)
1503 printfPQExpBuffer(&buf,
1504 "SELECT r.rulename, trim(trailing ';' from pg_catalog.pg_get_ruledef(r.oid, true))\n"
1505 "FROM pg_catalog.pg_rewrite r\n"
1506 "WHERE r.ev_class = '%s' AND r.rulename != '_RETURN' ORDER BY 1",
1508 result = PSQLexec(buf.data, false);
1512 if (PQntuples(result) > 0)
1514 printTableAddFooter(&cont, _("Rules:"));
1515 for (i = 0; i < PQntuples(result); i++)
1517 const char *ruledef;
1519 /* Everything after "CREATE RULE" is echoed verbatim */
1520 ruledef = PQgetvalue(result, i, 1);
1523 printfPQExpBuffer(&buf, " %s", ruledef);
1524 printTableAddFooter(&cont, buf.data);
1530 else if (tableinfo.relkind == 'r')
1532 /* Footer information about a table */
1533 PGresult *result = NULL;
1537 if (tableinfo.hasindex)
1539 printfPQExpBuffer(&buf,
1540 "SELECT c2.relname, i.indisprimary, i.indisunique, i.indisclustered, ");
1541 if (pset.sversion >= 80200)
1542 appendPQExpBuffer(&buf, "i.indisvalid, ");
1544 appendPQExpBuffer(&buf, "true as indisvalid, ");
1545 appendPQExpBuffer(&buf, "pg_catalog.pg_get_indexdef(i.indexrelid, 0, true),\n ");
1546 if (pset.sversion >= 90000)
1547 appendPQExpBuffer(&buf,
1548 "pg_catalog.pg_get_constraintdef(con.oid, true), "
1549 "contype, condeferrable, condeferred");
1551 appendPQExpBuffer(&buf,
1552 "null AS constraintdef, null AS contype, "
1553 "false AS condeferrable, false AS condeferred");
1554 if (pset.sversion >= 80000)
1555 appendPQExpBuffer(&buf, ", c2.reltablespace");
1556 appendPQExpBuffer(&buf,
1557 "\nFROM pg_catalog.pg_class c, pg_catalog.pg_class c2, pg_catalog.pg_index i\n");
1558 if (pset.sversion >= 90000)
1559 appendPQExpBuffer(&buf,
1560 " LEFT JOIN pg_catalog.pg_constraint con ON (conrelid = i.indrelid AND conindid = i.indexrelid AND contype IN ('p','u','x'))\n");
1561 appendPQExpBuffer(&buf,
1562 "WHERE c.oid = '%s' AND c.oid = i.indrelid AND i.indexrelid = c2.oid\n"
1563 "ORDER BY i.indisprimary DESC, i.indisunique DESC, c2.relname",
1565 result = PSQLexec(buf.data, false);
1569 tuples = PQntuples(result);
1573 printTableAddFooter(&cont, _("Indexes:"));
1574 for (i = 0; i < tuples; i++)
1576 /* untranslated index name */
1577 printfPQExpBuffer(&buf, " \"%s\"",
1578 PQgetvalue(result, i, 0));
1580 /* If exclusion constraint, print the constraintdef */
1581 if (strcmp(PQgetvalue(result, i, 7), "x") == 0)
1583 appendPQExpBuffer(&buf, " %s",
1584 PQgetvalue(result, i, 6));
1588 const char *indexdef;
1589 const char *usingpos;
1591 /* Label as primary key or unique (but not both) */
1592 if (strcmp(PQgetvalue(result, i, 1), "t") == 0)
1593 appendPQExpBuffer(&buf, " PRIMARY KEY,");
1594 else if (strcmp(PQgetvalue(result, i, 2), "t") == 0)
1595 appendPQExpBuffer(&buf, " UNIQUE,");
1597 /* Everything after "USING" is echoed verbatim */
1598 indexdef = PQgetvalue(result, i, 5);
1599 usingpos = strstr(indexdef, " USING ");
1601 indexdef = usingpos + 7;
1602 appendPQExpBuffer(&buf, " %s", indexdef);
1604 /* Need these for deferrable PK/UNIQUE indexes */
1605 if (strcmp(PQgetvalue(result, i, 8), "t") == 0)
1606 appendPQExpBuffer(&buf, " DEFERRABLE");
1608 if (strcmp(PQgetvalue(result, i, 9), "t") == 0)
1609 appendPQExpBuffer(&buf, " INITIALLY DEFERRED");
1612 /* Add these for all cases */
1613 if (strcmp(PQgetvalue(result, i, 3), "t") == 0)
1614 appendPQExpBuffer(&buf, " CLUSTER");
1616 if (strcmp(PQgetvalue(result, i, 4), "t") != 0)
1617 appendPQExpBuffer(&buf, " INVALID");
1619 printTableAddFooter(&cont, buf.data);
1621 /* Print tablespace of the index on the same line */
1622 if (pset.sversion >= 80000)
1623 add_tablespace_footer(&cont, 'i',
1624 atooid(PQgetvalue(result, i, 10)),
1631 /* print table (and column) check constraints */
1632 if (tableinfo.checks)
1634 printfPQExpBuffer(&buf,
1635 "SELECT r.conname, "
1636 "pg_catalog.pg_get_constraintdef(r.oid, true)\n"
1637 "FROM pg_catalog.pg_constraint r\n"
1638 "WHERE r.conrelid = '%s' AND r.contype = 'c'\nORDER BY 1",
1640 result = PSQLexec(buf.data, false);
1644 tuples = PQntuples(result);
1648 printTableAddFooter(&cont, _("Check constraints:"));
1649 for (i = 0; i < tuples; i++)
1651 /* untranslated contraint name and def */
1652 printfPQExpBuffer(&buf, " \"%s\" %s",
1653 PQgetvalue(result, i, 0),
1654 PQgetvalue(result, i, 1));
1656 printTableAddFooter(&cont, buf.data);
1662 /* print foreign-key constraints (there are none if no triggers) */
1663 if (tableinfo.hastriggers)
1665 printfPQExpBuffer(&buf,
1667 " pg_catalog.pg_get_constraintdef(r.oid, true) as condef\n"
1668 "FROM pg_catalog.pg_constraint r\n"
1669 "WHERE r.conrelid = '%s' AND r.contype = 'f' ORDER BY 1",
1671 result = PSQLexec(buf.data, false);
1675 tuples = PQntuples(result);
1679 printTableAddFooter(&cont, _("Foreign-key constraints:"));
1680 for (i = 0; i < tuples; i++)
1682 /* untranslated constraint name and def */
1683 printfPQExpBuffer(&buf, " \"%s\" %s",
1684 PQgetvalue(result, i, 0),
1685 PQgetvalue(result, i, 1));
1687 printTableAddFooter(&cont, buf.data);
1693 /* print incoming foreign-key references (none if no triggers) */
1694 if (tableinfo.hastriggers)
1696 printfPQExpBuffer(&buf,
1697 "SELECT conname, conrelid::pg_catalog.regclass,\n"
1698 " pg_catalog.pg_get_constraintdef(c.oid, true) as condef\n"
1699 "FROM pg_catalog.pg_constraint c\n"
1700 "WHERE c.confrelid = '%s' AND c.contype = 'f' ORDER BY 1",
1702 result = PSQLexec(buf.data, false);
1706 tuples = PQntuples(result);
1710 printTableAddFooter(&cont, _("Referenced by:"));
1711 for (i = 0; i < tuples; i++)
1713 printfPQExpBuffer(&buf, " TABLE \"%s\" CONSTRAINT \"%s\" %s",
1714 PQgetvalue(result, i, 1),
1715 PQgetvalue(result, i, 0),
1716 PQgetvalue(result, i, 2));
1718 printTableAddFooter(&cont, buf.data);
1725 if (tableinfo.hasrules)
1727 if (pset.sversion >= 80300)
1729 printfPQExpBuffer(&buf,
1730 "SELECT r.rulename, trim(trailing ';' from pg_catalog.pg_get_ruledef(r.oid, true)), "
1732 "FROM pg_catalog.pg_rewrite r\n"
1733 "WHERE r.ev_class = '%s' ORDER BY 1",
1738 printfPQExpBuffer(&buf,
1739 "SELECT r.rulename, trim(trailing ';' from pg_catalog.pg_get_ruledef(r.oid, true)), "
1740 "'O'::char AS ev_enabled\n"
1741 "FROM pg_catalog.pg_rewrite r\n"
1742 "WHERE r.ev_class = '%s' ORDER BY 1",
1745 result = PSQLexec(buf.data, false);
1749 tuples = PQntuples(result);
1756 for (category = 0; category < 4; category++)
1758 have_heading = false;
1760 for (i = 0; i < tuples; i++)
1762 const char *ruledef;
1763 bool list_rule = false;
1768 if (*PQgetvalue(result, i, 2) == 'O')
1772 if (*PQgetvalue(result, i, 2) == 'D')
1776 if (*PQgetvalue(result, i, 2) == 'A')
1780 if (*PQgetvalue(result, i, 2) == 'R')
1792 printfPQExpBuffer(&buf, _("Rules:"));
1795 printfPQExpBuffer(&buf, _("Disabled rules:"));
1798 printfPQExpBuffer(&buf, _("Rules firing always:"));
1801 printfPQExpBuffer(&buf, _("Rules firing on replica only:"));
1804 printTableAddFooter(&cont, buf.data);
1805 have_heading = true;
1808 /* Everything after "CREATE RULE" is echoed verbatim */
1809 ruledef = PQgetvalue(result, i, 1);
1811 printfPQExpBuffer(&buf, " %s", ruledef);
1812 printTableAddFooter(&cont, buf.data);
1819 /* print triggers (but only user-defined triggers) */
1820 if (tableinfo.hastriggers)
1822 printfPQExpBuffer(&buf,
1824 "pg_catalog.pg_get_triggerdef(t.oid%s), "
1826 "FROM pg_catalog.pg_trigger t\n"
1827 "WHERE t.tgrelid = '%s' AND ",
1828 (pset.sversion >= 90000 ? ", true" : ""),
1830 if (pset.sversion >= 90000)
1831 appendPQExpBuffer(&buf, "NOT t.tgisinternal");
1832 else if (pset.sversion >= 80300)
1833 appendPQExpBuffer(&buf, "t.tgconstraint = 0");
1835 appendPQExpBuffer(&buf,
1836 "(NOT tgisconstraint "
1838 " (SELECT 1 FROM pg_catalog.pg_depend d "
1839 " JOIN pg_catalog.pg_constraint c ON (d.refclassid = c.tableoid AND d.refobjid = c.oid) "
1840 " WHERE d.classid = t.tableoid AND d.objid = t.oid AND d.deptype = 'i' AND c.contype = 'f'))");
1841 appendPQExpBuffer(&buf, "\nORDER BY 1");
1843 result = PSQLexec(buf.data, false);
1847 tuples = PQntuples(result);
1855 * split the output into 4 different categories. Enabled
1856 * triggers, disabled triggers and the two special ALWAYS and
1857 * REPLICA configurations.
1859 for (category = 0; category < 4; category++)
1861 have_heading = false;
1862 for (i = 0; i < tuples; i++)
1866 const char *usingpos;
1867 const char *tgenabled;
1870 * Check if this trigger falls into the current
1873 tgenabled = PQgetvalue(result, i, 2);
1874 list_trigger = false;
1878 if (*tgenabled == 'O' || *tgenabled == 't')
1879 list_trigger = true;
1882 if (*tgenabled == 'D' || *tgenabled == 'f')
1883 list_trigger = true;
1886 if (*tgenabled == 'A')
1887 list_trigger = true;
1890 if (*tgenabled == 'R')
1891 list_trigger = true;
1894 if (list_trigger == false)
1897 /* Print the category heading once */
1898 if (have_heading == false)
1903 printfPQExpBuffer(&buf, _("Triggers:"));
1906 printfPQExpBuffer(&buf, _("Disabled triggers:"));
1909 printfPQExpBuffer(&buf, _("Triggers firing always:"));
1912 printfPQExpBuffer(&buf, _("Triggers firing on replica only:"));
1916 printTableAddFooter(&cont, buf.data);
1917 have_heading = true;
1920 /* Everything after "TRIGGER" is echoed verbatim */
1921 tgdef = PQgetvalue(result, i, 1);
1922 usingpos = strstr(tgdef, " TRIGGER ");
1924 tgdef = usingpos + 9;
1926 printfPQExpBuffer(&buf, " %s", tgdef);
1927 printTableAddFooter(&cont, buf.data);
1934 /* print inherited tables */
1935 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);
1937 result = PSQLexec(buf.data, false);
1941 tuples = PQntuples(result);
1943 for (i = 0; i < tuples; i++)
1945 const char *s = _("Inherits");
1948 printfPQExpBuffer(&buf, "%s: %s", s, PQgetvalue(result, i, 0));
1950 printfPQExpBuffer(&buf, "%*s %s", (int) strlen(s), "", PQgetvalue(result, i, 0));
1952 appendPQExpBuffer(&buf, ",");
1954 printTableAddFooter(&cont, buf.data);
1958 /* print child tables */
1959 if (pset.sversion >= 80300)
1960 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);
1962 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);
1964 result = PSQLexec(buf.data, false);
1968 tuples = PQntuples(result);
1972 /* print the number of child tables, if any */
1975 printfPQExpBuffer(&buf, _("Number of child tables: %d (Use \\d+ to list them.)"), tuples);
1976 printTableAddFooter(&cont, buf.data);
1981 /* display the list of child tables */
1982 const char *ct = _("Child tables");
1984 for (i = 0; i < tuples; i++)
1987 printfPQExpBuffer(&buf, "%s: %s",
1988 ct, PQgetvalue(result, i, 0));
1990 printfPQExpBuffer(&buf, "%*s %s",
1991 (int) strlen(ct), "",
1992 PQgetvalue(result, i, 0));
1994 appendPQExpBuffer(&buf, ",");
1996 printTableAddFooter(&cont, buf.data);
2002 if (tableinfo.reloftype)
2004 printfPQExpBuffer(&buf, _("Typed table of type: %s"), tableinfo.reloftype);
2005 printTableAddFooter(&cont, buf.data);
2008 /* OIDs and options */
2011 const char *s = _("Has OIDs");
2013 printfPQExpBuffer(&buf, "%s: %s", s,
2014 (tableinfo.hasoids ? _("yes") : _("no")));
2015 printTableAddFooter(&cont, buf.data);
2017 /* print reloptions */
2018 if (pset.sversion >= 80200)
2020 if (tableinfo.reloptions && tableinfo.reloptions[0] != '\0')
2022 const char *t = _("Options");
2024 printfPQExpBuffer(&buf, "%s: %s", t,
2025 tableinfo.reloptions);
2026 printTableAddFooter(&cont, buf.data);
2031 add_tablespace_footer(&cont, tableinfo.relkind, tableinfo.tablespace,
2035 printTable(&cont, pset.queryFout, pset.logfile);
2036 printTableCleanup(&cont);
2043 if (printTableInitialized)
2044 printTableCleanup(&cont);
2045 termPQExpBuffer(&buf);
2046 termPQExpBuffer(&title);
2047 termPQExpBuffer(&tmpbuf);
2051 for (ptr = seq_values; *ptr; ptr++)
2058 for (ptr = modifiers; *ptr; ptr++)
2073 * Add a tablespace description to a footer. If 'newline' is true, it is added
2074 * in a new line; otherwise it's appended to the current value of the last
2078 add_tablespace_footer(printTableContent *const cont, char relkind,
2079 Oid tablespace, const bool newline)
2081 /* relkinds for which we support tablespaces */
2082 if (relkind == 'r' || relkind == 'i')
2085 * We ignore the database default tablespace so that users not using
2086 * tablespaces don't need to know about them. This case also covers
2087 * pre-8.0 servers, for which tablespace will always be 0.
2089 if (tablespace != 0)
2091 PGresult *result = NULL;
2092 PQExpBufferData buf;
2094 initPQExpBuffer(&buf);
2095 printfPQExpBuffer(&buf,
2096 "SELECT spcname FROM pg_catalog.pg_tablespace\n"
2097 "WHERE oid = '%u'", tablespace);
2098 result = PSQLexec(buf.data, false);
2101 /* Should always be the case, but.... */
2102 if (PQntuples(result) > 0)
2106 /* Add the tablespace as a new footer */
2107 printfPQExpBuffer(&buf, _("Tablespace: \"%s\""),
2108 PQgetvalue(result, 0, 0));
2109 printTableAddFooter(cont, buf.data);
2113 /* Append the tablespace to the latest footer */
2114 printfPQExpBuffer(&buf, "%s", cont->footer->data);
2117 * translator: before this string there's an index
2118 * description like '"foo_pkey" PRIMARY KEY, btree (a)'
2120 appendPQExpBuffer(&buf, _(", tablespace \"%s\""),
2121 PQgetvalue(result, 0, 0));
2122 printTableSetFooter(cont, buf.data);
2126 termPQExpBuffer(&buf);
2134 * Describes roles. Any schema portion of the pattern is ignored.
2137 describeRoles(const char *pattern, bool verbose)
2139 PQExpBufferData buf;
2141 printTableContent cont;
2142 printTableOpt myopt = pset.popt.topt;
2147 const char align = 'l';
2150 initPQExpBuffer(&buf);
2152 if (pset.sversion >= 80100)
2154 printfPQExpBuffer(&buf,
2155 "SELECT r.rolname, r.rolsuper, r.rolinherit,\n"
2156 " r.rolcreaterole, r.rolcreatedb, r.rolcanlogin,\n"
2157 " r.rolconnlimit,\n"
2158 " ARRAY(SELECT b.rolname\n"
2159 " FROM pg_catalog.pg_auth_members m\n"
2160 " JOIN pg_catalog.pg_roles b ON (m.roleid = b.oid)\n"
2161 " WHERE m.member = r.oid) as memberof");
2163 if (verbose && pset.sversion >= 80200)
2165 appendPQExpBufferStr(&buf, "\n, pg_catalog.shobj_description(r.oid, 'pg_authid') AS description");
2169 appendPQExpBufferStr(&buf, "\nFROM pg_catalog.pg_roles r\n");
2171 processSQLNamePattern(pset.db, &buf, pattern, false, false,
2172 NULL, "r.rolname", NULL, NULL);
2176 printfPQExpBuffer(&buf,
2177 "SELECT u.usename AS rolname,\n"
2178 " u.usesuper AS rolsuper,\n"
2179 " true AS rolinherit, false AS rolcreaterole,\n"
2180 " u.usecreatedb AS rolcreatedb, true AS rolcanlogin,\n"
2181 " -1 AS rolconnlimit,\n"
2182 " ARRAY(SELECT g.groname FROM pg_catalog.pg_group g WHERE u.usesysid = ANY(g.grolist)) as memberof"
2183 "\nFROM pg_catalog.pg_user u\n");
2185 processSQLNamePattern(pset.db, &buf, pattern, false, false,
2186 NULL, "u.usename", NULL, NULL);
2189 appendPQExpBuffer(&buf, "ORDER BY 1;");
2191 res = PSQLexec(buf.data, false);
2195 nrows = PQntuples(res);
2196 attr = pg_malloc_zero((nrows + 1) * sizeof(*attr));
2198 printTableInit(&cont, &myopt, _("List of roles"), ncols, nrows);
2200 printTableAddHeader(&cont, gettext_noop("Role name"), true, align);
2201 printTableAddHeader(&cont, gettext_noop("Attributes"), true, align);
2202 printTableAddHeader(&cont, gettext_noop("Member of"), true, align);
2204 if (verbose && pset.sversion >= 80200)
2205 printTableAddHeader(&cont, gettext_noop("Description"), true, align);
2207 for (i = 0; i < nrows; i++)
2209 printTableAddCell(&cont, PQgetvalue(res, i, 0), false, false);
2211 resetPQExpBuffer(&buf);
2212 if (strcmp(PQgetvalue(res, i, 1), "t") == 0)
2213 add_role_attribute(&buf, _("Superuser"));
2215 if (strcmp(PQgetvalue(res, i, 2), "t") != 0)
2216 add_role_attribute(&buf, _("No inheritance"));
2218 if (strcmp(PQgetvalue(res, i, 3), "t") == 0)
2219 add_role_attribute(&buf, _("Create role"));
2221 if (strcmp(PQgetvalue(res, i, 4), "t") == 0)
2222 add_role_attribute(&buf, _("Create DB"));
2224 if (strcmp(PQgetvalue(res, i, 5), "t") != 0)
2225 add_role_attribute(&buf, _("Cannot login"));
2227 conns = atoi(PQgetvalue(res, i, 6));
2231 appendPQExpBufferStr(&buf, "\n");
2234 appendPQExpBuffer(&buf, _("No connections"));
2236 appendPQExpBuffer(&buf, ngettext("%d connection",
2242 attr[i] = pg_strdup(buf.data);
2244 printTableAddCell(&cont, attr[i], false, false);
2246 printTableAddCell(&cont, PQgetvalue(res, i, 7), false, false);
2248 if (verbose && pset.sversion >= 80200)
2249 printTableAddCell(&cont, PQgetvalue(res, i, 8), false, false);
2251 termPQExpBuffer(&buf);
2253 printTable(&cont, pset.queryFout, pset.logfile);
2254 printTableCleanup(&cont);
2256 for (i = 0; i < nrows; i++)
2265 add_role_attribute(PQExpBuffer buf, const char *const str)
2268 appendPQExpBufferStr(buf, ", ");
2270 appendPQExpBufferStr(buf, str);
2277 listDbRoleSettings(const char *pattern, const char *pattern2)
2279 PQExpBufferData buf;
2281 printQueryOpt myopt = pset.popt;
2283 initPQExpBuffer(&buf);
2285 if (pset.sversion >= 90000)
2289 printfPQExpBuffer(&buf, "SELECT rolname AS role, datname AS database,\n"
2290 "pg_catalog.array_to_string(setconfig, E'\\n') AS settings\n"
2291 "FROM pg_db_role_setting AS s\n"
2292 "LEFT JOIN pg_database ON pg_database.oid = setdatabase\n"
2293 "LEFT JOIN pg_roles ON pg_roles.oid = setrole\n");
2294 havewhere = processSQLNamePattern(pset.db, &buf, pattern, false, false,
2295 NULL, "pg_roles.rolname", NULL, NULL);
2296 processSQLNamePattern(pset.db, &buf, pattern2, havewhere, false,
2297 NULL, "pg_database.datname", NULL, NULL);
2298 appendPQExpBufferStr(&buf, "ORDER BY role, database");
2302 fprintf(pset.queryFout,
2303 _("No per-database role settings support in this server version.\n"));
2307 res = PSQLexec(buf.data, false);
2311 if (PQntuples(res) == 0 && !pset.quiet)
2314 fprintf(pset.queryFout, _("No matching settings found.\n"));
2316 fprintf(pset.queryFout, _("No settings found.\n"));
2320 myopt.nullPrint = NULL;
2321 myopt.title = _("List of settings");
2322 myopt.translate_header = true;
2324 printQuery(res, &myopt, pset.queryFout, pset.logfile);
2328 resetPQExpBuffer(&buf);
2336 * handler for \dt, \di, etc.
2338 * tabtypes is an array of characters, specifying what info is desired:
2343 * (any order of the above is fine)
2344 * If tabtypes is empty, we default to \dtvs.
2347 listTables(const char *tabtypes, const char *pattern, bool verbose, bool showSystem)
2349 bool showTables = strchr(tabtypes, 't') != NULL;
2350 bool showIndexes = strchr(tabtypes, 'i') != NULL;
2351 bool showViews = strchr(tabtypes, 'v') != NULL;
2352 bool showSeq = strchr(tabtypes, 's') != NULL;
2354 PQExpBufferData buf;
2356 printQueryOpt myopt = pset.popt;
2357 static const bool translate_columns[] = {false, false, true, false, false, false, false};
2359 if (!(showTables || showIndexes || showViews || showSeq))
2360 showTables = showViews = showSeq = true;
2362 initPQExpBuffer(&buf);
2365 * Note: as of Pg 8.2, we no longer use relkind 's', but we keep it here
2366 * for backwards compatibility.
2368 printfPQExpBuffer(&buf,
2369 "SELECT n.nspname as \"%s\",\n"
2370 " c.relname as \"%s\",\n"
2371 " CASE c.relkind WHEN 'r' THEN '%s' WHEN 'v' THEN '%s' WHEN 'i' THEN '%s' WHEN 'S' THEN '%s' WHEN 's' THEN '%s' END as \"%s\",\n"
2372 " pg_catalog.pg_get_userbyid(c.relowner) as \"%s\"",
2373 gettext_noop("Schema"),
2374 gettext_noop("Name"),
2375 gettext_noop("table"),
2376 gettext_noop("view"),
2377 gettext_noop("index"),
2378 gettext_noop("sequence"),
2379 gettext_noop("special"),
2380 gettext_noop("Type"),
2381 gettext_noop("Owner"));
2384 appendPQExpBuffer(&buf,
2385 ",\n c2.relname as \"%s\"",
2386 gettext_noop("Table"));
2388 if (verbose && pset.sversion >= 80100)
2389 appendPQExpBuffer(&buf,
2390 ",\n pg_catalog.pg_size_pretty(pg_catalog.pg_relation_size(c.oid)) as \"%s\"",
2391 gettext_noop("Size"));
2393 appendPQExpBuffer(&buf,
2394 ",\n pg_catalog.obj_description(c.oid, 'pg_class') as \"%s\"",
2395 gettext_noop("Description"));
2397 appendPQExpBuffer(&buf,
2398 "\nFROM pg_catalog.pg_class c"
2399 "\n LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace");
2401 appendPQExpBuffer(&buf,
2402 "\n LEFT JOIN pg_catalog.pg_index i ON i.indexrelid = c.oid"
2403 "\n LEFT JOIN pg_catalog.pg_class c2 ON i.indrelid = c2.oid");
2405 appendPQExpBuffer(&buf, "\nWHERE c.relkind IN (");
2407 appendPQExpBuffer(&buf, "'r',");
2409 appendPQExpBuffer(&buf, "'v',");
2411 appendPQExpBuffer(&buf, "'i',");
2413 appendPQExpBuffer(&buf, "'S',");
2414 if (showSystem || pattern)
2415 appendPQExpBuffer(&buf, "'s',"); /* was RELKIND_SPECIAL in <=
2417 appendPQExpBuffer(&buf, "''"); /* dummy */
2418 appendPQExpBuffer(&buf, ")\n");
2420 if (!showSystem && !pattern)
2421 appendPQExpBuffer(&buf, " AND n.nspname <> 'pg_catalog'\n"
2422 " AND n.nspname <> 'information_schema'\n");
2425 * TOAST objects are suppressed unconditionally. Since we don't provide
2426 * any way to select relkind 't' above, we would never show toast tables
2427 * in any case; it seems a bit confusing to allow their indexes to be
2428 * shown. Use plain \d if you really need to look at a TOAST table/index.
2430 appendPQExpBuffer(&buf, " AND n.nspname !~ '^pg_toast'\n");
2432 processSQLNamePattern(pset.db, &buf, pattern, true, false,
2433 "n.nspname", "c.relname", NULL,
2434 "pg_catalog.pg_table_is_visible(c.oid)");
2436 appendPQExpBuffer(&buf, "ORDER BY 1,2;");
2438 res = PSQLexec(buf.data, false);
2439 termPQExpBuffer(&buf);
2443 if (PQntuples(res) == 0 && !pset.quiet)
2446 fprintf(pset.queryFout, _("No matching relations found.\n"));
2448 fprintf(pset.queryFout, _("No relations found.\n"));
2452 myopt.nullPrint = NULL;
2453 myopt.title = _("List of relations");
2454 myopt.translate_header = true;
2455 myopt.translate_columns = translate_columns;
2457 printQuery(res, &myopt, pset.queryFout, pset.logfile);
2468 * Describes domains.
2471 listDomains(const char *pattern, bool showSystem)
2473 PQExpBufferData buf;
2475 printQueryOpt myopt = pset.popt;
2477 initPQExpBuffer(&buf);
2479 printfPQExpBuffer(&buf,
2480 "SELECT n.nspname as \"%s\",\n"
2481 " t.typname as \"%s\",\n"
2482 " pg_catalog.format_type(t.typbasetype, t.typtypmod) as \"%s\",\n"
2483 " CASE WHEN t.typnotnull AND t.typdefault IS NOT NULL THEN 'not null default '||t.typdefault\n"
2484 " WHEN t.typnotnull AND t.typdefault IS NULL THEN 'not null'\n"
2485 " WHEN NOT t.typnotnull AND t.typdefault IS NOT NULL THEN 'default '||t.typdefault\n"
2488 " pg_catalog.array_to_string(ARRAY(\n"
2489 " SELECT pg_catalog.pg_get_constraintdef(r.oid, true) FROM pg_catalog.pg_constraint r WHERE t.oid = r.contypid\n"
2490 " ), ' ') as \"%s\"\n"
2491 "FROM pg_catalog.pg_type t\n"
2492 " LEFT JOIN pg_catalog.pg_namespace n ON n.oid = t.typnamespace\n"
2493 "WHERE t.typtype = 'd'\n",
2494 gettext_noop("Schema"),
2495 gettext_noop("Name"),
2496 gettext_noop("Type"),
2497 gettext_noop("Modifier"),
2498 gettext_noop("Check"));
2500 if (!showSystem && !pattern)
2501 appendPQExpBuffer(&buf, " AND n.nspname <> 'pg_catalog'\n"
2502 " AND n.nspname <> 'information_schema'\n");
2504 processSQLNamePattern(pset.db, &buf, pattern, true, false,
2505 "n.nspname", "t.typname", NULL,
2506 "pg_catalog.pg_type_is_visible(t.oid)");
2508 appendPQExpBuffer(&buf, "ORDER BY 1, 2;");
2510 res = PSQLexec(buf.data, false);
2511 termPQExpBuffer(&buf);
2515 myopt.nullPrint = NULL;
2516 myopt.title = _("List of domains");
2517 myopt.translate_header = true;
2519 printQuery(res, &myopt, pset.queryFout, pset.logfile);
2528 * Describes conversions.
2531 listConversions(const char *pattern, bool showSystem)
2533 PQExpBufferData buf;
2535 printQueryOpt myopt = pset.popt;
2536 static const bool translate_columns[] = {false, false, false, false, true};
2538 initPQExpBuffer(&buf);
2540 printfPQExpBuffer(&buf,
2541 "SELECT n.nspname AS \"%s\",\n"
2542 " c.conname AS \"%s\",\n"
2543 " pg_catalog.pg_encoding_to_char(c.conforencoding) AS \"%s\",\n"
2544 " pg_catalog.pg_encoding_to_char(c.contoencoding) AS \"%s\",\n"
2545 " CASE WHEN c.condefault THEN '%s'\n"
2546 " ELSE '%s' END AS \"%s\"\n"
2547 "FROM pg_catalog.pg_conversion c, pg_catalog.pg_namespace n\n"
2548 "WHERE n.oid = c.connamespace\n",
2549 gettext_noop("Schema"),
2550 gettext_noop("Name"),
2551 gettext_noop("Source"),
2552 gettext_noop("Destination"),
2553 gettext_noop("yes"), gettext_noop("no"),
2554 gettext_noop("Default?"));
2556 if (!showSystem && !pattern)
2557 appendPQExpBuffer(&buf, " AND n.nspname <> 'pg_catalog'\n"
2558 " AND n.nspname <> 'information_schema'\n");
2560 processSQLNamePattern(pset.db, &buf, pattern, true, false,
2561 "n.nspname", "c.conname", NULL,
2562 "pg_catalog.pg_conversion_is_visible(c.oid)");
2564 appendPQExpBuffer(&buf, "ORDER BY 1, 2;");
2566 res = PSQLexec(buf.data, false);
2567 termPQExpBuffer(&buf);
2571 myopt.nullPrint = NULL;
2572 myopt.title = _("List of conversions");
2573 myopt.translate_header = true;
2574 myopt.translate_columns = translate_columns;
2576 printQuery(res, &myopt, pset.queryFout, pset.logfile);
2588 listCasts(const char *pattern)
2590 PQExpBufferData buf;
2592 printQueryOpt myopt = pset.popt;
2593 static const bool translate_columns[] = {false, false, false, true};
2595 initPQExpBuffer(&buf);
2598 * We need a left join to pg_proc for binary casts; the others are just
2599 * paranoia. Also note that we don't attempt to localize '(binary
2600 * coercible)', because there's too much risk of gettext translating a
2601 * function name that happens to match some string in the PO database.
2603 printfPQExpBuffer(&buf,
2604 "SELECT pg_catalog.format_type(castsource, NULL) AS \"%s\",\n"
2605 " pg_catalog.format_type(casttarget, NULL) AS \"%s\",\n"
2606 " CASE WHEN castfunc = 0 THEN '(binary coercible)'\n"
2609 " CASE WHEN c.castcontext = 'e' THEN '%s'\n"
2610 " WHEN c.castcontext = 'a' THEN '%s'\n"
2613 "FROM pg_catalog.pg_cast c LEFT JOIN pg_catalog.pg_proc p\n"
2614 " ON c.castfunc = p.oid\n"
2615 " LEFT JOIN pg_catalog.pg_type ts\n"
2616 " ON c.castsource = ts.oid\n"
2617 " LEFT JOIN pg_catalog.pg_namespace ns\n"
2618 " ON ns.oid = ts.typnamespace\n"
2619 " LEFT JOIN pg_catalog.pg_type tt\n"
2620 " ON c.casttarget = tt.oid\n"
2621 " LEFT JOIN pg_catalog.pg_namespace nt\n"
2622 " ON nt.oid = tt.typnamespace\n"
2624 gettext_noop("Source type"),
2625 gettext_noop("Target type"),
2626 gettext_noop("Function"),
2627 gettext_noop("no"), gettext_noop("in assignment"), gettext_noop("yes"),
2628 gettext_noop("Implicit?"));
2631 * Match name pattern against either internal or external name of either
2632 * castsource or casttarget
2634 processSQLNamePattern(pset.db, &buf, pattern, true, false,
2635 "ns.nspname", "ts.typname",
2636 "pg_catalog.format_type(ts.oid, NULL)",
2637 "pg_catalog.pg_type_is_visible(ts.oid)");
2639 appendPQExpBuffer(&buf, ") OR (true");
2641 processSQLNamePattern(pset.db, &buf, pattern, true, false,
2642 "nt.nspname", "tt.typname",
2643 "pg_catalog.format_type(tt.oid, NULL)",
2644 "pg_catalog.pg_type_is_visible(tt.oid)");
2646 appendPQExpBuffer(&buf, ")\nORDER BY 1, 2;");
2648 res = PSQLexec(buf.data, false);
2649 termPQExpBuffer(&buf);
2653 myopt.nullPrint = NULL;
2654 myopt.title = _("List of casts");
2655 myopt.translate_header = true;
2656 myopt.translate_columns = translate_columns;
2658 printQuery(res, &myopt, pset.queryFout, pset.logfile);
2667 * Describes schemas (namespaces)
2670 listSchemas(const char *pattern, bool verbose)
2672 PQExpBufferData buf;
2674 printQueryOpt myopt = pset.popt;
2676 initPQExpBuffer(&buf);
2677 printfPQExpBuffer(&buf,
2678 "SELECT n.nspname AS \"%s\",\n"
2679 " pg_catalog.pg_get_userbyid(n.nspowner) AS \"%s\"",
2680 gettext_noop("Name"),
2681 gettext_noop("Owner"));
2685 appendPQExpBuffer(&buf, ",\n ");
2686 printACLColumn(&buf, "n.nspacl");
2687 appendPQExpBuffer(&buf,
2688 ",\n pg_catalog.obj_description(n.oid, 'pg_namespace') AS \"%s\"",
2689 gettext_noop("Description"));
2692 appendPQExpBuffer(&buf,
2693 "\nFROM pg_catalog.pg_namespace n\n"
2694 "WHERE (n.nspname !~ '^pg_temp_' OR\n"
2695 " n.nspname = (pg_catalog.current_schemas(true))[1])\n"); /* temp schema is first */
2697 processSQLNamePattern(pset.db, &buf, pattern, true, false,
2698 NULL, "n.nspname", NULL,
2701 appendPQExpBuffer(&buf, "ORDER BY 1;");
2703 res = PSQLexec(buf.data, false);
2704 termPQExpBuffer(&buf);
2708 myopt.nullPrint = NULL;
2709 myopt.title = _("List of schemas");
2710 myopt.translate_header = true;
2712 printQuery(res, &myopt, pset.queryFout, pset.logfile);
2721 * list text search parsers
2724 listTSParsers(const char *pattern, bool verbose)
2726 PQExpBufferData buf;
2728 printQueryOpt myopt = pset.popt;
2730 if (pset.sversion < 80300)
2732 fprintf(stderr, _("The server (version %d.%d) does not support full text search.\n"),
2733 pset.sversion / 10000, (pset.sversion / 100) % 100);
2738 return listTSParsersVerbose(pattern);
2740 initPQExpBuffer(&buf);
2742 printfPQExpBuffer(&buf,
2744 " n.nspname as \"%s\",\n"
2745 " p.prsname as \"%s\",\n"
2746 " pg_catalog.obj_description(p.oid, 'pg_ts_parser') as \"%s\"\n"
2747 "FROM pg_catalog.pg_ts_parser p \n"
2748 "LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.prsnamespace\n",
2749 gettext_noop("Schema"),
2750 gettext_noop("Name"),
2751 gettext_noop("Description")
2754 processSQLNamePattern(pset.db, &buf, pattern, false, false,
2755 "n.nspname", "p.prsname", NULL,
2756 "pg_catalog.pg_ts_parser_is_visible(p.oid)");
2758 appendPQExpBuffer(&buf, "ORDER BY 1, 2;");
2760 res = PSQLexec(buf.data, false);
2761 termPQExpBuffer(&buf);
2765 myopt.nullPrint = NULL;
2766 myopt.title = _("List of text search parsers");
2767 myopt.translate_header = true;
2769 printQuery(res, &myopt, pset.queryFout, pset.logfile);
2776 * full description of parsers
2779 listTSParsersVerbose(const char *pattern)
2781 PQExpBufferData buf;
2785 initPQExpBuffer(&buf);
2787 printfPQExpBuffer(&buf,
2791 "FROM pg_catalog.pg_ts_parser p\n"
2792 "LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.prsnamespace\n"
2795 processSQLNamePattern(pset.db, &buf, pattern, false, false,
2796 "n.nspname", "p.prsname", NULL,
2797 "pg_catalog.pg_ts_parser_is_visible(p.oid)");
2799 appendPQExpBuffer(&buf, "ORDER BY 1, 2;");
2801 res = PSQLexec(buf.data, false);
2802 termPQExpBuffer(&buf);
2806 if (PQntuples(res) == 0)
2809 fprintf(stderr, _("Did not find any text search parser named \"%s\".\n"),
2815 for (i = 0; i < PQntuples(res); i++)
2818 const char *nspname = NULL;
2819 const char *prsname;
2821 oid = PQgetvalue(res, i, 0);
2822 if (!PQgetisnull(res, i, 1))
2823 nspname = PQgetvalue(res, i, 1);
2824 prsname = PQgetvalue(res, i, 2);
2826 if (!describeOneTSParser(oid, nspname, prsname))
2844 describeOneTSParser(const char *oid, const char *nspname, const char *prsname)
2846 PQExpBufferData buf;
2849 printQueryOpt myopt = pset.popt;
2850 static const bool translate_columns[] = {true, false, false};
2852 initPQExpBuffer(&buf);
2854 printfPQExpBuffer(&buf,
2855 "SELECT '%s' AS \"%s\", \n"
2856 " p.prsstart::pg_catalog.regproc AS \"%s\", \n"
2857 " pg_catalog.obj_description(p.prsstart, 'pg_proc') as \"%s\" \n"
2858 " FROM pg_catalog.pg_ts_parser p \n"
2859 " WHERE p.oid = '%s' \n"
2862 " p.prstoken::pg_catalog.regproc, \n"
2863 " pg_catalog.obj_description(p.prstoken, 'pg_proc') \n"
2864 " FROM pg_catalog.pg_ts_parser p \n"
2865 " WHERE p.oid = '%s' \n"
2868 " p.prsend::pg_catalog.regproc, \n"
2869 " pg_catalog.obj_description(p.prsend, 'pg_proc') \n"
2870 " FROM pg_catalog.pg_ts_parser p \n"
2871 " WHERE p.oid = '%s' \n"
2874 " p.prsheadline::pg_catalog.regproc, \n"
2875 " pg_catalog.obj_description(p.prsheadline, 'pg_proc') \n"
2876 " FROM pg_catalog.pg_ts_parser p \n"
2877 " WHERE p.oid = '%s' \n"
2880 " p.prslextype::pg_catalog.regproc, \n"
2881 " pg_catalog.obj_description(p.prslextype, 'pg_proc') \n"
2882 " FROM pg_catalog.pg_ts_parser p \n"
2883 " WHERE p.oid = '%s' \n",
2884 gettext_noop("Start parse"),
2885 gettext_noop("Method"),
2886 gettext_noop("Function"),
2887 gettext_noop("Description"),
2889 gettext_noop("Get next token"),
2891 gettext_noop("End parse"),
2893 gettext_noop("Get headline"),
2895 gettext_noop("Get token types"),
2898 res = PSQLexec(buf.data, false);
2899 termPQExpBuffer(&buf);
2903 myopt.nullPrint = NULL;
2905 sprintf(title, _("Text search parser \"%s.%s\""), nspname, prsname);
2907 sprintf(title, _("Text search parser \"%s\""), prsname);
2908 myopt.title = title;
2909 myopt.footers = NULL;
2910 myopt.default_footer = false;
2911 myopt.translate_header = true;
2912 myopt.translate_columns = translate_columns;
2914 printQuery(res, &myopt, pset.queryFout, pset.logfile);
2918 initPQExpBuffer(&buf);
2920 printfPQExpBuffer(&buf,
2921 "SELECT t.alias as \"%s\", \n"
2922 " t.description as \"%s\" \n"
2923 "FROM pg_catalog.ts_token_type( '%s'::pg_catalog.oid ) as t \n"
2925 gettext_noop("Token name"),
2926 gettext_noop("Description"),
2929 res = PSQLexec(buf.data, false);
2930 termPQExpBuffer(&buf);
2934 myopt.nullPrint = NULL;
2936 sprintf(title, _("Token types for parser \"%s.%s\""), nspname, prsname);
2938 sprintf(title, _("Token types for parser \"%s\""), prsname);
2939 myopt.title = title;
2940 myopt.footers = NULL;
2941 myopt.default_footer = true;
2942 myopt.translate_header = true;
2943 myopt.translate_columns = NULL;
2945 printQuery(res, &myopt, pset.queryFout, pset.logfile);
2954 * list text search dictionaries
2957 listTSDictionaries(const char *pattern, bool verbose)
2959 PQExpBufferData buf;
2961 printQueryOpt myopt = pset.popt;
2963 if (pset.sversion < 80300)
2965 fprintf(stderr, _("The server (version %d.%d) does not support full text search.\n"),
2966 pset.sversion / 10000, (pset.sversion / 100) % 100);
2970 initPQExpBuffer(&buf);
2972 printfPQExpBuffer(&buf,
2974 " n.nspname as \"%s\",\n"
2975 " d.dictname as \"%s\",\n",
2976 gettext_noop("Schema"),
2977 gettext_noop("Name"));
2981 appendPQExpBuffer(&buf,
2982 " ( SELECT COALESCE(nt.nspname, '(null)')::pg_catalog.text || '.' || t.tmplname FROM \n"
2983 " pg_catalog.pg_ts_template t \n"
2984 " LEFT JOIN pg_catalog.pg_namespace nt ON nt.oid = t.tmplnamespace \n"
2985 " WHERE d.dicttemplate = t.oid ) AS \"%s\", \n"
2986 " d.dictinitoption as \"%s\", \n",
2987 gettext_noop("Template"),
2988 gettext_noop("Init options"));
2991 appendPQExpBuffer(&buf,
2992 " pg_catalog.obj_description(d.oid, 'pg_ts_dict') as \"%s\"\n",
2993 gettext_noop("Description"));
2995 appendPQExpBuffer(&buf, "FROM pg_catalog.pg_ts_dict d\n"
2996 "LEFT JOIN pg_catalog.pg_namespace n ON n.oid = d.dictnamespace\n");
2998 processSQLNamePattern(pset.db, &buf, pattern, false, false,
2999 "n.nspname", "d.dictname", NULL,
3000 "pg_catalog.pg_ts_dict_is_visible(d.oid)");
3002 appendPQExpBuffer(&buf, "ORDER BY 1, 2;");
3004 res = PSQLexec(buf.data, false);
3005 termPQExpBuffer(&buf);
3009 myopt.nullPrint = NULL;
3010 myopt.title = _("List of text search dictionaries");
3011 myopt.translate_header = true;
3013 printQuery(res, &myopt, pset.queryFout, pset.logfile);
3022 * list text search templates
3025 listTSTemplates(const char *pattern, bool verbose)
3027 PQExpBufferData buf;
3029 printQueryOpt myopt = pset.popt;
3031 if (pset.sversion < 80300)
3033 fprintf(stderr, _("The server (version %d.%d) does not support full text search.\n"),
3034 pset.sversion / 10000, (pset.sversion / 100) % 100);
3038 initPQExpBuffer(&buf);
3041 printfPQExpBuffer(&buf,
3043 " n.nspname AS \"%s\",\n"
3044 " t.tmplname AS \"%s\",\n"
3045 " t.tmplinit::pg_catalog.regproc AS \"%s\",\n"
3046 " t.tmpllexize::pg_catalog.regproc AS \"%s\",\n"
3047 " pg_catalog.obj_description(t.oid, 'pg_ts_template') AS \"%s\"\n",
3048 gettext_noop("Schema"),
3049 gettext_noop("Name"),
3050 gettext_noop("Init"),
3051 gettext_noop("Lexize"),
3052 gettext_noop("Description"));
3054 printfPQExpBuffer(&buf,
3056 " n.nspname AS \"%s\",\n"
3057 " t.tmplname AS \"%s\",\n"
3058 " pg_catalog.obj_description(t.oid, 'pg_ts_template') AS \"%s\"\n",
3059 gettext_noop("Schema"),
3060 gettext_noop("Name"),
3061 gettext_noop("Description"));
3063 appendPQExpBuffer(&buf, "FROM pg_catalog.pg_ts_template t\n"
3064 "LEFT JOIN pg_catalog.pg_namespace n ON n.oid = t.tmplnamespace\n");
3066 processSQLNamePattern(pset.db, &buf, pattern, false, false,
3067 "n.nspname", "t.tmplname", NULL,
3068 "pg_catalog.pg_ts_template_is_visible(t.oid)");
3070 appendPQExpBuffer(&buf, "ORDER BY 1, 2;");
3072 res = PSQLexec(buf.data, false);
3073 termPQExpBuffer(&buf);
3077 myopt.nullPrint = NULL;
3078 myopt.title = _("List of text search templates");
3079 myopt.translate_header = true;
3081 printQuery(res, &myopt, pset.queryFout, pset.logfile);
3090 * list text search configurations
3093 listTSConfigs(const char *pattern, bool verbose)
3095 PQExpBufferData buf;
3097 printQueryOpt myopt = pset.popt;
3099 if (pset.sversion < 80300)
3101 fprintf(stderr, _("The server (version %d.%d) does not support full text search.\n"),
3102 pset.sversion / 10000, (pset.sversion / 100) % 100);
3107 return listTSConfigsVerbose(pattern);
3109 initPQExpBuffer(&buf);
3111 printfPQExpBuffer(&buf,
3113 " n.nspname as \"%s\",\n"
3114 " c.cfgname as \"%s\",\n"
3115 " pg_catalog.obj_description(c.oid, 'pg_ts_config') as \"%s\"\n"
3116 "FROM pg_catalog.pg_ts_config c\n"
3117 "LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.cfgnamespace \n",
3118 gettext_noop("Schema"),
3119 gettext_noop("Name"),
3120 gettext_noop("Description")
3123 processSQLNamePattern(pset.db, &buf, pattern, false, false,
3124 "n.nspname", "c.cfgname", NULL,
3125 "pg_catalog.pg_ts_config_is_visible(c.oid)");
3127 appendPQExpBuffer(&buf, "ORDER BY 1, 2;");
3129 res = PSQLexec(buf.data, false);
3130 termPQExpBuffer(&buf);
3134 myopt.nullPrint = NULL;
3135 myopt.title = _("List of text search configurations");
3136 myopt.translate_header = true;
3138 printQuery(res, &myopt, pset.queryFout, pset.logfile);
3145 listTSConfigsVerbose(const char *pattern)
3147 PQExpBufferData buf;
3151 initPQExpBuffer(&buf);
3153 printfPQExpBuffer(&buf,
3154 "SELECT c.oid, c.cfgname,\n"
3157 " np.nspname as pnspname \n"
3158 "FROM pg_catalog.pg_ts_config c \n"
3159 " LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.cfgnamespace, \n"
3160 " pg_catalog.pg_ts_parser p \n"
3161 " LEFT JOIN pg_catalog.pg_namespace np ON np.oid = p.prsnamespace \n"
3162 "WHERE p.oid = c.cfgparser\n"
3165 processSQLNamePattern(pset.db, &buf, pattern, true, false,
3166 "n.nspname", "c.cfgname", NULL,
3167 "pg_catalog.pg_ts_config_is_visible(c.oid)");
3169 appendPQExpBuffer(&buf, "ORDER BY 3, 2;");
3171 res = PSQLexec(buf.data, false);
3172 termPQExpBuffer(&buf);
3176 if (PQntuples(res) == 0)
3179 fprintf(stderr, _("Did not find any text search configuration named \"%s\".\n"),
3185 for (i = 0; i < PQntuples(res); i++)
3188 const char *cfgname;
3189 const char *nspname = NULL;
3190 const char *prsname;
3191 const char *pnspname = NULL;
3193 oid = PQgetvalue(res, i, 0);
3194 cfgname = PQgetvalue(res, i, 1);
3195 if (!PQgetisnull(res, i, 2))
3196 nspname = PQgetvalue(res, i, 2);
3197 prsname = PQgetvalue(res, i, 3);
3198 if (!PQgetisnull(res, i, 4))
3199 pnspname = PQgetvalue(res, i, 4);
3201 if (!describeOneTSConfig(oid, nspname, cfgname, pnspname, prsname))
3219 describeOneTSConfig(const char *oid, const char *nspname, const char *cfgname,
3220 const char *pnspname, const char *prsname)
3222 PQExpBufferData buf,
3225 printQueryOpt myopt = pset.popt;
3227 initPQExpBuffer(&buf);
3229 printfPQExpBuffer(&buf,
3231 " ( SELECT t.alias FROM \n"
3232 " pg_catalog.ts_token_type(c.cfgparser) AS t \n"
3233 " WHERE t.tokid = m.maptokentype ) AS \"%s\", \n"
3234 " pg_catalog.btrim( \n"
3235 " ARRAY( SELECT mm.mapdict::pg_catalog.regdictionary \n"
3236 " FROM pg_catalog.pg_ts_config_map AS mm \n"
3237 " WHERE mm.mapcfg = m.mapcfg AND mm.maptokentype = m.maptokentype \n"
3238 " ORDER BY mapcfg, maptokentype, mapseqno \n"
3239 " ) :: pg_catalog.text , \n"
3240 " '{}') AS \"%s\" \n"
3241 "FROM pg_catalog.pg_ts_config AS c, pg_catalog.pg_ts_config_map AS m \n"
3242 "WHERE c.oid = '%s' AND m.mapcfg = c.oid \n"
3243 "GROUP BY m.mapcfg, m.maptokentype, c.cfgparser \n"
3245 gettext_noop("Token"),
3246 gettext_noop("Dictionaries"),
3249 res = PSQLexec(buf.data, false);
3250 termPQExpBuffer(&buf);
3254 initPQExpBuffer(&title);
3257 appendPQExpBuffer(&title, _("Text search configuration \"%s.%s\""),
3260 appendPQExpBuffer(&title, _("Text search configuration \"%s\""),
3264 appendPQExpBuffer(&title, _("\nParser: \"%s.%s\""),
3267 appendPQExpBuffer(&title, _("\nParser: \"%s\""),
3270 myopt.nullPrint = NULL;
3271 myopt.title = title.data;
3272 myopt.footers = NULL;
3273 myopt.default_footer = false;
3274 myopt.translate_header = true;
3276 printQuery(res, &myopt, pset.queryFout, pset.logfile);
3278 termPQExpBuffer(&title);
3288 * Describes foreign-data wrappers
3291 listForeignDataWrappers(const char *pattern, bool verbose)
3293 PQExpBufferData buf;
3295 printQueryOpt myopt = pset.popt;
3297 if (pset.sversion < 80400)
3299 fprintf(stderr, _("The server (version %d.%d) does not support foreign-data wrappers.\n"),
3300 pset.sversion / 10000, (pset.sversion / 100) % 100);
3304 initPQExpBuffer(&buf);
3305 printfPQExpBuffer(&buf,
3306 "SELECT fdwname AS \"%s\",\n"
3307 " pg_catalog.pg_get_userbyid(fdwowner) AS \"%s\",\n"
3308 " fdwvalidator::pg_catalog.regproc AS \"%s\"",
3309 gettext_noop("Name"),
3310 gettext_noop("Owner"),
3311 gettext_noop("Validator"));
3315 appendPQExpBuffer(&buf, ",\n ");
3316 printACLColumn(&buf, "fdwacl");
3317 appendPQExpBuffer(&buf,
3318 ",\n fdwoptions AS \"%s\"",
3319 gettext_noop("Options"));
3322 appendPQExpBuffer(&buf, "\nFROM pg_catalog.pg_foreign_data_wrapper\n");
3324 processSQLNamePattern(pset.db, &buf, pattern, false, false,
3325 NULL, "fdwname", NULL, NULL);
3327 appendPQExpBuffer(&buf, "ORDER BY 1;");
3329 res = PSQLexec(buf.data, false);
3330 termPQExpBuffer(&buf);
3334 myopt.nullPrint = NULL;
3335 myopt.title = _("List of foreign-data wrappers");
3336 myopt.translate_header = true;
3338 printQuery(res, &myopt, pset.queryFout, pset.logfile);
3347 * Describes foreign servers.
3350 listForeignServers(const char *pattern, bool verbose)
3352 PQExpBufferData buf;
3354 printQueryOpt myopt = pset.popt;
3356 if (pset.sversion < 80400)
3358 fprintf(stderr, _("The server (version %d.%d) does not support foreign servers.\n"),
3359 pset.sversion / 10000, (pset.sversion / 100) % 100);
3363 initPQExpBuffer(&buf);
3364 printfPQExpBuffer(&buf,
3365 "SELECT s.srvname AS \"%s\",\n"
3366 " pg_catalog.pg_get_userbyid(s.srvowner) AS \"%s\",\n"
3367 " f.fdwname AS \"%s\"",
3368 gettext_noop("Name"),
3369 gettext_noop("Owner"),
3370 gettext_noop("Foreign-data wrapper"));
3374 appendPQExpBuffer(&buf, ",\n ");
3375 printACLColumn(&buf, "s.srvacl");
3376 appendPQExpBuffer(&buf,
3378 " s.srvtype AS \"%s\",\n"
3379 " s.srvversion AS \"%s\",\n"
3380 " s.srvoptions AS \"%s\"",
3381 gettext_noop("Type"),
3382 gettext_noop("Version"),
3383 gettext_noop("Options"));
3386 appendPQExpBuffer(&buf,
3387 "\nFROM pg_catalog.pg_foreign_server s\n"
3388 " JOIN pg_catalog.pg_foreign_data_wrapper f ON f.oid=s.srvfdw\n");
3390 processSQLNamePattern(pset.db, &buf, pattern, false, false,
3391 NULL, "s.srvname", NULL, NULL);
3393 appendPQExpBuffer(&buf, "ORDER BY 1;");
3395 res = PSQLexec(buf.data, false);
3396 termPQExpBuffer(&buf);
3400 myopt.nullPrint = NULL;
3401 myopt.title = _("List of foreign servers");
3402 myopt.translate_header = true;
3404 printQuery(res, &myopt, pset.queryFout, pset.logfile);
3413 * Describes user mappings.
3416 listUserMappings(const char *pattern, bool verbose)
3418 PQExpBufferData buf;
3420 printQueryOpt myopt = pset.popt;
3422 if (pset.sversion < 80400)
3424 fprintf(stderr, _("The server (version %d.%d) does not support user mappings.\n"),
3425 pset.sversion / 10000, (pset.sversion / 100) % 100);
3429 initPQExpBuffer(&buf);
3430 printfPQExpBuffer(&buf,
3431 "SELECT um.srvname AS \"%s\",\n"
3432 " um.usename AS \"%s\"",
3433 gettext_noop("Server"),
3434 gettext_noop("User name"));
3437 appendPQExpBuffer(&buf,
3438 ",\n um.umoptions AS \"%s\"",
3439 gettext_noop("Options"));
3441 appendPQExpBuffer(&buf, "\nFROM pg_catalog.pg_user_mappings um\n");
3443 processSQLNamePattern(pset.db, &buf, pattern, false, false,
3444 NULL, "um.srvname", "um.usename", NULL);
3446 appendPQExpBuffer(&buf, "ORDER BY 1, 2;");
3448 res = PSQLexec(buf.data, false);
3449 termPQExpBuffer(&buf);
3453 myopt.nullPrint = NULL;
3454 myopt.title = _("List of user mappings");
3455 myopt.translate_header = true;
3457 printQuery(res, &myopt, pset.queryFout, pset.logfile);
3466 * Helper function for consistently formatting ACL (privilege) columns.
3467 * The proper targetlist entry is appended to buf. Note lack of any
3468 * whitespace or comma decoration.
3471 printACLColumn(PQExpBuffer buf, const char *colname)
3473 if (pset.sversion >= 80100)
3474 appendPQExpBuffer(buf,
3475 "pg_catalog.array_to_string(%s, E'\\n') AS \"%s\"",
3476 colname, gettext_noop("Access privileges"));
3478 appendPQExpBuffer(buf,
3479 "pg_catalog.array_to_string(%s, '\\n') AS \"%s\"",
3480 colname, gettext_noop("Access privileges"));