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

   1 /*
   2  * psql - the PostgreSQL interactive terminal
   3  *
   4  * Copyright (c) 2000-2007, PostgreSQL Global Development Group
   5  *
   6  * $PostgreSQL: pgsql/src/bin/psql/describe.c,v 1.151 2007/02/14 01:58:58 tgl Exp $
   7  */
   8 #include "postgres_fe.h"
   9 #include "describe.h"
  10
  11 #include "dumputils.h"
  12
  13 #include "common.h"
  14 #include "settings.h"
  15 #include "print.h"
  16 #include "variables.h"
  17
  18 #include <ctype.h>
  19
  20 #ifdef WIN32
  21 /*
  22  * mbvalidate() is used in function describeOneTableDetails() to make sure
  23  * all characters of the cells will be printed to the DOS console in a
  24  * correct way
  25  */
  26 #include "mbprint.h"
  27 #endif
  28
  29
  30 static bool describeOneTableDetails(const char *schemaname,
  31                                                 const char *relationname,
  32                                                 const char *oid,
  33                                                 bool verbose);
  34 static bool add_tablespace_footer(char relkind, Oid tablespace, char **footers,
  35                                           int *count, PQExpBufferData buf, bool newline);
  36
  37 /*----------------
  38  * Handlers for various slash commands displaying some sort of list
  39  * of things in the database.
  40  *
  41  * If you add something here, try to format the query to look nice in -E output.
  42  *----------------
  43  */
  44
  45
  46 /* \da
  47  * Takes an optional regexp to select particular aggregates
  48  */
  49 bool
  50 describeAggregates(const char *pattern, bool verbose)
  51 {
  52         PQExpBufferData buf;
  53         PGresult   *res;
  54         printQueryOpt myopt = pset.popt;
  55
  56         initPQExpBuffer(&buf);
  57
  58         /*
  59          * There are two kinds of aggregates: ones that work on particular types
  60          * and ones that work on all (denoted by input type = "any")
  61          */
  62         printfPQExpBuffer(&buf,
  63                                           "SELECT n.nspname as \"%s\",\n"
  64                                           "  p.proname AS \"%s\",\n"
  65                                           "  CASE WHEN p.pronargs = 0\n"
  66                                           "    THEN CAST('*' AS pg_catalog.text)\n"
  67                                           "    ELSE\n"
  68                                           "    pg_catalog.array_to_string(ARRAY(\n"
  69                                           "      SELECT\n"
  70                                  "        pg_catalog.format_type(p.proargtypes[s.i], NULL)\n"
  71                                           "      FROM\n"
  72                                           "        pg_catalog.generate_series(0, pg_catalog.array_upper(p.proargtypes, 1)) AS s(i)\n"
  73                                           "    ), ', ')\n"
  74                                           "  END AS \"%s\",\n"
  75                                  "  pg_catalog.obj_description(p.oid, 'pg_proc') as \"%s\"\n"
  76                                           "FROM pg_catalog.pg_proc p\n"
  77            "     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace\n"
  78                                           "WHERE p.proisagg\n",
  79                                           _("Schema"), _("Name"),
  80                                           _("Argument data types"), _("Description"));
  81
  82         processSQLNamePattern(pset.db, &buf, pattern, true, false,
  83                                                   "n.nspname", "p.proname", NULL,
  84                                                   "pg_catalog.pg_function_is_visible(p.oid)");
  85
  86         appendPQExpBuffer(&buf, "ORDER BY 1, 2, 3;");
  87
  88         res = PSQLexec(buf.data, false);
  89         termPQExpBuffer(&buf);
  90         if (!res)
  91                 return false;
  92
  93         myopt.nullPrint = NULL;
  94         myopt.title = _("List of aggregate functions");
  95
  96         printQuery(res, &myopt, pset.queryFout, pset.logfile);
  97
  98         PQclear(res);
  99         return true;
 100 }
 101
 102 /* \db
 103  * Takes an optional regexp to select particular tablespaces
 104  */
 105 bool
 106 describeTablespaces(const char *pattern, bool verbose)
 107 {
 108         PQExpBufferData buf;
 109         PGresult   *res;
 110         printQueryOpt myopt = pset.popt;
 111
 112         if (pset.sversion < 80000)
 113         {
 114                 fprintf(stderr, _("The server version (%d) does not support tablespaces.\n"),
 115                                 pset.sversion);
 116                 return true;
 117         }
 118
 119         initPQExpBuffer(&buf);
 120
 121         printfPQExpBuffer(&buf,
 122                                           "SELECT spcname AS \"%s\",\n"
 123                                           "  pg_catalog.pg_get_userbyid(spcowner) AS \"%s\",\n"
 124                                           "  spclocation AS \"%s\"",
 125                                           _("Name"), _("Owner"), _("Location"));
 126
 127         if (verbose)
 128                 appendPQExpBuffer(&buf,
 129                                                   ",\n  spcacl as \"%s\""
 130                  ",\n  pg_catalog.shobj_description(oid, 'pg_tablespace') AS \"%s\"",
 131                                                   _("Access privileges"), _("Description"));
 132
 133         appendPQExpBuffer(&buf,
 134                                           "\nFROM pg_catalog.pg_tablespace\n");
 135
 136         processSQLNamePattern(pset.db, &buf, pattern, false, false,
 137                                                   NULL, "spcname", NULL,
 138                                                   NULL);
 139
 140         appendPQExpBuffer(&buf, "ORDER BY 1;");
 141
 142         res = PSQLexec(buf.data, false);
 143         termPQExpBuffer(&buf);
 144         if (!res)
 145                 return false;
 146
 147         myopt.nullPrint = NULL;
 148         myopt.title = _("List of tablespaces");
 149
 150         printQuery(res, &myopt, pset.queryFout, pset.logfile);
 151
 152         PQclear(res);
 153         return true;
 154 }
 155
 156
 157 /* \df
 158  * Takes an optional regexp to select particular functions
 159  */
 160 bool
 161 describeFunctions(const char *pattern, bool verbose)
 162 {
 163         PQExpBufferData buf;
 164         PGresult   *res;
 165         printQueryOpt myopt = pset.popt;
 166
 167         initPQExpBuffer(&buf);
 168
 169         printfPQExpBuffer(&buf,
 170                                           "SELECT n.nspname as \"%s\",\n"
 171                                           "  p.proname as \"%s\",\n"
 172                                           "  CASE WHEN p.proretset THEN 'setof ' ELSE '' END ||\n"
 173                                   "  pg_catalog.format_type(p.prorettype, NULL) as \"%s\",\n"
 174                                           "  CASE WHEN proallargtypes IS NOT NULL THEN\n"
 175                                           "    pg_catalog.array_to_string(ARRAY(\n"
 176                                           "      SELECT\n"
 177                                           "        CASE\n"
 178                                           "          WHEN p.proargmodes[s.i] = 'i' THEN ''\n"
 179                                           "          WHEN p.proargmodes[s.i] = 'o' THEN 'OUT '\n"
 180                                         "          WHEN p.proargmodes[s.i] = 'b' THEN 'INOUT '\n"
 181                                           "        END ||\n"
 182                                           "        CASE\n"
 183                          "          WHEN COALESCE(p.proargnames[s.i], '') = '' THEN ''\n"
 184                                           "          ELSE p.proargnames[s.i] || ' ' \n"
 185                                           "        END ||\n"
 186                           "        pg_catalog.format_type(p.proallargtypes[s.i], NULL)\n"
 187                                           "      FROM\n"
 188                                           "        pg_catalog.generate_series(1, pg_catalog.array_upper(p.proallargtypes, 1)) AS s(i)\n"
 189                                           "    ), ', ')\n"
 190                                           "  ELSE\n"
 191                                           "    pg_catalog.array_to_string(ARRAY(\n"
 192                                           "      SELECT\n"
 193                                           "        CASE\n"
 194                    "          WHEN COALESCE(p.proargnames[s.i+1], '') = '' THEN ''\n"
 195                                           "          ELSE p.proargnames[s.i+1] || ' '\n"
 196                                           "          END ||\n"
 197                                  "        pg_catalog.format_type(p.proargtypes[s.i], NULL)\n"
 198                                           "      FROM\n"
 199                                           "        pg_catalog.generate_series(0, pg_catalog.array_upper(p.proargtypes, 1)) AS s(i)\n"
 200                                           "    ), ', ')\n"
 201                                           "  END AS \"%s\"",
 202                                           _("Schema"), _("Name"), _("Result data type"),
 203                                           _("Argument data types"));
 204
 205         if (verbose)
 206                 appendPQExpBuffer(&buf,
 207                                                   ",\n  r.rolname as \"%s\",\n"
 208                                                   "  l.lanname as \"%s\",\n"
 209                                                   "  p.prosrc as \"%s\",\n"
 210                                   "  pg_catalog.obj_description(p.oid, 'pg_proc') as \"%s\"",
 211                                                   _("Owner"), _("Language"),
 212                                                   _("Source code"), _("Description"));
 213
 214         if (!verbose)
 215                 appendPQExpBuffer(&buf,
 216                                                   "\nFROM pg_catalog.pg_proc p"
 217                                                   "\n     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace\n");
 218         else
 219                 appendPQExpBuffer(&buf,
 220                                                   "\nFROM pg_catalog.pg_proc p"
 221                 "\n     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace"
 222                          "\n     LEFT JOIN pg_catalog.pg_language l ON l.oid = p.prolang"
 223                                 "\n     JOIN pg_catalog.pg_roles r ON r.oid = p.proowner\n");
 224
 225         /*
 226          * we skip in/out funcs by excluding functions that take or return cstring
 227          */
 228         appendPQExpBuffer(&buf,
 229                    "WHERE p.prorettype <> 'pg_catalog.cstring'::pg_catalog.regtype\n"
 230                                           "      AND (p.proargtypes[0] IS NULL\n"
 231                                           "      OR   p.proargtypes[0] <> 'pg_catalog.cstring'::pg_catalog.regtype)\n"
 232                                           "      AND NOT p.proisagg\n");
 233
 234         processSQLNamePattern(pset.db, &buf, pattern, true, false,
 235                                                   "n.nspname", "p.proname", NULL,
 236                                                   "pg_catalog.pg_function_is_visible(p.oid)");
 237
 238         appendPQExpBuffer(&buf, "ORDER BY 1, 2, 3, 4;");
 239
 240         res = PSQLexec(buf.data, false);
 241         termPQExpBuffer(&buf);
 242         if (!res)
 243                 return false;
 244
 245         myopt.nullPrint = NULL;
 246         myopt.title = _("List of functions");
 247
 248         printQuery(res, &myopt, pset.queryFout, pset.logfile);
 249
 250         PQclear(res);
 251         return true;
 252 }
 253
 254
 255
 256 /*
 257  * \dT
 258  * describe types
 259  */
 260 bool
 261 describeTypes(const char *pattern, bool verbose)
 262 {
 263         PQExpBufferData buf;
 264         PGresult   *res;
 265         printQueryOpt myopt = pset.popt;
 266
 267         initPQExpBuffer(&buf);
 268
 269         printfPQExpBuffer(&buf,
 270                                           "SELECT n.nspname as \"%s\",\n"
 271                                           "  pg_catalog.format_type(t.oid, NULL) AS \"%s\",\n",
 272                                           _("Schema"), _("Name"));
 273         if (verbose)
 274                 appendPQExpBuffer(&buf,
 275                                                   "  t.typname AS \"%s\",\n"
 276                                                   "  CASE WHEN t.typrelid != 0\n"
 277                                                   "      THEN CAST('tuple' AS pg_catalog.text)\n"
 278                                                   "    WHEN t.typlen < 0\n"
 279                                                   "      THEN CAST('var' AS pg_catalog.text)\n"
 280                                                   "    ELSE CAST(t.typlen AS pg_catalog.text)\n"
 281                                                   "  END AS \"%s\",\n",
 282                                                   _("Internal name"), _("Size"));
 283         appendPQExpBuffer(&buf,
 284                                 "  pg_catalog.obj_description(t.oid, 'pg_type') as \"%s\"\n",
 285                                           _("Description"));
 286
 287         appendPQExpBuffer(&buf, "FROM pg_catalog.pg_type t\n"
 288          "     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = t.typnamespace\n");
 289
 290         /*
 291          * do not include array types (start with underscore); do not include
 292          * complex types (typrelid!=0) unless they are standalone composite types
 293          */
 294         appendPQExpBuffer(&buf, "WHERE (t.typrelid = 0 ");
 295         appendPQExpBuffer(&buf, "OR (SELECT c.relkind = 'c' FROM pg_catalog.pg_class c "
 296                                           "WHERE c.oid = t.typrelid)) ");
 297         appendPQExpBuffer(&buf, "AND t.typname !~ '^_'\n");
 298
 299         /* Match name pattern against either internal or external name */
 300         processSQLNamePattern(pset.db, &buf, pattern, true, false,
 301                                                   "n.nspname", "t.typname",
 302                                                   "pg_catalog.format_type(t.oid, NULL)",
 303                                                   "pg_catalog.pg_type_is_visible(t.oid)");
 304
 305         appendPQExpBuffer(&buf, "ORDER BY 1, 2;");
 306
 307         res = PSQLexec(buf.data, false);
 308         termPQExpBuffer(&buf);
 309         if (!res)
 310                 return false;
 311
 312         myopt.nullPrint = NULL;
 313         myopt.title = _("List of data types");
 314
 315         printQuery(res, &myopt, pset.queryFout, pset.logfile);
 316
 317         PQclear(res);
 318         return true;
 319 }
 320
 321
 322
 323 /* \do
 324  */
 325 bool
 326 describeOperators(const char *pattern)
 327 {
 328         PQExpBufferData buf;
 329         PGresult   *res;
 330         printQueryOpt myopt = pset.popt;
 331
 332         initPQExpBuffer(&buf);
 333
 334         printfPQExpBuffer(&buf,
 335                                           "SELECT n.nspname as \"%s\",\n"
 336                                           "  o.oprname AS \"%s\",\n"
 337                                           "  CASE WHEN o.oprkind='l' THEN NULL ELSE pg_catalog.format_type(o.oprleft, NULL) END AS \"%s\",\n"
 338                                           "  CASE WHEN o.oprkind='r' THEN NULL ELSE pg_catalog.format_type(o.oprright, NULL) END AS \"%s\",\n"
 339                                    "  pg_catalog.format_type(o.oprresult, NULL) AS \"%s\",\n"
 340                          "  coalesce(pg_catalog.obj_description(o.oid, 'pg_operator'),\n"
 341         "           pg_catalog.obj_description(o.oprcode, 'pg_proc')) AS \"%s\"\n"
 342                                           "FROM pg_catalog.pg_operator o\n"
 343           "     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = o.oprnamespace\n",
 344                                           _("Schema"), _("Name"),
 345                                           _("Left arg type"), _("Right arg type"),
 346                                           _("Result type"), _("Description"));
 347
 348         processSQLNamePattern(pset.db, &buf, pattern, false, true,
 349                                                   "n.nspname", "o.oprname", NULL,
 350                                                   "pg_catalog.pg_operator_is_visible(o.oid)");
 351
 352         appendPQExpBuffer(&buf, "ORDER BY 1, 2, 3, 4;");
 353
 354         res = PSQLexec(buf.data, false);
 355         termPQExpBuffer(&buf);
 356         if (!res)
 357                 return false;
 358
 359         myopt.nullPrint = NULL;
 360         myopt.title = _("List of operators");
 361
 362         printQuery(res, &myopt, pset.queryFout, pset.logfile);
 363
 364         PQclear(res);
 365         return true;
 366 }
 367
 368
 369 /*
 370  * listAllDbs
 371  *
 372  * for \l, \list, and -l switch
 373  */
 374 bool
 375 listAllDbs(bool verbose)
 376 {
 377         PGresult   *res;
 378         PQExpBufferData buf;
 379         printQueryOpt myopt = pset.popt;
 380
 381         initPQExpBuffer(&buf);
 382
 383         printfPQExpBuffer(&buf,
 384                                           "SELECT d.datname as \"%s\",\n"
 385                                           "       r.rolname as \"%s\"",
 386                                           _("Name"), _("Owner"));
 387         appendPQExpBuffer(&buf,
 388                         ",\n       pg_catalog.pg_encoding_to_char(d.encoding) as \"%s\"",
 389                                           _("Encoding"));
 390         if (verbose)
 391         {
 392                 appendPQExpBuffer(&buf,
 393                                                   ",\n       t.spcname as \"%s\"",
 394                                                   _("Tablespace"));
 395                 appendPQExpBuffer(&buf,
 396                                                   ",\n       pg_catalog.shobj_description(d.oid, 'pg_database') as \"%s\"",
 397                                                   _("Description"));
 398         }
 399         appendPQExpBuffer(&buf,
 400                                           "\nFROM pg_catalog.pg_database d"
 401                                           "\n  JOIN pg_catalog.pg_roles r ON d.datdba = r.oid\n");
 402         if (verbose)
 403                 appendPQExpBuffer(&buf,
 404                    "  JOIN pg_catalog.pg_tablespace t on d.dattablespace = t.oid\n");
 405         appendPQExpBuffer(&buf, "ORDER BY 1;");
 406         res = PSQLexec(buf.data, false);
 407         termPQExpBuffer(&buf);
 408         if (!res)
 409                 return false;
 410
 411         myopt.nullPrint = NULL;
 412         myopt.title = _("List of databases");
 413
 414         printQuery(res, &myopt, pset.queryFout, pset.logfile);
 415
 416         PQclear(res);
 417         return true;
 418 }
 419
 420
 421 /*
 422  * List Tables Grant/Revoke Permissions
 423  * \z (now also \dp -- perhaps more mnemonic)
 424  */
 425 bool
 426 permissionsList(const char *pattern)
 427 {
 428         PQExpBufferData buf;
 429         PGresult   *res;
 430         printQueryOpt myopt = pset.popt;
 431
 432         initPQExpBuffer(&buf);
 433
 434         /*
 435          * we ignore indexes and toast tables since they have no meaningful rights
 436          */
 437         printfPQExpBuffer(&buf,
 438                                           "SELECT n.nspname as \"%s\",\n"
 439                                           "  c.relname as \"%s\",\n"
 440                                           "  CASE c.relkind WHEN 'r' THEN '%s' WHEN 'v' THEN '%s' WHEN 'S' THEN '%s' END as \"%s\",\n"
 441                                           "  c.relacl as \"%s\"\n"
 442                                           "FROM pg_catalog.pg_class c\n"
 443            "     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace\n"
 444                                           "WHERE c.relkind IN ('r', 'v', 'S')\n",
 445                                           _("Schema"), _("Name"), _("table"), _("view"), _("sequence"), _("Type"), _("Access privileges"));
 446
 447         /*
 448          * Unless a schema pattern is specified, we suppress system and temp
 449          * tables, since they normally aren't very interesting from a permissions
 450          * point of view.  You can see 'em by explicit request though, eg with \z
 451          * pg_catalog.*
 452          */
 453         processSQLNamePattern(pset.db, &buf, pattern, true, false,
 454                                                   "n.nspname", "c.relname", NULL,
 455                         "n.nspname !~ '^pg_' AND pg_catalog.pg_table_is_visible(c.oid)");
 456
 457         appendPQExpBuffer(&buf, "ORDER BY 1, 2;");
 458
 459         res = PSQLexec(buf.data, false);
 460         if (!res)
 461         {
 462                 termPQExpBuffer(&buf);
 463                 return false;
 464         }
 465
 466         myopt.nullPrint = NULL;
 467         printfPQExpBuffer(&buf, _("Access privileges for database \"%s\""), PQdb(pset.db));
 468         myopt.title = buf.data;
 469
 470         printQuery(res, &myopt, pset.queryFout, pset.logfile);
 471
 472         termPQExpBuffer(&buf);
 473         PQclear(res);
 474         return true;
 475 }
 476
 477
 478
 479 /*
 480  * Get object comments
 481  *
 482  * \dd [foo]
 483  *
 484  * Note: This only lists things that actually have a description. For complete
 485  * lists of things, there are other \d? commands.
 486  */
 487 bool
 488 objectDescription(const char *pattern)
 489 {
 490         PQExpBufferData buf;
 491         PGresult   *res;
 492         printQueryOpt myopt = pset.popt;
 493
 494         initPQExpBuffer(&buf);
 495
 496         appendPQExpBuffer(&buf,
 497                                           "SELECT DISTINCT tt.nspname AS \"%s\", tt.name AS \"%s\", tt.object AS \"%s\", d.description AS \"%s\"\n"
 498                                           "FROM (\n",
 499                                           _("Schema"), _("Name"), _("Object"), _("Description"));
 500
 501         /* Aggregate descriptions */
 502         appendPQExpBuffer(&buf,
 503                                           "  SELECT p.oid as oid, p.tableoid as tableoid,\n"
 504                                           "  n.nspname as nspname,\n"
 505                                           "  CAST(p.proname AS pg_catalog.text) as name,"
 506                                           "  CAST('%s' AS pg_catalog.text) as object\n"
 507                                           "  FROM pg_catalog.pg_proc p\n"
 508          "       LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace\n"
 509                                           "  WHERE p.proisagg\n",
 510                                           _("aggregate"));
 511         processSQLNamePattern(pset.db, &buf, pattern, true, false,
 512                                                   "n.nspname", "p.proname", NULL,
 513                                                   "pg_catalog.pg_function_is_visible(p.oid)");
 514
 515         /* Function descriptions (except in/outs for datatypes) */
 516         appendPQExpBuffer(&buf,
 517                                           "UNION ALL\n"
 518                                           "  SELECT p.oid as oid, p.tableoid as tableoid,\n"
 519                                           "  n.nspname as nspname,\n"
 520                                           "  CAST(p.proname AS pg_catalog.text) as name,"
 521                                           "  CAST('%s' AS pg_catalog.text) as object\n"
 522                                           "  FROM pg_catalog.pg_proc p\n"
 523          "       LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace\n"
 524
 525                  "  WHERE p.prorettype <> 'pg_catalog.cstring'::pg_catalog.regtype\n"
 526                                           "      AND (p.proargtypes[0] IS NULL\n"
 527                                           "      OR   p.proargtypes[0] <> 'pg_catalog.cstring'::pg_catalog.regtype)\n"
 528                                           "      AND NOT p.proisagg\n",
 529                                           _("function"));
 530         processSQLNamePattern(pset.db, &buf, pattern, true, false,
 531                                                   "n.nspname", "p.proname", NULL,
 532                                                   "pg_catalog.pg_function_is_visible(p.oid)");
 533
 534         /* Operator descriptions (only if operator has its own comment) */
 535         appendPQExpBuffer(&buf,
 536                                           "UNION ALL\n"
 537                                           "  SELECT o.oid as oid, o.tableoid as tableoid,\n"
 538                                           "  n.nspname as nspname,\n"
 539                                           "  CAST(o.oprname AS pg_catalog.text) as name,"
 540                                           "  CAST('%s' AS pg_catalog.text) as object\n"
 541                                           "  FROM pg_catalog.pg_operator o\n"
 542         "       LEFT JOIN pg_catalog.pg_namespace n ON n.oid = o.oprnamespace\n",
 543                                           _("operator"));
 544         processSQLNamePattern(pset.db, &buf, pattern, false, false,
 545                                                   "n.nspname", "o.oprname", NULL,
 546                                                   "pg_catalog.pg_operator_is_visible(o.oid)");
 547
 548         /* Type description */
 549         appendPQExpBuffer(&buf,
 550                                           "UNION ALL\n"
 551                                           "  SELECT t.oid as oid, t.tableoid as tableoid,\n"
 552                                           "  n.nspname as nspname,\n"
 553                                           "  pg_catalog.format_type(t.oid, NULL) as name,"
 554                                           "  CAST('%s' AS pg_catalog.text) as object\n"
 555                                           "  FROM pg_catalog.pg_type t\n"
 556         "       LEFT JOIN pg_catalog.pg_namespace n ON n.oid = t.typnamespace\n",
 557                                           _("data type"));
 558         processSQLNamePattern(pset.db, &buf, pattern, false, false,
 559                                                   "n.nspname", "pg_catalog.format_type(t.oid, NULL)",
 560                                                   NULL,
 561                                                   "pg_catalog.pg_type_is_visible(t.oid)");
 562
 563         /* Relation (tables, views, indexes, sequences) descriptions */
 564         appendPQExpBuffer(&buf,
 565                                           "UNION ALL\n"
 566                                           "  SELECT c.oid as oid, c.tableoid as tableoid,\n"
 567                                           "  n.nspname as nspname,\n"
 568                                           "  CAST(c.relname AS pg_catalog.text) as name,\n"
 569                                           "  CAST(\n"
 570                                           "    CASE c.relkind WHEN 'r' THEN '%s' WHEN 'v' THEN '%s' WHEN 'i' THEN '%s' WHEN 'S' THEN '%s' END"
 571                                           "  AS pg_catalog.text) as object\n"
 572                                           "  FROM pg_catalog.pg_class c\n"
 573          "       LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace\n"
 574                                           "  WHERE c.relkind IN ('r', 'v', 'i', 'S')\n",
 575                                           _("table"), _("view"), _("index"), _("sequence"));
 576         processSQLNamePattern(pset.db, &buf, pattern, true, false,
 577                                                   "n.nspname", "c.relname", NULL,
 578                                                   "pg_catalog.pg_table_is_visible(c.oid)");
 579
 580         /* Rule description (ignore rules for views) */
 581         appendPQExpBuffer(&buf,
 582                                           "UNION ALL\n"
 583                                           "  SELECT r.oid as oid, r.tableoid as tableoid,\n"
 584                                           "  n.nspname as nspname,\n"
 585                                           "  CAST(r.rulename AS pg_catalog.text) as name,"
 586                                           "  CAST('%s' AS pg_catalog.text) as object\n"
 587                                           "  FROM pg_catalog.pg_rewrite r\n"
 588                                   "       JOIN pg_catalog.pg_class c ON c.oid = r.ev_class\n"
 589          "       LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace\n"
 590                                           "  WHERE r.rulename != '_RETURN'\n",
 591                                           _("rule"));
 592         /* XXX not sure what to do about visibility rule here? */
 593         processSQLNamePattern(pset.db, &buf, pattern, true, false,
 594                                                   "n.nspname", "r.rulename", NULL,
 595                                                   "pg_catalog.pg_table_is_visible(c.oid)");
 596
 597         /* Trigger description */
 598         appendPQExpBuffer(&buf,
 599                                           "UNION ALL\n"
 600                                           "  SELECT t.oid as oid, t.tableoid as tableoid,\n"
 601                                           "  n.nspname as nspname,\n"
 602                                           "  CAST(t.tgname AS pg_catalog.text) as name,"
 603                                           "  CAST('%s' AS pg_catalog.text) as object\n"
 604                                           "  FROM pg_catalog.pg_trigger t\n"
 605                                    "       JOIN pg_catalog.pg_class c ON c.oid = t.tgrelid\n"
 606         "       LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace\n",
 607                                           _("trigger"));
 608         /* XXX not sure what to do about visibility rule here? */
 609         processSQLNamePattern(pset.db, &buf, pattern, false, false,
 610                                                   "n.nspname", "t.tgname", NULL,
 611                                                   "pg_catalog.pg_table_is_visible(c.oid)");
 612
 613         appendPQExpBuffer(&buf,
 614                                           ") AS tt\n"
 615                                           "  JOIN pg_catalog.pg_description d ON (tt.oid = d.objoid AND tt.tableoid = d.classoid AND d.objsubid = 0)\n");
 616
 617         appendPQExpBuffer(&buf, "ORDER BY 1, 2, 3;");
 618
 619         res = PSQLexec(buf.data, false);
 620         termPQExpBuffer(&buf);
 621         if (!res)
 622                 return false;
 623
 624         myopt.nullPrint = NULL;
 625         myopt.title = _("Object descriptions");
 626
 627         printQuery(res, &myopt, pset.queryFout, pset.logfile);
 628
 629         PQclear(res);
 630         return true;
 631 }
 632
 633
 634
 635 /*
 636  * describeTableDetails (for \d)
 637  *
 638  * This routine finds the tables to be displayed, and calls
 639  * describeOneTableDetails for each one.
 640  *
 641  * verbose: if true, this is \d+
 642  */
 643 bool
 644 describeTableDetails(const char *pattern, bool verbose)
 645 {
 646         PQExpBufferData buf;
 647         PGresult   *res;
 648         int                     i;
 649
 650         initPQExpBuffer(&buf);
 651
 652         printfPQExpBuffer(&buf,
 653                                           "SELECT c.oid,\n"
 654                                           "  n.nspname,\n"
 655                                           "  c.relname\n"
 656                                           "FROM pg_catalog.pg_class c\n"
 657          "     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace\n");
 658
 659         processSQLNamePattern(pset.db, &buf, pattern, false, false,
 660                                                   "n.nspname", "c.relname", NULL,
 661                                                   "pg_catalog.pg_table_is_visible(c.oid)");
 662
 663         appendPQExpBuffer(&buf, "ORDER BY 2, 3;");
 664
 665         res = PSQLexec(buf.data, false);
 666         termPQExpBuffer(&buf);
 667         if (!res)
 668                 return false;
 669
 670         if (PQntuples(res) == 0)
 671         {
 672                 if (!pset.quiet)
 673                         fprintf(stderr, _("Did not find any relation named \"%s\".\n"),
 674                                         pattern);
 675                 PQclear(res);
 676                 return false;
 677         }
 678
 679         for (i = 0; i < PQntuples(res); i++)
 680         {
 681                 const char *oid;
 682                 const char *nspname;
 683                 const char *relname;
 684
 685                 oid = PQgetvalue(res, i, 0);
 686                 nspname = PQgetvalue(res, i, 1);
 687                 relname = PQgetvalue(res, i, 2);
 688
 689                 if (!describeOneTableDetails(nspname, relname, oid, verbose))
 690                 {
 691                         PQclear(res);
 692                         return false;
 693                 }
 694                 if (cancel_pressed)
 695                 {
 696                         PQclear(res);
 697                         return false;
 698                 }
 699         }
 700
 701         PQclear(res);
 702         return true;
 703 }
 704
 705 /*
 706  * describeOneTableDetails (for \d)
 707  *
 708  * Unfortunately, the information presented here is so complicated that it
 709  * cannot be done in a single query. So we have to assemble the printed table
 710  * by hand and pass it to the underlying printTable() function.
 711  */
 712 static bool
 713 describeOneTableDetails(const char *schemaname,
 714                                                 const char *relationname,
 715                                                 const char *oid,
 716                                                 bool verbose)
 717 {
 718         PQExpBufferData buf;
 719         PGresult   *res = NULL;
 720         printTableOpt myopt = pset.popt.topt;
 721         int                     i;
 722         char       *view_def = NULL;
 723         const char *headers[5];
 724         char      **cells = NULL;
 725         char      **footers = NULL;
 726         char      **ptr;
 727         PQExpBufferData title;
 728         PQExpBufferData tmpbuf;
 729         int                     cols = 0;
 730         int                     numrows = 0;
 731         struct
 732         {
 733                 int16           checks;
 734                 int16           triggers;
 735                 char            relkind;
 736                 bool            hasindex;
 737                 bool            hasrules;
 738                 bool            hasoids;
 739                 Oid                     tablespace;
 740         }                       tableinfo;
 741         bool            show_modifiers = false;
 742         bool            retval;
 743
 744         retval = false;
 745
 746         /* This output looks confusing in expanded mode. */
 747         myopt.expanded = false;
 748
 749         initPQExpBuffer(&buf);
 750         initPQExpBuffer(&title);
 751         initPQExpBuffer(&tmpbuf);
 752
 753         /* Get general table info */
 754         printfPQExpBuffer(&buf,
 755            "SELECT relhasindex, relkind, relchecks, reltriggers, relhasrules, \n"
 756                                           "relhasoids %s \n"
 757                                           "FROM pg_catalog.pg_class WHERE oid = '%s'",
 758                                           pset.sversion >= 80000 ? ", reltablespace" : "",
 759                                           oid);
 760         res = PSQLexec(buf.data, false);
 761         if (!res)
 762                 goto error_return;
 763
 764         /* Did we get anything? */
 765         if (PQntuples(res) == 0)
 766         {
 767                 if (!pset.quiet)
 768                         fprintf(stderr, _("Did not find any relation with OID %s.\n"),
 769                                         oid);
 770                 goto error_return;
 771         }
 772
 773         /* FIXME: check for null pointers here? */
 774         tableinfo.checks = atoi(PQgetvalue(res, 0, 2));
 775         tableinfo.triggers = atoi(PQgetvalue(res, 0, 3));
 776         tableinfo.relkind = *(PQgetvalue(res, 0, 1));
 777         tableinfo.hasindex = strcmp(PQgetvalue(res, 0, 0), "t") == 0;
 778         tableinfo.hasrules = strcmp(PQgetvalue(res, 0, 4), "t") == 0;
 779         tableinfo.hasoids = strcmp(PQgetvalue(res, 0, 5), "t") == 0;
 780         tableinfo.tablespace = (pset.sversion >= 80000) ?
 781                 atooid(PQgetvalue(res, 0, 6)) : 0;
 782         PQclear(res);
 783
 784         headers[0] = _("Column");
 785         headers[1] = _("Type");
 786         cols = 2;
 787
 788         if (tableinfo.relkind == 'r' || tableinfo.relkind == 'v')
 789         {
 790                 show_modifiers = true;
 791                 cols++;
 792                 headers[cols - 1] = _("Modifiers");
 793         }
 794
 795         if (verbose)
 796         {
 797                 cols++;
 798                 headers[cols - 1] = _("Description");
 799         }
 800
 801         headers[cols] = NULL;
 802
 803         /* Get column info (index requires additional checks) */
 804         printfPQExpBuffer(&buf, "SELECT a.attname,");
 805         appendPQExpBuffer(&buf, "\n  pg_catalog.format_type(a.atttypid, a.atttypmod),"
 806                                           "\n  (SELECT substring(pg_catalog.pg_get_expr(d.adbin, d.adrelid) for 128)"
 807                                           "\n   FROM pg_catalog.pg_attrdef d"
 808                                           "\n   WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef),"
 809                                           "\n  a.attnotnull, a.attnum");
 810         if (verbose)
 811                 appendPQExpBuffer(&buf, ", pg_catalog.col_description(a.attrelid, a.attnum)");
 812         appendPQExpBuffer(&buf, "\nFROM pg_catalog.pg_attribute a");
 813         if (tableinfo.relkind == 'i')
 814                 appendPQExpBuffer(&buf, ", pg_catalog.pg_index i");
 815         appendPQExpBuffer(&buf, "\nWHERE a.attrelid = '%s' AND a.attnum > 0 AND NOT a.attisdropped", oid);
 816         if (tableinfo.relkind == 'i')
 817                 appendPQExpBuffer(&buf, " AND a.attrelid = i.indexrelid");
 818         appendPQExpBuffer(&buf, "\nORDER BY a.attnum");
 819
 820         res = PSQLexec(buf.data, false);
 821         if (!res)
 822                 goto error_return;
 823         numrows = PQntuples(res);
 824
 825         /* Check if table is a view */
 826         if (tableinfo.relkind == 'v')
 827         {
 828                 PGresult   *result;
 829
 830                 printfPQExpBuffer(&buf, "SELECT pg_catalog.pg_get_viewdef('%s'::pg_catalog.oid, true)", oid);
 831                 result = PSQLexec(buf.data, false);
 832                 if (!result)
 833                         goto error_return;
 834
 835                 if (PQntuples(result) > 0)
 836                         view_def = pg_strdup(PQgetvalue(result, 0, 0));
 837
 838                 PQclear(result);
 839         }
 840
 841         /* Generate table cells to be printed */
 842         /* note: initialize all cells[] to NULL in case of error exit */
 843         cells = pg_malloc_zero((numrows * cols + 1) * sizeof(*cells));
 844
 845         for (i = 0; i < numrows; i++)
 846         {
 847                 /* Name */
 848 #ifdef WIN32
 849                 cells[i * cols + 0] = mbvalidate(PQgetvalue(res, i, 0), myopt.encoding);
 850 #else
 851                 cells[i * cols + 0] = PQgetvalue(res, i, 0);    /* don't free this
 852                                                                                                                  * afterwards */
 853 #endif
 854
 855                 /* Type */
 856 #ifdef WIN32
 857                 cells[i * cols + 1] = mbvalidate(PQgetvalue(res, i, 1), myopt.encoding);
 858 #else
 859                 cells[i * cols + 1] = PQgetvalue(res, i, 1);    /* don't free this
 860                                                                                                                  * either */
 861 #endif
 862
 863                 /* Extra: not null and default */
 864                 if (show_modifiers)
 865                 {
 866                         resetPQExpBuffer(&tmpbuf);
 867                         if (strcmp(PQgetvalue(res, i, 3), "t") == 0)
 868                                 appendPQExpBufferStr(&tmpbuf, "not null");
 869
 870                         /* handle "default" here */
 871                         /* (note: above we cut off the 'default' string at 128) */
 872                         if (strlen(PQgetvalue(res, i, 2)) != 0)
 873                         {
 874                                 if (tmpbuf.len > 0)
 875                                         appendPQExpBufferStr(&tmpbuf, " ");
 876                                 appendPQExpBuffer(&tmpbuf, "default %s",
 877                                                                   PQgetvalue(res, i, 2));
 878                         }
 879
 880 #ifdef WIN32
 881                         cells[i * cols + 2] = pg_strdup(mbvalidate(tmpbuf.data, myopt.encoding));
 882 #else
 883                         cells[i * cols + 2] = pg_strdup(tmpbuf.data);
 884 #endif
 885                 }
 886
 887                 /* Description */
 888                 if (verbose)
 889 #ifdef WIN32
 890                         cells[i * cols + cols - 1] = mbvalidate(PQgetvalue(res, i, 5), myopt.encoding);
 891 #else
 892                         cells[i * cols + cols - 1] = PQgetvalue(res, i, 5);
 893 #endif
 894         }
 895
 896         /* Make title */
 897         switch (tableinfo.relkind)
 898         {
 899                 case 'r':
 900                         printfPQExpBuffer(&title, _("Table \"%s.%s\""),
 901                                                           schemaname, relationname);
 902                         break;
 903                 case 'v':
 904                         printfPQExpBuffer(&title, _("View \"%s.%s\""),
 905                                                           schemaname, relationname);
 906                         break;
 907                 case 'S':
 908                         printfPQExpBuffer(&title, _("Sequence \"%s.%s\""),
 909                                                           schemaname, relationname);
 910                         break;
 911                 case 'i':
 912                         printfPQExpBuffer(&title, _("Index \"%s.%s\""),
 913                                                           schemaname, relationname);
 914                         break;
 915                 case 's':
 916                         /* not used as of 8.2, but keep it for backwards compatibility */
 917                         printfPQExpBuffer(&title, _("Special relation \"%s.%s\""),
 918                                                           schemaname, relationname);
 919                         break;
 920                 case 't':
 921                         printfPQExpBuffer(&title, _("TOAST table \"%s.%s\""),
 922                                                           schemaname, relationname);
 923                         break;
 924                 case 'c':
 925                         printfPQExpBuffer(&title, _("Composite type \"%s.%s\""),
 926                                                           schemaname, relationname);
 927                         break;
 928                 default:
 929                         printfPQExpBuffer(&title, _("?%c? \"%s.%s\""),
 930                                                           tableinfo.relkind, schemaname, relationname);
 931                         break;
 932         }
 933
 934         /* Make footers */
 935         if (tableinfo.relkind == 'i')
 936         {
 937                 /* Footer information about an index */
 938                 PGresult   *result;
 939
 940                 printfPQExpBuffer(&buf,
 941                                                   "SELECT i.indisunique, i.indisprimary, i.indisclustered, i.indisvalid, a.amname, c2.relname,\n"
 942                                         "  pg_catalog.pg_get_expr(i.indpred, i.indrelid, true)\n"
 943                                                   "FROM pg_catalog.pg_index i, pg_catalog.pg_class c, pg_catalog.pg_class c2, pg_catalog.pg_am a\n"
 944                   "WHERE i.indexrelid = c.oid AND c.oid = '%s' AND c.relam = a.oid\n"
 945                                                   "AND i.indrelid = c2.oid",
 946                                                   oid);
 947
 948                 result = PSQLexec(buf.data, false);
 949                 if (!result)
 950                         goto error_return;
 951                 else if (PQntuples(result) != 1)
 952                 {
 953                         PQclear(result);
 954                         goto error_return;
 955                 }
 956                 else
 957                 {
 958                         char       *indisunique = PQgetvalue(result, 0, 0);
 959                         char       *indisprimary = PQgetvalue(result, 0, 1);
 960                         char       *indisclustered = PQgetvalue(result, 0, 2);
 961                         char       *indisvalid = PQgetvalue(result, 0, 3);
 962                         char       *indamname = PQgetvalue(result, 0, 4);
 963                         char       *indtable = PQgetvalue(result, 0, 5);
 964                         char       *indpred = PQgetvalue(result, 0, 6);
 965                         int                     count_footers = 0;
 966
 967                         if (strcmp(indisprimary, "t") == 0)
 968                                 printfPQExpBuffer(&tmpbuf, _("primary key, "));
 969                         else if (strcmp(indisunique, "t") == 0)
 970                                 printfPQExpBuffer(&tmpbuf, _("unique, "));
 971                         else
 972                                 resetPQExpBuffer(&tmpbuf);
 973                         appendPQExpBuffer(&tmpbuf, "%s, ", indamname);
 974
 975                         /* we assume here that index and table are in same schema */
 976                         appendPQExpBuffer(&tmpbuf, _("for table \"%s.%s\""),
 977                                                           schemaname, indtable);
 978
 979                         if (strlen(indpred))
 980                                 appendPQExpBuffer(&tmpbuf, _(", predicate (%s)"), indpred);
 981
 982                         if (strcmp(indisclustered, "t") == 0)
 983                                 appendPQExpBuffer(&tmpbuf, _(", clustered"));
 984
 985                         if (strcmp(indisvalid, "t") != 0)
 986                                 appendPQExpBuffer(&tmpbuf, _(", invalid"));
 987
 988                         footers = pg_malloc_zero(4 * sizeof(*footers));
 989                         footers[count_footers++] = pg_strdup(tmpbuf.data);
 990                         add_tablespace_footer(tableinfo.relkind, tableinfo.tablespace,
 991                                                                   footers, &count_footers, tmpbuf, true);
 992                         footers[count_footers] = NULL;
 993
 994                 }
 995
 996                 PQclear(result);
 997         }
 998         else if (view_def)
 999         {
1000                 PGresult   *result = NULL;
1001                 int                     rule_count = 0;
1002                 int                     count_footers = 0;
1003
1004                 /* count rules other than the view rule */
1005                 if (tableinfo.hasrules)
1006                 {
1007                         printfPQExpBuffer(&buf,
1008                                                           "SELECT r.rulename, trim(trailing ';' from pg_catalog.pg_get_ruledef(r.oid, true))\n"
1009                                                           "FROM pg_catalog.pg_rewrite r\n"
1010                         "WHERE r.ev_class = '%s' AND r.rulename != '_RETURN' ORDER BY 1",
1011                                                           oid);
1012                         result = PSQLexec(buf.data, false);
1013                         if (!result)
1014                                 goto error_return;
1015                         else
1016                                 rule_count = PQntuples(result);
1017                 }
1018
1019                 /* Footer information about a view */
1020                 footers = pg_malloc_zero((rule_count + 3) * sizeof(*footers));
1021                 footers[count_footers] = pg_malloc(64 + strlen(view_def));
1022                 snprintf(footers[count_footers], 64 + strlen(view_def),
1023                                  _("View definition:\n%s"), view_def);
1024                 count_footers++;
1025
1026                 /* print rules */
1027                 if (rule_count > 0)
1028                 {
1029                         printfPQExpBuffer(&buf, _("Rules:"));
1030                         footers[count_footers++] = pg_strdup(buf.data);
1031                         for (i = 0; i < rule_count; i++)
1032                         {
1033                                 const char *ruledef;
1034
1035                                 /* Everything after "CREATE RULE" is echoed verbatim */
1036                                 ruledef = PQgetvalue(result, i, 1);
1037                                 ruledef += 12;
1038
1039                                 printfPQExpBuffer(&buf, " %s", ruledef);
1040
1041                                 footers[count_footers++] = pg_strdup(buf.data);
1042                         }
1043                         PQclear(result);
1044                 }
1045
1046                 footers[count_footers] = NULL;
1047
1048         }
1049         else if (tableinfo.relkind == 'r')
1050         {
1051                 /* Footer information about a table */
1052                 PGresult   *result1 = NULL,
1053                                    *result2 = NULL,
1054                                    *result3 = NULL,
1055                                    *result4 = NULL,
1056                                    *result5 = NULL,
1057                                    *result6 = NULL,
1058                                    *result7 = NULL;
1059                 int                     check_count = 0,
1060                                         index_count = 0,
1061                                         foreignkey_count = 0,
1062                                         rule_count = 0,
1063                                         trigger_count = 0,
1064                                         disabled_trigger_count = 0,
1065                                         inherits_count = 0;
1066                 int                     count_footers = 0;
1067
1068                 /* count indexes */
1069                 if (tableinfo.hasindex)
1070                 {
1071                         printfPQExpBuffer(&buf,
1072                                                           "SELECT c2.relname, i.indisprimary, i.indisunique, i.indisclustered, i.indisvalid, "
1073                                                           "pg_catalog.pg_get_indexdef(i.indexrelid, 0, true), c2.reltablespace\n"
1074                                                           "FROM pg_catalog.pg_class c, pg_catalog.pg_class c2, pg_catalog.pg_index i\n"
1075                                                           "WHERE c.oid = '%s' AND c.oid = i.indrelid AND i.indexrelid = c2.oid\n"
1076                           "ORDER BY i.indisprimary DESC, i.indisunique DESC, c2.relname",
1077                                                           oid);
1078                         result1 = PSQLexec(buf.data, false);
1079                         if (!result1)
1080                                 goto error_return;
1081                         else
1082                                 index_count = PQntuples(result1);
1083                 }
1084
1085                 /* count table (and column) check constraints */
1086                 if (tableinfo.checks)
1087                 {
1088                         printfPQExpBuffer(&buf,
1089                                                           "SELECT r.conname, "
1090                                                           "pg_catalog.pg_get_constraintdef(r.oid, true)\n"
1091                                                           "FROM pg_catalog.pg_constraint r\n"
1092                                         "WHERE r.conrelid = '%s' AND r.contype = 'c' ORDER BY 1",
1093                                                           oid);
1094                         result2 = PSQLexec(buf.data, false);
1095                         if (!result2)
1096                         {
1097                                 PQclear(result1);
1098                                 goto error_return;
1099                         }
1100                         else
1101                                 check_count = PQntuples(result2);
1102                 }
1103
1104                 /* count rules */
1105                 if (tableinfo.hasrules)
1106                 {
1107                         printfPQExpBuffer(&buf,
1108                                                           "SELECT r.rulename, trim(trailing ';' from pg_catalog.pg_get_ruledef(r.oid, true))\n"
1109                                                           "FROM pg_catalog.pg_rewrite r\n"
1110                                                           "WHERE r.ev_class = '%s' ORDER BY 1",
1111                                                           oid);
1112                         result3 = PSQLexec(buf.data, false);
1113                         if (!result3)
1114                         {
1115                                 PQclear(result1);
1116                                 PQclear(result2);
1117                                 goto error_return;
1118                         }
1119                         else
1120                                 rule_count = PQntuples(result3);
1121                 }
1122
1123                 /* count triggers (but ignore foreign-key triggers) */
1124                 if (tableinfo.triggers)
1125                 {
1126                         printfPQExpBuffer(&buf,
1127                                          "SELECT t.tgname, pg_catalog.pg_get_triggerdef(t.oid)\n"
1128                                                           "FROM pg_catalog.pg_trigger t\n"
1129                                                           "WHERE t.tgrelid = '%s' "
1130                                                           "AND t.tgenabled "
1131                                                           "AND t.tgconstraint = 0\n"
1132                                                           "ORDER BY 1",
1133                                                           oid);
1134                         result4 = PSQLexec(buf.data, false);
1135                         if (!result4)
1136                         {
1137                                 PQclear(result1);
1138                                 PQclear(result2);
1139                                 PQclear(result3);
1140                                 goto error_return;
1141                         }
1142                         else
1143                                 trigger_count = PQntuples(result4);
1144
1145                         /* acquire disabled triggers as a separate list */
1146                         printfPQExpBuffer(&buf,
1147                                          "SELECT t.tgname, pg_catalog.pg_get_triggerdef(t.oid)\n"
1148                                                           "FROM pg_catalog.pg_trigger t\n"
1149                                                           "WHERE t.tgrelid = '%s' "
1150                                                           "AND NOT t.tgenabled "
1151                                                           "AND t.tgconstraint = 0\n"
1152                                                           "ORDER BY 1",
1153                                                           oid);
1154                         result7 = PSQLexec(buf.data, false);
1155                         if (!result7)
1156                         {
1157                                 PQclear(result1);
1158                                 PQclear(result2);
1159                                 PQclear(result3);
1160                                 PQclear(result4);
1161                                 goto error_return;
1162                         }
1163                         else
1164                                 disabled_trigger_count = PQntuples(result7);
1165                 }
1166
1167                 /* count foreign-key constraints (there are none if no triggers) */
1168                 if (tableinfo.triggers)
1169                 {
1170                         printfPQExpBuffer(&buf,
1171                                                           "SELECT conname,\n"
1172                                    "  pg_catalog.pg_get_constraintdef(oid, true) as condef\n"
1173                                                           "FROM pg_catalog.pg_constraint r\n"
1174                                         "WHERE r.conrelid = '%s' AND r.contype = 'f' ORDER BY 1",
1175                                                           oid);
1176                         result5 = PSQLexec(buf.data, false);
1177                         if (!result5)
1178                         {
1179                                 PQclear(result1);
1180                                 PQclear(result2);
1181                                 PQclear(result3);
1182                                 PQclear(result4);
1183                                 PQclear(result7);
1184                                 goto error_return;
1185                         }
1186                         else
1187                                 foreignkey_count = PQntuples(result5);
1188                 }
1189
1190                 /* count inherited tables */
1191                 printfPQExpBuffer(&buf, "SELECT c.relname FROM pg_catalog.pg_class c, pg_catalog.pg_inherits i WHERE c.oid=i.inhparent AND i.inhrelid = '%s' ORDER BY inhseqno ASC", oid);
1192
1193                 result6 = PSQLexec(buf.data, false);
1194                 if (!result6)
1195                 {
1196                         PQclear(result1);
1197                         PQclear(result2);
1198                         PQclear(result3);
1199                         PQclear(result4);
1200                         PQclear(result5);
1201                         PQclear(result7);
1202                         goto error_return;
1203                 }
1204                 else
1205                         inherits_count = PQntuples(result6);
1206
1207                 footers = pg_malloc_zero((index_count + check_count + rule_count + trigger_count + foreignkey_count + inherits_count + 7 + 1)
1208                                                                  * sizeof(*footers));
1209
1210                 /* print indexes */
1211                 if (index_count > 0)
1212                 {
1213                         printfPQExpBuffer(&buf, _("Indexes:"));
1214                         footers[count_footers++] = pg_strdup(buf.data);
1215                         for (i = 0; i < index_count; i++)
1216                         {
1217                                 const char *indexdef;
1218                                 const char *usingpos;
1219                                 PQExpBufferData tmpbuf;
1220
1221                                 /* Output index name */
1222                                 printfPQExpBuffer(&buf, _("    \"%s\""),
1223                                                                   PQgetvalue(result1, i, 0));
1224
1225                                 /* Label as primary key or unique (but not both) */
1226                                 appendPQExpBuffer(&buf,
1227                                                                   strcmp(PQgetvalue(result1, i, 1), "t") == 0
1228                                                                   ? " PRIMARY KEY," :
1229                                                                   (strcmp(PQgetvalue(result1, i, 2), "t") == 0
1230                                                                    ? " UNIQUE,"
1231                                                                    : ""));
1232                                 /* Everything after "USING" is echoed verbatim */
1233                                 indexdef = PQgetvalue(result1, i, 5);
1234                                 usingpos = strstr(indexdef, " USING ");
1235                                 if (usingpos)
1236                                         indexdef = usingpos + 7;
1237
1238                                 appendPQExpBuffer(&buf, " %s", indexdef);
1239
1240                                 if (strcmp(PQgetvalue(result1, i, 3), "t") == 0)
1241                                         appendPQExpBuffer(&buf, " CLUSTER");
1242
1243                                 if (strcmp(PQgetvalue(result1, i, 4), "t") != 0)
1244                                         appendPQExpBuffer(&buf, " INVALID");
1245
1246                                 /* Print tablespace of the index on the same line */
1247                                 count_footers += 1;
1248                                 initPQExpBuffer(&tmpbuf);
1249                                 if (add_tablespace_footer('i',
1250                                                                                   atooid(PQgetvalue(result1, i, 6)),
1251                                                                          footers, &count_footers, tmpbuf, false))
1252                                 {
1253                                         appendPQExpBuffer(&buf, ", ");
1254                                         appendPQExpBuffer(&buf, tmpbuf.data);
1255
1256                                         count_footers -= 2;
1257                                 }
1258                                 else
1259                                         count_footers -= 1;
1260                                 termPQExpBuffer(&tmpbuf);
1261
1262                                 footers[count_footers++] = pg_strdup(buf.data);
1263                         }
1264                 }
1265
1266                 /* print check constraints */
1267                 if (check_count > 0)
1268                 {
1269                         printfPQExpBuffer(&buf, _("Check constraints:"));
1270                         footers[count_footers++] = pg_strdup(buf.data);
1271                         for (i = 0; i < check_count; i++)
1272                         {
1273                                 printfPQExpBuffer(&buf, _("    \"%s\" %s"),
1274                                                                   PQgetvalue(result2, i, 0),
1275                                                                   PQgetvalue(result2, i, 1));
1276
1277                                 footers[count_footers++] = pg_strdup(buf.data);
1278                         }
1279                 }
1280
1281                 /* print foreign key constraints */
1282                 if (foreignkey_count > 0)
1283                 {
1284                         printfPQExpBuffer(&buf, _("Foreign-key constraints:"));
1285                         footers[count_footers++] = pg_strdup(buf.data);
1286                         for (i = 0; i < foreignkey_count; i++)
1287                         {
1288                                 printfPQExpBuffer(&buf, _("    \"%s\" %s"),
1289                                                                   PQgetvalue(result5, i, 0),
1290                                                                   PQgetvalue(result5, i, 1));
1291
1292                                 footers[count_footers++] = pg_strdup(buf.data);
1293                         }
1294                 }
1295
1296                 /* print rules */
1297                 if (rule_count > 0)
1298                 {
1299                         printfPQExpBuffer(&buf, _("Rules:"));
1300                         footers[count_footers++] = pg_strdup(buf.data);
1301                         for (i = 0; i < rule_count; i++)
1302                         {
1303                                 const char *ruledef;
1304
1305                                 /* Everything after "CREATE RULE" is echoed verbatim */
1306                                 ruledef = PQgetvalue(result3, i, 1);
1307                                 ruledef += 12;
1308
1309                                 printfPQExpBuffer(&buf, "    %s", ruledef);
1310
1311                                 footers[count_footers++] = pg_strdup(buf.data);
1312                         }
1313                 }
1314
1315                 /* print triggers */
1316                 if (trigger_count > 0)
1317                 {
1318                         printfPQExpBuffer(&buf, _("Triggers:"));
1319                         footers[count_footers++] = pg_strdup(buf.data);
1320                         for (i = 0; i < trigger_count; i++)
1321                         {
1322                                 const char *tgdef;
1323                                 const char *usingpos;
1324
1325                                 /* Everything after "TRIGGER" is echoed verbatim */
1326                                 tgdef = PQgetvalue(result4, i, 1);
1327                                 usingpos = strstr(tgdef, " TRIGGER ");
1328                                 if (usingpos)
1329                                         tgdef = usingpos + 9;
1330
1331                                 printfPQExpBuffer(&buf, "    %s", tgdef);
1332
1333                                 footers[count_footers++] = pg_strdup(buf.data);
1334                         }
1335                 }
1336
1337                 /* print disabled triggers */
1338                 if (disabled_trigger_count > 0)
1339                 {
1340                         printfPQExpBuffer(&buf, _("Disabled triggers:"));
1341                         footers[count_footers++] = pg_strdup(buf.data);
1342                         for (i = 0; i < disabled_trigger_count; i++)
1343                         {
1344                                 const char *tgdef;
1345                                 const char *usingpos;
1346
1347                                 /* Everything after "TRIGGER" is echoed verbatim */
1348                                 tgdef = PQgetvalue(result7, i, 1);
1349                                 usingpos = strstr(tgdef, " TRIGGER ");
1350                                 if (usingpos)
1351                                         tgdef = usingpos + 9;
1352
1353                                 printfPQExpBuffer(&buf, "    %s", tgdef);
1354
1355                                 footers[count_footers++] = pg_strdup(buf.data);
1356                         }
1357                 }
1358
1359                 /* print inherits */
1360                 for (i = 0; i < inherits_count; i++)
1361                 {
1362                         char       *s = _("Inherits");
1363
1364                         if (i == 0)
1365                                 printfPQExpBuffer(&buf, "%s: %s", s, PQgetvalue(result6, i, 0));
1366                         else
1367                                 printfPQExpBuffer(&buf, "%*s  %s", (int) strlen(s), "", PQgetvalue(result6, i, 0));
1368                         if (i < inherits_count - 1)
1369                                 appendPQExpBuffer(&buf, ",");
1370
1371                         footers[count_footers++] = pg_strdup(buf.data);
1372                 }
1373
1374                 if (verbose)
1375                 {
1376                         char       *s = _("Has OIDs");
1377
1378                         printfPQExpBuffer(&buf, "%s: %s", s,
1379                                                           (tableinfo.hasoids ? _("yes") : _("no")));
1380                         footers[count_footers++] = pg_strdup(buf.data);
1381                 }
1382
1383                 add_tablespace_footer(tableinfo.relkind, tableinfo.tablespace,
1384                                                           footers, &count_footers, buf, true);
1385                 /* end of list marker */
1386                 footers[count_footers] = NULL;
1387
1388                 PQclear(result1);
1389                 PQclear(result2);
1390                 PQclear(result3);
1391                 PQclear(result4);
1392                 PQclear(result5);
1393                 PQclear(result6);
1394                 PQclear(result7);
1395         }
1396
1397         printTable(title.data, headers,
1398                            (const char **) cells, (const char **) footers,
1399                            "llll", &myopt, pset.queryFout, pset.logfile);
1400
1401         retval = true;
1402
1403 error_return:
1404
1405         /* clean up */
1406         termPQExpBuffer(&buf);
1407         termPQExpBuffer(&title);
1408         termPQExpBuffer(&tmpbuf);
1409
1410         if (cells)
1411         {
1412                 for (i = 0; i < numrows; i++)
1413                 {
1414                         if (show_modifiers)
1415                                 free(cells[i * cols + 2]);
1416                 }
1417                 free(cells);
1418         }
1419
1420         if (footers)
1421         {
1422                 for (ptr = footers; *ptr; ptr++)
1423                         free(*ptr);
1424                 free(footers);
1425         }
1426
1427         if (view_def)
1428                 free(view_def);
1429
1430         if (res)
1431                 PQclear(res);
1432
1433         return retval;
1434 }
1435
1436
1437 /*
1438  * Return true if the relation uses non default tablespace;
1439  * otherwise return false
1440  */
1441 static bool
1442 add_tablespace_footer(char relkind, Oid tablespace, char **footers,
1443                                           int *count, PQExpBufferData buf, bool newline)
1444 {
1445         /* relkinds for which we support tablespaces */
1446         if (relkind == 'r' || relkind == 'i')
1447         {
1448                 /*
1449                  * We ignore the database default tablespace so that users not using
1450                  * tablespaces don't need to know about them.
1451                  */
1452                 if (tablespace != 0)
1453                 {
1454                         PGresult   *result1 = NULL;
1455
1456                         printfPQExpBuffer(&buf, "SELECT spcname FROM pg_tablespace \n"
1457                                                           "WHERE oid = '%u';", tablespace);
1458                         result1 = PSQLexec(buf.data, false);
1459                         if (!result1)
1460                                 return false;
1461                         /* Should always be the case, but.... */
1462                         if (PQntuples(result1) > 0)
1463                         {
1464                                 printfPQExpBuffer(&buf,
1465                                   newline ? _("Tablespace: \"%s\"") : _("tablespace \"%s\""),
1466                                                                   PQgetvalue(result1, 0, 0));
1467
1468                                 footers[(*count)++] = pg_strdup(buf.data);
1469                         }
1470                         PQclear(result1);
1471
1472                         return true;
1473                 }
1474         }
1475
1476         return false;
1477 }
1478
1479 /*
1480  * \du or \dg
1481  *
1482  * Describes roles.  Any schema portion of the pattern is ignored.
1483  */
1484 bool
1485 describeRoles(const char *pattern, bool verbose)
1486 {
1487         PQExpBufferData buf;
1488         PGresult   *res;
1489         printQueryOpt myopt = pset.popt;
1490
1491         initPQExpBuffer(&buf);
1492
1493         printfPQExpBuffer(&buf,
1494                                           "SELECT r.rolname AS \"%s\",\n"
1495                                 "  CASE WHEN r.rolsuper THEN '%s' ELSE '%s' END AS \"%s\",\n"
1496                    "  CASE WHEN r.rolcreaterole THEN '%s' ELSE '%s' END AS \"%s\",\n"
1497                          "  CASE WHEN r.rolcreatedb THEN '%s' ELSE '%s' END AS \"%s\",\n"
1498                 "  CASE WHEN r.rolconnlimit < 0 THEN CAST('%s' AS pg_catalog.text)\n"
1499                                           "       ELSE CAST(r.rolconnlimit AS pg_catalog.text)\n"
1500                                           "  END AS \"%s\", \n"
1501                                           "  ARRAY(SELECT b.rolname FROM pg_catalog.pg_auth_members m JOIN pg_catalog.pg_roles b ON (m.roleid = b.oid) WHERE m.member = r.oid) as \"%s\"",
1502                                           _("Role name"),
1503                                           _("yes"), _("no"), _("Superuser"),
1504                                           _("yes"), _("no"), _("Create role"),
1505                                           _("yes"), _("no"), _("Create DB"),
1506                                           _("no limit"), _("Connections"),
1507                                           _("Member of"));
1508
1509         if (verbose)
1510                 appendPQExpBuffer(&buf, "\n, pg_catalog.shobj_description(r.oid, 'pg_authid') AS \"%s\"",
1511                                                   _("Description"));
1512
1513         appendPQExpBuffer(&buf, "\nFROM pg_catalog.pg_roles r\n");
1514
1515         processSQLNamePattern(pset.db, &buf, pattern, false, false,
1516                                                   NULL, "r.rolname", NULL, NULL);
1517
1518         appendPQExpBuffer(&buf, "ORDER BY 1;");
1519
1520         res = PSQLexec(buf.data, false);
1521         termPQExpBuffer(&buf);
1522         if (!res)
1523                 return false;
1524
1525         myopt.nullPrint = NULL;
1526         myopt.title = _("List of roles");
1527
1528         printQuery(res, &myopt, pset.queryFout, pset.logfile);
1529
1530         PQclear(res);
1531         return true;
1532 }
1533
1534
1535 /*
1536  * listTables()
1537  *
1538  * handler for \d, \dt, etc.
1539  *
1540  * tabtypes is an array of characters, specifying what info is desired:
1541  * t - tables
1542  * i - indexes
1543  * v - views
1544  * s - sequences
1545  * S - system tables (pg_catalog)
1546  * (any order of the above is fine)
1547  */
1548 bool
1549 listTables(const char *tabtypes, const char *pattern, bool verbose)
1550 {
1551         bool            showTables = strchr(tabtypes, 't') != NULL;
1552         bool            showIndexes = strchr(tabtypes, 'i') != NULL;
1553         bool            showViews = strchr(tabtypes, 'v') != NULL;
1554         bool            showSeq = strchr(tabtypes, 's') != NULL;
1555         bool            showSystem = strchr(tabtypes, 'S') != NULL;
1556
1557         PQExpBufferData buf;
1558         PGresult   *res;
1559         printQueryOpt myopt = pset.popt;
1560
1561         if (!(showTables || showIndexes || showViews || showSeq))
1562                 showTables = showViews = showSeq = true;
1563
1564         initPQExpBuffer(&buf);
1565
1566         /*
1567          * Note: as of Pg 8.2, we no longer use relkind 's', but we keep it here
1568          * for backwards compatibility.
1569          */
1570         printfPQExpBuffer(&buf,
1571                                           "SELECT n.nspname as \"%s\",\n"
1572                                           "  c.relname as \"%s\",\n"
1573                                           "  CASE c.relkind WHEN 'r' THEN '%s' WHEN 'v' THEN '%s' WHEN 'i' THEN '%s' WHEN 'S' THEN '%s' WHEN 's' THEN '%s' END as \"%s\",\n"
1574                                           "  r.rolname as \"%s\"",
1575                                           _("Schema"), _("Name"),
1576                                           _("table"), _("view"), _("index"), _("sequence"),
1577                                           _("special"), _("Type"), _("Owner"));
1578
1579         if (showIndexes)
1580                 appendPQExpBuffer(&buf,
1581                                                   ",\n c2.relname as \"%s\"",
1582                                                   _("Table"));
1583
1584         if (verbose)
1585                 appendPQExpBuffer(&buf,
1586                           ",\n  pg_catalog.obj_description(c.oid, 'pg_class') as \"%s\"",
1587                                                   _("Description"));
1588
1589         appendPQExpBuffer(&buf,
1590                                           "\nFROM pg_catalog.pg_class c"
1591                                         "\n     JOIN pg_catalog.pg_roles r ON r.oid = c.relowner"
1592          "\n     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace");
1593         if (showIndexes)
1594                 appendPQExpBuffer(&buf,
1595                          "\n     LEFT JOIN pg_catalog.pg_index i ON i.indexrelid = c.oid"
1596                    "\n     LEFT JOIN pg_catalog.pg_class c2 ON i.indrelid = c2.oid");
1597
1598         appendPQExpBuffer(&buf, "\nWHERE c.relkind IN (");
1599         if (showTables)
1600                 appendPQExpBuffer(&buf, "'r',");
1601         if (showViews)
1602                 appendPQExpBuffer(&buf, "'v',");
1603         if (showIndexes)
1604                 appendPQExpBuffer(&buf, "'i',");
1605         if (showSeq)
1606                 appendPQExpBuffer(&buf, "'S',");
1607         if (showSystem && showTables)
1608                 appendPQExpBuffer(&buf, "'s',");
1609         appendPQExpBuffer(&buf, "''");          /* dummy */
1610         appendPQExpBuffer(&buf, ")\n");
1611
1612         /*
1613          * If showSystem is specified, show only system objects (those in
1614          * pg_catalog).  Otherwise, suppress system objects, including those in
1615          * pg_catalog and pg_toast.  (We don't want to hide temp tables though.)
1616          */
1617         if (showSystem)
1618                 appendPQExpBuffer(&buf, "      AND n.nspname = 'pg_catalog'\n");
1619         else
1620                 appendPQExpBuffer(&buf, "      AND n.nspname NOT IN ('pg_catalog', 'pg_toast')\n");
1621
1622         processSQLNamePattern(pset.db, &buf, pattern, true, false,
1623                                                   "n.nspname", "c.relname", NULL,
1624                                                   "pg_catalog.pg_table_is_visible(c.oid)");
1625
1626         appendPQExpBuffer(&buf, "ORDER BY 1,2;");
1627
1628         res = PSQLexec(buf.data, false);
1629         termPQExpBuffer(&buf);
1630         if (!res)
1631                 return false;
1632
1633         if (PQntuples(res) == 0 && !pset.quiet)
1634         {
1635                 if (pattern)
1636                         fprintf(pset.queryFout, _("No matching relations found.\n"));
1637                 else
1638                         fprintf(pset.queryFout, _("No relations found.\n"));
1639         }
1640         else
1641         {
1642                 myopt.nullPrint = NULL;
1643                 myopt.title = _("List of relations");
1644
1645                 printQuery(res, &myopt, pset.queryFout, pset.logfile);
1646         }
1647
1648         PQclear(res);
1649         return true;
1650 }
1651
1652
1653 /*
1654  * \dD
1655  *
1656  * Describes domains.
1657  */
1658 bool
1659 listDomains(const char *pattern)
1660 {
1661         PQExpBufferData buf;
1662         PGresult   *res;
1663         printQueryOpt myopt = pset.popt;
1664
1665         initPQExpBuffer(&buf);
1666
1667         printfPQExpBuffer(&buf,
1668                                           "SELECT n.nspname as \"%s\",\n"
1669                                           "       t.typname as \"%s\",\n"
1670          "       pg_catalog.format_type(t.typbasetype, t.typtypmod) as \"%s\",\n"
1671                                           "       CASE WHEN t.typnotnull AND t.typdefault IS NOT NULL THEN 'not null default '||t.typdefault\n"
1672         "            WHEN t.typnotnull AND t.typdefault IS NULL THEN 'not null'\n"
1673                                           "            WHEN NOT t.typnotnull AND t.typdefault IS NOT NULL THEN 'default '||t.typdefault\n"
1674                                           "            ELSE ''\n"
1675                                           "       END as \"%s\",\n"
1676                         "       pg_catalog.pg_get_constraintdef(r.oid, true) as \"%s\"\n"
1677                                           "FROM pg_catalog.pg_type t\n"
1678            "     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = t.typnamespace\n"
1679                   "     LEFT JOIN pg_catalog.pg_constraint r ON t.oid = r.contypid\n"
1680                                           "WHERE t.typtype = 'd'\n",
1681                                           _("Schema"),
1682                                           _("Name"),
1683                                           _("Type"),
1684                                           _("Modifier"),
1685                                           _("Check"));
1686
1687         processSQLNamePattern(pset.db, &buf, pattern, true, false,
1688                                                   "n.nspname", "t.typname", NULL,
1689                                                   "pg_catalog.pg_type_is_visible(t.oid)");
1690
1691         appendPQExpBuffer(&buf, "ORDER BY 1, 2;");
1692
1693         res = PSQLexec(buf.data, false);
1694         termPQExpBuffer(&buf);
1695         if (!res)
1696                 return false;
1697
1698         myopt.nullPrint = NULL;
1699         myopt.title = _("List of domains");
1700
1701         printQuery(res, &myopt, pset.queryFout, pset.logfile);
1702
1703         PQclear(res);
1704         return true;
1705 }
1706
1707 /*
1708  * \dc
1709  *
1710  * Describes conversions.
1711  */
1712 bool
1713 listConversions(const char *pattern)
1714 {
1715         PQExpBufferData buf;
1716         PGresult   *res;
1717         printQueryOpt myopt = pset.popt;
1718
1719         initPQExpBuffer(&buf);
1720
1721         printfPQExpBuffer(&buf,
1722                                           "SELECT n.nspname AS \"%s\",\n"
1723                                           "       c.conname AS \"%s\",\n"
1724            "       pg_catalog.pg_encoding_to_char(c.conforencoding) AS \"%s\",\n"
1725                 "       pg_catalog.pg_encoding_to_char(c.contoencoding) AS \"%s\",\n"
1726                                           "       CASE WHEN c.condefault THEN '%s'\n"
1727                                           "       ELSE '%s' END AS \"%s\"\n"
1728                            "FROM pg_catalog.pg_conversion c, pg_catalog.pg_namespace n\n"
1729                                           "WHERE n.oid = c.connamespace\n",
1730                                           _("Schema"),
1731                                           _("Name"),
1732                                           _("Source"),
1733                                           _("Destination"),
1734                                           _("yes"),
1735                                           _("no"),
1736                                           _("Default?"));
1737
1738         processSQLNamePattern(pset.db, &buf, pattern, true, false,
1739                                                   "n.nspname", "c.conname", NULL,
1740                                                   "pg_catalog.pg_conversion_is_visible(c.oid)");
1741
1742         appendPQExpBuffer(&buf, "ORDER BY 1, 2;");
1743
1744         res = PSQLexec(buf.data, false);
1745         termPQExpBuffer(&buf);
1746         if (!res)
1747                 return false;
1748
1749         myopt.nullPrint = NULL;
1750         myopt.title = _("List of conversions");
1751
1752         printQuery(res, &myopt, pset.queryFout, pset.logfile);
1753
1754         PQclear(res);
1755         return true;
1756 }
1757
1758 /*
1759  * \dC
1760  *
1761  * Describes casts.
1762  */
1763 bool
1764 listCasts(const char *pattern)
1765 {
1766         PQExpBufferData buf;
1767         PGresult   *res;
1768         printQueryOpt myopt = pset.popt;
1769
1770         initPQExpBuffer(&buf);
1771 /* NEED LEFT JOIN FOR BINARY CASTS */
1772         printfPQExpBuffer(&buf,
1773                            "SELECT pg_catalog.format_type(castsource, NULL) AS \"%s\",\n"
1774                            "       pg_catalog.format_type(casttarget, NULL) AS \"%s\",\n"
1775                                           "       CASE WHEN castfunc = 0 THEN '%s'\n"
1776                                           "            ELSE p.proname\n"
1777                                           "       END as \"%s\",\n"
1778                                           "       CASE WHEN c.castcontext = 'e' THEN '%s'\n"
1779                                           "            WHEN c.castcontext = 'a' THEN '%s'\n"
1780                                           "            ELSE '%s'\n"
1781                                           "       END as \"%s\"\n"
1782                                  "FROM pg_catalog.pg_cast c LEFT JOIN pg_catalog.pg_proc p\n"
1783                                           "     ON c.castfunc = p.oid\n"
1784                                           "ORDER BY 1, 2",
1785                                           _("Source type"),
1786                                           _("Target type"),
1787                                           _("(binary compatible)"),
1788                                           _("Function"),
1789                                           _("no"),
1790                                           _("in assignment"),
1791                                           _("yes"),
1792                                           _("Implicit?"));
1793
1794         res = PSQLexec(buf.data, false);
1795         termPQExpBuffer(&buf);
1796         if (!res)
1797                 return false;
1798
1799         myopt.nullPrint = NULL;
1800         myopt.title = _("List of casts");
1801
1802         printQuery(res, &myopt, pset.queryFout, pset.logfile);
1803
1804         PQclear(res);
1805         return true;
1806 }
1807
1808 /*
1809  * \dn
1810  *
1811  * Describes schemas (namespaces)
1812  */
1813 bool
1814 listSchemas(const char *pattern, bool verbose)
1815 {
1816         PQExpBufferData buf;
1817         PGresult   *res;
1818         printQueryOpt myopt = pset.popt;
1819
1820         initPQExpBuffer(&buf);
1821         printfPQExpBuffer(&buf,
1822                                           "SELECT n.nspname AS \"%s\",\n"
1823                                           "       r.rolname AS \"%s\"",
1824                                           _("Name"), _("Owner"));
1825
1826         if (verbose)
1827                 appendPQExpBuffer(&buf,
1828                                                   ",\n  n.nspacl as \"%s\","
1829                          "  pg_catalog.obj_description(n.oid, 'pg_namespace') as \"%s\"",
1830                                                   _("Access privileges"), _("Description"));
1831
1832         appendPQExpBuffer(&buf,
1833                           "\nFROM pg_catalog.pg_namespace n JOIN pg_catalog.pg_roles r\n"
1834                                           "       ON n.nspowner=r.oid\n"
1835                                           "WHERE        (n.nspname !~ '^pg_temp_' OR\n"
1836                    "             n.nspname = (pg_catalog.current_schemas(true))[1])\n");                /* temp schema is first */
1837
1838         processSQLNamePattern(pset.db, &buf, pattern, true, false,
1839                                                   NULL, "n.nspname", NULL,
1840                                                   NULL);
1841
1842         appendPQExpBuffer(&buf, "ORDER BY 1;");
1843
1844         res = PSQLexec(buf.data, false);
1845         termPQExpBuffer(&buf);
1846         if (!res)
1847                 return false;
1848
1849         myopt.nullPrint = NULL;
1850         myopt.title = _("List of schemas");
1851
1852         printQuery(res, &myopt, pset.queryFout, pset.logfile);
1853
1854         PQclear(res);
1855         return true;
1856 }