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