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