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