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-2009, PostgreSQL Global Development Group
11 * $PostgreSQL: pgsql/src/bin/psql/describe.c,v 1.203 2009/03/26 22:26:07 petere 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"));
98 appendPQExpBuffer(&buf, " AND n.nspname <> 'pg_catalog'\n");
100 processSQLNamePattern(pset.db, &buf, pattern, true, false,
101 "n.nspname", "p.proname", NULL,
102 "pg_catalog.pg_function_is_visible(p.oid)");
104 appendPQExpBuffer(&buf, "ORDER BY 1, 2, 4;");
106 res = PSQLexec(buf.data, false);
107 termPQExpBuffer(&buf);
111 myopt.nullPrint = NULL;
112 myopt.title = _("List of aggregate functions");
113 myopt.translate_header = true;
115 printQuery(res, &myopt, pset.queryFout, pset.logfile);
122 * Takes an optional regexp to select particular tablespaces
125 describeTablespaces(const char *pattern, bool verbose)
129 printQueryOpt myopt = pset.popt;
131 if (pset.sversion < 80000)
133 fprintf(stderr, _("The server (version %d.%d) does not support tablespaces.\n"),
134 pset.sversion / 10000, (pset.sversion / 100) % 100);
138 initPQExpBuffer(&buf);
140 printfPQExpBuffer(&buf,
141 "SELECT spcname AS \"%s\",\n"
142 " pg_catalog.pg_get_userbyid(spcowner) AS \"%s\",\n"
143 " spclocation AS \"%s\"",
144 gettext_noop("Name"),
145 gettext_noop("Owner"),
146 gettext_noop("Location"));
150 appendPQExpBuffer(&buf, ",\n ");
151 printACLColumn(&buf, "spcacl");
154 if (verbose && pset.sversion >= 80200)
155 appendPQExpBuffer(&buf,
156 ",\n pg_catalog.shobj_description(oid, 'pg_tablespace') AS \"%s\"",
157 gettext_noop("Description"));
159 appendPQExpBuffer(&buf,
160 "\nFROM pg_catalog.pg_tablespace\n");
162 processSQLNamePattern(pset.db, &buf, pattern, false, false,
163 NULL, "spcname", NULL,
166 appendPQExpBuffer(&buf, "ORDER BY 1;");
168 res = PSQLexec(buf.data, false);
169 termPQExpBuffer(&buf);
173 myopt.nullPrint = NULL;
174 myopt.title = _("List of tablespaces");
175 myopt.translate_header = true;
177 printQuery(res, &myopt, pset.queryFout, pset.logfile);
185 * Takes an optional regexp to select particular functions
188 describeFunctions(const char *pattern, bool verbose, bool showSystem)
192 printQueryOpt myopt = pset.popt;
194 initPQExpBuffer(&buf);
196 printfPQExpBuffer(&buf,
197 "SELECT n.nspname as \"%s\",\n"
198 " p.proname as \"%s\",\n",
199 gettext_noop("Schema"),
200 gettext_noop("Name"));
202 if (pset.sversion >= 80400)
203 appendPQExpBuffer(&buf,
204 " pg_catalog.pg_get_function_result(p.oid) as \"%s\",\n"
205 " pg_catalog.pg_get_function_arguments(p.oid) as \"%s\"",
206 gettext_noop("Result data type"),
207 gettext_noop("Argument data types"));
208 else if (pset.sversion >= 80100)
209 appendPQExpBuffer(&buf,
210 " CASE WHEN p.proretset THEN 'SETOF ' ELSE '' END ||\n"
211 " pg_catalog.format_type(p.prorettype, NULL) as \"%s\",\n"
212 " CASE WHEN proallargtypes IS NOT NULL THEN\n"
213 " pg_catalog.array_to_string(ARRAY(\n"
216 " WHEN p.proargmodes[s.i] = 'i' THEN ''\n"
217 " WHEN p.proargmodes[s.i] = 'o' THEN 'OUT '\n"
218 " WHEN p.proargmodes[s.i] = 'b' THEN 'INOUT '\n"
219 " WHEN p.proargmodes[s.i] = 'v' THEN 'VARIADIC '\n"
222 " WHEN COALESCE(p.proargnames[s.i], '') = '' THEN ''\n"
223 " ELSE p.proargnames[s.i] || ' ' \n"
225 " pg_catalog.format_type(p.proallargtypes[s.i], NULL)\n"
227 " pg_catalog.generate_series(1, pg_catalog.array_upper(p.proallargtypes, 1)) AS s(i)\n"
230 " pg_catalog.array_to_string(ARRAY(\n"
233 " WHEN COALESCE(p.proargnames[s.i+1], '') = '' THEN ''\n"
234 " ELSE p.proargnames[s.i+1] || ' '\n"
236 " pg_catalog.format_type(p.proargtypes[s.i], NULL)\n"
238 " pg_catalog.generate_series(0, pg_catalog.array_upper(p.proargtypes, 1)) AS s(i)\n"
241 gettext_noop("Result data type"),
242 gettext_noop("Argument data types"));
244 appendPQExpBuffer(&buf,
245 " CASE WHEN p.proretset THEN 'SETOF ' ELSE '' END ||\n"
246 " pg_catalog.format_type(p.prorettype, NULL) as \"%s\",\n"
247 " pg_catalog.oidvectortypes(p.proargtypes) as \"%s\"",
248 gettext_noop("Result data type"),
249 gettext_noop("Argument data types"));
252 appendPQExpBuffer(&buf,
254 " WHEN p.provolatile = 'i' THEN 'immutable'\n"
255 " WHEN p.provolatile = 's' THEN 'stable'\n"
256 " WHEN p.provolatile = 'v' THEN 'volatile'\n"
258 ",\n pg_catalog.pg_get_userbyid(p.proowner) as \"%s\",\n"
259 " l.lanname as \"%s\",\n"
260 " p.prosrc as \"%s\",\n"
261 " pg_catalog.obj_description(p.oid, 'pg_proc') as \"%s\"",
262 gettext_noop("Volatility"),
263 gettext_noop("Owner"),
264 gettext_noop("Language"),
265 gettext_noop("Source code"),
266 gettext_noop("Description"));
268 appendPQExpBuffer(&buf,
269 "\nFROM pg_catalog.pg_proc p"
270 "\n LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace\n");
273 appendPQExpBuffer(&buf,
274 " LEFT JOIN pg_catalog.pg_language l ON l.oid = p.prolang\n");
277 * we skip in/out funcs by excluding functions that take or return cstring
279 appendPQExpBuffer(&buf,
280 "WHERE p.prorettype <> 'pg_catalog.cstring'::pg_catalog.regtype\n"
281 " AND p.proargtypes[0] IS DISTINCT FROM 'pg_catalog.cstring'::pg_catalog.regtype\n"
282 " AND NOT p.proisagg\n");
285 appendPQExpBuffer(&buf, " AND n.nspname <> 'pg_catalog'\n");
287 processSQLNamePattern(pset.db, &buf, pattern, true, false,
288 "n.nspname", "p.proname", NULL,
289 "pg_catalog.pg_function_is_visible(p.oid)");
291 appendPQExpBuffer(&buf, "ORDER BY 1, 2, 4;");
293 res = PSQLexec(buf.data, false);
294 termPQExpBuffer(&buf);
298 myopt.nullPrint = NULL;
299 myopt.title = _("List of functions");
300 myopt.translate_header = true;
302 printQuery(res, &myopt, pset.queryFout, pset.logfile);
315 describeTypes(const char *pattern, bool verbose, bool showSystem)
319 printQueryOpt myopt = pset.popt;
321 initPQExpBuffer(&buf);
323 printfPQExpBuffer(&buf,
324 "SELECT n.nspname as \"%s\",\n"
325 " pg_catalog.format_type(t.oid, NULL) AS \"%s\",\n",
326 gettext_noop("Schema"),
327 gettext_noop("Name"));
329 appendPQExpBuffer(&buf,
330 " t.typname AS \"%s\",\n"
331 " CASE WHEN t.typrelid != 0\n"
332 " THEN CAST('tuple' AS pg_catalog.text)\n"
333 " WHEN t.typlen < 0\n"
334 " THEN CAST('var' AS pg_catalog.text)\n"
335 " ELSE CAST(t.typlen AS pg_catalog.text)\n"
337 gettext_noop("Internal name"),
338 gettext_noop("Size"));
339 if (verbose && pset.sversion >= 80300)
340 appendPQExpBuffer(&buf,
341 " pg_catalog.array_to_string(\n"
343 " SELECT e.enumlabel\n"
344 " FROM pg_catalog.pg_enum e\n"
345 " WHERE e.enumtypid = t.oid\n"
350 gettext_noop("Elements"));
352 appendPQExpBuffer(&buf,
353 " pg_catalog.obj_description(t.oid, 'pg_type') as \"%s\"\n",
354 gettext_noop("Description"));
356 appendPQExpBuffer(&buf, "FROM pg_catalog.pg_type t\n"
357 " LEFT JOIN pg_catalog.pg_namespace n ON n.oid = t.typnamespace\n");
360 * do not include complex types (typrelid!=0) unless they are standalone
363 appendPQExpBuffer(&buf, "WHERE (t.typrelid = 0 ");
364 appendPQExpBuffer(&buf, "OR (SELECT c.relkind = 'c' FROM pg_catalog.pg_class c "
365 "WHERE c.oid = t.typrelid))\n");
367 * do not include array types (before 8.3 we have to use the assumption
368 * that their names start with underscore)
370 if (pset.sversion >= 80300)
371 appendPQExpBuffer(&buf, " AND NOT EXISTS(SELECT 1 FROM pg_catalog.pg_type el WHERE el.oid = t.typelem AND el.typarray = t.oid)\n");
373 appendPQExpBuffer(&buf, " AND t.typname !~ '^_'\n");
376 appendPQExpBuffer(&buf, " AND n.nspname <> 'pg_catalog'\n");
378 /* Match name pattern against either internal or external name */
379 processSQLNamePattern(pset.db, &buf, pattern, true, false,
380 "n.nspname", "t.typname",
381 "pg_catalog.format_type(t.oid, NULL)",
382 "pg_catalog.pg_type_is_visible(t.oid)");
384 appendPQExpBuffer(&buf, "ORDER BY 1, 2;");
386 res = PSQLexec(buf.data, false);
387 termPQExpBuffer(&buf);
391 myopt.nullPrint = NULL;
392 myopt.title = _("List of data types");
393 myopt.translate_header = true;
395 printQuery(res, &myopt, pset.queryFout, pset.logfile);
405 describeOperators(const char *pattern, bool showSystem)
409 printQueryOpt myopt = pset.popt;
411 initPQExpBuffer(&buf);
413 printfPQExpBuffer(&buf,
414 "SELECT n.nspname as \"%s\",\n"
415 " o.oprname AS \"%s\",\n"
416 " CASE WHEN o.oprkind='l' THEN NULL ELSE pg_catalog.format_type(o.oprleft, NULL) END AS \"%s\",\n"
417 " CASE WHEN o.oprkind='r' THEN NULL ELSE pg_catalog.format_type(o.oprright, NULL) END AS \"%s\",\n"
418 " pg_catalog.format_type(o.oprresult, NULL) AS \"%s\",\n"
419 " coalesce(pg_catalog.obj_description(o.oid, 'pg_operator'),\n"
420 " pg_catalog.obj_description(o.oprcode, 'pg_proc')) AS \"%s\"\n"
421 "FROM pg_catalog.pg_operator o\n"
422 " LEFT JOIN pg_catalog.pg_namespace n ON n.oid = o.oprnamespace\n",
423 gettext_noop("Schema"),
424 gettext_noop("Name"),
425 gettext_noop("Left arg type"),
426 gettext_noop("Right arg type"),
427 gettext_noop("Result type"),
428 gettext_noop("Description"));
431 appendPQExpBuffer(&buf, " WHERE n.nspname <> 'pg_catalog'\n");
433 processSQLNamePattern(pset.db, &buf, pattern, !showSystem, true,
434 "n.nspname", "o.oprname", NULL,
435 "pg_catalog.pg_operator_is_visible(o.oid)");
437 appendPQExpBuffer(&buf, "ORDER BY 1, 2, 3, 4;");
439 res = PSQLexec(buf.data, false);
440 termPQExpBuffer(&buf);
444 myopt.nullPrint = NULL;
445 myopt.title = _("List of operators");
446 myopt.translate_header = true;
448 printQuery(res, &myopt, pset.queryFout, pset.logfile);
458 * for \l, \list, and -l switch
461 listAllDbs(bool verbose)
465 printQueryOpt myopt = pset.popt;
467 initPQExpBuffer(&buf);
469 printfPQExpBuffer(&buf,
470 "SELECT d.datname as \"%s\",\n"
471 " pg_catalog.pg_get_userbyid(d.datdba) as \"%s\",\n"
472 " pg_catalog.pg_encoding_to_char(d.encoding) as \"%s\",\n",
473 gettext_noop("Name"),
474 gettext_noop("Owner"),
475 gettext_noop("Encoding"));
476 if (pset.sversion >= 80400)
477 appendPQExpBuffer(&buf,
478 " d.datcollate as \"%s\",\n"
479 " d.datctype as \"%s\",\n",
480 gettext_noop("Collation"),
481 gettext_noop("Ctype"));
482 appendPQExpBuffer(&buf, " ");
483 printACLColumn(&buf, "d.datacl");
484 if (verbose && pset.sversion >= 80200)
485 appendPQExpBuffer(&buf,
486 ",\n CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT')\n"
487 " THEN pg_catalog.pg_size_pretty(pg_catalog.pg_database_size(d.datname))\n"
488 " ELSE 'No Access'\n"
490 gettext_noop("Size"));
491 if (verbose && pset.sversion >= 80000)
492 appendPQExpBuffer(&buf,
493 ",\n t.spcname as \"%s\"",
494 gettext_noop("Tablespace"));
495 if (verbose && pset.sversion >= 80200)
496 appendPQExpBuffer(&buf,
497 ",\n pg_catalog.shobj_description(d.oid, 'pg_database') as \"%s\"",
498 gettext_noop("Description"));
499 appendPQExpBuffer(&buf,
500 "\nFROM pg_catalog.pg_database d\n");
501 if (verbose && pset.sversion >= 80000)
502 appendPQExpBuffer(&buf,
503 " JOIN pg_catalog.pg_tablespace t on d.dattablespace = t.oid\n");
504 appendPQExpBuffer(&buf, "ORDER BY 1;");
505 res = PSQLexec(buf.data, false);
506 termPQExpBuffer(&buf);
510 myopt.nullPrint = NULL;
511 myopt.title = _("List of databases");
512 myopt.translate_header = true;
514 printQuery(res, &myopt, pset.queryFout, pset.logfile);
522 * List Tables' Grant/Revoke Permissions
523 * \z (now also \dp -- perhaps more mnemonic)
526 permissionsList(const char *pattern)
530 printQueryOpt myopt = pset.popt;
531 static const bool translate_columns[] = {false, false, true, false, false};
533 initPQExpBuffer(&buf);
536 * we ignore indexes and toast tables since they have no meaningful rights
538 printfPQExpBuffer(&buf,
539 "SELECT n.nspname as \"%s\",\n"
540 " c.relname as \"%s\",\n"
541 " CASE c.relkind WHEN 'r' THEN '%s' WHEN 'v' THEN '%s' WHEN 'S' THEN '%s' END as \"%s\",\n"
543 gettext_noop("Schema"),
544 gettext_noop("Name"),
545 gettext_noop("table"), gettext_noop("view"), gettext_noop("sequence"),
546 gettext_noop("Type"));
548 printACLColumn(&buf, "c.relacl");
550 if (pset.sversion >= 80400)
551 appendPQExpBuffer(&buf,
552 ",\n pg_catalog.array_to_string(ARRAY(\n"
553 " SELECT attname || E':\\n ' || pg_catalog.array_to_string(attacl, E'\\n ')\n"
554 " FROM pg_catalog.pg_attribute a\n"
555 " WHERE attrelid = c.oid AND NOT attisdropped AND attacl IS NOT NULL\n"
556 " ), E'\\n') AS \"%s\"",
557 gettext_noop("Column access privileges"));
559 appendPQExpBuffer(&buf, "\nFROM pg_catalog.pg_class c\n"
560 " LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace\n"
561 "WHERE c.relkind IN ('r', 'v', 'S')\n");
564 * Unless a schema pattern is specified, we suppress system and temp
565 * tables, since they normally aren't very interesting from a permissions
566 * point of view. You can see 'em by explicit request though, eg with \z
569 processSQLNamePattern(pset.db, &buf, pattern, true, false,
570 "n.nspname", "c.relname", NULL,
571 "n.nspname !~ '^pg_' AND pg_catalog.pg_table_is_visible(c.oid)");
573 appendPQExpBuffer(&buf, "ORDER BY 1, 2;");
575 res = PSQLexec(buf.data, false);
578 termPQExpBuffer(&buf);
582 myopt.nullPrint = NULL;
583 printfPQExpBuffer(&buf, _("Access privileges"));
584 myopt.title = buf.data;
585 myopt.translate_header = true;
586 myopt.translate_columns = translate_columns;
588 printQuery(res, &myopt, pset.queryFout, pset.logfile);
590 termPQExpBuffer(&buf);
598 * Get object comments
602 * Note: This only lists things that actually have a description. For complete
603 * lists of things, there are other \d? commands.
606 objectDescription(const char *pattern, bool showSystem)
610 printQueryOpt myopt = pset.popt;
611 static const bool translate_columns[] = {false, false, true, false};
613 initPQExpBuffer(&buf);
615 appendPQExpBuffer(&buf,
616 "SELECT DISTINCT tt.nspname AS \"%s\", tt.name AS \"%s\", tt.object AS \"%s\", d.description AS \"%s\"\n"
618 gettext_noop("Schema"),
619 gettext_noop("Name"),
620 gettext_noop("Object"),
621 gettext_noop("Description"));
623 /* Aggregate descriptions */
624 appendPQExpBuffer(&buf,
625 " SELECT p.oid as oid, p.tableoid as tableoid,\n"
626 " n.nspname as nspname,\n"
627 " CAST(p.proname AS pg_catalog.text) as name,"
628 " CAST('%s' AS pg_catalog.text) as object\n"
629 " FROM pg_catalog.pg_proc p\n"
630 " LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace\n"
631 " WHERE p.proisagg\n",
632 gettext_noop("aggregate"));
635 appendPQExpBuffer(&buf, " AND n.nspname <> 'pg_catalog'\n");
637 processSQLNamePattern(pset.db, &buf, pattern, true, false,
638 "n.nspname", "p.proname", NULL,
639 "pg_catalog.pg_function_is_visible(p.oid)");
641 /* Function descriptions (except in/outs for datatypes) */
642 appendPQExpBuffer(&buf,
644 " SELECT p.oid as oid, p.tableoid as tableoid,\n"
645 " n.nspname as nspname,\n"
646 " CAST(p.proname AS pg_catalog.text) as name,"
647 " CAST('%s' AS pg_catalog.text) as object\n"
648 " FROM pg_catalog.pg_proc p\n"
649 " LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace\n"
651 " WHERE p.prorettype <> 'pg_catalog.cstring'::pg_catalog.regtype\n"
652 " AND (p.proargtypes[0] IS NULL\n"
653 " OR p.proargtypes[0] <> 'pg_catalog.cstring'::pg_catalog.regtype)\n"
654 " AND NOT p.proisagg\n",
655 gettext_noop("function"));
658 appendPQExpBuffer(&buf, " AND n.nspname <> 'pg_catalog'\n");
660 processSQLNamePattern(pset.db, &buf, pattern, true, false,
661 "n.nspname", "p.proname", NULL,
662 "pg_catalog.pg_function_is_visible(p.oid)");
664 /* Operator descriptions (only if operator has its own comment) */
665 appendPQExpBuffer(&buf,
667 " SELECT o.oid as oid, o.tableoid as tableoid,\n"
668 " n.nspname as nspname,\n"
669 " CAST(o.oprname AS pg_catalog.text) as name,"
670 " CAST('%s' AS pg_catalog.text) as object\n"
671 " FROM pg_catalog.pg_operator o\n"
672 " LEFT JOIN pg_catalog.pg_namespace n ON n.oid = o.oprnamespace\n",
673 gettext_noop("operator"));
676 appendPQExpBuffer(&buf, " WHERE n.nspname <> 'pg_catalog'\n");
678 processSQLNamePattern(pset.db, &buf, pattern, !showSystem, false,
679 "n.nspname", "o.oprname", NULL,
680 "pg_catalog.pg_operator_is_visible(o.oid)");
682 /* Type description */
683 appendPQExpBuffer(&buf,
685 " SELECT t.oid as oid, t.tableoid as tableoid,\n"
686 " n.nspname as nspname,\n"
687 " pg_catalog.format_type(t.oid, NULL) as name,"
688 " CAST('%s' AS pg_catalog.text) as object\n"
689 " FROM pg_catalog.pg_type t\n"
690 " LEFT JOIN pg_catalog.pg_namespace n ON n.oid = t.typnamespace\n",
691 gettext_noop("data type"));
694 appendPQExpBuffer(&buf, " WHERE n.nspname <> 'pg_catalog'\n");
696 processSQLNamePattern(pset.db, &buf, pattern, !showSystem, false,
697 "n.nspname", "pg_catalog.format_type(t.oid, NULL)",
699 "pg_catalog.pg_type_is_visible(t.oid)");
701 /* Relation (tables, views, indexes, sequences) descriptions */
702 appendPQExpBuffer(&buf,
704 " SELECT c.oid as oid, c.tableoid as tableoid,\n"
705 " n.nspname as nspname,\n"
706 " CAST(c.relname AS pg_catalog.text) as name,\n"
708 " CASE c.relkind WHEN 'r' THEN '%s' WHEN 'v' THEN '%s' WHEN 'i' THEN '%s' WHEN 'S' THEN '%s' END"
709 " AS pg_catalog.text) as object\n"
710 " FROM pg_catalog.pg_class c\n"
711 " LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace\n"
712 " WHERE c.relkind IN ('r', 'v', 'i', 'S')\n",
713 gettext_noop("table"),
714 gettext_noop("view"),
715 gettext_noop("index"),
716 gettext_noop("sequence"));
718 appendPQExpBuffer(&buf, " AND n.nspname <> 'pg_catalog'\n");
720 processSQLNamePattern(pset.db, &buf, pattern, true, false,
721 "n.nspname", "c.relname", NULL,
722 "pg_catalog.pg_table_is_visible(c.oid)");
724 /* Rule description (ignore rules for views) */
725 appendPQExpBuffer(&buf,
727 " SELECT r.oid as oid, r.tableoid as tableoid,\n"
728 " n.nspname as nspname,\n"
729 " CAST(r.rulename AS pg_catalog.text) as name,"
730 " CAST('%s' AS pg_catalog.text) as object\n"
731 " FROM pg_catalog.pg_rewrite r\n"
732 " JOIN pg_catalog.pg_class c ON c.oid = r.ev_class\n"
733 " LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace\n"
734 " WHERE r.rulename != '_RETURN'\n",
735 gettext_noop("rule"));
738 appendPQExpBuffer(&buf, " AND n.nspname <> 'pg_catalog'\n");
740 /* XXX not sure what to do about visibility rule here? */
741 processSQLNamePattern(pset.db, &buf, pattern, true, false,
742 "n.nspname", "r.rulename", NULL,
743 "pg_catalog.pg_table_is_visible(c.oid)");
745 /* Trigger description */
746 appendPQExpBuffer(&buf,
748 " SELECT t.oid as oid, t.tableoid as tableoid,\n"
749 " n.nspname as nspname,\n"
750 " CAST(t.tgname AS pg_catalog.text) as name,"
751 " CAST('%s' AS pg_catalog.text) as object\n"
752 " FROM pg_catalog.pg_trigger t\n"
753 " JOIN pg_catalog.pg_class c ON c.oid = t.tgrelid\n"
754 " LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace\n",
755 gettext_noop("trigger"));
757 appendPQExpBuffer(&buf, " WHERE n.nspname <> 'pg_catalog'\n");
759 /* XXX not sure what to do about visibility rule here? */
760 processSQLNamePattern(pset.db, &buf, pattern, !showSystem, false,
761 "n.nspname", "t.tgname", NULL,
762 "pg_catalog.pg_table_is_visible(c.oid)");
764 appendPQExpBuffer(&buf,
766 " JOIN pg_catalog.pg_description d ON (tt.oid = d.objoid AND tt.tableoid = d.classoid AND d.objsubid = 0)\n");
768 appendPQExpBuffer(&buf, "ORDER BY 1, 2, 3;");
770 res = PSQLexec(buf.data, false);
771 termPQExpBuffer(&buf);
775 myopt.nullPrint = NULL;
776 myopt.title = _("Object descriptions");
777 myopt.translate_header = true;
778 myopt.translate_columns = translate_columns;
780 printQuery(res, &myopt, pset.queryFout, pset.logfile);
788 * describeTableDetails (for \d)
790 * This routine finds the tables to be displayed, and calls
791 * describeOneTableDetails for each one.
793 * verbose: if true, this is \d+
796 describeTableDetails(const char *pattern, bool verbose, bool showSystem)
802 initPQExpBuffer(&buf);
804 printfPQExpBuffer(&buf,
808 "FROM pg_catalog.pg_class c\n"
809 " LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace\n");
812 appendPQExpBuffer(&buf, " WHERE n.nspname <> 'pg_catalog'\n");
814 processSQLNamePattern(pset.db, &buf, pattern, !showSystem, false,
815 "n.nspname", "c.relname", NULL,
816 "pg_catalog.pg_table_is_visible(c.oid)");
818 appendPQExpBuffer(&buf, "ORDER BY 2, 3;");
820 res = PSQLexec(buf.data, false);
821 termPQExpBuffer(&buf);
825 if (PQntuples(res) == 0)
828 fprintf(stderr, _("Did not find any relation named \"%s\".\n"),
834 for (i = 0; i < PQntuples(res); i++)
840 oid = PQgetvalue(res, i, 0);
841 nspname = PQgetvalue(res, i, 1);
842 relname = PQgetvalue(res, i, 2);
844 if (!describeOneTableDetails(nspname, relname, oid, verbose))
861 * describeOneTableDetails (for \d)
863 * Unfortunately, the information presented here is so complicated that it
864 * cannot be done in a single query. So we have to assemble the printed table
865 * by hand and pass it to the underlying printTable() function.
868 describeOneTableDetails(const char *schemaname,
869 const char *relationname,
874 PGresult *res = NULL;
875 printTableOpt myopt = pset.popt.topt;
876 printTableContent cont;
877 bool printTableInitialized = false;
879 char *view_def = NULL;
881 char **seq_values = NULL;
882 char **modifiers = NULL;
884 PQExpBufferData title;
885 PQExpBufferData tmpbuf;
899 bool show_modifiers = false;
904 /* This output looks confusing in expanded mode. */
905 myopt.expanded = false;
907 initPQExpBuffer(&buf);
908 initPQExpBuffer(&title);
909 initPQExpBuffer(&tmpbuf);
911 /* Get general table info */
912 if (pset.sversion >= 80400)
914 printfPQExpBuffer(&buf,
915 "SELECT c.relchecks, c.relkind, c.relhasindex, c.relhasrules, "
916 "c.relhastriggers, c.relhasoids, "
917 "%s, c.reltablespace\n"
918 "FROM pg_catalog.pg_class c\n "
919 "LEFT JOIN pg_catalog.pg_class tc ON (c.reltoastrelid = tc.oid)\n"
920 "WHERE c.oid = '%s'\n",
922 "pg_catalog.array_to_string(c.reloptions || "
923 "array(select 'toast.' || x from pg_catalog.unnest(tc.reloptions) x), ', ')\n"
927 else if (pset.sversion >= 80200)
929 printfPQExpBuffer(&buf,
930 "SELECT relchecks, relkind, relhasindex, relhasrules, "
931 "reltriggers <> 0, relhasoids, "
932 "%s, reltablespace\n"
933 "FROM pg_catalog.pg_class WHERE oid = '%s'",
935 "pg_catalog.array_to_string(reloptions, E', ')" : ",''"),
938 else if (pset.sversion >= 80000)
940 printfPQExpBuffer(&buf,
941 "SELECT relchecks, relkind, relhasindex, relhasrules, "
942 "reltriggers <> 0, relhasoids, "
943 "'', reltablespace\n"
944 "FROM pg_catalog.pg_class WHERE oid = '%s'",
949 printfPQExpBuffer(&buf,
950 "SELECT relchecks, relkind, relhasindex, relhasrules, "
951 "reltriggers <> 0, relhasoids, "
953 "FROM pg_catalog.pg_class WHERE oid = '%s'",
957 res = PSQLexec(buf.data, false);
961 /* Did we get anything? */
962 if (PQntuples(res) == 0)
965 fprintf(stderr, _("Did not find any relation with OID %s.\n"),
970 tableinfo.checks = atoi(PQgetvalue(res, 0, 0));
971 tableinfo.relkind = *(PQgetvalue(res, 0, 1));
972 tableinfo.hasindex = strcmp(PQgetvalue(res, 0, 2), "t") == 0;
973 tableinfo.hasrules = strcmp(PQgetvalue(res, 0, 3), "t") == 0;
974 tableinfo.hastriggers = strcmp(PQgetvalue(res, 0, 4), "t") == 0;
975 tableinfo.hasoids = strcmp(PQgetvalue(res, 0, 5), "t") == 0;
976 tableinfo.reloptions = pset.sversion >= 80200 ?
977 strdup(PQgetvalue(res, 0, 6)) : 0;
978 tableinfo.tablespace = (pset.sversion >= 80000) ?
979 atooid(PQgetvalue(res, 0, 7)) : 0;
984 * If it's a sequence, fetch its values and store into an
985 * array that will be used later.
987 if (tableinfo.relkind == 'S')
991 #define SEQ_NUM_COLS 10
992 printfPQExpBuffer(&buf,
993 "SELECT sequence_name, last_value,\n"
994 " start_value, increment_by,\n"
995 " max_value, min_value, cache_value,\n"
996 " log_cnt, is_cycled, is_called\n"
999 /* must be separate because fmtId isn't reentrant */
1000 appendPQExpBuffer(&buf, ".%s", fmtId(relationname));
1002 result = PSQLexec(buf.data, false);
1006 seq_values = pg_malloc_zero((SEQ_NUM_COLS+1) * sizeof(*seq_values));
1008 for (i = 0; i < SEQ_NUM_COLS; i++)
1009 seq_values[i] = pg_strdup(PQgetvalue(result, 0, i));
1014 /* Get column info (index requires additional checks) */
1015 printfPQExpBuffer(&buf, "SELECT a.attname,");
1016 appendPQExpBuffer(&buf, "\n pg_catalog.format_type(a.atttypid, a.atttypmod),"
1017 "\n (SELECT substring(pg_catalog.pg_get_expr(d.adbin, d.adrelid) for 128)"
1018 "\n FROM pg_catalog.pg_attrdef d"
1019 "\n WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef),"
1020 "\n a.attnotnull, a.attnum");
1022 appendPQExpBuffer(&buf, ", a.attstorage, pg_catalog.col_description(a.attrelid, a.attnum)");
1023 appendPQExpBuffer(&buf, "\nFROM pg_catalog.pg_attribute a");
1024 if (tableinfo.relkind == 'i')
1025 appendPQExpBuffer(&buf, ", pg_catalog.pg_index i");
1026 appendPQExpBuffer(&buf, "\nWHERE a.attrelid = '%s' AND a.attnum > 0 AND NOT a.attisdropped", oid);
1027 if (tableinfo.relkind == 'i')
1028 appendPQExpBuffer(&buf, " AND a.attrelid = i.indexrelid");
1029 appendPQExpBuffer(&buf, "\nORDER BY a.attnum");
1031 res = PSQLexec(buf.data, false);
1034 numrows = PQntuples(res);
1037 switch (tableinfo.relkind)
1040 printfPQExpBuffer(&title, _("Table \"%s.%s\""),
1041 schemaname, relationname);
1044 printfPQExpBuffer(&title, _("View \"%s.%s\""),
1045 schemaname, relationname);
1048 printfPQExpBuffer(&title, _("Sequence \"%s.%s\""),
1049 schemaname, relationname);
1052 printfPQExpBuffer(&title, _("Index \"%s.%s\""),
1053 schemaname, relationname);
1056 /* not used as of 8.2, but keep it for backwards compatibility */
1057 printfPQExpBuffer(&title, _("Special relation \"%s.%s\""),
1058 schemaname, relationname);
1061 printfPQExpBuffer(&title, _("TOAST table \"%s.%s\""),
1062 schemaname, relationname);
1065 printfPQExpBuffer(&title, _("Composite type \"%s.%s\""),
1066 schemaname, relationname);
1069 /* untranslated unknown relkind */
1070 printfPQExpBuffer(&title, "?%c? \"%s.%s\"",
1071 tableinfo.relkind, schemaname, relationname);
1075 /* Set the number of columns, and their names */
1077 headers[0] = gettext_noop("Column");
1078 headers[1] = gettext_noop("Type");
1080 if (tableinfo.relkind == 'r' || tableinfo.relkind == 'v')
1082 show_modifiers = true;
1083 headers[cols++] = gettext_noop("Modifiers");
1084 modifiers = pg_malloc_zero((numrows + 1) * sizeof(*modifiers));
1087 if (tableinfo.relkind == 'S')
1088 headers[cols++] = gettext_noop("Value");
1092 headers[cols++] = gettext_noop("Storage");
1093 headers[cols++] = gettext_noop("Description");
1096 printTableInit(&cont, &myopt, title.data, cols, numrows);
1097 printTableInitialized = true;
1099 for (i = 0; i < cols; i++)
1100 printTableAddHeader(&cont, headers[i], true, 'l');
1102 /* Check if table is a view */
1103 if (tableinfo.relkind == 'v')
1107 printfPQExpBuffer(&buf,
1108 "SELECT pg_catalog.pg_get_viewdef('%s'::pg_catalog.oid, true)",
1110 result = PSQLexec(buf.data, false);
1114 if (PQntuples(result) > 0)
1115 view_def = pg_strdup(PQgetvalue(result, 0, 0));
1120 /* Generate table cells to be printed */
1121 for (i = 0; i < numrows; i++)
1124 printTableAddCell(&cont, PQgetvalue(res, i, 0), false);
1127 printTableAddCell(&cont, PQgetvalue(res, i, 1), false);
1129 /* Modifiers: not null and default */
1132 resetPQExpBuffer(&tmpbuf);
1133 if (strcmp(PQgetvalue(res, i, 3), "t") == 0)
1134 appendPQExpBufferStr(&tmpbuf, _("not null"));
1136 /* handle "default" here */
1137 /* (note: above we cut off the 'default' string at 128) */
1138 if (strlen(PQgetvalue(res, i, 2)) != 0)
1141 appendPQExpBufferStr(&tmpbuf, " ");
1142 /* translator: default values of column definitions */
1143 appendPQExpBuffer(&tmpbuf, _("default %s"),
1144 PQgetvalue(res, i, 2));
1147 modifiers[i] = pg_strdup(tmpbuf.data);
1148 printTableAddCell(&cont, modifiers[i], false);
1151 /* Value: for sequences only */
1152 if (tableinfo.relkind == 'S')
1153 printTableAddCell(&cont, seq_values[i], false);
1155 /* Storage and Description */
1158 char *storage = PQgetvalue(res, i, 5);
1160 /* these strings are literal in our syntax, so not translated. */
1161 printTableAddCell(&cont, (storage[0]=='p' ? "plain" :
1162 (storage[0]=='m' ? "main" :
1163 (storage[0]=='x' ? "extended" :
1164 (storage[0]=='e' ? "external" :
1167 printTableAddCell(&cont, PQgetvalue(res, i, 6), false);
1172 if (tableinfo.relkind == 'i')
1174 /* Footer information about an index */
1177 printfPQExpBuffer(&buf,
1178 "SELECT i.indisunique, i.indisprimary, i.indisclustered, ");
1179 if (pset.sversion >= 80200)
1180 appendPQExpBuffer(&buf, "i.indisvalid, ");
1182 appendPQExpBuffer(&buf, "true as indisvalid, ");
1183 appendPQExpBuffer(&buf, "a.amname, c2.relname,\n"
1184 " pg_catalog.pg_get_expr(i.indpred, i.indrelid, true)\n"
1185 "FROM pg_catalog.pg_index i, pg_catalog.pg_class c, pg_catalog.pg_class c2, pg_catalog.pg_am a\n"
1186 "WHERE i.indexrelid = c.oid AND c.oid = '%s' AND c.relam = a.oid\n"
1187 "AND i.indrelid = c2.oid",
1190 result = PSQLexec(buf.data, false);
1193 else if (PQntuples(result) != 1)
1200 char *indisunique = PQgetvalue(result, 0, 0);
1201 char *indisprimary = PQgetvalue(result, 0, 1);
1202 char *indisclustered = PQgetvalue(result, 0, 2);
1203 char *indisvalid = PQgetvalue(result, 0, 3);
1204 char *indamname = PQgetvalue(result, 0, 4);
1205 char *indtable = PQgetvalue(result, 0, 5);
1206 char *indpred = PQgetvalue(result, 0, 6);
1208 if (strcmp(indisprimary, "t") == 0)
1209 printfPQExpBuffer(&tmpbuf, _("primary key, "));
1210 else if (strcmp(indisunique, "t") == 0)
1211 printfPQExpBuffer(&tmpbuf, _("unique, "));
1213 resetPQExpBuffer(&tmpbuf);
1214 appendPQExpBuffer(&tmpbuf, "%s, ", indamname);
1216 /* we assume here that index and table are in same schema */
1217 appendPQExpBuffer(&tmpbuf, _("for table \"%s.%s\""),
1218 schemaname, indtable);
1220 if (strlen(indpred))
1221 appendPQExpBuffer(&tmpbuf, _(", predicate (%s)"), indpred);
1223 if (strcmp(indisclustered, "t") == 0)
1224 appendPQExpBuffer(&tmpbuf, _(", clustered"));
1226 if (strcmp(indisvalid, "t") != 0)
1227 appendPQExpBuffer(&tmpbuf, _(", invalid"));
1229 printTableAddFooter(&cont, tmpbuf.data);
1230 add_tablespace_footer(&cont, tableinfo.relkind,
1231 tableinfo.tablespace, true);
1238 PGresult *result = NULL;
1240 /* Footer information about a view */
1241 printTableAddFooter(&cont, _("View definition:"));
1242 printTableAddFooter(&cont, view_def);
1245 if (tableinfo.hasrules)
1247 printfPQExpBuffer(&buf,
1248 "SELECT r.rulename, trim(trailing ';' from pg_catalog.pg_get_ruledef(r.oid, true))\n"
1249 "FROM pg_catalog.pg_rewrite r\n"
1250 "WHERE r.ev_class = '%s' AND r.rulename != '_RETURN' ORDER BY 1",
1252 result = PSQLexec(buf.data, false);
1256 if (PQntuples(result) > 0)
1258 printTableAddFooter(&cont, _("Rules:"));
1259 for (i = 0; i < PQntuples(result); i++)
1261 const char *ruledef;
1263 /* Everything after "CREATE RULE" is echoed verbatim */
1264 ruledef = PQgetvalue(result, i, 1);
1267 printfPQExpBuffer(&buf, " %s", ruledef);
1268 printTableAddFooter(&cont, buf.data);
1274 else if (tableinfo.relkind == 'r')
1276 /* Footer information about a table */
1277 PGresult *result = NULL;
1281 if (tableinfo.hasindex)
1283 printfPQExpBuffer(&buf,
1284 "SELECT c2.relname, i.indisprimary, i.indisunique, i.indisclustered, ");
1285 if (pset.sversion >= 80200)
1286 appendPQExpBuffer(&buf, "i.indisvalid, ");
1288 appendPQExpBuffer(&buf, "true as indisvalid, ");
1289 appendPQExpBuffer(&buf, "pg_catalog.pg_get_indexdef(i.indexrelid, 0, true)");
1290 if (pset.sversion >= 80000)
1291 appendPQExpBuffer(&buf, ", c2.reltablespace");
1292 appendPQExpBuffer(&buf,
1293 "\nFROM pg_catalog.pg_class c, pg_catalog.pg_class c2, pg_catalog.pg_index i\n"
1294 "WHERE c.oid = '%s' AND c.oid = i.indrelid AND i.indexrelid = c2.oid\n"
1295 "ORDER BY i.indisprimary DESC, i.indisunique DESC, c2.relname",
1297 result = PSQLexec(buf.data, false);
1301 tuples = PQntuples(result);
1305 printTableAddFooter(&cont, _("Indexes:"));
1306 for (i = 0; i < tuples; i++)
1308 const char *indexdef;
1309 const char *usingpos;
1311 /* untranslated index name */
1312 printfPQExpBuffer(&buf, " \"%s\"",
1313 PQgetvalue(result, i, 0));
1315 /* Label as primary key or unique (but not both) */
1316 appendPQExpBuffer(&buf,
1317 strcmp(PQgetvalue(result, i, 1), "t") == 0
1319 (strcmp(PQgetvalue(result, i, 2), "t") == 0
1322 /* Everything after "USING" is echoed verbatim */
1323 indexdef = PQgetvalue(result, i, 5);
1324 usingpos = strstr(indexdef, " USING ");
1326 indexdef = usingpos + 7;
1328 appendPQExpBuffer(&buf, " %s", indexdef);
1330 if (strcmp(PQgetvalue(result, i, 3), "t") == 0)
1331 appendPQExpBuffer(&buf, " CLUSTER");
1333 if (strcmp(PQgetvalue(result, i, 4), "t") != 0)
1334 appendPQExpBuffer(&buf, " INVALID");
1336 printTableAddFooter(&cont, buf.data);
1338 /* Print tablespace of the index on the same line */
1339 if (pset.sversion >= 80000)
1340 add_tablespace_footer(&cont, 'i',
1341 atooid(PQgetvalue(result, i, 6)),
1348 /* print table (and column) check constraints */
1349 if (tableinfo.checks)
1351 printfPQExpBuffer(&buf,
1352 "SELECT r.conname, "
1353 "pg_catalog.pg_get_constraintdef(r.oid, true)\n"
1354 "FROM pg_catalog.pg_constraint r\n"
1355 "WHERE r.conrelid = '%s' AND r.contype = 'c'\nORDER BY 1",
1357 result = PSQLexec(buf.data, false);
1361 tuples = PQntuples(result);
1365 printTableAddFooter(&cont, _("Check constraints:"));
1366 for (i = 0; i < tuples; i++)
1368 /* untranslated contraint name and def */
1369 printfPQExpBuffer(&buf, " \"%s\" %s",
1370 PQgetvalue(result, i, 0),
1371 PQgetvalue(result, i, 1));
1373 printTableAddFooter(&cont, buf.data);
1379 /* print foreign-key constraints (there are none if no triggers) */
1380 if (tableinfo.hastriggers)
1382 printfPQExpBuffer(&buf,
1384 " pg_catalog.pg_get_constraintdef(r.oid, true) as condef\n"
1385 "FROM pg_catalog.pg_constraint r\n"
1386 "WHERE r.conrelid = '%s' AND r.contype = 'f' ORDER BY 1",
1388 result = PSQLexec(buf.data, false);
1392 tuples = PQntuples(result);
1396 printTableAddFooter(&cont, _("Foreign-key constraints:"));
1397 for (i = 0; i < tuples; i++)
1399 /* untranslated constraint name and def */
1400 printfPQExpBuffer(&buf, " \"%s\" %s",
1401 PQgetvalue(result, i, 0),
1402 PQgetvalue(result, i, 1));
1404 printTableAddFooter(&cont, buf.data);
1410 /* print incoming foreign-key references (none if no triggers) */
1411 if (tableinfo.hastriggers)
1413 printfPQExpBuffer(&buf,
1414 "SELECT conname, conrelid::pg_catalog.regclass,\n"
1415 " pg_catalog.pg_get_constraintdef(c.oid, true) as condef\n"
1416 "FROM pg_catalog.pg_constraint c\n"
1417 "WHERE c.confrelid = '%s' AND c.contype = 'f' ORDER BY 1",
1419 result = PSQLexec(buf.data, false);
1423 tuples = PQntuples(result);
1427 printTableAddFooter(&cont, _("Referenced by:"));
1428 for (i = 0; i < tuples; i++)
1430 /* translator: the first %s is a FK name, the following are
1431 * a table name and the FK definition */
1432 printfPQExpBuffer(&buf, _(" \"%s\" IN %s %s"),
1433 PQgetvalue(result, i, 0),
1434 PQgetvalue(result, i, 1),
1435 PQgetvalue(result, i, 2));
1437 printTableAddFooter(&cont, buf.data);
1444 if (tableinfo.hasrules)
1446 if (pset.sversion >= 80300)
1448 printfPQExpBuffer(&buf,
1449 "SELECT r.rulename, trim(trailing ';' from pg_catalog.pg_get_ruledef(r.oid, true)), "
1451 "FROM pg_catalog.pg_rewrite r\n"
1452 "WHERE r.ev_class = '%s' ORDER BY 1",
1457 printfPQExpBuffer(&buf,
1458 "SELECT r.rulename, trim(trailing ';' from pg_catalog.pg_get_ruledef(r.oid, true)), "
1459 "'O'::char AS ev_enabled\n"
1460 "FROM pg_catalog.pg_rewrite r\n"
1461 "WHERE r.ev_class = '%s' ORDER BY 1",
1464 result = PSQLexec(buf.data, false);
1468 tuples = PQntuples(result);
1475 for (category = 0; category < 4; category++)
1477 have_heading = false;
1479 for (i = 0; i < tuples; i++)
1481 const char *ruledef;
1482 bool list_rule = false;
1487 if (*PQgetvalue(result, i, 2) == 'O')
1491 if (*PQgetvalue(result, i, 2) == 'D')
1495 if (*PQgetvalue(result, i, 2) == 'A')
1499 if (*PQgetvalue(result, i, 2) == 'R')
1511 printfPQExpBuffer(&buf, _("Rules:"));
1514 printfPQExpBuffer(&buf, _("Disabled rules:"));
1517 printfPQExpBuffer(&buf, _("Rules firing always:"));
1520 printfPQExpBuffer(&buf, _("Rules firing on replica only:"));
1523 printTableAddFooter(&cont, buf.data);
1524 have_heading = true;
1527 /* Everything after "CREATE RULE" is echoed verbatim */
1528 ruledef = PQgetvalue(result, i, 1);
1530 printfPQExpBuffer(&buf, " %s", ruledef);
1531 printTableAddFooter(&cont, buf.data);
1538 /* print triggers (but ignore foreign-key triggers) */
1539 if (tableinfo.hastriggers)
1541 printfPQExpBuffer(&buf,
1543 "pg_catalog.pg_get_triggerdef(t.oid), "
1545 "FROM pg_catalog.pg_trigger t\n"
1546 "WHERE t.tgrelid = '%s' AND ",
1548 if (pset.sversion >= 80300)
1549 appendPQExpBuffer(&buf, "t.tgconstraint = 0");
1551 appendPQExpBuffer(&buf,
1552 "(NOT tgisconstraint "
1554 " (SELECT 1 FROM pg_catalog.pg_depend d "
1555 " JOIN pg_catalog.pg_constraint c ON (d.refclassid = c.tableoid AND d.refobjid = c.oid) "
1556 " WHERE d.classid = t.tableoid AND d.objid = t.oid AND d.deptype = 'i' AND c.contype = 'f'))");
1557 appendPQExpBuffer(&buf, "\nORDER BY 1");
1559 result = PSQLexec(buf.data, false);
1563 tuples = PQntuples(result);
1571 * split the output into 4 different categories. Enabled triggers,
1572 * disabled triggers and the two special ALWAYS and REPLICA
1575 for (category = 0; category < 4; category++)
1577 have_heading = false;
1578 for (i = 0; i < tuples; i++)
1582 const char *usingpos;
1583 const char *tgenabled;
1585 /* Check if this trigger falls into the current category */
1586 tgenabled = PQgetvalue(result, i, 2);
1587 list_trigger = false;
1591 if (*tgenabled == 'O' || *tgenabled == 't')
1592 list_trigger = true;
1595 if (*tgenabled == 'D' || *tgenabled == 'f')
1596 list_trigger = true;
1599 if (*tgenabled == 'A')
1600 list_trigger = true;
1603 if (*tgenabled == 'R')
1604 list_trigger = true;
1607 if (list_trigger == false)
1610 /* Print the category heading once */
1611 if (have_heading == false)
1616 printfPQExpBuffer(&buf, _("Triggers:"));
1619 printfPQExpBuffer(&buf, _("Disabled triggers:"));
1622 printfPQExpBuffer(&buf, _("Triggers firing always:"));
1625 printfPQExpBuffer(&buf, _("Triggers firing on replica only:"));
1629 printTableAddFooter(&cont, buf.data);
1630 have_heading = true;
1633 /* Everything after "TRIGGER" is echoed verbatim */
1634 tgdef = PQgetvalue(result, i, 1);
1635 usingpos = strstr(tgdef, " TRIGGER ");
1637 tgdef = usingpos + 9;
1639 printfPQExpBuffer(&buf, " %s", tgdef);
1640 printTableAddFooter(&cont, buf.data);
1647 /* print inherited tables */
1648 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);
1650 result = PSQLexec(buf.data, false);
1654 tuples = PQntuples(result);
1656 for (i = 0; i < tuples; i++)
1658 const char *s = _("Inherits");
1661 printfPQExpBuffer(&buf, "%s: %s", s, PQgetvalue(result, i, 0));
1663 printfPQExpBuffer(&buf, "%*s %s", (int) strlen(s), "", PQgetvalue(result, i, 0));
1665 appendPQExpBuffer(&buf, ",");
1667 printTableAddFooter(&cont, buf.data);
1673 const char *s = _("Has OIDs");
1675 printfPQExpBuffer(&buf, "%s: %s", s,
1676 (tableinfo.hasoids ? _("yes") : _("no")));
1677 printTableAddFooter(&cont, buf.data);
1679 /* print reloptions */
1680 if (pset.sversion >= 80200)
1682 if (tableinfo.reloptions && tableinfo.reloptions[0] != '\0')
1684 const char *t = _("Options");
1686 printfPQExpBuffer(&buf, "%s: %s", t,
1687 tableinfo.reloptions);
1688 printTableAddFooter(&cont, buf.data);
1693 add_tablespace_footer(&cont, tableinfo.relkind, tableinfo.tablespace,
1697 printTable(&cont, pset.queryFout, pset.logfile);
1698 printTableCleanup(&cont);
1705 if (printTableInitialized)
1706 printTableCleanup(&cont);
1707 termPQExpBuffer(&buf);
1708 termPQExpBuffer(&title);
1709 termPQExpBuffer(&tmpbuf);
1713 for (ptr = seq_values; *ptr; ptr++)
1720 for (ptr = modifiers; *ptr; ptr++)
1735 * Add a tablespace description to a footer. If 'newline' is true, it is added
1736 * in a new line; otherwise it's appended to the current value of the last
1740 add_tablespace_footer(printTableContent *const cont, char relkind,
1741 Oid tablespace, const bool newline)
1743 /* relkinds for which we support tablespaces */
1744 if (relkind == 'r' || relkind == 'i')
1747 * We ignore the database default tablespace so that users not using
1748 * tablespaces don't need to know about them. This case also covers
1749 * pre-8.0 servers, for which tablespace will always be 0.
1751 if (tablespace != 0)
1753 PGresult *result = NULL;
1754 PQExpBufferData buf;
1756 initPQExpBuffer(&buf);
1757 printfPQExpBuffer(&buf,
1758 "SELECT spcname FROM pg_catalog.pg_tablespace\n"
1759 "WHERE oid = '%u'", tablespace);
1760 result = PSQLexec(buf.data, false);
1763 /* Should always be the case, but.... */
1764 if (PQntuples(result) > 0)
1768 /* Add the tablespace as a new footer */
1769 printfPQExpBuffer(&buf, _("Tablespace: \"%s\""),
1770 PQgetvalue(result, 0, 0));
1771 printTableAddFooter(cont, buf.data);
1775 /* Append the tablespace to the latest footer */
1776 printfPQExpBuffer(&buf, "%s", cont->footer->data);
1777 /* translator: before this string there's an index
1778 * description like '"foo_pkey" PRIMARY KEY, btree (a)' */
1779 appendPQExpBuffer(&buf, _(", tablespace \"%s\""),
1780 PQgetvalue(result, 0, 0));
1781 printTableSetFooter(cont, buf.data);
1785 termPQExpBuffer(&buf);
1793 * Describes roles. Any schema portion of the pattern is ignored.
1796 describeRoles(const char *pattern, bool verbose)
1798 PQExpBufferData buf;
1800 printTableContent cont;
1801 printTableOpt myopt = pset.popt.topt;
1806 const char align = 'l';
1809 initPQExpBuffer(&buf);
1811 if (pset.sversion >= 80100)
1813 printfPQExpBuffer(&buf,
1814 "SELECT r.rolname, r.rolsuper, r.rolinherit,\n"
1815 " r.rolcreaterole, r.rolcreatedb, r.rolcanlogin,\n"
1816 " r.rolconnlimit,\n"
1817 " ARRAY(SELECT b.rolname\n"
1818 " FROM pg_catalog.pg_auth_members m\n"
1819 " JOIN pg_catalog.pg_roles b ON (m.roleid = b.oid)\n"
1820 " WHERE m.member = r.oid) as memberof");
1822 if (verbose && pset.sversion >= 80200)
1824 appendPQExpBufferStr(&buf, "\n, pg_catalog.shobj_description(r.oid, 'pg_authid') AS description");
1828 appendPQExpBufferStr(&buf, "\nFROM pg_catalog.pg_roles r\n");
1830 processSQLNamePattern(pset.db, &buf, pattern, false, false,
1831 NULL, "r.rolname", NULL, NULL);
1835 printfPQExpBuffer(&buf,
1836 "SELECT u.usename AS rolname,\n"
1837 " u.usesuper AS rolsuper,\n"
1838 " true AS rolinherit, false AS rolcreaterole,\n"
1839 " u.usecreatedb AS rolcreatedb, true AS rolcanlogin,\n"
1840 " -1 AS rolconnlimit,\n"
1841 " ARRAY(SELECT g.groname FROM pg_catalog.pg_group g WHERE u.usesysid = ANY(g.grolist)) as memberof"
1842 "\nFROM pg_catalog.pg_user u\n");
1844 processSQLNamePattern(pset.db, &buf, pattern, false, false,
1845 NULL, "u.usename", NULL, NULL);
1848 appendPQExpBuffer(&buf, "ORDER BY 1;");
1850 res = PSQLexec(buf.data, false);
1854 nrows = PQntuples(res);
1855 attr = pg_malloc_zero((nrows + 1) * sizeof(*attr));
1857 printTableInit(&cont, &myopt, _("List of roles"), ncols, nrows);
1859 printTableAddHeader(&cont, gettext_noop("Role name"), true, align);
1860 printTableAddHeader(&cont, gettext_noop("Attributes"), true, align);
1861 printTableAddHeader(&cont, gettext_noop("Member of"), true, align);
1863 if (verbose && pset.sversion >= 80200)
1864 printTableAddHeader(&cont, gettext_noop("Description"), true, align);
1866 for (i = 0; i < nrows; i++)
1868 printTableAddCell(&cont, PQgetvalue(res, i, 0), false);
1870 resetPQExpBuffer(&buf);
1871 if (strcmp(PQgetvalue(res, i, 1), "t") == 0)
1872 add_role_attribute(&buf, _("Superuser"));
1874 if (strcmp(PQgetvalue(res, i, 2), "t") != 0)
1875 add_role_attribute(&buf, _("No inheritance"));
1877 if (strcmp(PQgetvalue(res, i, 3), "t") == 0)
1878 add_role_attribute(&buf, _("Create role"));
1880 if (strcmp(PQgetvalue(res, i, 4), "t") == 0)
1881 add_role_attribute(&buf, _("Create DB"));
1883 if (strcmp(PQgetvalue(res, i, 5), "t") != 0)
1884 add_role_attribute(&buf, _("Cannot login"));
1886 conns = atoi(PQgetvalue(res, i, 6));
1890 appendPQExpBufferStr(&buf, "\n");
1893 appendPQExpBuffer(&buf, _("No connections"));
1895 appendPQExpBuffer(&buf, ngettext("1 connection", "%d connections", conns), conns);
1898 attr[i] = pg_strdup(buf.data);
1900 printTableAddCell(&cont, attr[i], false);
1902 printTableAddCell(&cont, PQgetvalue(res, i, 7), false);
1904 if (verbose && pset.sversion >= 80200)
1905 printTableAddCell(&cont, PQgetvalue(res, i, 8), false);
1907 termPQExpBuffer(&buf);
1909 printTable(&cont, pset.queryFout, pset.logfile);
1910 printTableCleanup(&cont);
1912 for (i = 0; i < nrows; i++)
1921 add_role_attribute(PQExpBuffer buf, const char *const str)
1924 appendPQExpBufferStr(buf, "\n");
1926 appendPQExpBufferStr(buf, str);
1933 * handler for \d, \dt, etc.
1935 * tabtypes is an array of characters, specifying what info is desired:
1940 * S - system tables (pg_catalog)
1941 * (any order of the above is fine)
1944 listTables(const char *tabtypes, const char *pattern, bool verbose, bool showSystem)
1946 bool showTables = strchr(tabtypes, 't') != NULL;
1947 bool showIndexes = strchr(tabtypes, 'i') != NULL;
1948 bool showViews = strchr(tabtypes, 'v') != NULL;
1949 bool showSeq = strchr(tabtypes, 's') != NULL;
1951 PQExpBufferData buf;
1953 printQueryOpt myopt = pset.popt;
1954 static const bool translate_columns[] = {false, false, true, false, false, false, false};
1956 if (!(showTables || showIndexes || showViews || showSeq))
1957 showTables = showViews = showSeq = true;
1959 initPQExpBuffer(&buf);
1962 * Note: as of Pg 8.2, we no longer use relkind 's', but we keep it here
1963 * for backwards compatibility.
1965 printfPQExpBuffer(&buf,
1966 "SELECT n.nspname as \"%s\",\n"
1967 " c.relname as \"%s\",\n"
1968 " 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"
1969 " pg_catalog.pg_get_userbyid(c.relowner) as \"%s\"",
1970 gettext_noop("Schema"),
1971 gettext_noop("Name"),
1972 gettext_noop("table"),
1973 gettext_noop("view"),
1974 gettext_noop("index"),
1975 gettext_noop("sequence"),
1976 gettext_noop("special"),
1977 gettext_noop("Type"),
1978 gettext_noop("Owner"));
1981 appendPQExpBuffer(&buf,
1982 ",\n c2.relname as \"%s\"",
1983 gettext_noop("Table"));
1985 if (verbose && pset.sversion >= 80100)
1986 appendPQExpBuffer(&buf,
1987 ",\n pg_catalog.pg_size_pretty(pg_catalog.pg_relation_size(c.oid)) as \"%s\"",
1988 gettext_noop("Size"));
1990 appendPQExpBuffer(&buf,
1991 ",\n pg_catalog.obj_description(c.oid, 'pg_class') as \"%s\"",
1992 gettext_noop("Description"));
1994 appendPQExpBuffer(&buf,
1995 "\nFROM pg_catalog.pg_class c"
1996 "\n LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace");
1998 appendPQExpBuffer(&buf,
1999 "\n LEFT JOIN pg_catalog.pg_index i ON i.indexrelid = c.oid"
2000 "\n LEFT JOIN pg_catalog.pg_class c2 ON i.indrelid = c2.oid");
2002 appendPQExpBuffer(&buf, "\nWHERE c.relkind IN (");
2004 appendPQExpBuffer(&buf, "'r',");
2006 appendPQExpBuffer(&buf, "'v',");
2008 appendPQExpBuffer(&buf, "'i',");
2010 appendPQExpBuffer(&buf, "'S',");
2012 appendPQExpBuffer(&buf, "'s',"); /* was RELKIND_SPECIAL in <= 8.1 */
2013 appendPQExpBuffer(&buf, "''"); /* dummy */
2014 appendPQExpBuffer(&buf, ")\n");
2017 /* Exclude system and pg_toast objects, but show temp tables */
2018 appendPQExpBuffer(&buf,
2019 " AND n.nspname <> 'pg_catalog'\n"
2020 " AND n.nspname !~ '^pg_toast'\n");
2022 processSQLNamePattern(pset.db, &buf, pattern, true, false,
2023 "n.nspname", "c.relname", NULL,
2024 "pg_catalog.pg_table_is_visible(c.oid)");
2026 appendPQExpBuffer(&buf, "ORDER BY 1,2;");
2028 res = PSQLexec(buf.data, false);
2029 termPQExpBuffer(&buf);
2033 if (PQntuples(res) == 0 && !pset.quiet)
2036 fprintf(pset.queryFout, _("No matching relations found.\n"));
2038 fprintf(pset.queryFout, _("No relations found.\n"));
2042 myopt.nullPrint = NULL;
2043 myopt.title = _("List of relations");
2044 myopt.translate_header = true;
2045 myopt.translate_columns = translate_columns;
2047 printQuery(res, &myopt, pset.queryFout, pset.logfile);
2058 * Describes domains.
2061 listDomains(const char *pattern, bool showSystem)
2063 PQExpBufferData buf;
2065 printQueryOpt myopt = pset.popt;
2067 initPQExpBuffer(&buf);
2069 printfPQExpBuffer(&buf,
2070 "SELECT n.nspname as \"%s\",\n"
2071 " t.typname as \"%s\",\n"
2072 " pg_catalog.format_type(t.typbasetype, t.typtypmod) as \"%s\",\n"
2073 " CASE WHEN t.typnotnull AND t.typdefault IS NOT NULL THEN 'not null default '||t.typdefault\n"
2074 " WHEN t.typnotnull AND t.typdefault IS NULL THEN 'not null'\n"
2075 " WHEN NOT t.typnotnull AND t.typdefault IS NOT NULL THEN 'default '||t.typdefault\n"
2078 " pg_catalog.array_to_string(ARRAY(\n"
2079 " SELECT pg_catalog.pg_get_constraintdef(r.oid, true) FROM pg_catalog.pg_constraint r WHERE t.oid = r.contypid\n"
2080 " ), ' ') as \"%s\"\n"
2081 "FROM pg_catalog.pg_type t\n"
2082 " LEFT JOIN pg_catalog.pg_namespace n ON n.oid = t.typnamespace\n"
2083 "WHERE t.typtype = 'd'\n",
2084 gettext_noop("Schema"),
2085 gettext_noop("Name"),
2086 gettext_noop("Type"),
2087 gettext_noop("Modifier"),
2088 gettext_noop("Check"));
2091 appendPQExpBuffer(&buf, " AND n.nspname <> 'pg_catalog'\n");
2093 processSQLNamePattern(pset.db, &buf, pattern, true, false,
2094 "n.nspname", "t.typname", NULL,
2095 "pg_catalog.pg_type_is_visible(t.oid)");
2097 appendPQExpBuffer(&buf, "ORDER BY 1, 2;");
2099 res = PSQLexec(buf.data, false);
2100 termPQExpBuffer(&buf);
2104 myopt.nullPrint = NULL;
2105 myopt.title = _("List of domains");
2106 myopt.translate_header = true;
2108 printQuery(res, &myopt, pset.queryFout, pset.logfile);
2117 * Describes conversions.
2120 listConversions(const char *pattern, bool showSystem)
2122 PQExpBufferData buf;
2124 printQueryOpt myopt = pset.popt;
2125 static const bool translate_columns[] = {false, false, false, false, true};
2127 initPQExpBuffer(&buf);
2129 printfPQExpBuffer(&buf,
2130 "SELECT n.nspname AS \"%s\",\n"
2131 " c.conname AS \"%s\",\n"
2132 " pg_catalog.pg_encoding_to_char(c.conforencoding) AS \"%s\",\n"
2133 " pg_catalog.pg_encoding_to_char(c.contoencoding) AS \"%s\",\n"
2134 " CASE WHEN c.condefault THEN '%s'\n"
2135 " ELSE '%s' END AS \"%s\"\n"
2136 "FROM pg_catalog.pg_conversion c, pg_catalog.pg_namespace n\n"
2137 "WHERE n.oid = c.connamespace\n",
2138 gettext_noop("Schema"),
2139 gettext_noop("Name"),
2140 gettext_noop("Source"),
2141 gettext_noop("Destination"),
2142 gettext_noop("yes"), gettext_noop("no"),
2143 gettext_noop("Default?"));
2146 appendPQExpBuffer(&buf, " AND n.nspname <> 'pg_catalog'\n");
2148 processSQLNamePattern(pset.db, &buf, pattern, true, false,
2149 "n.nspname", "c.conname", NULL,
2150 "pg_catalog.pg_conversion_is_visible(c.oid)");
2152 appendPQExpBuffer(&buf, "ORDER BY 1, 2;");
2154 res = PSQLexec(buf.data, false);
2155 termPQExpBuffer(&buf);
2159 myopt.nullPrint = NULL;
2160 myopt.title = _("List of conversions");
2161 myopt.translate_header = true;
2162 myopt.translate_columns = translate_columns;
2164 printQuery(res, &myopt, pset.queryFout, pset.logfile);
2176 listCasts(const char *pattern)
2178 PQExpBufferData buf;
2180 printQueryOpt myopt = pset.popt;
2181 static const bool translate_columns[] = {false, false, false, true};
2183 initPQExpBuffer(&buf);
2185 * We need a left join to pg_proc for binary casts; the others are just
2186 * paranoia. Also note that we don't attempt to localize '(binary
2187 * coercible)', because there's too much risk of gettext translating a
2188 * function name that happens to match some string in the PO database.
2190 printfPQExpBuffer(&buf,
2191 "SELECT pg_catalog.format_type(castsource, NULL) AS \"%s\",\n"
2192 " pg_catalog.format_type(casttarget, NULL) AS \"%s\",\n"
2193 " CASE WHEN castfunc = 0 THEN '(binary coercible)'\n"
2196 " CASE WHEN c.castcontext = 'e' THEN '%s'\n"
2197 " WHEN c.castcontext = 'a' THEN '%s'\n"
2200 "FROM pg_catalog.pg_cast c LEFT JOIN pg_catalog.pg_proc p\n"
2201 " ON c.castfunc = p.oid\n"
2202 " LEFT JOIN pg_catalog.pg_type ts\n"
2203 " ON c.castsource = ts.oid\n"
2204 " LEFT JOIN pg_catalog.pg_namespace ns\n"
2205 " ON ns.oid = ts.typnamespace\n"
2206 " LEFT JOIN pg_catalog.pg_type tt\n"
2207 " ON c.casttarget = tt.oid\n"
2208 " LEFT JOIN pg_catalog.pg_namespace nt\n"
2209 " ON nt.oid = tt.typnamespace\n"
2211 gettext_noop("Source type"),
2212 gettext_noop("Target type"),
2213 gettext_noop("Function"),
2214 gettext_noop("no"), gettext_noop("in assignment"), gettext_noop("yes"),
2215 gettext_noop("Implicit?"));
2218 * Match name pattern against either internal or external name of either
2219 * castsource or casttarget
2221 processSQLNamePattern(pset.db, &buf, pattern, true, false,
2222 "ns.nspname", "ts.typname",
2223 "pg_catalog.format_type(ts.oid, NULL)",
2224 "pg_catalog.pg_type_is_visible(ts.oid)");
2226 appendPQExpBuffer(&buf, ") OR (true");
2228 processSQLNamePattern(pset.db, &buf, pattern, true, false,
2229 "nt.nspname", "tt.typname",
2230 "pg_catalog.format_type(tt.oid, NULL)",
2231 "pg_catalog.pg_type_is_visible(tt.oid)");
2233 appendPQExpBuffer(&buf, ")\nORDER BY 1, 2;");
2235 res = PSQLexec(buf.data, false);
2236 termPQExpBuffer(&buf);
2240 myopt.nullPrint = NULL;
2241 myopt.title = _("List of casts");
2242 myopt.translate_header = true;
2243 myopt.translate_columns = translate_columns;
2245 printQuery(res, &myopt, pset.queryFout, pset.logfile);
2254 * Describes schemas (namespaces)
2257 listSchemas(const char *pattern, bool verbose)
2259 PQExpBufferData buf;
2261 printQueryOpt myopt = pset.popt;
2263 initPQExpBuffer(&buf);
2264 printfPQExpBuffer(&buf,
2265 "SELECT n.nspname AS \"%s\",\n"
2266 " pg_catalog.pg_get_userbyid(n.nspowner) AS \"%s\"",
2267 gettext_noop("Name"),
2268 gettext_noop("Owner"));
2272 appendPQExpBuffer(&buf, ",\n ");
2273 printACLColumn(&buf, "n.nspacl");
2274 appendPQExpBuffer(&buf,
2275 ",\n pg_catalog.obj_description(n.oid, 'pg_namespace') AS \"%s\"",
2276 gettext_noop("Description"));
2279 appendPQExpBuffer(&buf,
2280 "\nFROM pg_catalog.pg_namespace n\n"
2281 "WHERE (n.nspname !~ '^pg_temp_' OR\n"
2282 " n.nspname = (pg_catalog.current_schemas(true))[1])\n"); /* temp schema is first */
2284 processSQLNamePattern(pset.db, &buf, pattern, true, false,
2285 NULL, "n.nspname", NULL,
2288 appendPQExpBuffer(&buf, "ORDER BY 1;");
2290 res = PSQLexec(buf.data, false);
2291 termPQExpBuffer(&buf);
2295 myopt.nullPrint = NULL;
2296 myopt.title = _("List of schemas");
2297 myopt.translate_header = true;
2299 printQuery(res, &myopt, pset.queryFout, pset.logfile);
2308 * list text search parsers
2311 listTSParsers(const char *pattern, bool verbose)
2313 PQExpBufferData buf;
2315 printQueryOpt myopt = pset.popt;
2317 if (pset.sversion < 80300)
2319 fprintf(stderr, _("The server (version %d.%d) does not support full text search.\n"),
2320 pset.sversion / 10000, (pset.sversion / 100) % 100);
2325 return listTSParsersVerbose(pattern);
2327 initPQExpBuffer(&buf);
2329 printfPQExpBuffer(&buf,
2331 " n.nspname as \"%s\",\n"
2332 " p.prsname as \"%s\",\n"
2333 " pg_catalog.obj_description(p.oid, 'pg_ts_parser') as \"%s\"\n"
2334 "FROM pg_catalog.pg_ts_parser p \n"
2335 "LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.prsnamespace\n",
2336 gettext_noop("Schema"),
2337 gettext_noop("Name"),
2338 gettext_noop("Description")
2341 processSQLNamePattern(pset.db, &buf, pattern, false, false,
2342 "n.nspname", "p.prsname", NULL,
2343 "pg_catalog.pg_ts_parser_is_visible(p.oid)");
2345 appendPQExpBuffer(&buf, "ORDER BY 1, 2;");
2347 res = PSQLexec(buf.data, false);
2348 termPQExpBuffer(&buf);
2352 myopt.nullPrint = NULL;
2353 myopt.title = _("List of text search parsers");
2354 myopt.translate_header = true;
2356 printQuery(res, &myopt, pset.queryFout, pset.logfile);
2363 * full description of parsers
2366 listTSParsersVerbose(const char *pattern)
2368 PQExpBufferData buf;
2372 initPQExpBuffer(&buf);
2374 printfPQExpBuffer(&buf,
2378 "FROM pg_catalog.pg_ts_parser p\n"
2379 "LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.prsnamespace\n"
2382 processSQLNamePattern(pset.db, &buf, pattern, false, false,
2383 "n.nspname", "p.prsname", NULL,
2384 "pg_catalog.pg_ts_parser_is_visible(p.oid)");
2386 appendPQExpBuffer(&buf, "ORDER BY 1, 2;");
2388 res = PSQLexec(buf.data, false);
2389 termPQExpBuffer(&buf);
2393 if (PQntuples(res) == 0)
2396 fprintf(stderr, _("Did not find any text search parser named \"%s\".\n"),
2402 for (i = 0; i < PQntuples(res); i++)
2405 const char *nspname = NULL;
2406 const char *prsname;
2408 oid = PQgetvalue(res, i, 0);
2409 if (!PQgetisnull(res, i, 1))
2410 nspname = PQgetvalue(res, i, 1);
2411 prsname = PQgetvalue(res, i, 2);
2413 if (!describeOneTSParser(oid, nspname, prsname))
2431 describeOneTSParser(const char *oid, const char *nspname, const char *prsname)
2433 PQExpBufferData buf;
2436 printQueryOpt myopt = pset.popt;
2437 static const bool translate_columns[] = {true, false, false};
2439 initPQExpBuffer(&buf);
2441 printfPQExpBuffer(&buf,
2442 "SELECT '%s' AS \"%s\", \n"
2443 " p.prsstart::pg_catalog.regproc AS \"%s\", \n"
2444 " pg_catalog.obj_description(p.prsstart, 'pg_proc') as \"%s\" \n"
2445 " FROM pg_catalog.pg_ts_parser p \n"
2446 " WHERE p.oid = '%s' \n"
2449 " p.prstoken::pg_catalog.regproc, \n"
2450 " pg_catalog.obj_description(p.prstoken, 'pg_proc') \n"
2451 " FROM pg_catalog.pg_ts_parser p \n"
2452 " WHERE p.oid = '%s' \n"
2455 " p.prsend::pg_catalog.regproc, \n"
2456 " pg_catalog.obj_description(p.prsend, 'pg_proc') \n"
2457 " FROM pg_catalog.pg_ts_parser p \n"
2458 " WHERE p.oid = '%s' \n"
2461 " p.prsheadline::pg_catalog.regproc, \n"
2462 " pg_catalog.obj_description(p.prsheadline, 'pg_proc') \n"
2463 " FROM pg_catalog.pg_ts_parser p \n"
2464 " WHERE p.oid = '%s' \n"
2467 " p.prslextype::pg_catalog.regproc, \n"
2468 " pg_catalog.obj_description(p.prslextype, 'pg_proc') \n"
2469 " FROM pg_catalog.pg_ts_parser p \n"
2470 " WHERE p.oid = '%s' \n",
2471 gettext_noop("Start parse"),
2472 gettext_noop("Method"),
2473 gettext_noop("Function"),
2474 gettext_noop("Description"),
2476 gettext_noop("Get next token"),
2478 gettext_noop("End parse"),
2480 gettext_noop("Get headline"),
2482 gettext_noop("Get token types"),
2485 res = PSQLexec(buf.data, false);
2486 termPQExpBuffer(&buf);
2490 myopt.nullPrint = NULL;
2492 sprintf(title, _("Text search parser \"%s.%s\""), nspname, prsname);
2494 sprintf(title, _("Text search parser \"%s\""), prsname);
2495 myopt.title = title;
2496 myopt.footers = NULL;
2497 myopt.default_footer = false;
2498 myopt.translate_header = true;
2499 myopt.translate_columns = translate_columns;
2501 printQuery(res, &myopt, pset.queryFout, pset.logfile);
2505 initPQExpBuffer(&buf);
2507 printfPQExpBuffer(&buf,
2508 "SELECT t.alias as \"%s\", \n"
2509 " t.description as \"%s\" \n"
2510 "FROM pg_catalog.ts_token_type( '%s'::pg_catalog.oid ) as t \n"
2512 gettext_noop("Token name"),
2513 gettext_noop("Description"),
2516 res = PSQLexec(buf.data, false);
2517 termPQExpBuffer(&buf);
2521 myopt.nullPrint = NULL;
2523 sprintf(title, _("Token types for parser \"%s.%s\""), nspname, prsname);
2525 sprintf(title, _("Token types for parser \"%s\""), prsname);
2526 myopt.title = title;
2527 myopt.footers = NULL;
2528 myopt.default_footer = true;
2529 myopt.translate_header = true;
2530 myopt.translate_columns = NULL;
2532 printQuery(res, &myopt, pset.queryFout, pset.logfile);
2541 * list text search dictionaries
2544 listTSDictionaries(const char *pattern, bool verbose)
2546 PQExpBufferData buf;
2548 printQueryOpt myopt = pset.popt;
2550 if (pset.sversion < 80300)
2552 fprintf(stderr, _("The server (version %d.%d) does not support full text search.\n"),
2553 pset.sversion / 10000, (pset.sversion / 100) % 100);
2557 initPQExpBuffer(&buf);
2559 printfPQExpBuffer(&buf,
2561 " n.nspname as \"%s\",\n"
2562 " d.dictname as \"%s\",\n",
2563 gettext_noop("Schema"),
2564 gettext_noop("Name"));
2568 appendPQExpBuffer(&buf,
2569 " ( SELECT COALESCE(nt.nspname, '(null)')::pg_catalog.text || '.' || t.tmplname FROM \n"
2570 " pg_catalog.pg_ts_template t \n"
2571 " LEFT JOIN pg_catalog.pg_namespace nt ON nt.oid = t.tmplnamespace \n"
2572 " WHERE d.dicttemplate = t.oid ) AS \"%s\", \n"
2573 " d.dictinitoption as \"%s\", \n",
2574 gettext_noop("Template"),
2575 gettext_noop("Init options"));
2578 appendPQExpBuffer(&buf,
2579 " pg_catalog.obj_description(d.oid, 'pg_ts_dict') as \"%s\"\n",
2580 gettext_noop("Description"));
2582 appendPQExpBuffer(&buf, "FROM pg_catalog.pg_ts_dict d\n"
2583 "LEFT JOIN pg_catalog.pg_namespace n ON n.oid = d.dictnamespace\n");
2585 processSQLNamePattern(pset.db, &buf, pattern, false, false,
2586 "n.nspname", "d.dictname", NULL,
2587 "pg_catalog.pg_ts_dict_is_visible(d.oid)");
2589 appendPQExpBuffer(&buf, "ORDER BY 1, 2;");
2591 res = PSQLexec(buf.data, false);
2592 termPQExpBuffer(&buf);
2596 myopt.nullPrint = NULL;
2597 myopt.title = _("List of text search dictionaries");
2598 myopt.translate_header = true;
2600 printQuery(res, &myopt, pset.queryFout, pset.logfile);
2609 * list text search templates
2612 listTSTemplates(const char *pattern, bool verbose)
2614 PQExpBufferData buf;
2616 printQueryOpt myopt = pset.popt;
2618 if (pset.sversion < 80300)
2620 fprintf(stderr, _("The server (version %d.%d) does not support full text search.\n"),
2621 pset.sversion / 10000, (pset.sversion / 100) % 100);
2625 initPQExpBuffer(&buf);
2628 printfPQExpBuffer(&buf,
2630 " n.nspname AS \"%s\",\n"
2631 " t.tmplname AS \"%s\",\n"
2632 " t.tmplinit::pg_catalog.regproc AS \"%s\",\n"
2633 " t.tmpllexize::pg_catalog.regproc AS \"%s\",\n"
2634 " pg_catalog.obj_description(t.oid, 'pg_ts_template') AS \"%s\"\n",
2635 gettext_noop("Schema"),
2636 gettext_noop("Name"),
2637 gettext_noop("Init"),
2638 gettext_noop("Lexize"),
2639 gettext_noop("Description"));
2641 printfPQExpBuffer(&buf,
2643 " n.nspname AS \"%s\",\n"
2644 " t.tmplname AS \"%s\",\n"
2645 " pg_catalog.obj_description(t.oid, 'pg_ts_template') AS \"%s\"\n",
2646 gettext_noop("Schema"),
2647 gettext_noop("Name"),
2648 gettext_noop("Description"));
2650 appendPQExpBuffer(&buf, "FROM pg_catalog.pg_ts_template t\n"
2651 "LEFT JOIN pg_catalog.pg_namespace n ON n.oid = t.tmplnamespace\n");
2653 processSQLNamePattern(pset.db, &buf, pattern, false, false,
2654 "n.nspname", "t.tmplname", NULL,
2655 "pg_catalog.pg_ts_template_is_visible(t.oid)");
2657 appendPQExpBuffer(&buf, "ORDER BY 1, 2;");
2659 res = PSQLexec(buf.data, false);
2660 termPQExpBuffer(&buf);
2664 myopt.nullPrint = NULL;
2665 myopt.title = _("List of text search templates");
2666 myopt.translate_header = true;
2668 printQuery(res, &myopt, pset.queryFout, pset.logfile);
2677 * list text search configurations
2680 listTSConfigs(const char *pattern, bool verbose)
2682 PQExpBufferData buf;
2684 printQueryOpt myopt = pset.popt;
2686 if (pset.sversion < 80300)
2688 fprintf(stderr, _("The server (version %d.%d) does not support full text search.\n"),
2689 pset.sversion / 10000, (pset.sversion / 100) % 100);
2694 return listTSConfigsVerbose(pattern);
2696 initPQExpBuffer(&buf);
2698 printfPQExpBuffer(&buf,
2700 " n.nspname as \"%s\",\n"
2701 " c.cfgname as \"%s\",\n"
2702 " pg_catalog.obj_description(c.oid, 'pg_ts_config') as \"%s\"\n"
2703 "FROM pg_catalog.pg_ts_config c\n"
2704 "LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.cfgnamespace \n",
2705 gettext_noop("Schema"),
2706 gettext_noop("Name"),
2707 gettext_noop("Description")
2710 processSQLNamePattern(pset.db, &buf, pattern, false, false,
2711 "n.nspname", "c.cfgname", NULL,
2712 "pg_catalog.pg_ts_config_is_visible(c.oid)");
2714 appendPQExpBuffer(&buf, "ORDER BY 1, 2;");
2716 res = PSQLexec(buf.data, false);
2717 termPQExpBuffer(&buf);
2721 myopt.nullPrint = NULL;
2722 myopt.title = _("List of text search configurations");
2723 myopt.translate_header = true;
2725 printQuery(res, &myopt, pset.queryFout, pset.logfile);
2732 listTSConfigsVerbose(const char *pattern)
2734 PQExpBufferData buf;
2738 initPQExpBuffer(&buf);
2740 printfPQExpBuffer(&buf,
2741 "SELECT c.oid, c.cfgname,\n"
2744 " np.nspname as pnspname \n"
2745 "FROM pg_catalog.pg_ts_config c \n"
2746 " LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.cfgnamespace, \n"
2747 " pg_catalog.pg_ts_parser p \n"
2748 " LEFT JOIN pg_catalog.pg_namespace np ON np.oid = p.prsnamespace \n"
2749 "WHERE p.oid = c.cfgparser\n"
2752 processSQLNamePattern(pset.db, &buf, pattern, true, false,
2753 "n.nspname", "c.cfgname", NULL,
2754 "pg_catalog.pg_ts_config_is_visible(c.oid)");
2756 appendPQExpBuffer(&buf, "ORDER BY 3, 2;");
2758 res = PSQLexec(buf.data, false);
2759 termPQExpBuffer(&buf);
2763 if (PQntuples(res) == 0)
2766 fprintf(stderr, _("Did not find any text search configuration named \"%s\".\n"),
2772 for (i = 0; i < PQntuples(res); i++)
2775 const char *cfgname;
2776 const char *nspname = NULL;
2777 const char *prsname;
2778 const char *pnspname = NULL;
2780 oid = PQgetvalue(res, i, 0);
2781 cfgname = PQgetvalue(res, i, 1);
2782 if (!PQgetisnull(res, i, 2))
2783 nspname = PQgetvalue(res, i, 2);
2784 prsname = PQgetvalue(res, i, 3);
2785 if (!PQgetisnull(res, i, 4))
2786 pnspname = PQgetvalue(res, i, 4);
2788 if (!describeOneTSConfig(oid, nspname, cfgname, pnspname, prsname))
2806 describeOneTSConfig(const char *oid, const char *nspname, const char *cfgname,
2807 const char *pnspname, const char *prsname)
2809 PQExpBufferData buf,
2812 printQueryOpt myopt = pset.popt;
2814 initPQExpBuffer(&buf);
2816 printfPQExpBuffer(&buf,
2818 " ( SELECT t.alias FROM \n"
2819 " pg_catalog.ts_token_type(c.cfgparser) AS t \n"
2820 " WHERE t.tokid = m.maptokentype ) AS \"%s\", \n"
2821 " pg_catalog.btrim( \n"
2822 " ARRAY( SELECT mm.mapdict::pg_catalog.regdictionary \n"
2823 " FROM pg_catalog.pg_ts_config_map AS mm \n"
2824 " WHERE mm.mapcfg = m.mapcfg AND mm.maptokentype = m.maptokentype \n"
2825 " ORDER BY mapcfg, maptokentype, mapseqno \n"
2826 " ) :: pg_catalog.text , \n"
2827 " '{}') AS \"%s\" \n"
2828 "FROM pg_catalog.pg_ts_config AS c, pg_catalog.pg_ts_config_map AS m \n"
2829 "WHERE c.oid = '%s' AND m.mapcfg = c.oid \n"
2830 "GROUP BY m.mapcfg, m.maptokentype, c.cfgparser \n"
2832 gettext_noop("Token"),
2833 gettext_noop("Dictionaries"),
2836 res = PSQLexec(buf.data, false);
2837 termPQExpBuffer(&buf);
2841 initPQExpBuffer(&title);
2844 appendPQExpBuffer(&title, _("Text search configuration \"%s.%s\""),
2847 appendPQExpBuffer(&title, _("Text search configuration \"%s\""),
2851 appendPQExpBuffer(&title, _("\nParser: \"%s.%s\""),
2854 appendPQExpBuffer(&title, _("\nParser: \"%s\""),
2857 myopt.nullPrint = NULL;
2858 myopt.title = title.data;
2859 myopt.footers = NULL;
2860 myopt.default_footer = false;
2861 myopt.translate_header = true;
2863 printQuery(res, &myopt, pset.queryFout, pset.logfile);
2865 termPQExpBuffer(&title);
2875 * Describes foreign-data wrappers
2878 listForeignDataWrappers(const char *pattern, bool verbose)
2880 PQExpBufferData buf;
2882 printQueryOpt myopt = pset.popt;
2884 if (pset.sversion < 80400)
2886 fprintf(stderr, _("The server (version %d.%d) does not support foreign-data wrappers.\n"),
2887 pset.sversion / 10000, (pset.sversion / 100) % 100);
2891 initPQExpBuffer(&buf);
2892 printfPQExpBuffer(&buf,
2893 "SELECT fdwname AS \"%s\",\n"
2894 " pg_catalog.pg_get_userbyid(fdwowner) AS \"%s\",\n"
2895 " fdwvalidator::pg_catalog.regproc AS \"%s\"",
2896 gettext_noop("Name"),
2897 gettext_noop("Owner"),
2898 gettext_noop("Validator"));
2902 appendPQExpBuffer(&buf, ",\n ");
2903 printACLColumn(&buf, "fdwacl");
2904 appendPQExpBuffer(&buf,
2905 ",\n fdwoptions AS \"%s\"",
2906 gettext_noop("Options"));
2909 appendPQExpBuffer(&buf, "\nFROM pg_catalog.pg_foreign_data_wrapper\n");
2911 processSQLNamePattern(pset.db, &buf, pattern, false, false,
2912 NULL, "fdwname", NULL, NULL);
2914 appendPQExpBuffer(&buf, "ORDER BY 1;");
2916 res = PSQLexec(buf.data, false);
2917 termPQExpBuffer(&buf);
2921 myopt.nullPrint = NULL;
2922 myopt.title = _("List of foreign-data wrappers");
2923 myopt.translate_header = true;
2925 printQuery(res, &myopt, pset.queryFout, pset.logfile);
2934 * Describes foreign servers.
2937 listForeignServers(const char *pattern, bool verbose)
2939 PQExpBufferData buf;
2941 printQueryOpt myopt = pset.popt;
2943 if (pset.sversion < 80400)
2945 fprintf(stderr, _("The server (version %d.%d) does not support foreign servers.\n"),
2946 pset.sversion / 10000, (pset.sversion / 100) % 100);
2950 initPQExpBuffer(&buf);
2951 printfPQExpBuffer(&buf,
2952 "SELECT s.srvname AS \"%s\",\n"
2953 " pg_catalog.pg_get_userbyid(s.srvowner) AS \"%s\",\n"
2954 " f.fdwname AS \"%s\"",
2955 gettext_noop("Name"),
2956 gettext_noop("Owner"),
2957 gettext_noop("Foreign-data wrapper"));
2961 appendPQExpBuffer(&buf, ",\n ");
2962 printACLColumn(&buf, "s.srvacl");
2963 appendPQExpBuffer(&buf,
2965 " s.srvtype AS \"%s\",\n"
2966 " s.srvversion AS \"%s\",\n"
2967 " s.srvoptions AS \"%s\"",
2968 gettext_noop("Type"),
2969 gettext_noop("Version"),
2970 gettext_noop("Options"));
2973 appendPQExpBuffer(&buf,
2974 "\nFROM pg_catalog.pg_foreign_server s\n"
2975 " JOIN pg_catalog.pg_foreign_data_wrapper f ON f.oid=s.srvfdw\n");
2977 processSQLNamePattern(pset.db, &buf, pattern, false, false,
2978 NULL, "s.srvname", NULL, NULL);
2980 appendPQExpBuffer(&buf, "ORDER BY 1;");
2982 res = PSQLexec(buf.data, false);
2983 termPQExpBuffer(&buf);
2987 myopt.nullPrint = NULL;
2988 myopt.title = _("List of foreign servers");
2989 myopt.translate_header = true;
2991 printQuery(res, &myopt, pset.queryFout, pset.logfile);
3000 * Describes user mappings.
3003 listUserMappings(const char *pattern, bool verbose)
3005 PQExpBufferData buf;
3007 printQueryOpt myopt = pset.popt;
3009 if (pset.sversion < 80400)
3011 fprintf(stderr, _("The server (version %d.%d) does not support user mappings.\n"),
3012 pset.sversion / 10000, (pset.sversion / 100) % 100);
3016 initPQExpBuffer(&buf);
3017 printfPQExpBuffer(&buf,
3018 "SELECT um.srvname AS \"%s\",\n"
3019 " um.usename AS \"%s\"",
3020 gettext_noop("Server"),
3021 gettext_noop("User name"));
3024 appendPQExpBuffer(&buf,
3025 ",\n um.umoptions AS \"%s\"",
3026 gettext_noop("Options"));
3028 appendPQExpBuffer(&buf, "\nFROM pg_catalog.pg_user_mappings um\n");
3030 processSQLNamePattern(pset.db, &buf, pattern, false, false,
3031 NULL, "um.srvname", "um.usename", NULL);
3033 appendPQExpBuffer(&buf, "ORDER BY 1, 2;");
3035 res = PSQLexec(buf.data, false);
3036 termPQExpBuffer(&buf);
3040 myopt.nullPrint = NULL;
3041 myopt.title = _("List of user mappings");
3042 myopt.translate_header = true;
3044 printQuery(res, &myopt, pset.queryFout, pset.logfile);
3053 * Helper function for consistently formatting ACL (privilege) columns.
3054 * The proper targetlist entry is appended to buf. Note lack of any
3055 * whitespace or comma decoration.
3058 printACLColumn(PQExpBuffer buf, const char *colname)
3060 if (pset.sversion >= 80100)
3061 appendPQExpBuffer(buf,
3062 "pg_catalog.array_to_string(%s, E'\\n') AS \"%s\"",
3063 colname, gettext_noop("Access privileges"));
3065 appendPQExpBuffer(buf,
3066 "pg_catalog.array_to_string(%s, '\\n') AS \"%s\"",
3067 colname, gettext_noop("Access privileges"));