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