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