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-2019, PostgreSQL Global Development Group 10 * 11 * src/bin/psql/describe.c 12 */ 13 #include "postgres_fe.h" 14 15 #include <ctype.h> 16 17 #include "catalog/pg_attribute_d.h" 18 #include "catalog/pg_cast_d.h" 19 #include "catalog/pg_class_d.h" 20 #include "catalog/pg_default_acl_d.h" 21 22 #include "common/logging.h" 23 #include "fe_utils/mbprint.h" 24 #include "fe_utils/print.h" 25 #include "fe_utils/string_utils.h" 26 27 #include "common.h" 28 #include "describe.h" 29 #include "settings.h" 30 #include "variables.h" 31 32 33 static bool describeOneTableDetails(const char *schemaname, 34 const char *relationname, 35 const char *oid, 36 bool verbose); 37 static void add_tablespace_footer(printTableContent *const cont, char relkind, 38 Oid tablespace, const bool newline); 39 static void add_role_attribute(PQExpBuffer buf, const char *const str); 40 static bool listTSParsersVerbose(const char *pattern); 41 static bool describeOneTSParser(const char *oid, const char *nspname, 42 const char *prsname); 43 static bool listTSConfigsVerbose(const char *pattern); 44 static bool describeOneTSConfig(const char *oid, const char *nspname, 45 const char *cfgname, 46 const char *pnspname, const char *prsname); 47 static void printACLColumn(PQExpBuffer buf, const char *colname); 48 static bool listOneExtensionContents(const char *extname, const char *oid); 49 50 51 /*---------------- 52 * Handlers for various slash commands displaying some sort of list 53 * of things in the database. 54 * 55 * Note: try to format the queries to look nice in -E output. 56 *---------------- 57 */ 58 59 60 /* 61 * \da 62 * Takes an optional regexp to select particular aggregates 63 */ 64 bool 65 describeAggregates(const char *pattern, bool verbose, bool showSystem) 66 { 67 PQExpBufferData buf; 68 PGresult *res; 69 printQueryOpt myopt = pset.popt; 70 71 initPQExpBuffer(&buf); 72 73 printfPQExpBuffer(&buf, 74 "SELECT n.nspname as \"%s\",\n" 75 " p.proname AS \"%s\",\n" 76 " pg_catalog.format_type(p.prorettype, NULL) AS \"%s\",\n", 77 gettext_noop("Schema"), 78 gettext_noop("Name"), 79 gettext_noop("Result data type")); 80 81 if (pset.sversion >= 80400) 82 appendPQExpBuffer(&buf, 83 " CASE WHEN p.pronargs = 0\n" 84 " THEN CAST('*' AS pg_catalog.text)\n" 85 " ELSE pg_catalog.pg_get_function_arguments(p.oid)\n" 86 " END AS \"%s\",\n", 87 gettext_noop("Argument data types")); 88 else if (pset.sversion >= 80200) 89 appendPQExpBuffer(&buf, 90 " CASE WHEN p.pronargs = 0\n" 91 " THEN CAST('*' AS pg_catalog.text)\n" 92 " ELSE\n" 93 " pg_catalog.array_to_string(ARRAY(\n" 94 " SELECT\n" 95 " pg_catalog.format_type(p.proargtypes[s.i], NULL)\n" 96 " FROM\n" 97 " pg_catalog.generate_series(0, pg_catalog.array_upper(p.proargtypes, 1)) AS s(i)\n" 98 " ), ', ')\n" 99 " END AS \"%s\",\n", 100 gettext_noop("Argument data types")); 101 else 102 appendPQExpBuffer(&buf, 103 " pg_catalog.format_type(p.proargtypes[0], NULL) AS \"%s\",\n", 104 gettext_noop("Argument data types")); 105 106 if (pset.sversion >= 110000) 107 appendPQExpBuffer(&buf, 108 " pg_catalog.obj_description(p.oid, 'pg_proc') as \"%s\"\n" 109 "FROM pg_catalog.pg_proc p\n" 110 " LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace\n" 111 "WHERE p.prokind = 'a'\n", 112 gettext_noop("Description")); 113 else 114 appendPQExpBuffer(&buf, 115 " pg_catalog.obj_description(p.oid, 'pg_proc') as \"%s\"\n" 116 "FROM pg_catalog.pg_proc p\n" 117 " LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace\n" 118 "WHERE p.proisagg\n", 119 gettext_noop("Description")); 120 121 if (!showSystem && !pattern) 122 appendPQExpBufferStr(&buf, " AND n.nspname <> 'pg_catalog'\n" 123 " AND n.nspname <> 'information_schema'\n"); 124 125 processSQLNamePattern(pset.db, &buf, pattern, true, false, 126 "n.nspname", "p.proname", NULL, 127 "pg_catalog.pg_function_is_visible(p.oid)"); 128 129 appendPQExpBufferStr(&buf, "ORDER BY 1, 2, 4;"); 130 131 res = PSQLexec(buf.data); 132 termPQExpBuffer(&buf); 133 if (!res) 134 return false; 135 136 myopt.nullPrint = NULL; 137 myopt.title = _("List of aggregate functions"); 138 myopt.translate_header = true; 139 140 printQuery(res, &myopt, pset.queryFout, false, pset.logfile); 141 142 PQclear(res); 143 return true; 144 } 145 146 /* 147 * \dA 148 * Takes an optional regexp to select particular access methods 149 */ 150 bool 151 describeAccessMethods(const char *pattern, bool verbose) 152 { 153 PQExpBufferData buf; 154 PGresult *res; 155 printQueryOpt myopt = pset.popt; 156 static const bool translate_columns[] = {false, true, false, false}; 157 158 if (pset.sversion < 90600) 159 { 160 char sverbuf[32]; 161 162 pg_log_error("The server (version %s) does not support access methods.", 163 formatPGVersionNumber(pset.sversion, false, 164 sverbuf, sizeof(sverbuf))); 165 return true; 166 } 167 168 initPQExpBuffer(&buf); 169 170 printfPQExpBuffer(&buf, 171 "SELECT amname AS \"%s\",\n" 172 " CASE amtype" 173 " WHEN 'i' THEN '%s'" 174 " WHEN 't' THEN '%s'" 175 " END AS \"%s\"", 176 gettext_noop("Name"), 177 gettext_noop("Index"), 178 gettext_noop("Table"), 179 gettext_noop("Type")); 180 181 if (verbose) 182 { 183 appendPQExpBuffer(&buf, 184 ",\n amhandler AS \"%s\",\n" 185 " pg_catalog.obj_description(oid, 'pg_am') AS \"%s\"", 186 gettext_noop("Handler"), 187 gettext_noop("Description")); 188 } 189 190 appendPQExpBufferStr(&buf, 191 "\nFROM pg_catalog.pg_am\n"); 192 193 processSQLNamePattern(pset.db, &buf, pattern, false, false, 194 NULL, "amname", NULL, 195 NULL); 196 197 appendPQExpBufferStr(&buf, "ORDER BY 1;"); 198 199 res = PSQLexec(buf.data); 200 termPQExpBuffer(&buf); 201 if (!res) 202 return false; 203 204 myopt.nullPrint = NULL; 205 myopt.title = _("List of access methods"); 206 myopt.translate_header = true; 207 myopt.translate_columns = translate_columns; 208 myopt.n_translate_columns = lengthof(translate_columns); 209 210 printQuery(res, &myopt, pset.queryFout, false, pset.logfile); 211 212 PQclear(res); 213 return true; 214 } 215 216 /* 217 * \db 218 * Takes an optional regexp to select particular tablespaces 219 */ 220 bool 221 describeTablespaces(const char *pattern, bool verbose) 222 { 223 PQExpBufferData buf; 224 PGresult *res; 225 printQueryOpt myopt = pset.popt; 226 227 if (pset.sversion < 80000) 228 { 229 char sverbuf[32]; 230 231 pg_log_info("The server (version %s) does not support tablespaces.", 232 formatPGVersionNumber(pset.sversion, false, 233 sverbuf, sizeof(sverbuf))); 234 return true; 235 } 236 237 initPQExpBuffer(&buf); 238 239 if (pset.sversion >= 90200) 240 printfPQExpBuffer(&buf, 241 "SELECT spcname AS \"%s\",\n" 242 " pg_catalog.pg_get_userbyid(spcowner) AS \"%s\",\n" 243 " pg_catalog.pg_tablespace_location(oid) AS \"%s\"", 244 gettext_noop("Name"), 245 gettext_noop("Owner"), 246 gettext_noop("Location")); 247 else 248 printfPQExpBuffer(&buf, 249 "SELECT spcname AS \"%s\",\n" 250 " pg_catalog.pg_get_userbyid(spcowner) AS \"%s\",\n" 251 " spclocation AS \"%s\"", 252 gettext_noop("Name"), 253 gettext_noop("Owner"), 254 gettext_noop("Location")); 255 256 if (verbose) 257 { 258 appendPQExpBufferStr(&buf, ",\n "); 259 printACLColumn(&buf, "spcacl"); 260 } 261 262 if (verbose && pset.sversion >= 90000) 263 appendPQExpBuffer(&buf, 264 ",\n spcoptions AS \"%s\"", 265 gettext_noop("Options")); 266 267 if (verbose && pset.sversion >= 90200) 268 appendPQExpBuffer(&buf, 269 ",\n pg_catalog.pg_size_pretty(pg_catalog.pg_tablespace_size(oid)) AS \"%s\"", 270 gettext_noop("Size")); 271 272 if (verbose && pset.sversion >= 80200) 273 appendPQExpBuffer(&buf, 274 ",\n pg_catalog.shobj_description(oid, 'pg_tablespace') AS \"%s\"", 275 gettext_noop("Description")); 276 277 appendPQExpBufferStr(&buf, 278 "\nFROM pg_catalog.pg_tablespace\n"); 279 280 processSQLNamePattern(pset.db, &buf, pattern, false, false, 281 NULL, "spcname", NULL, 282 NULL); 283 284 appendPQExpBufferStr(&buf, "ORDER BY 1;"); 285 286 res = PSQLexec(buf.data); 287 termPQExpBuffer(&buf); 288 if (!res) 289 return false; 290 291 myopt.nullPrint = NULL; 292 myopt.title = _("List of tablespaces"); 293 myopt.translate_header = true; 294 295 printQuery(res, &myopt, pset.queryFout, false, pset.logfile); 296 297 PQclear(res); 298 return true; 299 } 300 301 302 /* 303 * \df 304 * Takes an optional regexp to select particular functions. 305 * 306 * As with \d, you can specify the kinds of functions you want: 307 * 308 * a for aggregates 309 * n for normal 310 * t for trigger 311 * w for window 312 * 313 * and you can mix and match these in any order. 314 */ 315 bool 316 describeFunctions(const char *functypes, const char *pattern, bool verbose, bool showSystem) 317 { 318 bool showAggregate = strchr(functypes, 'a') != NULL; 319 bool showNormal = strchr(functypes, 'n') != NULL; 320 bool showProcedure = strchr(functypes, 'p') != NULL; 321 bool showTrigger = strchr(functypes, 't') != NULL; 322 bool showWindow = strchr(functypes, 'w') != NULL; 323 bool have_where; 324 PQExpBufferData buf; 325 PGresult *res; 326 printQueryOpt myopt = pset.popt; 327 static const bool translate_columns[] = {false, false, false, false, true, true, true, false, true, false, false, false, false}; 328 329 /* No "Parallel" column before 9.6 */ 330 static const bool translate_columns_pre_96[] = {false, false, false, false, true, true, false, true, false, false, false, false}; 331 332 if (strlen(functypes) != strspn(functypes, "anptwS+")) 333 { 334 pg_log_error("\\df only takes [anptwS+] as options"); 335 return true; 336 } 337 338 if (showProcedure && pset.sversion < 110000) 339 { 340 char sverbuf[32]; 341 342 pg_log_error("\\df does not take a \"%c\" option with server version %s", 343 'p', 344 formatPGVersionNumber(pset.sversion, false, 345 sverbuf, sizeof(sverbuf))); 346 return true; 347 } 348 349 if (showWindow && pset.sversion < 80400) 350 { 351 char sverbuf[32]; 352 353 pg_log_error("\\df does not take a \"%c\" option with server version %s", 354 'w', 355 formatPGVersionNumber(pset.sversion, false, 356 sverbuf, sizeof(sverbuf))); 357 return true; 358 } 359 360 if (!showAggregate && !showNormal && !showProcedure && !showTrigger && !showWindow) 361 { 362 showAggregate = showNormal = showTrigger = true; 363 if (pset.sversion >= 110000) 364 showProcedure = true; 365 if (pset.sversion >= 80400) 366 showWindow = true; 367 } 368 369 initPQExpBuffer(&buf); 370 371 printfPQExpBuffer(&buf, 372 "SELECT n.nspname as \"%s\",\n" 373 " p.proname as \"%s\",\n", 374 gettext_noop("Schema"), 375 gettext_noop("Name")); 376 377 if (pset.sversion >= 110000) 378 appendPQExpBuffer(&buf, 379 " pg_catalog.pg_get_function_result(p.oid) as \"%s\",\n" 380 " pg_catalog.pg_get_function_arguments(p.oid) as \"%s\",\n" 381 " CASE p.prokind\n" 382 " WHEN 'a' THEN '%s'\n" 383 " WHEN 'w' THEN '%s'\n" 384 " WHEN 'p' THEN '%s'\n" 385 " ELSE '%s'\n" 386 " END as \"%s\"", 387 gettext_noop("Result data type"), 388 gettext_noop("Argument data types"), 389 /* translator: "agg" is short for "aggregate" */ 390 gettext_noop("agg"), 391 gettext_noop("window"), 392 gettext_noop("proc"), 393 gettext_noop("func"), 394 gettext_noop("Type")); 395 else if (pset.sversion >= 80400) 396 appendPQExpBuffer(&buf, 397 " pg_catalog.pg_get_function_result(p.oid) as \"%s\",\n" 398 " pg_catalog.pg_get_function_arguments(p.oid) as \"%s\",\n" 399 " CASE\n" 400 " WHEN p.proisagg THEN '%s'\n" 401 " WHEN p.proiswindow THEN '%s'\n" 402 " WHEN p.prorettype = 'pg_catalog.trigger'::pg_catalog.regtype THEN '%s'\n" 403 " ELSE '%s'\n" 404 " END as \"%s\"", 405 gettext_noop("Result data type"), 406 gettext_noop("Argument data types"), 407 /* translator: "agg" is short for "aggregate" */ 408 gettext_noop("agg"), 409 gettext_noop("window"), 410 gettext_noop("trigger"), 411 gettext_noop("func"), 412 gettext_noop("Type")); 413 else if (pset.sversion >= 80100) 414 appendPQExpBuffer(&buf, 415 " CASE WHEN p.proretset THEN 'SETOF ' ELSE '' END ||\n" 416 " pg_catalog.format_type(p.prorettype, NULL) as \"%s\",\n" 417 " CASE WHEN proallargtypes IS NOT NULL THEN\n" 418 " pg_catalog.array_to_string(ARRAY(\n" 419 " SELECT\n" 420 " CASE\n" 421 " WHEN p.proargmodes[s.i] = 'i' THEN ''\n" 422 " WHEN p.proargmodes[s.i] = 'o' THEN 'OUT '\n" 423 " WHEN p.proargmodes[s.i] = 'b' THEN 'INOUT '\n" 424 " WHEN p.proargmodes[s.i] = 'v' THEN 'VARIADIC '\n" 425 " END ||\n" 426 " CASE\n" 427 " WHEN COALESCE(p.proargnames[s.i], '') = '' THEN ''\n" 428 " ELSE p.proargnames[s.i] || ' '\n" 429 " END ||\n" 430 " pg_catalog.format_type(p.proallargtypes[s.i], NULL)\n" 431 " FROM\n" 432 " pg_catalog.generate_series(1, pg_catalog.array_upper(p.proallargtypes, 1)) AS s(i)\n" 433 " ), ', ')\n" 434 " ELSE\n" 435 " pg_catalog.array_to_string(ARRAY(\n" 436 " SELECT\n" 437 " CASE\n" 438 " WHEN COALESCE(p.proargnames[s.i+1], '') = '' THEN ''\n" 439 " ELSE p.proargnames[s.i+1] || ' '\n" 440 " END ||\n" 441 " pg_catalog.format_type(p.proargtypes[s.i], NULL)\n" 442 " FROM\n" 443 " pg_catalog.generate_series(0, pg_catalog.array_upper(p.proargtypes, 1)) AS s(i)\n" 444 " ), ', ')\n" 445 " END AS \"%s\",\n" 446 " CASE\n" 447 " WHEN p.proisagg THEN '%s'\n" 448 " WHEN p.prorettype = 'pg_catalog.trigger'::pg_catalog.regtype THEN '%s'\n" 449 " ELSE '%s'\n" 450 " END AS \"%s\"", 451 gettext_noop("Result data type"), 452 gettext_noop("Argument data types"), 453 /* translator: "agg" is short for "aggregate" */ 454 gettext_noop("agg"), 455 gettext_noop("trigger"), 456 gettext_noop("func"), 457 gettext_noop("Type")); 458 else 459 appendPQExpBuffer(&buf, 460 " CASE WHEN p.proretset THEN 'SETOF ' ELSE '' END ||\n" 461 " pg_catalog.format_type(p.prorettype, NULL) as \"%s\",\n" 462 " pg_catalog.oidvectortypes(p.proargtypes) as \"%s\",\n" 463 " CASE\n" 464 " WHEN p.proisagg THEN '%s'\n" 465 " WHEN p.prorettype = 'pg_catalog.trigger'::pg_catalog.regtype THEN '%s'\n" 466 " ELSE '%s'\n" 467 " END AS \"%s\"", 468 gettext_noop("Result data type"), 469 gettext_noop("Argument data types"), 470 /* translator: "agg" is short for "aggregate" */ 471 gettext_noop("agg"), 472 gettext_noop("trigger"), 473 gettext_noop("func"), 474 gettext_noop("Type")); 475 476 if (verbose) 477 { 478 appendPQExpBuffer(&buf, 479 ",\n CASE\n" 480 " WHEN p.provolatile = 'i' THEN '%s'\n" 481 " WHEN p.provolatile = 's' THEN '%s'\n" 482 " WHEN p.provolatile = 'v' THEN '%s'\n" 483 " END as \"%s\"", 484 gettext_noop("immutable"), 485 gettext_noop("stable"), 486 gettext_noop("volatile"), 487 gettext_noop("Volatility")); 488 if (pset.sversion >= 90600) 489 appendPQExpBuffer(&buf, 490 ",\n CASE\n" 491 " WHEN p.proparallel = 'r' THEN '%s'\n" 492 " WHEN p.proparallel = 's' THEN '%s'\n" 493 " WHEN p.proparallel = 'u' THEN '%s'\n" 494 " END as \"%s\"", 495 gettext_noop("restricted"), 496 gettext_noop("safe"), 497 gettext_noop("unsafe"), 498 gettext_noop("Parallel")); 499 appendPQExpBuffer(&buf, 500 ",\n pg_catalog.pg_get_userbyid(p.proowner) as \"%s\"" 501 ",\n CASE WHEN prosecdef THEN '%s' ELSE '%s' END AS \"%s\"", 502 gettext_noop("Owner"), 503 gettext_noop("definer"), 504 gettext_noop("invoker"), 505 gettext_noop("Security")); 506 appendPQExpBufferStr(&buf, ",\n "); 507 printACLColumn(&buf, "p.proacl"); 508 appendPQExpBuffer(&buf, 509 ",\n l.lanname as \"%s\"" 510 ",\n p.prosrc as \"%s\"" 511 ",\n pg_catalog.obj_description(p.oid, 'pg_proc') as \"%s\"", 512 gettext_noop("Language"), 513 gettext_noop("Source code"), 514 gettext_noop("Description")); 515 } 516 517 appendPQExpBufferStr(&buf, 518 "\nFROM pg_catalog.pg_proc p" 519 "\n LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace\n"); 520 521 if (verbose) 522 appendPQExpBufferStr(&buf, 523 " LEFT JOIN pg_catalog.pg_language l ON l.oid = p.prolang\n"); 524 525 have_where = false; 526 527 /* filter by function type, if requested */ 528 if (showNormal && showAggregate && showProcedure && showTrigger && showWindow) 529 /* Do nothing */ ; 530 else if (showNormal) 531 { 532 if (!showAggregate) 533 { 534 if (have_where) 535 appendPQExpBufferStr(&buf, " AND "); 536 else 537 { 538 appendPQExpBufferStr(&buf, "WHERE "); 539 have_where = true; 540 } 541 if (pset.sversion >= 110000) 542 appendPQExpBufferStr(&buf, "p.prokind <> 'a'\n"); 543 else 544 appendPQExpBufferStr(&buf, "NOT p.proisagg\n"); 545 } 546 if (!showProcedure && pset.sversion >= 110000) 547 { 548 if (have_where) 549 appendPQExpBufferStr(&buf, " AND "); 550 else 551 { 552 appendPQExpBufferStr(&buf, "WHERE "); 553 have_where = true; 554 } 555 appendPQExpBufferStr(&buf, "p.prokind <> 'p'\n"); 556 } 557 if (!showTrigger) 558 { 559 if (have_where) 560 appendPQExpBufferStr(&buf, " AND "); 561 else 562 { 563 appendPQExpBufferStr(&buf, "WHERE "); 564 have_where = true; 565 } 566 appendPQExpBufferStr(&buf, "p.prorettype <> 'pg_catalog.trigger'::pg_catalog.regtype\n"); 567 } 568 if (!showWindow && pset.sversion >= 80400) 569 { 570 if (have_where) 571 appendPQExpBufferStr(&buf, " AND "); 572 else 573 { 574 appendPQExpBufferStr(&buf, "WHERE "); 575 have_where = true; 576 } 577 if (pset.sversion >= 110000) 578 appendPQExpBufferStr(&buf, "p.prokind <> 'w'\n"); 579 else 580 appendPQExpBufferStr(&buf, "NOT p.proiswindow\n"); 581 } 582 } 583 else 584 { 585 bool needs_or = false; 586 587 appendPQExpBufferStr(&buf, "WHERE (\n "); 588 have_where = true; 589 /* Note: at least one of these must be true ... */ 590 if (showAggregate) 591 { 592 if (pset.sversion >= 110000) 593 appendPQExpBufferStr(&buf, "p.prokind = 'a'\n"); 594 else 595 appendPQExpBufferStr(&buf, "p.proisagg\n"); 596 needs_or = true; 597 } 598 if (showTrigger) 599 { 600 if (needs_or) 601 appendPQExpBufferStr(&buf, " OR "); 602 appendPQExpBufferStr(&buf, 603 "p.prorettype = 'pg_catalog.trigger'::pg_catalog.regtype\n"); 604 needs_or = true; 605 } 606 if (showProcedure) 607 { 608 if (needs_or) 609 appendPQExpBufferStr(&buf, " OR "); 610 appendPQExpBufferStr(&buf, "p.prokind = 'p'\n"); 611 needs_or = true; 612 } 613 if (showWindow) 614 { 615 if (needs_or) 616 appendPQExpBufferStr(&buf, " OR "); 617 if (pset.sversion >= 110000) 618 appendPQExpBufferStr(&buf, "p.prokind = 'w'\n"); 619 else 620 appendPQExpBufferStr(&buf, "p.proiswindow\n"); 621 needs_or = true; 622 } 623 appendPQExpBufferStr(&buf, " )\n"); 624 } 625 626 processSQLNamePattern(pset.db, &buf, pattern, have_where, false, 627 "n.nspname", "p.proname", NULL, 628 "pg_catalog.pg_function_is_visible(p.oid)"); 629 630 if (!showSystem && !pattern) 631 appendPQExpBufferStr(&buf, " AND n.nspname <> 'pg_catalog'\n" 632 " AND n.nspname <> 'information_schema'\n"); 633 634 appendPQExpBufferStr(&buf, "ORDER BY 1, 2, 4;"); 635 636 res = PSQLexec(buf.data); 637 termPQExpBuffer(&buf); 638 if (!res) 639 return false; 640 641 myopt.nullPrint = NULL; 642 myopt.title = _("List of functions"); 643 myopt.translate_header = true; 644 if (pset.sversion >= 90600) 645 { 646 myopt.translate_columns = translate_columns; 647 myopt.n_translate_columns = lengthof(translate_columns); 648 } 649 else 650 { 651 myopt.translate_columns = translate_columns_pre_96; 652 myopt.n_translate_columns = lengthof(translate_columns_pre_96); 653 } 654 655 printQuery(res, &myopt, pset.queryFout, false, pset.logfile); 656 657 PQclear(res); 658 return true; 659 } 660 661 662 663 /* 664 * \dT 665 * describe types 666 */ 667 bool 668 describeTypes(const char *pattern, bool verbose, bool showSystem) 669 { 670 PQExpBufferData buf; 671 PGresult *res; 672 printQueryOpt myopt = pset.popt; 673 674 initPQExpBuffer(&buf); 675 676 printfPQExpBuffer(&buf, 677 "SELECT n.nspname as \"%s\",\n" 678 " pg_catalog.format_type(t.oid, NULL) AS \"%s\",\n", 679 gettext_noop("Schema"), 680 gettext_noop("Name")); 681 if (verbose) 682 appendPQExpBuffer(&buf, 683 " t.typname AS \"%s\",\n" 684 " CASE WHEN t.typrelid != 0\n" 685 " THEN CAST('tuple' AS pg_catalog.text)\n" 686 " WHEN t.typlen < 0\n" 687 " THEN CAST('var' AS pg_catalog.text)\n" 688 " ELSE CAST(t.typlen AS pg_catalog.text)\n" 689 " END AS \"%s\",\n", 690 gettext_noop("Internal name"), 691 gettext_noop("Size")); 692 if (verbose && pset.sversion >= 80300) 693 { 694 appendPQExpBufferStr(&buf, 695 " pg_catalog.array_to_string(\n" 696 " ARRAY(\n" 697 " SELECT e.enumlabel\n" 698 " FROM pg_catalog.pg_enum e\n" 699 " WHERE e.enumtypid = t.oid\n"); 700 701 if (pset.sversion >= 90100) 702 appendPQExpBufferStr(&buf, 703 " ORDER BY e.enumsortorder\n"); 704 else 705 appendPQExpBufferStr(&buf, 706 " ORDER BY e.oid\n"); 707 708 appendPQExpBuffer(&buf, 709 " ),\n" 710 " E'\\n'\n" 711 " ) AS \"%s\",\n", 712 gettext_noop("Elements")); 713 } 714 if (verbose) 715 { 716 appendPQExpBuffer(&buf, 717 " pg_catalog.pg_get_userbyid(t.typowner) AS \"%s\",\n", 718 gettext_noop("Owner")); 719 } 720 if (verbose && pset.sversion >= 90200) 721 { 722 printACLColumn(&buf, "t.typacl"); 723 appendPQExpBufferStr(&buf, ",\n "); 724 } 725 726 appendPQExpBuffer(&buf, 727 " pg_catalog.obj_description(t.oid, 'pg_type') as \"%s\"\n", 728 gettext_noop("Description")); 729 730 appendPQExpBufferStr(&buf, "FROM pg_catalog.pg_type t\n" 731 " LEFT JOIN pg_catalog.pg_namespace n ON n.oid = t.typnamespace\n"); 732 733 /* 734 * do not include complex types (typrelid!=0) unless they are standalone 735 * composite types 736 */ 737 appendPQExpBufferStr(&buf, "WHERE (t.typrelid = 0 "); 738 appendPQExpBufferStr(&buf, "OR (SELECT c.relkind = " CppAsString2(RELKIND_COMPOSITE_TYPE) 739 " FROM pg_catalog.pg_class c " 740 "WHERE c.oid = t.typrelid))\n"); 741 742 /* 743 * do not include array types (before 8.3 we have to use the assumption 744 * that their names start with underscore) 745 */ 746 if (pset.sversion >= 80300) 747 appendPQExpBufferStr(&buf, " AND NOT EXISTS(SELECT 1 FROM pg_catalog.pg_type el WHERE el.oid = t.typelem AND el.typarray = t.oid)\n"); 748 else 749 appendPQExpBufferStr(&buf, " AND t.typname !~ '^_'\n"); 750 751 if (!showSystem && !pattern) 752 appendPQExpBufferStr(&buf, " AND n.nspname <> 'pg_catalog'\n" 753 " AND n.nspname <> 'information_schema'\n"); 754 755 /* Match name pattern against either internal or external name */ 756 processSQLNamePattern(pset.db, &buf, pattern, true, false, 757 "n.nspname", "t.typname", 758 "pg_catalog.format_type(t.oid, NULL)", 759 "pg_catalog.pg_type_is_visible(t.oid)"); 760 761 appendPQExpBufferStr(&buf, "ORDER BY 1, 2;"); 762 763 res = PSQLexec(buf.data); 764 termPQExpBuffer(&buf); 765 if (!res) 766 return false; 767 768 myopt.nullPrint = NULL; 769 myopt.title = _("List of data types"); 770 myopt.translate_header = true; 771 772 printQuery(res, &myopt, pset.queryFout, false, pset.logfile); 773 774 PQclear(res); 775 return true; 776 } 777 778 779 /* 780 * \do 781 * Describe operators 782 */ 783 bool 784 describeOperators(const char *pattern, bool verbose, bool showSystem) 785 { 786 PQExpBufferData buf; 787 PGresult *res; 788 printQueryOpt myopt = pset.popt; 789 790 initPQExpBuffer(&buf); 791 792 /* 793 * Note: before Postgres 9.1, we did not assign comments to any built-in 794 * operators, preferring to let the comment on the underlying function 795 * suffice. The coalesce() on the obj_description() calls below supports 796 * this convention by providing a fallback lookup of a comment on the 797 * operator's function. As of 9.1 there is a policy that every built-in 798 * operator should have a comment; so the coalesce() is no longer 799 * necessary so far as built-in operators are concerned. We keep it 800 * anyway, for now, because (1) third-party modules may still be following 801 * the old convention, and (2) we'd need to do it anyway when talking to a 802 * pre-9.1 server. 803 */ 804 805 printfPQExpBuffer(&buf, 806 "SELECT n.nspname as \"%s\",\n" 807 " o.oprname AS \"%s\",\n" 808 " CASE WHEN o.oprkind='l' THEN NULL ELSE pg_catalog.format_type(o.oprleft, NULL) END AS \"%s\",\n" 809 " CASE WHEN o.oprkind='r' THEN NULL ELSE pg_catalog.format_type(o.oprright, NULL) END AS \"%s\",\n" 810 " pg_catalog.format_type(o.oprresult, NULL) AS \"%s\",\n", 811 gettext_noop("Schema"), 812 gettext_noop("Name"), 813 gettext_noop("Left arg type"), 814 gettext_noop("Right arg type"), 815 gettext_noop("Result type")); 816 817 if (verbose) 818 appendPQExpBuffer(&buf, 819 " o.oprcode AS \"%s\",\n", 820 gettext_noop("Function")); 821 822 appendPQExpBuffer(&buf, 823 " coalesce(pg_catalog.obj_description(o.oid, 'pg_operator'),\n" 824 " pg_catalog.obj_description(o.oprcode, 'pg_proc')) AS \"%s\"\n" 825 "FROM pg_catalog.pg_operator o\n" 826 " LEFT JOIN pg_catalog.pg_namespace n ON n.oid = o.oprnamespace\n", 827 gettext_noop("Description")); 828 829 if (!showSystem && !pattern) 830 appendPQExpBufferStr(&buf, "WHERE n.nspname <> 'pg_catalog'\n" 831 " AND n.nspname <> 'information_schema'\n"); 832 833 processSQLNamePattern(pset.db, &buf, pattern, !showSystem && !pattern, true, 834 "n.nspname", "o.oprname", NULL, 835 "pg_catalog.pg_operator_is_visible(o.oid)"); 836 837 appendPQExpBufferStr(&buf, "ORDER BY 1, 2, 3, 4;"); 838 839 res = PSQLexec(buf.data); 840 termPQExpBuffer(&buf); 841 if (!res) 842 return false; 843 844 myopt.nullPrint = NULL; 845 myopt.title = _("List of operators"); 846 myopt.translate_header = true; 847 848 printQuery(res, &myopt, pset.queryFout, false, pset.logfile); 849 850 PQclear(res); 851 return true; 852 } 853 854 855 /* 856 * listAllDbs 857 * 858 * for \l, \list, and -l switch 859 */ 860 bool 861 listAllDbs(const char *pattern, bool verbose) 862 { 863 PGresult *res; 864 PQExpBufferData buf; 865 printQueryOpt myopt = pset.popt; 866 867 initPQExpBuffer(&buf); 868 869 printfPQExpBuffer(&buf, 870 "SELECT d.datname as \"%s\",\n" 871 " pg_catalog.pg_get_userbyid(d.datdba) as \"%s\",\n" 872 " pg_catalog.pg_encoding_to_char(d.encoding) as \"%s\",\n", 873 gettext_noop("Name"), 874 gettext_noop("Owner"), 875 gettext_noop("Encoding")); 876 if (pset.sversion >= 80400) 877 appendPQExpBuffer(&buf, 878 " d.datcollate as \"%s\",\n" 879 " d.datctype as \"%s\",\n", 880 gettext_noop("Collate"), 881 gettext_noop("Ctype")); 882 appendPQExpBufferStr(&buf, " "); 883 printACLColumn(&buf, "d.datacl"); 884 if (verbose && pset.sversion >= 80200) 885 appendPQExpBuffer(&buf, 886 ",\n CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT')\n" 887 " THEN pg_catalog.pg_size_pretty(pg_catalog.pg_database_size(d.datname))\n" 888 " ELSE 'No Access'\n" 889 " END as \"%s\"", 890 gettext_noop("Size")); 891 if (verbose && pset.sversion >= 80000) 892 appendPQExpBuffer(&buf, 893 ",\n t.spcname as \"%s\"", 894 gettext_noop("Tablespace")); 895 if (verbose && pset.sversion >= 80200) 896 appendPQExpBuffer(&buf, 897 ",\n pg_catalog.shobj_description(d.oid, 'pg_database') as \"%s\"", 898 gettext_noop("Description")); 899 appendPQExpBufferStr(&buf, 900 "\nFROM pg_catalog.pg_database d\n"); 901 if (verbose && pset.sversion >= 80000) 902 appendPQExpBufferStr(&buf, 903 " JOIN pg_catalog.pg_tablespace t on d.dattablespace = t.oid\n"); 904 905 if (pattern) 906 processSQLNamePattern(pset.db, &buf, pattern, false, false, 907 NULL, "d.datname", NULL, NULL); 908 909 appendPQExpBufferStr(&buf, "ORDER BY 1;"); 910 res = PSQLexec(buf.data); 911 termPQExpBuffer(&buf); 912 if (!res) 913 return false; 914 915 myopt.nullPrint = NULL; 916 myopt.title = _("List of databases"); 917 myopt.translate_header = true; 918 919 printQuery(res, &myopt, pset.queryFout, false, pset.logfile); 920 921 PQclear(res); 922 return true; 923 } 924 925 926 /* 927 * List Tables' Grant/Revoke Permissions 928 * \z (now also \dp -- perhaps more mnemonic) 929 */ 930 bool 931 permissionsList(const char *pattern) 932 { 933 PQExpBufferData buf; 934 PGresult *res; 935 printQueryOpt myopt = pset.popt; 936 static const bool translate_columns[] = {false, false, true, false, false, false}; 937 938 initPQExpBuffer(&buf); 939 940 /* 941 * we ignore indexes and toast tables since they have no meaningful rights 942 */ 943 printfPQExpBuffer(&buf, 944 "SELECT n.nspname as \"%s\",\n" 945 " c.relname as \"%s\",\n" 946 " CASE c.relkind" 947 " WHEN " CppAsString2(RELKIND_RELATION) " THEN '%s'" 948 " WHEN " CppAsString2(RELKIND_VIEW) " THEN '%s'" 949 " WHEN " CppAsString2(RELKIND_MATVIEW) " THEN '%s'" 950 " WHEN " CppAsString2(RELKIND_SEQUENCE) " THEN '%s'" 951 " WHEN " CppAsString2(RELKIND_FOREIGN_TABLE) " THEN '%s'" 952 " WHEN " CppAsString2(RELKIND_PARTITIONED_TABLE) " THEN '%s'" 953 " END as \"%s\",\n" 954 " ", 955 gettext_noop("Schema"), 956 gettext_noop("Name"), 957 gettext_noop("table"), 958 gettext_noop("view"), 959 gettext_noop("materialized view"), 960 gettext_noop("sequence"), 961 gettext_noop("foreign table"), 962 gettext_noop("partitioned table"), 963 gettext_noop("Type")); 964 965 printACLColumn(&buf, "c.relacl"); 966 967 if (pset.sversion >= 80400) 968 appendPQExpBuffer(&buf, 969 ",\n pg_catalog.array_to_string(ARRAY(\n" 970 " SELECT attname || E':\\n ' || pg_catalog.array_to_string(attacl, E'\\n ')\n" 971 " FROM pg_catalog.pg_attribute a\n" 972 " WHERE attrelid = c.oid AND NOT attisdropped AND attacl IS NOT NULL\n" 973 " ), E'\\n') AS \"%s\"", 974 gettext_noop("Column privileges")); 975 976 if (pset.sversion >= 90500 && pset.sversion < 100000) 977 appendPQExpBuffer(&buf, 978 ",\n pg_catalog.array_to_string(ARRAY(\n" 979 " SELECT polname\n" 980 " || CASE WHEN polcmd != '*' THEN\n" 981 " E' (' || polcmd || E'):'\n" 982 " ELSE E':'\n" 983 " END\n" 984 " || CASE WHEN polqual IS NOT NULL THEN\n" 985 " E'\\n (u): ' || pg_catalog.pg_get_expr(polqual, polrelid)\n" 986 " ELSE E''\n" 987 " END\n" 988 " || CASE WHEN polwithcheck IS NOT NULL THEN\n" 989 " E'\\n (c): ' || pg_catalog.pg_get_expr(polwithcheck, polrelid)\n" 990 " ELSE E''\n" 991 " END" 992 " || CASE WHEN polroles <> '{0}' THEN\n" 993 " E'\\n to: ' || pg_catalog.array_to_string(\n" 994 " ARRAY(\n" 995 " SELECT rolname\n" 996 " FROM pg_catalog.pg_roles\n" 997 " WHERE oid = ANY (polroles)\n" 998 " ORDER BY 1\n" 999 " ), E', ')\n" 1000 " ELSE E''\n" 1001 " END\n" 1002 " FROM pg_catalog.pg_policy pol\n" 1003 " WHERE polrelid = c.oid), E'\\n')\n" 1004 " AS \"%s\"", 1005 gettext_noop("Policies")); 1006 1007 if (pset.sversion >= 100000) 1008 appendPQExpBuffer(&buf, 1009 ",\n pg_catalog.array_to_string(ARRAY(\n" 1010 " SELECT polname\n" 1011 " || CASE WHEN NOT polpermissive THEN\n" 1012 " E' (RESTRICTIVE)'\n" 1013 " ELSE '' END\n" 1014 " || CASE WHEN polcmd != '*' THEN\n" 1015 " E' (' || polcmd || E'):'\n" 1016 " ELSE E':'\n" 1017 " END\n" 1018 " || CASE WHEN polqual IS NOT NULL THEN\n" 1019 " E'\\n (u): ' || pg_catalog.pg_get_expr(polqual, polrelid)\n" 1020 " ELSE E''\n" 1021 " END\n" 1022 " || CASE WHEN polwithcheck IS NOT NULL THEN\n" 1023 " E'\\n (c): ' || pg_catalog.pg_get_expr(polwithcheck, polrelid)\n" 1024 " ELSE E''\n" 1025 " END" 1026 " || CASE WHEN polroles <> '{0}' THEN\n" 1027 " E'\\n to: ' || pg_catalog.array_to_string(\n" 1028 " ARRAY(\n" 1029 " SELECT rolname\n" 1030 " FROM pg_catalog.pg_roles\n" 1031 " WHERE oid = ANY (polroles)\n" 1032 " ORDER BY 1\n" 1033 " ), E', ')\n" 1034 " ELSE E''\n" 1035 " END\n" 1036 " FROM pg_catalog.pg_policy pol\n" 1037 " WHERE polrelid = c.oid), E'\\n')\n" 1038 " AS \"%s\"", 1039 gettext_noop("Policies")); 1040 1041 appendPQExpBufferStr(&buf, "\nFROM pg_catalog.pg_class c\n" 1042 " LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace\n" 1043 "WHERE c.relkind IN (" 1044 CppAsString2(RELKIND_RELATION) "," 1045 CppAsString2(RELKIND_VIEW) "," 1046 CppAsString2(RELKIND_MATVIEW) "," 1047 CppAsString2(RELKIND_SEQUENCE) "," 1048 CppAsString2(RELKIND_FOREIGN_TABLE) "," 1049 CppAsString2(RELKIND_PARTITIONED_TABLE) ")\n"); 1050 1051 /* 1052 * Unless a schema pattern is specified, we suppress system and temp 1053 * tables, since they normally aren't very interesting from a permissions 1054 * point of view. You can see 'em by explicit request though, eg with \z 1055 * pg_catalog.* 1056 */ 1057 processSQLNamePattern(pset.db, &buf, pattern, true, false, 1058 "n.nspname", "c.relname", NULL, 1059 "n.nspname !~ '^pg_' AND pg_catalog.pg_table_is_visible(c.oid)"); 1060 1061 appendPQExpBufferStr(&buf, "ORDER BY 1, 2;"); 1062 1063 res = PSQLexec(buf.data); 1064 if (!res) 1065 { 1066 termPQExpBuffer(&buf); 1067 return false; 1068 } 1069 1070 myopt.nullPrint = NULL; 1071 printfPQExpBuffer(&buf, _("Access privileges")); 1072 myopt.title = buf.data; 1073 myopt.translate_header = true; 1074 myopt.translate_columns = translate_columns; 1075 myopt.n_translate_columns = lengthof(translate_columns); 1076 1077 printQuery(res, &myopt, pset.queryFout, false, pset.logfile); 1078 1079 termPQExpBuffer(&buf); 1080 PQclear(res); 1081 return true; 1082 } 1083 1084 1085 /* 1086 * \ddp 1087 * 1088 * List Default ACLs. The pattern can match either schema or role name. 1089 */ 1090 bool 1091 listDefaultACLs(const char *pattern) 1092 { 1093 PQExpBufferData buf; 1094 PGresult *res; 1095 printQueryOpt myopt = pset.popt; 1096 static const bool translate_columns[] = {false, false, true, false}; 1097 1098 if (pset.sversion < 90000) 1099 { 1100 char sverbuf[32]; 1101 1102 pg_log_error("The server (version %s) does not support altering default privileges.", 1103 formatPGVersionNumber(pset.sversion, false, 1104 sverbuf, sizeof(sverbuf))); 1105 return true; 1106 } 1107 1108 initPQExpBuffer(&buf); 1109 1110 printfPQExpBuffer(&buf, 1111 "SELECT pg_catalog.pg_get_userbyid(d.defaclrole) AS \"%s\",\n" 1112 " n.nspname AS \"%s\",\n" 1113 " CASE d.defaclobjtype WHEN '%c' THEN '%s' WHEN '%c' THEN '%s' WHEN '%c' THEN '%s' WHEN '%c' THEN '%s' WHEN '%c' THEN '%s' END AS \"%s\",\n" 1114 " ", 1115 gettext_noop("Owner"), 1116 gettext_noop("Schema"), 1117 DEFACLOBJ_RELATION, 1118 gettext_noop("table"), 1119 DEFACLOBJ_SEQUENCE, 1120 gettext_noop("sequence"), 1121 DEFACLOBJ_FUNCTION, 1122 gettext_noop("function"), 1123 DEFACLOBJ_TYPE, 1124 gettext_noop("type"), 1125 DEFACLOBJ_NAMESPACE, 1126 gettext_noop("schema"), 1127 gettext_noop("Type")); 1128 1129 printACLColumn(&buf, "d.defaclacl"); 1130 1131 appendPQExpBufferStr(&buf, "\nFROM pg_catalog.pg_default_acl d\n" 1132 " LEFT JOIN pg_catalog.pg_namespace n ON n.oid = d.defaclnamespace\n"); 1133 1134 processSQLNamePattern(pset.db, &buf, pattern, false, false, 1135 NULL, 1136 "n.nspname", 1137 "pg_catalog.pg_get_userbyid(d.defaclrole)", 1138 NULL); 1139 1140 appendPQExpBufferStr(&buf, "ORDER BY 1, 2, 3;"); 1141 1142 res = PSQLexec(buf.data); 1143 if (!res) 1144 { 1145 termPQExpBuffer(&buf); 1146 return false; 1147 } 1148 1149 myopt.nullPrint = NULL; 1150 printfPQExpBuffer(&buf, _("Default access privileges")); 1151 myopt.title = buf.data; 1152 myopt.translate_header = true; 1153 myopt.translate_columns = translate_columns; 1154 myopt.n_translate_columns = lengthof(translate_columns); 1155 1156 printQuery(res, &myopt, pset.queryFout, false, pset.logfile); 1157 1158 termPQExpBuffer(&buf); 1159 PQclear(res); 1160 return true; 1161 } 1162 1163 1164 /* 1165 * Get object comments 1166 * 1167 * \dd [foo] 1168 * 1169 * Note: This command only lists comments for object types which do not have 1170 * their comments displayed by their own backslash commands. The following 1171 * types of objects will be displayed: constraint, operator class, 1172 * operator family, rule, and trigger. 1173 * 1174 */ 1175 bool 1176 objectDescription(const char *pattern, bool showSystem) 1177 { 1178 PQExpBufferData buf; 1179 PGresult *res; 1180 printQueryOpt myopt = pset.popt; 1181 static const bool translate_columns[] = {false, false, true, false}; 1182 1183 initPQExpBuffer(&buf); 1184 1185 appendPQExpBuffer(&buf, 1186 "SELECT DISTINCT tt.nspname AS \"%s\", tt.name AS \"%s\", tt.object AS \"%s\", d.description AS \"%s\"\n" 1187 "FROM (\n", 1188 gettext_noop("Schema"), 1189 gettext_noop("Name"), 1190 gettext_noop("Object"), 1191 gettext_noop("Description")); 1192 1193 /* Table constraint descriptions */ 1194 appendPQExpBuffer(&buf, 1195 " SELECT pgc.oid as oid, pgc.tableoid AS tableoid,\n" 1196 " n.nspname as nspname,\n" 1197 " CAST(pgc.conname AS pg_catalog.text) as name," 1198 " CAST('%s' AS pg_catalog.text) as object\n" 1199 " FROM pg_catalog.pg_constraint pgc\n" 1200 " JOIN pg_catalog.pg_class c " 1201 "ON c.oid = pgc.conrelid\n" 1202 " LEFT JOIN pg_catalog.pg_namespace n " 1203 " ON n.oid = c.relnamespace\n", 1204 gettext_noop("table constraint")); 1205 1206 if (!showSystem && !pattern) 1207 appendPQExpBufferStr(&buf, "WHERE n.nspname <> 'pg_catalog'\n" 1208 " AND n.nspname <> 'information_schema'\n"); 1209 1210 processSQLNamePattern(pset.db, &buf, pattern, !showSystem && !pattern, 1211 false, "n.nspname", "pgc.conname", NULL, 1212 "pg_catalog.pg_table_is_visible(c.oid)"); 1213 1214 /* Domain constraint descriptions */ 1215 appendPQExpBuffer(&buf, 1216 "UNION ALL\n" 1217 " SELECT pgc.oid as oid, pgc.tableoid AS tableoid,\n" 1218 " n.nspname as nspname,\n" 1219 " CAST(pgc.conname AS pg_catalog.text) as name," 1220 " CAST('%s' AS pg_catalog.text) as object\n" 1221 " FROM pg_catalog.pg_constraint pgc\n" 1222 " JOIN pg_catalog.pg_type t " 1223 "ON t.oid = pgc.contypid\n" 1224 " LEFT JOIN pg_catalog.pg_namespace n " 1225 " ON n.oid = t.typnamespace\n", 1226 gettext_noop("domain constraint")); 1227 1228 if (!showSystem && !pattern) 1229 appendPQExpBufferStr(&buf, "WHERE n.nspname <> 'pg_catalog'\n" 1230 " AND n.nspname <> 'information_schema'\n"); 1231 1232 processSQLNamePattern(pset.db, &buf, pattern, !showSystem && !pattern, 1233 false, "n.nspname", "pgc.conname", NULL, 1234 "pg_catalog.pg_type_is_visible(t.oid)"); 1235 1236 1237 /* 1238 * pg_opclass.opcmethod only available in 8.3+ 1239 */ 1240 if (pset.sversion >= 80300) 1241 { 1242 /* Operator class descriptions */ 1243 appendPQExpBuffer(&buf, 1244 "UNION ALL\n" 1245 " SELECT o.oid as oid, o.tableoid as tableoid,\n" 1246 " n.nspname as nspname,\n" 1247 " CAST(o.opcname AS pg_catalog.text) as name,\n" 1248 " CAST('%s' AS pg_catalog.text) as object\n" 1249 " FROM pg_catalog.pg_opclass o\n" 1250 " JOIN pg_catalog.pg_am am ON " 1251 "o.opcmethod = am.oid\n" 1252 " JOIN pg_catalog.pg_namespace n ON " 1253 "n.oid = o.opcnamespace\n", 1254 gettext_noop("operator class")); 1255 1256 if (!showSystem && !pattern) 1257 appendPQExpBufferStr(&buf, " AND n.nspname <> 'pg_catalog'\n" 1258 " AND n.nspname <> 'information_schema'\n"); 1259 1260 processSQLNamePattern(pset.db, &buf, pattern, true, false, 1261 "n.nspname", "o.opcname", NULL, 1262 "pg_catalog.pg_opclass_is_visible(o.oid)"); 1263 } 1264 1265 /* 1266 * although operator family comments have been around since 8.3, 1267 * pg_opfamily_is_visible is only available in 9.2+ 1268 */ 1269 if (pset.sversion >= 90200) 1270 { 1271 /* Operator family descriptions */ 1272 appendPQExpBuffer(&buf, 1273 "UNION ALL\n" 1274 " SELECT opf.oid as oid, opf.tableoid as tableoid,\n" 1275 " n.nspname as nspname,\n" 1276 " CAST(opf.opfname AS pg_catalog.text) AS name,\n" 1277 " CAST('%s' AS pg_catalog.text) as object\n" 1278 " FROM pg_catalog.pg_opfamily opf\n" 1279 " JOIN pg_catalog.pg_am am " 1280 "ON opf.opfmethod = am.oid\n" 1281 " JOIN pg_catalog.pg_namespace n " 1282 "ON opf.opfnamespace = n.oid\n", 1283 gettext_noop("operator family")); 1284 1285 if (!showSystem && !pattern) 1286 appendPQExpBufferStr(&buf, " AND n.nspname <> 'pg_catalog'\n" 1287 " AND n.nspname <> 'information_schema'\n"); 1288 1289 processSQLNamePattern(pset.db, &buf, pattern, true, false, 1290 "n.nspname", "opf.opfname", NULL, 1291 "pg_catalog.pg_opfamily_is_visible(opf.oid)"); 1292 } 1293 1294 /* Rule descriptions (ignore rules for views) */ 1295 appendPQExpBuffer(&buf, 1296 "UNION ALL\n" 1297 " SELECT r.oid as oid, r.tableoid as tableoid,\n" 1298 " n.nspname as nspname,\n" 1299 " CAST(r.rulename AS pg_catalog.text) as name," 1300 " CAST('%s' AS pg_catalog.text) as object\n" 1301 " FROM pg_catalog.pg_rewrite r\n" 1302 " JOIN pg_catalog.pg_class c ON c.oid = r.ev_class\n" 1303 " LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace\n" 1304 " WHERE r.rulename != '_RETURN'\n", 1305 gettext_noop("rule")); 1306 1307 if (!showSystem && !pattern) 1308 appendPQExpBufferStr(&buf, " AND n.nspname <> 'pg_catalog'\n" 1309 " AND n.nspname <> 'information_schema'\n"); 1310 1311 processSQLNamePattern(pset.db, &buf, pattern, true, false, 1312 "n.nspname", "r.rulename", NULL, 1313 "pg_catalog.pg_table_is_visible(c.oid)"); 1314 1315 /* Trigger descriptions */ 1316 appendPQExpBuffer(&buf, 1317 "UNION ALL\n" 1318 " SELECT t.oid as oid, t.tableoid as tableoid,\n" 1319 " n.nspname as nspname,\n" 1320 " CAST(t.tgname AS pg_catalog.text) as name," 1321 " CAST('%s' AS pg_catalog.text) as object\n" 1322 " FROM pg_catalog.pg_trigger t\n" 1323 " JOIN pg_catalog.pg_class c ON c.oid = t.tgrelid\n" 1324 " LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace\n", 1325 gettext_noop("trigger")); 1326 1327 if (!showSystem && !pattern) 1328 appendPQExpBufferStr(&buf, "WHERE n.nspname <> 'pg_catalog'\n" 1329 " AND n.nspname <> 'information_schema'\n"); 1330 1331 processSQLNamePattern(pset.db, &buf, pattern, !showSystem && !pattern, false, 1332 "n.nspname", "t.tgname", NULL, 1333 "pg_catalog.pg_table_is_visible(c.oid)"); 1334 1335 appendPQExpBufferStr(&buf, 1336 ") AS tt\n" 1337 " JOIN pg_catalog.pg_description d ON (tt.oid = d.objoid AND tt.tableoid = d.classoid AND d.objsubid = 0)\n"); 1338 1339 appendPQExpBufferStr(&buf, "ORDER BY 1, 2, 3;"); 1340 1341 res = PSQLexec(buf.data); 1342 termPQExpBuffer(&buf); 1343 if (!res) 1344 return false; 1345 1346 myopt.nullPrint = NULL; 1347 myopt.title = _("Object descriptions"); 1348 myopt.translate_header = true; 1349 myopt.translate_columns = translate_columns; 1350 myopt.n_translate_columns = lengthof(translate_columns); 1351 1352 printQuery(res, &myopt, pset.queryFout, false, pset.logfile); 1353 1354 PQclear(res); 1355 return true; 1356 } 1357 1358 1359 /* 1360 * describeTableDetails (for \d) 1361 * 1362 * This routine finds the tables to be displayed, and calls 1363 * describeOneTableDetails for each one. 1364 * 1365 * verbose: if true, this is \d+ 1366 */ 1367 bool 1368 describeTableDetails(const char *pattern, bool verbose, bool showSystem) 1369 { 1370 PQExpBufferData buf; 1371 PGresult *res; 1372 int i; 1373 1374 initPQExpBuffer(&buf); 1375 1376 printfPQExpBuffer(&buf, 1377 "SELECT c.oid,\n" 1378 " n.nspname,\n" 1379 " c.relname\n" 1380 "FROM pg_catalog.pg_class c\n" 1381 " LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace\n"); 1382 1383 if (!showSystem && !pattern) 1384 appendPQExpBufferStr(&buf, "WHERE n.nspname <> 'pg_catalog'\n" 1385 " AND n.nspname <> 'information_schema'\n"); 1386 1387 processSQLNamePattern(pset.db, &buf, pattern, !showSystem && !pattern, false, 1388 "n.nspname", "c.relname", NULL, 1389 "pg_catalog.pg_table_is_visible(c.oid)"); 1390 1391 appendPQExpBufferStr(&buf, "ORDER BY 2, 3;"); 1392 1393 res = PSQLexec(buf.data); 1394 termPQExpBuffer(&buf); 1395 if (!res) 1396 return false; 1397 1398 if (PQntuples(res) == 0) 1399 { 1400 if (!pset.quiet) 1401 { 1402 if (pattern) 1403 pg_log_error("Did not find any relation named \"%s\".", 1404 pattern); 1405 else 1406 pg_log_error("Did not find any relations."); 1407 } 1408 PQclear(res); 1409 return false; 1410 } 1411 1412 for (i = 0; i < PQntuples(res); i++) 1413 { 1414 const char *oid; 1415 const char *nspname; 1416 const char *relname; 1417 1418 oid = PQgetvalue(res, i, 0); 1419 nspname = PQgetvalue(res, i, 1); 1420 relname = PQgetvalue(res, i, 2); 1421 1422 if (!describeOneTableDetails(nspname, relname, oid, verbose)) 1423 { 1424 PQclear(res); 1425 return false; 1426 } 1427 if (cancel_pressed) 1428 { 1429 PQclear(res); 1430 return false; 1431 } 1432 } 1433 1434 PQclear(res); 1435 return true; 1436 } 1437 1438 /* 1439 * describeOneTableDetails (for \d) 1440 * 1441 * Unfortunately, the information presented here is so complicated that it 1442 * cannot be done in a single query. So we have to assemble the printed table 1443 * by hand and pass it to the underlying printTable() function. 1444 */ 1445 static bool 1446 describeOneTableDetails(const char *schemaname, 1447 const char *relationname, 1448 const char *oid, 1449 bool verbose) 1450 { 1451 bool retval = false; 1452 PQExpBufferData buf; 1453 PGresult *res = NULL; 1454 printTableOpt myopt = pset.popt.topt; 1455 printTableContent cont; 1456 bool printTableInitialized = false; 1457 int i; 1458 char *view_def = NULL; 1459 char *headers[11]; 1460 PQExpBufferData title; 1461 PQExpBufferData tmpbuf; 1462 int cols; 1463 int attname_col = -1, /* column indexes in "res" */ 1464 atttype_col = -1, 1465 attrdef_col = -1, 1466 attnotnull_col = -1, 1467 attcoll_col = -1, 1468 attidentity_col = -1, 1469 attgenerated_col = -1, 1470 isindexkey_col = -1, 1471 indexdef_col = -1, 1472 fdwopts_col = -1, 1473 attstorage_col = -1, 1474 attstattarget_col = -1, 1475 attdescr_col = -1; 1476 int numrows; 1477 struct 1478 { 1479 int16 checks; 1480 char relkind; 1481 bool hasindex; 1482 bool hasrules; 1483 bool hastriggers; 1484 bool rowsecurity; 1485 bool forcerowsecurity; 1486 bool hasoids; 1487 bool ispartition; 1488 Oid tablespace; 1489 char *reloptions; 1490 char *reloftype; 1491 char relpersistence; 1492 char relreplident; 1493 char *relam; 1494 } tableinfo; 1495 bool show_column_details = false; 1496 1497 myopt.default_footer = false; 1498 /* This output looks confusing in expanded mode. */ 1499 myopt.expanded = false; 1500 1501 initPQExpBuffer(&buf); 1502 initPQExpBuffer(&title); 1503 initPQExpBuffer(&tmpbuf); 1504 1505 /* Get general table info */ 1506 if (pset.sversion >= 120000) 1507 { 1508 printfPQExpBuffer(&buf, 1509 "SELECT c.relchecks, c.relkind, c.relhasindex, c.relhasrules, " 1510 "c.relhastriggers, c.relrowsecurity, c.relforcerowsecurity, " 1511 "false AS relhasoids, c.relispartition, %s, c.reltablespace, " 1512 "CASE WHEN c.reloftype = 0 THEN '' ELSE c.reloftype::pg_catalog.regtype::pg_catalog.text END, " 1513 "c.relpersistence, c.relreplident, am.amname\n" 1514 "FROM pg_catalog.pg_class c\n " 1515 "LEFT JOIN pg_catalog.pg_class tc ON (c.reltoastrelid = tc.oid)\n" 1516 "LEFT JOIN pg_catalog.pg_am am ON (c.relam = am.oid)\n" 1517 "WHERE c.oid = '%s';", 1518 (verbose ? 1519 "pg_catalog.array_to_string(c.reloptions || " 1520 "array(select 'toast.' || x from pg_catalog.unnest(tc.reloptions) x), ', ')\n" 1521 : "''"), 1522 oid); 1523 } 1524 else if (pset.sversion >= 100000) 1525 { 1526 printfPQExpBuffer(&buf, 1527 "SELECT c.relchecks, c.relkind, c.relhasindex, c.relhasrules, " 1528 "c.relhastriggers, c.relrowsecurity, c.relforcerowsecurity, " 1529 "c.relhasoids, c.relispartition, %s, c.reltablespace, " 1530 "CASE WHEN c.reloftype = 0 THEN '' ELSE c.reloftype::pg_catalog.regtype::pg_catalog.text END, " 1531 "c.relpersistence, c.relreplident\n" 1532 "FROM pg_catalog.pg_class c\n " 1533 "LEFT JOIN pg_catalog.pg_class tc ON (c.reltoastrelid = tc.oid)\n" 1534 "WHERE c.oid = '%s';", 1535 (verbose ? 1536 "pg_catalog.array_to_string(c.reloptions || " 1537 "array(select 'toast.' || x from pg_catalog.unnest(tc.reloptions) x), ', ')\n" 1538 : "''"), 1539 oid); 1540 } 1541 else if (pset.sversion >= 90500) 1542 { 1543 printfPQExpBuffer(&buf, 1544 "SELECT c.relchecks, c.relkind, c.relhasindex, c.relhasrules, " 1545 "c.relhastriggers, c.relrowsecurity, c.relforcerowsecurity, " 1546 "c.relhasoids, false as relispartition, %s, c.reltablespace, " 1547 "CASE WHEN c.reloftype = 0 THEN '' ELSE c.reloftype::pg_catalog.regtype::pg_catalog.text END, " 1548 "c.relpersistence, c.relreplident\n" 1549 "FROM pg_catalog.pg_class c\n " 1550 "LEFT JOIN pg_catalog.pg_class tc ON (c.reltoastrelid = tc.oid)\n" 1551 "WHERE c.oid = '%s';", 1552 (verbose ? 1553 "pg_catalog.array_to_string(c.reloptions || " 1554 "array(select 'toast.' || x from pg_catalog.unnest(tc.reloptions) x), ', ')\n" 1555 : "''"), 1556 oid); 1557 } 1558 else if (pset.sversion >= 90400) 1559 { 1560 printfPQExpBuffer(&buf, 1561 "SELECT c.relchecks, c.relkind, c.relhasindex, c.relhasrules, " 1562 "c.relhastriggers, false, false, c.relhasoids, " 1563 "false as relispartition, %s, c.reltablespace, " 1564 "CASE WHEN c.reloftype = 0 THEN '' ELSE c.reloftype::pg_catalog.regtype::pg_catalog.text END, " 1565 "c.relpersistence, c.relreplident\n" 1566 "FROM pg_catalog.pg_class c\n " 1567 "LEFT JOIN pg_catalog.pg_class tc ON (c.reltoastrelid = tc.oid)\n" 1568 "WHERE c.oid = '%s';", 1569 (verbose ? 1570 "pg_catalog.array_to_string(c.reloptions || " 1571 "array(select 'toast.' || x from pg_catalog.unnest(tc.reloptions) x), ', ')\n" 1572 : "''"), 1573 oid); 1574 } 1575 else if (pset.sversion >= 90100) 1576 { 1577 printfPQExpBuffer(&buf, 1578 "SELECT c.relchecks, c.relkind, c.relhasindex, c.relhasrules, " 1579 "c.relhastriggers, false, false, c.relhasoids, " 1580 "false as relispartition, %s, c.reltablespace, " 1581 "CASE WHEN c.reloftype = 0 THEN '' ELSE c.reloftype::pg_catalog.regtype::pg_catalog.text END, " 1582 "c.relpersistence\n" 1583 "FROM pg_catalog.pg_class c\n " 1584 "LEFT JOIN pg_catalog.pg_class tc ON (c.reltoastrelid = tc.oid)\n" 1585 "WHERE c.oid = '%s';", 1586 (verbose ? 1587 "pg_catalog.array_to_string(c.reloptions || " 1588 "array(select 'toast.' || x from pg_catalog.unnest(tc.reloptions) x), ', ')\n" 1589 : "''"), 1590 oid); 1591 } 1592 else if (pset.sversion >= 90000) 1593 { 1594 printfPQExpBuffer(&buf, 1595 "SELECT c.relchecks, c.relkind, c.relhasindex, c.relhasrules, " 1596 "c.relhastriggers, false, false, c.relhasoids, " 1597 "false as relispartition, %s, c.reltablespace, " 1598 "CASE WHEN c.reloftype = 0 THEN '' ELSE c.reloftype::pg_catalog.regtype::pg_catalog.text END\n" 1599 "FROM pg_catalog.pg_class c\n " 1600 "LEFT JOIN pg_catalog.pg_class tc ON (c.reltoastrelid = tc.oid)\n" 1601 "WHERE c.oid = '%s';", 1602 (verbose ? 1603 "pg_catalog.array_to_string(c.reloptions || " 1604 "array(select 'toast.' || x from pg_catalog.unnest(tc.reloptions) x), ', ')\n" 1605 : "''"), 1606 oid); 1607 } 1608 else if (pset.sversion >= 80400) 1609 { 1610 printfPQExpBuffer(&buf, 1611 "SELECT c.relchecks, c.relkind, c.relhasindex, c.relhasrules, " 1612 "c.relhastriggers, false, false, c.relhasoids, " 1613 "false as relispartition, %s, c.reltablespace\n" 1614 "FROM pg_catalog.pg_class c\n " 1615 "LEFT JOIN pg_catalog.pg_class tc ON (c.reltoastrelid = tc.oid)\n" 1616 "WHERE c.oid = '%s';", 1617 (verbose ? 1618 "pg_catalog.array_to_string(c.reloptions || " 1619 "array(select 'toast.' || x from pg_catalog.unnest(tc.reloptions) x), ', ')\n" 1620 : "''"), 1621 oid); 1622 } 1623 else if (pset.sversion >= 80200) 1624 { 1625 printfPQExpBuffer(&buf, 1626 "SELECT relchecks, relkind, relhasindex, relhasrules, " 1627 "reltriggers <> 0, false, false, relhasoids, " 1628 "false as relispartition, %s, reltablespace\n" 1629 "FROM pg_catalog.pg_class WHERE oid = '%s';", 1630 (verbose ? 1631 "pg_catalog.array_to_string(reloptions, E', ')" : "''"), 1632 oid); 1633 } 1634 else if (pset.sversion >= 80000) 1635 { 1636 printfPQExpBuffer(&buf, 1637 "SELECT relchecks, relkind, relhasindex, relhasrules, " 1638 "reltriggers <> 0, false, false, relhasoids, " 1639 "false as relispartition, '', reltablespace\n" 1640 "FROM pg_catalog.pg_class WHERE oid = '%s';", 1641 oid); 1642 } 1643 else 1644 { 1645 printfPQExpBuffer(&buf, 1646 "SELECT relchecks, relkind, relhasindex, relhasrules, " 1647 "reltriggers <> 0, false, false, relhasoids, " 1648 "false as relispartition, '', ''\n" 1649 "FROM pg_catalog.pg_class WHERE oid = '%s';", 1650 oid); 1651 } 1652 1653 res = PSQLexec(buf.data); 1654 if (!res) 1655 goto error_return; 1656 1657 /* Did we get anything? */ 1658 if (PQntuples(res) == 0) 1659 { 1660 if (!pset.quiet) 1661 pg_log_error("Did not find any relation with OID %s.", oid); 1662 goto error_return; 1663 } 1664 1665 tableinfo.checks = atoi(PQgetvalue(res, 0, 0)); 1666 tableinfo.relkind = *(PQgetvalue(res, 0, 1)); 1667 tableinfo.hasindex = strcmp(PQgetvalue(res, 0, 2), "t") == 0; 1668 tableinfo.hasrules = strcmp(PQgetvalue(res, 0, 3), "t") == 0; 1669 tableinfo.hastriggers = strcmp(PQgetvalue(res, 0, 4), "t") == 0; 1670 tableinfo.rowsecurity = strcmp(PQgetvalue(res, 0, 5), "t") == 0; 1671 tableinfo.forcerowsecurity = strcmp(PQgetvalue(res, 0, 6), "t") == 0; 1672 tableinfo.hasoids = strcmp(PQgetvalue(res, 0, 7), "t") == 0; 1673 tableinfo.ispartition = strcmp(PQgetvalue(res, 0, 8), "t") == 0; 1674 tableinfo.reloptions = (pset.sversion >= 80200) ? 1675 pg_strdup(PQgetvalue(res, 0, 9)) : NULL; 1676 tableinfo.tablespace = (pset.sversion >= 80000) ? 1677 atooid(PQgetvalue(res, 0, 10)) : 0; 1678 tableinfo.reloftype = (pset.sversion >= 90000 && 1679 strcmp(PQgetvalue(res, 0, 11), "") != 0) ? 1680 pg_strdup(PQgetvalue(res, 0, 11)) : NULL; 1681 tableinfo.relpersistence = (pset.sversion >= 90100) ? 1682 *(PQgetvalue(res, 0, 12)) : 0; 1683 tableinfo.relreplident = (pset.sversion >= 90400) ? 1684 *(PQgetvalue(res, 0, 13)) : 'd'; 1685 if (pset.sversion >= 120000) 1686 tableinfo.relam = PQgetisnull(res, 0, 14) ? 1687 (char *) NULL : pg_strdup(PQgetvalue(res, 0, 14)); 1688 else 1689 tableinfo.relam = NULL; 1690 PQclear(res); 1691 res = NULL; 1692 1693 /* 1694 * If it's a sequence, deal with it here separately. 1695 */ 1696 if (tableinfo.relkind == RELKIND_SEQUENCE) 1697 { 1698 PGresult *result = NULL; 1699 printQueryOpt myopt = pset.popt; 1700 char *footers[2] = {NULL, NULL}; 1701 1702 if (pset.sversion >= 100000) 1703 { 1704 printfPQExpBuffer(&buf, 1705 "SELECT pg_catalog.format_type(seqtypid, NULL) AS \"%s\",\n" 1706 " seqstart AS \"%s\",\n" 1707 " seqmin AS \"%s\",\n" 1708 " seqmax AS \"%s\",\n" 1709 " seqincrement AS \"%s\",\n" 1710 " CASE WHEN seqcycle THEN '%s' ELSE '%s' END AS \"%s\",\n" 1711 " seqcache AS \"%s\"\n", 1712 gettext_noop("Type"), 1713 gettext_noop("Start"), 1714 gettext_noop("Minimum"), 1715 gettext_noop("Maximum"), 1716 gettext_noop("Increment"), 1717 gettext_noop("yes"), 1718 gettext_noop("no"), 1719 gettext_noop("Cycles?"), 1720 gettext_noop("Cache")); 1721 appendPQExpBuffer(&buf, 1722 "FROM pg_catalog.pg_sequence\n" 1723 "WHERE seqrelid = '%s';", 1724 oid); 1725 } 1726 else 1727 { 1728 printfPQExpBuffer(&buf, 1729 "SELECT 'bigint' AS \"%s\",\n" 1730 " start_value AS \"%s\",\n" 1731 " min_value AS \"%s\",\n" 1732 " max_value AS \"%s\",\n" 1733 " increment_by AS \"%s\",\n" 1734 " CASE WHEN is_cycled THEN '%s' ELSE '%s' END AS \"%s\",\n" 1735 " cache_value AS \"%s\"\n", 1736 gettext_noop("Type"), 1737 gettext_noop("Start"), 1738 gettext_noop("Minimum"), 1739 gettext_noop("Maximum"), 1740 gettext_noop("Increment"), 1741 gettext_noop("yes"), 1742 gettext_noop("no"), 1743 gettext_noop("Cycles?"), 1744 gettext_noop("Cache")); 1745 appendPQExpBuffer(&buf, "FROM %s", fmtId(schemaname)); 1746 /* must be separate because fmtId isn't reentrant */ 1747 appendPQExpBuffer(&buf, ".%s;", fmtId(relationname)); 1748 } 1749 1750 res = PSQLexec(buf.data); 1751 if (!res) 1752 goto error_return; 1753 1754 /* Footer information about a sequence */ 1755 1756 /* Get the column that owns this sequence */ 1757 printfPQExpBuffer(&buf, "SELECT pg_catalog.quote_ident(nspname) || '.' ||" 1758 "\n pg_catalog.quote_ident(relname) || '.' ||" 1759 "\n pg_catalog.quote_ident(attname)," 1760 "\n d.deptype" 1761 "\nFROM pg_catalog.pg_class c" 1762 "\nINNER JOIN pg_catalog.pg_depend d ON c.oid=d.refobjid" 1763 "\nINNER JOIN pg_catalog.pg_namespace n ON n.oid=c.relnamespace" 1764 "\nINNER JOIN pg_catalog.pg_attribute a ON (" 1765 "\n a.attrelid=c.oid AND" 1766 "\n a.attnum=d.refobjsubid)" 1767 "\nWHERE d.classid='pg_catalog.pg_class'::pg_catalog.regclass" 1768 "\n AND d.refclassid='pg_catalog.pg_class'::pg_catalog.regclass" 1769 "\n AND d.objid='%s'" 1770 "\n AND d.deptype IN ('a', 'i')", 1771 oid); 1772 1773 result = PSQLexec(buf.data); 1774 1775 /* 1776 * If we get no rows back, don't show anything (obviously). We should 1777 * never get more than one row back, but if we do, just ignore it and 1778 * don't print anything. 1779 */ 1780 if (!result) 1781 goto error_return; 1782 else if (PQntuples(result) == 1) 1783 { 1784 switch (PQgetvalue(result, 0, 1)[0]) 1785 { 1786 case 'a': 1787 footers[0] = psprintf(_("Owned by: %s"), 1788 PQgetvalue(result, 0, 0)); 1789 break; 1790 case 'i': 1791 footers[0] = psprintf(_("Sequence for identity column: %s"), 1792 PQgetvalue(result, 0, 0)); 1793 break; 1794 } 1795 } 1796 PQclear(result); 1797 1798 printfPQExpBuffer(&title, _("Sequence \"%s.%s\""), 1799 schemaname, relationname); 1800 1801 myopt.footers = footers; 1802 myopt.topt.default_footer = false; 1803 myopt.title = title.data; 1804 myopt.translate_header = true; 1805 1806 printQuery(res, &myopt, pset.queryFout, false, pset.logfile); 1807 1808 if (footers[0]) 1809 free(footers[0]); 1810 1811 retval = true; 1812 goto error_return; /* not an error, just return early */ 1813 } 1814 1815 /* Identify whether we should print collation, nullable, default vals */ 1816 if (tableinfo.relkind == RELKIND_RELATION || 1817 tableinfo.relkind == RELKIND_VIEW || 1818 tableinfo.relkind == RELKIND_MATVIEW || 1819 tableinfo.relkind == RELKIND_FOREIGN_TABLE || 1820 tableinfo.relkind == RELKIND_COMPOSITE_TYPE || 1821 tableinfo.relkind == RELKIND_PARTITIONED_TABLE) 1822 show_column_details = true; 1823 1824 /* 1825 * Get per-column info 1826 * 1827 * Since the set of query columns we need varies depending on relkind and 1828 * server version, we compute all the column numbers on-the-fly. Column 1829 * number variables for columns not fetched are left as -1; this avoids 1830 * duplicative test logic below. 1831 */ 1832 cols = 0; 1833 printfPQExpBuffer(&buf, "SELECT a.attname"); 1834 attname_col = cols++; 1835 appendPQExpBufferStr(&buf, ",\n pg_catalog.format_type(a.atttypid, a.atttypmod)"); 1836 atttype_col = cols++; 1837 1838 if (show_column_details) 1839 { 1840 /* use "pretty" mode for expression to avoid excessive parentheses */ 1841 appendPQExpBufferStr(&buf, 1842 ",\n (SELECT pg_catalog.pg_get_expr(d.adbin, d.adrelid, true)" 1843 "\n FROM pg_catalog.pg_attrdef d" 1844 "\n WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef)" 1845 ",\n a.attnotnull"); 1846 attrdef_col = cols++; 1847 attnotnull_col = cols++; 1848 if (pset.sversion >= 90100) 1849 appendPQExpBufferStr(&buf, ",\n (SELECT c.collname FROM pg_catalog.pg_collation c, pg_catalog.pg_type t\n" 1850 " WHERE c.oid = a.attcollation AND t.oid = a.atttypid AND a.attcollation <> t.typcollation) AS attcollation"); 1851 else 1852 appendPQExpBufferStr(&buf, ",\n NULL AS attcollation"); 1853 attcoll_col = cols++; 1854 if (pset.sversion >= 100000) 1855 appendPQExpBufferStr(&buf, ",\n a.attidentity"); 1856 else 1857 appendPQExpBufferStr(&buf, ",\n ''::pg_catalog.char AS attidentity"); 1858 attidentity_col = cols++; 1859 if (pset.sversion >= 120000) 1860 appendPQExpBufferStr(&buf, ",\n a.attgenerated"); 1861 else 1862 appendPQExpBufferStr(&buf, ",\n ''::pg_catalog.char AS attgenerated"); 1863 attgenerated_col = cols++; 1864 } 1865 if (tableinfo.relkind == RELKIND_INDEX || 1866 tableinfo.relkind == RELKIND_PARTITIONED_INDEX) 1867 { 1868 if (pset.sversion >= 110000) 1869 { 1870 appendPQExpBuffer(&buf, ",\n CASE WHEN a.attnum <= (SELECT i.indnkeyatts FROM pg_catalog.pg_index i WHERE i.indexrelid = '%s') THEN '%s' ELSE '%s' END AS is_key", 1871 oid, 1872 gettext_noop("yes"), 1873 gettext_noop("no")); 1874 isindexkey_col = cols++; 1875 } 1876 appendPQExpBufferStr(&buf, ",\n pg_catalog.pg_get_indexdef(a.attrelid, a.attnum, TRUE) AS indexdef"); 1877 indexdef_col = cols++; 1878 } 1879 /* FDW options for foreign table column, only for 9.2 or later */ 1880 if (tableinfo.relkind == RELKIND_FOREIGN_TABLE && pset.sversion >= 90200) 1881 { 1882 appendPQExpBufferStr(&buf, ",\n CASE WHEN attfdwoptions IS NULL THEN '' ELSE " 1883 " '(' || pg_catalog.array_to_string(ARRAY(SELECT pg_catalog.quote_ident(option_name) || ' ' || pg_catalog.quote_literal(option_value) FROM " 1884 " pg_catalog.pg_options_to_table(attfdwoptions)), ', ') || ')' END AS attfdwoptions"); 1885 fdwopts_col = cols++; 1886 } 1887 if (verbose) 1888 { 1889 appendPQExpBufferStr(&buf, ",\n a.attstorage"); 1890 attstorage_col = cols++; 1891 1892 /* stats target, if relevant to relkind */ 1893 if (tableinfo.relkind == RELKIND_RELATION || 1894 tableinfo.relkind == RELKIND_INDEX || 1895 tableinfo.relkind == RELKIND_PARTITIONED_INDEX || 1896 tableinfo.relkind == RELKIND_MATVIEW || 1897 tableinfo.relkind == RELKIND_FOREIGN_TABLE || 1898 tableinfo.relkind == RELKIND_PARTITIONED_TABLE) 1899 { 1900 appendPQExpBufferStr(&buf, ",\n CASE WHEN a.attstattarget=-1 THEN NULL ELSE a.attstattarget END AS attstattarget"); 1901 attstattarget_col = cols++; 1902 } 1903 1904 /* 1905 * In 9.0+, we have column comments for: relations, views, composite 1906 * types, and foreign tables (cf. CommentObject() in comment.c). 1907 */ 1908 if (tableinfo.relkind == RELKIND_RELATION || 1909 tableinfo.relkind == RELKIND_VIEW || 1910 tableinfo.relkind == RELKIND_MATVIEW || 1911 tableinfo.relkind == RELKIND_FOREIGN_TABLE || 1912 tableinfo.relkind == RELKIND_COMPOSITE_TYPE || 1913 tableinfo.relkind == RELKIND_PARTITIONED_TABLE) 1914 { 1915 appendPQExpBufferStr(&buf, ",\n pg_catalog.col_description(a.attrelid, a.attnum)"); 1916 attdescr_col = cols++; 1917 } 1918 } 1919 1920 appendPQExpBufferStr(&buf, "\nFROM pg_catalog.pg_attribute a"); 1921 appendPQExpBuffer(&buf, "\nWHERE a.attrelid = '%s' AND a.attnum > 0 AND NOT a.attisdropped", oid); 1922 appendPQExpBufferStr(&buf, "\nORDER BY a.attnum;"); 1923 1924 res = PSQLexec(buf.data); 1925 if (!res) 1926 goto error_return; 1927 numrows = PQntuples(res); 1928 1929 /* Make title */ 1930 switch (tableinfo.relkind) 1931 { 1932 case RELKIND_RELATION: 1933 if (tableinfo.relpersistence == 'u') 1934 printfPQExpBuffer(&title, _("Unlogged table \"%s.%s\""), 1935 schemaname, relationname); 1936 else 1937 printfPQExpBuffer(&title, _("Table \"%s.%s\""), 1938 schemaname, relationname); 1939 break; 1940 case RELKIND_VIEW: 1941 printfPQExpBuffer(&title, _("View \"%s.%s\""), 1942 schemaname, relationname); 1943 break; 1944 case RELKIND_MATVIEW: 1945 if (tableinfo.relpersistence == 'u') 1946 printfPQExpBuffer(&title, _("Unlogged materialized view \"%s.%s\""), 1947 schemaname, relationname); 1948 else 1949 printfPQExpBuffer(&title, _("Materialized view \"%s.%s\""), 1950 schemaname, relationname); 1951 break; 1952 case RELKIND_INDEX: 1953 if (tableinfo.relpersistence == 'u') 1954 printfPQExpBuffer(&title, _("Unlogged index \"%s.%s\""), 1955 schemaname, relationname); 1956 else 1957 printfPQExpBuffer(&title, _("Index \"%s.%s\""), 1958 schemaname, relationname); 1959 break; 1960 case RELKIND_PARTITIONED_INDEX: 1961 if (tableinfo.relpersistence == 'u') 1962 printfPQExpBuffer(&title, _("Unlogged partitioned index \"%s.%s\""), 1963 schemaname, relationname); 1964 else 1965 printfPQExpBuffer(&title, _("Partitioned index \"%s.%s\""), 1966 schemaname, relationname); 1967 break; 1968 case 's': 1969 /* not used as of 8.2, but keep it for backwards compatibility */ 1970 printfPQExpBuffer(&title, _("Special relation \"%s.%s\""), 1971 schemaname, relationname); 1972 break; 1973 case RELKIND_TOASTVALUE: 1974 printfPQExpBuffer(&title, _("TOAST table \"%s.%s\""), 1975 schemaname, relationname); 1976 break; 1977 case RELKIND_COMPOSITE_TYPE: 1978 printfPQExpBuffer(&title, _("Composite type \"%s.%s\""), 1979 schemaname, relationname); 1980 break; 1981 case RELKIND_FOREIGN_TABLE: 1982 printfPQExpBuffer(&title, _("Foreign table \"%s.%s\""), 1983 schemaname, relationname); 1984 break; 1985 case RELKIND_PARTITIONED_TABLE: 1986 if (tableinfo.relpersistence == 'u') 1987 printfPQExpBuffer(&title, _("Unlogged partitioned table \"%s.%s\""), 1988 schemaname, relationname); 1989 else 1990 printfPQExpBuffer(&title, _("Partitioned table \"%s.%s\""), 1991 schemaname, relationname); 1992 break; 1993 default: 1994 /* untranslated unknown relkind */ 1995 printfPQExpBuffer(&title, "?%c? \"%s.%s\"", 1996 tableinfo.relkind, schemaname, relationname); 1997 break; 1998 } 1999 2000 /* Fill headers[] with the names of the columns we will output */ 2001 cols = 0; 2002 headers[cols++] = gettext_noop("Column"); 2003 headers[cols++] = gettext_noop("Type"); 2004 if (show_column_details) 2005 { 2006 headers[cols++] = gettext_noop("Collation"); 2007 headers[cols++] = gettext_noop("Nullable"); 2008 headers[cols++] = gettext_noop("Default"); 2009 } 2010 if (isindexkey_col >= 0) 2011 headers[cols++] = gettext_noop("Key?"); 2012 if (indexdef_col >= 0) 2013 headers[cols++] = gettext_noop("Definition"); 2014 if (fdwopts_col >= 0) 2015 headers[cols++] = gettext_noop("FDW options"); 2016 if (attstorage_col >= 0) 2017 headers[cols++] = gettext_noop("Storage"); 2018 if (attstattarget_col >= 0) 2019 headers[cols++] = gettext_noop("Stats target"); 2020 if (attdescr_col >= 0) 2021 headers[cols++] = gettext_noop("Description"); 2022 2023 Assert(cols <= lengthof(headers)); 2024 2025 printTableInit(&cont, &myopt, title.data, cols, numrows); 2026 printTableInitialized = true; 2027 2028 for (i = 0; i < cols; i++) 2029 printTableAddHeader(&cont, headers[i], true, 'l'); 2030 2031 /* Generate table cells to be printed */ 2032 for (i = 0; i < numrows; i++) 2033 { 2034 /* Column */ 2035 printTableAddCell(&cont, PQgetvalue(res, i, attname_col), false, false); 2036 2037 /* Type */ 2038 printTableAddCell(&cont, PQgetvalue(res, i, atttype_col), false, false); 2039 2040 /* Collation, Nullable, Default */ 2041 if (show_column_details) 2042 { 2043 char *identity; 2044 char *generated; 2045 char *default_str; 2046 bool mustfree = false; 2047 2048 printTableAddCell(&cont, PQgetvalue(res, i, attcoll_col), false, false); 2049 2050 printTableAddCell(&cont, 2051 strcmp(PQgetvalue(res, i, attnotnull_col), "t") == 0 ? "not null" : "", 2052 false, false); 2053 2054 identity = PQgetvalue(res, i, attidentity_col); 2055 generated = PQgetvalue(res, i, attgenerated_col); 2056 2057 if (identity[0] == ATTRIBUTE_IDENTITY_ALWAYS) 2058 default_str = "generated always as identity"; 2059 else if (identity[0] == ATTRIBUTE_IDENTITY_BY_DEFAULT) 2060 default_str = "generated by default as identity"; 2061 else if (generated[0] == ATTRIBUTE_GENERATED_STORED) 2062 { 2063 default_str = psprintf("generated always as (%s) stored", 2064 PQgetvalue(res, i, attrdef_col)); 2065 mustfree = true; 2066 } 2067 else 2068 default_str = PQgetvalue(res, i, attrdef_col); 2069 2070 printTableAddCell(&cont, default_str, false, mustfree); 2071 } 2072 2073 /* Info for index columns */ 2074 if (isindexkey_col >= 0) 2075 printTableAddCell(&cont, PQgetvalue(res, i, isindexkey_col), true, false); 2076 if (indexdef_col >= 0) 2077 printTableAddCell(&cont, PQgetvalue(res, i, indexdef_col), false, false); 2078 2079 /* FDW options for foreign table columns */ 2080 if (fdwopts_col >= 0) 2081 printTableAddCell(&cont, PQgetvalue(res, i, fdwopts_col), false, false); 2082 2083 /* Storage and Description */ 2084 if (attstorage_col >= 0) 2085 { 2086 char *storage = PQgetvalue(res, i, attstorage_col); 2087 2088 /* these strings are literal in our syntax, so not translated. */ 2089 printTableAddCell(&cont, (storage[0] == 'p' ? "plain" : 2090 (storage[0] == 'm' ? "main" : 2091 (storage[0] == 'x' ? "extended" : 2092 (storage[0] == 'e' ? "external" : 2093 "???")))), 2094 false, false); 2095 } 2096 2097 /* Statistics target, if the relkind supports this feature */ 2098 if (attstattarget_col >= 0) 2099 printTableAddCell(&cont, PQgetvalue(res, i, attstattarget_col), 2100 false, false); 2101 2102 /* Column comments, if the relkind supports this feature */ 2103 if (attdescr_col >= 0) 2104 printTableAddCell(&cont, PQgetvalue(res, i, attdescr_col), 2105 false, false); 2106 } 2107 2108 /* Make footers */ 2109 2110 if (tableinfo.ispartition) 2111 { 2112 /* Footer information for a partition child table */ 2113 PGresult *result; 2114 2115 printfPQExpBuffer(&buf, 2116 "SELECT inhparent::pg_catalog.regclass,\n" 2117 " pg_catalog.pg_get_expr(c.relpartbound, c.oid)"); 2118 /* If verbose, also request the partition constraint definition */ 2119 if (verbose) 2120 appendPQExpBufferStr(&buf, 2121 ",\n pg_catalog.pg_get_partition_constraintdef(c.oid)"); 2122 appendPQExpBuffer(&buf, 2123 "\nFROM pg_catalog.pg_class c" 2124 " JOIN pg_catalog.pg_inherits i" 2125 " ON c.oid = inhrelid" 2126 "\nWHERE c.oid = '%s';", oid); 2127 result = PSQLexec(buf.data); 2128 if (!result) 2129 goto error_return; 2130 2131 if (PQntuples(result) > 0) 2132 { 2133 char *parent_name = PQgetvalue(result, 0, 0); 2134 char *partdef = PQgetvalue(result, 0, 1); 2135 2136 printfPQExpBuffer(&tmpbuf, _("Partition of: %s %s"), parent_name, 2137 partdef); 2138 printTableAddFooter(&cont, tmpbuf.data); 2139 2140 if (verbose) 2141 { 2142 char *partconstraintdef = NULL; 2143 2144 if (!PQgetisnull(result, 0, 2)) 2145 partconstraintdef = PQgetvalue(result, 0, 2); 2146 /* If there isn't any constraint, show that explicitly */ 2147 if (partconstraintdef == NULL || partconstraintdef[0] == '\0') 2148 printfPQExpBuffer(&tmpbuf, _("No partition constraint")); 2149 else 2150 printfPQExpBuffer(&tmpbuf, _("Partition constraint: %s"), 2151 partconstraintdef); 2152 printTableAddFooter(&cont, tmpbuf.data); 2153 } 2154 } 2155 PQclear(result); 2156 } 2157 2158 if (tableinfo.relkind == RELKIND_PARTITIONED_TABLE) 2159 { 2160 /* Footer information for a partitioned table (partitioning parent) */ 2161 PGresult *result; 2162 2163 printfPQExpBuffer(&buf, 2164 "SELECT pg_catalog.pg_get_partkeydef('%s'::pg_catalog.oid);", 2165 oid); 2166 result = PSQLexec(buf.data); 2167 if (!result) 2168 goto error_return; 2169 2170 if (PQntuples(result) == 1) 2171 { 2172 char *partkeydef = PQgetvalue(result, 0, 0); 2173 2174 printfPQExpBuffer(&tmpbuf, _("Partition key: %s"), partkeydef); 2175 printTableAddFooter(&cont, tmpbuf.data); 2176 } 2177 PQclear(result); 2178 } 2179 2180 if (tableinfo.relkind == RELKIND_INDEX || 2181 tableinfo.relkind == RELKIND_PARTITIONED_INDEX) 2182 { 2183 /* Footer information about an index */ 2184 PGresult *result; 2185 2186 printfPQExpBuffer(&buf, 2187 "SELECT i.indisunique, i.indisprimary, i.indisclustered, "); 2188 if (pset.sversion >= 80200) 2189 appendPQExpBufferStr(&buf, "i.indisvalid,\n"); 2190 else 2191 appendPQExpBufferStr(&buf, "true AS indisvalid,\n"); 2192 if (pset.sversion >= 90000) 2193 appendPQExpBufferStr(&buf, 2194 " (NOT i.indimmediate) AND " 2195 "EXISTS (SELECT 1 FROM pg_catalog.pg_constraint " 2196 "WHERE conrelid = i.indrelid AND " 2197 "conindid = i.indexrelid AND " 2198 "contype IN ('p','u','x') AND " 2199 "condeferrable) AS condeferrable,\n" 2200 " (NOT i.indimmediate) AND " 2201 "EXISTS (SELECT 1 FROM pg_catalog.pg_constraint " 2202 "WHERE conrelid = i.indrelid AND " 2203 "conindid = i.indexrelid AND " 2204 "contype IN ('p','u','x') AND " 2205 "condeferred) AS condeferred,\n"); 2206 else 2207 appendPQExpBufferStr(&buf, 2208 " false AS condeferrable, false AS condeferred,\n"); 2209 2210 if (pset.sversion >= 90400) 2211 appendPQExpBuffer(&buf, "i.indisreplident,\n"); 2212 else 2213 appendPQExpBuffer(&buf, "false AS indisreplident,\n"); 2214 2215 appendPQExpBuffer(&buf, " a.amname, c2.relname, " 2216 "pg_catalog.pg_get_expr(i.indpred, i.indrelid, true)\n" 2217 "FROM pg_catalog.pg_index i, pg_catalog.pg_class c, pg_catalog.pg_class c2, pg_catalog.pg_am a\n" 2218 "WHERE i.indexrelid = c.oid AND c.oid = '%s' AND c.relam = a.oid\n" 2219 "AND i.indrelid = c2.oid;", 2220 oid); 2221 2222 result = PSQLexec(buf.data); 2223 if (!result) 2224 goto error_return; 2225 else if (PQntuples(result) != 1) 2226 { 2227 PQclear(result); 2228 goto error_return; 2229 } 2230 else 2231 { 2232 char *indisunique = PQgetvalue(result, 0, 0); 2233 char *indisprimary = PQgetvalue(result, 0, 1); 2234 char *indisclustered = PQgetvalue(result, 0, 2); 2235 char *indisvalid = PQgetvalue(result, 0, 3); 2236 char *deferrable = PQgetvalue(result, 0, 4); 2237 char *deferred = PQgetvalue(result, 0, 5); 2238 char *indisreplident = PQgetvalue(result, 0, 6); 2239 char *indamname = PQgetvalue(result, 0, 7); 2240 char *indtable = PQgetvalue(result, 0, 8); 2241 char *indpred = PQgetvalue(result, 0, 9); 2242 2243 if (strcmp(indisprimary, "t") == 0) 2244 printfPQExpBuffer(&tmpbuf, _("primary key, ")); 2245 else if (strcmp(indisunique, "t") == 0) 2246 printfPQExpBuffer(&tmpbuf, _("unique, ")); 2247 else 2248 resetPQExpBuffer(&tmpbuf); 2249 appendPQExpBuffer(&tmpbuf, "%s, ", indamname); 2250 2251 /* we assume here that index and table are in same schema */ 2252 appendPQExpBuffer(&tmpbuf, _("for table \"%s.%s\""), 2253 schemaname, indtable); 2254 2255 if (strlen(indpred)) 2256 appendPQExpBuffer(&tmpbuf, _(", predicate (%s)"), indpred); 2257 2258 if (strcmp(indisclustered, "t") == 0) 2259 appendPQExpBufferStr(&tmpbuf, _(", clustered")); 2260 2261 if (strcmp(indisvalid, "t") != 0) 2262 appendPQExpBufferStr(&tmpbuf, _(", invalid")); 2263 2264 if (strcmp(deferrable, "t") == 0) 2265 appendPQExpBufferStr(&tmpbuf, _(", deferrable")); 2266 2267 if (strcmp(deferred, "t") == 0) 2268 appendPQExpBufferStr(&tmpbuf, _(", initially deferred")); 2269 2270 if (strcmp(indisreplident, "t") == 0) 2271 appendPQExpBuffer(&tmpbuf, _(", replica identity")); 2272 2273 printTableAddFooter(&cont, tmpbuf.data); 2274 add_tablespace_footer(&cont, tableinfo.relkind, 2275 tableinfo.tablespace, true); 2276 } 2277 2278 PQclear(result); 2279 } 2280 else if (tableinfo.relkind == RELKIND_RELATION || 2281 tableinfo.relkind == RELKIND_MATVIEW || 2282 tableinfo.relkind == RELKIND_FOREIGN_TABLE || 2283 tableinfo.relkind == RELKIND_PARTITIONED_TABLE) 2284 { 2285 /* Footer information about a table */ 2286 PGresult *result = NULL; 2287 int tuples = 0; 2288 2289 /* print indexes */ 2290 if (tableinfo.hasindex) 2291 { 2292 printfPQExpBuffer(&buf, 2293 "SELECT c2.relname, i.indisprimary, i.indisunique, i.indisclustered, "); 2294 if (pset.sversion >= 80200) 2295 appendPQExpBufferStr(&buf, "i.indisvalid, "); 2296 else 2297 appendPQExpBufferStr(&buf, "true as indisvalid, "); 2298 appendPQExpBufferStr(&buf, "pg_catalog.pg_get_indexdef(i.indexrelid, 0, true),\n "); 2299 if (pset.sversion >= 90000) 2300 appendPQExpBufferStr(&buf, 2301 "pg_catalog.pg_get_constraintdef(con.oid, true), " 2302 "contype, condeferrable, condeferred"); 2303 else 2304 appendPQExpBufferStr(&buf, 2305 "null AS constraintdef, null AS contype, " 2306 "false AS condeferrable, false AS condeferred"); 2307 if (pset.sversion >= 90400) 2308 appendPQExpBufferStr(&buf, ", i.indisreplident"); 2309 else 2310 appendPQExpBufferStr(&buf, ", false AS indisreplident"); 2311 if (pset.sversion >= 80000) 2312 appendPQExpBufferStr(&buf, ", c2.reltablespace"); 2313 appendPQExpBufferStr(&buf, 2314 "\nFROM pg_catalog.pg_class c, pg_catalog.pg_class c2, pg_catalog.pg_index i\n"); 2315 if (pset.sversion >= 90000) 2316 appendPQExpBufferStr(&buf, 2317 " LEFT JOIN pg_catalog.pg_constraint con ON (conrelid = i.indrelid AND conindid = i.indexrelid AND contype IN ('p','u','x'))\n"); 2318 appendPQExpBuffer(&buf, 2319 "WHERE c.oid = '%s' AND c.oid = i.indrelid AND i.indexrelid = c2.oid\n" 2320 "ORDER BY i.indisprimary DESC, i.indisunique DESC, c2.relname;", 2321 oid); 2322 result = PSQLexec(buf.data); 2323 if (!result) 2324 goto error_return; 2325 else 2326 tuples = PQntuples(result); 2327 2328 if (tuples > 0) 2329 { 2330 printTableAddFooter(&cont, _("Indexes:")); 2331 for (i = 0; i < tuples; i++) 2332 { 2333 /* untranslated index name */ 2334 printfPQExpBuffer(&buf, " \"%s\"", 2335 PQgetvalue(result, i, 0)); 2336 2337 /* If exclusion constraint, print the constraintdef */ 2338 if (strcmp(PQgetvalue(result, i, 7), "x") == 0) 2339 { 2340 appendPQExpBuffer(&buf, " %s", 2341 PQgetvalue(result, i, 6)); 2342 } 2343 else 2344 { 2345 const char *indexdef; 2346 const char *usingpos; 2347 2348 /* Label as primary key or unique (but not both) */ 2349 if (strcmp(PQgetvalue(result, i, 1), "t") == 0) 2350 appendPQExpBufferStr(&buf, " PRIMARY KEY,"); 2351 else if (strcmp(PQgetvalue(result, i, 2), "t") == 0) 2352 { 2353 if (strcmp(PQgetvalue(result, i, 7), "u") == 0) 2354 appendPQExpBufferStr(&buf, " UNIQUE CONSTRAINT,"); 2355 else 2356 appendPQExpBufferStr(&buf, " UNIQUE,"); 2357 } 2358 2359 /* Everything after "USING" is echoed verbatim */ 2360 indexdef = PQgetvalue(result, i, 5); 2361 usingpos = strstr(indexdef, " USING "); 2362 if (usingpos) 2363 indexdef = usingpos + 7; 2364 appendPQExpBuffer(&buf, " %s", indexdef); 2365 2366 /* Need these for deferrable PK/UNIQUE indexes */ 2367 if (strcmp(PQgetvalue(result, i, 8), "t") == 0) 2368 appendPQExpBufferStr(&buf, " DEFERRABLE"); 2369 2370 if (strcmp(PQgetvalue(result, i, 9), "t") == 0) 2371 appendPQExpBufferStr(&buf, " INITIALLY DEFERRED"); 2372 } 2373 2374 /* Add these for all cases */ 2375 if (strcmp(PQgetvalue(result, i, 3), "t") == 0) 2376 appendPQExpBufferStr(&buf, " CLUSTER"); 2377 2378 if (strcmp(PQgetvalue(result, i, 4), "t") != 0) 2379 appendPQExpBufferStr(&buf, " INVALID"); 2380 2381 if (strcmp(PQgetvalue(result, i, 10), "t") == 0) 2382 appendPQExpBuffer(&buf, " REPLICA IDENTITY"); 2383 2384 printTableAddFooter(&cont, buf.data); 2385 2386 /* Print tablespace of the index on the same line */ 2387 if (pset.sversion >= 80000) 2388 add_tablespace_footer(&cont, RELKIND_INDEX, 2389 atooid(PQgetvalue(result, i, 11)), 2390 false); 2391 } 2392 } 2393 PQclear(result); 2394 } 2395 2396 /* print table (and column) check constraints */ 2397 if (tableinfo.checks) 2398 { 2399 printfPQExpBuffer(&buf, 2400 "SELECT r.conname, " 2401 "pg_catalog.pg_get_constraintdef(r.oid, true)\n" 2402 "FROM pg_catalog.pg_constraint r\n" 2403 "WHERE r.conrelid = '%s' AND r.contype = 'c'\n" 2404 "ORDER BY 1;", 2405 oid); 2406 result = PSQLexec(buf.data); 2407 if (!result) 2408 goto error_return; 2409 else 2410 tuples = PQntuples(result); 2411 2412 if (tuples > 0) 2413 { 2414 printTableAddFooter(&cont, _("Check constraints:")); 2415 for (i = 0; i < tuples; i++) 2416 { 2417 /* untranslated constraint name and def */ 2418 printfPQExpBuffer(&buf, " \"%s\" %s", 2419 PQgetvalue(result, i, 0), 2420 PQgetvalue(result, i, 1)); 2421 2422 printTableAddFooter(&cont, buf.data); 2423 } 2424 } 2425 PQclear(result); 2426 } 2427 2428 /* 2429 * Print foreign-key constraints (there are none if no triggers, 2430 * except if the table is partitioned, in which case the triggers 2431 * appear in the partitions) 2432 */ 2433 if (tableinfo.hastriggers || 2434 tableinfo.relkind == RELKIND_PARTITIONED_TABLE) 2435 { 2436 if (pset.sversion >= 120000 && 2437 (tableinfo.ispartition || tableinfo.relkind == RELKIND_PARTITIONED_TABLE)) 2438 { 2439 /* 2440 * Put the constraints defined in this table first, followed 2441 * by the constraints defined in ancestor partitioned tables. 2442 */ 2443 printfPQExpBuffer(&buf, 2444 "SELECT conrelid = '%s'::pg_catalog.regclass AS sametable,\n" 2445 " conname,\n" 2446 " pg_catalog.pg_get_constraintdef(oid, true) AS condef,\n" 2447 " conrelid::pg_catalog.regclass AS ontable\n" 2448 " FROM pg_catalog.pg_constraint,\n" 2449 " pg_catalog.pg_partition_ancestors('%s')\n" 2450 " WHERE conrelid = relid AND contype = 'f' AND conparentid = 0\n" 2451 "ORDER BY sametable DESC, conname;", 2452 oid, oid); 2453 } 2454 else 2455 { 2456 printfPQExpBuffer(&buf, 2457 "SELECT true as sametable, conname,\n" 2458 " pg_catalog.pg_get_constraintdef(r.oid, true) as condef,\n" 2459 " conrelid::pg_catalog.regclass AS ontable\n" 2460 "FROM pg_catalog.pg_constraint r\n" 2461 "WHERE r.conrelid = '%s' AND r.contype = 'f'\n", 2462 oid); 2463 2464 if (pset.sversion >= 120000) 2465 appendPQExpBuffer(&buf, " AND conparentid = 0\n"); 2466 appendPQExpBuffer(&buf, "ORDER BY conname"); 2467 } 2468 2469 result = PSQLexec(buf.data); 2470 if (!result) 2471 goto error_return; 2472 else 2473 tuples = PQntuples(result); 2474 2475 if (tuples > 0) 2476 { 2477 int i_sametable = PQfnumber(result, "sametable"), 2478 i_conname = PQfnumber(result, "conname"), 2479 i_condef = PQfnumber(result, "condef"), 2480 i_ontable = PQfnumber(result, "ontable"); 2481 2482 printTableAddFooter(&cont, _("Foreign-key constraints:")); 2483 for (i = 0; i < tuples; i++) 2484 { 2485 /* 2486 * Print untranslated constraint name and definition. Use 2487 * a "TABLE tab" prefix when the constraint is defined in 2488 * a parent partitioned table. 2489 */ 2490 if (strcmp(PQgetvalue(result, i, i_sametable), "f") == 0) 2491 printfPQExpBuffer(&buf, " TABLE \"%s\" CONSTRAINT \"%s\" %s", 2492 PQgetvalue(result, i, i_ontable), 2493 PQgetvalue(result, i, i_conname), 2494 PQgetvalue(result, i, i_condef)); 2495 else 2496 printfPQExpBuffer(&buf, " \"%s\" %s", 2497 PQgetvalue(result, i, i_conname), 2498 PQgetvalue(result, i, i_condef)); 2499 2500 printTableAddFooter(&cont, buf.data); 2501 } 2502 } 2503 PQclear(result); 2504 } 2505 2506 /* print incoming foreign-key references */ 2507 if (tableinfo.hastriggers || 2508 tableinfo.relkind == RELKIND_PARTITIONED_TABLE) 2509 { 2510 if (pset.sversion >= 120000) 2511 { 2512 printfPQExpBuffer(&buf, 2513 "SELECT conname, conrelid::pg_catalog.regclass AS ontable,\n" 2514 " pg_catalog.pg_get_constraintdef(oid, true) AS condef\n" 2515 " FROM pg_catalog.pg_constraint c\n" 2516 " WHERE confrelid IN (SELECT pg_catalog.pg_partition_ancestors('%s')\n" 2517 " UNION ALL VALUES ('%s'::pg_catalog.regclass))\n" 2518 " AND contype = 'f' AND conparentid = 0\n" 2519 "ORDER BY conname;", 2520 oid, oid); 2521 } 2522 else 2523 { 2524 printfPQExpBuffer(&buf, 2525 "SELECT conname, conrelid::pg_catalog.regclass AS ontable,\n" 2526 " pg_catalog.pg_get_constraintdef(oid, true) AS condef\n" 2527 " FROM pg_catalog.pg_constraint\n" 2528 " WHERE confrelid = %s AND contype = 'f'\n" 2529 "ORDER BY conname;", 2530 oid); 2531 } 2532 2533 result = PSQLexec(buf.data); 2534 if (!result) 2535 goto error_return; 2536 else 2537 tuples = PQntuples(result); 2538 2539 if (tuples > 0) 2540 { 2541 int i_conname = PQfnumber(result, "conname"), 2542 i_ontable = PQfnumber(result, "ontable"), 2543 i_condef = PQfnumber(result, "condef"); 2544 2545 printTableAddFooter(&cont, _("Referenced by:")); 2546 for (i = 0; i < tuples; i++) 2547 { 2548 printfPQExpBuffer(&buf, " TABLE \"%s\" CONSTRAINT \"%s\" %s", 2549 PQgetvalue(result, i, i_ontable), 2550 PQgetvalue(result, i, i_conname), 2551 PQgetvalue(result, i, i_condef)); 2552 2553 printTableAddFooter(&cont, buf.data); 2554 } 2555 } 2556 PQclear(result); 2557 } 2558 2559 /* print any row-level policies */ 2560 if (pset.sversion >= 90500) 2561 { 2562 printfPQExpBuffer(&buf, "SELECT pol.polname,"); 2563 if (pset.sversion >= 100000) 2564 appendPQExpBuffer(&buf, 2565 " pol.polpermissive,\n"); 2566 else 2567 appendPQExpBuffer(&buf, 2568 " 't' as polpermissive,\n"); 2569 appendPQExpBuffer(&buf, 2570 " CASE WHEN pol.polroles = '{0}' THEN NULL ELSE pg_catalog.array_to_string(array(select rolname from pg_catalog.pg_roles where oid = any (pol.polroles) order by 1),',') END,\n" 2571 " pg_catalog.pg_get_expr(pol.polqual, pol.polrelid),\n" 2572 " pg_catalog.pg_get_expr(pol.polwithcheck, pol.polrelid),\n" 2573 " CASE pol.polcmd\n" 2574 " WHEN 'r' THEN 'SELECT'\n" 2575 " WHEN 'a' THEN 'INSERT'\n" 2576 " WHEN 'w' THEN 'UPDATE'\n" 2577 " WHEN 'd' THEN 'DELETE'\n" 2578 " END AS cmd\n" 2579 "FROM pg_catalog.pg_policy pol\n" 2580 "WHERE pol.polrelid = '%s' ORDER BY 1;", 2581 oid); 2582 2583 result = PSQLexec(buf.data); 2584 if (!result) 2585 goto error_return; 2586 else 2587 tuples = PQntuples(result); 2588 2589 /* 2590 * Handle cases where RLS is enabled and there are policies, or 2591 * there aren't policies, or RLS isn't enabled but there are 2592 * policies 2593 */ 2594 if (tableinfo.rowsecurity && !tableinfo.forcerowsecurity && tuples > 0) 2595 printTableAddFooter(&cont, _("Policies:")); 2596 2597 if (tableinfo.rowsecurity && tableinfo.forcerowsecurity && tuples > 0) 2598 printTableAddFooter(&cont, _("Policies (forced row security enabled):")); 2599 2600 if (tableinfo.rowsecurity && !tableinfo.forcerowsecurity && tuples == 0) 2601 printTableAddFooter(&cont, _("Policies (row security enabled): (none)")); 2602 2603 if (tableinfo.rowsecurity && tableinfo.forcerowsecurity && tuples == 0) 2604 printTableAddFooter(&cont, _("Policies (forced row security enabled): (none)")); 2605 2606 if (!tableinfo.rowsecurity && tuples > 0) 2607 printTableAddFooter(&cont, _("Policies (row security disabled):")); 2608 2609 /* Might be an empty set - that's ok */ 2610 for (i = 0; i < tuples; i++) 2611 { 2612 printfPQExpBuffer(&buf, " POLICY \"%s\"", 2613 PQgetvalue(result, i, 0)); 2614 2615 if (*(PQgetvalue(result, i, 1)) == 'f') 2616 appendPQExpBuffer(&buf, " AS RESTRICTIVE"); 2617 2618 if (!PQgetisnull(result, i, 5)) 2619 appendPQExpBuffer(&buf, " FOR %s", 2620 PQgetvalue(result, i, 5)); 2621 2622 if (!PQgetisnull(result, i, 2)) 2623 { 2624 appendPQExpBuffer(&buf, "\n TO %s", 2625 PQgetvalue(result, i, 2)); 2626 } 2627 2628 if (!PQgetisnull(result, i, 3)) 2629 appendPQExpBuffer(&buf, "\n USING (%s)", 2630 PQgetvalue(result, i, 3)); 2631 2632 if (!PQgetisnull(result, i, 4)) 2633 appendPQExpBuffer(&buf, "\n WITH CHECK (%s)", 2634 PQgetvalue(result, i, 4)); 2635 2636 printTableAddFooter(&cont, buf.data); 2637 2638 } 2639 PQclear(result); 2640 } 2641 2642 /* print any extended statistics */ 2643 if (pset.sversion >= 100000) 2644 { 2645 printfPQExpBuffer(&buf, 2646 "SELECT oid, " 2647 "stxrelid::pg_catalog.regclass, " 2648 "stxnamespace::pg_catalog.regnamespace AS nsp, " 2649 "stxname,\n" 2650 " (SELECT pg_catalog.string_agg(pg_catalog.quote_ident(attname),', ')\n" 2651 " FROM pg_catalog.unnest(stxkeys) s(attnum)\n" 2652 " JOIN pg_catalog.pg_attribute a ON (stxrelid = a.attrelid AND\n" 2653 " a.attnum = s.attnum AND NOT attisdropped)) AS columns,\n" 2654 " 'd' = any(stxkind) AS ndist_enabled,\n" 2655 " 'f' = any(stxkind) AS deps_enabled,\n" 2656 " 'm' = any(stxkind) AS mcv_enabled\n" 2657 "FROM pg_catalog.pg_statistic_ext stat " 2658 "WHERE stxrelid = '%s'\n" 2659 "ORDER BY 1;", 2660 oid); 2661 2662 result = PSQLexec(buf.data); 2663 if (!result) 2664 goto error_return; 2665 else 2666 tuples = PQntuples(result); 2667 2668 if (tuples > 0) 2669 { 2670 printTableAddFooter(&cont, _("Statistics objects:")); 2671 2672 for (i = 0; i < tuples; i++) 2673 { 2674 bool gotone = false; 2675 2676 printfPQExpBuffer(&buf, " "); 2677 2678 /* statistics object name (qualified with namespace) */ 2679 appendPQExpBuffer(&buf, "\"%s\".\"%s\" (", 2680 PQgetvalue(result, i, 2), 2681 PQgetvalue(result, i, 3)); 2682 2683 /* options */ 2684 if (strcmp(PQgetvalue(result, i, 5), "t") == 0) 2685 { 2686 appendPQExpBufferStr(&buf, "ndistinct"); 2687 gotone = true; 2688 } 2689 2690 if (strcmp(PQgetvalue(result, i, 6), "t") == 0) 2691 { 2692 appendPQExpBuffer(&buf, "%sdependencies", gotone ? ", " : ""); 2693 gotone = true; 2694 } 2695 2696 if (strcmp(PQgetvalue(result, i, 7), "t") == 0) 2697 { 2698 appendPQExpBuffer(&buf, "%smcv", gotone ? ", " : ""); 2699 } 2700 2701 appendPQExpBuffer(&buf, ") ON %s FROM %s", 2702 PQgetvalue(result, i, 4), 2703 PQgetvalue(result, i, 1)); 2704 2705 printTableAddFooter(&cont, buf.data); 2706 } 2707 } 2708 PQclear(result); 2709 } 2710 2711 /* print rules */ 2712 if (tableinfo.hasrules && tableinfo.relkind != RELKIND_MATVIEW) 2713 { 2714 if (pset.sversion >= 80300) 2715 { 2716 printfPQExpBuffer(&buf, 2717 "SELECT r.rulename, trim(trailing ';' from pg_catalog.pg_get_ruledef(r.oid, true)), " 2718 "ev_enabled\n" 2719 "FROM pg_catalog.pg_rewrite r\n" 2720 "WHERE r.ev_class = '%s' ORDER BY 1;", 2721 oid); 2722 } 2723 else 2724 { 2725 printfPQExpBuffer(&buf, 2726 "SELECT r.rulename, trim(trailing ';' from pg_catalog.pg_get_ruledef(r.oid, true)), " 2727 "'O' AS ev_enabled\n" 2728 "FROM pg_catalog.pg_rewrite r\n" 2729 "WHERE r.ev_class = '%s' ORDER BY 1;", 2730 oid); 2731 } 2732 result = PSQLexec(buf.data); 2733 if (!result) 2734 goto error_return; 2735 else 2736 tuples = PQntuples(result); 2737 2738 if (tuples > 0) 2739 { 2740 bool have_heading; 2741 int category; 2742 2743 for (category = 0; category < 4; category++) 2744 { 2745 have_heading = false; 2746 2747 for (i = 0; i < tuples; i++) 2748 { 2749 const char *ruledef; 2750 bool list_rule = false; 2751 2752 switch (category) 2753 { 2754 case 0: 2755 if (*PQgetvalue(result, i, 2) == 'O') 2756 list_rule = true; 2757 break; 2758 case 1: 2759 if (*PQgetvalue(result, i, 2) == 'D') 2760 list_rule = true; 2761 break; 2762 case 2: 2763 if (*PQgetvalue(result, i, 2) == 'A') 2764 list_rule = true; 2765 break; 2766 case 3: 2767 if (*PQgetvalue(result, i, 2) == 'R') 2768 list_rule = true; 2769 break; 2770 } 2771 if (!list_rule) 2772 continue; 2773 2774 if (!have_heading) 2775 { 2776 switch (category) 2777 { 2778 case 0: 2779 printfPQExpBuffer(&buf, _("Rules:")); 2780 break; 2781 case 1: 2782 printfPQExpBuffer(&buf, _("Disabled rules:")); 2783 break; 2784 case 2: 2785 printfPQExpBuffer(&buf, _("Rules firing always:")); 2786 break; 2787 case 3: 2788 printfPQExpBuffer(&buf, _("Rules firing on replica only:")); 2789 break; 2790 } 2791 printTableAddFooter(&cont, buf.data); 2792 have_heading = true; 2793 } 2794 2795 /* Everything after "CREATE RULE" is echoed verbatim */ 2796 ruledef = PQgetvalue(result, i, 1); 2797 ruledef += 12; 2798 printfPQExpBuffer(&buf, " %s", ruledef); 2799 printTableAddFooter(&cont, buf.data); 2800 } 2801 } 2802 } 2803 PQclear(result); 2804 } 2805 2806 /* print any publications */ 2807 if (pset.sversion >= 100000) 2808 { 2809 printfPQExpBuffer(&buf, 2810 "SELECT pubname\n" 2811 "FROM pg_catalog.pg_publication p\n" 2812 "JOIN pg_catalog.pg_publication_rel pr ON p.oid = pr.prpubid\n" 2813 "WHERE pr.prrelid = '%s'\n" 2814 "UNION ALL\n" 2815 "SELECT pubname\n" 2816 "FROM pg_catalog.pg_publication p\n" 2817 "WHERE p.puballtables AND pg_catalog.pg_relation_is_publishable('%s')\n" 2818 "ORDER BY 1;", 2819 oid, oid); 2820 2821 result = PSQLexec(buf.data); 2822 if (!result) 2823 goto error_return; 2824 else 2825 tuples = PQntuples(result); 2826 2827 if (tuples > 0) 2828 printTableAddFooter(&cont, _("Publications:")); 2829 2830 /* Might be an empty set - that's ok */ 2831 for (i = 0; i < tuples; i++) 2832 { 2833 printfPQExpBuffer(&buf, " \"%s\"", 2834 PQgetvalue(result, i, 0)); 2835 2836 printTableAddFooter(&cont, buf.data); 2837 } 2838 PQclear(result); 2839 } 2840 } 2841 2842 /* Get view_def if table is a view or materialized view */ 2843 if ((tableinfo.relkind == RELKIND_VIEW || 2844 tableinfo.relkind == RELKIND_MATVIEW) && verbose) 2845 { 2846 PGresult *result; 2847 2848 printfPQExpBuffer(&buf, 2849 "SELECT pg_catalog.pg_get_viewdef('%s'::pg_catalog.oid, true);", 2850 oid); 2851 result = PSQLexec(buf.data); 2852 if (!result) 2853 goto error_return; 2854 2855 if (PQntuples(result) > 0) 2856 view_def = pg_strdup(PQgetvalue(result, 0, 0)); 2857 2858 PQclear(result); 2859 } 2860 2861 if (view_def) 2862 { 2863 PGresult *result = NULL; 2864 2865 /* Footer information about a view */ 2866 printTableAddFooter(&cont, _("View definition:")); 2867 printTableAddFooter(&cont, view_def); 2868 2869 /* print rules */ 2870 if (tableinfo.hasrules) 2871 { 2872 printfPQExpBuffer(&buf, 2873 "SELECT r.rulename, trim(trailing ';' from pg_catalog.pg_get_ruledef(r.oid, true))\n" 2874 "FROM pg_catalog.pg_rewrite r\n" 2875 "WHERE r.ev_class = '%s' AND r.rulename != '_RETURN' ORDER BY 1;", 2876 oid); 2877 result = PSQLexec(buf.data); 2878 if (!result) 2879 goto error_return; 2880 2881 if (PQntuples(result) > 0) 2882 { 2883 printTableAddFooter(&cont, _("Rules:")); 2884 for (i = 0; i < PQntuples(result); i++) 2885 { 2886 const char *ruledef; 2887 2888 /* Everything after "CREATE RULE" is echoed verbatim */ 2889 ruledef = PQgetvalue(result, i, 1); 2890 ruledef += 12; 2891 2892 printfPQExpBuffer(&buf, " %s", ruledef); 2893 printTableAddFooter(&cont, buf.data); 2894 } 2895 } 2896 PQclear(result); 2897 } 2898 } 2899 2900 /* 2901 * Print triggers next, if any (but only user-defined triggers). This 2902 * could apply to either a table or a view. 2903 */ 2904 if (tableinfo.hastriggers) 2905 { 2906 PGresult *result; 2907 int tuples; 2908 2909 printfPQExpBuffer(&buf, 2910 "SELECT t.tgname, " 2911 "pg_catalog.pg_get_triggerdef(t.oid%s), " 2912 "t.tgenabled, %s\n" 2913 "FROM pg_catalog.pg_trigger t\n" 2914 "WHERE t.tgrelid = '%s' AND ", 2915 (pset.sversion >= 90000 ? ", true" : ""), 2916 (pset.sversion >= 90000 ? "t.tgisinternal" : 2917 pset.sversion >= 80300 ? 2918 "t.tgconstraint <> 0 AS tgisinternal" : 2919 "false AS tgisinternal"), oid); 2920 if (pset.sversion >= 110000) 2921 appendPQExpBuffer(&buf, "(NOT t.tgisinternal OR (t.tgisinternal AND t.tgenabled = 'D') \n" 2922 " OR EXISTS (SELECT 1 FROM pg_catalog.pg_depend WHERE objid = t.oid \n" 2923 " AND refclassid = 'pg_catalog.pg_trigger'::pg_catalog.regclass))"); 2924 else if (pset.sversion >= 90000) 2925 /* display/warn about disabled internal triggers */ 2926 appendPQExpBuffer(&buf, "(NOT t.tgisinternal OR (t.tgisinternal AND t.tgenabled = 'D'))"); 2927 else if (pset.sversion >= 80300) 2928 appendPQExpBufferStr(&buf, "(t.tgconstraint = 0 OR (t.tgconstraint <> 0 AND t.tgenabled = 'D'))"); 2929 else 2930 appendPQExpBufferStr(&buf, 2931 "(NOT tgisconstraint " 2932 " OR NOT EXISTS" 2933 " (SELECT 1 FROM pg_catalog.pg_depend d " 2934 " JOIN pg_catalog.pg_constraint c ON (d.refclassid = c.tableoid AND d.refobjid = c.oid) " 2935 " WHERE d.classid = t.tableoid AND d.objid = t.oid AND d.deptype = 'i' AND c.contype = 'f'))"); 2936 appendPQExpBufferStr(&buf, "\nORDER BY 1;"); 2937 2938 result = PSQLexec(buf.data); 2939 if (!result) 2940 goto error_return; 2941 else 2942 tuples = PQntuples(result); 2943 2944 if (tuples > 0) 2945 { 2946 bool have_heading; 2947 int category; 2948 2949 /* 2950 * split the output into 4 different categories. Enabled triggers, 2951 * disabled triggers and the two special ALWAYS and REPLICA 2952 * configurations. 2953 */ 2954 for (category = 0; category <= 4; category++) 2955 { 2956 have_heading = false; 2957 for (i = 0; i < tuples; i++) 2958 { 2959 bool list_trigger; 2960 const char *tgdef; 2961 const char *usingpos; 2962 const char *tgenabled; 2963 const char *tgisinternal; 2964 2965 /* 2966 * Check if this trigger falls into the current category 2967 */ 2968 tgenabled = PQgetvalue(result, i, 2); 2969 tgisinternal = PQgetvalue(result, i, 3); 2970 list_trigger = false; 2971 switch (category) 2972 { 2973 case 0: 2974 if (*tgenabled == 'O' || *tgenabled == 't') 2975 list_trigger = true; 2976 break; 2977 case 1: 2978 if ((*tgenabled == 'D' || *tgenabled == 'f') && 2979 *tgisinternal == 'f') 2980 list_trigger = true; 2981 break; 2982 case 2: 2983 if ((*tgenabled == 'D' || *tgenabled == 'f') && 2984 *tgisinternal == 't') 2985 list_trigger = true; 2986 break; 2987 case 3: 2988 if (*tgenabled == 'A') 2989 list_trigger = true; 2990 break; 2991 case 4: 2992 if (*tgenabled == 'R') 2993 list_trigger = true; 2994 break; 2995 } 2996 if (list_trigger == false) 2997 continue; 2998 2999 /* Print the category heading once */ 3000 if (have_heading == false) 3001 { 3002 switch (category) 3003 { 3004 case 0: 3005 printfPQExpBuffer(&buf, _("Triggers:")); 3006 break; 3007 case 1: 3008 if (pset.sversion >= 80300) 3009 printfPQExpBuffer(&buf, _("Disabled user triggers:")); 3010 else 3011 printfPQExpBuffer(&buf, _("Disabled triggers:")); 3012 break; 3013 case 2: 3014 printfPQExpBuffer(&buf, _("Disabled internal triggers:")); 3015 break; 3016 case 3: 3017 printfPQExpBuffer(&buf, _("Triggers firing always:")); 3018 break; 3019 case 4: 3020 printfPQExpBuffer(&buf, _("Triggers firing on replica only:")); 3021 break; 3022 3023 } 3024 printTableAddFooter(&cont, buf.data); 3025 have_heading = true; 3026 } 3027 3028 /* Everything after "TRIGGER" is echoed verbatim */ 3029 tgdef = PQgetvalue(result, i, 1); 3030 usingpos = strstr(tgdef, " TRIGGER "); 3031 if (usingpos) 3032 tgdef = usingpos + 9; 3033 3034 printfPQExpBuffer(&buf, " %s", tgdef); 3035 printTableAddFooter(&cont, buf.data); 3036 } 3037 } 3038 } 3039 PQclear(result); 3040 } 3041 3042 /* 3043 * Finish printing the footer information about a table. 3044 */ 3045 if (tableinfo.relkind == RELKIND_RELATION || 3046 tableinfo.relkind == RELKIND_MATVIEW || 3047 tableinfo.relkind == RELKIND_FOREIGN_TABLE || 3048 tableinfo.relkind == RELKIND_PARTITIONED_TABLE) 3049 { 3050 PGresult *result; 3051 int tuples; 3052 3053 /* print foreign server name */ 3054 if (tableinfo.relkind == RELKIND_FOREIGN_TABLE) 3055 { 3056 char *ftoptions; 3057 3058 /* Footer information about foreign table */ 3059 printfPQExpBuffer(&buf, 3060 "SELECT s.srvname,\n" 3061 " pg_catalog.array_to_string(ARRAY(\n" 3062 " SELECT pg_catalog.quote_ident(option_name)" 3063 " || ' ' || pg_catalog.quote_literal(option_value)\n" 3064 " FROM pg_catalog.pg_options_to_table(ftoptions)), ', ')\n" 3065 "FROM pg_catalog.pg_foreign_table f,\n" 3066 " pg_catalog.pg_foreign_server s\n" 3067 "WHERE f.ftrelid = '%s' AND s.oid = f.ftserver;", 3068 oid); 3069 result = PSQLexec(buf.data); 3070 if (!result) 3071 goto error_return; 3072 else if (PQntuples(result) != 1) 3073 { 3074 PQclear(result); 3075 goto error_return; 3076 } 3077 3078 /* Print server name */ 3079 printfPQExpBuffer(&buf, _("Server: %s"), 3080 PQgetvalue(result, 0, 0)); 3081 printTableAddFooter(&cont, buf.data); 3082 3083 /* Print per-table FDW options, if any */ 3084 ftoptions = PQgetvalue(result, 0, 1); 3085 if (ftoptions && ftoptions[0] != '\0') 3086 { 3087 printfPQExpBuffer(&buf, _("FDW options: (%s)"), ftoptions); 3088 printTableAddFooter(&cont, buf.data); 3089 } 3090 PQclear(result); 3091 } 3092 3093 /* print inherited tables (exclude, if parent is a partitioned table) */ 3094 printfPQExpBuffer(&buf, 3095 "SELECT c.oid::pg_catalog.regclass" 3096 " FROM pg_catalog.pg_class c, pg_catalog.pg_inherits i" 3097 " WHERE c.oid=i.inhparent AND i.inhrelid = '%s'" 3098 " AND c.relkind != " CppAsString2(RELKIND_PARTITIONED_TABLE) 3099 " ORDER BY inhseqno;", oid); 3100 3101 result = PSQLexec(buf.data); 3102 if (!result) 3103 goto error_return; 3104 else 3105 { 3106 const char *s = _("Inherits"); 3107 int sw = pg_wcswidth(s, strlen(s), pset.encoding); 3108 3109 tuples = PQntuples(result); 3110 3111 for (i = 0; i < tuples; i++) 3112 { 3113 if (i == 0) 3114 printfPQExpBuffer(&buf, "%s: %s", 3115 s, PQgetvalue(result, i, 0)); 3116 else 3117 printfPQExpBuffer(&buf, "%*s %s", 3118 sw, "", PQgetvalue(result, i, 0)); 3119 if (i < tuples - 1) 3120 appendPQExpBufferChar(&buf, ','); 3121 3122 printTableAddFooter(&cont, buf.data); 3123 } 3124 3125 PQclear(result); 3126 } 3127 3128 /* print child tables (with additional info if partitions) */ 3129 if (pset.sversion >= 100000) 3130 printfPQExpBuffer(&buf, 3131 "SELECT c.oid::pg_catalog.regclass," 3132 " pg_catalog.pg_get_expr(c.relpartbound, c.oid)," 3133 " c.relkind" 3134 " FROM pg_catalog.pg_class c, pg_catalog.pg_inherits i" 3135 " WHERE c.oid=i.inhrelid AND i.inhparent = '%s'" 3136 " ORDER BY pg_catalog.pg_get_expr(c.relpartbound, c.oid) = 'DEFAULT'," 3137 " c.oid::pg_catalog.regclass::pg_catalog.text;", oid); 3138 else if (pset.sversion >= 80300) 3139 printfPQExpBuffer(&buf, 3140 "SELECT c.oid::pg_catalog.regclass" 3141 " FROM pg_catalog.pg_class c, pg_catalog.pg_inherits i" 3142 " WHERE c.oid=i.inhrelid AND i.inhparent = '%s'" 3143 " ORDER BY c.oid::pg_catalog.regclass::pg_catalog.text;", oid); 3144 else 3145 printfPQExpBuffer(&buf, 3146 "SELECT c.oid::pg_catalog.regclass" 3147 " FROM pg_catalog.pg_class c, pg_catalog.pg_inherits i" 3148 " WHERE c.oid=i.inhrelid AND i.inhparent = '%s'" 3149 " ORDER BY c.relname;", oid); 3150 3151 result = PSQLexec(buf.data); 3152 if (!result) 3153 goto error_return; 3154 else 3155 tuples = PQntuples(result); 3156 3157 /* 3158 * For a partitioned table with no partitions, always print the number 3159 * of partitions as zero, even when verbose output is expected. 3160 * Otherwise, we will not print "Partitions" section for a partitioned 3161 * table without any partitions. 3162 */ 3163 if (tableinfo.relkind == RELKIND_PARTITIONED_TABLE && tuples == 0) 3164 { 3165 printfPQExpBuffer(&buf, _("Number of partitions: %d"), tuples); 3166 printTableAddFooter(&cont, buf.data); 3167 } 3168 else if (!verbose) 3169 { 3170 /* print the number of child tables, if any */ 3171 if (tuples > 0) 3172 { 3173 if (tableinfo.relkind != RELKIND_PARTITIONED_TABLE) 3174 printfPQExpBuffer(&buf, _("Number of child tables: %d (Use \\d+ to list them.)"), tuples); 3175 else 3176 printfPQExpBuffer(&buf, _("Number of partitions: %d (Use \\d+ to list them.)"), tuples); 3177 printTableAddFooter(&cont, buf.data); 3178 } 3179 } 3180 else 3181 { 3182 /* display the list of child tables */ 3183 const char *ct = (tableinfo.relkind != RELKIND_PARTITIONED_TABLE) ? 3184 _("Child tables") : _("Partitions"); 3185 int ctw = pg_wcswidth(ct, strlen(ct), pset.encoding); 3186 3187 for (i = 0; i < tuples; i++) 3188 { 3189 if (tableinfo.relkind != RELKIND_PARTITIONED_TABLE) 3190 { 3191 if (i == 0) 3192 printfPQExpBuffer(&buf, "%s: %s", 3193 ct, PQgetvalue(result, i, 0)); 3194 else 3195 printfPQExpBuffer(&buf, "%*s %s", 3196 ctw, "", PQgetvalue(result, i, 0)); 3197 } 3198 else 3199 { 3200 char *partitioned_note; 3201 3202 if (*PQgetvalue(result, i, 2) == RELKIND_PARTITIONED_TABLE) 3203 partitioned_note = ", PARTITIONED"; 3204 else 3205 partitioned_note = ""; 3206 3207 if (i == 0) 3208 printfPQExpBuffer(&buf, "%s: %s %s%s", 3209 ct, PQgetvalue(result, i, 0), PQgetvalue(result, i, 1), 3210 partitioned_note); 3211 else 3212 printfPQExpBuffer(&buf, "%*s %s %s%s", 3213 ctw, "", PQgetvalue(result, i, 0), PQgetvalue(result, i, 1), 3214 partitioned_note); 3215 } 3216 if (i < tuples - 1) 3217 appendPQExpBufferChar(&buf, ','); 3218 3219 printTableAddFooter(&cont, buf.data); 3220 } 3221 } 3222 PQclear(result); 3223 3224 /* Table type */ 3225 if (tableinfo.reloftype) 3226 { 3227 printfPQExpBuffer(&buf, _("Typed table of type: %s"), tableinfo.reloftype); 3228 printTableAddFooter(&cont, buf.data); 3229 } 3230 3231 if (verbose && 3232 (tableinfo.relkind == RELKIND_RELATION || 3233 tableinfo.relkind == RELKIND_MATVIEW) && 3234 3235 /* 3236 * No need to display default values; we already display a REPLICA 3237 * IDENTITY marker on indexes. 3238 */ 3239 tableinfo.relreplident != 'i' && 3240 ((strcmp(schemaname, "pg_catalog") != 0 && tableinfo.relreplident != 'd') || 3241 (strcmp(schemaname, "pg_catalog") == 0 && tableinfo.relreplident != 'n'))) 3242 { 3243 const char *s = _("Replica Identity"); 3244 3245 printfPQExpBuffer(&buf, "%s: %s", 3246 s, 3247 tableinfo.relreplident == 'f' ? "FULL" : 3248 tableinfo.relreplident == 'n' ? "NOTHING" : 3249 "???"); 3250 3251 printTableAddFooter(&cont, buf.data); 3252 } 3253 3254 /* OIDs, if verbose and not a materialized view */ 3255 if (verbose && tableinfo.relkind != RELKIND_MATVIEW && tableinfo.hasoids) 3256 printTableAddFooter(&cont, _("Has OIDs: yes")); 3257 3258 /* Tablespace info */ 3259 add_tablespace_footer(&cont, tableinfo.relkind, tableinfo.tablespace, 3260 true); 3261 3262 /* Access method info */ 3263 if (verbose && tableinfo.relam != NULL && !pset.hide_tableam) 3264 { 3265 printfPQExpBuffer(&buf, _("Access method: %s"), tableinfo.relam); 3266 printTableAddFooter(&cont, buf.data); 3267 } 3268 } 3269 3270 /* reloptions, if verbose */ 3271 if (verbose && 3272 tableinfo.reloptions && tableinfo.reloptions[0] != '\0') 3273 { 3274 const char *t = _("Options"); 3275 3276 printfPQExpBuffer(&buf, "%s: %s", t, tableinfo.reloptions); 3277 printTableAddFooter(&cont, buf.data); 3278 } 3279 3280 printTable(&cont, pset.queryFout, false, pset.logfile); 3281 3282 retval = true; 3283 3284 error_return: 3285 3286 /* clean up */ 3287 if (printTableInitialized) 3288 printTableCleanup(&cont); 3289 termPQExpBuffer(&buf); 3290 termPQExpBuffer(&title); 3291 termPQExpBuffer(&tmpbuf); 3292 3293 if (view_def) 3294 free(view_def); 3295 3296 if (res) 3297 PQclear(res); 3298 3299 return retval; 3300 } 3301 3302 /* 3303 * Add a tablespace description to a footer. If 'newline' is true, it is added 3304 * in a new line; otherwise it's appended to the current value of the last 3305 * footer. 3306 */ 3307 static void 3308 add_tablespace_footer(printTableContent *const cont, char relkind, 3309 Oid tablespace, const bool newline) 3310 { 3311 /* relkinds for which we support tablespaces */ 3312 if (relkind == RELKIND_RELATION || 3313 relkind == RELKIND_MATVIEW || 3314 relkind == RELKIND_INDEX || 3315 relkind == RELKIND_PARTITIONED_TABLE || 3316 relkind == RELKIND_PARTITIONED_INDEX) 3317 { 3318 /* 3319 * We ignore the database default tablespace so that users not using 3320 * tablespaces don't need to know about them. This case also covers 3321 * pre-8.0 servers, for which tablespace will always be 0. 3322 */ 3323 if (tablespace != 0) 3324 { 3325 PGresult *result = NULL; 3326 PQExpBufferData buf; 3327 3328 initPQExpBuffer(&buf); 3329 printfPQExpBuffer(&buf, 3330 "SELECT spcname FROM pg_catalog.pg_tablespace\n" 3331 "WHERE oid = '%u';", tablespace); 3332 result = PSQLexec(buf.data); 3333 if (!result) 3334 { 3335 termPQExpBuffer(&buf); 3336 return; 3337 } 3338 /* Should always be the case, but.... */ 3339 if (PQntuples(result) > 0) 3340 { 3341 if (newline) 3342 { 3343 /* Add the tablespace as a new footer */ 3344 printfPQExpBuffer(&buf, _("Tablespace: \"%s\""), 3345 PQgetvalue(result, 0, 0)); 3346 printTableAddFooter(cont, buf.data); 3347 } 3348 else 3349 { 3350 /* Append the tablespace to the latest footer */ 3351 printfPQExpBuffer(&buf, "%s", cont->footer->data); 3352 3353 /*------- 3354 translator: before this string there's an index description like 3355 '"foo_pkey" PRIMARY KEY, btree (a)' */ 3356 appendPQExpBuffer(&buf, _(", tablespace \"%s\""), 3357 PQgetvalue(result, 0, 0)); 3358 printTableSetFooter(cont, buf.data); 3359 } 3360 } 3361 PQclear(result); 3362 termPQExpBuffer(&buf); 3363 } 3364 } 3365 } 3366 3367 /* 3368 * \du or \dg 3369 * 3370 * Describes roles. Any schema portion of the pattern is ignored. 3371 */ 3372 bool 3373 describeRoles(const char *pattern, bool verbose, bool showSystem) 3374 { 3375 PQExpBufferData buf; 3376 PGresult *res; 3377 printTableContent cont; 3378 printTableOpt myopt = pset.popt.topt; 3379 int ncols = 3; 3380 int nrows = 0; 3381 int i; 3382 int conns; 3383 const char align = 'l'; 3384 char **attr; 3385 3386 myopt.default_footer = false; 3387 3388 initPQExpBuffer(&buf); 3389 3390 if (pset.sversion >= 80100) 3391 { 3392 printfPQExpBuffer(&buf, 3393 "SELECT r.rolname, r.rolsuper, r.rolinherit,\n" 3394 " r.rolcreaterole, r.rolcreatedb, r.rolcanlogin,\n" 3395 " r.rolconnlimit, r.rolvaliduntil,\n" 3396 " ARRAY(SELECT b.rolname\n" 3397 " FROM pg_catalog.pg_auth_members m\n" 3398 " JOIN pg_catalog.pg_roles b ON (m.roleid = b.oid)\n" 3399 " WHERE m.member = r.oid) as memberof"); 3400 3401 if (verbose && pset.sversion >= 80200) 3402 { 3403 appendPQExpBufferStr(&buf, "\n, pg_catalog.shobj_description(r.oid, 'pg_authid') AS description"); 3404 ncols++; 3405 } 3406 if (pset.sversion >= 90100) 3407 { 3408 appendPQExpBufferStr(&buf, "\n, r.rolreplication"); 3409 } 3410 3411 if (pset.sversion >= 90500) 3412 { 3413 appendPQExpBufferStr(&buf, "\n, r.rolbypassrls"); 3414 } 3415 3416 appendPQExpBufferStr(&buf, "\nFROM pg_catalog.pg_roles r\n"); 3417 3418 if (!showSystem && !pattern) 3419 appendPQExpBufferStr(&buf, "WHERE r.rolname !~ '^pg_'\n"); 3420 3421 processSQLNamePattern(pset.db, &buf, pattern, false, false, 3422 NULL, "r.rolname", NULL, NULL); 3423 } 3424 else 3425 { 3426 printfPQExpBuffer(&buf, 3427 "SELECT u.usename AS rolname,\n" 3428 " u.usesuper AS rolsuper,\n" 3429 " true AS rolinherit, false AS rolcreaterole,\n" 3430 " u.usecreatedb AS rolcreatedb, true AS rolcanlogin,\n" 3431 " -1 AS rolconnlimit," 3432 " u.valuntil as rolvaliduntil,\n" 3433 " ARRAY(SELECT g.groname FROM pg_catalog.pg_group g WHERE u.usesysid = ANY(g.grolist)) as memberof" 3434 "\nFROM pg_catalog.pg_user u\n"); 3435 3436 processSQLNamePattern(pset.db, &buf, pattern, false, false, 3437 NULL, "u.usename", NULL, NULL); 3438 } 3439 3440 appendPQExpBufferStr(&buf, "ORDER BY 1;"); 3441 3442 res = PSQLexec(buf.data); 3443 if (!res) 3444 return false; 3445 3446 nrows = PQntuples(res); 3447 attr = pg_malloc0((nrows + 1) * sizeof(*attr)); 3448 3449 printTableInit(&cont, &myopt, _("List of roles"), ncols, nrows); 3450 3451 printTableAddHeader(&cont, gettext_noop("Role name"), true, align); 3452 printTableAddHeader(&cont, gettext_noop("Attributes"), true, align); 3453 printTableAddHeader(&cont, gettext_noop("Member of"), true, align); 3454 3455 if (verbose && pset.sversion >= 80200) 3456 printTableAddHeader(&cont, gettext_noop("Description"), true, align); 3457 3458 for (i = 0; i < nrows; i++) 3459 { 3460 printTableAddCell(&cont, PQgetvalue(res, i, 0), false, false); 3461 3462 resetPQExpBuffer(&buf); 3463 if (strcmp(PQgetvalue(res, i, 1), "t") == 0) 3464 add_role_attribute(&buf, _("Superuser")); 3465 3466 if (strcmp(PQgetvalue(res, i, 2), "t") != 0) 3467 add_role_attribute(&buf, _("No inheritance")); 3468 3469 if (strcmp(PQgetvalue(res, i, 3), "t") == 0) 3470 add_role_attribute(&buf, _("Create role")); 3471 3472 if (strcmp(PQgetvalue(res, i, 4), "t") == 0) 3473 add_role_attribute(&buf, _("Create DB")); 3474 3475 if (strcmp(PQgetvalue(res, i, 5), "t") != 0) 3476 add_role_attribute(&buf, _("Cannot login")); 3477 3478 if (pset.sversion >= 90100) 3479 if (strcmp(PQgetvalue(res, i, (verbose ? 10 : 9)), "t") == 0) 3480 add_role_attribute(&buf, _("Replication")); 3481 3482 if (pset.sversion >= 90500) 3483 if (strcmp(PQgetvalue(res, i, (verbose ? 11 : 10)), "t") == 0) 3484 add_role_attribute(&buf, _("Bypass RLS")); 3485 3486 conns = atoi(PQgetvalue(res, i, 6)); 3487 if (conns >= 0) 3488 { 3489 if (buf.len > 0) 3490 appendPQExpBufferChar(&buf, '\n'); 3491 3492 if (conns == 0) 3493 appendPQExpBufferStr(&buf, _("No connections")); 3494 else 3495 appendPQExpBuffer(&buf, ngettext("%d connection", 3496 "%d connections", 3497 conns), 3498 conns); 3499 } 3500 3501 if (strcmp(PQgetvalue(res, i, 7), "") != 0) 3502 { 3503 if (buf.len > 0) 3504 appendPQExpBufferChar(&buf, '\n'); 3505 appendPQExpBufferStr(&buf, _("Password valid until ")); 3506 appendPQExpBufferStr(&buf, PQgetvalue(res, i, 7)); 3507 } 3508 3509 attr[i] = pg_strdup(buf.data); 3510 3511 printTableAddCell(&cont, attr[i], false, false); 3512 3513 printTableAddCell(&cont, PQgetvalue(res, i, 8), false, false); 3514 3515 if (verbose && pset.sversion >= 80200) 3516 printTableAddCell(&cont, PQgetvalue(res, i, 9), false, false); 3517 } 3518 termPQExpBuffer(&buf); 3519 3520 printTable(&cont, pset.queryFout, false, pset.logfile); 3521 printTableCleanup(&cont); 3522 3523 for (i = 0; i < nrows; i++) 3524 free(attr[i]); 3525 free(attr); 3526 3527 PQclear(res); 3528 return true; 3529 } 3530 3531 static void 3532 add_role_attribute(PQExpBuffer buf, const char *const str) 3533 { 3534 if (buf->len > 0) 3535 appendPQExpBufferStr(buf, ", "); 3536 3537 appendPQExpBufferStr(buf, str); 3538 } 3539 3540 /* 3541 * \drds 3542 */ 3543 bool 3544 listDbRoleSettings(const char *pattern, const char *pattern2) 3545 { 3546 PQExpBufferData buf; 3547 PGresult *res; 3548 printQueryOpt myopt = pset.popt; 3549 bool havewhere; 3550 3551 if (pset.sversion < 90000) 3552 { 3553 char sverbuf[32]; 3554 3555 pg_log_error("The server (version %s) does not support per-database role settings.", 3556 formatPGVersionNumber(pset.sversion, false, 3557 sverbuf, sizeof(sverbuf))); 3558 return true; 3559 } 3560 3561 initPQExpBuffer(&buf); 3562 3563 printfPQExpBuffer(&buf, "SELECT rolname AS \"%s\", datname AS \"%s\",\n" 3564 "pg_catalog.array_to_string(setconfig, E'\\n') AS \"%s\"\n" 3565 "FROM pg_catalog.pg_db_role_setting s\n" 3566 "LEFT JOIN pg_catalog.pg_database d ON d.oid = setdatabase\n" 3567 "LEFT JOIN pg_catalog.pg_roles r ON r.oid = setrole\n", 3568 gettext_noop("Role"), 3569 gettext_noop("Database"), 3570 gettext_noop("Settings")); 3571 havewhere = processSQLNamePattern(pset.db, &buf, pattern, false, false, 3572 NULL, "r.rolname", NULL, NULL); 3573 processSQLNamePattern(pset.db, &buf, pattern2, havewhere, false, 3574 NULL, "d.datname", NULL, NULL); 3575 appendPQExpBufferStr(&buf, "ORDER BY 1, 2;"); 3576 3577 res = PSQLexec(buf.data); 3578 termPQExpBuffer(&buf); 3579 if (!res) 3580 return false; 3581 3582 /* 3583 * Most functions in this file are content to print an empty table when 3584 * there are no matching objects. We intentionally deviate from that 3585 * here, but only in !quiet mode, because of the possibility that the user 3586 * is confused about what the two pattern arguments mean. 3587 */ 3588 if (PQntuples(res) == 0 && !pset.quiet) 3589 { 3590 if (pattern && pattern2) 3591 pg_log_error("Did not find any settings for role \"%s\" and database \"%s\".", 3592 pattern, pattern2); 3593 else if (pattern) 3594 pg_log_error("Did not find any settings for role \"%s\".", 3595 pattern); 3596 else 3597 pg_log_error("Did not find any settings."); 3598 } 3599 else 3600 { 3601 myopt.nullPrint = NULL; 3602 myopt.title = _("List of settings"); 3603 myopt.translate_header = true; 3604 3605 printQuery(res, &myopt, pset.queryFout, false, pset.logfile); 3606 } 3607 3608 PQclear(res); 3609 return true; 3610 } 3611 3612 3613 /* 3614 * listTables() 3615 * 3616 * handler for \dt, \di, etc. 3617 * 3618 * tabtypes is an array of characters, specifying what info is desired: 3619 * t - tables 3620 * i - indexes 3621 * v - views 3622 * m - materialized views 3623 * s - sequences 3624 * E - foreign table (Note: different from 'f', the relkind value) 3625 * (any order of the above is fine) 3626 */ 3627 bool 3628 listTables(const char *tabtypes, const char *pattern, bool verbose, bool showSystem) 3629 { 3630 bool showTables = strchr(tabtypes, 't') != NULL; 3631 bool showIndexes = strchr(tabtypes, 'i') != NULL; 3632 bool showViews = strchr(tabtypes, 'v') != NULL; 3633 bool showMatViews = strchr(tabtypes, 'm') != NULL; 3634 bool showSeq = strchr(tabtypes, 's') != NULL; 3635 bool showForeign = strchr(tabtypes, 'E') != NULL; 3636 3637 PQExpBufferData buf; 3638 PGresult *res; 3639 printQueryOpt myopt = pset.popt; 3640 static const bool translate_columns[] = {false, false, true, false, false, false, false}; 3641 3642 /* If tabtypes is empty, we default to \dtvmsE (but see also command.c) */ 3643 if (!(showTables || showIndexes || showViews || showMatViews || showSeq || showForeign)) 3644 showTables = showViews = showMatViews = showSeq = showForeign = true; 3645 3646 initPQExpBuffer(&buf); 3647 3648 /* 3649 * Note: as of Pg 8.2, we no longer use relkind 's' (special), but we keep 3650 * it here for backwards compatibility. 3651 */ 3652 printfPQExpBuffer(&buf, 3653 "SELECT n.nspname as \"%s\",\n" 3654 " c.relname as \"%s\",\n" 3655 " CASE c.relkind" 3656 " WHEN " CppAsString2(RELKIND_RELATION) " THEN '%s'" 3657 " WHEN " CppAsString2(RELKIND_VIEW) " THEN '%s'" 3658 " WHEN " CppAsString2(RELKIND_MATVIEW) " THEN '%s'" 3659 " WHEN " CppAsString2(RELKIND_INDEX) " THEN '%s'" 3660 " WHEN " CppAsString2(RELKIND_SEQUENCE) " THEN '%s'" 3661 " WHEN 's' THEN '%s'" 3662 " WHEN " CppAsString2(RELKIND_FOREIGN_TABLE) " THEN '%s'" 3663 " WHEN " CppAsString2(RELKIND_PARTITIONED_TABLE) " THEN '%s'" 3664 " WHEN " CppAsString2(RELKIND_PARTITIONED_INDEX) " THEN '%s'" 3665 " END as \"%s\",\n" 3666 " pg_catalog.pg_get_userbyid(c.relowner) as \"%s\"", 3667 gettext_noop("Schema"), 3668 gettext_noop("Name"), 3669 gettext_noop("table"), 3670 gettext_noop("view"), 3671 gettext_noop("materialized view"), 3672 gettext_noop("index"), 3673 gettext_noop("sequence"), 3674 gettext_noop("special"), 3675 gettext_noop("foreign table"), 3676 gettext_noop("partitioned table"), 3677 gettext_noop("partitioned index"), 3678 gettext_noop("Type"), 3679 gettext_noop("Owner")); 3680 3681 if (showIndexes) 3682 appendPQExpBuffer(&buf, 3683 ",\n c2.relname as \"%s\"", 3684 gettext_noop("Table")); 3685 3686 if (verbose) 3687 { 3688 /* 3689 * As of PostgreSQL 9.0, use pg_table_size() to show a more accurate 3690 * size of a table, including FSM, VM and TOAST tables. 3691 */ 3692 if (pset.sversion >= 90000) 3693 appendPQExpBuffer(&buf, 3694 ",\n pg_catalog.pg_size_pretty(pg_catalog.pg_table_size(c.oid)) as \"%s\"", 3695 gettext_noop("Size")); 3696 else if (pset.sversion >= 80100) 3697 appendPQExpBuffer(&buf, 3698 ",\n pg_catalog.pg_size_pretty(pg_catalog.pg_relation_size(c.oid)) as \"%s\"", 3699 gettext_noop("Size")); 3700 3701 appendPQExpBuffer(&buf, 3702 ",\n pg_catalog.obj_description(c.oid, 'pg_class') as \"%s\"", 3703 gettext_noop("Description")); 3704 } 3705 3706 appendPQExpBufferStr(&buf, 3707 "\nFROM pg_catalog.pg_class c" 3708 "\n LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace"); 3709 if (showIndexes) 3710 appendPQExpBufferStr(&buf, 3711 "\n LEFT JOIN pg_catalog.pg_index i ON i.indexrelid = c.oid" 3712 "\n LEFT JOIN pg_catalog.pg_class c2 ON i.indrelid = c2.oid"); 3713 3714 appendPQExpBufferStr(&buf, "\nWHERE c.relkind IN ("); 3715 if (showTables) 3716 appendPQExpBufferStr(&buf, CppAsString2(RELKIND_RELATION) "," 3717 CppAsString2(RELKIND_PARTITIONED_TABLE) ","); 3718 if (showViews) 3719 appendPQExpBufferStr(&buf, CppAsString2(RELKIND_VIEW) ","); 3720 if (showMatViews) 3721 appendPQExpBufferStr(&buf, CppAsString2(RELKIND_MATVIEW) ","); 3722 if (showIndexes) 3723 appendPQExpBufferStr(&buf, CppAsString2(RELKIND_INDEX) "," 3724 CppAsString2(RELKIND_PARTITIONED_INDEX) ","); 3725 if (showSeq) 3726 appendPQExpBufferStr(&buf, CppAsString2(RELKIND_SEQUENCE) ","); 3727 if (showSystem || pattern) 3728 appendPQExpBufferStr(&buf, "'s',"); /* was RELKIND_SPECIAL */ 3729 if (showForeign) 3730 appendPQExpBufferStr(&buf, CppAsString2(RELKIND_FOREIGN_TABLE) ","); 3731 3732 appendPQExpBufferStr(&buf, "''"); /* dummy */ 3733 appendPQExpBufferStr(&buf, ")\n"); 3734 3735 if (!showSystem && !pattern) 3736 appendPQExpBufferStr(&buf, " AND n.nspname <> 'pg_catalog'\n" 3737 " AND n.nspname <> 'information_schema'\n"); 3738 3739 /* 3740 * TOAST objects are suppressed unconditionally. Since we don't provide 3741 * any way to select RELKIND_TOASTVALUE above, we would never show toast 3742 * tables in any case; it seems a bit confusing to allow their indexes to 3743 * be shown. Use plain \d if you really need to look at a TOAST 3744 * table/index. 3745 */ 3746 appendPQExpBufferStr(&buf, " AND n.nspname !~ '^pg_toast'\n"); 3747 3748 processSQLNamePattern(pset.db, &buf, pattern, true, false, 3749 "n.nspname", "c.relname", NULL, 3750 "pg_catalog.pg_table_is_visible(c.oid)"); 3751 3752 appendPQExpBufferStr(&buf, "ORDER BY 1,2;"); 3753 3754 res = PSQLexec(buf.data); 3755 termPQExpBuffer(&buf); 3756 if (!res) 3757 return false; 3758 3759 /* 3760 * Most functions in this file are content to print an empty table when 3761 * there are no matching objects. We intentionally deviate from that 3762 * here, but only in !quiet mode, for historical reasons. 3763 */ 3764 if (PQntuples(res) == 0 && !pset.quiet) 3765 { 3766 if (pattern) 3767 pg_log_error("Did not find any relation named \"%s\".", 3768 pattern); 3769 else 3770 pg_log_error("Did not find any relations."); 3771 } 3772 else 3773 { 3774 myopt.nullPrint = NULL; 3775 myopt.title = _("List of relations"); 3776 myopt.translate_header = true; 3777 myopt.translate_columns = translate_columns; 3778 myopt.n_translate_columns = lengthof(translate_columns); 3779 3780 printQuery(res, &myopt, pset.queryFout, false, pset.logfile); 3781 } 3782 3783 PQclear(res); 3784 return true; 3785 } 3786 3787 /* 3788 * \dP 3789 * Takes an optional regexp to select particular relations 3790 * 3791 * As with \d, you can specify the kinds of relations you want: 3792 * 3793 * t for tables 3794 * i for indexes 3795 * 3796 * And there's additional flags: 3797 * 3798 * n to list non-leaf partitioned tables 3799 * 3800 * and you can mix and match these in any order. 3801 */ 3802 bool 3803 listPartitionedTables(const char *reltypes, const char *pattern, bool verbose) 3804 { 3805 bool showTables = strchr(reltypes, 't') != NULL; 3806 bool showIndexes = strchr(reltypes, 'i') != NULL; 3807 bool showNested = strchr(reltypes, 'n') != NULL; 3808 PQExpBufferData buf; 3809 PQExpBufferData title; 3810 PGresult *res; 3811 printQueryOpt myopt = pset.popt; 3812 bool translate_columns[] = {false, false, false, false, false, false, false, false, false}; 3813 const char *tabletitle; 3814 bool mixed_output = false; 3815 3816 /* 3817 * Note: Declarative table partitioning is only supported as of Pg 10.0. 3818 */ 3819 if (pset.sversion < 100000) 3820 { 3821 char sverbuf[32]; 3822 3823 pg_log_error("The server (version %s) does not support declarative table partitioning.", 3824 formatPGVersionNumber(pset.sversion, false, 3825 sverbuf, sizeof(sverbuf))); 3826 return true; 3827 } 3828 3829 /* If no relation kind was selected, show them all */ 3830 if (!showTables && !showIndexes) 3831 showTables = showIndexes = true; 3832 3833 if (showIndexes && !showTables) 3834 tabletitle = _("List of partitioned indexes"); /* \dPi */ 3835 else if (showTables && !showIndexes) 3836 tabletitle = _("List of partitioned tables"); /* \dPt */ 3837 else 3838 { 3839 /* show all kinds */ 3840 tabletitle = _("List of partitioned relations"); 3841 mixed_output = true; 3842 } 3843 3844 initPQExpBuffer(&buf); 3845 3846 printfPQExpBuffer(&buf, 3847 "SELECT n.nspname as \"%s\",\n" 3848 " c.relname as \"%s\",\n" 3849 " pg_catalog.pg_get_userbyid(c.relowner) as \"%s\"", 3850 gettext_noop("Schema"), 3851 gettext_noop("Name"), 3852 gettext_noop("Owner")); 3853 3854 if (mixed_output) 3855 { 3856 appendPQExpBuffer(&buf, 3857 ",\n CASE c.relkind" 3858 " WHEN " CppAsString2(RELKIND_PARTITIONED_TABLE) " THEN '%s'" 3859 " WHEN " CppAsString2(RELKIND_PARTITIONED_INDEX) " THEN '%s'" 3860 " END as \"%s\"", 3861 gettext_noop("partitioned table"), 3862 gettext_noop("partitioned index"), 3863 gettext_noop("Type")); 3864 3865 translate_columns[3] = true; 3866 } 3867 3868 if (showNested || pattern) 3869 appendPQExpBuffer(&buf, 3870 ",\n inh.inhparent::pg_catalog.regclass as \"%s\"", 3871 gettext_noop("Parent name")); 3872 3873 if (showIndexes) 3874 appendPQExpBuffer(&buf, 3875 ",\n c2.oid::pg_catalog.regclass as \"%s\"", 3876 gettext_noop("Table")); 3877 3878 if (verbose) 3879 { 3880 if (showNested) 3881 { 3882 appendPQExpBuffer(&buf, 3883 ",\n s.dps as \"%s\"", 3884 gettext_noop("Leaf partition size")); 3885 appendPQExpBuffer(&buf, 3886 ",\n s.tps as \"%s\"", 3887 gettext_noop("Total size")); 3888 } 3889 else 3890 /* Sizes of all partitions are considered in this case. */ 3891 appendPQExpBuffer(&buf, 3892 ",\n s.tps as \"%s\"", 3893 gettext_noop("Total size")); 3894 3895 appendPQExpBuffer(&buf, 3896 ",\n pg_catalog.obj_description(c.oid, 'pg_class') as \"%s\"", 3897 gettext_noop("Description")); 3898 } 3899 3900 appendPQExpBufferStr(&buf, 3901 "\nFROM pg_catalog.pg_class c" 3902 "\n LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace"); 3903 3904 if (showIndexes) 3905 appendPQExpBufferStr(&buf, 3906 "\n LEFT JOIN pg_catalog.pg_index i ON i.indexrelid = c.oid" 3907 "\n LEFT JOIN pg_catalog.pg_class c2 ON i.indrelid = c2.oid"); 3908 3909 if (showNested || pattern) 3910 appendPQExpBufferStr(&buf, 3911 "\n LEFT JOIN pg_catalog.pg_inherits inh ON c.oid = inh.inhrelid"); 3912 3913 if (verbose) 3914 { 3915 if (pset.sversion < 120000) 3916 { 3917 appendPQExpBuffer(&buf, 3918 ",\n LATERAL (WITH RECURSIVE d\n" 3919 " AS (SELECT inhrelid AS oid, 1 AS level\n" 3920 " FROM pg_catalog.pg_inherits\n" 3921 " WHERE inhparent = c.oid\n" 3922 " UNION ALL\n" 3923 " SELECT inhrelid, level + 1\n" 3924 " FROM pg_catalog.pg_inherits i\n" 3925 " JOIN d ON i.inhparent = d.oid)\n" 3926 " SELECT pg_catalog.pg_size_pretty(sum(pg_catalog.pg_table_size(" 3927 "d.oid))) AS tps,\n" 3928 " pg_catalog.pg_size_pretty(sum(" 3929 "\n CASE WHEN d.level = 1" 3930 " THEN pg_catalog.pg_table_size(d.oid) ELSE 0 END)) AS dps\n" 3931 " FROM d) s"); 3932 } 3933 else 3934 { 3935 /* PostgreSQL 12 has pg_partition_tree function */ 3936 appendPQExpBuffer(&buf, 3937 ",\n LATERAL (SELECT pg_catalog.pg_size_pretty(sum(" 3938 "\n CASE WHEN ppt.isleaf AND ppt.level = 1" 3939 "\n THEN pg_catalog.pg_table_size(ppt.relid)" 3940 " ELSE 0 END)) AS dps" 3941 ",\n pg_catalog.pg_size_pretty(sum(" 3942 "pg_catalog.pg_table_size(ppt.relid))) AS tps" 3943 "\n FROM pg_catalog.pg_partition_tree(c.oid) ppt) s"); 3944 } 3945 } 3946 3947 appendPQExpBufferStr(&buf, "\nWHERE c.relkind IN ("); 3948 if (showTables) 3949 appendPQExpBufferStr(&buf, CppAsString2(RELKIND_PARTITIONED_TABLE) ","); 3950 if (showIndexes) 3951 appendPQExpBufferStr(&buf, CppAsString2(RELKIND_PARTITIONED_INDEX) ","); 3952 appendPQExpBufferStr(&buf, "''"); /* dummy */ 3953 appendPQExpBufferStr(&buf, ")\n"); 3954 3955 appendPQExpBufferStr(&buf, !showNested && !pattern ? 3956 " AND NOT c.relispartition\n" : ""); 3957 3958 if (!pattern) 3959 appendPQExpBufferStr(&buf, " AND n.nspname <> 'pg_catalog'\n" 3960 " AND n.nspname <> 'information_schema'\n"); 3961 3962 /* 3963 * TOAST objects are suppressed unconditionally. Since we don't provide 3964 * any way to select RELKIND_TOASTVALUE above, we would never show toast 3965 * tables in any case; it seems a bit confusing to allow their indexes to 3966 * be shown. Use plain \d if you really need to look at a TOAST 3967 * table/index. 3968 */ 3969 appendPQExpBufferStr(&buf, " AND n.nspname !~ '^pg_toast'\n"); 3970 3971 processSQLNamePattern(pset.db, &buf, pattern, true, false, 3972 "n.nspname", "c.relname", NULL, 3973 "pg_catalog.pg_table_is_visible(c.oid)"); 3974 3975 appendPQExpBuffer(&buf, "ORDER BY \"Schema\", %s%s\"Name\";", 3976 mixed_output ? "\"Type\" DESC, " : "", 3977 showNested || pattern ? "\"Parent name\" NULLS FIRST, " : ""); 3978 3979 res = PSQLexec(buf.data); 3980 termPQExpBuffer(&buf); 3981 if (!res) 3982 return false; 3983 3984 initPQExpBuffer(&title); 3985 appendPQExpBuffer(&title, "%s", tabletitle); 3986 3987 myopt.nullPrint = NULL; 3988 myopt.title = title.data; 3989 myopt.translate_header = true; 3990 myopt.translate_columns = translate_columns; 3991 myopt.n_translate_columns = lengthof(translate_columns); 3992 3993 printQuery(res, &myopt, pset.queryFout, false, pset.logfile); 3994 3995 termPQExpBuffer(&title); 3996 3997 PQclear(res); 3998 return true; 3999 } 4000 4001 /* 4002 * \dL 4003 * 4004 * Describes languages. 4005 */ 4006 bool 4007 listLanguages(const char *pattern, bool verbose, bool showSystem) 4008 { 4009 PQExpBufferData buf; 4010 PGresult *res; 4011 printQueryOpt myopt = pset.popt; 4012 4013 initPQExpBuffer(&buf); 4014 4015 printfPQExpBuffer(&buf, 4016 "SELECT l.lanname AS \"%s\",\n", 4017 gettext_noop("Name")); 4018 if (pset.sversion >= 80300) 4019 appendPQExpBuffer(&buf, 4020 " pg_catalog.pg_get_userbyid(l.lanowner) as \"%s\",\n", 4021 gettext_noop("Owner")); 4022 4023 appendPQExpBuffer(&buf, 4024 " l.lanpltrusted AS \"%s\"", 4025 gettext_noop("Trusted")); 4026 4027 if (verbose) 4028 { 4029 appendPQExpBuffer(&buf, 4030 ",\n NOT l.lanispl AS \"%s\",\n" 4031 " l.lanplcallfoid::pg_catalog.regprocedure AS \"%s\",\n" 4032 " l.lanvalidator::pg_catalog.regprocedure AS \"%s\",\n ", 4033 gettext_noop("Internal language"), 4034 gettext_noop("Call handler"), 4035 gettext_noop("Validator")); 4036 if (pset.sversion >= 90000) 4037 appendPQExpBuffer(&buf, "l.laninline::pg_catalog.regprocedure AS \"%s\",\n ", 4038 gettext_noop("Inline handler")); 4039 printACLColumn(&buf, "l.lanacl"); 4040 } 4041 4042 appendPQExpBuffer(&buf, 4043 ",\n d.description AS \"%s\"" 4044 "\nFROM pg_catalog.pg_language l\n" 4045 "LEFT JOIN pg_catalog.pg_description d\n" 4046 " ON d.classoid = l.tableoid AND d.objoid = l.oid\n" 4047 " AND d.objsubid = 0\n", 4048 gettext_noop("Description")); 4049 4050 if (pattern) 4051 processSQLNamePattern(pset.db, &buf, pattern, false, false, 4052 NULL, "l.lanname", NULL, NULL); 4053 4054 if (!showSystem && !pattern) 4055 appendPQExpBufferStr(&buf, "WHERE l.lanplcallfoid != 0\n"); 4056 4057 4058 appendPQExpBufferStr(&buf, "ORDER BY 1;"); 4059 4060 res = PSQLexec(buf.data); 4061 termPQExpBuffer(&buf); 4062 if (!res) 4063 return false; 4064 4065 myopt.nullPrint = NULL; 4066 myopt.title = _("List of languages"); 4067 myopt.translate_header = true; 4068 4069 printQuery(res, &myopt, pset.queryFout, false, pset.logfile); 4070 4071 PQclear(res); 4072 return true; 4073 } 4074 4075 4076 /* 4077 * \dD 4078 * 4079 * Describes domains. 4080 */ 4081 bool 4082 listDomains(const char *pattern, bool verbose, bool showSystem) 4083 { 4084 PQExpBufferData buf; 4085 PGresult *res; 4086 printQueryOpt myopt = pset.popt; 4087 4088 initPQExpBuffer(&buf); 4089 4090 printfPQExpBuffer(&buf, 4091 "SELECT n.nspname as \"%s\",\n" 4092 " t.typname as \"%s\",\n" 4093 " pg_catalog.format_type(t.typbasetype, t.typtypmod) as \"%s\",\n", 4094 gettext_noop("Schema"), 4095 gettext_noop("Name"), 4096 gettext_noop("Type")); 4097 4098 if (pset.sversion >= 90100) 4099 appendPQExpBuffer(&buf, 4100 " (SELECT c.collname FROM pg_catalog.pg_collation c, pg_catalog.pg_type bt\n" 4101 " WHERE c.oid = t.typcollation AND bt.oid = t.typbasetype AND t.typcollation <> bt.typcollation) as \"%s\",\n", 4102 gettext_noop("Collation")); 4103 appendPQExpBuffer(&buf, 4104 " CASE WHEN t.typnotnull THEN 'not null' END as \"%s\",\n" 4105 " t.typdefault as \"%s\",\n" 4106 " pg_catalog.array_to_string(ARRAY(\n" 4107 " SELECT pg_catalog.pg_get_constraintdef(r.oid, true) FROM pg_catalog.pg_constraint r WHERE t.oid = r.contypid\n" 4108 " ), ' ') as \"%s\"", 4109 gettext_noop("Nullable"), 4110 gettext_noop("Default"), 4111 gettext_noop("Check")); 4112 4113 if (verbose) 4114 { 4115 if (pset.sversion >= 90200) 4116 { 4117 appendPQExpBufferStr(&buf, ",\n "); 4118 printACLColumn(&buf, "t.typacl"); 4119 } 4120 appendPQExpBuffer(&buf, 4121 ",\n d.description as \"%s\"", 4122 gettext_noop("Description")); 4123 } 4124 4125 appendPQExpBufferStr(&buf, 4126 "\nFROM pg_catalog.pg_type t\n" 4127 " LEFT JOIN pg_catalog.pg_namespace n ON n.oid = t.typnamespace\n"); 4128 4129 if (verbose) 4130 appendPQExpBufferStr(&buf, 4131 " LEFT JOIN pg_catalog.pg_description d " 4132 "ON d.classoid = t.tableoid AND d.objoid = t.oid " 4133 "AND d.objsubid = 0\n"); 4134 4135 appendPQExpBufferStr(&buf, "WHERE t.typtype = 'd'\n"); 4136 4137 if (!showSystem && !pattern) 4138 appendPQExpBufferStr(&buf, " AND n.nspname <> 'pg_catalog'\n" 4139 " AND n.nspname <> 'information_schema'\n"); 4140 4141 processSQLNamePattern(pset.db, &buf, pattern, true, false, 4142 "n.nspname", "t.typname", NULL, 4143 "pg_catalog.pg_type_is_visible(t.oid)"); 4144 4145 appendPQExpBufferStr(&buf, "ORDER BY 1, 2;"); 4146 4147 res = PSQLexec(buf.data); 4148 termPQExpBuffer(&buf); 4149 if (!res) 4150 return false; 4151 4152 myopt.nullPrint = NULL; 4153 myopt.title = _("List of domains"); 4154 myopt.translate_header = true; 4155 4156 printQuery(res, &myopt, pset.queryFout, false, pset.logfile); 4157 4158 PQclear(res); 4159 return true; 4160 } 4161 4162 /* 4163 * \dc 4164 * 4165 * Describes conversions. 4166 */ 4167 bool 4168 listConversions(const char *pattern, bool verbose, bool showSystem) 4169 { 4170 PQExpBufferData buf; 4171 PGresult *res; 4172 printQueryOpt myopt = pset.popt; 4173 static const bool translate_columns[] = 4174 {false, false, false, false, true, false}; 4175 4176 initPQExpBuffer(&buf); 4177 4178 printfPQExpBuffer(&buf, 4179 "SELECT n.nspname AS \"%s\",\n" 4180 " c.conname AS \"%s\",\n" 4181 " pg_catalog.pg_encoding_to_char(c.conforencoding) AS \"%s\",\n" 4182 " pg_catalog.pg_encoding_to_char(c.contoencoding) AS \"%s\",\n" 4183 " CASE WHEN c.condefault THEN '%s'\n" 4184 " ELSE '%s' END AS \"%s\"", 4185 gettext_noop("Schema"), 4186 gettext_noop("Name"), 4187 gettext_noop("Source"), 4188 gettext_noop("Destination"), 4189 gettext_noop("yes"), gettext_noop("no"), 4190 gettext_noop("Default?")); 4191 4192 if (verbose) 4193 appendPQExpBuffer(&buf, 4194 ",\n d.description AS \"%s\"", 4195 gettext_noop("Description")); 4196 4197 appendPQExpBufferStr(&buf, 4198 "\nFROM pg_catalog.pg_conversion c\n" 4199 " JOIN pg_catalog.pg_namespace n " 4200 "ON n.oid = c.connamespace\n"); 4201 4202 if (verbose) 4203 appendPQExpBufferStr(&buf, 4204 "LEFT JOIN pg_catalog.pg_description d " 4205 "ON d.classoid = c.tableoid\n" 4206 " AND d.objoid = c.oid " 4207 "AND d.objsubid = 0\n"); 4208 4209 appendPQExpBufferStr(&buf, "WHERE true\n"); 4210 4211 if (!showSystem && !pattern) 4212 appendPQExpBufferStr(&buf, " AND n.nspname <> 'pg_catalog'\n" 4213 " AND n.nspname <> 'information_schema'\n"); 4214 4215 processSQLNamePattern(pset.db, &buf, pattern, true, false, 4216 "n.nspname", "c.conname", NULL, 4217 "pg_catalog.pg_conversion_is_visible(c.oid)"); 4218 4219 appendPQExpBufferStr(&buf, "ORDER BY 1, 2;"); 4220 4221 res = PSQLexec(buf.data); 4222 termPQExpBuffer(&buf); 4223 if (!res) 4224 return false; 4225 4226 myopt.nullPrint = NULL; 4227 myopt.title = _("List of conversions"); 4228 myopt.translate_header = true; 4229 myopt.translate_columns = translate_columns; 4230 myopt.n_translate_columns = lengthof(translate_columns); 4231 4232 printQuery(res, &myopt, pset.queryFout, false, pset.logfile); 4233 4234 PQclear(res); 4235 return true; 4236 } 4237 4238 /* 4239 * \dy 4240 * 4241 * Describes Event Triggers. 4242 */ 4243 bool 4244 listEventTriggers(const char *pattern, bool verbose) 4245 { 4246 PQExpBufferData buf; 4247 PGresult *res; 4248 printQueryOpt myopt = pset.popt; 4249 static const bool translate_columns[] = 4250 {false, false, false, true, false, false, false}; 4251 4252 initPQExpBuffer(&buf); 4253 4254 printfPQExpBuffer(&buf, 4255 "SELECT evtname as \"%s\", " 4256 "evtevent as \"%s\", " 4257 "pg_catalog.pg_get_userbyid(e.evtowner) as \"%s\",\n" 4258 " case evtenabled when 'O' then '%s'" 4259 " when 'R' then '%s'" 4260 " when 'A' then '%s'" 4261 " when 'D' then '%s' end as \"%s\",\n" 4262 " e.evtfoid::pg_catalog.regproc as \"%s\", " 4263 "pg_catalog.array_to_string(array(select x" 4264 " from pg_catalog.unnest(evttags) as t(x)), ', ') as \"%s\"", 4265 gettext_noop("Name"), 4266 gettext_noop("Event"), 4267 gettext_noop("Owner"), 4268 gettext_noop("enabled"), 4269 gettext_noop("replica"), 4270 gettext_noop("always"), 4271 gettext_noop("disabled"), 4272 gettext_noop("Enabled"), 4273 gettext_noop("Function"), 4274 gettext_noop("Tags")); 4275 if (verbose) 4276 appendPQExpBuffer(&buf, 4277 ",\npg_catalog.obj_description(e.oid, 'pg_event_trigger') as \"%s\"", 4278 gettext_noop("Description")); 4279 appendPQExpBufferStr(&buf, 4280 "\nFROM pg_catalog.pg_event_trigger e "); 4281 4282 processSQLNamePattern(pset.db, &buf, pattern, false, false, 4283 NULL, "evtname", NULL, NULL); 4284 4285 appendPQExpBufferStr(&buf, "ORDER BY 1"); 4286 4287 res = PSQLexec(buf.data); 4288 termPQExpBuffer(&buf); 4289 if (!res) 4290 return false; 4291 4292 myopt.nullPrint = NULL; 4293 myopt.title = _("List of event triggers"); 4294 myopt.translate_header = true; 4295 myopt.translate_columns = translate_columns; 4296 myopt.n_translate_columns = lengthof(translate_columns); 4297 4298 printQuery(res, &myopt, pset.queryFout, false, pset.logfile); 4299 4300 PQclear(res); 4301 return true; 4302 } 4303 4304 /* 4305 * \dC 4306 * 4307 * Describes casts. 4308 */ 4309 bool 4310 listCasts(const char *pattern, bool verbose) 4311 { 4312 PQExpBufferData buf; 4313 PGresult *res; 4314 printQueryOpt myopt = pset.popt; 4315 static const bool translate_columns[] = {false, false, false, true, false}; 4316 4317 initPQExpBuffer(&buf); 4318 4319 printfPQExpBuffer(&buf, 4320 "SELECT pg_catalog.format_type(castsource, NULL) AS \"%s\",\n" 4321 " pg_catalog.format_type(casttarget, NULL) AS \"%s\",\n", 4322 gettext_noop("Source type"), 4323 gettext_noop("Target type")); 4324 4325 /* 4326 * We don't attempt to localize '(binary coercible)' or '(with inout)', 4327 * because there's too much risk of gettext translating a function name 4328 * that happens to match some string in the PO database. 4329 */ 4330 if (pset.sversion >= 80400) 4331 appendPQExpBuffer(&buf, 4332 " CASE WHEN c.castmethod = '%c' THEN '(binary coercible)'\n" 4333 " WHEN c.castmethod = '%c' THEN '(with inout)'\n" 4334 " ELSE p.proname\n" 4335 " END AS \"%s\",\n", 4336 COERCION_METHOD_BINARY, 4337 COERCION_METHOD_INOUT, 4338 gettext_noop("Function")); 4339 else 4340 appendPQExpBuffer(&buf, 4341 " CASE WHEN c.castfunc = 0 THEN '(binary coercible)'\n" 4342 " ELSE p.proname\n" 4343 " END AS \"%s\",\n", 4344 gettext_noop("Function")); 4345 4346 appendPQExpBuffer(&buf, 4347 " CASE WHEN c.castcontext = '%c' THEN '%s'\n" 4348 " WHEN c.castcontext = '%c' THEN '%s'\n" 4349 " ELSE '%s'\n" 4350 " END AS \"%s\"", 4351 COERCION_CODE_EXPLICIT, 4352 gettext_noop("no"), 4353 COERCION_CODE_ASSIGNMENT, 4354 gettext_noop("in assignment"), 4355 gettext_noop("yes"), 4356 gettext_noop("Implicit?")); 4357 4358 if (verbose) 4359 appendPQExpBuffer(&buf, 4360 ",\n d.description AS \"%s\"", 4361 gettext_noop("Description")); 4362 4363 /* 4364 * We need a left join to pg_proc for binary casts; the others are just 4365 * paranoia. 4366 */ 4367 appendPQExpBufferStr(&buf, 4368 "\nFROM pg_catalog.pg_cast c LEFT JOIN pg_catalog.pg_proc p\n" 4369 " ON c.castfunc = p.oid\n" 4370 " LEFT JOIN pg_catalog.pg_type ts\n" 4371 " ON c.castsource = ts.oid\n" 4372 " LEFT JOIN pg_catalog.pg_namespace ns\n" 4373 " ON ns.oid = ts.typnamespace\n" 4374 " LEFT JOIN pg_catalog.pg_type tt\n" 4375 " ON c.casttarget = tt.oid\n" 4376 " LEFT JOIN pg_catalog.pg_namespace nt\n" 4377 " ON nt.oid = tt.typnamespace\n"); 4378 4379 if (verbose) 4380 appendPQExpBufferStr(&buf, 4381 " LEFT JOIN pg_catalog.pg_description d\n" 4382 " ON d.classoid = c.tableoid AND d.objoid = " 4383 "c.oid AND d.objsubid = 0\n"); 4384 4385 appendPQExpBufferStr(&buf, "WHERE ( (true"); 4386 4387 /* 4388 * Match name pattern against either internal or external name of either 4389 * castsource or casttarget 4390 */ 4391 processSQLNamePattern(pset.db, &buf, pattern, true, false, 4392 "ns.nspname", "ts.typname", 4393 "pg_catalog.format_type(ts.oid, NULL)", 4394 "pg_catalog.pg_type_is_visible(ts.oid)"); 4395 4396 appendPQExpBufferStr(&buf, ") OR (true"); 4397 4398 processSQLNamePattern(pset.db, &buf, pattern, true, false, 4399 "nt.nspname", "tt.typname", 4400 "pg_catalog.format_type(tt.oid, NULL)", 4401 "pg_catalog.pg_type_is_visible(tt.oid)"); 4402 4403 appendPQExpBufferStr(&buf, ") )\nORDER BY 1, 2;"); 4404 4405 res = PSQLexec(buf.data); 4406 termPQExpBuffer(&buf); 4407 if (!res) 4408 return false; 4409 4410 myopt.nullPrint = NULL; 4411 myopt.title = _("List of casts"); 4412 myopt.translate_header = true; 4413 myopt.translate_columns = translate_columns; 4414 myopt.n_translate_columns = lengthof(translate_columns); 4415 4416 printQuery(res, &myopt, pset.queryFout, false, pset.logfile); 4417 4418 PQclear(res); 4419 return true; 4420 } 4421 4422 /* 4423 * \dO 4424 * 4425 * Describes collations. 4426 */ 4427 bool 4428 listCollations(const char *pattern, bool verbose, bool showSystem) 4429 { 4430 PQExpBufferData buf; 4431 PGresult *res; 4432 printQueryOpt myopt = pset.popt; 4433 static const bool translate_columns[] = {false, false, false, false, false, true, false}; 4434 4435 if (pset.sversion < 90100) 4436 { 4437 char sverbuf[32]; 4438 4439 pg_log_error("The server (version %s) does not support collations.", 4440 formatPGVersionNumber(pset.sversion, false, 4441 sverbuf, sizeof(sverbuf))); 4442 return true; 4443 } 4444 4445 initPQExpBuffer(&buf); 4446 4447 printfPQExpBuffer(&buf, 4448 "SELECT n.nspname AS \"%s\",\n" 4449 " c.collname AS \"%s\",\n" 4450 " c.collcollate AS \"%s\",\n" 4451 " c.collctype AS \"%s\"", 4452 gettext_noop("Schema"), 4453 gettext_noop("Name"), 4454 gettext_noop("Collate"), 4455 gettext_noop("Ctype")); 4456 4457 if (pset.sversion >= 100000) 4458 appendPQExpBuffer(&buf, 4459 ",\n CASE c.collprovider WHEN 'd' THEN 'default' WHEN 'c' THEN 'libc' WHEN 'i' THEN 'icu' END AS \"%s\"", 4460 gettext_noop("Provider")); 4461 else 4462 appendPQExpBuffer(&buf, 4463 ",\n 'libc' AS \"%s\"", 4464 gettext_noop("Provider")); 4465 4466 if (pset.sversion >= 120000) 4467 appendPQExpBuffer(&buf, 4468 ",\n CASE WHEN c.collisdeterministic THEN '%s' ELSE '%s' END AS \"%s\"", 4469 gettext_noop("yes"), gettext_noop("no"), 4470 gettext_noop("Deterministic?")); 4471 else 4472 appendPQExpBuffer(&buf, 4473 ",\n '%s' AS \"%s\"", 4474 gettext_noop("yes"), 4475 gettext_noop("Deterministic?")); 4476 4477 if (verbose) 4478 appendPQExpBuffer(&buf, 4479 ",\n pg_catalog.obj_description(c.oid, 'pg_collation') AS \"%s\"", 4480 gettext_noop("Description")); 4481 4482 appendPQExpBufferStr(&buf, 4483 "\nFROM pg_catalog.pg_collation c, pg_catalog.pg_namespace n\n" 4484 "WHERE n.oid = c.collnamespace\n"); 4485 4486 if (!showSystem && !pattern) 4487 appendPQExpBufferStr(&buf, " AND n.nspname <> 'pg_catalog'\n" 4488 " AND n.nspname <> 'information_schema'\n"); 4489 4490 /* 4491 * Hide collations that aren't usable in the current database's encoding. 4492 * If you think to change this, note that pg_collation_is_visible rejects 4493 * unusable collations, so you will need to hack name pattern processing 4494 * somehow to avoid inconsistent behavior. 4495 */ 4496 appendPQExpBufferStr(&buf, " AND c.collencoding IN (-1, pg_catalog.pg_char_to_encoding(pg_catalog.getdatabaseencoding()))\n"); 4497 4498 processSQLNamePattern(pset.db, &buf, pattern, true, false, 4499 "n.nspname", "c.collname", NULL, 4500 "pg_catalog.pg_collation_is_visible(c.oid)"); 4501 4502 appendPQExpBufferStr(&buf, "ORDER BY 1, 2;"); 4503 4504 res = PSQLexec(buf.data); 4505 termPQExpBuffer(&buf); 4506 if (!res) 4507 return false; 4508 4509 myopt.nullPrint = NULL; 4510 myopt.title = _("List of collations"); 4511 myopt.translate_header = true; 4512 myopt.translate_columns = translate_columns; 4513 myopt.n_translate_columns = lengthof(translate_columns); 4514 4515 printQuery(res, &myopt, pset.queryFout, false, pset.logfile); 4516 4517 PQclear(res); 4518 return true; 4519 } 4520 4521 /* 4522 * \dn 4523 * 4524 * Describes schemas (namespaces) 4525 */ 4526 bool 4527 listSchemas(const char *pattern, bool verbose, bool showSystem) 4528 { 4529 PQExpBufferData buf; 4530 PGresult *res; 4531 printQueryOpt myopt = pset.popt; 4532 4533 initPQExpBuffer(&buf); 4534 printfPQExpBuffer(&buf, 4535 "SELECT n.nspname AS \"%s\",\n" 4536 " pg_catalog.pg_get_userbyid(n.nspowner) AS \"%s\"", 4537 gettext_noop("Name"), 4538 gettext_noop("Owner")); 4539 4540 if (verbose) 4541 { 4542 appendPQExpBufferStr(&buf, ",\n "); 4543 printACLColumn(&buf, "n.nspacl"); 4544 appendPQExpBuffer(&buf, 4545 ",\n pg_catalog.obj_description(n.oid, 'pg_namespace') AS \"%s\"", 4546 gettext_noop("Description")); 4547 } 4548 4549 appendPQExpBuffer(&buf, 4550 "\nFROM pg_catalog.pg_namespace n\n"); 4551 4552 if (!showSystem && !pattern) 4553 appendPQExpBufferStr(&buf, 4554 "WHERE n.nspname !~ '^pg_' AND n.nspname <> 'information_schema'\n"); 4555 4556 processSQLNamePattern(pset.db, &buf, pattern, 4557 !showSystem && !pattern, false, 4558 NULL, "n.nspname", NULL, 4559 NULL); 4560 4561 appendPQExpBufferStr(&buf, "ORDER BY 1;"); 4562 4563 res = PSQLexec(buf.data); 4564 termPQExpBuffer(&buf); 4565 if (!res) 4566 return false; 4567 4568 myopt.nullPrint = NULL; 4569 myopt.title = _("List of schemas"); 4570 myopt.translate_header = true; 4571 4572 printQuery(res, &myopt, pset.queryFout, false, pset.logfile); 4573 4574 PQclear(res); 4575 return true; 4576 } 4577 4578 4579 /* 4580 * \dFp 4581 * list text search parsers 4582 */ 4583 bool 4584 listTSParsers(const char *pattern, bool verbose) 4585 { 4586 PQExpBufferData buf; 4587 PGresult *res; 4588 printQueryOpt myopt = pset.popt; 4589 4590 if (pset.sversion < 80300) 4591 { 4592 char sverbuf[32]; 4593 4594 pg_log_error("The server (version %s) does not support full text search.", 4595 formatPGVersionNumber(pset.sversion, false, 4596 sverbuf, sizeof(sverbuf))); 4597 return true; 4598 } 4599 4600 if (verbose) 4601 return listTSParsersVerbose(pattern); 4602 4603 initPQExpBuffer(&buf); 4604 4605 printfPQExpBuffer(&buf, 4606 "SELECT\n" 4607 " n.nspname as \"%s\",\n" 4608 " p.prsname as \"%s\",\n" 4609 " pg_catalog.obj_description(p.oid, 'pg_ts_parser') as \"%s\"\n" 4610 "FROM pg_catalog.pg_ts_parser p\n" 4611 "LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.prsnamespace\n", 4612 gettext_noop("Schema"), 4613 gettext_noop("Name"), 4614 gettext_noop("Description") 4615 ); 4616 4617 processSQLNamePattern(pset.db, &buf, pattern, false, false, 4618 "n.nspname", "p.prsname", NULL, 4619 "pg_catalog.pg_ts_parser_is_visible(p.oid)"); 4620 4621 appendPQExpBufferStr(&buf, "ORDER BY 1, 2;"); 4622 4623 res = PSQLexec(buf.data); 4624 termPQExpBuffer(&buf); 4625 if (!res) 4626 return false; 4627 4628 myopt.nullPrint = NULL; 4629 myopt.title = _("List of text search parsers"); 4630 myopt.translate_header = true; 4631 4632 printQuery(res, &myopt, pset.queryFout, false, pset.logfile); 4633 4634 PQclear(res); 4635 return true; 4636 } 4637 4638 /* 4639 * full description of parsers 4640 */ 4641 static bool 4642 listTSParsersVerbose(const char *pattern) 4643 { 4644 PQExpBufferData buf; 4645 PGresult *res; 4646 int i; 4647 4648 initPQExpBuffer(&buf); 4649 4650 printfPQExpBuffer(&buf, 4651 "SELECT p.oid,\n" 4652 " n.nspname,\n" 4653 " p.prsname\n" 4654 "FROM pg_catalog.pg_ts_parser p\n" 4655 "LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.prsnamespace\n" 4656 ); 4657 4658 processSQLNamePattern(pset.db, &buf, pattern, false, false, 4659 "n.nspname", "p.prsname", NULL, 4660 "pg_catalog.pg_ts_parser_is_visible(p.oid)"); 4661 4662 appendPQExpBufferStr(&buf, "ORDER BY 1, 2;"); 4663 4664 res = PSQLexec(buf.data); 4665 termPQExpBuffer(&buf); 4666 if (!res) 4667 return false; 4668 4669 if (PQntuples(res) == 0) 4670 { 4671 if (!pset.quiet) 4672 { 4673 if (pattern) 4674 pg_log_error("Did not find any text search parser named \"%s\".", 4675 pattern); 4676 else 4677 pg_log_error("Did not find any text search parsers."); 4678 } 4679 PQclear(res); 4680 return false; 4681 } 4682 4683 for (i = 0; i < PQntuples(res); i++) 4684 { 4685 const char *oid; 4686 const char *nspname = NULL; 4687 const char *prsname; 4688 4689 oid = PQgetvalue(res, i, 0); 4690 if (!PQgetisnull(res, i, 1)) 4691 nspname = PQgetvalue(res, i, 1); 4692 prsname = PQgetvalue(res, i, 2); 4693 4694 if (!describeOneTSParser(oid, nspname, prsname)) 4695 { 4696 PQclear(res); 4697 return false; 4698 } 4699 4700 if (cancel_pressed) 4701 { 4702 PQclear(res); 4703 return false; 4704 } 4705 } 4706 4707 PQclear(res); 4708 return true; 4709 } 4710 4711 static bool 4712 describeOneTSParser(const char *oid, const char *nspname, const char *prsname) 4713 { 4714 PQExpBufferData buf; 4715 PGresult *res; 4716 PQExpBufferData title; 4717 printQueryOpt myopt = pset.popt; 4718 static const bool translate_columns[] = {true, false, false}; 4719 4720 initPQExpBuffer(&buf); 4721 4722 printfPQExpBuffer(&buf, 4723 "SELECT '%s' AS \"%s\",\n" 4724 " p.prsstart::pg_catalog.regproc AS \"%s\",\n" 4725 " pg_catalog.obj_description(p.prsstart, 'pg_proc') as \"%s\"\n" 4726 " FROM pg_catalog.pg_ts_parser p\n" 4727 " WHERE p.oid = '%s'\n" 4728 "UNION ALL\n" 4729 "SELECT '%s',\n" 4730 " p.prstoken::pg_catalog.regproc,\n" 4731 " pg_catalog.obj_description(p.prstoken, 'pg_proc')\n" 4732 " FROM pg_catalog.pg_ts_parser p\n" 4733 " WHERE p.oid = '%s'\n" 4734 "UNION ALL\n" 4735 "SELECT '%s',\n" 4736 " p.prsend::pg_catalog.regproc,\n" 4737 " pg_catalog.obj_description(p.prsend, 'pg_proc')\n" 4738 " FROM pg_catalog.pg_ts_parser p\n" 4739 " WHERE p.oid = '%s'\n" 4740 "UNION ALL\n" 4741 "SELECT '%s',\n" 4742 " p.prsheadline::pg_catalog.regproc,\n" 4743 " pg_catalog.obj_description(p.prsheadline, 'pg_proc')\n" 4744 " FROM pg_catalog.pg_ts_parser p\n" 4745 " WHERE p.oid = '%s'\n" 4746 "UNION ALL\n" 4747 "SELECT '%s',\n" 4748 " p.prslextype::pg_catalog.regproc,\n" 4749 " pg_catalog.obj_description(p.prslextype, 'pg_proc')\n" 4750 " FROM pg_catalog.pg_ts_parser p\n" 4751 " WHERE p.oid = '%s';", 4752 gettext_noop("Start parse"), 4753 gettext_noop("Method"), 4754 gettext_noop("Function"), 4755 gettext_noop("Description"), 4756 oid, 4757 gettext_noop("Get next token"), 4758 oid, 4759 gettext_noop("End parse"), 4760 oid, 4761 gettext_noop("Get headline"), 4762 oid, 4763 gettext_noop("Get token types"), 4764 oid); 4765 4766 res = PSQLexec(buf.data); 4767 termPQExpBuffer(&buf); 4768 if (!res) 4769 return false; 4770 4771 myopt.nullPrint = NULL; 4772 initPQExpBuffer(&title); 4773 if (nspname) 4774 printfPQExpBuffer(&title, _("Text search parser \"%s.%s\""), 4775 nspname, prsname); 4776 else 4777 printfPQExpBuffer(&title, _("Text search parser \"%s\""), prsname); 4778 myopt.title = title.data; 4779 myopt.footers = NULL; 4780 myopt.topt.default_footer = false; 4781 myopt.translate_header = true; 4782 myopt.translate_columns = translate_columns; 4783 myopt.n_translate_columns = lengthof(translate_columns); 4784 4785 printQuery(res, &myopt, pset.queryFout, false, pset.logfile); 4786 4787 PQclear(res); 4788 4789 initPQExpBuffer(&buf); 4790 4791 printfPQExpBuffer(&buf, 4792 "SELECT t.alias as \"%s\",\n" 4793 " t.description as \"%s\"\n" 4794 "FROM pg_catalog.ts_token_type( '%s'::pg_catalog.oid ) as t\n" 4795 "ORDER BY 1;", 4796 gettext_noop("Token name"), 4797 gettext_noop("Description"), 4798 oid); 4799 4800 res = PSQLexec(buf.data); 4801 termPQExpBuffer(&buf); 4802 if (!res) 4803 return false; 4804 4805 myopt.nullPrint = NULL; 4806 if (nspname) 4807 printfPQExpBuffer(&title, _("Token types for parser \"%s.%s\""), 4808 nspname, prsname); 4809 else 4810 printfPQExpBuffer(&title, _("Token types for parser \"%s\""), prsname); 4811 myopt.title = title.data; 4812 myopt.footers = NULL; 4813 myopt.topt.default_footer = true; 4814 myopt.translate_header = true; 4815 myopt.translate_columns = NULL; 4816 myopt.n_translate_columns = 0; 4817 4818 printQuery(res, &myopt, pset.queryFout, false, pset.logfile); 4819 4820 termPQExpBuffer(&title); 4821 PQclear(res); 4822 return true; 4823 } 4824 4825 4826 /* 4827 * \dFd 4828 * list text search dictionaries 4829 */ 4830 bool 4831 listTSDictionaries(const char *pattern, bool verbose) 4832 { 4833 PQExpBufferData buf; 4834 PGresult *res; 4835 printQueryOpt myopt = pset.popt; 4836 4837 if (pset.sversion < 80300) 4838 { 4839 char sverbuf[32]; 4840 4841 pg_log_error("The server (version %s) does not support full text search.", 4842 formatPGVersionNumber(pset.sversion, false, 4843 sverbuf, sizeof(sverbuf))); 4844 return true; 4845 } 4846 4847 initPQExpBuffer(&buf); 4848 4849 printfPQExpBuffer(&buf, 4850 "SELECT\n" 4851 " n.nspname as \"%s\",\n" 4852 " d.dictname as \"%s\",\n", 4853 gettext_noop("Schema"), 4854 gettext_noop("Name")); 4855 4856 if (verbose) 4857 { 4858 appendPQExpBuffer(&buf, 4859 " ( SELECT COALESCE(nt.nspname, '(null)')::pg_catalog.text || '.' || t.tmplname FROM\n" 4860 " pg_catalog.pg_ts_template t\n" 4861 " LEFT JOIN pg_catalog.pg_namespace nt ON nt.oid = t.tmplnamespace\n" 4862 " WHERE d.dicttemplate = t.oid ) AS \"%s\",\n" 4863 " d.dictinitoption as \"%s\",\n", 4864 gettext_noop("Template"), 4865 gettext_noop("Init options")); 4866 } 4867 4868 appendPQExpBuffer(&buf, 4869 " pg_catalog.obj_description(d.oid, 'pg_ts_dict') as \"%s\"\n", 4870 gettext_noop("Description")); 4871 4872 appendPQExpBufferStr(&buf, "FROM pg_catalog.pg_ts_dict d\n" 4873 "LEFT JOIN pg_catalog.pg_namespace n ON n.oid = d.dictnamespace\n"); 4874 4875 processSQLNamePattern(pset.db, &buf, pattern, false, false, 4876 "n.nspname", "d.dictname", NULL, 4877 "pg_catalog.pg_ts_dict_is_visible(d.oid)"); 4878 4879 appendPQExpBufferStr(&buf, "ORDER BY 1, 2;"); 4880 4881 res = PSQLexec(buf.data); 4882 termPQExpBuffer(&buf); 4883 if (!res) 4884 return false; 4885 4886 myopt.nullPrint = NULL; 4887 myopt.title = _("List of text search dictionaries"); 4888 myopt.translate_header = true; 4889 4890 printQuery(res, &myopt, pset.queryFout, false, pset.logfile); 4891 4892 PQclear(res); 4893 return true; 4894 } 4895 4896 4897 /* 4898 * \dFt 4899 * list text search templates 4900 */ 4901 bool 4902 listTSTemplates(const char *pattern, bool verbose) 4903 { 4904 PQExpBufferData buf; 4905 PGresult *res; 4906 printQueryOpt myopt = pset.popt; 4907 4908 if (pset.sversion < 80300) 4909 { 4910 char sverbuf[32]; 4911 4912 pg_log_error("The server (version %s) does not support full text search.", 4913 formatPGVersionNumber(pset.sversion, false, 4914 sverbuf, sizeof(sverbuf))); 4915 return true; 4916 } 4917 4918 initPQExpBuffer(&buf); 4919 4920 if (verbose) 4921 printfPQExpBuffer(&buf, 4922 "SELECT\n" 4923 " n.nspname AS \"%s\",\n" 4924 " t.tmplname AS \"%s\",\n" 4925 " t.tmplinit::pg_catalog.regproc AS \"%s\",\n" 4926 " t.tmpllexize::pg_catalog.regproc AS \"%s\",\n" 4927 " pg_catalog.obj_description(t.oid, 'pg_ts_template') AS \"%s\"\n", 4928 gettext_noop("Schema"), 4929 gettext_noop("Name"), 4930 gettext_noop("Init"), 4931 gettext_noop("Lexize"), 4932 gettext_noop("Description")); 4933 else 4934 printfPQExpBuffer(&buf, 4935 "SELECT\n" 4936 " n.nspname AS \"%s\",\n" 4937 " t.tmplname AS \"%s\",\n" 4938 " pg_catalog.obj_description(t.oid, 'pg_ts_template') AS \"%s\"\n", 4939 gettext_noop("Schema"), 4940 gettext_noop("Name"), 4941 gettext_noop("Description")); 4942 4943 appendPQExpBufferStr(&buf, "FROM pg_catalog.pg_ts_template t\n" 4944 "LEFT JOIN pg_catalog.pg_namespace n ON n.oid = t.tmplnamespace\n"); 4945 4946 processSQLNamePattern(pset.db, &buf, pattern, false, false, 4947 "n.nspname", "t.tmplname", NULL, 4948 "pg_catalog.pg_ts_template_is_visible(t.oid)"); 4949 4950 appendPQExpBufferStr(&buf, "ORDER BY 1, 2;"); 4951 4952 res = PSQLexec(buf.data); 4953 termPQExpBuffer(&buf); 4954 if (!res) 4955 return false; 4956 4957 myopt.nullPrint = NULL; 4958 myopt.title = _("List of text search templates"); 4959 myopt.translate_header = true; 4960 4961 printQuery(res, &myopt, pset.queryFout, false, pset.logfile); 4962 4963 PQclear(res); 4964 return true; 4965 } 4966 4967 4968 /* 4969 * \dF 4970 * list text search configurations 4971 */ 4972 bool 4973 listTSConfigs(const char *pattern, bool verbose) 4974 { 4975 PQExpBufferData buf; 4976 PGresult *res; 4977 printQueryOpt myopt = pset.popt; 4978 4979 if (pset.sversion < 80300) 4980 { 4981 char sverbuf[32]; 4982 4983 pg_log_error("The server (version %s) does not support full text search.", 4984 formatPGVersionNumber(pset.sversion, false, 4985 sverbuf, sizeof(sverbuf))); 4986 return true; 4987 } 4988 4989 if (verbose) 4990 return listTSConfigsVerbose(pattern); 4991 4992 initPQExpBuffer(&buf); 4993 4994 printfPQExpBuffer(&buf, 4995 "SELECT\n" 4996 " n.nspname as \"%s\",\n" 4997 " c.cfgname as \"%s\",\n" 4998 " pg_catalog.obj_description(c.oid, 'pg_ts_config') as \"%s\"\n" 4999 "FROM pg_catalog.pg_ts_config c\n" 5000 "LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.cfgnamespace\n", 5001 gettext_noop("Schema"), 5002 gettext_noop("Name"), 5003 gettext_noop("Description") 5004 ); 5005 5006 processSQLNamePattern(pset.db, &buf, pattern, false, false, 5007 "n.nspname", "c.cfgname", NULL, 5008 "pg_catalog.pg_ts_config_is_visible(c.oid)"); 5009 5010 appendPQExpBufferStr(&buf, "ORDER BY 1, 2;"); 5011 5012 res = PSQLexec(buf.data); 5013 termPQExpBuffer(&buf); 5014 if (!res) 5015 return false; 5016 5017 myopt.nullPrint = NULL; 5018 myopt.title = _("List of text search configurations"); 5019 myopt.translate_header = true; 5020 5021 printQuery(res, &myopt, pset.queryFout, false, pset.logfile); 5022 5023 PQclear(res); 5024 return true; 5025 } 5026 5027 static bool 5028 listTSConfigsVerbose(const char *pattern) 5029 { 5030 PQExpBufferData buf; 5031 PGresult *res; 5032 int i; 5033 5034 initPQExpBuffer(&buf); 5035 5036 printfPQExpBuffer(&buf, 5037 "SELECT c.oid, c.cfgname,\n" 5038 " n.nspname,\n" 5039 " p.prsname,\n" 5040 " np.nspname as pnspname\n" 5041 "FROM pg_catalog.pg_ts_config c\n" 5042 " LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.cfgnamespace,\n" 5043 " pg_catalog.pg_ts_parser p\n" 5044 " LEFT JOIN pg_catalog.pg_namespace np ON np.oid = p.prsnamespace\n" 5045 "WHERE p.oid = c.cfgparser\n" 5046 ); 5047 5048 processSQLNamePattern(pset.db, &buf, pattern, true, false, 5049 "n.nspname", "c.cfgname", NULL, 5050 "pg_catalog.pg_ts_config_is_visible(c.oid)"); 5051 5052 appendPQExpBufferStr(&buf, "ORDER BY 3, 2;"); 5053 5054 res = PSQLexec(buf.data); 5055 termPQExpBuffer(&buf); 5056 if (!res) 5057 return false; 5058 5059 if (PQntuples(res) == 0) 5060 { 5061 if (!pset.quiet) 5062 { 5063 if (pattern) 5064 pg_log_error("Did not find any text search configuration named \"%s\".", 5065 pattern); 5066 else 5067 pg_log_error("Did not find any text search configurations."); 5068 } 5069 PQclear(res); 5070 return false; 5071 } 5072 5073 for (i = 0; i < PQntuples(res); i++) 5074 { 5075 const char *oid; 5076 const char *cfgname; 5077 const char *nspname = NULL; 5078 const char *prsname; 5079 const char *pnspname = NULL; 5080 5081 oid = PQgetvalue(res, i, 0); 5082 cfgname = PQgetvalue(res, i, 1); 5083 if (!PQgetisnull(res, i, 2)) 5084 nspname = PQgetvalue(res, i, 2); 5085 prsname = PQgetvalue(res, i, 3); 5086 if (!PQgetisnull(res, i, 4)) 5087 pnspname = PQgetvalue(res, i, 4); 5088 5089 if (!describeOneTSConfig(oid, nspname, cfgname, pnspname, prsname)) 5090 { 5091 PQclear(res); 5092 return false; 5093 } 5094 5095 if (cancel_pressed) 5096 { 5097 PQclear(res); 5098 return false; 5099 } 5100 } 5101 5102 PQclear(res); 5103 return true; 5104 } 5105 5106 static bool 5107 describeOneTSConfig(const char *oid, const char *nspname, const char *cfgname, 5108 const char *pnspname, const char *prsname) 5109 { 5110 PQExpBufferData buf, 5111 title; 5112 PGresult *res; 5113 printQueryOpt myopt = pset.popt; 5114 5115 initPQExpBuffer(&buf); 5116 5117 printfPQExpBuffer(&buf, 5118 "SELECT\n" 5119 " ( SELECT t.alias FROM\n" 5120 " pg_catalog.ts_token_type(c.cfgparser) AS t\n" 5121 " WHERE t.tokid = m.maptokentype ) AS \"%s\",\n" 5122 " pg_catalog.btrim(\n" 5123 " ARRAY( SELECT mm.mapdict::pg_catalog.regdictionary\n" 5124 " FROM pg_catalog.pg_ts_config_map AS mm\n" 5125 " WHERE mm.mapcfg = m.mapcfg AND mm.maptokentype = m.maptokentype\n" 5126 " ORDER BY mapcfg, maptokentype, mapseqno\n" 5127 " ) :: pg_catalog.text,\n" 5128 " '{}') AS \"%s\"\n" 5129 "FROM pg_catalog.pg_ts_config AS c, pg_catalog.pg_ts_config_map AS m\n" 5130 "WHERE c.oid = '%s' AND m.mapcfg = c.oid\n" 5131 "GROUP BY m.mapcfg, m.maptokentype, c.cfgparser\n" 5132 "ORDER BY 1;", 5133 gettext_noop("Token"), 5134 gettext_noop("Dictionaries"), 5135 oid); 5136 5137 res = PSQLexec(buf.data); 5138 termPQExpBuffer(&buf); 5139 if (!res) 5140 return false; 5141 5142 initPQExpBuffer(&title); 5143 5144 if (nspname) 5145 appendPQExpBuffer(&title, _("Text search configuration \"%s.%s\""), 5146 nspname, cfgname); 5147 else 5148 appendPQExpBuffer(&title, _("Text search configuration \"%s\""), 5149 cfgname); 5150 5151 if (pnspname) 5152 appendPQExpBuffer(&title, _("\nParser: \"%s.%s\""), 5153 pnspname, prsname); 5154 else 5155 appendPQExpBuffer(&title, _("\nParser: \"%s\""), 5156 prsname); 5157 5158 myopt.nullPrint = NULL; 5159 myopt.title = title.data; 5160 myopt.footers = NULL; 5161 myopt.topt.default_footer = false; 5162 myopt.translate_header = true; 5163 5164 printQuery(res, &myopt, pset.queryFout, false, pset.logfile); 5165 5166 termPQExpBuffer(&title); 5167 5168 PQclear(res); 5169 return true; 5170 } 5171 5172 5173 /* 5174 * \dew 5175 * 5176 * Describes foreign-data wrappers 5177 */ 5178 bool 5179 listForeignDataWrappers(const char *pattern, bool verbose) 5180 { 5181 PQExpBufferData buf; 5182 PGresult *res; 5183 printQueryOpt myopt = pset.popt; 5184 5185 if (pset.sversion < 80400) 5186 { 5187 char sverbuf[32]; 5188 5189 pg_log_error("The server (version %s) does not support foreign-data wrappers.", 5190 formatPGVersionNumber(pset.sversion, false, 5191 sverbuf, sizeof(sverbuf))); 5192 return true; 5193 } 5194 5195 initPQExpBuffer(&buf); 5196 printfPQExpBuffer(&buf, 5197 "SELECT fdw.fdwname AS \"%s\",\n" 5198 " pg_catalog.pg_get_userbyid(fdw.fdwowner) AS \"%s\",\n", 5199 gettext_noop("Name"), 5200 gettext_noop("Owner")); 5201 if (pset.sversion >= 90100) 5202 appendPQExpBuffer(&buf, 5203 " fdw.fdwhandler::pg_catalog.regproc AS \"%s\",\n", 5204 gettext_noop("Handler")); 5205 appendPQExpBuffer(&buf, 5206 " fdw.fdwvalidator::pg_catalog.regproc AS \"%s\"", 5207 gettext_noop("Validator")); 5208 5209 if (verbose) 5210 { 5211 appendPQExpBufferStr(&buf, ",\n "); 5212 printACLColumn(&buf, "fdwacl"); 5213 appendPQExpBuffer(&buf, 5214 ",\n CASE WHEN fdwoptions IS NULL THEN '' ELSE " 5215 " '(' || pg_catalog.array_to_string(ARRAY(SELECT " 5216 " pg_catalog.quote_ident(option_name) || ' ' || " 5217 " pg_catalog.quote_literal(option_value) FROM " 5218 " pg_catalog.pg_options_to_table(fdwoptions)), ', ') || ')' " 5219 " END AS \"%s\"", 5220 gettext_noop("FDW options")); 5221 5222 if (pset.sversion >= 90100) 5223 appendPQExpBuffer(&buf, 5224 ",\n d.description AS \"%s\" ", 5225 gettext_noop("Description")); 5226 } 5227 5228 appendPQExpBufferStr(&buf, "\nFROM pg_catalog.pg_foreign_data_wrapper fdw\n"); 5229 5230 if (verbose && pset.sversion >= 90100) 5231 appendPQExpBufferStr(&buf, 5232 "LEFT JOIN pg_catalog.pg_description d\n" 5233 " ON d.classoid = fdw.tableoid " 5234 "AND d.objoid = fdw.oid AND d.objsubid = 0\n"); 5235 5236 processSQLNamePattern(pset.db, &buf, pattern, false, false, 5237 NULL, "fdwname", NULL, NULL); 5238 5239 appendPQExpBufferStr(&buf, "ORDER BY 1;"); 5240 5241 res = PSQLexec(buf.data); 5242 termPQExpBuffer(&buf); 5243 if (!res) 5244 return false; 5245 5246 myopt.nullPrint = NULL; 5247 myopt.title = _("List of foreign-data wrappers"); 5248 myopt.translate_header = true; 5249 5250 printQuery(res, &myopt, pset.queryFout, false, pset.logfile); 5251 5252 PQclear(res); 5253 return true; 5254 } 5255 5256 /* 5257 * \des 5258 * 5259 * Describes foreign servers. 5260 */ 5261 bool 5262 listForeignServers(const char *pattern, bool verbose) 5263 { 5264 PQExpBufferData buf; 5265 PGresult *res; 5266 printQueryOpt myopt = pset.popt; 5267 5268 if (pset.sversion < 80400) 5269 { 5270 char sverbuf[32]; 5271 5272 pg_log_error("The server (version %s) does not support foreign servers.", 5273 formatPGVersionNumber(pset.sversion, false, 5274 sverbuf, sizeof(sverbuf))); 5275 return true; 5276 } 5277 5278 initPQExpBuffer(&buf); 5279 printfPQExpBuffer(&buf, 5280 "SELECT s.srvname AS \"%s\",\n" 5281 " pg_catalog.pg_get_userbyid(s.srvowner) AS \"%s\",\n" 5282 " f.fdwname AS \"%s\"", 5283 gettext_noop("Name"), 5284 gettext_noop("Owner"), 5285 gettext_noop("Foreign-data wrapper")); 5286 5287 if (verbose) 5288 { 5289 appendPQExpBufferStr(&buf, ",\n "); 5290 printACLColumn(&buf, "s.srvacl"); 5291 appendPQExpBuffer(&buf, 5292 ",\n" 5293 " s.srvtype AS \"%s\",\n" 5294 " s.srvversion AS \"%s\",\n" 5295 " CASE WHEN srvoptions IS NULL THEN '' ELSE " 5296 " '(' || pg_catalog.array_to_string(ARRAY(SELECT " 5297 " pg_catalog.quote_ident(option_name) || ' ' || " 5298 " pg_catalog.quote_literal(option_value) FROM " 5299 " pg_catalog.pg_options_to_table(srvoptions)), ', ') || ')' " 5300 " END AS \"%s\",\n" 5301 " d.description AS \"%s\"", 5302 gettext_noop("Type"), 5303 gettext_noop("Version"), 5304 gettext_noop("FDW options"), 5305 gettext_noop("Description")); 5306 } 5307 5308 appendPQExpBufferStr(&buf, 5309 "\nFROM pg_catalog.pg_foreign_server s\n" 5310 " JOIN pg_catalog.pg_foreign_data_wrapper f ON f.oid=s.srvfdw\n"); 5311 5312 if (verbose) 5313 appendPQExpBufferStr(&buf, 5314 "LEFT JOIN pg_catalog.pg_description d\n " 5315 "ON d.classoid = s.tableoid AND d.objoid = s.oid " 5316 "AND d.objsubid = 0\n"); 5317 5318 processSQLNamePattern(pset.db, &buf, pattern, false, false, 5319 NULL, "s.srvname", NULL, NULL); 5320 5321 appendPQExpBufferStr(&buf, "ORDER BY 1;"); 5322 5323 res = PSQLexec(buf.data); 5324 termPQExpBuffer(&buf); 5325 if (!res) 5326 return false; 5327 5328 myopt.nullPrint = NULL; 5329 myopt.title = _("List of foreign servers"); 5330 myopt.translate_header = true; 5331 5332 printQuery(res, &myopt, pset.queryFout, false, pset.logfile); 5333 5334 PQclear(res); 5335 return true; 5336 } 5337 5338 /* 5339 * \deu 5340 * 5341 * Describes user mappings. 5342 */ 5343 bool 5344 listUserMappings(const char *pattern, bool verbose) 5345 { 5346 PQExpBufferData buf; 5347 PGresult *res; 5348 printQueryOpt myopt = pset.popt; 5349 5350 if (pset.sversion < 80400) 5351 { 5352 char sverbuf[32]; 5353 5354 pg_log_error("The server (version %s) does not support user mappings.", 5355 formatPGVersionNumber(pset.sversion, false, 5356 sverbuf, sizeof(sverbuf))); 5357 return true; 5358 } 5359 5360 initPQExpBuffer(&buf); 5361 printfPQExpBuffer(&buf, 5362 "SELECT um.srvname AS \"%s\",\n" 5363 " um.usename AS \"%s\"", 5364 gettext_noop("Server"), 5365 gettext_noop("User name")); 5366 5367 if (verbose) 5368 appendPQExpBuffer(&buf, 5369 ",\n CASE WHEN umoptions IS NULL THEN '' ELSE " 5370 " '(' || pg_catalog.array_to_string(ARRAY(SELECT " 5371 " pg_catalog.quote_ident(option_name) || ' ' || " 5372 " pg_catalog.quote_literal(option_value) FROM " 5373 " pg_catalog.pg_options_to_table(umoptions)), ', ') || ')' " 5374 " END AS \"%s\"", 5375 gettext_noop("FDW options")); 5376 5377 appendPQExpBufferStr(&buf, "\nFROM pg_catalog.pg_user_mappings um\n"); 5378 5379 processSQLNamePattern(pset.db, &buf, pattern, false, false, 5380 NULL, "um.srvname", "um.usename", NULL); 5381 5382 appendPQExpBufferStr(&buf, "ORDER BY 1, 2;"); 5383 5384 res = PSQLexec(buf.data); 5385 termPQExpBuffer(&buf); 5386 if (!res) 5387 return false; 5388 5389 myopt.nullPrint = NULL; 5390 myopt.title = _("List of user mappings"); 5391 myopt.translate_header = true; 5392 5393 printQuery(res, &myopt, pset.queryFout, false, pset.logfile); 5394 5395 PQclear(res); 5396 return true; 5397 } 5398 5399 /* 5400 * \det 5401 * 5402 * Describes foreign tables. 5403 */ 5404 bool 5405 listForeignTables(const char *pattern, bool verbose) 5406 { 5407 PQExpBufferData buf; 5408 PGresult *res; 5409 printQueryOpt myopt = pset.popt; 5410 5411 if (pset.sversion < 90100) 5412 { 5413 char sverbuf[32]; 5414 5415 pg_log_error("The server (version %s) does not support foreign tables.", 5416 formatPGVersionNumber(pset.sversion, false, 5417 sverbuf, sizeof(sverbuf))); 5418 return true; 5419 } 5420 5421 initPQExpBuffer(&buf); 5422 printfPQExpBuffer(&buf, 5423 "SELECT n.nspname AS \"%s\",\n" 5424 " c.relname AS \"%s\",\n" 5425 " s.srvname AS \"%s\"", 5426 gettext_noop("Schema"), 5427 gettext_noop("Table"), 5428 gettext_noop("Server")); 5429 5430 if (verbose) 5431 appendPQExpBuffer(&buf, 5432 ",\n CASE WHEN ftoptions IS NULL THEN '' ELSE " 5433 " '(' || pg_catalog.array_to_string(ARRAY(SELECT " 5434 " pg_catalog.quote_ident(option_name) || ' ' || " 5435 " pg_catalog.quote_literal(option_value) FROM " 5436 " pg_catalog.pg_options_to_table(ftoptions)), ', ') || ')' " 5437 " END AS \"%s\",\n" 5438 " d.description AS \"%s\"", 5439 gettext_noop("FDW options"), 5440 gettext_noop("Description")); 5441 5442 appendPQExpBufferStr(&buf, 5443 "\nFROM pg_catalog.pg_foreign_table ft\n" 5444 " INNER JOIN pg_catalog.pg_class c" 5445 " ON c.oid = ft.ftrelid\n" 5446 " INNER JOIN pg_catalog.pg_namespace n" 5447 " ON n.oid = c.relnamespace\n" 5448 " INNER JOIN pg_catalog.pg_foreign_server s" 5449 " ON s.oid = ft.ftserver\n"); 5450 if (verbose) 5451 appendPQExpBufferStr(&buf, 5452 " LEFT JOIN pg_catalog.pg_description d\n" 5453 " ON d.classoid = c.tableoid AND " 5454 "d.objoid = c.oid AND d.objsubid = 0\n"); 5455 5456 processSQLNamePattern(pset.db, &buf, pattern, false, false, 5457 "n.nspname", "c.relname", NULL, 5458 "pg_catalog.pg_table_is_visible(c.oid)"); 5459 5460 appendPQExpBufferStr(&buf, "ORDER BY 1, 2;"); 5461 5462 res = PSQLexec(buf.data); 5463 termPQExpBuffer(&buf); 5464 if (!res) 5465 return false; 5466 5467 myopt.nullPrint = NULL; 5468 myopt.title = _("List of foreign tables"); 5469 myopt.translate_header = true; 5470 5471 printQuery(res, &myopt, pset.queryFout, false, pset.logfile); 5472 5473 PQclear(res); 5474 return true; 5475 } 5476 5477 /* 5478 * \dx 5479 * 5480 * Briefly describes installed extensions. 5481 */ 5482 bool 5483 listExtensions(const char *pattern) 5484 { 5485 PQExpBufferData buf; 5486 PGresult *res; 5487 printQueryOpt myopt = pset.popt; 5488 5489 if (pset.sversion < 90100) 5490 { 5491 char sverbuf[32]; 5492 5493 pg_log_error("The server (version %s) does not support extensions.", 5494 formatPGVersionNumber(pset.sversion, false, 5495 sverbuf, sizeof(sverbuf))); 5496 return true; 5497 } 5498 5499 initPQExpBuffer(&buf); 5500 printfPQExpBuffer(&buf, 5501 "SELECT e.extname AS \"%s\", " 5502 "e.extversion AS \"%s\", n.nspname AS \"%s\", c.description AS \"%s\"\n" 5503 "FROM pg_catalog.pg_extension e " 5504 "LEFT JOIN pg_catalog.pg_namespace n ON n.oid = e.extnamespace " 5505 "LEFT JOIN pg_catalog.pg_description c ON c.objoid = e.oid " 5506 "AND c.classoid = 'pg_catalog.pg_extension'::pg_catalog.regclass\n", 5507 gettext_noop("Name"), 5508 gettext_noop("Version"), 5509 gettext_noop("Schema"), 5510 gettext_noop("Description")); 5511 5512 processSQLNamePattern(pset.db, &buf, pattern, 5513 false, false, 5514 NULL, "e.extname", NULL, 5515 NULL); 5516 5517 appendPQExpBufferStr(&buf, "ORDER BY 1;"); 5518 5519 res = PSQLexec(buf.data); 5520 termPQExpBuffer(&buf); 5521 if (!res) 5522 return false; 5523 5524 myopt.nullPrint = NULL; 5525 myopt.title = _("List of installed extensions"); 5526 myopt.translate_header = true; 5527 5528 printQuery(res, &myopt, pset.queryFout, false, pset.logfile); 5529 5530 PQclear(res); 5531 return true; 5532 } 5533 5534 /* 5535 * \dx+ 5536 * 5537 * List contents of installed extensions. 5538 */ 5539 bool 5540 listExtensionContents(const char *pattern) 5541 { 5542 PQExpBufferData buf; 5543 PGresult *res; 5544 int i; 5545 5546 if (pset.sversion < 90100) 5547 { 5548 char sverbuf[32]; 5549 5550 pg_log_error("The server (version %s) does not support extensions.", 5551 formatPGVersionNumber(pset.sversion, false, 5552 sverbuf, sizeof(sverbuf))); 5553 return true; 5554 } 5555 5556 initPQExpBuffer(&buf); 5557 printfPQExpBuffer(&buf, 5558 "SELECT e.extname, e.oid\n" 5559 "FROM pg_catalog.pg_extension e\n"); 5560 5561 processSQLNamePattern(pset.db, &buf, pattern, 5562 false, false, 5563 NULL, "e.extname", NULL, 5564 NULL); 5565 5566 appendPQExpBufferStr(&buf, "ORDER BY 1;"); 5567 5568 res = PSQLexec(buf.data); 5569 termPQExpBuffer(&buf); 5570 if (!res) 5571 return false; 5572 5573 if (PQntuples(res) == 0) 5574 { 5575 if (!pset.quiet) 5576 { 5577 if (pattern) 5578 pg_log_error("Did not find any extension named \"%s\".", 5579 pattern); 5580 else 5581 pg_log_error("Did not find any extensions."); 5582 } 5583 PQclear(res); 5584 return false; 5585 } 5586 5587 for (i = 0; i < PQntuples(res); i++) 5588 { 5589 const char *extname; 5590 const char *oid; 5591 5592 extname = PQgetvalue(res, i, 0); 5593 oid = PQgetvalue(res, i, 1); 5594 5595 if (!listOneExtensionContents(extname, oid)) 5596 { 5597 PQclear(res); 5598 return false; 5599 } 5600 if (cancel_pressed) 5601 { 5602 PQclear(res); 5603 return false; 5604 } 5605 } 5606 5607 PQclear(res); 5608 return true; 5609 } 5610 5611 static bool 5612 listOneExtensionContents(const char *extname, const char *oid) 5613 { 5614 PQExpBufferData buf; 5615 PGresult *res; 5616 PQExpBufferData title; 5617 printQueryOpt myopt = pset.popt; 5618 5619 initPQExpBuffer(&buf); 5620 printfPQExpBuffer(&buf, 5621 "SELECT pg_catalog.pg_describe_object(classid, objid, 0) AS \"%s\"\n" 5622 "FROM pg_catalog.pg_depend\n" 5623 "WHERE refclassid = 'pg_catalog.pg_extension'::pg_catalog.regclass AND refobjid = '%s' AND deptype = 'e'\n" 5624 "ORDER BY 1;", 5625 gettext_noop("Object description"), 5626 oid); 5627 5628 res = PSQLexec(buf.data); 5629 termPQExpBuffer(&buf); 5630 if (!res) 5631 return false; 5632 5633 myopt.nullPrint = NULL; 5634 initPQExpBuffer(&title); 5635 printfPQExpBuffer(&title, _("Objects in extension \"%s\""), extname); 5636 myopt.title = title.data; 5637 myopt.translate_header = true; 5638 5639 printQuery(res, &myopt, pset.queryFout, false, pset.logfile); 5640 5641 termPQExpBuffer(&title); 5642 PQclear(res); 5643 return true; 5644 } 5645 5646 /* 5647 * \dRp 5648 * Lists publications. 5649 * 5650 * Takes an optional regexp to select particular publications 5651 */ 5652 bool 5653 listPublications(const char *pattern) 5654 { 5655 PQExpBufferData buf; 5656 PGresult *res; 5657 printQueryOpt myopt = pset.popt; 5658 static const bool translate_columns[] = {false, false, false, false, false, false, false}; 5659 5660 if (pset.sversion < 100000) 5661 { 5662 char sverbuf[32]; 5663 5664 pg_log_error("The server (version %s) does not support publications.", 5665 formatPGVersionNumber(pset.sversion, false, 5666 sverbuf, sizeof(sverbuf))); 5667 return true; 5668 } 5669 5670 initPQExpBuffer(&buf); 5671 5672 printfPQExpBuffer(&buf, 5673 "SELECT pubname AS \"%s\",\n" 5674 " pg_catalog.pg_get_userbyid(pubowner) AS \"%s\",\n" 5675 " puballtables AS \"%s\",\n" 5676 " pubinsert AS \"%s\",\n" 5677 " pubupdate AS \"%s\",\n" 5678 " pubdelete AS \"%s\"", 5679 gettext_noop("Name"), 5680 gettext_noop("Owner"), 5681 gettext_noop("All tables"), 5682 gettext_noop("Inserts"), 5683 gettext_noop("Updates"), 5684 gettext_noop("Deletes")); 5685 if (pset.sversion >= 110000) 5686 appendPQExpBuffer(&buf, 5687 ",\n pubtruncate AS \"%s\"", 5688 gettext_noop("Truncates")); 5689 5690 appendPQExpBufferStr(&buf, 5691 "\nFROM pg_catalog.pg_publication\n"); 5692 5693 processSQLNamePattern(pset.db, &buf, pattern, false, false, 5694 NULL, "pubname", NULL, 5695 NULL); 5696 5697 appendPQExpBufferStr(&buf, "ORDER BY 1;"); 5698 5699 res = PSQLexec(buf.data); 5700 termPQExpBuffer(&buf); 5701 if (!res) 5702 return false; 5703 5704 myopt.nullPrint = NULL; 5705 myopt.title = _("List of publications"); 5706 myopt.translate_header = true; 5707 myopt.translate_columns = translate_columns; 5708 myopt.n_translate_columns = lengthof(translate_columns); 5709 5710 printQuery(res, &myopt, pset.queryFout, false, pset.logfile); 5711 5712 PQclear(res); 5713 5714 return true; 5715 } 5716 5717 /* 5718 * \dRp+ 5719 * Describes publications including the contents. 5720 * 5721 * Takes an optional regexp to select particular publications 5722 */ 5723 bool 5724 describePublications(const char *pattern) 5725 { 5726 PQExpBufferData buf; 5727 int i; 5728 PGresult *res; 5729 bool has_pubtruncate; 5730 5731 if (pset.sversion < 100000) 5732 { 5733 char sverbuf[32]; 5734 5735 pg_log_error("The server (version %s) does not support publications.", 5736 formatPGVersionNumber(pset.sversion, false, 5737 sverbuf, sizeof(sverbuf))); 5738 return true; 5739 } 5740 5741 has_pubtruncate = (pset.sversion >= 110000); 5742 5743 initPQExpBuffer(&buf); 5744 5745 printfPQExpBuffer(&buf, 5746 "SELECT oid, pubname,\n" 5747 " pg_catalog.pg_get_userbyid(pubowner) AS owner,\n" 5748 " puballtables, pubinsert, pubupdate, pubdelete"); 5749 if (has_pubtruncate) 5750 appendPQExpBuffer(&buf, 5751 ", pubtruncate"); 5752 appendPQExpBuffer(&buf, 5753 "\nFROM pg_catalog.pg_publication\n"); 5754 5755 processSQLNamePattern(pset.db, &buf, pattern, false, false, 5756 NULL, "pubname", NULL, 5757 NULL); 5758 5759 appendPQExpBufferStr(&buf, "ORDER BY 2;"); 5760 5761 res = PSQLexec(buf.data); 5762 if (!res) 5763 { 5764 termPQExpBuffer(&buf); 5765 return false; 5766 } 5767 5768 if (PQntuples(res) == 0) 5769 { 5770 if (!pset.quiet) 5771 { 5772 if (pattern) 5773 pg_log_error("Did not find any publication named \"%s\".", 5774 pattern); 5775 else 5776 pg_log_error("Did not find any publications."); 5777 } 5778 5779 termPQExpBuffer(&buf); 5780 PQclear(res); 5781 return false; 5782 } 5783 5784 for (i = 0; i < PQntuples(res); i++) 5785 { 5786 const char align = 'l'; 5787 int ncols = 5; 5788 int nrows = 1; 5789 int tables = 0; 5790 PGresult *tabres; 5791 char *pubid = PQgetvalue(res, i, 0); 5792 char *pubname = PQgetvalue(res, i, 1); 5793 bool puballtables = strcmp(PQgetvalue(res, i, 3), "t") == 0; 5794 int j; 5795 PQExpBufferData title; 5796 printTableOpt myopt = pset.popt.topt; 5797 printTableContent cont; 5798 5799 if (has_pubtruncate) 5800 ncols++; 5801 5802 initPQExpBuffer(&title); 5803 printfPQExpBuffer(&title, _("Publication %s"), pubname); 5804 printTableInit(&cont, &myopt, title.data, ncols, nrows); 5805 5806 printTableAddHeader(&cont, gettext_noop("Owner"), true, align); 5807 printTableAddHeader(&cont, gettext_noop("All tables"), true, align); 5808 printTableAddHeader(&cont, gettext_noop("Inserts"), true, align); 5809 printTableAddHeader(&cont, gettext_noop("Updates"), true, align); 5810 printTableAddHeader(&cont, gettext_noop("Deletes"), true, align); 5811 if (has_pubtruncate) 5812 printTableAddHeader(&cont, gettext_noop("Truncates"), true, align); 5813 5814 printTableAddCell(&cont, PQgetvalue(res, i, 2), false, false); 5815 printTableAddCell(&cont, PQgetvalue(res, i, 3), false, false); 5816 printTableAddCell(&cont, PQgetvalue(res, i, 4), false, false); 5817 printTableAddCell(&cont, PQgetvalue(res, i, 5), false, false); 5818 printTableAddCell(&cont, PQgetvalue(res, i, 6), false, false); 5819 if (has_pubtruncate) 5820 printTableAddCell(&cont, PQgetvalue(res, i, 7), false, false); 5821 5822 if (!puballtables) 5823 { 5824 printfPQExpBuffer(&buf, 5825 "SELECT n.nspname, c.relname\n" 5826 "FROM pg_catalog.pg_class c,\n" 5827 " pg_catalog.pg_namespace n,\n" 5828 " pg_catalog.pg_publication_rel pr\n" 5829 "WHERE c.relnamespace = n.oid\n" 5830 " AND c.oid = pr.prrelid\n" 5831 " AND pr.prpubid = '%s'\n" 5832 "ORDER BY 1,2", pubid); 5833 5834 tabres = PSQLexec(buf.data); 5835 if (!tabres) 5836 { 5837 printTableCleanup(&cont); 5838 PQclear(res); 5839 termPQExpBuffer(&buf); 5840 termPQExpBuffer(&title); 5841 return false; 5842 } 5843 else 5844 tables = PQntuples(tabres); 5845 5846 if (tables > 0) 5847 printTableAddFooter(&cont, _("Tables:")); 5848 5849 for (j = 0; j < tables; j++) 5850 { 5851 printfPQExpBuffer(&buf, " \"%s.%s\"", 5852 PQgetvalue(tabres, j, 0), 5853 PQgetvalue(tabres, j, 1)); 5854 5855 printTableAddFooter(&cont, buf.data); 5856 } 5857 PQclear(tabres); 5858 } 5859 5860 printTable(&cont, pset.queryFout, false, pset.logfile); 5861 printTableCleanup(&cont); 5862 5863 termPQExpBuffer(&title); 5864 } 5865 5866 termPQExpBuffer(&buf); 5867 PQclear(res); 5868 5869 return true; 5870 } 5871 5872 /* 5873 * \dRs 5874 * Describes subscriptions. 5875 * 5876 * Takes an optional regexp to select particular subscriptions 5877 */ 5878 bool 5879 describeSubscriptions(const char *pattern, bool verbose) 5880 { 5881 PQExpBufferData buf; 5882 PGresult *res; 5883 printQueryOpt myopt = pset.popt; 5884 static const bool translate_columns[] = {false, false, false, false, 5885 false, false}; 5886 5887 if (pset.sversion < 100000) 5888 { 5889 char sverbuf[32]; 5890 5891 pg_log_error("The server (version %s) does not support subscriptions.", 5892 formatPGVersionNumber(pset.sversion, false, 5893 sverbuf, sizeof(sverbuf))); 5894 return true; 5895 } 5896 5897 initPQExpBuffer(&buf); 5898 5899 printfPQExpBuffer(&buf, 5900 "SELECT subname AS \"%s\"\n" 5901 ", pg_catalog.pg_get_userbyid(subowner) AS \"%s\"\n" 5902 ", subenabled AS \"%s\"\n" 5903 ", subpublications AS \"%s\"\n", 5904 gettext_noop("Name"), 5905 gettext_noop("Owner"), 5906 gettext_noop("Enabled"), 5907 gettext_noop("Publication")); 5908 5909 if (verbose) 5910 { 5911 appendPQExpBuffer(&buf, 5912 ", subsynccommit AS \"%s\"\n" 5913 ", subconninfo AS \"%s\"\n", 5914 gettext_noop("Synchronous commit"), 5915 gettext_noop("Conninfo")); 5916 } 5917 5918 /* Only display subscriptions in current database. */ 5919 appendPQExpBufferStr(&buf, 5920 "FROM pg_catalog.pg_subscription\n" 5921 "WHERE subdbid = (SELECT oid\n" 5922 " FROM pg_catalog.pg_database\n" 5923 " WHERE datname = pg_catalog.current_database())"); 5924 5925 processSQLNamePattern(pset.db, &buf, pattern, true, false, 5926 NULL, "subname", NULL, 5927 NULL); 5928 5929 appendPQExpBufferStr(&buf, "ORDER BY 1;"); 5930 5931 res = PSQLexec(buf.data); 5932 termPQExpBuffer(&buf); 5933 if (!res) 5934 return false; 5935 5936 myopt.nullPrint = NULL; 5937 myopt.title = _("List of subscriptions"); 5938 myopt.translate_header = true; 5939 myopt.translate_columns = translate_columns; 5940 myopt.n_translate_columns = lengthof(translate_columns); 5941 5942 printQuery(res, &myopt, pset.queryFout, false, pset.logfile); 5943 5944 PQclear(res); 5945 return true; 5946 } 5947 5948 /* 5949 * printACLColumn 5950 * 5951 * Helper function for consistently formatting ACL (privilege) columns. 5952 * The proper targetlist entry is appended to buf. Note lack of any 5953 * whitespace or comma decoration. 5954 */ 5955 static void 5956 printACLColumn(PQExpBuffer buf, const char *colname) 5957 { 5958 if (pset.sversion >= 80100) 5959 appendPQExpBuffer(buf, 5960 "pg_catalog.array_to_string(%s, E'\\n') AS \"%s\"", 5961 colname, gettext_noop("Access privileges")); 5962 else 5963 appendPQExpBuffer(buf, 5964 "pg_catalog.array_to_string(%s, '\\n') AS \"%s\"", 5965 colname, gettext_noop("Access privileges")); 5966 } 5967