?  search:
summary | log | graphiclog1 | graphiclog2 | commit | commitdiff | tree | refs | edit | fork
blame | history | raw | HEAD
Improve obsolete comment.
[PostgreSQL.git] / src / bin / psql / describe.c
blobcc8898439fd71e90f0522b9031bee6021f35d0da
1 /*
2 * psql - the PostgreSQL interactive terminal
3 *
4 * Support for the various \d ("describe") commands. Note that the current
5 * expectation is that all functions in this file will succeed when working
6 * with servers of versions 7.4 and up. It's okay to omit irrelevant
7 * information for an old server, but not to fail outright.
8 *
9 * Copyright (c) 2000-2009, PostgreSQL Global Development Group
10 *
11 * $PostgreSQL$
12 */
13 #include "postgres_fe.h"
14
15 #include <ctype.h>
16
17 #include "common.h"
18 #include "describe.h"
19 #include "dumputils.h"
20 #include "mbprint.h"
21 #include "print.h"
22 #include "settings.h"
23 #include "variables.h"
24
25
26 static bool describeOneTableDetails(const char *schemaname,
27 const char *relationname,
28 const char *oid,
29 bool verbose);
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,
35 const char *prsname);
36 static bool listTSConfigsVerbose(const char *pattern);
37 static bool describeOneTSConfig(const char *oid, const char *nspname,
38 const char *cfgname,
39 const char *pnspname, const char *prsname);
40 static void printACLColumn(PQExpBuffer buf, const char *colname);
41
42
43 /*----------------
44 * Handlers for various slash commands displaying some sort of list
45 * of things in the database.
46 *
47 * Note: try to format the queries to look nice in -E output.
48 *----------------
49 */
50
51
52 /* \da
53 * Takes an optional regexp to select particular aggregates
54 */
55 bool
56 describeAggregates(const char *pattern, bool verbose, bool showSystem)
57 {
58 PQExpBufferData buf;
59 PGresult *res;
60 printQueryOpt myopt = pset.popt;
61
62 initPQExpBuffer(&buf);
63
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"),
69 gettext_noop("Name"),
70 gettext_noop("Result data type"));
71
72 if (pset.sversion >= 80200)
73 appendPQExpBuffer(&buf,
74 " CASE WHEN p.pronargs = 0\n"
75 " THEN CAST('*' AS pg_catalog.text)\n"
76 " ELSE\n"
77 " pg_catalog.array_to_string(ARRAY(\n"
78 " SELECT\n"
79 " pg_catalog.format_type(p.proargtypes[s.i], NULL)\n"
80 " FROM\n"
81 " pg_catalog.generate_series(0, pg_catalog.array_upper(p.proargtypes, 1)) AS s(i)\n"
82 " ), ', ')\n"
83 " END AS \"%s\",\n",
84 gettext_noop("Argument data types"));
85 else
86 appendPQExpBuffer(&buf,
87 " pg_catalog.format_type(p.proargtypes[0], NULL) AS \"%s\",\n",
88 gettext_noop("Argument data types"));
89
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"
94 "WHERE p.proisagg\n",
95 gettext_noop("Description"));
96
97 if (!showSystem && !pattern)
98 appendPQExpBuffer(&buf, " AND n.nspname <> 'pg_catalog'\n"
99 " AND n.nspname <> 'information_schema'\n");
100
101 processSQLNamePattern(pset.db, &buf, pattern, true, false,
102 "n.nspname", "p.proname", NULL,
103 "pg_catalog.pg_function_is_visible(p.oid)");
104
105 appendPQExpBuffer(&buf, "ORDER BY 1, 2, 4;");
106
107 res = PSQLexec(buf.data, false);
108 termPQExpBuffer(&buf);
109 if (!res)
110 return false;
111
112 myopt.nullPrint = NULL;
113 myopt.title = _("List of aggregate functions");
114 myopt.translate_header = true;
115
116 printQuery(res, &myopt, pset.queryFout, pset.logfile);
117
118 PQclear(res);
119 return true;
120 }
121
122 /* \db
123 * Takes an optional regexp to select particular tablespaces
124 */
125 bool
126 describeTablespaces(const char *pattern, bool verbose)
127 {
128 PQExpBufferData buf;
129 PGresult *res;
130 printQueryOpt myopt = pset.popt;
131
132 if (pset.sversion < 80000)
133 {
134 fprintf(stderr, _("The server (version %d.%d) does not support tablespaces.\n"),
135 pset.sversion / 10000, (pset.sversion / 100) % 100);
136 return true;
137 }
138
139 initPQExpBuffer(&buf);
140
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"));
148
149 if (verbose)
150 {
151 appendPQExpBuffer(&buf, ",\n ");
152 printACLColumn(&buf, "spcacl");
153 }
154
155 if (verbose && pset.sversion >= 80200)
156 appendPQExpBuffer(&buf,
157 ",\n pg_catalog.shobj_description(oid, 'pg_tablespace') AS \"%s\"",
158 gettext_noop("Description"));
159
160 appendPQExpBuffer(&buf,
161 "\nFROM pg_catalog.pg_tablespace\n");
162
163 processSQLNamePattern(pset.db, &buf, pattern, false, false,
164 NULL, "spcname", NULL,
165 NULL);
166
167 appendPQExpBuffer(&buf, "ORDER BY 1;");
168
169 res = PSQLexec(buf.data, false);
170 termPQExpBuffer(&buf);
171 if (!res)
172 return false;
173
174 myopt.nullPrint = NULL;
175 myopt.title = _("List of tablespaces");
176 myopt.translate_header = true;
177
178 printQuery(res, &myopt, pset.queryFout, pset.logfile);
179
180 PQclear(res);
181 return true;
182 }
183
184
185 /* \df
186 * Takes an optional regexp to select particular functions
187 */
188 bool
189 describeFunctions(const char *pattern, bool verbose, bool showSystem)
190 {
191 PQExpBufferData buf;
192 PGresult *res;
193 printQueryOpt myopt = pset.popt;
194
195 initPQExpBuffer(&buf);
196
197 printfPQExpBuffer(&buf,
198 "SELECT n.nspname as \"%s\",\n"
199 " p.proname as \"%s\",\n",
200 gettext_noop("Schema"),
201 gettext_noop("Name"));
202
203 if (pset.sversion >= 80400)
204 appendPQExpBuffer(&buf,
205 " pg_catalog.pg_get_function_result(p.oid) as \"%s\",\n"
206 " pg_catalog.pg_get_function_arguments(p.oid) as \"%s\"",
207 gettext_noop("Result data type"),
208 gettext_noop("Argument data types"));
209 else if (pset.sversion >= 80100)
210 appendPQExpBuffer(&buf,
211 " CASE WHEN p.proretset THEN 'SETOF ' ELSE '' END ||\n"
212 " pg_catalog.format_type(p.prorettype, NULL) as \"%s\",\n"
213 " CASE WHEN proallargtypes IS NOT NULL THEN\n"
214 " pg_catalog.array_to_string(ARRAY(\n"
215 " SELECT\n"
216 " CASE\n"
217 " WHEN p.proargmodes[s.i] = 'i' THEN ''\n"
218 " WHEN p.proargmodes[s.i] = 'o' THEN 'OUT '\n"
219 " WHEN p.proargmodes[s.i] = 'b' THEN 'INOUT '\n"
220 " WHEN p.proargmodes[s.i] = 'v' THEN 'VARIADIC '\n"
221 " END ||\n"
222 " CASE\n"
223 " WHEN COALESCE(p.proargnames[s.i], '') = '' THEN ''\n"
224 " ELSE p.proargnames[s.i] || ' ' \n"
225 " END ||\n"
226 " pg_catalog.format_type(p.proallargtypes[s.i], NULL)\n"
227 " FROM\n"
228 " pg_catalog.generate_series(1, pg_catalog.array_upper(p.proallargtypes, 1)) AS s(i)\n"
229 " ), ', ')\n"
230 " ELSE\n"
231 " pg_catalog.array_to_string(ARRAY(\n"
232 " SELECT\n"
233 " CASE\n"
234 " WHEN COALESCE(p.proargnames[s.i+1], '') = '' THEN ''\n"
235 " ELSE p.proargnames[s.i+1] || ' '\n"
236 " END ||\n"
237 " pg_catalog.format_type(p.proargtypes[s.i], NULL)\n"
238 " FROM\n"
239 " pg_catalog.generate_series(0, pg_catalog.array_upper(p.proargtypes, 1)) AS s(i)\n"
240 " ), ', ')\n"
241 " END AS \"%s\"",
242 gettext_noop("Result data type"),
243 gettext_noop("Argument data types"));
244 else
245 appendPQExpBuffer(&buf,
246 " CASE WHEN p.proretset THEN 'SETOF ' ELSE '' END ||\n"
247 " pg_catalog.format_type(p.prorettype, NULL) as \"%s\",\n"
248 " pg_catalog.oidvectortypes(p.proargtypes) as \"%s\"",
249 gettext_noop("Result data type"),
250 gettext_noop("Argument data types"));
251
252 if (verbose)
253 appendPQExpBuffer(&buf,
254 ",\n CASE\n"
255 " WHEN p.provolatile = 'i' THEN 'immutable'\n"
256 " WHEN p.provolatile = 's' THEN 'stable'\n"
257 " WHEN p.provolatile = 'v' THEN 'volatile'\n"
258 "END as \"%s\""
259 ",\n pg_catalog.pg_get_userbyid(p.proowner) as \"%s\",\n"
260 " l.lanname as \"%s\",\n"
261 " p.prosrc as \"%s\",\n"
262 " pg_catalog.obj_description(p.oid, 'pg_proc') as \"%s\"",
263 gettext_noop("Volatility"),
264 gettext_noop("Owner"),
265 gettext_noop("Language"),
266 gettext_noop("Source code"),
267 gettext_noop("Description"));
268
269 appendPQExpBuffer(&buf,
270 "\nFROM pg_catalog.pg_proc p"
271 "\n LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace\n");
272
273 if (verbose)
274 appendPQExpBuffer(&buf,
275 " LEFT JOIN pg_catalog.pg_language l ON l.oid = p.prolang\n");
276
277 /*
278 * we skip in/out funcs by excluding functions that take or return cstring
279 */
280 appendPQExpBuffer(&buf,
281 "WHERE p.prorettype <> 'pg_catalog.cstring'::pg_catalog.regtype\n"
282 " AND p.proargtypes[0] IS DISTINCT FROM 'pg_catalog.cstring'::pg_catalog.regtype\n"
283 " AND NOT p.proisagg\n");
284
285 if (!showSystem && !pattern)
286 appendPQExpBuffer(&buf, " AND n.nspname <> 'pg_catalog'\n"
287 " AND n.nspname <> 'information_schema'\n");
288
289 processSQLNamePattern(pset.db, &buf, pattern, true, false,
290 "n.nspname", "p.proname", NULL,
291 "pg_catalog.pg_function_is_visible(p.oid)");
292
293 appendPQExpBuffer(&buf, "ORDER BY 1, 2, 4;");
294
295 res = PSQLexec(buf.data, false);
296 termPQExpBuffer(&buf);
297 if (!res)
298 return false;
299
300 myopt.nullPrint = NULL;
301 myopt.title = _("List of functions");
302 myopt.translate_header = true;
303
304 printQuery(res, &myopt, pset.queryFout, pset.logfile);
305
306 PQclear(res);
307 return true;
308 }
309
310
311
312 /*
313 * \dT
314 * describe types
315 */
316 bool
317 describeTypes(const char *pattern, bool verbose, bool showSystem)
318 {
319 PQExpBufferData buf;
320 PGresult *res;
321 printQueryOpt myopt = pset.popt;
322
323 initPQExpBuffer(&buf);
324
325 printfPQExpBuffer(&buf,
326 "SELECT n.nspname as \"%s\",\n"
327 " pg_catalog.format_type(t.oid, NULL) AS \"%s\",\n",
328 gettext_noop("Schema"),
329 gettext_noop("Name"));
330 if (verbose)
331 appendPQExpBuffer(&buf,
332 " t.typname AS \"%s\",\n"
333 " CASE WHEN t.typrelid != 0\n"
334 " THEN CAST('tuple' AS pg_catalog.text)\n"
335 " WHEN t.typlen < 0\n"
336 " THEN CAST('var' AS pg_catalog.text)\n"
337 " ELSE CAST(t.typlen AS pg_catalog.text)\n"
338 " END AS \"%s\",\n",
339 gettext_noop("Internal name"),
340 gettext_noop("Size"));
341 if (verbose && pset.sversion >= 80300)
342 appendPQExpBuffer(&buf,
343 " pg_catalog.array_to_string(\n"
344 " ARRAY(\n"
345 " SELECT e.enumlabel\n"
346 " FROM pg_catalog.pg_enum e\n"
347 " WHERE e.enumtypid = t.oid\n"
348 " ORDER BY e.oid\n"
349 " ),\n"
350 " E'\\n'\n"
351 " ) AS \"%s\",\n",
352 gettext_noop("Elements"));
353
354 appendPQExpBuffer(&buf,
355 " pg_catalog.obj_description(t.oid, 'pg_type') as \"%s\"\n",
356 gettext_noop("Description"));
357
358 appendPQExpBuffer(&buf, "FROM pg_catalog.pg_type t\n"
359 " LEFT JOIN pg_catalog.pg_namespace n ON n.oid = t.typnamespace\n");
360
361 /*
362 * do not include complex types (typrelid!=0) unless they are standalone
363 * composite types
364 */
365 appendPQExpBuffer(&buf, "WHERE (t.typrelid = 0 ");
366 appendPQExpBuffer(&buf, "OR (SELECT c.relkind = 'c' FROM pg_catalog.pg_class c "
367 "WHERE c.oid = t.typrelid))\n");
368 /*
369 * do not include array types (before 8.3 we have to use the assumption
370 * that their names start with underscore)
371 */
372 if (pset.sversion >= 80300)
373 appendPQExpBuffer(&buf, " AND NOT EXISTS(SELECT 1 FROM pg_catalog.pg_type el WHERE el.oid = t.typelem AND el.typarray = t.oid)\n");
374 else
375 appendPQExpBuffer(&buf, " AND t.typname !~ '^_'\n");
376
377 if (!showSystem && !pattern)
378 appendPQExpBuffer(&buf, " AND n.nspname <> 'pg_catalog'\n"
379 " AND n.nspname <> 'information_schema'\n");
380
381 /* Match name pattern against either internal or external name */
382 processSQLNamePattern(pset.db, &buf, pattern, true, false,
383 "n.nspname", "t.typname",
384 "pg_catalog.format_type(t.oid, NULL)",
385 "pg_catalog.pg_type_is_visible(t.oid)");
386
387 appendPQExpBuffer(&buf, "ORDER BY 1, 2;");
388
389 res = PSQLexec(buf.data, false);
390 termPQExpBuffer(&buf);
391 if (!res)
392 return false;
393
394 myopt.nullPrint = NULL;
395 myopt.title = _("List of data types");
396 myopt.translate_header = true;
397
398 printQuery(res, &myopt, pset.queryFout, pset.logfile);
399
400 PQclear(res);
401 return true;
402 }
403
404
405 /* \do
406 */
407 bool
408 describeOperators(const char *pattern, bool showSystem)
409 {
410 PQExpBufferData buf;
411 PGresult *res;
412 printQueryOpt myopt = pset.popt;
413
414 initPQExpBuffer(&buf);
415
416 printfPQExpBuffer(&buf,
417 "SELECT n.nspname as \"%s\",\n"
418 " o.oprname AS \"%s\",\n"
419 " CASE WHEN o.oprkind='l' THEN NULL ELSE pg_catalog.format_type(o.oprleft, NULL) END AS \"%s\",\n"
420 " CASE WHEN o.oprkind='r' THEN NULL ELSE pg_catalog.format_type(o.oprright, NULL) END AS \"%s\",\n"
421 " pg_catalog.format_type(o.oprresult, NULL) AS \"%s\",\n"
422 " coalesce(pg_catalog.obj_description(o.oid, 'pg_operator'),\n"
423 " pg_catalog.obj_description(o.oprcode, 'pg_proc')) AS \"%s\"\n"
424 "FROM pg_catalog.pg_operator o\n"
425 " LEFT JOIN pg_catalog.pg_namespace n ON n.oid = o.oprnamespace\n",
426 gettext_noop("Schema"),
427 gettext_noop("Name"),
428 gettext_noop("Left arg type"),
429 gettext_noop("Right arg type"),
430 gettext_noop("Result type"),
431 gettext_noop("Description"));
432
433 if (!showSystem && !pattern)
434 appendPQExpBuffer(&buf, "WHERE n.nspname <> 'pg_catalog'\n"
435 " AND n.nspname <> 'information_schema'\n");
436
437 processSQLNamePattern(pset.db, &buf, pattern, !showSystem && !pattern, true,
438 "n.nspname", "o.oprname", NULL,
439 "pg_catalog.pg_operator_is_visible(o.oid)");
440
441 appendPQExpBuffer(&buf, "ORDER BY 1, 2, 3, 4;");
442
443 res = PSQLexec(buf.data, false);
444 termPQExpBuffer(&buf);
445 if (!res)
446 return false;
447
448 myopt.nullPrint = NULL;
449 myopt.title = _("List of operators");
450 myopt.translate_header = true;
451
452 printQuery(res, &myopt, pset.queryFout, pset.logfile);
453
454 PQclear(res);
455 return true;
456 }
457
458
459 /*
460 * listAllDbs
461 *
462 * for \l, \list, and -l switch
463 */
464 bool
465 listAllDbs(bool verbose)
466 {
467 PGresult *res;
468 PQExpBufferData buf;
469 printQueryOpt myopt = pset.popt;
470
471 initPQExpBuffer(&buf);
472
473 printfPQExpBuffer(&buf,
474 "SELECT d.datname as \"%s\",\n"
475 " pg_catalog.pg_get_userbyid(d.datdba) as \"%s\",\n"
476 " pg_catalog.pg_encoding_to_char(d.encoding) as \"%s\",\n",
477 gettext_noop("Name"),
478 gettext_noop("Owner"),
479 gettext_noop("Encoding"));
480 if (pset.sversion >= 80400)
481 appendPQExpBuffer(&buf,
482 " d.datcollate as \"%s\",\n"
483 " d.datctype as \"%s\",\n",
484 gettext_noop("Collation"),
485 gettext_noop("Ctype"));
486 appendPQExpBuffer(&buf, " ");
487 printACLColumn(&buf, "d.datacl");
488 if (verbose && pset.sversion >= 80200)
489 appendPQExpBuffer(&buf,
490 ",\n CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT')\n"
491 " THEN pg_catalog.pg_size_pretty(pg_catalog.pg_database_size(d.datname))\n"
492 " ELSE 'No Access'\n"
493 " END as \"%s\"",
494 gettext_noop("Size"));
495 if (verbose && pset.sversion >= 80000)
496 appendPQExpBuffer(&buf,
497 ",\n t.spcname as \"%s\"",
498 gettext_noop("Tablespace"));
499 if (verbose && pset.sversion >= 80200)
500 appendPQExpBuffer(&buf,
501 ",\n pg_catalog.shobj_description(d.oid, 'pg_database') as \"%s\"",
502 gettext_noop("Description"));
503 appendPQExpBuffer(&buf,
504 "\nFROM pg_catalog.pg_database d\n");
505 if (verbose && pset.sversion >= 80000)
506 appendPQExpBuffer(&buf,
507 " JOIN pg_catalog.pg_tablespace t on d.dattablespace = t.oid\n");
508 appendPQExpBuffer(&buf, "ORDER BY 1;");
509 res = PSQLexec(buf.data, false);
510 termPQExpBuffer(&buf);
511 if (!res)
512 return false;
513
514 myopt.nullPrint = NULL;
515 myopt.title = _("List of databases");
516 myopt.translate_header = true;
517
518 printQuery(res, &myopt, pset.queryFout, pset.logfile);
519
520 PQclear(res);
521 return true;
522 }
523
524
525 /*
526 * List Tables' Grant/Revoke Permissions
527 * \z (now also \dp -- perhaps more mnemonic)
528 */
529 bool
530 permissionsList(const char *pattern)
531 {
532 PQExpBufferData buf;
533 PGresult *res;
534 printQueryOpt myopt = pset.popt;
535 static const bool translate_columns[] = {false, false, true, false, false};
536
537 initPQExpBuffer(&buf);
538
539 /*
540 * we ignore indexes and toast tables since they have no meaningful rights
541 */
542 printfPQExpBuffer(&buf,
543 "SELECT n.nspname as \"%s\",\n"
544 " c.relname as \"%s\",\n"
545 " CASE c.relkind WHEN 'r' THEN '%s' WHEN 'v' THEN '%s' WHEN 'S' THEN '%s' END as \"%s\",\n"
546 " ",
547 gettext_noop("Schema"),
548 gettext_noop("Name"),
549 gettext_noop("table"), gettext_noop("view"), gettext_noop("sequence"),
550 gettext_noop("Type"));
551
552 printACLColumn(&buf, "c.relacl");
553
554 if (pset.sversion >= 80400)
555 appendPQExpBuffer(&buf,
556 ",\n pg_catalog.array_to_string(ARRAY(\n"
557 " SELECT attname || E':\\n ' || pg_catalog.array_to_string(attacl, E'\\n ')\n"
558 " FROM pg_catalog.pg_attribute a\n"
559 " WHERE attrelid = c.oid AND NOT attisdropped AND attacl IS NOT NULL\n"
560 " ), E'\\n') AS \"%s\"",
561 gettext_noop("Column access privileges"));
562
563 appendPQExpBuffer(&buf, "\nFROM pg_catalog.pg_class c\n"
564 " LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace\n"
565 "WHERE c.relkind IN ('r', 'v', 'S')\n");
566
567 /*
568 * Unless a schema pattern is specified, we suppress system and temp
569 * tables, since they normally aren't very interesting from a permissions
570 * point of view. You can see 'em by explicit request though, eg with \z
571 * pg_catalog.*
572 */
573 processSQLNamePattern(pset.db, &buf, pattern, true, false,
574 "n.nspname", "c.relname", NULL,
575 "n.nspname !~ '^pg_' AND pg_catalog.pg_table_is_visible(c.oid)");
576
577 appendPQExpBuffer(&buf, "ORDER BY 1, 2;");
578
579 res = PSQLexec(buf.data, false);
580 if (!res)
581 {
582 termPQExpBuffer(&buf);
583 return false;
584 }
585
586 myopt.nullPrint = NULL;
587 printfPQExpBuffer(&buf, _("Access privileges"));
588 myopt.title = buf.data;
589 myopt.translate_header = true;
590 myopt.translate_columns = translate_columns;
591
592 printQuery(res, &myopt, pset.queryFout, pset.logfile);
593
594 termPQExpBuffer(&buf);
595 PQclear(res);
596 return true;
597 }
598
599
600
601 /*
602 * Get object comments
603 *
604 * \dd [foo]
605 *
606 * Note: This only lists things that actually have a description. For complete
607 * lists of things, there are other \d? commands.
608 */
609 bool
610 objectDescription(const char *pattern, bool showSystem)
611 {
612 PQExpBufferData buf;
613 PGresult *res;
614 printQueryOpt myopt = pset.popt;
615 static const bool translate_columns[] = {false, false, true, false};
616
617 initPQExpBuffer(&buf);
618
619 appendPQExpBuffer(&buf,
620 "SELECT DISTINCT tt.nspname AS \"%s\", tt.name AS \"%s\", tt.object AS \"%s\", d.description AS \"%s\"\n"
621 "FROM (\n",
622 gettext_noop("Schema"),
623 gettext_noop("Name"),
624 gettext_noop("Object"),
625 gettext_noop("Description"));
626
627 /* Aggregate descriptions */
628 appendPQExpBuffer(&buf,
629 " SELECT p.oid as oid, p.tableoid as tableoid,\n"
630 " n.nspname as nspname,\n"
631 " CAST(p.proname AS pg_catalog.text) as name,"
632 " CAST('%s' AS pg_catalog.text) as object\n"
633 " FROM pg_catalog.pg_proc p\n"
634 " LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace\n"
635 " WHERE p.proisagg\n",
636 gettext_noop("aggregate"));
637
638 if (!showSystem && !pattern)
639 appendPQExpBuffer(&buf, " AND n.nspname <> 'pg_catalog'\n"
640 " AND n.nspname <> 'information_schema'\n");
641
642 processSQLNamePattern(pset.db, &buf, pattern, true, false,
643 "n.nspname", "p.proname", NULL,
644 "pg_catalog.pg_function_is_visible(p.oid)");
645
646 /* Function descriptions (except in/outs for datatypes) */
647 appendPQExpBuffer(&buf,
648 "UNION ALL\n"
649 " SELECT p.oid as oid, p.tableoid as tableoid,\n"
650 " n.nspname as nspname,\n"
651 " CAST(p.proname AS pg_catalog.text) as name,"
652 " CAST('%s' AS pg_catalog.text) as object\n"
653 " FROM pg_catalog.pg_proc p\n"
654 " LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace\n"
655
656 " WHERE p.prorettype <> 'pg_catalog.cstring'::pg_catalog.regtype\n"
657 " AND (p.proargtypes[0] IS NULL\n"
658 " OR p.proargtypes[0] <> 'pg_catalog.cstring'::pg_catalog.regtype)\n"
659 " AND NOT p.proisagg\n",
660 gettext_noop("function"));
661
662 if (!showSystem && !pattern)
663 appendPQExpBuffer(&buf, " AND n.nspname <> 'pg_catalog'\n"
664 " AND n.nspname <> 'information_schema'\n");
665
666 processSQLNamePattern(pset.db, &buf, pattern, true, false,
667 "n.nspname", "p.proname", NULL,
668 "pg_catalog.pg_function_is_visible(p.oid)");
669
670 /* Operator descriptions (only if operator has its own comment) */
671 appendPQExpBuffer(&buf,
672 "UNION ALL\n"
673 " SELECT o.oid as oid, o.tableoid as tableoid,\n"
674 " n.nspname as nspname,\n"
675 " CAST(o.oprname AS pg_catalog.text) as name,"
676 " CAST('%s' AS pg_catalog.text) as object\n"
677 " FROM pg_catalog.pg_operator o\n"
678 " LEFT JOIN pg_catalog.pg_namespace n ON n.oid = o.oprnamespace\n",
679 gettext_noop("operator"));
680
681 if (!showSystem && !pattern)
682 appendPQExpBuffer(&buf, "WHERE n.nspname <> 'pg_catalog'\n"
683 " AND n.nspname <> 'information_schema'\n");
684
685 processSQLNamePattern(pset.db, &buf, pattern, !showSystem && !pattern, false,
686 "n.nspname", "o.oprname", NULL,
687 "pg_catalog.pg_operator_is_visible(o.oid)");
688
689 /* Type description */
690 appendPQExpBuffer(&buf,
691 "UNION ALL\n"
692 " SELECT t.oid as oid, t.tableoid as tableoid,\n"
693 " n.nspname as nspname,\n"
694 " pg_catalog.format_type(t.oid, NULL) as name,"
695 " CAST('%s' AS pg_catalog.text) as object\n"
696 " FROM pg_catalog.pg_type t\n"
697 " LEFT JOIN pg_catalog.pg_namespace n ON n.oid = t.typnamespace\n",
698 gettext_noop("data type"));
699
700 if (!showSystem && !pattern)
701 appendPQExpBuffer(&buf, "WHERE n.nspname <> 'pg_catalog'\n"
702 " AND n.nspname <> 'information_schema'\n");
703
704 processSQLNamePattern(pset.db, &buf, pattern, !showSystem && !pattern, false,
705 "n.nspname", "pg_catalog.format_type(t.oid, NULL)",
706 NULL,
707 "pg_catalog.pg_type_is_visible(t.oid)");
708
709 /* Relation (tables, views, indexes, sequences) descriptions */
710 appendPQExpBuffer(&buf,
711 "UNION ALL\n"
712 " SELECT c.oid as oid, c.tableoid as tableoid,\n"
713 " n.nspname as nspname,\n"
714 " CAST(c.relname AS pg_catalog.text) as name,\n"
715 " CAST(\n"
716 " CASE c.relkind WHEN 'r' THEN '%s' WHEN 'v' THEN '%s' WHEN 'i' THEN '%s' WHEN 'S' THEN '%s' END"
717 " AS pg_catalog.text) as object\n"
718 " FROM pg_catalog.pg_class c\n"
719 " LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace\n"
720 " WHERE c.relkind IN ('r', 'v', 'i', 'S')\n",
721 gettext_noop("table"),
722 gettext_noop("view"),
723 gettext_noop("index"),
724 gettext_noop("sequence"));
725
726 if (!showSystem && !pattern)
727 appendPQExpBuffer(&buf, " AND n.nspname <> 'pg_catalog'\n"
728 " AND n.nspname <> 'information_schema'\n");
729
730 processSQLNamePattern(pset.db, &buf, pattern, true, false,
731 "n.nspname", "c.relname", NULL,
732 "pg_catalog.pg_table_is_visible(c.oid)");
733
734 /* Rule description (ignore rules for views) */
735 appendPQExpBuffer(&buf,
736 "UNION ALL\n"
737 " SELECT r.oid as oid, r.tableoid as tableoid,\n"
738 " n.nspname as nspname,\n"
739 " CAST(r.rulename AS pg_catalog.text) as name,"
740 " CAST('%s' AS pg_catalog.text) as object\n"
741 " FROM pg_catalog.pg_rewrite r\n"
742 " JOIN pg_catalog.pg_class c ON c.oid = r.ev_class\n"
743 " LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace\n"
744 " WHERE r.rulename != '_RETURN'\n",
745 gettext_noop("rule"));
746
747 if (!showSystem && !pattern)
748 appendPQExpBuffer(&buf, " AND n.nspname <> 'pg_catalog'\n"
749 " AND n.nspname <> 'information_schema'\n");
750
751 /* XXX not sure what to do about visibility rule here? */
752 processSQLNamePattern(pset.db, &buf, pattern, true, false,
753 "n.nspname", "r.rulename", NULL,
754 "pg_catalog.pg_table_is_visible(c.oid)");
755
756 /* Trigger description */
757 appendPQExpBuffer(&buf,
758 "UNION ALL\n"
759 " SELECT t.oid as oid, t.tableoid as tableoid,\n"
760 " n.nspname as nspname,\n"
761 " CAST(t.tgname AS pg_catalog.text) as name,"
762 " CAST('%s' AS pg_catalog.text) as object\n"
763 " FROM pg_catalog.pg_trigger t\n"
764 " JOIN pg_catalog.pg_class c ON c.oid = t.tgrelid\n"
765 " LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace\n",
766 gettext_noop("trigger"));
767
768 if (!showSystem && !pattern)
769 appendPQExpBuffer(&buf, "WHERE n.nspname <> 'pg_catalog'\n"
770 " AND n.nspname <> 'information_schema'\n");
771
772 /* XXX not sure what to do about visibility rule here? */
773 processSQLNamePattern(pset.db, &buf, pattern, !showSystem && !pattern, false,
774 "n.nspname", "t.tgname", NULL,
775 "pg_catalog.pg_table_is_visible(c.oid)");
776
777 appendPQExpBuffer(&buf,
778 ") AS tt\n"
779 " JOIN pg_catalog.pg_description d ON (tt.oid = d.objoid AND tt.tableoid = d.classoid AND d.objsubid = 0)\n");
780
781 appendPQExpBuffer(&buf, "ORDER BY 1, 2, 3;");
782
783 res = PSQLexec(buf.data, false);
784 termPQExpBuffer(&buf);
785 if (!res)
786 return false;
787
788 myopt.nullPrint = NULL;
789 myopt.title = _("Object descriptions");
790 myopt.translate_header = true;
791 myopt.translate_columns = translate_columns;
792
793 printQuery(res, &myopt, pset.queryFout, pset.logfile);
794
795 PQclear(res);
796 return true;
797 }
798
799
800 /*
801 * describeTableDetails (for \d)
802 *
803 * This routine finds the tables to be displayed, and calls
804 * describeOneTableDetails for each one.
805 *
806 * verbose: if true, this is \d+
807 */
808 bool
809 describeTableDetails(const char *pattern, bool verbose, bool showSystem)
810 {
811 PQExpBufferData buf;
812 PGresult *res;
813 int i;
814
815 initPQExpBuffer(&buf);
816
817 printfPQExpBuffer(&buf,
818 "SELECT c.oid,\n"
819 " n.nspname,\n"
820 " c.relname\n"
821 "FROM pg_catalog.pg_class c\n"
822 " LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace\n");
823
824 if (!showSystem && !pattern)
825 appendPQExpBuffer(&buf, "WHERE n.nspname <> 'pg_catalog'\n"
826 " AND n.nspname <> 'information_schema'\n");
827
828 processSQLNamePattern(pset.db, &buf, pattern, !showSystem && !pattern, false,
829 "n.nspname", "c.relname", NULL,
830 "pg_catalog.pg_table_is_visible(c.oid)");
831
832 appendPQExpBuffer(&buf, "ORDER BY 2, 3;");
833
834 res = PSQLexec(buf.data, false);
835 termPQExpBuffer(&buf);
836 if (!res)
837 return false;
838
839 if (PQntuples(res) == 0)
840 {
841 if (!pset.quiet)
842 fprintf(stderr, _("Did not find any relation named \"%s\".\n"),
843 pattern);
844 PQclear(res);
845 return false;
846 }
847
848 for (i = 0; i < PQntuples(res); i++)
849 {
850 const char *oid;
851 const char *nspname;
852 const char *relname;
853
854 oid = PQgetvalue(res, i, 0);
855 nspname = PQgetvalue(res, i, 1);
856 relname = PQgetvalue(res, i, 2);
857
858 if (!describeOneTableDetails(nspname, relname, oid, verbose))
859 {
860 PQclear(res);
861 return false;
862 }
863 if (cancel_pressed)
864 {
865 PQclear(res);
866 return false;
867 }
868 }
869
870 PQclear(res);
871 return true;
872 }
873
874 /*
875 * describeOneTableDetails (for \d)
876 *
877 * Unfortunately, the information presented here is so complicated that it
878 * cannot be done in a single query. So we have to assemble the printed table
879 * by hand and pass it to the underlying printTable() function.
880 */
881 static bool
882 describeOneTableDetails(const char *schemaname,
883 const char *relationname,
884 const char *oid,
885 bool verbose)
886 {
887 PQExpBufferData buf;
888 PGresult *res = NULL;
889 printTableOpt myopt = pset.popt.topt;
890 printTableContent cont;
891 bool printTableInitialized = false;
892 int i;
893 char *view_def = NULL;
894 char *headers[6];
895 char **seq_values = NULL;
896 char **modifiers = NULL;
897 char **ptr;
898 PQExpBufferData title;
899 PQExpBufferData tmpbuf;
900 int cols = 0;
901 int numrows = 0;
902 struct
903 {
904 int16 checks;
905 char relkind;
906 bool hasindex;
907 bool hasrules;
908 bool hastriggers;
909 bool hasoids;
910 Oid tablespace;
911 char *reloptions;
912 } tableinfo;
913 bool show_modifiers = false;
914 bool retval;
915
916 retval = false;
917
918 /* This output looks confusing in expanded mode. */
919 myopt.expanded = false;
920
921 initPQExpBuffer(&buf);
922 initPQExpBuffer(&title);
923 initPQExpBuffer(&tmpbuf);
924
925 /* Get general table info */
926 if (pset.sversion >= 80400)
927 {
928 printfPQExpBuffer(&buf,
929 "SELECT c.relchecks, c.relkind, c.relhasindex, c.relhasrules, "
930 "c.relhastriggers, c.relhasoids, "
931 "%s, c.reltablespace\n"
932 "FROM pg_catalog.pg_class c\n "
933 "LEFT JOIN pg_catalog.pg_class tc ON (c.reltoastrelid = tc.oid)\n"
934 "WHERE c.oid = '%s'\n",
935 (verbose ?
936 "pg_catalog.array_to_string(c.reloptions || "
937 "array(select 'toast.' || x from pg_catalog.unnest(tc.reloptions) x), ', ')\n"
938 : "''"),
939 oid);
940 }
941 else if (pset.sversion >= 80200)
942 {
943 printfPQExpBuffer(&buf,
944 "SELECT relchecks, relkind, relhasindex, relhasrules, "
945 "reltriggers <> 0, relhasoids, "
946 "%s, reltablespace\n"
947 "FROM pg_catalog.pg_class WHERE oid = '%s'",
948 (verbose ?
949 "pg_catalog.array_to_string(reloptions, E', ')" : ",''"),
950 oid);
951 }
952 else if (pset.sversion >= 80000)
953 {
954 printfPQExpBuffer(&buf,
955 "SELECT relchecks, relkind, relhasindex, relhasrules, "
956 "reltriggers <> 0, relhasoids, "
957 "'', reltablespace\n"
958 "FROM pg_catalog.pg_class WHERE oid = '%s'",
959 oid);
960 }
961 else
962 {
963 printfPQExpBuffer(&buf,
964 "SELECT relchecks, relkind, relhasindex, relhasrules, "
965 "reltriggers <> 0, relhasoids, "
966 "'', ''\n"
967 "FROM pg_catalog.pg_class WHERE oid = '%s'",
968 oid);
969 }
970
971 res = PSQLexec(buf.data, false);
972 if (!res)
973 goto error_return;
974
975 /* Did we get anything? */
976 if (PQntuples(res) == 0)
977 {
978 if (!pset.quiet)
979 fprintf(stderr, _("Did not find any relation with OID %s.\n"),
980 oid);
981 goto error_return;
982 }
983
984 tableinfo.checks = atoi(PQgetvalue(res, 0, 0));
985 tableinfo.relkind = *(PQgetvalue(res, 0, 1));
986 tableinfo.hasindex = strcmp(PQgetvalue(res, 0, 2), "t") == 0;
987 tableinfo.hasrules = strcmp(PQgetvalue(res, 0, 3), "t") == 0;
988 tableinfo.hastriggers = strcmp(PQgetvalue(res, 0, 4), "t") == 0;
989 tableinfo.hasoids = strcmp(PQgetvalue(res, 0, 5), "t") == 0;
990 tableinfo.reloptions = pset.sversion >= 80200 ?
991 strdup(PQgetvalue(res, 0, 6)) : 0;
992 tableinfo.tablespace = (pset.sversion >= 80000) ?
993 atooid(PQgetvalue(res, 0, 7)) : 0;
994 PQclear(res);
995 res = NULL;
996
997 /*
998 * If it's a sequence, fetch its values and store into an
999 * array that will be used later.
1000 */
1001 if (tableinfo.relkind == 'S')
1002 {
1003 PGresult *result;
1004
1005 #define SEQ_NUM_COLS 10
1006 printfPQExpBuffer(&buf,
1007 "SELECT sequence_name, last_value,\n"
1008 " start_value, increment_by,\n"
1009 " max_value, min_value, cache_value,\n"
1010 " log_cnt, is_cycled, is_called\n"
1011 "FROM %s",
1012 fmtId(schemaname));
1013 /* must be separate because fmtId isn't reentrant */
1014 appendPQExpBuffer(&buf, ".%s", fmtId(relationname));
1015
1016 result = PSQLexec(buf.data, false);
1017 if (!result)
1018 goto error_return;
1019
1020 seq_values = pg_malloc_zero((SEQ_NUM_COLS+1) * sizeof(*seq_values));
1021
1022 for (i = 0; i < SEQ_NUM_COLS; i++)
1023 seq_values[i] = pg_strdup(PQgetvalue(result, 0, i));
1024
1025 PQclear(result);
1026 }
1027
1028 /* Get column info (index requires additional checks) */
1029 printfPQExpBuffer(&buf, "SELECT a.attname,");
1030 appendPQExpBuffer(&buf, "\n pg_catalog.format_type(a.atttypid, a.atttypmod),"
1031 "\n (SELECT substring(pg_catalog.pg_get_expr(d.adbin, d.adrelid) for 128)"
1032 "\n FROM pg_catalog.pg_attrdef d"
1033 "\n WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef),"
1034 "\n a.attnotnull, a.attnum");
1035 if (verbose)
1036 appendPQExpBuffer(&buf, ", a.attstorage, pg_catalog.col_description(a.attrelid, a.attnum)");
1037 appendPQExpBuffer(&buf, "\nFROM pg_catalog.pg_attribute a");
1038 if (tableinfo.relkind == 'i')
1039 appendPQExpBuffer(&buf, ", pg_catalog.pg_index i");
1040 appendPQExpBuffer(&buf, "\nWHERE a.attrelid = '%s' AND a.attnum > 0 AND NOT a.attisdropped", oid);
1041 if (tableinfo.relkind == 'i')
1042 appendPQExpBuffer(&buf, " AND a.attrelid = i.indexrelid");
1043 appendPQExpBuffer(&buf, "\nORDER BY a.attnum");
1044
1045 res = PSQLexec(buf.data, false);
1046 if (!res)
1047 goto error_return;
1048 numrows = PQntuples(res);
1049
1050 /* Make title */
1051 switch (tableinfo.relkind)
1052 {
1053 case 'r':
1054 printfPQExpBuffer(&title, _("Table \"%s.%s\""),
1055 schemaname, relationname);
1056 break;
1057 case 'v':
1058 printfPQExpBuffer(&title, _("View \"%s.%s\""),
1059 schemaname, relationname);
1060 break;
1061 case 'S':
1062 printfPQExpBuffer(&title, _("Sequence \"%s.%s\""),
1063 schemaname, relationname);
1064 break;
1065 case 'i':
1066 printfPQExpBuffer(&title, _("Index \"%s.%s\""),
1067 schemaname, relationname);
1068 break;
1069 case 's':
1070 /* not used as of 8.2, but keep it for backwards compatibility */
1071 printfPQExpBuffer(&title, _("Special relation \"%s.%s\""),
1072 schemaname, relationname);
1073 break;
1074 case 't':
1075 printfPQExpBuffer(&title, _("TOAST table \"%s.%s\""),
1076 schemaname, relationname);
1077 break;
1078 case 'c':
1079 printfPQExpBuffer(&title, _("Composite type \"%s.%s\""),
1080 schemaname, relationname);
1081 break;
1082 default:
1083 /* untranslated unknown relkind */
1084 printfPQExpBuffer(&title, "?%c? \"%s.%s\"",
1085 tableinfo.relkind, schemaname, relationname);
1086 break;
1087 }
1088
1089 /* Set the number of columns, and their names */
1090 cols += 2;
1091 headers[0] = gettext_noop("Column");
1092 headers[1] = gettext_noop("Type");
1093
1094 if (tableinfo.relkind == 'r' || tableinfo.relkind == 'v')
1095 {
1096 show_modifiers = true;
1097 headers[cols++] = gettext_noop("Modifiers");
1098 modifiers = pg_malloc_zero((numrows + 1) * sizeof(*modifiers));
1099 }
1100
1101 if (tableinfo.relkind == 'S')
1102 headers[cols++] = gettext_noop("Value");
1103
1104 if (verbose)
1105 {
1106 headers[cols++] = gettext_noop("Storage");
1107 headers[cols++] = gettext_noop("Description");
1108 }
1109
1110 printTableInit(&cont, &myopt, title.data, cols, numrows);
1111 printTableInitialized = true;
1112
1113 for (i = 0; i < cols; i++)
1114 printTableAddHeader(&cont, headers[i], true, 'l');
1115
1116 /* Check if table is a view */
1117 if (tableinfo.relkind == 'v')
1118 {
1119 PGresult *result;
1120
1121 printfPQExpBuffer(&buf,
1122 "SELECT pg_catalog.pg_get_viewdef('%s'::pg_catalog.oid, true)",
1123 oid);
1124 result = PSQLexec(buf.data, false);
1125 if (!result)
1126 goto error_return;
1127
1128 if (PQntuples(result) > 0)
1129 view_def = pg_strdup(PQgetvalue(result, 0, 0));
1130
1131 PQclear(result);
1132 }
1133
1134 /* Generate table cells to be printed */
1135 for (i = 0; i < numrows; i++)
1136 {
1137 /* Column */
1138 printTableAddCell(&cont, PQgetvalue(res, i, 0), false);
1139
1140 /* Type */
1141 printTableAddCell(&cont, PQgetvalue(res, i, 1), false);
1142
1143 /* Modifiers: not null and default */
1144 if (show_modifiers)
1145 {
1146 resetPQExpBuffer(&tmpbuf);
1147 if (strcmp(PQgetvalue(res, i, 3), "t") == 0)
1148 appendPQExpBufferStr(&tmpbuf, _("not null"));
1149
1150 /* handle "default" here */
1151 /* (note: above we cut off the 'default' string at 128) */
1152 if (strlen(PQgetvalue(res, i, 2)) != 0)
1153 {
1154 if (tmpbuf.len > 0)
1155 appendPQExpBufferStr(&tmpbuf, " ");
1156 /* translator: default values of column definitions */
1157 appendPQExpBuffer(&tmpbuf, _("default %s"),
1158 PQgetvalue(res, i, 2));
1159 }
1160
1161 modifiers[i] = pg_strdup(tmpbuf.data);
1162 printTableAddCell(&cont, modifiers[i], false);
1163 }
1164
1165 /* Value: for sequences only */
1166 if (tableinfo.relkind == 'S')
1167 printTableAddCell(&cont, seq_values[i], false);
1168
1169 /* Storage and Description */
1170 if (verbose)
1171 {
1172 char *storage = PQgetvalue(res, i, 5);
1173
1174 /* these strings are literal in our syntax, so not translated. */
1175 printTableAddCell(&cont, (storage[0]=='p' ? "plain" :
1176 (storage[0]=='m' ? "main" :
1177 (storage[0]=='x' ? "extended" :
1178 (storage[0]=='e' ? "external" :
1179 "???")))),
1180 false);
1181 printTableAddCell(&cont, PQgetvalue(res, i, 6), false);
1182 }
1183 }
1184
1185 /* Make footers */
1186 if (tableinfo.relkind == 'i')
1187 {
1188 /* Footer information about an index */
1189 PGresult *result;
1190
1191 printfPQExpBuffer(&buf,
1192 "SELECT i.indisunique, i.indisprimary, i.indisclustered, ");
1193 if (pset.sversion >= 80200)
1194 appendPQExpBuffer(&buf, "i.indisvalid, ");
1195 else
1196 appendPQExpBuffer(&buf, "true as indisvalid, ");
1197 appendPQExpBuffer(&buf, "a.amname, c2.relname,\n"
1198 " pg_catalog.pg_get_expr(i.indpred, i.indrelid, true)\n"
1199 "FROM pg_catalog.pg_index i, pg_catalog.pg_class c, pg_catalog.pg_class c2, pg_catalog.pg_am a\n"
1200 "WHERE i.indexrelid = c.oid AND c.oid = '%s' AND c.relam = a.oid\n"
1201 "AND i.indrelid = c2.oid",
1202 oid);
1203
1204 result = PSQLexec(buf.data, false);
1205 if (!result)
1206 goto error_return;
1207 else if (PQntuples(result) != 1)
1208 {
1209 PQclear(result);
1210 goto error_return;
1211 }
1212 else
1213 {
1214 char *indisunique = PQgetvalue(result, 0, 0);
1215 char *indisprimary = PQgetvalue(result, 0, 1);
1216 char *indisclustered = PQgetvalue(result, 0, 2);
1217 char *indisvalid = PQgetvalue(result, 0, 3);
1218 char *indamname = PQgetvalue(result, 0, 4);
1219 char *indtable = PQgetvalue(result, 0, 5);
1220 char *indpred = PQgetvalue(result, 0, 6);
1221
1222 if (strcmp(indisprimary, "t") == 0)
1223 printfPQExpBuffer(&tmpbuf, _("primary key, "));
1224 else if (strcmp(indisunique, "t") == 0)
1225 printfPQExpBuffer(&tmpbuf, _("unique, "));
1226 else
1227 resetPQExpBuffer(&tmpbuf);
1228 appendPQExpBuffer(&tmpbuf, "%s, ", indamname);
1229
1230 /* we assume here that index and table are in same schema */
1231 appendPQExpBuffer(&tmpbuf, _("for table \"%s.%s\""),
1232 schemaname, indtable);
1233
1234 if (strlen(indpred))
1235 appendPQExpBuffer(&tmpbuf, _(", predicate (%s)"), indpred);
1236
1237 if (strcmp(indisclustered, "t") == 0)
1238 appendPQExpBuffer(&tmpbuf, _(", clustered"));
1239
1240 if (strcmp(indisvalid, "t") != 0)
1241 appendPQExpBuffer(&tmpbuf, _(", invalid"));
1242
1243 printTableAddFooter(&cont, tmpbuf.data);
1244 add_tablespace_footer(&cont, tableinfo.relkind,
1245 tableinfo.tablespace, true);
1246 }
1247
1248 PQclear(result);
1249 }
1250 else if (view_def)
1251 {
1252 PGresult *result = NULL;
1253
1254 /* Footer information about a view */
1255 printTableAddFooter(&cont, _("View definition:"));
1256 printTableAddFooter(&cont, view_def);
1257
1258 /* print rules */
1259 if (tableinfo.hasrules)
1260 {
1261 printfPQExpBuffer(&buf,
1262 "SELECT r.rulename, trim(trailing ';' from pg_catalog.pg_get_ruledef(r.oid, true))\n"
1263 "FROM pg_catalog.pg_rewrite r\n"
1264 "WHERE r.ev_class = '%s' AND r.rulename != '_RETURN' ORDER BY 1",
1265 oid);
1266 result = PSQLexec(buf.data, false);
1267 if (!result)
1268 goto error_return;
1269
1270 if (PQntuples(result) > 0)
1271 {
1272 printTableAddFooter(&cont, _("Rules:"));
1273 for (i = 0; i < PQntuples(result); i++)
1274 {
1275 const char *ruledef;
1276
1277 /* Everything after "CREATE RULE" is echoed verbatim */
1278 ruledef = PQgetvalue(result, i, 1);
1279 ruledef += 12;
1280
1281 printfPQExpBuffer(&buf, " %s", ruledef);
1282 printTableAddFooter(&cont, buf.data);
1283 }
1284 }
1285 PQclear(result);
1286 }
1287 }
1288 else if (tableinfo.relkind == 'r')
1289 {
1290 /* Footer information about a table */
1291 PGresult *result = NULL;
1292 int tuples = 0;
1293
1294 /* print indexes */
1295 if (tableinfo.hasindex)
1296 {
1297 printfPQExpBuffer(&buf,
1298 "SELECT c2.relname, i.indisprimary, i.indisunique, i.indisclustered, ");
1299 if (pset.sversion >= 80200)
1300 appendPQExpBuffer(&buf, "i.indisvalid, ");
1301 else
1302 appendPQExpBuffer(&buf, "true as indisvalid, ");
1303 appendPQExpBuffer(&buf, "pg_catalog.pg_get_indexdef(i.indexrelid, 0, true)");
1304 if (pset.sversion >= 80000)
1305 appendPQExpBuffer(&buf, ", c2.reltablespace");
1306 appendPQExpBuffer(&buf,
1307 "\nFROM pg_catalog.pg_class c, pg_catalog.pg_class c2, pg_catalog.pg_index i\n"
1308 "WHERE c.oid = '%s' AND c.oid = i.indrelid AND i.indexrelid = c2.oid\n"
1309 "ORDER BY i.indisprimary DESC, i.indisunique DESC, c2.relname",
1310 oid);
1311 result = PSQLexec(buf.data, false);
1312 if (!result)
1313 goto error_return;
1314 else
1315 tuples = PQntuples(result);
1316
1317 if (tuples > 0)
1318 {
1319 printTableAddFooter(&cont, _("Indexes:"));
1320 for (i = 0; i < tuples; i++)
1321 {
1322 const char *indexdef;
1323 const char *usingpos;
1324
1325 /* untranslated index name */
1326 printfPQExpBuffer(&buf, " \"%s\"",
1327 PQgetvalue(result, i, 0));
1328
1329 /* Label as primary key or unique (but not both) */
1330 appendPQExpBuffer(&buf,
1331 strcmp(PQgetvalue(result, i, 1), "t") == 0
1332 ? " PRIMARY KEY," :
1333 (strcmp(PQgetvalue(result, i, 2), "t") == 0
1334 ? " UNIQUE,"
1335 : ""));
1336 /* Everything after "USING" is echoed verbatim */
1337 indexdef = PQgetvalue(result, i, 5);
1338 usingpos = strstr(indexdef, " USING ");
1339 if (usingpos)
1340 indexdef = usingpos + 7;
1341
1342 appendPQExpBuffer(&buf, " %s", indexdef);
1343
1344 if (strcmp(PQgetvalue(result, i, 3), "t") == 0)
1345 appendPQExpBuffer(&buf, " CLUSTER");
1346
1347 if (strcmp(PQgetvalue(result, i, 4), "t") != 0)
1348 appendPQExpBuffer(&buf, " INVALID");
1349
1350 printTableAddFooter(&cont, buf.data);
1351
1352 /* Print tablespace of the index on the same line */
1353 if (pset.sversion >= 80000)
1354 add_tablespace_footer(&cont, 'i',
1355 atooid(PQgetvalue(result, i, 6)),
1356 false);
1357 }
1358 }
1359 PQclear(result);
1360 }
1361
1362 /* print table (and column) check constraints */
1363 if (tableinfo.checks)
1364 {
1365 printfPQExpBuffer(&buf,
1366 "SELECT r.conname, "
1367 "pg_catalog.pg_get_constraintdef(r.oid, true)\n"
1368 "FROM pg_catalog.pg_constraint r\n"
1369 "WHERE r.conrelid = '%s' AND r.contype = 'c'\nORDER BY 1",
1370 oid);
1371 result = PSQLexec(buf.data, false);
1372 if (!result)
1373 goto error_return;
1374 else
1375 tuples = PQntuples(result);
1376
1377 if (tuples > 0)
1378 {
1379 printTableAddFooter(&cont, _("Check constraints:"));
1380 for (i = 0; i < tuples; i++)
1381 {
1382 /* untranslated contraint name and def */
1383 printfPQExpBuffer(&buf, " \"%s\" %s",
1384 PQgetvalue(result, i, 0),
1385 PQgetvalue(result, i, 1));
1386
1387 printTableAddFooter(&cont, buf.data);
1388 }
1389 }
1390 PQclear(result);
1391 }
1392
1393 /* print foreign-key constraints (there are none if no triggers) */
1394 if (tableinfo.hastriggers)
1395 {
1396 printfPQExpBuffer(&buf,
1397 "SELECT conname,\n"
1398 " pg_catalog.pg_get_constraintdef(r.oid, true) as condef\n"
1399 "FROM pg_catalog.pg_constraint r\n"
1400 "WHERE r.conrelid = '%s' AND r.contype = 'f' ORDER BY 1",
1401 oid);
1402 result = PSQLexec(buf.data, false);
1403 if (!result)
1404 goto error_return;
1405 else
1406 tuples = PQntuples(result);
1407
1408 if (tuples > 0)
1409 {
1410 printTableAddFooter(&cont, _("Foreign-key constraints:"));
1411 for (i = 0; i < tuples; i++)
1412 {
1413 /* untranslated constraint name and def */
1414 printfPQExpBuffer(&buf, " \"%s\" %s",
1415 PQgetvalue(result, i, 0),
1416 PQgetvalue(result, i, 1));
1417
1418 printTableAddFooter(&cont, buf.data);
1419 }
1420 }
1421 PQclear(result);
1422 }
1423
1424 /* print incoming foreign-key references (none if no triggers) */
1425 if (tableinfo.hastriggers)
1426 {
1427 printfPQExpBuffer(&buf,
1428 "SELECT conname, conrelid::pg_catalog.regclass,\n"
1429 " pg_catalog.pg_get_constraintdef(c.oid, true) as condef\n"
1430 "FROM pg_catalog.pg_constraint c\n"
1431 "WHERE c.confrelid = '%s' AND c.contype = 'f' ORDER BY 1",
1432 oid);
1433 result = PSQLexec(buf.data, false);
1434 if (!result)
1435 goto error_return;
1436 else
1437 tuples = PQntuples(result);
1438
1439 if (tuples > 0)
1440 {
1441 printTableAddFooter(&cont, _("Referenced by:"));
1442 for (i = 0; i < tuples; i++)
1443 {
1444 /* translator: the first %s is a FK name, the following are
1445 * a table name and the FK definition */
1446 printfPQExpBuffer(&buf, _(" \"%s\" IN %s %s"),
1447 PQgetvalue(result, i, 0),
1448 PQgetvalue(result, i, 1),
1449 PQgetvalue(result, i, 2));
1450
1451 printTableAddFooter(&cont, buf.data);
1452 }
1453 }
1454 PQclear(result);
1455 }
1456
1457 /* print rules */
1458 if (tableinfo.hasrules)
1459 {
1460 if (pset.sversion >= 80300)
1461 {
1462 printfPQExpBuffer(&buf,
1463 "SELECT r.rulename, trim(trailing ';' from pg_catalog.pg_get_ruledef(r.oid, true)), "
1464 "ev_enabled\n"
1465 "FROM pg_catalog.pg_rewrite r\n"
1466 "WHERE r.ev_class = '%s' ORDER BY 1",
1467 oid);
1468 }
1469 else
1470 {
1471 printfPQExpBuffer(&buf,
1472 "SELECT r.rulename, trim(trailing ';' from pg_catalog.pg_get_ruledef(r.oid, true)), "
1473 "'O'::char AS ev_enabled\n"
1474 "FROM pg_catalog.pg_rewrite r\n"
1475 "WHERE r.ev_class = '%s' ORDER BY 1",
1476 oid);
1477 }
1478 result = PSQLexec(buf.data, false);
1479 if (!result)
1480 goto error_return;
1481 else
1482 tuples = PQntuples(result);
1483
1484 if (tuples > 0)
1485 {
1486 bool have_heading;
1487 int category;
1488
1489 for (category = 0; category < 4; category++)
1490 {
1491 have_heading = false;
1492
1493 for (i = 0; i < tuples; i++)
1494 {
1495 const char *ruledef;
1496 bool list_rule = false;
1497
1498 switch (category)
1499 {
1500 case 0:
1501 if (*PQgetvalue(result, i, 2) == 'O')
1502 list_rule = true;
1503 break;
1504 case 1:
1505 if (*PQgetvalue(result, i, 2) == 'D')
1506 list_rule = true;
1507 break;
1508 case 2:
1509 if (*PQgetvalue(result, i, 2) == 'A')
1510 list_rule = true;
1511 break;
1512 case 3:
1513 if (*PQgetvalue(result, i, 2) == 'R')
1514 list_rule = true;
1515 break;
1516 }
1517 if (!list_rule)
1518 continue;
1519
1520 if (!have_heading)
1521 {
1522 switch (category)
1523 {
1524 case 0:
1525 printfPQExpBuffer(&buf, _("Rules:"));
1526 break;
1527 case 1:
1528 printfPQExpBuffer(&buf, _("Disabled rules:"));
1529 break;
1530 case 2:
1531 printfPQExpBuffer(&buf, _("Rules firing always:"));
1532 break;
1533 case 3:
1534 printfPQExpBuffer(&buf, _("Rules firing on replica only:"));
1535 break;
1536 }
1537 printTableAddFooter(&cont, buf.data);
1538 have_heading = true;
1539 }
1540
1541 /* Everything after "CREATE RULE" is echoed verbatim */
1542 ruledef = PQgetvalue(result, i, 1);
1543 ruledef += 12;
1544 printfPQExpBuffer(&buf, " %s", ruledef);
1545 printTableAddFooter(&cont, buf.data);
1546 }
1547 }
1548 }
1549 PQclear(result);
1550 }
1551
1552 /* print triggers (but ignore foreign-key triggers) */
1553 if (tableinfo.hastriggers)
1554 {
1555 printfPQExpBuffer(&buf,
1556 "SELECT t.tgname, "
1557 "pg_catalog.pg_get_triggerdef(t.oid), "
1558 "t.tgenabled\n"
1559 "FROM pg_catalog.pg_trigger t\n"
1560 "WHERE t.tgrelid = '%s' AND ",
1561 oid);
1562 if (pset.sversion >= 80300)
1563 appendPQExpBuffer(&buf, "t.tgconstraint = 0");
1564 else
1565 appendPQExpBuffer(&buf,
1566 "(NOT tgisconstraint "
1567 " OR NOT EXISTS"
1568 " (SELECT 1 FROM pg_catalog.pg_depend d "
1569 " JOIN pg_catalog.pg_constraint c ON (d.refclassid = c.tableoid AND d.refobjid = c.oid) "
1570 " WHERE d.classid = t.tableoid AND d.objid = t.oid AND d.deptype = 'i' AND c.contype = 'f'))");
1571 appendPQExpBuffer(&buf, "\nORDER BY 1");
1572
1573 result = PSQLexec(buf.data, false);
1574 if (!result)
1575 goto error_return;
1576 else
1577 tuples = PQntuples(result);
1578
1579 if (tuples > 0)
1580 {
1581 bool have_heading;
1582 int category;
1583
1584 /*
1585 * split the output into 4 different categories. Enabled triggers,
1586 * disabled triggers and the two special ALWAYS and REPLICA
1587 * configurations.
1588 */
1589 for (category = 0; category < 4; category++)
1590 {
1591 have_heading = false;
1592 for (i = 0; i < tuples; i++)
1593 {
1594 bool list_trigger;
1595 const char *tgdef;
1596 const char *usingpos;
1597 const char *tgenabled;
1598
1599 /* Check if this trigger falls into the current category */
1600 tgenabled = PQgetvalue(result, i, 2);
1601 list_trigger = false;
1602 switch (category)
1603 {
1604 case 0:
1605 if (*tgenabled == 'O' || *tgenabled == 't')
1606 list_trigger = true;
1607 break;
1608 case 1:
1609 if (*tgenabled == 'D' || *tgenabled == 'f')
1610 list_trigger = true;
1611 break;
1612 case 2:
1613 if (*tgenabled == 'A')
1614 list_trigger = true;
1615 break;
1616 case 3:
1617 if (*tgenabled == 'R')
1618 list_trigger = true;
1619 break;
1620 }
1621 if (list_trigger == false)
1622 continue;
1623
1624 /* Print the category heading once */
1625 if (have_heading == false)
1626 {
1627 switch (category)
1628 {
1629 case 0:
1630 printfPQExpBuffer(&buf, _("Triggers:"));
1631 break;
1632 case 1:
1633 printfPQExpBuffer(&buf, _("Disabled triggers:"));
1634 break;
1635 case 2:
1636 printfPQExpBuffer(&buf, _("Triggers firing always:"));
1637 break;
1638 case 3:
1639 printfPQExpBuffer(&buf, _("Triggers firing on replica only:"));
1640 break;
1641
1642 }
1643 printTableAddFooter(&cont, buf.data);
1644 have_heading = true;
1645 }
1646
1647 /* Everything after "TRIGGER" is echoed verbatim */
1648 tgdef = PQgetvalue(result, i, 1);
1649 usingpos = strstr(tgdef, " TRIGGER ");
1650 if (usingpos)
1651 tgdef = usingpos + 9;
1652
1653 printfPQExpBuffer(&buf, " %s", tgdef);
1654 printTableAddFooter(&cont, buf.data);
1655 }
1656 }
1657 }
1658 PQclear(result);
1659 }
1660
1661 /* print inherited tables */
1662 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);
1663
1664 result = PSQLexec(buf.data, false);
1665 if (!result)
1666 goto error_return;
1667 else
1668 tuples = PQntuples(result);
1669
1670 for (i = 0; i < tuples; i++)
1671 {
1672 const char *s = _("Inherits");
1673
1674 if (i == 0)
1675 printfPQExpBuffer(&buf, "%s: %s", s, PQgetvalue(result, i, 0));
1676 else
1677 printfPQExpBuffer(&buf, "%*s %s", (int) strlen(s), "", PQgetvalue(result, i, 0));
1678 if (i < tuples - 1)
1679 appendPQExpBuffer(&buf, ",");
1680
1681 printTableAddFooter(&cont, buf.data);
1682 }
1683 PQclear(result);
1684
1685 if (verbose)
1686 {
1687 const char *s = _("Has OIDs");
1688
1689 printfPQExpBuffer(&buf, "%s: %s", s,
1690 (tableinfo.hasoids ? _("yes") : _("no")));
1691 printTableAddFooter(&cont, buf.data);
1692
1693 /* print reloptions */
1694 if (pset.sversion >= 80200)
1695 {
1696 if (tableinfo.reloptions && tableinfo.reloptions[0] != '\0')
1697 {
1698 const char *t = _("Options");
1699
1700 printfPQExpBuffer(&buf, "%s: %s", t,
1701 tableinfo.reloptions);
1702 printTableAddFooter(&cont, buf.data);
1703 }
1704 }
1705 }
1706
1707 add_tablespace_footer(&cont, tableinfo.relkind, tableinfo.tablespace,
1708 true);
1709 }
1710
1711 printTable(&cont, pset.queryFout, pset.logfile);
1712 printTableCleanup(&cont);
1713
1714 retval = true;
1715
1716 error_return:
1717
1718 /* clean up */
1719 if (printTableInitialized)
1720 printTableCleanup(&cont);
1721 termPQExpBuffer(&buf);
1722 termPQExpBuffer(&title);
1723 termPQExpBuffer(&tmpbuf);
1724
1725 if (seq_values)
1726 {
1727 for (ptr = seq_values; *ptr; ptr++)
1728 free(*ptr);
1729 free(seq_values);
1730 }
1731
1732 if (modifiers)
1733 {
1734 for (ptr = modifiers; *ptr; ptr++)
1735 free(*ptr);
1736 free(modifiers);
1737 }
1738
1739 if (view_def)
1740 free(view_def);
1741
1742 if (res)
1743 PQclear(res);
1744
1745 return retval;
1746 }
1747
1748 /*
1749 * Add a tablespace description to a footer. If 'newline' is true, it is added
1750 * in a new line; otherwise it's appended to the current value of the last
1751 * footer.
1752 */
1753 static void
1754 add_tablespace_footer(printTableContent *const cont, char relkind,
1755 Oid tablespace, const bool newline)
1756 {
1757 /* relkinds for which we support tablespaces */
1758 if (relkind == 'r' || relkind == 'i')
1759 {
1760 /*
1761 * We ignore the database default tablespace so that users not using
1762 * tablespaces don't need to know about them. This case also covers
1763 * pre-8.0 servers, for which tablespace will always be 0.
1764 */
1765 if (tablespace != 0)
1766 {
1767 PGresult *result = NULL;
1768 PQExpBufferData buf;
1769
1770 initPQExpBuffer(&buf);
1771 printfPQExpBuffer(&buf,
1772 "SELECT spcname FROM pg_catalog.pg_tablespace\n"
1773 "WHERE oid = '%u'", tablespace);
1774 result = PSQLexec(buf.data, false);
1775 if (!result)
1776 return;
1777 /* Should always be the case, but.... */
1778 if (PQntuples(result) > 0)
1779 {
1780 if (newline)
1781 {
1782 /* Add the tablespace as a new footer */
1783 printfPQExpBuffer(&buf, _("Tablespace: \"%s\""),
1784 PQgetvalue(result, 0, 0));
1785 printTableAddFooter(cont, buf.data);
1786 }
1787 else
1788 {
1789 /* Append the tablespace to the latest footer */
1790 printfPQExpBuffer(&buf, "%s", cont->footer->data);
1791 /* translator: before this string there's an index
1792 * description like '"foo_pkey" PRIMARY KEY, btree (a)' */
1793 appendPQExpBuffer(&buf, _(", tablespace \"%s\""),
1794 PQgetvalue(result, 0, 0));
1795 printTableSetFooter(cont, buf.data);
1796 }
1797 }
1798 PQclear(result);
1799 termPQExpBuffer(&buf);
1800 }
1801 }
1802 }
1803
1804 /*
1805 * \du or \dg
1806 *
1807 * Describes roles. Any schema portion of the pattern is ignored.
1808 */
1809 bool
1810 describeRoles(const char *pattern, bool verbose)
1811 {
1812 PQExpBufferData buf;
1813 PGresult *res;
1814 printTableContent cont;
1815 printTableOpt myopt = pset.popt.topt;
1816 int ncols = 3;
1817 int nrows = 0;
1818 int i;
1819 int conns;
1820 const char align = 'l';
1821 char **attr;
1822
1823 initPQExpBuffer(&buf);
1824
1825 if (pset.sversion >= 80100)
1826 {
1827 printfPQExpBuffer(&buf,
1828 "SELECT r.rolname, r.rolsuper, r.rolinherit,\n"
1829 " r.rolcreaterole, r.rolcreatedb, r.rolcanlogin,\n"
1830 " r.rolconnlimit,\n"
1831 " ARRAY(SELECT b.rolname\n"
1832 " FROM pg_catalog.pg_auth_members m\n"
1833 " JOIN pg_catalog.pg_roles b ON (m.roleid = b.oid)\n"
1834 " WHERE m.member = r.oid) as memberof");
1835
1836 if (verbose && pset.sversion >= 80200)
1837 {
1838 appendPQExpBufferStr(&buf, "\n, pg_catalog.shobj_description(r.oid, 'pg_authid') AS description");
1839 ncols++;
1840 }
1841
1842 appendPQExpBufferStr(&buf, "\nFROM pg_catalog.pg_roles r\n");
1843
1844 processSQLNamePattern(pset.db, &buf, pattern, false, false,
1845 NULL, "r.rolname", NULL, NULL);
1846 }
1847 else
1848 {
1849 printfPQExpBuffer(&buf,
1850 "SELECT u.usename AS rolname,\n"
1851 " u.usesuper AS rolsuper,\n"
1852 " true AS rolinherit, false AS rolcreaterole,\n"
1853 " u.usecreatedb AS rolcreatedb, true AS rolcanlogin,\n"
1854 " -1 AS rolconnlimit,\n"
1855 " ARRAY(SELECT g.groname FROM pg_catalog.pg_group g WHERE u.usesysid = ANY(g.grolist)) as memberof"
1856 "\nFROM pg_catalog.pg_user u\n");
1857
1858 processSQLNamePattern(pset.db, &buf, pattern, false, false,
1859 NULL, "u.usename", NULL, NULL);
1860 }
1861
1862 appendPQExpBuffer(&buf, "ORDER BY 1;");
1863
1864 res = PSQLexec(buf.data, false);
1865 if (!res)
1866 return false;
1867
1868 nrows = PQntuples(res);
1869 attr = pg_malloc_zero((nrows + 1) * sizeof(*attr));
1870
1871 printTableInit(&cont, &myopt, _("List of roles"), ncols, nrows);
1872
1873 printTableAddHeader(&cont, gettext_noop("Role name"), true, align);
1874 printTableAddHeader(&cont, gettext_noop("Attributes"), true, align);
1875 printTableAddHeader(&cont, gettext_noop("Member of"), true, align);
1876
1877 if (verbose && pset.sversion >= 80200)
1878 printTableAddHeader(&cont, gettext_noop("Description"), true, align);
1879
1880 for (i = 0; i < nrows; i++)
1881 {
1882 printTableAddCell(&cont, PQgetvalue(res, i, 0), false);
1883
1884 resetPQExpBuffer(&buf);
1885 if (strcmp(PQgetvalue(res, i, 1), "t") == 0)
1886 add_role_attribute(&buf, _("Superuser"));
1887
1888 if (strcmp(PQgetvalue(res, i, 2), "t") != 0)
1889 add_role_attribute(&buf, _("No inheritance"));
1890
1891 if (strcmp(PQgetvalue(res, i, 3), "t") == 0)
1892 add_role_attribute(&buf, _("Create role"));
1893
1894 if (strcmp(PQgetvalue(res, i, 4), "t") == 0)
1895 add_role_attribute(&buf, _("Create DB"));
1896
1897 if (strcmp(PQgetvalue(res, i, 5), "t") != 0)
1898 add_role_attribute(&buf, _("Cannot login"));
1899
1900 conns = atoi(PQgetvalue(res, i, 6));
1901 if (conns >= 0)
1902 {
1903 if (buf.len > 0)
1904 appendPQExpBufferStr(&buf, "\n");
1905
1906 if (conns == 0)
1907 appendPQExpBuffer(&buf, _("No connections"));
1908 else
1909 appendPQExpBuffer(&buf, ngettext("1 connection", "%d connections", conns), conns);
1910 }
1911
1912 attr[i] = pg_strdup(buf.data);
1913
1914 printTableAddCell(&cont, attr[i], false);
1915
1916 printTableAddCell(&cont, PQgetvalue(res, i, 7), false);
1917
1918 if (verbose && pset.sversion >= 80200)
1919 printTableAddCell(&cont, PQgetvalue(res, i, 8), false);
1920 }
1921 termPQExpBuffer(&buf);
1922
1923 printTable(&cont, pset.queryFout, pset.logfile);
1924 printTableCleanup(&cont);
1925
1926 for (i = 0; i < nrows; i++)
1927 free(attr[i]);
1928 free(attr);
1929
1930 PQclear(res);
1931 return true;
1932 }
1933
1934 void
1935 add_role_attribute(PQExpBuffer buf, const char *const str)
1936 {
1937 if (buf->len > 0)
1938 appendPQExpBufferStr(buf, "\n");
1939
1940 appendPQExpBufferStr(buf, str);
1941 }
1942
1943
1944 /*
1945 * listTables()
1946 *
1947 * handler for \dt, \di, etc.
1948 *
1949 * tabtypes is an array of characters, specifying what info is desired:
1950 * t - tables
1951 * i - indexes
1952 * v - views
1953 * s - sequences
1954 * (any order of the above is fine)
1955 * If tabtypes is empty, we default to \dtvs.
1956 */
1957 bool
1958 listTables(const char *tabtypes, const char *pattern, bool verbose, bool showSystem)
1959 {
1960 bool showTables = strchr(tabtypes, 't') != NULL;
1961 bool showIndexes = strchr(tabtypes, 'i') != NULL;
1962 bool showViews = strchr(tabtypes, 'v') != NULL;
1963 bool showSeq = strchr(tabtypes, 's') != NULL;
1964
1965 PQExpBufferData buf;
1966 PGresult *res;
1967 printQueryOpt myopt = pset.popt;
1968 static const bool translate_columns[] = {false, false, true, false, false, false, false};
1969
1970 if (!(showTables || showIndexes || showViews || showSeq))
1971 showTables = showViews = showSeq = true;
1972
1973 initPQExpBuffer(&buf);
1974
1975 /*
1976 * Note: as of Pg 8.2, we no longer use relkind 's', but we keep it here
1977 * for backwards compatibility.
1978 */
1979 printfPQExpBuffer(&buf,
1980 "SELECT n.nspname as \"%s\",\n"
1981 " c.relname as \"%s\",\n"
1982 " 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"
1983 " pg_catalog.pg_get_userbyid(c.relowner) as \"%s\"",
1984 gettext_noop("Schema"),
1985 gettext_noop("Name"),
1986 gettext_noop("table"),
1987 gettext_noop("view"),
1988 gettext_noop("index"),
1989 gettext_noop("sequence"),
1990 gettext_noop("special"),
1991 gettext_noop("Type"),
1992 gettext_noop("Owner"));
1993
1994 if (showIndexes)
1995 appendPQExpBuffer(&buf,
1996 ",\n c2.relname as \"%s\"",
1997 gettext_noop("Table"));
1998
1999 if (verbose && pset.sversion >= 80100)
2000 appendPQExpBuffer(&buf,
2001 ",\n pg_catalog.pg_size_pretty(pg_catalog.pg_relation_size(c.oid)) as \"%s\"",
2002 gettext_noop("Size"));
2003 if (verbose)
2004 appendPQExpBuffer(&buf,
2005 ",\n pg_catalog.obj_description(c.oid, 'pg_class') as \"%s\"",
2006 gettext_noop("Description"));
2007
2008 appendPQExpBuffer(&buf,
2009 "\nFROM pg_catalog.pg_class c"
2010 "\n LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace");
2011 if (showIndexes)
2012 appendPQExpBuffer(&buf,
2013 "\n LEFT JOIN pg_catalog.pg_index i ON i.indexrelid = c.oid"
2014 "\n LEFT JOIN pg_catalog.pg_class c2 ON i.indrelid = c2.oid");
2015
2016 appendPQExpBuffer(&buf, "\nWHERE c.relkind IN (");
2017 if (showTables)
2018 appendPQExpBuffer(&buf, "'r',");
2019 if (showViews)
2020 appendPQExpBuffer(&buf, "'v',");
2021 if (showIndexes)
2022 appendPQExpBuffer(&buf, "'i',");
2023 if (showSeq)
2024 appendPQExpBuffer(&buf, "'S',");
2025 if (showSystem || pattern)
2026 appendPQExpBuffer(&buf, "'s',"); /* was RELKIND_SPECIAL in <= 8.1 */
2027 appendPQExpBuffer(&buf, "''"); /* dummy */
2028 appendPQExpBuffer(&buf, ")\n");
2029
2030 if (!showSystem && !pattern)
2031 appendPQExpBuffer(&buf, " AND n.nspname <> 'pg_catalog'\n"
2032 " AND n.nspname <> 'information_schema'\n");
2033
2034 /*
2035 * TOAST objects are suppressed unconditionally. Since we don't provide
2036 * any way to select relkind 't' above, we would never show toast tables
2037 * in any case; it seems a bit confusing to allow their indexes to be
2038 * shown. Use plain \d if you really need to look at a TOAST table/index.
2039 */
2040 appendPQExpBuffer(&buf, " AND n.nspname !~ '^pg_toast'\n");
2041
2042 processSQLNamePattern(pset.db, &buf, pattern, true, false,
2043 "n.nspname", "c.relname", NULL,
2044 "pg_catalog.pg_table_is_visible(c.oid)");
2045
2046 appendPQExpBuffer(&buf, "ORDER BY 1,2;");
2047
2048 res = PSQLexec(buf.data, false);
2049 termPQExpBuffer(&buf);
2050 if (!res)
2051 return false;
2052
2053 if (PQntuples(res) == 0 && !pset.quiet)
2054 {
2055 if (pattern)
2056 fprintf(pset.queryFout, _("No matching relations found.\n"));
2057 else
2058 fprintf(pset.queryFout, _("No relations found.\n"));
2059 }
2060 else
2061 {
2062 myopt.nullPrint = NULL;
2063 myopt.title = _("List of relations");
2064 myopt.translate_header = true;
2065 myopt.translate_columns = translate_columns;
2066
2067 printQuery(res, &myopt, pset.queryFout, pset.logfile);
2068 }
2069
2070 PQclear(res);
2071 return true;
2072 }
2073
2074
2075 /*
2076 * \dD
2077 *
2078 * Describes domains.
2079 */
2080 bool
2081 listDomains(const char *pattern, bool showSystem)
2082 {
2083 PQExpBufferData buf;
2084 PGresult *res;
2085 printQueryOpt myopt = pset.popt;
2086
2087 initPQExpBuffer(&buf);
2088
2089 printfPQExpBuffer(&buf,
2090 "SELECT n.nspname as \"%s\",\n"
2091 " t.typname as \"%s\",\n"
2092 " pg_catalog.format_type(t.typbasetype, t.typtypmod) as \"%s\",\n"
2093 " CASE WHEN t.typnotnull AND t.typdefault IS NOT NULL THEN 'not null default '||t.typdefault\n"
2094 " WHEN t.typnotnull AND t.typdefault IS NULL THEN 'not null'\n"
2095 " WHEN NOT t.typnotnull AND t.typdefault IS NOT NULL THEN 'default '||t.typdefault\n"
2096 " ELSE ''\n"
2097 " END as \"%s\",\n"
2098 " pg_catalog.array_to_string(ARRAY(\n"
2099 " SELECT pg_catalog.pg_get_constraintdef(r.oid, true) FROM pg_catalog.pg_constraint r WHERE t.oid = r.contypid\n"
2100 " ), ' ') as \"%s\"\n"
2101 "FROM pg_catalog.pg_type t\n"
2102 " LEFT JOIN pg_catalog.pg_namespace n ON n.oid = t.typnamespace\n"
2103 "WHERE t.typtype = 'd'\n",
2104 gettext_noop("Schema"),
2105 gettext_noop("Name"),
2106 gettext_noop("Type"),
2107 gettext_noop("Modifier"),
2108 gettext_noop("Check"));
2109
2110 if (!showSystem && !pattern)
2111 appendPQExpBuffer(&buf, " AND n.nspname <> 'pg_catalog'\n"
2112 " AND n.nspname <> 'information_schema'\n");
2113
2114 processSQLNamePattern(pset.db, &buf, pattern, true, false,
2115 "n.nspname", "t.typname", NULL,
2116 "pg_catalog.pg_type_is_visible(t.oid)");
2117
2118 appendPQExpBuffer(&buf, "ORDER BY 1, 2;");
2119
2120 res = PSQLexec(buf.data, false);
2121 termPQExpBuffer(&buf);
2122 if (!res)
2123 return false;
2124
2125 myopt.nullPrint = NULL;
2126 myopt.title = _("List of domains");
2127 myopt.translate_header = true;
2128
2129 printQuery(res, &myopt, pset.queryFout, pset.logfile);
2130
2131 PQclear(res);
2132 return true;
2133 }
2134
2135 /*
2136 * \dc
2137 *
2138 * Describes conversions.
2139 */
2140 bool
2141 listConversions(const char *pattern, bool showSystem)
2142 {
2143 PQExpBufferData buf;
2144 PGresult *res;
2145 printQueryOpt myopt = pset.popt;
2146 static const bool translate_columns[] = {false, false, false, false, true};
2147
2148 initPQExpBuffer(&buf);
2149
2150 printfPQExpBuffer(&buf,
2151 "SELECT n.nspname AS \"%s\",\n"
2152 " c.conname AS \"%s\",\n"
2153 " pg_catalog.pg_encoding_to_char(c.conforencoding) AS \"%s\",\n"
2154 " pg_catalog.pg_encoding_to_char(c.contoencoding) AS \"%s\",\n"
2155 " CASE WHEN c.condefault THEN '%s'\n"
2156 " ELSE '%s' END AS \"%s\"\n"
2157 "FROM pg_catalog.pg_conversion c, pg_catalog.pg_namespace n\n"
2158 "WHERE n.oid = c.connamespace\n",
2159 gettext_noop("Schema"),
2160 gettext_noop("Name"),
2161 gettext_noop("Source"),
2162 gettext_noop("Destination"),
2163 gettext_noop("yes"), gettext_noop("no"),
2164 gettext_noop("Default?"));
2165
2166 if (!showSystem && !pattern)
2167 appendPQExpBuffer(&buf, " AND n.nspname <> 'pg_catalog'\n"
2168 " AND n.nspname <> 'information_schema'\n");
2169
2170 processSQLNamePattern(pset.db, &buf, pattern, true, false,
2171 "n.nspname", "c.conname", NULL,
2172 "pg_catalog.pg_conversion_is_visible(c.oid)");
2173
2174 appendPQExpBuffer(&buf, "ORDER BY 1, 2;");
2175
2176 res = PSQLexec(buf.data, false);
2177 termPQExpBuffer(&buf);
2178 if (!res)
2179 return false;
2180
2181 myopt.nullPrint = NULL;
2182 myopt.title = _("List of conversions");
2183 myopt.translate_header = true;
2184 myopt.translate_columns = translate_columns;
2185
2186 printQuery(res, &myopt, pset.queryFout, pset.logfile);
2187
2188 PQclear(res);
2189 return true;
2190 }
2191
2192 /*
2193 * \dC
2194 *
2195 * Describes casts.
2196 */
2197 bool
2198 listCasts(const char *pattern)
2199 {
2200 PQExpBufferData buf;
2201 PGresult *res;
2202 printQueryOpt myopt = pset.popt;
2203 static const bool translate_columns[] = {false, false, false, true};
2204
2205 initPQExpBuffer(&buf);
2206 /*
2207 * We need a left join to pg_proc for binary casts; the others are just
2208 * paranoia. Also note that we don't attempt to localize '(binary
2209 * coercible)', because there's too much risk of gettext translating a
2210 * function name that happens to match some string in the PO database.
2211 */
2212 printfPQExpBuffer(&buf,
2213 "SELECT pg_catalog.format_type(castsource, NULL) AS \"%s\",\n"
2214 " pg_catalog.format_type(casttarget, NULL) AS \"%s\",\n"
2215 " CASE WHEN castfunc = 0 THEN '(binary coercible)'\n"
2216 " ELSE p.proname\n"
2217 " END as \"%s\",\n"
2218 " CASE WHEN c.castcontext = 'e' THEN '%s'\n"
2219 " WHEN c.castcontext = 'a' THEN '%s'\n"
2220 " ELSE '%s'\n"
2221 " END as \"%s\"\n"
2222 "FROM pg_catalog.pg_cast c LEFT JOIN pg_catalog.pg_proc p\n"
2223 " ON c.castfunc = p.oid\n"
2224 " LEFT JOIN pg_catalog.pg_type ts\n"
2225 " ON c.castsource = ts.oid\n"
2226 " LEFT JOIN pg_catalog.pg_namespace ns\n"
2227 " ON ns.oid = ts.typnamespace\n"
2228 " LEFT JOIN pg_catalog.pg_type tt\n"
2229 " ON c.casttarget = tt.oid\n"
2230 " LEFT JOIN pg_catalog.pg_namespace nt\n"
2231 " ON nt.oid = tt.typnamespace\n"
2232 "WHERE (true",
2233 gettext_noop("Source type"),
2234 gettext_noop("Target type"),
2235 gettext_noop("Function"),
2236 gettext_noop("no"), gettext_noop("in assignment"), gettext_noop("yes"),
2237 gettext_noop("Implicit?"));
2238
2239 /*
2240 * Match name pattern against either internal or external name of either
2241 * castsource or casttarget
2242 */
2243 processSQLNamePattern(pset.db, &buf, pattern, true, false,
2244 "ns.nspname", "ts.typname",
2245 "pg_catalog.format_type(ts.oid, NULL)",
2246 "pg_catalog.pg_type_is_visible(ts.oid)");
2247
2248 appendPQExpBuffer(&buf, ") OR (true");
2249
2250 processSQLNamePattern(pset.db, &buf, pattern, true, false,
2251 "nt.nspname", "tt.typname",
2252 "pg_catalog.format_type(tt.oid, NULL)",
2253 "pg_catalog.pg_type_is_visible(tt.oid)");
2254
2255 appendPQExpBuffer(&buf, ")\nORDER BY 1, 2;");
2256
2257 res = PSQLexec(buf.data, false);
2258 termPQExpBuffer(&buf);
2259 if (!res)
2260 return false;
2261
2262 myopt.nullPrint = NULL;
2263 myopt.title = _("List of casts");
2264 myopt.translate_header = true;
2265 myopt.translate_columns = translate_columns;
2266
2267 printQuery(res, &myopt, pset.queryFout, pset.logfile);
2268
2269 PQclear(res);
2270 return true;
2271 }
2272
2273 /*
2274 * \dn
2275 *
2276 * Describes schemas (namespaces)
2277 */
2278 bool
2279 listSchemas(const char *pattern, bool verbose)
2280 {
2281 PQExpBufferData buf;
2282 PGresult *res;
2283 printQueryOpt myopt = pset.popt;
2284
2285 initPQExpBuffer(&buf);
2286 printfPQExpBuffer(&buf,
2287 "SELECT n.nspname AS \"%s\",\n"
2288 " pg_catalog.pg_get_userbyid(n.nspowner) AS \"%s\"",
2289 gettext_noop("Name"),
2290 gettext_noop("Owner"));
2291
2292 if (verbose)
2293 {
2294 appendPQExpBuffer(&buf, ",\n ");
2295 printACLColumn(&buf, "n.nspacl");
2296 appendPQExpBuffer(&buf,
2297 ",\n pg_catalog.obj_description(n.oid, 'pg_namespace') AS \"%s\"",
2298 gettext_noop("Description"));
2299 }
2300
2301 appendPQExpBuffer(&buf,
2302 "\nFROM pg_catalog.pg_namespace n\n"
2303 "WHERE (n.nspname !~ '^pg_temp_' OR\n"
2304 " n.nspname = (pg_catalog.current_schemas(true))[1])\n"); /* temp schema is first */
2305
2306 processSQLNamePattern(pset.db, &buf, pattern, true, false,
2307 NULL, "n.nspname", NULL,
2308 NULL);
2309
2310 appendPQExpBuffer(&buf, "ORDER BY 1;");
2311
2312 res = PSQLexec(buf.data, false);
2313 termPQExpBuffer(&buf);
2314 if (!res)
2315 return false;
2316
2317 myopt.nullPrint = NULL;
2318 myopt.title = _("List of schemas");
2319 myopt.translate_header = true;
2320
2321 printQuery(res, &myopt, pset.queryFout, pset.logfile);
2322
2323 PQclear(res);
2324 return true;
2325 }
2326
2327
2328 /*
2329 * \dFp
2330 * list text search parsers
2331 */
2332 bool
2333 listTSParsers(const char *pattern, bool verbose)
2334 {
2335 PQExpBufferData buf;
2336 PGresult *res;
2337 printQueryOpt myopt = pset.popt;
2338
2339 if (pset.sversion < 80300)
2340 {
2341 fprintf(stderr, _("The server (version %d.%d) does not support full text search.\n"),
2342 pset.sversion / 10000, (pset.sversion / 100) % 100);
2343 return true;
2344 }
2345
2346 if (verbose)
2347 return listTSParsersVerbose(pattern);
2348
2349 initPQExpBuffer(&buf);
2350
2351 printfPQExpBuffer(&buf,
2352 "SELECT \n"
2353 " n.nspname as \"%s\",\n"
2354 " p.prsname as \"%s\",\n"
2355 " pg_catalog.obj_description(p.oid, 'pg_ts_parser') as \"%s\"\n"
2356 "FROM pg_catalog.pg_ts_parser p \n"
2357 "LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.prsnamespace\n",
2358 gettext_noop("Schema"),
2359 gettext_noop("Name"),
2360 gettext_noop("Description")
2361 );
2362
2363 processSQLNamePattern(pset.db, &buf, pattern, false, false,
2364 "n.nspname", "p.prsname", NULL,
2365 "pg_catalog.pg_ts_parser_is_visible(p.oid)");
2366
2367 appendPQExpBuffer(&buf, "ORDER BY 1, 2;");
2368
2369 res = PSQLexec(buf.data, false);
2370 termPQExpBuffer(&buf);
2371 if (!res)
2372 return false;
2373
2374 myopt.nullPrint = NULL;
2375 myopt.title = _("List of text search parsers");
2376 myopt.translate_header = true;
2377
2378 printQuery(res, &myopt, pset.queryFout, pset.logfile);
2379
2380 PQclear(res);
2381 return true;
2382 }
2383
2384 /*
2385 * full description of parsers
2386 */
2387 static bool
2388 listTSParsersVerbose(const char *pattern)
2389 {
2390 PQExpBufferData buf;
2391 PGresult *res;
2392 int i;
2393
2394 initPQExpBuffer(&buf);
2395
2396 printfPQExpBuffer(&buf,
2397 "SELECT p.oid, \n"
2398 " n.nspname, \n"
2399 " p.prsname \n"
2400 "FROM pg_catalog.pg_ts_parser p\n"
2401 "LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.prsnamespace\n"
2402 );
2403
2404 processSQLNamePattern(pset.db, &buf, pattern, false, false,
2405 "n.nspname", "p.prsname", NULL,
2406 "pg_catalog.pg_ts_parser_is_visible(p.oid)");
2407
2408 appendPQExpBuffer(&buf, "ORDER BY 1, 2;");
2409
2410 res = PSQLexec(buf.data, false);
2411 termPQExpBuffer(&buf);
2412 if (!res)
2413 return false;
2414
2415 if (PQntuples(res) == 0)
2416 {
2417 if (!pset.quiet)
2418 fprintf(stderr, _("Did not find any text search parser named \"%s\".\n"),
2419 pattern);
2420 PQclear(res);
2421 return false;
2422 }
2423
2424 for (i = 0; i < PQntuples(res); i++)
2425 {
2426 const char *oid;
2427 const char *nspname = NULL;
2428 const char *prsname;
2429
2430 oid = PQgetvalue(res, i, 0);
2431 if (!PQgetisnull(res, i, 1))
2432 nspname = PQgetvalue(res, i, 1);
2433 prsname = PQgetvalue(res, i, 2);
2434
2435 if (!describeOneTSParser(oid, nspname, prsname))
2436 {
2437 PQclear(res);
2438 return false;
2439 }
2440
2441 if (cancel_pressed)
2442 {
2443 PQclear(res);
2444 return false;
2445 }
2446 }
2447
2448 PQclear(res);
2449 return true;
2450 }
2451
2452 static bool
2453 describeOneTSParser(const char *oid, const char *nspname, const char *prsname)
2454 {
2455 PQExpBufferData buf;
2456 PGresult *res;
2457 char title[1024];
2458 printQueryOpt myopt = pset.popt;
2459 static const bool translate_columns[] = {true, false, false};
2460
2461 initPQExpBuffer(&buf);
2462
2463 printfPQExpBuffer(&buf,
2464 "SELECT '%s' AS \"%s\", \n"
2465 " p.prsstart::pg_catalog.regproc AS \"%s\", \n"
2466 " pg_catalog.obj_description(p.prsstart, 'pg_proc') as \"%s\" \n"
2467 " FROM pg_catalog.pg_ts_parser p \n"
2468 " WHERE p.oid = '%s' \n"
2469 "UNION ALL \n"
2470 "SELECT '%s', \n"
2471 " p.prstoken::pg_catalog.regproc, \n"
2472 " pg_catalog.obj_description(p.prstoken, 'pg_proc') \n"
2473 " FROM pg_catalog.pg_ts_parser p \n"
2474 " WHERE p.oid = '%s' \n"
2475 "UNION ALL \n"
2476 "SELECT '%s', \n"
2477 " p.prsend::pg_catalog.regproc, \n"
2478 " pg_catalog.obj_description(p.prsend, 'pg_proc') \n"
2479 " FROM pg_catalog.pg_ts_parser p \n"
2480 " WHERE p.oid = '%s' \n"
2481 "UNION ALL \n"
2482 "SELECT '%s', \n"
2483 " p.prsheadline::pg_catalog.regproc, \n"
2484 " pg_catalog.obj_description(p.prsheadline, 'pg_proc') \n"
2485 " FROM pg_catalog.pg_ts_parser p \n"
2486 " WHERE p.oid = '%s' \n"
2487 "UNION ALL \n"
2488 "SELECT '%s', \n"
2489 " p.prslextype::pg_catalog.regproc, \n"
2490 " pg_catalog.obj_description(p.prslextype, 'pg_proc') \n"
2491 " FROM pg_catalog.pg_ts_parser p \n"
2492 " WHERE p.oid = '%s' \n",
2493 gettext_noop("Start parse"),
2494 gettext_noop("Method"),
2495 gettext_noop("Function"),
2496 gettext_noop("Description"),
2497 oid,
2498 gettext_noop("Get next token"),
2499 oid,
2500 gettext_noop("End parse"),
2501 oid,
2502 gettext_noop("Get headline"),
2503 oid,
2504 gettext_noop("Get token types"),
2505 oid);
2506
2507 res = PSQLexec(buf.data, false);
2508 termPQExpBuffer(&buf);
2509 if (!res)
2510 return false;
2511
2512 myopt.nullPrint = NULL;
2513 if (nspname)
2514 sprintf(title, _("Text search parser \"%s.%s\""), nspname, prsname);
2515 else
2516 sprintf(title, _("Text search parser \"%s\""), prsname);
2517 myopt.title = title;
2518 myopt.footers = NULL;
2519 myopt.default_footer = false;
2520 myopt.translate_header = true;
2521 myopt.translate_columns = translate_columns;
2522
2523 printQuery(res, &myopt, pset.queryFout, pset.logfile);
2524
2525 PQclear(res);
2526
2527 initPQExpBuffer(&buf);
2528
2529 printfPQExpBuffer(&buf,
2530 "SELECT t.alias as \"%s\", \n"
2531 " t.description as \"%s\" \n"
2532 "FROM pg_catalog.ts_token_type( '%s'::pg_catalog.oid ) as t \n"
2533 "ORDER BY 1;",
2534 gettext_noop("Token name"),
2535 gettext_noop("Description"),
2536 oid);
2537
2538 res = PSQLexec(buf.data, false);
2539 termPQExpBuffer(&buf);
2540 if (!res)
2541 return false;
2542
2543 myopt.nullPrint = NULL;
2544 if (nspname)
2545 sprintf(title, _("Token types for parser \"%s.%s\""), nspname, prsname);
2546 else
2547 sprintf(title, _("Token types for parser \"%s\""), prsname);
2548 myopt.title = title;
2549 myopt.footers = NULL;
2550 myopt.default_footer = true;
2551 myopt.translate_header = true;
2552 myopt.translate_columns = NULL;
2553
2554 printQuery(res, &myopt, pset.queryFout, pset.logfile);
2555
2556 PQclear(res);
2557 return true;
2558 }
2559
2560
2561 /*
2562 * \dFd
2563 * list text search dictionaries
2564 */
2565 bool
2566 listTSDictionaries(const char *pattern, bool verbose)
2567 {
2568 PQExpBufferData buf;
2569 PGresult *res;
2570 printQueryOpt myopt = pset.popt;
2571
2572 if (pset.sversion < 80300)
2573 {
2574 fprintf(stderr, _("The server (version %d.%d) does not support full text search.\n"),
2575 pset.sversion / 10000, (pset.sversion / 100) % 100);
2576 return true;
2577 }
2578
2579 initPQExpBuffer(&buf);
2580
2581 printfPQExpBuffer(&buf,
2582 "SELECT \n"
2583 " n.nspname as \"%s\",\n"
2584 " d.dictname as \"%s\",\n",
2585 gettext_noop("Schema"),
2586 gettext_noop("Name"));
2587
2588 if (verbose)
2589 {
2590 appendPQExpBuffer(&buf,
2591 " ( SELECT COALESCE(nt.nspname, '(null)')::pg_catalog.text || '.' || t.tmplname FROM \n"
2592 " pg_catalog.pg_ts_template t \n"
2593 " LEFT JOIN pg_catalog.pg_namespace nt ON nt.oid = t.tmplnamespace \n"
2594 " WHERE d.dicttemplate = t.oid ) AS \"%s\", \n"
2595 " d.dictinitoption as \"%s\", \n",
2596 gettext_noop("Template"),
2597 gettext_noop("Init options"));
2598 }
2599
2600 appendPQExpBuffer(&buf,
2601 " pg_catalog.obj_description(d.oid, 'pg_ts_dict') as \"%s\"\n",
2602 gettext_noop("Description"));
2603
2604 appendPQExpBuffer(&buf, "FROM pg_catalog.pg_ts_dict d\n"
2605 "LEFT JOIN pg_catalog.pg_namespace n ON n.oid = d.dictnamespace\n");
2606
2607 processSQLNamePattern(pset.db, &buf, pattern, false, false,
2608 "n.nspname", "d.dictname", NULL,
2609 "pg_catalog.pg_ts_dict_is_visible(d.oid)");
2610
2611 appendPQExpBuffer(&buf, "ORDER BY 1, 2;");
2612
2613 res = PSQLexec(buf.data, false);
2614 termPQExpBuffer(&buf);
2615 if (!res)
2616 return false;
2617
2618 myopt.nullPrint = NULL;
2619 myopt.title = _("List of text search dictionaries");
2620 myopt.translate_header = true;
2621
2622 printQuery(res, &myopt, pset.queryFout, pset.logfile);
2623
2624 PQclear(res);
2625 return true;
2626 }
2627
2628
2629 /*
2630 * \dFt
2631 * list text search templates
2632 */
2633 bool
2634 listTSTemplates(const char *pattern, bool verbose)
2635 {
2636 PQExpBufferData buf;
2637 PGresult *res;
2638 printQueryOpt myopt = pset.popt;
2639
2640 if (pset.sversion < 80300)
2641 {
2642 fprintf(stderr, _("The server (version %d.%d) does not support full text search.\n"),
2643 pset.sversion / 10000, (pset.sversion / 100) % 100);
2644 return true;
2645 }
2646
2647 initPQExpBuffer(&buf);
2648
2649 if (verbose)
2650 printfPQExpBuffer(&buf,
2651 "SELECT \n"
2652 " n.nspname AS \"%s\",\n"
2653 " t.tmplname AS \"%s\",\n"
2654 " t.tmplinit::pg_catalog.regproc AS \"%s\",\n"
2655 " t.tmpllexize::pg_catalog.regproc AS \"%s\",\n"
2656 " pg_catalog.obj_description(t.oid, 'pg_ts_template') AS \"%s\"\n",
2657 gettext_noop("Schema"),
2658 gettext_noop("Name"),
2659 gettext_noop("Init"),
2660 gettext_noop("Lexize"),
2661 gettext_noop("Description"));
2662 else
2663 printfPQExpBuffer(&buf,
2664 "SELECT \n"
2665 " n.nspname AS \"%s\",\n"
2666 " t.tmplname AS \"%s\",\n"
2667 " pg_catalog.obj_description(t.oid, 'pg_ts_template') AS \"%s\"\n",
2668 gettext_noop("Schema"),
2669 gettext_noop("Name"),
2670 gettext_noop("Description"));
2671
2672 appendPQExpBuffer(&buf, "FROM pg_catalog.pg_ts_template t\n"
2673 "LEFT JOIN pg_catalog.pg_namespace n ON n.oid = t.tmplnamespace\n");
2674
2675 processSQLNamePattern(pset.db, &buf, pattern, false, false,
2676 "n.nspname", "t.tmplname", NULL,
2677 "pg_catalog.pg_ts_template_is_visible(t.oid)");
2678
2679 appendPQExpBuffer(&buf, "ORDER BY 1, 2;");
2680
2681 res = PSQLexec(buf.data, false);
2682 termPQExpBuffer(&buf);
2683 if (!res)
2684 return false;
2685
2686 myopt.nullPrint = NULL;
2687 myopt.title = _("List of text search templates");
2688 myopt.translate_header = true;
2689
2690 printQuery(res, &myopt, pset.queryFout, pset.logfile);
2691
2692 PQclear(res);
2693 return true;
2694 }
2695
2696
2697 /*
2698 * \dF
2699 * list text search configurations
2700 */
2701 bool
2702 listTSConfigs(const char *pattern, bool verbose)
2703 {
2704 PQExpBufferData buf;
2705 PGresult *res;
2706 printQueryOpt myopt = pset.popt;
2707
2708 if (pset.sversion < 80300)
2709 {
2710 fprintf(stderr, _("The server (version %d.%d) does not support full text search.\n"),
2711 pset.sversion / 10000, (pset.sversion / 100) % 100);
2712 return true;
2713 }
2714
2715 if (verbose)
2716 return listTSConfigsVerbose(pattern);
2717
2718 initPQExpBuffer(&buf);
2719
2720 printfPQExpBuffer(&buf,
2721 "SELECT \n"
2722 " n.nspname as \"%s\",\n"
2723 " c.cfgname as \"%s\",\n"
2724 " pg_catalog.obj_description(c.oid, 'pg_ts_config') as \"%s\"\n"
2725 "FROM pg_catalog.pg_ts_config c\n"
2726 "LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.cfgnamespace \n",
2727 gettext_noop("Schema"),
2728 gettext_noop("Name"),
2729 gettext_noop("Description")
2730 );
2731
2732 processSQLNamePattern(pset.db, &buf, pattern, false, false,
2733 "n.nspname", "c.cfgname", NULL,
2734 "pg_catalog.pg_ts_config_is_visible(c.oid)");
2735
2736 appendPQExpBuffer(&buf, "ORDER BY 1, 2;");
2737
2738 res = PSQLexec(buf.data, false);
2739 termPQExpBuffer(&buf);
2740 if (!res)
2741 return false;
2742
2743 myopt.nullPrint = NULL;
2744 myopt.title = _("List of text search configurations");
2745 myopt.translate_header = true;
2746
2747 printQuery(res, &myopt, pset.queryFout, pset.logfile);
2748
2749 PQclear(res);
2750 return true;
2751 }
2752
2753 static bool
2754 listTSConfigsVerbose(const char *pattern)
2755 {
2756 PQExpBufferData buf;
2757 PGresult *res;
2758 int i;
2759
2760 initPQExpBuffer(&buf);
2761
2762 printfPQExpBuffer(&buf,
2763 "SELECT c.oid, c.cfgname,\n"
2764 " n.nspname, \n"
2765 " p.prsname, \n"
2766 " np.nspname as pnspname \n"
2767 "FROM pg_catalog.pg_ts_config c \n"
2768 " LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.cfgnamespace, \n"
2769 " pg_catalog.pg_ts_parser p \n"
2770 " LEFT JOIN pg_catalog.pg_namespace np ON np.oid = p.prsnamespace \n"
2771 "WHERE p.oid = c.cfgparser\n"
2772 );
2773
2774 processSQLNamePattern(pset.db, &buf, pattern, true, false,
2775 "n.nspname", "c.cfgname", NULL,
2776 "pg_catalog.pg_ts_config_is_visible(c.oid)");
2777
2778 appendPQExpBuffer(&buf, "ORDER BY 3, 2;");
2779
2780 res = PSQLexec(buf.data, false);
2781 termPQExpBuffer(&buf);
2782 if (!res)
2783 return false;
2784
2785 if (PQntuples(res) == 0)
2786 {
2787 if (!pset.quiet)
2788 fprintf(stderr, _("Did not find any text search configuration named \"%s\".\n"),
2789 pattern);
2790 PQclear(res);
2791 return false;
2792 }
2793
2794 for (i = 0; i < PQntuples(res); i++)
2795 {
2796 const char *oid;
2797 const char *cfgname;
2798 const char *nspname = NULL;
2799 const char *prsname;
2800 const char *pnspname = NULL;
2801
2802 oid = PQgetvalue(res, i, 0);
2803 cfgname = PQgetvalue(res, i, 1);
2804 if (!PQgetisnull(res, i, 2))
2805 nspname = PQgetvalue(res, i, 2);
2806 prsname = PQgetvalue(res, i, 3);
2807 if (!PQgetisnull(res, i, 4))
2808 pnspname = PQgetvalue(res, i, 4);
2809
2810 if (!describeOneTSConfig(oid, nspname, cfgname, pnspname, prsname))
2811 {
2812 PQclear(res);
2813 return false;
2814 }
2815
2816 if (cancel_pressed)
2817 {
2818 PQclear(res);
2819 return false;
2820 }
2821 }
2822
2823 PQclear(res);
2824 return true;
2825 }
2826
2827 static bool
2828 describeOneTSConfig(const char *oid, const char *nspname, const char *cfgname,
2829 const char *pnspname, const char *prsname)
2830 {
2831 PQExpBufferData buf,
2832 title;
2833 PGresult *res;
2834 printQueryOpt myopt = pset.popt;
2835
2836 initPQExpBuffer(&buf);
2837
2838 printfPQExpBuffer(&buf,
2839 "SELECT \n"
2840 " ( SELECT t.alias FROM \n"
2841 " pg_catalog.ts_token_type(c.cfgparser) AS t \n"
2842 " WHERE t.tokid = m.maptokentype ) AS \"%s\", \n"
2843 " pg_catalog.btrim( \n"
2844 " ARRAY( SELECT mm.mapdict::pg_catalog.regdictionary \n"
2845 " FROM pg_catalog.pg_ts_config_map AS mm \n"
2846 " WHERE mm.mapcfg = m.mapcfg AND mm.maptokentype = m.maptokentype \n"
2847 " ORDER BY mapcfg, maptokentype, mapseqno \n"
2848 " ) :: pg_catalog.text , \n"
2849 " '{}') AS \"%s\" \n"
2850 "FROM pg_catalog.pg_ts_config AS c, pg_catalog.pg_ts_config_map AS m \n"
2851 "WHERE c.oid = '%s' AND m.mapcfg = c.oid \n"
2852 "GROUP BY m.mapcfg, m.maptokentype, c.cfgparser \n"
2853 "ORDER BY 1",
2854 gettext_noop("Token"),
2855 gettext_noop("Dictionaries"),
2856 oid);
2857
2858 res = PSQLexec(buf.data, false);
2859 termPQExpBuffer(&buf);
2860 if (!res)
2861 return false;
2862
2863 initPQExpBuffer(&title);
2864
2865 if (nspname)
2866 appendPQExpBuffer(&title, _("Text search configuration \"%s.%s\""),
2867 nspname, cfgname);
2868 else
2869 appendPQExpBuffer(&title, _("Text search configuration \"%s\""),
2870 cfgname);
2871
2872 if (pnspname)
2873 appendPQExpBuffer(&title, _("\nParser: \"%s.%s\""),
2874 pnspname, prsname);
2875 else
2876 appendPQExpBuffer(&title, _("\nParser: \"%s\""),
2877 prsname);
2878
2879 myopt.nullPrint = NULL;
2880 myopt.title = title.data;
2881 myopt.footers = NULL;
2882 myopt.default_footer = false;
2883 myopt.translate_header = true;
2884
2885 printQuery(res, &myopt, pset.queryFout, pset.logfile);
2886
2887 termPQExpBuffer(&title);
2888
2889 PQclear(res);
2890 return true;
2891 }
2892
2893
2894 /*
2895 * \dew
2896 *
2897 * Describes foreign-data wrappers
2898 */
2899 bool
2900 listForeignDataWrappers(const char *pattern, bool verbose)
2901 {
2902 PQExpBufferData buf;
2903 PGresult *res;
2904 printQueryOpt myopt = pset.popt;
2905
2906 if (pset.sversion < 80400)
2907 {
2908 fprintf(stderr, _("The server (version %d.%d) does not support foreign-data wrappers.\n"),
2909 pset.sversion / 10000, (pset.sversion / 100) % 100);
2910 return true;
2911 }
2912
2913 initPQExpBuffer(&buf);
2914 printfPQExpBuffer(&buf,
2915 "SELECT fdwname AS \"%s\",\n"
2916 " pg_catalog.pg_get_userbyid(fdwowner) AS \"%s\",\n"
2917 " fdwvalidator::pg_catalog.regproc AS \"%s\"",
2918 gettext_noop("Name"),
2919 gettext_noop("Owner"),
2920 gettext_noop("Validator"));
2921
2922 if (verbose)
2923 {
2924 appendPQExpBuffer(&buf, ",\n ");
2925 printACLColumn(&buf, "fdwacl");
2926 appendPQExpBuffer(&buf,
2927 ",\n fdwoptions AS \"%s\"",
2928 gettext_noop("Options"));
2929 }
2930
2931 appendPQExpBuffer(&buf, "\nFROM pg_catalog.pg_foreign_data_wrapper\n");
2932
2933 processSQLNamePattern(pset.db, &buf, pattern, false, false,
2934 NULL, "fdwname", NULL, NULL);
2935
2936 appendPQExpBuffer(&buf, "ORDER BY 1;");
2937
2938 res = PSQLexec(buf.data, false);
2939 termPQExpBuffer(&buf);
2940 if (!res)
2941 return false;
2942
2943 myopt.nullPrint = NULL;
2944 myopt.title = _("List of foreign-data wrappers");
2945 myopt.translate_header = true;
2946
2947 printQuery(res, &myopt, pset.queryFout, pset.logfile);
2948
2949 PQclear(res);
2950 return true;
2951 }
2952
2953 /*
2954 * \des
2955 *
2956 * Describes foreign servers.
2957 */
2958 bool
2959 listForeignServers(const char *pattern, bool verbose)
2960 {
2961 PQExpBufferData buf;
2962 PGresult *res;
2963 printQueryOpt myopt = pset.popt;
2964
2965 if (pset.sversion < 80400)
2966 {
2967 fprintf(stderr, _("The server (version %d.%d) does not support foreign servers.\n"),
2968 pset.sversion / 10000, (pset.sversion / 100) % 100);
2969 return true;
2970 }
2971
2972 initPQExpBuffer(&buf);
2973 printfPQExpBuffer(&buf,
2974 "SELECT s.srvname AS \"%s\",\n"
2975 " pg_catalog.pg_get_userbyid(s.srvowner) AS \"%s\",\n"
2976 " f.fdwname AS \"%s\"",
2977 gettext_noop("Name"),
2978 gettext_noop("Owner"),
2979 gettext_noop("Foreign-data wrapper"));
2980
2981 if (verbose)
2982 {
2983 appendPQExpBuffer(&buf, ",\n ");
2984 printACLColumn(&buf, "s.srvacl");
2985 appendPQExpBuffer(&buf,
2986 ",\n"
2987 " s.srvtype AS \"%s\",\n"
2988 " s.srvversion AS \"%s\",\n"
2989 " s.srvoptions AS \"%s\"",
2990 gettext_noop("Type"),
2991 gettext_noop("Version"),
2992 gettext_noop("Options"));
2993 }
2994
2995 appendPQExpBuffer(&buf,
2996 "\nFROM pg_catalog.pg_foreign_server s\n"
2997 " JOIN pg_catalog.pg_foreign_data_wrapper f ON f.oid=s.srvfdw\n");
2998
2999 processSQLNamePattern(pset.db, &buf, pattern, false, false,
3000 NULL, "s.srvname", NULL, NULL);
3001
3002 appendPQExpBuffer(&buf, "ORDER BY 1;");
3003
3004 res = PSQLexec(buf.data, false);
3005 termPQExpBuffer(&buf);
3006 if (!res)
3007 return false;
3008
3009 myopt.nullPrint = NULL;
3010 myopt.title = _("List of foreign servers");
3011 myopt.translate_header = true;
3012
3013 printQuery(res, &myopt, pset.queryFout, pset.logfile);
3014
3015 PQclear(res);
3016 return true;
3017 }
3018
3019 /*
3020 * \deu
3021 *
3022 * Describes user mappings.
3023 */
3024 bool
3025 listUserMappings(const char *pattern, bool verbose)
3026 {
3027 PQExpBufferData buf;
3028 PGresult *res;
3029 printQueryOpt myopt = pset.popt;
3030
3031 if (pset.sversion < 80400)
3032 {
3033 fprintf(stderr, _("The server (version %d.%d) does not support user mappings.\n"),
3034 pset.sversion / 10000, (pset.sversion / 100) % 100);
3035 return true;
3036 }
3037
3038 initPQExpBuffer(&buf);
3039 printfPQExpBuffer(&buf,
3040 "SELECT um.srvname AS \"%s\",\n"
3041 " um.usename AS \"%s\"",
3042 gettext_noop("Server"),
3043 gettext_noop("User name"));
3044
3045 if (verbose)
3046 appendPQExpBuffer(&buf,
3047 ",\n um.umoptions AS \"%s\"",
3048 gettext_noop("Options"));
3049
3050 appendPQExpBuffer(&buf, "\nFROM pg_catalog.pg_user_mappings um\n");
3051
3052 processSQLNamePattern(pset.db, &buf, pattern, false, false,
3053 NULL, "um.srvname", "um.usename", NULL);
3054
3055 appendPQExpBuffer(&buf, "ORDER BY 1, 2;");
3056
3057 res = PSQLexec(buf.data, false);
3058 termPQExpBuffer(&buf);
3059 if (!res)
3060 return false;
3061
3062 myopt.nullPrint = NULL;
3063 myopt.title = _("List of user mappings");
3064 myopt.translate_header = true;
3065
3066 printQuery(res, &myopt, pset.queryFout, pset.logfile);
3067
3068 PQclear(res);
3069 return true;
3070 }
3071
3072 /*
3073 * printACLColumn
3074 *
3075 * Helper function for consistently formatting ACL (privilege) columns.
3076 * The proper targetlist entry is appended to buf. Note lack of any
3077 * whitespace or comma decoration.
3078 */
3079 static void
3080 printACLColumn(PQExpBuffer buf, const char *colname)
3081 {
3082 if (pset.sversion >= 80100)
3083 appendPQExpBuffer(buf,
3084 "pg_catalog.array_to_string(%s, E'\\n') AS \"%s\"",
3085 colname, gettext_noop("Access privileges"));
3086 else
3087 appendPQExpBuffer(buf,
3088 "pg_catalog.array_to_string(%s, '\\n') AS \"%s\"",
3089 colname, gettext_noop("Access privileges"));
3090 }
PostgreSQL from CVS