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