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