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