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