granicus.if.org Git - postgresql/blob - src/bin/psql/describe.c

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