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