granicus.if.org Git - postgresql/blob - src/bin/psql/describe.c

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