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, false);
 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, false);
 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, false);
 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, false);
 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, false);
 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, false);
 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 rsecpolname\n"
 787                                                   "    || CASE WHEN rseccmd IS NOT NULL THEN\n"
 788                                                   "           E' (' || rseccmd || E')'\n"
 789                                                   "       ELSE E':' \n"
 790                                                   "       END\n"
 791                                                   "    || CASE WHEN rs.rsecqual IS NOT NULL THEN\n"
 792                                                   "           E'\\n  (u): ' || pg_catalog.pg_get_expr(rsecqual, rsecrelid)\n"
 793                                                   "       ELSE E''\n"
 794                                                   "       END\n"
 795                                                   "    || CASE WHEN rsecwithcheck IS NOT NULL THEN\n"
 796                                                   "           E'\\n  (c): ' || pg_catalog.pg_get_expr(rsecwithcheck, rsecrelid)\n"
 797                                                   "       ELSE E''\n"
 798                                                   "       END"
 799                                                   "    || CASE WHEN rs.rsecroles <> '{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 (rs.rsecroles)\n"
 805                                                   "                   ORDER BY 1\n"
 806                                                   "               ), E', ')\n"
 807                                                   "       ELSE E''\n"
 808                                                   "       END\n"
 809                                                   "    FROM pg_catalog.pg_rowsecurity rs\n"
 810                                                   "    WHERE rsecrelid = 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, false);
 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, false);
 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, false);
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, false);
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, false);
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, false);
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, false);
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, false);
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, false);
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, false);
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, false);
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, false);
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, false);
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, false);
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                         appendPQExpBuffer(&buf,
2005                                 ",\n pg_catalog.pg_get_expr(rs.rsecqual, c.oid) as \"%s\"",
2006                                 gettext_noop("Row-security"));
2007
2008                         if (verbose)
2009                                 appendPQExpBuffer(&buf,
2010                                         "\n     LEFT JOIN pg_rowsecurity rs ON rs.rsecrelid = c.oid");
2011
2012                         printfPQExpBuffer(&buf,
2013                                                    "SELECT rs.rsecpolname,\n"
2014                                                    "CASE WHEN rs.rsecroles = '{0}' THEN NULL ELSE array_to_string(array(select rolname from pg_roles where oid = any (rs.rsecroles) order by 1),',') END,\n"
2015                                                    "pg_catalog.pg_get_expr(rs.rsecqual, rs.rsecrelid),\n"
2016                                                    "pg_catalog.pg_get_expr(rs.rsecwithcheck, rs.rsecrelid),\n"
2017                                                    "CASE rs.rseccmd \n"
2018                                                    "WHEN 'r' THEN 'SELECT'\n"
2019                                                    "WHEN 'u' THEN 'UPDATE'\n"
2020                                                    "WHEN 'a' THEN 'INSERT'\n"
2021                                                    "WHEN 'd' THEN 'DELETE'\n"
2022                                                    "END AS cmd\n"
2023                                                           "FROM pg_catalog.pg_rowsecurity rs\n"
2024                                   "WHERE rs.rsecrelid = '%s' ORDER BY 1;",
2025                                                           oid);
2026
2027                         result = PSQLexec(buf.data, false);
2028                         if (!result)
2029                                 goto error_return;
2030                         else
2031                                 tuples = PQntuples(result);
2032
2033                         /*
2034                          * Handle cases where RLS is enabled and there are policies,
2035                          * or there aren't policies, or RLS isn't enabled but there
2036                          * are policies
2037                          */
2038                         if (tableinfo.rowsecurity && tuples > 0)
2039                                 printTableAddFooter(&cont, _("Policies:"));
2040
2041                         if (tableinfo.rowsecurity && tuples == 0)
2042                                 printTableAddFooter(&cont, _("Policies (Row Security Enabled): (None)"));
2043
2044                         if (!tableinfo.rowsecurity && tuples > 0)
2045                                 printTableAddFooter(&cont, _("Policies (Row Security Disabled):"));
2046
2047                         /* Might be an empty set - that's ok */
2048                         for (i = 0; i < tuples; i++)
2049                         {
2050                                 printfPQExpBuffer(&buf, "    POLICY \"%s\"",
2051                                                                           PQgetvalue(result, i, 0));
2052
2053                                 if (!PQgetisnull(result, i, 4))
2054                                         appendPQExpBuffer(&buf, " FOR %s",
2055                                                                           PQgetvalue(result, i, 4));
2056
2057                                 if (!PQgetisnull(result, i, 1))
2058                                 {
2059                                         appendPQExpBuffer(&buf, "\n      TO %s",
2060                                                                           PQgetvalue(result, i, 1));
2061                                 }
2062
2063                                 if (!PQgetisnull(result, i, 2))
2064                                         appendPQExpBuffer(&buf, "\n      USING %s",
2065                                                                           PQgetvalue(result, i, 2));
2066
2067                                 if (!PQgetisnull(result, i, 3))
2068                                         appendPQExpBuffer(&buf, "\n      WITH CHECK %s",
2069                                                                           PQgetvalue(result, i, 3));
2070
2071                                 printTableAddFooter(&cont, buf.data);
2072
2073                         }
2074                         PQclear(result);
2075                 }
2076
2077                 /* print rules */
2078                 if (tableinfo.hasrules && tableinfo.relkind != 'm')
2079                 {
2080                         if (pset.sversion >= 80300)
2081                         {
2082                                 printfPQExpBuffer(&buf,
2083                                                                   "SELECT r.rulename, trim(trailing ';' from pg_catalog.pg_get_ruledef(r.oid, true)), "
2084                                                                   "ev_enabled\n"
2085                                                                   "FROM pg_catalog.pg_rewrite r\n"
2086                                                                   "WHERE r.ev_class = '%s' ORDER BY 1;",
2087                                                                   oid);
2088                         }
2089                         else
2090                         {
2091                                 printfPQExpBuffer(&buf,
2092                                                                   "SELECT r.rulename, trim(trailing ';' from pg_catalog.pg_get_ruledef(r.oid, true)), "
2093                                                                   "'O'::char AS ev_enabled\n"
2094                                                                   "FROM pg_catalog.pg_rewrite r\n"
2095                                                                   "WHERE r.ev_class = '%s' ORDER BY 1;",
2096                                                                   oid);
2097                         }
2098                         result = PSQLexec(buf.data, false);
2099                         if (!result)
2100                                 goto error_return;
2101                         else
2102                                 tuples = PQntuples(result);
2103
2104                         if (tuples > 0)
2105                         {
2106                                 bool            have_heading;
2107                                 int                     category;
2108
2109                                 for (category = 0; category < 4; category++)
2110                                 {
2111                                         have_heading = false;
2112
2113                                         for (i = 0; i < tuples; i++)
2114                                         {
2115                                                 const char *ruledef;
2116                                                 bool            list_rule = false;
2117
2118                                                 switch (category)
2119                                                 {
2120                                                         case 0:
2121                                                                 if (*PQgetvalue(result, i, 2) == 'O')
2122                                                                         list_rule = true;
2123                                                                 break;
2124                                                         case 1:
2125                                                                 if (*PQgetvalue(result, i, 2) == 'D')
2126                                                                         list_rule = true;
2127                                                                 break;
2128                                                         case 2:
2129                                                                 if (*PQgetvalue(result, i, 2) == 'A')
2130                                                                         list_rule = true;
2131                                                                 break;
2132                                                         case 3:
2133                                                                 if (*PQgetvalue(result, i, 2) == 'R')
2134                                                                         list_rule = true;
2135                                                                 break;
2136                                                 }
2137                                                 if (!list_rule)
2138                                                         continue;
2139
2140                                                 if (!have_heading)
2141                                                 {
2142                                                         switch (category)
2143                                                         {
2144                                                                 case 0:
2145                                                                         printfPQExpBuffer(&buf, _("Rules:"));
2146                                                                         break;
2147                                                                 case 1:
2148                                                                         printfPQExpBuffer(&buf, _("Disabled rules:"));
2149                                                                         break;
2150                                                                 case 2:
2151                                                                         printfPQExpBuffer(&buf, _("Rules firing always:"));
2152                                                                         break;
2153                                                                 case 3:
2154                                                                         printfPQExpBuffer(&buf, _("Rules firing on replica only:"));
2155                                                                         break;
2156                                                         }
2157                                                         printTableAddFooter(&cont, buf.data);
2158                                                         have_heading = true;
2159                                                 }
2160
2161                                                 /* Everything after "CREATE RULE" is echoed verbatim */
2162                                                 ruledef = PQgetvalue(result, i, 1);
2163                                                 ruledef += 12;
2164                                                 printfPQExpBuffer(&buf, "    %s", ruledef);
2165                                                 printTableAddFooter(&cont, buf.data);
2166                                         }
2167                                 }
2168                         }
2169                         PQclear(result);
2170                 }
2171         }
2172
2173         if (view_def)
2174         {
2175                 PGresult   *result = NULL;
2176
2177                 /* Footer information about a view */
2178                 printTableAddFooter(&cont, _("View definition:"));
2179                 printTableAddFooter(&cont, view_def);
2180
2181                 /* print rules */
2182                 if (tableinfo.hasrules)
2183                 {
2184                         printfPQExpBuffer(&buf,
2185                                                           "SELECT r.rulename, trim(trailing ';' from pg_catalog.pg_get_ruledef(r.oid, true))\n"
2186                                                           "FROM pg_catalog.pg_rewrite r\n"
2187                         "WHERE r.ev_class = '%s' AND r.rulename != '_RETURN' ORDER BY 1;",
2188                                                           oid);
2189                         result = PSQLexec(buf.data, false);
2190                         if (!result)
2191                                 goto error_return;
2192
2193                         if (PQntuples(result) > 0)
2194                         {
2195                                 printTableAddFooter(&cont, _("Rules:"));
2196                                 for (i = 0; i < PQntuples(result); i++)
2197                                 {
2198                                         const char *ruledef;
2199
2200                                         /* Everything after "CREATE RULE" is echoed verbatim */
2201                                         ruledef = PQgetvalue(result, i, 1);
2202                                         ruledef += 12;
2203
2204                                         printfPQExpBuffer(&buf, " %s", ruledef);
2205                                         printTableAddFooter(&cont, buf.data);
2206                                 }
2207                         }
2208                         PQclear(result);
2209                 }
2210         }
2211
2212         /*
2213          * Print triggers next, if any (but only user-defined triggers).  This
2214          * could apply to either a table or a view.
2215          */
2216         if (tableinfo.hastriggers)
2217         {
2218                 PGresult   *result;
2219                 int                     tuples;
2220
2221                 printfPQExpBuffer(&buf,
2222                                                   "SELECT t.tgname, "
2223                                                   "pg_catalog.pg_get_triggerdef(t.oid%s), "
2224                                                   "t.tgenabled, %s\n"
2225                                                   "FROM pg_catalog.pg_trigger t\n"
2226                                                   "WHERE t.tgrelid = '%s' AND ",
2227                                                   (pset.sversion >= 90000 ? ", true" : ""),
2228                                                   (pset.sversion >= 90000 ? "t.tgisinternal" :
2229                                                    pset.sversion >= 80300 ?
2230                                                    "t.tgconstraint <> 0 AS tgisinternal" :
2231                                                    "false AS tgisinternal"), oid);
2232                 if (pset.sversion >= 90000)
2233                         /* display/warn about disabled internal triggers */
2234                         appendPQExpBuffer(&buf, "(NOT t.tgisinternal OR (t.tgisinternal AND t.tgenabled = 'D'))");
2235                 else if (pset.sversion >= 80300)
2236                         appendPQExpBufferStr(&buf, "(t.tgconstraint = 0 OR (t.tgconstraint <> 0 AND t.tgenabled = 'D'))");
2237                 else
2238                         appendPQExpBufferStr(&buf,
2239                                                                  "(NOT tgisconstraint "
2240                                                                  " OR NOT EXISTS"
2241                                                                  "  (SELECT 1 FROM pg_catalog.pg_depend d "
2242                                                                  "   JOIN pg_catalog.pg_constraint c ON (d.refclassid = c.tableoid AND d.refobjid = c.oid) "
2243                                                                  "   WHERE d.classid = t.tableoid AND d.objid = t.oid AND d.deptype = 'i' AND c.contype = 'f'))");
2244                 appendPQExpBufferStr(&buf, "\nORDER BY 1;");
2245
2246                 result = PSQLexec(buf.data, false);
2247                 if (!result)
2248                         goto error_return;
2249                 else
2250                         tuples = PQntuples(result);
2251
2252                 if (tuples > 0)
2253                 {
2254                         bool            have_heading;
2255                         int                     category;
2256
2257                         /*
2258                          * split the output into 4 different categories. Enabled triggers,
2259                          * disabled triggers and the two special ALWAYS and REPLICA
2260                          * configurations.
2261                          */
2262                         for (category = 0; category <= 4; category++)
2263                         {
2264                                 have_heading = false;
2265                                 for (i = 0; i < tuples; i++)
2266                                 {
2267                                         bool            list_trigger;
2268                                         const char *tgdef;
2269                                         const char *usingpos;
2270                                         const char *tgenabled;
2271                                         const char *tgisinternal;
2272
2273                                         /*
2274                                          * Check if this trigger falls into the current category
2275                                          */
2276                                         tgenabled = PQgetvalue(result, i, 2);
2277                                         tgisinternal = PQgetvalue(result, i, 3);
2278                                         list_trigger = false;
2279                                         switch (category)
2280                                         {
2281                                                 case 0:
2282                                                         if (*tgenabled == 'O' || *tgenabled == 't')
2283                                                                 list_trigger = true;
2284                                                         break;
2285                                                 case 1:
2286                                                         if ((*tgenabled == 'D' || *tgenabled == 'f') &&
2287                                                                 *tgisinternal == 'f')
2288                                                                 list_trigger = true;
2289                                                         break;
2290                                                 case 2:
2291                                                         if ((*tgenabled == 'D' || *tgenabled == 'f') &&
2292                                                                 *tgisinternal == 't')
2293                                                                 list_trigger = true;
2294                                                         break;
2295                                                 case 3:
2296                                                         if (*tgenabled == 'A')
2297                                                                 list_trigger = true;
2298                                                         break;
2299                                                 case 4:
2300                                                         if (*tgenabled == 'R')
2301                                                                 list_trigger = true;
2302                                                         break;
2303                                         }
2304                                         if (list_trigger == false)
2305                                                 continue;
2306
2307                                         /* Print the category heading once */
2308                                         if (have_heading == false)
2309                                         {
2310                                                 switch (category)
2311                                                 {
2312                                                         case 0:
2313                                                                 printfPQExpBuffer(&buf, _("Triggers:"));
2314                                                                 break;
2315                                                         case 1:
2316                                                                 if (pset.sversion >= 80300)
2317                                                                         printfPQExpBuffer(&buf, _("Disabled user triggers:"));
2318                                                                 else
2319                                                                         printfPQExpBuffer(&buf, _("Disabled triggers:"));
2320                                                                 break;
2321                                                         case 2:
2322                                                                 printfPQExpBuffer(&buf, _("Disabled internal triggers:"));
2323                                                                 break;
2324                                                         case 3:
2325                                                                 printfPQExpBuffer(&buf, _("Triggers firing always:"));
2326                                                                 break;
2327                                                         case 4:
2328                                                                 printfPQExpBuffer(&buf, _("Triggers firing on replica only:"));
2329                                                                 break;
2330
2331                                                 }
2332                                                 printTableAddFooter(&cont, buf.data);
2333                                                 have_heading = true;
2334                                         }
2335
2336                                         /* Everything after "TRIGGER" is echoed verbatim */
2337                                         tgdef = PQgetvalue(result, i, 1);
2338                                         usingpos = strstr(tgdef, " TRIGGER ");
2339                                         if (usingpos)
2340                                                 tgdef = usingpos + 9;
2341
2342                                         printfPQExpBuffer(&buf, "    %s", tgdef);
2343                                         printTableAddFooter(&cont, buf.data);
2344                                 }
2345                         }
2346                 }
2347                 PQclear(result);
2348         }
2349
2350         /*
2351          * Finish printing the footer information about a table.
2352          */
2353         if (tableinfo.relkind == 'r' || tableinfo.relkind == 'm' ||
2354                 tableinfo.relkind == 'f')
2355         {
2356                 PGresult   *result;
2357                 int                     tuples;
2358
2359                 /* print foreign server name */
2360                 if (tableinfo.relkind == 'f')
2361                 {
2362                         char       *ftoptions;
2363
2364                         /* Footer information about foreign table */
2365                         printfPQExpBuffer(&buf,
2366                                                           "SELECT s.srvname,\n"
2367                                                           "       array_to_string(ARRAY(SELECT "
2368                                                           "       quote_ident(option_name) ||  ' ' || "
2369                                                           "       quote_literal(option_value)  FROM "
2370                                                         "       pg_options_to_table(ftoptions)),  ', ') "
2371                                                           "FROM pg_catalog.pg_foreign_table f,\n"
2372                                                           "     pg_catalog.pg_foreign_server s\n"
2373                                                           "WHERE f.ftrelid = %s AND s.oid = f.ftserver;",
2374                                                           oid);
2375                         result = PSQLexec(buf.data, false);
2376                         if (!result)
2377                                 goto error_return;
2378                         else if (PQntuples(result) != 1)
2379                         {
2380                                 PQclear(result);
2381                                 goto error_return;
2382                         }
2383
2384                         /* Print server name */
2385                         printfPQExpBuffer(&buf, "Server: %s",
2386                                                           PQgetvalue(result, 0, 0));
2387                         printTableAddFooter(&cont, buf.data);
2388
2389                         /* Print per-table FDW options, if any */
2390                         ftoptions = PQgetvalue(result, 0, 1);
2391                         if (ftoptions && ftoptions[0] != '\0')
2392                         {
2393                                 printfPQExpBuffer(&buf, "FDW Options: (%s)", ftoptions);
2394                                 printTableAddFooter(&cont, buf.data);
2395                         }
2396                         PQclear(result);
2397                 }
2398
2399                 /* print inherited tables */
2400                 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);
2401
2402                 result = PSQLexec(buf.data, false);
2403                 if (!result)
2404                         goto error_return;
2405                 else
2406                 {
2407                         const char *s = _("Inherits");
2408                         int                     sw = pg_wcswidth(s, strlen(s), pset.encoding);
2409
2410                         tuples = PQntuples(result);
2411
2412                         for (i = 0; i < tuples; i++)
2413                         {
2414                                 if (i == 0)
2415                                         printfPQExpBuffer(&buf, "%s: %s",
2416                                                                           s, PQgetvalue(result, i, 0));
2417                                 else
2418                                         printfPQExpBuffer(&buf, "%*s  %s",
2419                                                                           sw, "", PQgetvalue(result, i, 0));
2420                                 if (i < tuples - 1)
2421                                         appendPQExpBufferStr(&buf, ",");
2422
2423                                 printTableAddFooter(&cont, buf.data);
2424                         }
2425
2426                         PQclear(result);
2427                 }
2428
2429                 /* print child tables */
2430                 if (pset.sversion >= 80300)
2431                         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);
2432                 else
2433                         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);
2434
2435                 result = PSQLexec(buf.data, false);
2436                 if (!result)
2437                         goto error_return;
2438                 else
2439                         tuples = PQntuples(result);
2440
2441                 if (!verbose)
2442                 {
2443                         /* print the number of child tables, if any */
2444                         if (tuples > 0)
2445                         {
2446                                 printfPQExpBuffer(&buf, _("Number of child tables: %d (Use \\d+ to list them.)"), tuples);
2447                                 printTableAddFooter(&cont, buf.data);
2448                         }
2449                 }
2450                 else
2451                 {
2452                         /* display the list of child tables */
2453                         const char *ct = _("Child tables");
2454                         int                     ctw = pg_wcswidth(ct, strlen(ct), pset.encoding);
2455
2456                         for (i = 0; i < tuples; i++)
2457                         {
2458                                 if (i == 0)
2459                                         printfPQExpBuffer(&buf, "%s: %s",
2460                                                                           ct, PQgetvalue(result, i, 0));
2461                                 else
2462                                         printfPQExpBuffer(&buf, "%*s  %s",
2463                                                                           ctw, "", PQgetvalue(result, i, 0));
2464                                 if (i < tuples - 1)
2465                                         appendPQExpBufferChar(&buf, ',');
2466
2467                                 printTableAddFooter(&cont, buf.data);
2468                         }
2469                 }
2470                 PQclear(result);
2471
2472                 /* Table type */
2473                 if (tableinfo.reloftype)
2474                 {
2475                         printfPQExpBuffer(&buf, _("Typed table of type: %s"), tableinfo.reloftype);
2476                         printTableAddFooter(&cont, buf.data);
2477                 }
2478
2479                 if (verbose && (tableinfo.relkind == 'r' || tableinfo.relkind == 'm') &&
2480
2481                 /*
2482                  * No need to display default values;  we already display a REPLICA
2483                  * IDENTITY marker on indexes.
2484                  */
2485                         tableinfo.relreplident != 'i' &&
2486                         ((strcmp(schemaname, "pg_catalog") != 0 && tableinfo.relreplident != 'd') ||
2487                          (strcmp(schemaname, "pg_catalog") == 0 && tableinfo.relreplident != 'n')))
2488                 {
2489                         const char *s = _("Replica Identity");
2490
2491                         printfPQExpBuffer(&buf, "%s: %s",
2492                                                           s,
2493                                                           tableinfo.relreplident == 'f' ? "FULL" :
2494                                                           tableinfo.relreplident == 'n' ? "NOTHING" :
2495                                                           "???");
2496
2497                         printTableAddFooter(&cont, buf.data);
2498                 }
2499
2500                 /* OIDs, if verbose and not a materialized view */
2501                 if (verbose && tableinfo.relkind != 'm' && tableinfo.hasoids)
2502                         printTableAddFooter(&cont, _("Has OIDs: yes"));
2503
2504                 /* Tablespace info */
2505                 add_tablespace_footer(&cont, tableinfo.relkind, tableinfo.tablespace,
2506                                                           true);
2507         }
2508
2509         /* reloptions, if verbose */
2510         if (verbose &&
2511                 tableinfo.reloptions && tableinfo.reloptions[0] != '\0')
2512         {
2513                 const char *t = _("Options");
2514
2515                 printfPQExpBuffer(&buf, "%s: %s", t, tableinfo.reloptions);
2516                 printTableAddFooter(&cont, buf.data);
2517         }
2518
2519         printTable(&cont, pset.queryFout, pset.logfile);
2520         printTableCleanup(&cont);
2521
2522         retval = true;
2523
2524 error_return:
2525
2526         /* clean up */
2527         if (printTableInitialized)
2528                 printTableCleanup(&cont);
2529         termPQExpBuffer(&buf);
2530         termPQExpBuffer(&title);
2531         termPQExpBuffer(&tmpbuf);
2532
2533         if (seq_values)
2534         {
2535                 for (ptr = seq_values; *ptr; ptr++)
2536                         free(*ptr);
2537                 free(seq_values);
2538         }
2539
2540         if (modifiers)
2541         {
2542                 for (ptr = modifiers; *ptr; ptr++)
2543                         free(*ptr);
2544                 free(modifiers);
2545         }
2546
2547         if (view_def)
2548                 free(view_def);
2549
2550         if (res)
2551                 PQclear(res);
2552
2553         return retval;
2554 }
2555
2556 /*
2557  * Add a tablespace description to a footer.  If 'newline' is true, it is added
2558  * in a new line; otherwise it's appended to the current value of the last
2559  * footer.
2560  */
2561 static void
2562 add_tablespace_footer(printTableContent *const cont, char relkind,
2563                                           Oid tablespace, const bool newline)
2564 {
2565         /* relkinds for which we support tablespaces */
2566         if (relkind == 'r' || relkind == 'm' || relkind == 'i')
2567         {
2568                 /*
2569                  * We ignore the database default tablespace so that users not using
2570                  * tablespaces don't need to know about them.  This case also covers
2571                  * pre-8.0 servers, for which tablespace will always be 0.
2572                  */
2573                 if (tablespace != 0)
2574                 {
2575                         PGresult   *result = NULL;
2576                         PQExpBufferData buf;
2577
2578                         initPQExpBuffer(&buf);
2579                         printfPQExpBuffer(&buf,
2580                                                           "SELECT spcname FROM pg_catalog.pg_tablespace\n"
2581                                                           "WHERE oid = '%u';", tablespace);
2582                         result = PSQLexec(buf.data, false);
2583                         if (!result)
2584                                 return;
2585                         /* Should always be the case, but.... */
2586                         if (PQntuples(result) > 0)
2587                         {
2588                                 if (newline)
2589                                 {
2590                                         /* Add the tablespace as a new footer */
2591                                         printfPQExpBuffer(&buf, _("Tablespace: \"%s\""),
2592                                                                           PQgetvalue(result, 0, 0));
2593                                         printTableAddFooter(cont, buf.data);
2594                                 }
2595                                 else
2596                                 {
2597                                         /* Append the tablespace to the latest footer */
2598                                         printfPQExpBuffer(&buf, "%s", cont->footer->data);
2599
2600                                         /*-------
2601                                            translator: before this string there's an index description like
2602                                            '"foo_pkey" PRIMARY KEY, btree (a)' */
2603                                         appendPQExpBuffer(&buf, _(", tablespace \"%s\""),
2604                                                                           PQgetvalue(result, 0, 0));
2605                                         printTableSetFooter(cont, buf.data);
2606                                 }
2607                         }
2608                         PQclear(result);
2609                         termPQExpBuffer(&buf);
2610                 }
2611         }
2612 }
2613
2614 /*
2615  * \du or \dg
2616  *
2617  * Describes roles.  Any schema portion of the pattern is ignored.
2618  */
2619 bool
2620 describeRoles(const char *pattern, bool verbose)
2621 {
2622         PQExpBufferData buf;
2623         PGresult   *res;
2624         printTableContent cont;
2625         printTableOpt myopt = pset.popt.topt;
2626         int                     ncols = 3;
2627         int                     nrows = 0;
2628         int                     i;
2629         int                     conns;
2630         const char      align = 'l';
2631         char      **attr;
2632
2633         myopt.default_footer = false;
2634
2635         initPQExpBuffer(&buf);
2636
2637         if (pset.sversion >= 80100)
2638         {
2639                 printfPQExpBuffer(&buf,
2640                                                   "SELECT r.rolname, r.rolsuper, r.rolinherit,\n"
2641                                                   "  r.rolcreaterole, r.rolcreatedb, r.rolcanlogin,\n"
2642                                                   "  r.rolconnlimit, r.rolvaliduntil,\n"
2643                                                   "  ARRAY(SELECT b.rolname\n"
2644                                                   "        FROM pg_catalog.pg_auth_members m\n"
2645                                  "        JOIN pg_catalog.pg_roles b ON (m.roleid = b.oid)\n"
2646                                                   "        WHERE m.member = r.oid) as memberof");
2647
2648                 if (verbose && pset.sversion >= 80200)
2649                 {
2650                         appendPQExpBufferStr(&buf, "\n, pg_catalog.shobj_description(r.oid, 'pg_authid') AS description");
2651                         ncols++;
2652                 }
2653                 if (pset.sversion >= 90100)
2654                 {
2655                         appendPQExpBufferStr(&buf, "\n, r.rolreplication");
2656                 }
2657
2658                 if (pset.sversion >= 90500)
2659                 {
2660                         appendPQExpBufferStr(&buf, "\n, r.rolbypassrls");
2661                 }
2662
2663                 appendPQExpBufferStr(&buf, "\nFROM pg_catalog.pg_roles r\n");
2664
2665                 processSQLNamePattern(pset.db, &buf, pattern, false, false,
2666                                                           NULL, "r.rolname", NULL, NULL);
2667         }
2668         else
2669         {
2670                 printfPQExpBuffer(&buf,
2671                                                   "SELECT u.usename AS rolname,\n"
2672                                                   "  u.usesuper AS rolsuper,\n"
2673                                                   "  true AS rolinherit, false AS rolcreaterole,\n"
2674                                          "  u.usecreatedb AS rolcreatedb, true AS rolcanlogin,\n"
2675                                                   "  -1 AS rolconnlimit,"
2676                                                   "  u.valuntil as rolvaliduntil,\n"
2677                                                   "  ARRAY(SELECT g.groname FROM pg_catalog.pg_group g WHERE u.usesysid = ANY(g.grolist)) as memberof"
2678                                                   "\nFROM pg_catalog.pg_user u\n");
2679
2680                 processSQLNamePattern(pset.db, &buf, pattern, false, false,
2681                                                           NULL, "u.usename", NULL, NULL);
2682         }
2683
2684         appendPQExpBufferStr(&buf, "ORDER BY 1;");
2685
2686         res = PSQLexec(buf.data, false);
2687         if (!res)
2688                 return false;
2689
2690         nrows = PQntuples(res);
2691         attr = pg_malloc0((nrows + 1) * sizeof(*attr));
2692
2693         printTableInit(&cont, &myopt, _("List of roles"), ncols, nrows);
2694
2695         printTableAddHeader(&cont, gettext_noop("Role name"), true, align);
2696         printTableAddHeader(&cont, gettext_noop("Attributes"), true, align);
2697         printTableAddHeader(&cont, gettext_noop("Member of"), true, align);
2698
2699         if (verbose && pset.sversion >= 80200)
2700                 printTableAddHeader(&cont, gettext_noop("Description"), true, align);
2701
2702         for (i = 0; i < nrows; i++)
2703         {
2704                 printTableAddCell(&cont, PQgetvalue(res, i, 0), false, false);
2705
2706                 resetPQExpBuffer(&buf);
2707                 if (strcmp(PQgetvalue(res, i, 1), "t") == 0)
2708                         add_role_attribute(&buf, _("Superuser"));
2709
2710                 if (strcmp(PQgetvalue(res, i, 2), "t") != 0)
2711                         add_role_attribute(&buf, _("No inheritance"));
2712
2713                 if (strcmp(PQgetvalue(res, i, 3), "t") == 0)
2714                         add_role_attribute(&buf, _("Create role"));
2715
2716                 if (strcmp(PQgetvalue(res, i, 4), "t") == 0)
2717                         add_role_attribute(&buf, _("Create DB"));
2718
2719                 if (strcmp(PQgetvalue(res, i, 5), "t") != 0)
2720                         add_role_attribute(&buf, _("Cannot login"));
2721
2722                 if (pset.sversion >= 90100)
2723                         if (strcmp(PQgetvalue(res, i, (verbose ? 10 : 9)), "t") == 0)
2724                                 add_role_attribute(&buf, _("Replication"));
2725
2726                 if (pset.sversion >= 90500)
2727                         if (strcmp(PQgetvalue(res, i, (verbose ? 11 : 10)), "t") == 0)
2728                                 add_role_attribute(&buf, _("Bypass RLS"));
2729
2730                 conns = atoi(PQgetvalue(res, i, 6));
2731                 if (conns >= 0)
2732                 {
2733                         if (buf.len > 0)
2734                                 appendPQExpBufferChar(&buf, '\n');
2735
2736                         if (conns == 0)
2737                                 appendPQExpBufferStr(&buf, _("No connections"));
2738                         else
2739                                 appendPQExpBuffer(&buf, ngettext("%d connection",
2740                                                                                                  "%d connections",
2741                                                                                                  conns),
2742                                                                   conns);
2743                 }
2744
2745                 if (strcmp(PQgetvalue(res, i, 7), "") != 0)
2746                 {
2747                         if (buf.len > 0)
2748                                 appendPQExpBufferStr(&buf, "\n");
2749                         appendPQExpBufferStr(&buf, _("Password valid until "));
2750                         appendPQExpBufferStr(&buf, PQgetvalue(res, i, 7));
2751                 }
2752
2753                 attr[i] = pg_strdup(buf.data);
2754
2755                 printTableAddCell(&cont, attr[i], false, false);
2756
2757                 printTableAddCell(&cont, PQgetvalue(res, i, 8), false, false);
2758
2759                 if (verbose && pset.sversion >= 80200)
2760                         printTableAddCell(&cont, PQgetvalue(res, i, 9), false, false);
2761         }
2762         termPQExpBuffer(&buf);
2763
2764         printTable(&cont, pset.queryFout, pset.logfile);
2765         printTableCleanup(&cont);
2766
2767         for (i = 0; i < nrows; i++)
2768                 free(attr[i]);
2769         free(attr);
2770
2771         PQclear(res);
2772         return true;
2773 }
2774
2775 static void
2776 add_role_attribute(PQExpBuffer buf, const char *const str)
2777 {
2778         if (buf->len > 0)
2779                 appendPQExpBufferStr(buf, ", ");
2780
2781         appendPQExpBufferStr(buf, str);
2782 }
2783
2784 /*
2785  * \drds
2786  */
2787 bool
2788 listDbRoleSettings(const char *pattern, const char *pattern2)
2789 {
2790         PQExpBufferData buf;
2791         PGresult   *res;
2792         printQueryOpt myopt = pset.popt;
2793
2794         initPQExpBuffer(&buf);
2795
2796         if (pset.sversion >= 90000)
2797         {
2798                 bool            havewhere;
2799
2800                 printfPQExpBuffer(&buf, "SELECT rolname AS \"%s\", datname AS \"%s\",\n"
2801                                   "pg_catalog.array_to_string(setconfig, E'\\n') AS \"%s\"\n"
2802                                                   "FROM pg_db_role_setting AS s\n"
2803                                    "LEFT JOIN pg_database ON pg_database.oid = setdatabase\n"
2804                                                   "LEFT JOIN pg_roles ON pg_roles.oid = setrole\n",
2805                                                   gettext_noop("Role"),
2806                                                   gettext_noop("Database"),
2807                                                   gettext_noop("Settings"));
2808                 havewhere = processSQLNamePattern(pset.db, &buf, pattern, false, false,
2809                                                                            NULL, "pg_roles.rolname", NULL, NULL);
2810                 processSQLNamePattern(pset.db, &buf, pattern2, havewhere, false,
2811                                                           NULL, "pg_database.datname", NULL, NULL);
2812                 appendPQExpBufferStr(&buf, "ORDER BY 1, 2;");
2813         }
2814         else
2815         {
2816                 fprintf(pset.queryFout,
2817                 _("No per-database role settings support in this server version.\n"));
2818                 return false;
2819         }
2820
2821         res = PSQLexec(buf.data, false);
2822         if (!res)
2823                 return false;
2824
2825         if (PQntuples(res) == 0 && !pset.quiet)
2826         {
2827                 if (pattern)
2828                         fprintf(pset.queryFout, _("No matching settings found.\n"));
2829                 else
2830                         fprintf(pset.queryFout, _("No settings found.\n"));
2831         }
2832         else
2833         {
2834                 myopt.nullPrint = NULL;
2835                 myopt.title = _("List of settings");
2836                 myopt.translate_header = true;
2837
2838                 printQuery(res, &myopt, pset.queryFout, pset.logfile);
2839         }
2840
2841         PQclear(res);
2842         resetPQExpBuffer(&buf);
2843         return true;
2844 }
2845
2846
2847 /*
2848  * listTables()
2849  *
2850  * handler for \dt, \di, etc.
2851  *
2852  * tabtypes is an array of characters, specifying what info is desired:
2853  * t - tables
2854  * i - indexes
2855  * v - views
2856  * m - materialized views
2857  * s - sequences
2858  * E - foreign table (Note: different from 'f', the relkind value)
2859  * (any order of the above is fine)
2860  * If tabtypes is empty, we default to \dtvsE.
2861  */
2862 bool
2863 listTables(const char *tabtypes, const char *pattern, bool verbose, bool showSystem)
2864 {
2865         bool            showTables = strchr(tabtypes, 't') != NULL;
2866         bool            showIndexes = strchr(tabtypes, 'i') != NULL;
2867         bool            showViews = strchr(tabtypes, 'v') != NULL;
2868         bool            showMatViews = strchr(tabtypes, 'm') != NULL;
2869         bool            showSeq = strchr(tabtypes, 's') != NULL;
2870         bool            showForeign = strchr(tabtypes, 'E') != NULL;
2871
2872         PQExpBufferData buf;
2873         PGresult   *res;
2874         printQueryOpt myopt = pset.popt;
2875         static const bool translate_columns[] = {false, false, true, false, false, false, false};
2876
2877         if (!(showTables || showIndexes || showViews || showMatViews || showSeq || showForeign))
2878                 showTables = showViews = showMatViews = showSeq = showForeign = true;
2879
2880         initPQExpBuffer(&buf);
2881
2882         /*
2883          * Note: as of Pg 8.2, we no longer use relkind 's', but we keep it here
2884          * for backwards compatibility.
2885          */
2886         printfPQExpBuffer(&buf,
2887                                           "SELECT n.nspname as \"%s\",\n"
2888                                           "  c.relname as \"%s\",\n"
2889                                           "  CASE c.relkind"
2890                                           " WHEN 'r' THEN '%s'"
2891                                           " WHEN 'v' THEN '%s'"
2892                                           " WHEN 'm' THEN '%s'"
2893                                           " WHEN 'i' THEN '%s'"
2894                                           " WHEN 'S' THEN '%s'"
2895                                           " WHEN 's' THEN '%s'"
2896                                           " WHEN 'f' THEN '%s'"
2897                                           " END as \"%s\",\n"
2898                                           "  pg_catalog.pg_get_userbyid(c.relowner) as \"%s\"",
2899                                           gettext_noop("Schema"),
2900                                           gettext_noop("Name"),
2901                                           gettext_noop("table"),
2902                                           gettext_noop("view"),
2903                                           gettext_noop("materialized view"),
2904                                           gettext_noop("index"),
2905                                           gettext_noop("sequence"),
2906                                           gettext_noop("special"),
2907                                           gettext_noop("foreign table"),
2908                                           gettext_noop("Type"),
2909                                           gettext_noop("Owner"));
2910
2911         if (showIndexes)
2912                 appendPQExpBuffer(&buf,
2913                                                   ",\n c2.relname as \"%s\"",
2914                                                   gettext_noop("Table"));
2915
2916         if (verbose)
2917         {
2918                 /*
2919                  * As of PostgreSQL 9.0, use pg_table_size() to show a more acurate
2920                  * size of a table, including FSM, VM and TOAST tables.
2921                  */
2922                 if (pset.sversion >= 90000)
2923                         appendPQExpBuffer(&buf,
2924                                                           ",\n  pg_catalog.pg_size_pretty(pg_catalog.pg_table_size(c.oid)) as \"%s\"",
2925                                                           gettext_noop("Size"));
2926                 else if (pset.sversion >= 80100)
2927                         appendPQExpBuffer(&buf,
2928                                                           ",\n  pg_catalog.pg_size_pretty(pg_catalog.pg_relation_size(c.oid)) as \"%s\"",
2929                                                           gettext_noop("Size"));
2930
2931                 appendPQExpBuffer(&buf,
2932                           ",\n  pg_catalog.obj_description(c.oid, 'pg_class') as \"%s\"",
2933                                                   gettext_noop("Description"));
2934         }
2935
2936         appendPQExpBufferStr(&buf,
2937                                                  "\nFROM pg_catalog.pg_class c"
2938          "\n     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace");
2939         if (showIndexes)
2940                 appendPQExpBufferStr(&buf,
2941                          "\n     LEFT JOIN pg_catalog.pg_index i ON i.indexrelid = c.oid"
2942                    "\n     LEFT JOIN pg_catalog.pg_class c2 ON i.indrelid = c2.oid");
2943
2944         appendPQExpBufferStr(&buf, "\nWHERE c.relkind IN (");
2945         if (showTables)
2946                 appendPQExpBufferStr(&buf, "'r',");
2947         if (showViews)
2948                 appendPQExpBufferStr(&buf, "'v',");
2949         if (showMatViews)
2950                 appendPQExpBufferStr(&buf, "'m',");
2951         if (showIndexes)
2952                 appendPQExpBufferStr(&buf, "'i',");
2953         if (showSeq)
2954                 appendPQExpBufferStr(&buf, "'S',");
2955         if (showSystem || pattern)
2956                 appendPQExpBufferStr(&buf, "'s',");             /* was RELKIND_SPECIAL in <=
2957                                                                                                  * 8.1 */
2958         if (showForeign)
2959                 appendPQExpBufferStr(&buf, "'f',");
2960
2961         appendPQExpBufferStr(&buf, "''");       /* dummy */
2962         appendPQExpBufferStr(&buf, ")\n");
2963
2964         if (!showSystem && !pattern)
2965                 appendPQExpBufferStr(&buf, "      AND n.nspname <> 'pg_catalog'\n"
2966                                                          "      AND n.nspname <> 'information_schema'\n");
2967
2968         /*
2969          * TOAST objects are suppressed unconditionally.  Since we don't provide
2970          * any way to select relkind 't' above, we would never show toast tables
2971          * in any case; it seems a bit confusing to allow their indexes to be
2972          * shown. Use plain \d if you really need to look at a TOAST table/index.
2973          */
2974         appendPQExpBufferStr(&buf, "      AND n.nspname !~ '^pg_toast'\n");
2975
2976         processSQLNamePattern(pset.db, &buf, pattern, true, false,
2977                                                   "n.nspname", "c.relname", NULL,
2978                                                   "pg_catalog.pg_table_is_visible(c.oid)");
2979
2980         appendPQExpBufferStr(&buf, "ORDER BY 1,2;");
2981
2982         res = PSQLexec(buf.data, false);
2983         termPQExpBuffer(&buf);
2984         if (!res)
2985                 return false;
2986
2987         if (PQntuples(res) == 0 && !pset.quiet)
2988         {
2989                 if (pattern)
2990                         fprintf(pset.queryFout, _("No matching relations found.\n"));
2991                 else
2992                         fprintf(pset.queryFout, _("No relations found.\n"));
2993         }
2994         else
2995         {
2996                 myopt.nullPrint = NULL;
2997                 myopt.title = _("List of relations");
2998                 myopt.translate_header = true;
2999                 myopt.translate_columns = translate_columns;
3000                 myopt.n_translate_columns = lengthof(translate_columns);
3001
3002                 printQuery(res, &myopt, pset.queryFout, pset.logfile);
3003         }
3004
3005         PQclear(res);
3006         return true;
3007 }
3008
3009
3010 /*
3011  * \dL
3012  *
3013  * Describes languages.
3014  */
3015 bool
3016 listLanguages(const char *pattern, bool verbose, bool showSystem)
3017 {
3018         PQExpBufferData buf;
3019         PGresult   *res;
3020         printQueryOpt myopt = pset.popt;
3021
3022         initPQExpBuffer(&buf);
3023
3024         printfPQExpBuffer(&buf,
3025                                           "SELECT l.lanname AS \"%s\",\n",
3026                                           gettext_noop("Name"));
3027         if (pset.sversion >= 80300)
3028                 appendPQExpBuffer(&buf,
3029                                 "       pg_catalog.pg_get_userbyid(l.lanowner) as \"%s\",\n",
3030                                                   gettext_noop("Owner"));
3031
3032         appendPQExpBuffer(&buf,
3033                                           "       l.lanpltrusted AS \"%s\"",
3034                                           gettext_noop("Trusted"));
3035
3036         if (verbose)
3037         {
3038                 appendPQExpBuffer(&buf,
3039                                                   ",\n       NOT l.lanispl AS \"%s\",\n"
3040                                                   "       l.lanplcallfoid::regprocedure AS \"%s\",\n"
3041                                    "       l.lanvalidator::regprocedure AS \"%s\",\n       ",
3042                                                   gettext_noop("Internal Language"),
3043                                                   gettext_noop("Call Handler"),
3044                                                   gettext_noop("Validator"));
3045                 if (pset.sversion >= 90000)
3046                         appendPQExpBuffer(&buf, "l.laninline::regprocedure AS \"%s\",\n       ",
3047                                                           gettext_noop("Inline Handler"));
3048                 printACLColumn(&buf, "l.lanacl");
3049         }
3050
3051         appendPQExpBuffer(&buf,
3052                                           ",\n       d.description AS \"%s\""
3053                                           "\nFROM pg_catalog.pg_language l\n"
3054                                           "LEFT JOIN pg_catalog.pg_description d\n"
3055                                           "  ON d.classoid = l.tableoid AND d.objoid = l.oid\n"
3056                                           "  AND d.objsubid = 0\n",
3057                                           gettext_noop("Description"));
3058
3059         if (pattern)
3060                 processSQLNamePattern(pset.db, &buf, pattern, false, false,
3061                                                           NULL, "l.lanname", NULL, NULL);
3062
3063         if (!showSystem && !pattern)
3064                 appendPQExpBufferStr(&buf, "WHERE l.lanplcallfoid != 0\n");
3065
3066
3067         appendPQExpBufferStr(&buf, "ORDER BY 1;");
3068
3069         res = PSQLexec(buf.data, false);
3070         termPQExpBuffer(&buf);
3071         if (!res)
3072                 return false;
3073
3074         myopt.nullPrint = NULL;
3075         myopt.title = _("List of languages");
3076         myopt.translate_header = true;
3077
3078         printQuery(res, &myopt, pset.queryFout, pset.logfile);
3079
3080         PQclear(res);
3081         return true;
3082 }
3083
3084
3085 /*
3086  * \dD
3087  *
3088  * Describes domains.
3089  */
3090 bool
3091 listDomains(const char *pattern, bool verbose, bool showSystem)
3092 {
3093         PQExpBufferData buf;
3094         PGresult   *res;
3095         printQueryOpt myopt = pset.popt;
3096
3097         initPQExpBuffer(&buf);
3098
3099         printfPQExpBuffer(&buf,
3100                                           "SELECT n.nspname as \"%s\",\n"
3101                                           "       t.typname as \"%s\",\n"
3102          "       pg_catalog.format_type(t.typbasetype, t.typtypmod) as \"%s\",\n"
3103                                           "       TRIM(LEADING\n",
3104                                           gettext_noop("Schema"),
3105                                           gettext_noop("Name"),
3106                                           gettext_noop("Type"));
3107
3108         if (pset.sversion >= 90100)
3109                 appendPQExpBufferStr(&buf,
3110                                                          "            COALESCE((SELECT ' collate ' || c.collname FROM pg_catalog.pg_collation c, pg_catalog.pg_type bt\n"
3111                                                          "                      WHERE c.oid = t.typcollation AND bt.oid = t.typbasetype AND t.typcollation <> bt.typcollation), '') ||\n");
3112         appendPQExpBuffer(&buf,
3113            "            CASE WHEN t.typnotnull THEN ' not null' ELSE '' END ||\n"
3114                                           "            CASE WHEN t.typdefault IS NOT NULL THEN ' default ' || t.typdefault ELSE '' END\n"
3115                                           "       ) as \"%s\",\n"
3116                                           "       pg_catalog.array_to_string(ARRAY(\n"
3117                                           "         SELECT pg_catalog.pg_get_constraintdef(r.oid, true) FROM pg_catalog.pg_constraint r WHERE t.oid = r.contypid\n"
3118                                           "       ), ' ') as \"%s\"",
3119                                           gettext_noop("Modifier"),
3120                                           gettext_noop("Check"));
3121
3122         if (verbose)
3123         {
3124                 if (pset.sversion >= 90200)
3125                 {
3126                         appendPQExpBufferStr(&buf, ",\n  ");
3127                         printACLColumn(&buf, "t.typacl");
3128                 }
3129                 appendPQExpBuffer(&buf,
3130                                                   ",\n       d.description as \"%s\"",
3131                                                   gettext_noop("Description"));
3132         }
3133
3134         appendPQExpBufferStr(&buf,
3135                                                  "\nFROM pg_catalog.pg_type t\n"
3136          "     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = t.typnamespace\n");
3137
3138         if (verbose)
3139                 appendPQExpBufferStr(&buf,
3140                                                          "     LEFT JOIN pg_catalog.pg_description d "
3141                                                    "ON d.classoid = t.tableoid AND d.objoid = t.oid "
3142                                                          "AND d.objsubid = 0\n");
3143
3144         appendPQExpBufferStr(&buf, "WHERE t.typtype = 'd'\n");
3145
3146         if (!showSystem && !pattern)
3147                 appendPQExpBufferStr(&buf, "      AND n.nspname <> 'pg_catalog'\n"
3148                                                          "      AND n.nspname <> 'information_schema'\n");
3149
3150         processSQLNamePattern(pset.db, &buf, pattern, true, false,
3151                                                   "n.nspname", "t.typname", NULL,
3152                                                   "pg_catalog.pg_type_is_visible(t.oid)");
3153
3154         appendPQExpBufferStr(&buf, "ORDER BY 1, 2;");
3155
3156         res = PSQLexec(buf.data, false);
3157         termPQExpBuffer(&buf);
3158         if (!res)
3159                 return false;
3160
3161         myopt.nullPrint = NULL;
3162         myopt.title = _("List of domains");
3163         myopt.translate_header = true;
3164
3165         printQuery(res, &myopt, pset.queryFout, pset.logfile);
3166
3167         PQclear(res);
3168         return true;
3169 }
3170
3171 /*
3172  * \dc
3173  *
3174  * Describes conversions.
3175  */
3176 bool
3177 listConversions(const char *pattern, bool verbose, bool showSystem)
3178 {
3179         PQExpBufferData buf;
3180         PGresult   *res;
3181         printQueryOpt myopt = pset.popt;
3182         static const bool translate_columns[] =
3183         {false, false, false, false, true, false};
3184
3185         initPQExpBuffer(&buf);
3186
3187         printfPQExpBuffer(&buf,
3188                                           "SELECT n.nspname AS \"%s\",\n"
3189                                           "       c.conname AS \"%s\",\n"
3190            "       pg_catalog.pg_encoding_to_char(c.conforencoding) AS \"%s\",\n"
3191                 "       pg_catalog.pg_encoding_to_char(c.contoencoding) AS \"%s\",\n"
3192                                           "       CASE WHEN c.condefault THEN '%s'\n"
3193                                           "       ELSE '%s' END AS \"%s\"",
3194                                           gettext_noop("Schema"),
3195                                           gettext_noop("Name"),
3196                                           gettext_noop("Source"),
3197                                           gettext_noop("Destination"),
3198                                           gettext_noop("yes"), gettext_noop("no"),
3199                                           gettext_noop("Default?"));
3200
3201         if (verbose)
3202                 appendPQExpBuffer(&buf,
3203                                                   ",\n       d.description AS \"%s\"",
3204                                                   gettext_noop("Description"));
3205
3206         appendPQExpBufferStr(&buf,
3207                                                  "\nFROM pg_catalog.pg_conversion c\n"
3208                                                  "     JOIN pg_catalog.pg_namespace n "
3209                                                  "ON n.oid = c.connamespace\n");
3210
3211         if (verbose)
3212                 appendPQExpBufferStr(&buf,
3213                                                          "LEFT JOIN pg_catalog.pg_description d "
3214                                                          "ON d.classoid = c.tableoid\n"
3215                                                          "          AND d.objoid = c.oid "
3216                                                          "AND d.objsubid = 0\n");
3217
3218         appendPQExpBufferStr(&buf, "WHERE true\n");
3219
3220         if (!showSystem && !pattern)
3221                 appendPQExpBufferStr(&buf, "  AND n.nspname <> 'pg_catalog'\n"
3222                                                          "  AND n.nspname <> 'information_schema'\n");
3223
3224         processSQLNamePattern(pset.db, &buf, pattern, true, false,
3225                                                   "n.nspname", "c.conname", NULL,
3226                                                   "pg_catalog.pg_conversion_is_visible(c.oid)");
3227
3228         appendPQExpBufferStr(&buf, "ORDER BY 1, 2;");
3229
3230         res = PSQLexec(buf.data, false);
3231         termPQExpBuffer(&buf);
3232         if (!res)
3233                 return false;
3234
3235         myopt.nullPrint = NULL;
3236         myopt.title = _("List of conversions");
3237         myopt.translate_header = true;
3238         myopt.translate_columns = translate_columns;
3239         myopt.n_translate_columns = lengthof(translate_columns);
3240
3241         printQuery(res, &myopt, pset.queryFout, pset.logfile);
3242
3243         PQclear(res);
3244         return true;
3245 }
3246
3247 /*
3248  * \dy
3249  *
3250  * Describes Event Triggers.
3251  */
3252 bool
3253 listEventTriggers(const char *pattern, bool verbose)
3254 {
3255         PQExpBufferData buf;
3256         PGresult   *res;
3257         printQueryOpt myopt = pset.popt;
3258         static const bool translate_columns[] =
3259         {false, false, false, true, false, false, false};
3260
3261         initPQExpBuffer(&buf);
3262
3263         printfPQExpBuffer(&buf,
3264                                           "SELECT evtname as \"%s\", "
3265                                           "evtevent as \"%s\", "
3266                                           "pg_catalog.pg_get_userbyid(e.evtowner) as \"%s\",\n"
3267                                           " case evtenabled when 'O' then '%s'"
3268                                           "  when 'R' then '%s'"
3269                                           "  when 'A' then '%s'"
3270                                           "  when 'D' then '%s' end as \"%s\",\n"
3271                                           " e.evtfoid::pg_catalog.regproc as \"%s\", "
3272                                           "pg_catalog.array_to_string(array(select x"
3273                                 " from pg_catalog.unnest(evttags) as t(x)), ', ') as \"%s\"",
3274                                           gettext_noop("Name"),
3275                                           gettext_noop("Event"),
3276                                           gettext_noop("Owner"),
3277                                           gettext_noop("enabled"),
3278                                           gettext_noop("replica"),
3279                                           gettext_noop("always"),
3280                                           gettext_noop("disabled"),
3281                                           gettext_noop("Enabled"),
3282                                           gettext_noop("Procedure"),
3283                                           gettext_noop("Tags"));
3284         if (verbose)
3285                 appendPQExpBuffer(&buf,
3286                 ",\npg_catalog.obj_description(e.oid, 'pg_event_trigger') as \"%s\"",
3287                                                   gettext_noop("Description"));
3288         appendPQExpBufferStr(&buf,
3289                                                  "\nFROM pg_catalog.pg_event_trigger e ");
3290
3291         processSQLNamePattern(pset.db, &buf, pattern, false, false,
3292                                                   NULL, "evtname", NULL, NULL);
3293
3294         appendPQExpBufferStr(&buf, "ORDER BY 1");
3295
3296         res = PSQLexec(buf.data, false);
3297         termPQExpBuffer(&buf);
3298         if (!res)
3299                 return false;
3300
3301         myopt.nullPrint = NULL;
3302         myopt.title = _("List of event triggers");
3303         myopt.translate_header = true;
3304         myopt.translate_columns = translate_columns;
3305         myopt.n_translate_columns = lengthof(translate_columns);
3306
3307         printQuery(res, &myopt, pset.queryFout, pset.logfile);
3308
3309         PQclear(res);
3310         return true;
3311 }
3312
3313 /*
3314  * \dC
3315  *
3316  * Describes casts.
3317  */
3318 bool
3319 listCasts(const char *pattern, bool verbose)
3320 {
3321         PQExpBufferData buf;
3322         PGresult   *res;
3323         printQueryOpt myopt = pset.popt;
3324         static const bool translate_columns[] = {false, false, false, true, false};
3325
3326         initPQExpBuffer(&buf);
3327
3328         /*
3329          * We need a left join to pg_proc for binary casts; the others are just
3330          * paranoia.  Also note that we don't attempt to localize '(binary
3331          * coercible)', because there's too much risk of gettext translating a
3332          * function name that happens to match some string in the PO database.
3333          */
3334         printfPQExpBuffer(&buf,
3335                            "SELECT pg_catalog.format_type(castsource, NULL) AS \"%s\",\n"
3336                            "       pg_catalog.format_type(casttarget, NULL) AS \"%s\",\n"
3337                                   "       CASE WHEN castfunc = 0 THEN '(binary coercible)'\n"
3338                                           "            ELSE p.proname\n"
3339                                           "       END as \"%s\",\n"
3340                                           "       CASE WHEN c.castcontext = 'e' THEN '%s'\n"
3341                                           "            WHEN c.castcontext = 'a' THEN '%s'\n"
3342                                           "            ELSE '%s'\n"
3343                                           "       END as \"%s\"",
3344                                           gettext_noop("Source type"),
3345                                           gettext_noop("Target type"),
3346                                           gettext_noop("Function"),
3347                                           gettext_noop("no"),
3348                                           gettext_noop("in assignment"),
3349                                           gettext_noop("yes"),
3350                                           gettext_noop("Implicit?"));
3351
3352         if (verbose)
3353                 appendPQExpBuffer(&buf,
3354                                                   ",\n       d.description AS \"%s\"\n",
3355                                                   gettext_noop("Description"));
3356
3357         appendPQExpBufferStr(&buf,
3358                                  "FROM pg_catalog.pg_cast c LEFT JOIN pg_catalog.pg_proc p\n"
3359                                                  "     ON c.castfunc = p.oid\n"
3360                                                  "     LEFT JOIN pg_catalog.pg_type ts\n"
3361                                                  "     ON c.castsource = ts.oid\n"
3362                                                  "     LEFT JOIN pg_catalog.pg_namespace ns\n"
3363                                                  "     ON ns.oid = ts.typnamespace\n"
3364                                                  "     LEFT JOIN pg_catalog.pg_type tt\n"
3365                                                  "     ON c.casttarget = tt.oid\n"
3366                                                  "     LEFT JOIN pg_catalog.pg_namespace nt\n"
3367                                                  "     ON nt.oid = tt.typnamespace\n");
3368
3369         if (verbose)
3370                 appendPQExpBufferStr(&buf,
3371                                                          "     LEFT JOIN pg_catalog.pg_description d\n"
3372                                                          "     ON d.classoid = c.tableoid AND d.objoid = "
3373                                                          "c.oid AND d.objsubid = 0\n");
3374
3375         appendPQExpBufferStr(&buf, "WHERE ( (true");
3376
3377         /*
3378          * Match name pattern against either internal or external name of either
3379          * castsource or casttarget
3380          */
3381         processSQLNamePattern(pset.db, &buf, pattern, true, false,
3382                                                   "ns.nspname", "ts.typname",
3383                                                   "pg_catalog.format_type(ts.oid, NULL)",
3384                                                   "pg_catalog.pg_type_is_visible(ts.oid)");
3385
3386         appendPQExpBufferStr(&buf, ") OR (true");
3387
3388         processSQLNamePattern(pset.db, &buf, pattern, true, false,
3389                                                   "nt.nspname", "tt.typname",
3390                                                   "pg_catalog.format_type(tt.oid, NULL)",
3391                                                   "pg_catalog.pg_type_is_visible(tt.oid)");
3392
3393         appendPQExpBufferStr(&buf, ") )\nORDER BY 1, 2;");
3394
3395         res = PSQLexec(buf.data, false);
3396         termPQExpBuffer(&buf);
3397         if (!res)
3398                 return false;
3399
3400         myopt.nullPrint = NULL;
3401         myopt.title = _("List of casts");
3402         myopt.translate_header = true;
3403         myopt.translate_columns = translate_columns;
3404         myopt.n_translate_columns = lengthof(translate_columns);
3405
3406         printQuery(res, &myopt, pset.queryFout, pset.logfile);
3407
3408         PQclear(res);
3409         return true;
3410 }
3411
3412 /*
3413  * \dO
3414  *
3415  * Describes collations.
3416  */
3417 bool
3418 listCollations(const char *pattern, bool verbose, bool showSystem)
3419 {
3420         PQExpBufferData buf;
3421         PGresult   *res;
3422         printQueryOpt myopt = pset.popt;
3423         static const bool translate_columns[] = {false, false, false, false, false};
3424
3425         if (pset.sversion < 90100)
3426         {
3427                 psql_error("The server (version %d.%d) does not support collations.\n",
3428                                    pset.sversion / 10000, (pset.sversion / 100) % 100);
3429                 return true;
3430         }
3431
3432         initPQExpBuffer(&buf);
3433
3434         printfPQExpBuffer(&buf,
3435                                           "SELECT n.nspname AS \"%s\",\n"
3436                                           "       c.collname AS \"%s\",\n"
3437                                           "       c.collcollate AS \"%s\",\n"
3438                                           "       c.collctype AS \"%s\"",
3439                                           gettext_noop("Schema"),
3440                                           gettext_noop("Name"),
3441                                           gettext_noop("Collate"),
3442                                           gettext_noop("Ctype"));
3443
3444         if (verbose)
3445                 appendPQExpBuffer(&buf,
3446                                                   ",\n       pg_catalog.obj_description(c.oid, 'pg_collation') AS \"%s\"",
3447                                                   gettext_noop("Description"));
3448
3449         appendPQExpBufferStr(&buf,
3450                           "\nFROM pg_catalog.pg_collation c, pg_catalog.pg_namespace n\n"
3451                                                  "WHERE n.oid = c.collnamespace\n");
3452
3453         if (!showSystem && !pattern)
3454                 appendPQExpBufferStr(&buf, "      AND n.nspname <> 'pg_catalog'\n"
3455                                                          "      AND n.nspname <> 'information_schema'\n");
3456
3457         /*
3458          * Hide collations that aren't usable in the current database's encoding.
3459          * If you think to change this, note that pg_collation_is_visible rejects
3460          * unusable collations, so you will need to hack name pattern processing
3461          * somehow to avoid inconsistent behavior.
3462          */
3463         appendPQExpBufferStr(&buf, "      AND c.collencoding IN (-1, pg_catalog.pg_char_to_encoding(pg_catalog.getdatabaseencoding()))\n");
3464
3465         processSQLNamePattern(pset.db, &buf, pattern, true, false,
3466                                                   "n.nspname", "c.collname", NULL,
3467                                                   "pg_catalog.pg_collation_is_visible(c.oid)");
3468
3469         appendPQExpBufferStr(&buf, "ORDER BY 1, 2;");
3470
3471         res = PSQLexec(buf.data, false);
3472         termPQExpBuffer(&buf);
3473         if (!res)
3474                 return false;
3475
3476         myopt.nullPrint = NULL;
3477         myopt.title = _("List of collations");
3478         myopt.translate_header = true;
3479         myopt.translate_columns = translate_columns;
3480         myopt.n_translate_columns = lengthof(translate_columns);
3481
3482         printQuery(res, &myopt, pset.queryFout, pset.logfile);
3483
3484         PQclear(res);
3485         return true;
3486 }
3487
3488 /*
3489  * \dn
3490  *
3491  * Describes schemas (namespaces)
3492  */
3493 bool
3494 listSchemas(const char *pattern, bool verbose, bool showSystem)
3495 {
3496         PQExpBufferData buf;
3497         PGresult   *res;
3498         printQueryOpt myopt = pset.popt;
3499
3500         initPQExpBuffer(&buf);
3501         printfPQExpBuffer(&buf,
3502                                           "SELECT n.nspname AS \"%s\",\n"
3503                                           "  pg_catalog.pg_get_userbyid(n.nspowner) AS \"%s\"",
3504                                           gettext_noop("Name"),
3505                                           gettext_noop("Owner"));
3506
3507         if (verbose)
3508         {
3509                 appendPQExpBufferStr(&buf, ",\n  ");
3510                 printACLColumn(&buf, "n.nspacl");
3511                 appendPQExpBuffer(&buf,
3512                   ",\n  pg_catalog.obj_description(n.oid, 'pg_namespace') AS \"%s\"",
3513                                                   gettext_noop("Description"));
3514         }
3515
3516         appendPQExpBuffer(&buf,
3517                                           "\nFROM pg_catalog.pg_namespace n\n");
3518
3519         if (!showSystem && !pattern)
3520                 appendPQExpBufferStr(&buf,
3521                 "WHERE n.nspname !~ '^pg_' AND n.nspname <> 'information_schema'\n");
3522
3523         processSQLNamePattern(pset.db, &buf, pattern,
3524                                                   !showSystem && !pattern, false,
3525                                                   NULL, "n.nspname", NULL,
3526                                                   NULL);
3527
3528         appendPQExpBufferStr(&buf, "ORDER BY 1;");
3529
3530         res = PSQLexec(buf.data, false);
3531         termPQExpBuffer(&buf);
3532         if (!res)
3533                 return false;
3534
3535         myopt.nullPrint = NULL;
3536         myopt.title = _("List of schemas");
3537         myopt.translate_header = true;
3538
3539         printQuery(res, &myopt, pset.queryFout, pset.logfile);
3540
3541         PQclear(res);
3542         return true;
3543 }
3544
3545
3546 /*
3547  * \dFp
3548  * list text search parsers
3549  */
3550 bool
3551 listTSParsers(const char *pattern, bool verbose)
3552 {
3553         PQExpBufferData buf;
3554         PGresult   *res;
3555         printQueryOpt myopt = pset.popt;
3556
3557         if (pset.sversion < 80300)
3558         {
3559                 psql_error("The server (version %d.%d) does not support full text search.\n",
3560                                    pset.sversion / 10000, (pset.sversion / 100) % 100);
3561                 return true;
3562         }
3563
3564         if (verbose)
3565                 return listTSParsersVerbose(pattern);
3566
3567         initPQExpBuffer(&buf);
3568
3569         printfPQExpBuffer(&buf,
3570                                           "SELECT \n"
3571                                           "  n.nspname as \"%s\",\n"
3572                                           "  p.prsname as \"%s\",\n"
3573                         "  pg_catalog.obj_description(p.oid, 'pg_ts_parser') as \"%s\"\n"
3574                                           "FROM pg_catalog.pg_ts_parser p \n"
3575                    "LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.prsnamespace\n",
3576                                           gettext_noop("Schema"),
3577                                           gettext_noop("Name"),
3578                                           gettext_noop("Description")
3579                 );
3580
3581         processSQLNamePattern(pset.db, &buf, pattern, false, false,
3582                                                   "n.nspname", "p.prsname", NULL,
3583                                                   "pg_catalog.pg_ts_parser_is_visible(p.oid)");
3584
3585         appendPQExpBufferStr(&buf, "ORDER BY 1, 2;");
3586
3587         res = PSQLexec(buf.data, false);
3588         termPQExpBuffer(&buf);
3589         if (!res)
3590                 return false;
3591
3592         myopt.nullPrint = NULL;
3593         myopt.title = _("List of text search parsers");
3594         myopt.translate_header = true;
3595
3596         printQuery(res, &myopt, pset.queryFout, pset.logfile);
3597
3598         PQclear(res);
3599         return true;
3600 }
3601
3602 /*
3603  * full description of parsers
3604  */
3605 static bool
3606 listTSParsersVerbose(const char *pattern)
3607 {
3608         PQExpBufferData buf;
3609         PGresult   *res;
3610         int                     i;
3611
3612         initPQExpBuffer(&buf);
3613
3614         printfPQExpBuffer(&buf,
3615                                           "SELECT p.oid, \n"
3616                                           "  n.nspname, \n"
3617                                           "  p.prsname \n"
3618                                           "FROM pg_catalog.pg_ts_parser p\n"
3619                         "LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.prsnamespace\n"
3620                 );
3621
3622         processSQLNamePattern(pset.db, &buf, pattern, false, false,
3623                                                   "n.nspname", "p.prsname", NULL,
3624                                                   "pg_catalog.pg_ts_parser_is_visible(p.oid)");
3625
3626         appendPQExpBufferStr(&buf, "ORDER BY 1, 2;");
3627
3628         res = PSQLexec(buf.data, false);
3629         termPQExpBuffer(&buf);
3630         if (!res)
3631                 return false;
3632
3633         if (PQntuples(res) == 0)
3634         {
3635                 if (!pset.quiet)
3636                         psql_error("Did not find any text search parser named \"%s\".\n",
3637                                            pattern);
3638                 PQclear(res);
3639                 return false;
3640         }
3641
3642         for (i = 0; i < PQntuples(res); i++)
3643         {
3644                 const char *oid;
3645                 const char *nspname = NULL;
3646                 const char *prsname;
3647
3648                 oid = PQgetvalue(res, i, 0);
3649                 if (!PQgetisnull(res, i, 1))
3650                         nspname = PQgetvalue(res, i, 1);
3651                 prsname = PQgetvalue(res, i, 2);
3652
3653                 if (!describeOneTSParser(oid, nspname, prsname))
3654                 {
3655                         PQclear(res);
3656                         return false;
3657                 }
3658
3659                 if (cancel_pressed)
3660                 {
3661                         PQclear(res);
3662                         return false;
3663                 }
3664         }
3665
3666         PQclear(res);
3667         return true;
3668 }
3669
3670 static bool
3671 describeOneTSParser(const char *oid, const char *nspname, const char *prsname)
3672 {
3673         PQExpBufferData buf;
3674         PGresult   *res;
3675         char            title[1024];
3676         printQueryOpt myopt = pset.popt;
3677         static const bool translate_columns[] = {true, false, false};
3678
3679         initPQExpBuffer(&buf);
3680
3681         printfPQExpBuffer(&buf,
3682                                           "SELECT '%s' AS \"%s\", \n"
3683                                           "   p.prsstart::pg_catalog.regproc AS \"%s\", \n"
3684                   "   pg_catalog.obj_description(p.prsstart, 'pg_proc') as \"%s\" \n"
3685                                           " FROM pg_catalog.pg_ts_parser p \n"
3686                                           " WHERE p.oid = '%s' \n"
3687                                           "UNION ALL \n"
3688                                           "SELECT '%s', \n"
3689                                           "   p.prstoken::pg_catalog.regproc, \n"
3690                                         "   pg_catalog.obj_description(p.prstoken, 'pg_proc') \n"
3691                                           " FROM pg_catalog.pg_ts_parser p \n"
3692                                           " WHERE p.oid = '%s' \n"
3693                                           "UNION ALL \n"
3694                                           "SELECT '%s', \n"
3695                                           "   p.prsend::pg_catalog.regproc, \n"
3696                                           "   pg_catalog.obj_description(p.prsend, 'pg_proc') \n"
3697                                           " FROM pg_catalog.pg_ts_parser p \n"
3698                                           " WHERE p.oid = '%s' \n"
3699                                           "UNION ALL \n"
3700                                           "SELECT '%s', \n"
3701                                           "   p.prsheadline::pg_catalog.regproc, \n"
3702                                  "   pg_catalog.obj_description(p.prsheadline, 'pg_proc') \n"
3703                                           " FROM pg_catalog.pg_ts_parser p \n"
3704                                           " WHERE p.oid = '%s' \n"
3705                                           "UNION ALL \n"
3706                                           "SELECT '%s', \n"
3707                                           "   p.prslextype::pg_catalog.regproc, \n"
3708                                   "   pg_catalog.obj_description(p.prslextype, 'pg_proc') \n"
3709                                           " FROM pg_catalog.pg_ts_parser p \n"
3710                                           " WHERE p.oid = '%s';",
3711                                           gettext_noop("Start parse"),
3712                                           gettext_noop("Method"),
3713                                           gettext_noop("Function"),
3714                                           gettext_noop("Description"),
3715                                           oid,
3716                                           gettext_noop("Get next token"),
3717                                           oid,
3718                                           gettext_noop("End parse"),
3719                                           oid,
3720                                           gettext_noop("Get headline"),
3721                                           oid,
3722                                           gettext_noop("Get token types"),
3723                                           oid);
3724
3725         res = PSQLexec(buf.data, false);
3726         termPQExpBuffer(&buf);
3727         if (!res)
3728                 return false;
3729
3730         myopt.nullPrint = NULL;
3731         if (nspname)
3732                 sprintf(title, _("Text search parser \"%s.%s\""), nspname, prsname);
3733         else
3734                 sprintf(title, _("Text search parser \"%s\""), prsname);
3735         myopt.title = title;
3736         myopt.footers = NULL;
3737         myopt.topt.default_footer = false;
3738         myopt.translate_header = true;
3739         myopt.translate_columns = translate_columns;
3740         myopt.n_translate_columns = lengthof(translate_columns);
3741
3742         printQuery(res, &myopt, pset.queryFout, pset.logfile);
3743
3744         PQclear(res);
3745
3746         initPQExpBuffer(&buf);
3747
3748         printfPQExpBuffer(&buf,
3749                                           "SELECT t.alias as \"%s\", \n"
3750                                           "  t.description as \"%s\" \n"
3751                           "FROM pg_catalog.ts_token_type( '%s'::pg_catalog.oid ) as t \n"
3752                                           "ORDER BY 1;",
3753                                           gettext_noop("Token name"),
3754                                           gettext_noop("Description"),
3755                                           oid);
3756
3757         res = PSQLexec(buf.data, false);
3758         termPQExpBuffer(&buf);
3759         if (!res)
3760                 return false;
3761
3762         myopt.nullPrint = NULL;
3763         if (nspname)
3764                 sprintf(title, _("Token types for parser \"%s.%s\""), nspname, prsname);
3765         else
3766                 sprintf(title, _("Token types for parser \"%s\""), prsname);
3767         myopt.title = title;
3768         myopt.footers = NULL;
3769         myopt.topt.default_footer = true;
3770         myopt.translate_header = true;
3771         myopt.translate_columns = NULL;
3772         myopt.n_translate_columns = 0;
3773
3774         printQuery(res, &myopt, pset.queryFout, pset.logfile);
3775
3776         PQclear(res);
3777         return true;
3778 }
3779
3780
3781 /*
3782  * \dFd
3783  * list text search dictionaries
3784  */
3785 bool
3786 listTSDictionaries(const char *pattern, bool verbose)
3787 {
3788         PQExpBufferData buf;
3789         PGresult   *res;
3790         printQueryOpt myopt = pset.popt;
3791
3792         if (pset.sversion < 80300)
3793         {
3794                 psql_error("The server (version %d.%d) does not support full text search.\n",
3795                                    pset.sversion / 10000, (pset.sversion / 100) % 100);
3796                 return true;
3797         }
3798
3799         initPQExpBuffer(&buf);
3800
3801         printfPQExpBuffer(&buf,
3802                                           "SELECT \n"
3803                                           "  n.nspname as \"%s\",\n"
3804                                           "  d.dictname as \"%s\",\n",
3805                                           gettext_noop("Schema"),
3806                                           gettext_noop("Name"));
3807
3808         if (verbose)
3809         {
3810                 appendPQExpBuffer(&buf,
3811                                                   "  ( SELECT COALESCE(nt.nspname, '(null)')::pg_catalog.text || '.' || t.tmplname FROM \n"
3812                                                   "    pg_catalog.pg_ts_template t \n"
3813                                                   "                      LEFT JOIN pg_catalog.pg_namespace nt ON nt.oid = t.tmplnamespace \n"
3814                                                   "                      WHERE d.dicttemplate = t.oid ) AS  \"%s\", \n"
3815                                                   "  d.dictinitoption as \"%s\", \n",
3816                                                   gettext_noop("Template"),
3817                                                   gettext_noop("Init options"));
3818         }
3819
3820         appendPQExpBuffer(&buf,
3821                          "  pg_catalog.obj_description(d.oid, 'pg_ts_dict') as \"%s\"\n",
3822                                           gettext_noop("Description"));
3823
3824         appendPQExpBufferStr(&buf, "FROM pg_catalog.pg_ts_dict d\n"
3825                  "LEFT JOIN pg_catalog.pg_namespace n ON n.oid = d.dictnamespace\n");
3826
3827         processSQLNamePattern(pset.db, &buf, pattern, false, false,
3828                                                   "n.nspname", "d.dictname", NULL,
3829                                                   "pg_catalog.pg_ts_dict_is_visible(d.oid)");
3830
3831         appendPQExpBufferStr(&buf, "ORDER BY 1, 2;");
3832
3833         res = PSQLexec(buf.data, false);
3834         termPQExpBuffer(&buf);
3835         if (!res)
3836                 return false;
3837
3838         myopt.nullPrint = NULL;
3839         myopt.title = _("List of text search dictionaries");
3840         myopt.translate_header = true;
3841
3842         printQuery(res, &myopt, pset.queryFout, pset.logfile);
3843
3844         PQclear(res);
3845         return true;
3846 }
3847
3848
3849 /*
3850  * \dFt
3851  * list text search templates
3852  */
3853 bool
3854 listTSTemplates(const char *pattern, bool verbose)
3855 {
3856         PQExpBufferData buf;
3857         PGresult   *res;
3858         printQueryOpt myopt = pset.popt;
3859
3860         if (pset.sversion < 80300)
3861         {
3862                 psql_error("The server (version %d.%d) does not support full text search.\n",
3863                                    pset.sversion / 10000, (pset.sversion / 100) % 100);
3864                 return true;
3865         }
3866
3867         initPQExpBuffer(&buf);
3868
3869         if (verbose)
3870                 printfPQExpBuffer(&buf,
3871                                                   "SELECT \n"
3872                                                   "  n.nspname AS \"%s\",\n"
3873                                                   "  t.tmplname AS \"%s\",\n"
3874                                                   "  t.tmplinit::pg_catalog.regproc AS \"%s\",\n"
3875                                                   "  t.tmpllexize::pg_catalog.regproc AS \"%s\",\n"
3876                  "  pg_catalog.obj_description(t.oid, 'pg_ts_template') AS \"%s\"\n",
3877                                                   gettext_noop("Schema"),
3878                                                   gettext_noop("Name"),
3879                                                   gettext_noop("Init"),
3880                                                   gettext_noop("Lexize"),
3881                                                   gettext_noop("Description"));
3882         else
3883                 printfPQExpBuffer(&buf,
3884                                                   "SELECT \n"
3885                                                   "  n.nspname AS \"%s\",\n"
3886                                                   "  t.tmplname AS \"%s\",\n"
3887                  "  pg_catalog.obj_description(t.oid, 'pg_ts_template') AS \"%s\"\n",
3888                                                   gettext_noop("Schema"),
3889                                                   gettext_noop("Name"),
3890                                                   gettext_noop("Description"));
3891
3892         appendPQExpBufferStr(&buf, "FROM pg_catalog.pg_ts_template t\n"
3893                  "LEFT JOIN pg_catalog.pg_namespace n ON n.oid = t.tmplnamespace\n");
3894
3895         processSQLNamePattern(pset.db, &buf, pattern, false, false,
3896                                                   "n.nspname", "t.tmplname", NULL,
3897                                                   "pg_catalog.pg_ts_template_is_visible(t.oid)");
3898
3899         appendPQExpBufferStr(&buf, "ORDER BY 1, 2;");
3900
3901         res = PSQLexec(buf.data, false);
3902         termPQExpBuffer(&buf);
3903         if (!res)
3904                 return false;
3905
3906         myopt.nullPrint = NULL;
3907         myopt.title = _("List of text search templates");
3908         myopt.translate_header = true;
3909
3910         printQuery(res, &myopt, pset.queryFout, pset.logfile);
3911
3912         PQclear(res);
3913         return true;
3914 }
3915
3916
3917 /*
3918  * \dF
3919  * list text search configurations
3920  */
3921 bool
3922 listTSConfigs(const char *pattern, bool verbose)
3923 {
3924         PQExpBufferData buf;
3925         PGresult   *res;
3926         printQueryOpt myopt = pset.popt;
3927
3928         if (pset.sversion < 80300)
3929         {
3930                 psql_error("The server (version %d.%d) does not support full text search.\n",
3931                                    pset.sversion / 10000, (pset.sversion / 100) % 100);
3932                 return true;
3933         }
3934
3935         if (verbose)
3936                 return listTSConfigsVerbose(pattern);
3937
3938         initPQExpBuffer(&buf);
3939
3940         printfPQExpBuffer(&buf,
3941                                           "SELECT \n"
3942                                           "   n.nspname as \"%s\",\n"
3943                                           "   c.cfgname as \"%s\",\n"
3944                    "   pg_catalog.obj_description(c.oid, 'pg_ts_config') as \"%s\"\n"
3945                                           "FROM pg_catalog.pg_ts_config c\n"
3946                   "LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.cfgnamespace \n",
3947                                           gettext_noop("Schema"),
3948                                           gettext_noop("Name"),
3949                                           gettext_noop("Description")
3950                 );
3951
3952         processSQLNamePattern(pset.db, &buf, pattern, false, false,
3953                                                   "n.nspname", "c.cfgname", NULL,
3954                                                   "pg_catalog.pg_ts_config_is_visible(c.oid)");
3955
3956         appendPQExpBufferStr(&buf, "ORDER BY 1, 2;");
3957
3958         res = PSQLexec(buf.data, false);
3959         termPQExpBuffer(&buf);
3960         if (!res)
3961                 return false;
3962
3963         myopt.nullPrint = NULL;
3964         myopt.title = _("List of text search configurations");
3965         myopt.translate_header = true;
3966
3967         printQuery(res, &myopt, pset.queryFout, pset.logfile);
3968
3969         PQclear(res);
3970         return true;
3971 }
3972
3973 static bool
3974 listTSConfigsVerbose(const char *pattern)
3975 {
3976         PQExpBufferData buf;
3977         PGresult   *res;
3978         int                     i;
3979
3980         initPQExpBuffer(&buf);
3981
3982         printfPQExpBuffer(&buf,
3983                                           "SELECT c.oid, c.cfgname,\n"
3984                                           "   n.nspname, \n"
3985                                           "   p.prsname, \n"
3986                                           "   np.nspname as pnspname \n"
3987                                           "FROM pg_catalog.pg_ts_config c \n"
3988            "   LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.cfgnamespace, \n"
3989                                           " pg_catalog.pg_ts_parser p \n"
3990           "   LEFT JOIN pg_catalog.pg_namespace np ON np.oid = p.prsnamespace \n"
3991                                           "WHERE  p.oid = c.cfgparser\n"
3992                 );
3993
3994         processSQLNamePattern(pset.db, &buf, pattern, true, false,
3995                                                   "n.nspname", "c.cfgname", NULL,
3996                                                   "pg_catalog.pg_ts_config_is_visible(c.oid)");
3997
3998         appendPQExpBufferStr(&buf, "ORDER BY 3, 2;");
3999
4000         res = PSQLexec(buf.data, false);
4001         termPQExpBuffer(&buf);
4002         if (!res)
4003                 return false;
4004
4005         if (PQntuples(res) == 0)
4006         {
4007                 if (!pset.quiet)
4008                         psql_error("Did not find any text search configuration named \"%s\".\n",
4009                                            pattern);
4010                 PQclear(res);
4011                 return false;
4012         }
4013
4014         for (i = 0; i < PQntuples(res); i++)
4015         {
4016                 const char *oid;
4017                 const char *cfgname;
4018                 const char *nspname = NULL;
4019                 const char *prsname;
4020                 const char *pnspname = NULL;
4021
4022                 oid = PQgetvalue(res, i, 0);
4023                 cfgname = PQgetvalue(res, i, 1);
4024                 if (!PQgetisnull(res, i, 2))
4025                         nspname = PQgetvalue(res, i, 2);
4026                 prsname = PQgetvalue(res, i, 3);
4027                 if (!PQgetisnull(res, i, 4))
4028                         pnspname = PQgetvalue(res, i, 4);
4029
4030                 if (!describeOneTSConfig(oid, nspname, cfgname, pnspname, prsname))
4031                 {
4032                         PQclear(res);
4033                         return false;
4034                 }
4035
4036                 if (cancel_pressed)
4037                 {
4038                         PQclear(res);
4039                         return false;
4040                 }
4041         }
4042
4043         PQclear(res);
4044         return true;
4045 }
4046
4047 static bool
4048 describeOneTSConfig(const char *oid, const char *nspname, const char *cfgname,
4049                                         const char *pnspname, const char *prsname)
4050 {
4051         PQExpBufferData buf,
4052                                 title;
4053         PGresult   *res;
4054         printQueryOpt myopt = pset.popt;
4055
4056         initPQExpBuffer(&buf);
4057
4058         printfPQExpBuffer(&buf,
4059                                           "SELECT \n"
4060                                           "  ( SELECT t.alias FROM \n"
4061                                           "    pg_catalog.ts_token_type(c.cfgparser) AS t \n"
4062                                           "    WHERE t.tokid = m.maptokentype ) AS \"%s\", \n"
4063                                           "  pg_catalog.btrim( \n"
4064                                   "    ARRAY( SELECT mm.mapdict::pg_catalog.regdictionary \n"
4065                                           "           FROM pg_catalog.pg_ts_config_map AS mm \n"
4066                                           "           WHERE mm.mapcfg = m.mapcfg AND mm.maptokentype = m.maptokentype \n"
4067                                           "           ORDER BY mapcfg, maptokentype, mapseqno \n"
4068                                           "    ) :: pg_catalog.text , \n"
4069                                           "  '{}') AS \"%s\" \n"
4070          "FROM pg_catalog.pg_ts_config AS c, pg_catalog.pg_ts_config_map AS m \n"
4071                                           "WHERE c.oid = '%s' AND m.mapcfg = c.oid \n"
4072                                           "GROUP BY m.mapcfg, m.maptokentype, c.cfgparser \n"
4073                                           "ORDER BY 1;",
4074                                           gettext_noop("Token"),
4075                                           gettext_noop("Dictionaries"),
4076                                           oid);
4077
4078         res = PSQLexec(buf.data, false);
4079         termPQExpBuffer(&buf);
4080         if (!res)
4081                 return false;
4082
4083         initPQExpBuffer(&title);
4084
4085         if (nspname)
4086                 appendPQExpBuffer(&title, _("Text search configuration \"%s.%s\""),
4087                                                   nspname, cfgname);
4088         else
4089                 appendPQExpBuffer(&title, _("Text search configuration \"%s\""),
4090                                                   cfgname);
4091
4092         if (pnspname)
4093                 appendPQExpBuffer(&title, _("\nParser: \"%s.%s\""),
4094                                                   pnspname, prsname);
4095         else
4096                 appendPQExpBuffer(&title, _("\nParser: \"%s\""),
4097                                                   prsname);
4098
4099         myopt.nullPrint = NULL;
4100         myopt.title = title.data;
4101         myopt.footers = NULL;
4102         myopt.topt.default_footer = false;
4103         myopt.translate_header = true;
4104
4105         printQuery(res, &myopt, pset.queryFout, pset.logfile);
4106
4107         termPQExpBuffer(&title);
4108
4109         PQclear(res);
4110         return true;
4111 }
4112
4113
4114 /*
4115  * \dew
4116  *
4117  * Describes foreign-data wrappers
4118  */
4119 bool
4120 listForeignDataWrappers(const char *pattern, bool verbose)
4121 {
4122         PQExpBufferData buf;
4123         PGresult   *res;
4124         printQueryOpt myopt = pset.popt;
4125
4126         if (pset.sversion < 80400)
4127         {
4128                 psql_error("The server (version %d.%d) does not support foreign-data wrappers.\n",
4129                                    pset.sversion / 10000, (pset.sversion / 100) % 100);
4130                 return true;
4131         }
4132
4133         initPQExpBuffer(&buf);
4134         printfPQExpBuffer(&buf,
4135                                           "SELECT fdw.fdwname AS \"%s\",\n"
4136                                    "  pg_catalog.pg_get_userbyid(fdw.fdwowner) AS \"%s\",\n",
4137                                           gettext_noop("Name"),
4138                                           gettext_noop("Owner"));
4139         if (pset.sversion >= 90100)
4140                 appendPQExpBuffer(&buf,
4141                                                   "  fdw.fdwhandler::pg_catalog.regproc AS \"%s\",\n",
4142                                                   gettext_noop("Handler"));
4143         appendPQExpBuffer(&buf,
4144                                           "  fdw.fdwvalidator::pg_catalog.regproc AS \"%s\"",
4145                                           gettext_noop("Validator"));
4146
4147         if (verbose)
4148         {
4149                 appendPQExpBufferStr(&buf, ",\n  ");
4150                 printACLColumn(&buf, "fdwacl");
4151                 appendPQExpBuffer(&buf,
4152                                                   ",\n CASE WHEN fdwoptions IS NULL THEN '' ELSE "
4153                                                   "  '(' || array_to_string(ARRAY(SELECT "
4154                                                   "  quote_ident(option_name) ||  ' ' || "
4155                                                   "  quote_literal(option_value)  FROM "
4156                                                   "  pg_options_to_table(fdwoptions)),  ', ') || ')' "
4157                                                   "  END AS \"%s\"",
4158                                                   gettext_noop("FDW Options"));
4159
4160                 if (pset.sversion >= 90100)
4161                         appendPQExpBuffer(&buf,
4162                                                           ",\n  d.description AS \"%s\" ",
4163                                                           gettext_noop("Description"));
4164         }
4165
4166         appendPQExpBufferStr(&buf, "\nFROM pg_catalog.pg_foreign_data_wrapper fdw\n");
4167
4168         if (verbose && pset.sversion >= 90100)
4169                 appendPQExpBufferStr(&buf,
4170                                                          "LEFT JOIN pg_catalog.pg_description d\n"
4171                                                          "       ON d.classoid = fdw.tableoid "
4172                                                          "AND d.objoid = fdw.oid AND d.objsubid = 0\n");
4173
4174         processSQLNamePattern(pset.db, &buf, pattern, false, false,
4175                                                   NULL, "fdwname", NULL, NULL);
4176
4177         appendPQExpBufferStr(&buf, "ORDER BY 1;");
4178
4179         res = PSQLexec(buf.data, false);
4180         termPQExpBuffer(&buf);
4181         if (!res)
4182                 return false;
4183
4184         myopt.nullPrint = NULL;
4185         myopt.title = _("List of foreign-data wrappers");
4186         myopt.translate_header = true;
4187
4188         printQuery(res, &myopt, pset.queryFout, pset.logfile);
4189
4190         PQclear(res);
4191         return true;
4192 }
4193
4194 /*
4195  * \des
4196  *
4197  * Describes foreign servers.
4198  */
4199 bool
4200 listForeignServers(const char *pattern, bool verbose)
4201 {
4202         PQExpBufferData buf;
4203         PGresult   *res;
4204         printQueryOpt myopt = pset.popt;
4205
4206         if (pset.sversion < 80400)
4207         {
4208                 psql_error("The server (version %d.%d) does not support foreign servers.\n",
4209                                    pset.sversion / 10000, (pset.sversion / 100) % 100);
4210                 return true;
4211         }
4212
4213         initPQExpBuffer(&buf);
4214         printfPQExpBuffer(&buf,
4215                                           "SELECT s.srvname AS \"%s\",\n"
4216                                           "  pg_catalog.pg_get_userbyid(s.srvowner) AS \"%s\",\n"
4217                                           "  f.fdwname AS \"%s\"",
4218                                           gettext_noop("Name"),
4219                                           gettext_noop("Owner"),
4220                                           gettext_noop("Foreign-data wrapper"));
4221
4222         if (verbose)
4223         {
4224                 appendPQExpBufferStr(&buf, ",\n  ");
4225                 printACLColumn(&buf, "s.srvacl");
4226                 appendPQExpBuffer(&buf,
4227                                                   ",\n"
4228                                                   "  s.srvtype AS \"%s\",\n"
4229                                                   "  s.srvversion AS \"%s\",\n"
4230                                                   "  CASE WHEN srvoptions IS NULL THEN '' ELSE "
4231                                                   "  '(' || array_to_string(ARRAY(SELECT "
4232                                                   "  quote_ident(option_name) ||  ' ' || "
4233                                                   "  quote_literal(option_value)  FROM "
4234                                                   "  pg_options_to_table(srvoptions)),  ', ') || ')' "
4235                                                   "  END AS \"%s\",\n"
4236                                                   "  d.description AS \"%s\"",
4237                                                   gettext_noop("Type"),
4238                                                   gettext_noop("Version"),
4239                                                   gettext_noop("FDW Options"),
4240                                                   gettext_noop("Description"));
4241         }
4242
4243         appendPQExpBufferStr(&buf,
4244                                                  "\nFROM pg_catalog.pg_foreign_server s\n"
4245            "     JOIN pg_catalog.pg_foreign_data_wrapper f ON f.oid=s.srvfdw\n");
4246
4247         if (verbose)
4248                 appendPQExpBufferStr(&buf,
4249                                                          "LEFT JOIN pg_description d\n       "
4250                                                    "ON d.classoid = s.tableoid AND d.objoid = s.oid "
4251                                                          "AND d.objsubid = 0\n");
4252
4253         processSQLNamePattern(pset.db, &buf, pattern, false, false,
4254                                                   NULL, "s.srvname", NULL, NULL);
4255
4256         appendPQExpBufferStr(&buf, "ORDER BY 1;");
4257
4258         res = PSQLexec(buf.data, false);
4259         termPQExpBuffer(&buf);
4260         if (!res)
4261                 return false;
4262
4263         myopt.nullPrint = NULL;
4264         myopt.title = _("List of foreign servers");
4265         myopt.translate_header = true;
4266
4267         printQuery(res, &myopt, pset.queryFout, pset.logfile);
4268
4269         PQclear(res);
4270         return true;
4271 }
4272
4273 /*
4274  * \deu
4275  *
4276  * Describes user mappings.
4277  */
4278 bool
4279 listUserMappings(const char *pattern, bool verbose)
4280 {
4281         PQExpBufferData buf;
4282         PGresult   *res;
4283         printQueryOpt myopt = pset.popt;
4284
4285         if (pset.sversion < 80400)
4286         {
4287                 psql_error("The server (version %d.%d) does not support user mappings.\n",
4288                                    pset.sversion / 10000, (pset.sversion / 100) % 100);
4289                 return true;
4290         }
4291
4292         initPQExpBuffer(&buf);
4293         printfPQExpBuffer(&buf,
4294                                           "SELECT um.srvname AS \"%s\",\n"
4295                                           "  um.usename AS \"%s\"",
4296                                           gettext_noop("Server"),
4297                                           gettext_noop("User name"));
4298
4299         if (verbose)
4300                 appendPQExpBuffer(&buf,
4301                                                   ",\n CASE WHEN umoptions IS NULL THEN '' ELSE "
4302                                                   "  '(' || array_to_string(ARRAY(SELECT "
4303                                                   "  quote_ident(option_name) ||  ' ' || "
4304                                                   "  quote_literal(option_value)  FROM "
4305                                                   "  pg_options_to_table(umoptions)),  ', ') || ')' "
4306                                                   "  END AS \"%s\"",
4307                                                   gettext_noop("FDW Options"));
4308
4309         appendPQExpBufferStr(&buf, "\nFROM pg_catalog.pg_user_mappings um\n");
4310
4311         processSQLNamePattern(pset.db, &buf, pattern, false, false,
4312                                                   NULL, "um.srvname", "um.usename", NULL);
4313
4314         appendPQExpBufferStr(&buf, "ORDER BY 1, 2;");
4315
4316         res = PSQLexec(buf.data, false);
4317         termPQExpBuffer(&buf);
4318         if (!res)
4319                 return false;
4320
4321         myopt.nullPrint = NULL;
4322         myopt.title = _("List of user mappings");
4323         myopt.translate_header = true;
4324
4325         printQuery(res, &myopt, pset.queryFout, pset.logfile);
4326
4327         PQclear(res);
4328         return true;
4329 }
4330
4331 /*
4332  * \det
4333  *
4334  * Describes foreign tables.
4335  */
4336 bool
4337 listForeignTables(const char *pattern, bool verbose)
4338 {
4339         PQExpBufferData buf;
4340         PGresult   *res;
4341         printQueryOpt myopt = pset.popt;
4342
4343         if (pset.sversion < 90100)
4344         {
4345                 psql_error("The server (version %d.%d) does not support foreign tables.\n",
4346                                    pset.sversion / 10000, (pset.sversion / 100) % 100);
4347                 return true;
4348         }
4349
4350         initPQExpBuffer(&buf);
4351         printfPQExpBuffer(&buf,
4352                                           "SELECT n.nspname AS \"%s\",\n"
4353                                           "  c.relname AS \"%s\",\n"
4354                                           "  s.srvname AS \"%s\"",
4355                                           gettext_noop("Schema"),
4356                                           gettext_noop("Table"),
4357                                           gettext_noop("Server"));
4358
4359         if (verbose)
4360                 appendPQExpBuffer(&buf,
4361                                                   ",\n CASE WHEN ftoptions IS NULL THEN '' ELSE "
4362                                                   "  '(' || array_to_string(ARRAY(SELECT "
4363                                                   "  quote_ident(option_name) ||  ' ' || "
4364                                                   "  quote_literal(option_value)  FROM "
4365                                                   "  pg_options_to_table(ftoptions)),  ', ') || ')' "
4366                                                   "  END AS \"%s\",\n"
4367                                                   "  d.description AS \"%s\"",
4368                                                   gettext_noop("FDW Options"),
4369                                                   gettext_noop("Description"));
4370
4371         appendPQExpBufferStr(&buf,
4372                                                  "\nFROM pg_catalog.pg_foreign_table ft\n"
4373                                                  "  INNER JOIN pg_catalog.pg_class c"
4374                                                  " ON c.oid = ft.ftrelid\n"
4375                                                  "  INNER JOIN pg_catalog.pg_namespace n"
4376                                                  " ON n.oid = c.relnamespace\n"
4377                                                  "  INNER JOIN pg_catalog.pg_foreign_server s"
4378                                                  " ON s.oid = ft.ftserver\n");
4379         if (verbose)
4380                 appendPQExpBufferStr(&buf,
4381                                                          "   LEFT JOIN pg_catalog.pg_description d\n"
4382                                                          "          ON d.classoid = c.tableoid AND "
4383                                                          "d.objoid = c.oid AND d.objsubid = 0\n");
4384
4385         processSQLNamePattern(pset.db, &buf, pattern, false, false,
4386                                                   NULL, "n.nspname", "c.relname", NULL);
4387
4388         appendPQExpBufferStr(&buf, "ORDER BY 1, 2;");
4389
4390         res = PSQLexec(buf.data, false);
4391         termPQExpBuffer(&buf);
4392         if (!res)
4393                 return false;
4394
4395         myopt.nullPrint = NULL;
4396         myopt.title = _("List of foreign tables");
4397         myopt.translate_header = true;
4398
4399         printQuery(res, &myopt, pset.queryFout, pset.logfile);
4400
4401         PQclear(res);
4402         return true;
4403 }
4404
4405 /*
4406  * \dx
4407  *
4408  * Briefly describes installed extensions.
4409  */
4410 bool
4411 listExtensions(const char *pattern)
4412 {
4413         PQExpBufferData buf;
4414         PGresult   *res;
4415         printQueryOpt myopt = pset.popt;
4416
4417         if (pset.sversion < 90100)
4418         {
4419                 psql_error("The server (version %d.%d) does not support extensions.\n",
4420                                    pset.sversion / 10000, (pset.sversion / 100) % 100);
4421                 return true;
4422         }
4423
4424         initPQExpBuffer(&buf);
4425         printfPQExpBuffer(&buf,
4426                                           "SELECT e.extname AS \"%s\", "
4427          "e.extversion AS \"%s\", n.nspname AS \"%s\", c.description AS \"%s\"\n"
4428                                           "FROM pg_catalog.pg_extension e "
4429                          "LEFT JOIN pg_catalog.pg_namespace n ON n.oid = e.extnamespace "
4430                                  "LEFT JOIN pg_catalog.pg_description c ON c.objoid = e.oid "
4431                  "AND c.classoid = 'pg_catalog.pg_extension'::pg_catalog.regclass\n",
4432                                           gettext_noop("Name"),
4433                                           gettext_noop("Version"),
4434                                           gettext_noop("Schema"),
4435                                           gettext_noop("Description"));
4436
4437         processSQLNamePattern(pset.db, &buf, pattern,
4438                                                   false, false,
4439                                                   NULL, "e.extname", NULL,
4440                                                   NULL);
4441
4442         appendPQExpBufferStr(&buf, "ORDER BY 1;");
4443
4444         res = PSQLexec(buf.data, false);
4445         termPQExpBuffer(&buf);
4446         if (!res)
4447                 return false;
4448
4449         myopt.nullPrint = NULL;
4450         myopt.title = _("List of installed extensions");
4451         myopt.translate_header = true;
4452
4453         printQuery(res, &myopt, pset.queryFout, pset.logfile);
4454
4455         PQclear(res);
4456         return true;
4457 }
4458
4459 /*
4460  * \dx+
4461  *
4462  * List contents of installed extensions.
4463  */
4464 bool
4465 listExtensionContents(const char *pattern)
4466 {
4467         PQExpBufferData buf;
4468         PGresult   *res;
4469         int                     i;
4470
4471         if (pset.sversion < 90100)
4472         {
4473                 psql_error("The server (version %d.%d) does not support extensions.\n",
4474                                    pset.sversion / 10000, (pset.sversion / 100) % 100);
4475                 return true;
4476         }
4477
4478         initPQExpBuffer(&buf);
4479         printfPQExpBuffer(&buf,
4480                                           "SELECT e.extname, e.oid\n"
4481                                           "FROM pg_catalog.pg_extension e\n");
4482
4483         processSQLNamePattern(pset.db, &buf, pattern,
4484                                                   false, false,
4485                                                   NULL, "e.extname", NULL,
4486                                                   NULL);
4487
4488         appendPQExpBufferStr(&buf, "ORDER BY 1;");
4489
4490         res = PSQLexec(buf.data, false);
4491         termPQExpBuffer(&buf);
4492         if (!res)
4493                 return false;
4494
4495         if (PQntuples(res) == 0)
4496         {
4497                 if (!pset.quiet)
4498                 {
4499                         if (pattern)
4500                                 psql_error("Did not find any extension named \"%s\".\n",
4501                                                    pattern);
4502                         else
4503                                 psql_error("Did not find any extensions.\n");
4504                 }
4505                 PQclear(res);
4506                 return false;
4507         }
4508
4509         for (i = 0; i < PQntuples(res); i++)
4510         {
4511                 const char *extname;
4512                 const char *oid;
4513
4514                 extname = PQgetvalue(res, i, 0);
4515                 oid = PQgetvalue(res, i, 1);
4516
4517                 if (!listOneExtensionContents(extname, oid))
4518                 {
4519                         PQclear(res);
4520                         return false;
4521                 }
4522                 if (cancel_pressed)
4523                 {
4524                         PQclear(res);
4525                         return false;
4526                 }
4527         }
4528
4529         PQclear(res);
4530         return true;
4531 }
4532
4533 static bool
4534 listOneExtensionContents(const char *extname, const char *oid)
4535 {
4536         PQExpBufferData buf;
4537         PGresult   *res;
4538         char            title[1024];
4539         printQueryOpt myopt = pset.popt;
4540
4541         initPQExpBuffer(&buf);
4542         printfPQExpBuffer(&buf,
4543                 "SELECT pg_catalog.pg_describe_object(classid, objid, 0) AS \"%s\"\n"
4544                                           "FROM pg_catalog.pg_depend\n"
4545                                           "WHERE refclassid = 'pg_catalog.pg_extension'::pg_catalog.regclass AND refobjid = '%s' AND deptype = 'e'\n"
4546                                           "ORDER BY 1;",
4547                                           gettext_noop("Object Description"),
4548                                           oid);
4549
4550         res = PSQLexec(buf.data, false);
4551         termPQExpBuffer(&buf);
4552         if (!res)
4553                 return false;
4554
4555         myopt.nullPrint = NULL;
4556         snprintf(title, sizeof(title), _("Objects in extension \"%s\""), extname);
4557         myopt.title = title;
4558         myopt.translate_header = true;
4559
4560         printQuery(res, &myopt, pset.queryFout, pset.logfile);
4561
4562         PQclear(res);
4563         return true;
4564 }
4565
4566 /*
4567  * printACLColumn
4568  *
4569  * Helper function for consistently formatting ACL (privilege) columns.
4570  * The proper targetlist entry is appended to buf.  Note lack of any
4571  * whitespace or comma decoration.
4572  */
4573 static void
4574 printACLColumn(PQExpBuffer buf, const char *colname)
4575 {
4576         if (pset.sversion >= 80100)
4577                 appendPQExpBuffer(buf,
4578                                                   "pg_catalog.array_to_string(%s, E'\\n') AS \"%s\"",
4579                                                   colname, gettext_noop("Access privileges"));
4580         else
4581                 appendPQExpBuffer(buf,
4582                                                   "pg_catalog.array_to_string(%s, '\\n') AS \"%s\"",
4583                                                   colname, gettext_noop("Access privileges"));
4584 }