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