describe.c 50.9 KB
Newer Older
Peter Eisentraut's avatar
Peter Eisentraut committed
1 2 3
/*
 * psql - the PostgreSQL interactive terminal
 *
4
 * Copyright (c) 2000-2005, PostgreSQL Global Development Group
Peter Eisentraut's avatar
Peter Eisentraut committed
5
 *
6
 * $PostgreSQL: pgsql/src/bin/psql/describe.c,v 1.111 2005/01/01 05:43:08 momjian Exp $
Peter Eisentraut's avatar
Peter Eisentraut committed
7
 */
8
#include "postgres_fe.h"
9 10
#include "describe.h"

11
#include "libpq-fe.h"
12
#include "pqexpbuffer.h"
13 14 15 16 17 18

#include "common.h"
#include "settings.h"
#include "print.h"
#include "variables.h"

19 20
#include <ctype.h>

21 22 23 24 25 26 27 28 29 30
#ifdef WIN32
/*
 * mbvalidate() is used in function describeOneTableDetails() to make sure
 * all characters of the cells will be printed to the DOS console in a
 * correct way
 */
#include "mbprint.h"
#endif


31 32
#define _(x) gettext((x))

33
static bool describeOneTableDetails(const char *schemaname,
Bruce Momjian's avatar
Bruce Momjian committed
34 35 36
						const char *relationname,
						const char *oid,
						bool verbose);
37
static void processNamePattern(PQExpBuffer buf, const char *pattern,
Bruce Momjian's avatar
Bruce Momjian committed
38 39 40
				   bool have_where, bool force_escape,
				   const char *schemavar, const char *namevar,
				   const char *altnamevar, const char *visibilityrule);
41

Bruce Momjian's avatar
Bruce Momjian committed
42 43
static void add_tablespace_footer(char relkind, Oid tablespace,
					  char **footers, int *count, PQExpBufferData buf);
44

45 46 47 48
/*----------------
 * Handlers for various slash commands displaying some sort of list
 * of things in the database.
 *
49
 * If you add something here, try to format the query to look nice in -E output.
50 51 52 53 54
 *----------------
 */


/* \da
55
 * Takes an optional regexp to select particular aggregates
56 57
 */
bool
58
describeAggregates(const char *pattern, bool verbose)
59
{
60
	PQExpBufferData buf;
Bruce Momjian's avatar
Bruce Momjian committed
61
	PGresult   *res;
62
	printQueryOpt myopt = pset.popt;
Bruce Momjian's avatar
Bruce Momjian committed
63

64 65
	initPQExpBuffer(&buf);

Bruce Momjian's avatar
Bruce Momjian committed
66 67
	/*
	 * There are two kinds of aggregates: ones that work on particular
68
	 * types and ones that work on all (denoted by input type = "any")
Bruce Momjian's avatar
Bruce Momjian committed
69
	 */
70
	printfPQExpBuffer(&buf,
Bruce Momjian's avatar
Bruce Momjian committed
71 72 73 74 75 76 77
					  "SELECT n.nspname as \"%s\",\n"
					  "  p.proname AS \"%s\",\n"
					  "  CASE p.proargtypes[0]\n"
					"    WHEN 'pg_catalog.\"any\"'::pg_catalog.regtype\n"
					  "    THEN CAST('%s' AS pg_catalog.text)\n"
			  "    ELSE pg_catalog.format_type(p.proargtypes[0], NULL)\n"
					  "  END AS \"%s\",\n"
78
			 "  pg_catalog.obj_description(p.oid, 'pg_proc') as \"%s\"\n"
Bruce Momjian's avatar
Bruce Momjian committed
79 80 81 82 83
					  "FROM pg_catalog.pg_proc p\n"
	"     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace\n"
					  "WHERE p.proisagg\n",
					  _("Schema"), _("Name"), _("(all types)"),
					  _("Data type"), _("Description"));
84

85 86 87
	processNamePattern(&buf, pattern, true, false,
					   "n.nspname", "p.proname", NULL,
					   "pg_catalog.pg_function_is_visible(p.oid)");
Bruce Momjian's avatar
Bruce Momjian committed
88

89
	appendPQExpBuffer(&buf, "ORDER BY 1, 2, 3;");
Bruce Momjian's avatar
Bruce Momjian committed
90

91
	res = PSQLexec(buf.data, false);
92
	termPQExpBuffer(&buf);
Bruce Momjian's avatar
Bruce Momjian committed
93 94 95 96
	if (!res)
		return false;

	myopt.nullPrint = NULL;
97
	myopt.title = _("List of aggregate functions");
Bruce Momjian's avatar
Bruce Momjian committed
98

99
	printQuery(res, &myopt, pset.queryFout);
Bruce Momjian's avatar
Bruce Momjian committed
100 101 102

	PQclear(res);
	return true;
103 104
}

105 106 107 108
/* \db
 * Takes an optional regexp to select particular tablespaces
 */
bool
109
describeTablespaces(const char *pattern, bool verbose)
110 111 112 113 114
{
	PQExpBufferData buf;
	PGresult   *res;
	printQueryOpt myopt = pset.popt;

115
	if (pset.sversion < 80000)
Bruce Momjian's avatar
Bruce Momjian committed
116
	{
Peter Eisentraut's avatar
Peter Eisentraut committed
117
		fprintf(stderr, _("The server version (%d) does not support tablespaces.\n"),
Bruce Momjian's avatar
Bruce Momjian committed
118 119
				pset.sversion);
		return true;
120 121
	}

122 123 124 125
	initPQExpBuffer(&buf);

	printfPQExpBuffer(&buf,
					  "SELECT spcname AS \"%s\",\n"
Bruce Momjian's avatar
Bruce Momjian committed
126
					"  pg_catalog.pg_get_userbyid(spcowner) AS \"%s\",\n"
127
					  "  spclocation AS \"%s\"",
128 129
					  _("Name"), _("Owner"), _("Location"));

130 131
	if (verbose)
		appendPQExpBuffer(&buf,
Bruce Momjian's avatar
Bruce Momjian committed
132 133 134
						  ",\n  spcacl as \"%s\"",
						  _("Access privileges"));

135 136 137
	appendPQExpBuffer(&buf,
					  "\nFROM pg_catalog.pg_tablespace\n");

138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157
	processNamePattern(&buf, pattern, false, false,
					   NULL, "spcname", NULL,
					   NULL);

	appendPQExpBuffer(&buf, "ORDER BY 1;");

	res = PSQLexec(buf.data, false);
	termPQExpBuffer(&buf);
	if (!res)
		return false;

	myopt.nullPrint = NULL;
	myopt.title = _("List of tablespaces");

	printQuery(res, &myopt, pset.queryFout);

	PQclear(res);
	return true;
}

158 159

/* \df
160
 * Takes an optional regexp to select particular functions
161 162
 */
bool
163
describeFunctions(const char *pattern, bool verbose)
164
{
165
	PQExpBufferData buf;
Bruce Momjian's avatar
Bruce Momjian committed
166
	PGresult   *res;
167
	printQueryOpt myopt = pset.popt;
Bruce Momjian's avatar
Bruce Momjian committed
168

169 170 171
	initPQExpBuffer(&buf);

	printfPQExpBuffer(&buf,
172
					  "SELECT n.nspname as \"%s\",\n"
Bruce Momjian's avatar
Bruce Momjian committed
173
					  "  p.proname as \"%s\",\n"
174 175 176 177
					  "  CASE WHEN p.proretset THEN 'setof ' ELSE '' END ||\n"
					  "  pg_catalog.format_type(p.prorettype, NULL) as \"%s\",\n"
					  "  pg_catalog.oidvectortypes(p.proargtypes) as \"%s\"",
					  _("Schema"), _("Name"), _("Result data type"), 
Bruce Momjian's avatar
Bruce Momjian committed
178
					  _("Argument data types"));
179

180
	if (verbose)
181
		appendPQExpBuffer(&buf,
Bruce Momjian's avatar
Bruce Momjian committed
182 183 184 185 186 187
						  ",\n  u.usename as \"%s\",\n"
						  "  l.lanname as \"%s\",\n"
						  "  p.prosrc as \"%s\",\n"
			  "  pg_catalog.obj_description(p.oid, 'pg_proc') as \"%s\"",
						  _("Owner"), _("Language"),
						  _("Source code"), _("Description"));
188

189
	if (!verbose)
190
		appendPQExpBuffer(&buf,
Bruce Momjian's avatar
Bruce Momjian committed
191 192
						  "\nFROM pg_catalog.pg_proc p"
						  "\n     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace\n");
193
	else
194
		appendPQExpBuffer(&buf,
Bruce Momjian's avatar
Bruce Momjian committed
195 196 197 198
						  "\nFROM pg_catalog.pg_proc p"
						  "\n     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace"
		 "\n     LEFT JOIN pg_catalog.pg_language l ON l.oid = p.prolang"
						  "\n     LEFT JOIN pg_catalog.pg_user u ON u.usesysid = p.proowner\n");
199

200
	/*
Bruce Momjian's avatar
Bruce Momjian committed
201 202
	 * we skip in/out funcs by excluding functions that take or return
	 * cstring
203 204
	 */
	appendPQExpBuffer(&buf,
Bruce Momjian's avatar
Bruce Momjian committed
205
	   "WHERE p.prorettype <> 'pg_catalog.cstring'::pg_catalog.regtype\n"
206
					  "      AND p.proargtypes[0] <> 'pg_catalog.cstring'::pg_catalog.regtype\n"
207 208 209 210 211 212
					  "      AND NOT p.proisagg\n");

	processNamePattern(&buf, pattern, true, false,
					   "n.nspname", "p.proname", NULL,
					   "pg_catalog.pg_function_is_visible(p.oid)");

213
	appendPQExpBuffer(&buf, "ORDER BY 1, 2, 3, 4;");
Bruce Momjian's avatar
Bruce Momjian committed
214

215
	res = PSQLexec(buf.data, false);
216
	termPQExpBuffer(&buf);
Bruce Momjian's avatar
Bruce Momjian committed
217 218 219 220
	if (!res)
		return false;

	myopt.nullPrint = NULL;
221
	myopt.title = _("List of functions");
Bruce Momjian's avatar
Bruce Momjian committed
222

223
	printQuery(res, &myopt, pset.queryFout);
Bruce Momjian's avatar
Bruce Momjian committed
224 225 226

	PQclear(res);
	return true;
227 228 229 230 231
}



/*
232 233
 * \dT
 * describe types
234 235
 */
bool
236
describeTypes(const char *pattern, bool verbose)
237
{
238
	PQExpBufferData buf;
Bruce Momjian's avatar
Bruce Momjian committed
239
	PGresult   *res;
240
	printQueryOpt myopt = pset.popt;
Bruce Momjian's avatar
Bruce Momjian committed
241

242 243 244
	initPQExpBuffer(&buf);

	printfPQExpBuffer(&buf,
Bruce Momjian's avatar
Bruce Momjian committed
245 246 247
					  "SELECT n.nspname as \"%s\",\n"
					"  pg_catalog.format_type(t.oid, NULL) AS \"%s\",\n",
					  _("Schema"), _("Name"));
248
	if (verbose)
249
		appendPQExpBuffer(&buf,
Bruce Momjian's avatar
Bruce Momjian committed
250 251 252 253 254 255 256 257
						  "  t.typname AS \"%s\",\n"
						  "  CASE WHEN t.typrelid != 0\n"
						  "      THEN CAST('tuple' AS pg_catalog.text)\n"
						  "    WHEN t.typlen < 0\n"
						  "      THEN CAST('var' AS pg_catalog.text)\n"
						  "    ELSE CAST(t.typlen AS pg_catalog.text)\n"
						  "  END AS \"%s\",\n",
						  _("Internal name"), _("Size"));
258
	appendPQExpBuffer(&buf,
Bruce Momjian's avatar
Bruce Momjian committed
259 260
			"  pg_catalog.obj_description(t.oid, 'pg_type') as \"%s\"\n",
					  _("Description"));
261

262
	appendPQExpBuffer(&buf, "FROM pg_catalog.pg_type t\n"
Bruce Momjian's avatar
Bruce Momjian committed
263
					  "     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = t.typnamespace\n");
264

265
	/*
266
	 * do not include array types (start with underscore); do not include
Bruce Momjian's avatar
Bruce Momjian committed
267 268
	 * complex types (typrelid!=0) unless they are standalone composite
	 * types
269
	 */
Bruce Momjian's avatar
Bruce Momjian committed
270
	appendPQExpBuffer(&buf, "WHERE (t.typrelid = 0 ");
271
	appendPQExpBuffer(&buf, "OR (SELECT c.relkind = 'c' FROM pg_catalog.pg_class c "
Bruce Momjian's avatar
Bruce Momjian committed
272
					  "WHERE c.oid = t.typrelid)) ");
Bruce Momjian's avatar
Bruce Momjian committed
273
	appendPQExpBuffer(&buf, "AND t.typname !~ '^_'\n");
Bruce Momjian's avatar
Bruce Momjian committed
274

275 276 277 278 279
	/* Match name pattern against either internal or external name */
	processNamePattern(&buf, pattern, true, false,
					   "n.nspname", "t.typname",
					   "pg_catalog.format_type(t.oid, NULL)",
					   "pg_catalog.pg_type_is_visible(t.oid)");
Bruce Momjian's avatar
Bruce Momjian committed
280

281
	appendPQExpBuffer(&buf, "ORDER BY 1, 2;");
282

283
	res = PSQLexec(buf.data, false);
284
	termPQExpBuffer(&buf);
Bruce Momjian's avatar
Bruce Momjian committed
285 286 287 288
	if (!res)
		return false;

	myopt.nullPrint = NULL;
289
	myopt.title = _("List of data types");
Bruce Momjian's avatar
Bruce Momjian committed
290

291
	printQuery(res, &myopt, pset.queryFout);
Bruce Momjian's avatar
Bruce Momjian committed
292 293 294

	PQclear(res);
	return true;
295 296 297 298 299 300 301
}



/* \do
 */
bool
302
describeOperators(const char *pattern)
303
{
304
	PQExpBufferData buf;
Bruce Momjian's avatar
Bruce Momjian committed
305
	PGresult   *res;
306
	printQueryOpt myopt = pset.popt;
Bruce Momjian's avatar
Bruce Momjian committed
307

308 309 310
	initPQExpBuffer(&buf);

	printfPQExpBuffer(&buf,
Bruce Momjian's avatar
Bruce Momjian committed
311 312 313 314 315 316 317 318 319 320 321 322
					  "SELECT n.nspname as \"%s\",\n"
					  "  o.oprname AS \"%s\",\n"
					  "  CASE WHEN o.oprkind='l' THEN NULL ELSE pg_catalog.format_type(o.oprleft, NULL) END AS \"%s\",\n"
					  "  CASE WHEN o.oprkind='r' THEN NULL ELSE pg_catalog.format_type(o.oprright, NULL) END AS \"%s\",\n"
			   "  pg_catalog.format_type(o.oprresult, NULL) AS \"%s\",\n"
		 "  coalesce(pg_catalog.obj_description(o.oid, 'pg_operator'),\n"
					  "           pg_catalog.obj_description(o.oprcode, 'pg_proc')) AS \"%s\"\n"
					  "FROM pg_catalog.pg_operator o\n"
					  "     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = o.oprnamespace\n",
					  _("Schema"), _("Name"),
					  _("Left arg type"), _("Right arg type"),
					  _("Result type"), _("Description"));
323 324 325 326

	processNamePattern(&buf, pattern, false, true,
					   "n.nspname", "o.oprname", NULL,
					   "pg_catalog.pg_operator_is_visible(o.oid)");
Bruce Momjian's avatar
Bruce Momjian committed
327

328
	appendPQExpBuffer(&buf, "ORDER BY 1, 2, 3, 4;");
Bruce Momjian's avatar
Bruce Momjian committed
329

330
	res = PSQLexec(buf.data, false);
331
	termPQExpBuffer(&buf);
Bruce Momjian's avatar
Bruce Momjian committed
332 333 334 335
	if (!res)
		return false;

	myopt.nullPrint = NULL;
336
	myopt.title = _("List of operators");
Bruce Momjian's avatar
Bruce Momjian committed
337

338
	printQuery(res, &myopt, pset.queryFout);
Bruce Momjian's avatar
Bruce Momjian committed
339 340 341

	PQclear(res);
	return true;
342 343 344 345 346 347 348 349 350
}


/*
 * listAllDbs
 *
 * for \l, \list, and -l switch
 */
bool
351
listAllDbs(bool verbose)
352
{
Bruce Momjian's avatar
Bruce Momjian committed
353
	PGresult   *res;
354
	PQExpBufferData buf;
355
	printQueryOpt myopt = pset.popt;
356

357 358 359
	initPQExpBuffer(&buf);

	printfPQExpBuffer(&buf,
Bruce Momjian's avatar
Bruce Momjian committed
360 361 362
					  "SELECT d.datname as \"%s\",\n"
					  "       u.usename as \"%s\"",
					  _("Name"), _("Owner"));
363
	appendPQExpBuffer(&buf,
Bruce Momjian's avatar
Bruce Momjian committed
364 365
		",\n       pg_catalog.pg_encoding_to_char(d.encoding) as \"%s\"",
					  _("Encoding"));
366
	if (verbose)
367
		appendPQExpBuffer(&buf,
Bruce Momjian's avatar
Bruce Momjian committed
368 369
						  ",\n       pg_catalog.obj_description(d.oid, 'pg_database') as \"%s\"",
						  _("Description"));
370
	appendPQExpBuffer(&buf,
371
					  "\nFROM pg_catalog.pg_database d"
Bruce Momjian's avatar
Bruce Momjian committed
372 373
		  "\n  LEFT JOIN pg_catalog.pg_user u ON d.datdba = u.usesysid\n"
					  "ORDER BY 1;");
374

375
	res = PSQLexec(buf.data, false);
376
	termPQExpBuffer(&buf);
Bruce Momjian's avatar
Bruce Momjian committed
377 378
	if (!res)
		return false;
379

Bruce Momjian's avatar
Bruce Momjian committed
380
	myopt.nullPrint = NULL;
381
	myopt.title = _("List of databases");
382

383
	printQuery(res, &myopt, pset.queryFout);
384

Bruce Momjian's avatar
Bruce Momjian committed
385 386
	PQclear(res);
	return true;
387 388 389
}


390 391
/*
 * List Tables Grant/Revoke Permissions
392 393 394
 * \z (now also \dp -- perhaps more mnemonic)
 */
bool
395
permissionsList(const char *pattern)
396
{
397
	PQExpBufferData buf;
Bruce Momjian's avatar
Bruce Momjian committed
398
	PGresult   *res;
399
	printQueryOpt myopt = pset.popt;
Bruce Momjian's avatar
Bruce Momjian committed
400

401 402
	initPQExpBuffer(&buf);

403
	/*
Bruce Momjian's avatar
Bruce Momjian committed
404 405
	 * we ignore indexes and toast tables since they have no meaningful
	 * rights
406
	 */
407
	printfPQExpBuffer(&buf,
Bruce Momjian's avatar
Bruce Momjian committed
408 409
					  "SELECT n.nspname as \"%s\",\n"
					  "  c.relname as \"%s\",\n"
410
					  "  CASE c.relkind WHEN 'r' THEN '%s' WHEN 'v' THEN '%s' WHEN 'S' THEN '%s' END as \"%s\",\n"
Bruce Momjian's avatar
Bruce Momjian committed
411 412 413 414
					  "  c.relacl as \"%s\"\n"
					  "FROM pg_catalog.pg_class c\n"
	"     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace\n"
					  "WHERE c.relkind IN ('r', 'v', 'S')\n",
415
					  _("Schema"), _("Name"), _("table"), _("view"), _("sequence"), _("Type"), _("Access privileges"));
416 417 418

	/*
	 * Unless a schema pattern is specified, we suppress system and temp
Bruce Momjian's avatar
Bruce Momjian committed
419 420 421
	 * tables, since they normally aren't very interesting from a
	 * permissions point of view.  You can see 'em by explicit request
	 * though, eg with \z pg_catalog.*
422 423 424
	 */
	processNamePattern(&buf, pattern, true, false,
					   "n.nspname", "c.relname", NULL,
Bruce Momjian's avatar
Bruce Momjian committed
425
		"pg_catalog.pg_table_is_visible(c.oid) AND n.nspname !~ '^pg_'");
426 427

	appendPQExpBuffer(&buf, "ORDER BY 1, 2;");
428

429
	res = PSQLexec(buf.data, false);
Bruce Momjian's avatar
Bruce Momjian committed
430
	if (!res)
431 432
	{
		termPQExpBuffer(&buf);
Bruce Momjian's avatar
Bruce Momjian committed
433
		return false;
434
	}
Bruce Momjian's avatar
Bruce Momjian committed
435

436
	myopt.nullPrint = NULL;
437 438
	printfPQExpBuffer(&buf, _("Access privileges for database \"%s\""), PQdb(pset.db));
	myopt.title = buf.data;
439

440
	printQuery(res, &myopt, pset.queryFout);
Bruce Momjian's avatar
Bruce Momjian committed
441

442
	termPQExpBuffer(&buf);
Bruce Momjian's avatar
Bruce Momjian committed
443 444
	PQclear(res);
	return true;
445 446 447 448 449 450 451 452 453 454 455 456 457
}



/*
 * Get object comments
 *
 * \dd [foo]
 *
 * Note: This only lists things that actually have a description. For complete
 * lists of things, there are other \d? commands.
 */
bool
458
objectDescription(const char *pattern)
459
{
460
	PQExpBufferData buf;
Bruce Momjian's avatar
Bruce Momjian committed
461
	PGresult   *res;
462
	printQueryOpt myopt = pset.popt;
Bruce Momjian's avatar
Bruce Momjian committed
463

464 465
	initPQExpBuffer(&buf);

466
	appendPQExpBuffer(&buf,
Bruce Momjian's avatar
Bruce Momjian committed
467 468 469
					  "SELECT DISTINCT tt.nspname AS \"%s\", tt.name AS \"%s\", tt.object AS \"%s\", d.description AS \"%s\"\n"
					  "FROM (\n",
				  _("Schema"), _("Name"), _("Object"), _("Description"));
470

471
	/* Aggregate descriptions */
472
	appendPQExpBuffer(&buf,
Bruce Momjian's avatar
Bruce Momjian committed
473 474 475 476 477 478 479 480
					  "  SELECT p.oid as oid, p.tableoid as tableoid,\n"
					  "  n.nspname as nspname,\n"
					  "  CAST(p.proname AS pg_catalog.text) as name,"
					  "  CAST('%s' AS pg_catalog.text) as object\n"
					  "  FROM pg_catalog.pg_proc p\n"
					  "       LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace\n"
					  "  WHERE p.proisagg\n",
					  _("aggregate"));
481 482 483
	processNamePattern(&buf, pattern, true, false,
					   "n.nspname", "p.proname", NULL,
					   "pg_catalog.pg_function_is_visible(p.oid)");
484

485
	/* Function descriptions (except in/outs for datatypes) */
486
	appendPQExpBuffer(&buf,
Bruce Momjian's avatar
Bruce Momjian committed
487 488 489 490 491 492 493 494 495 496 497 498
					  "UNION ALL\n"
					  "  SELECT p.oid as oid, p.tableoid as tableoid,\n"
					  "  n.nspname as nspname,\n"
					  "  CAST(p.proname AS pg_catalog.text) as name,"
					  "  CAST('%s' AS pg_catalog.text) as object\n"
					  "  FROM pg_catalog.pg_proc p\n"
					  "       LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace\n"

	 "  WHERE p.prorettype <> 'pg_catalog.cstring'::pg_catalog.regtype\n"
					  "      AND p.proargtypes[0] <> 'pg_catalog.cstring'::pg_catalog.regtype\n"
					  "      AND NOT p.proisagg\n",
					  _("function"));
499 500 501
	processNamePattern(&buf, pattern, true, false,
					   "n.nspname", "p.proname", NULL,
					   "pg_catalog.pg_function_is_visible(p.oid)");
502

503
	/* Operator descriptions (only if operator has its own comment) */
504
	appendPQExpBuffer(&buf,
Bruce Momjian's avatar
Bruce Momjian committed
505 506 507 508 509 510 511 512
					  "UNION ALL\n"
					  "  SELECT o.oid as oid, o.tableoid as tableoid,\n"
					  "  n.nspname as nspname,\n"
					  "  CAST(o.oprname AS pg_catalog.text) as name,"
					  "  CAST('%s' AS pg_catalog.text) as object\n"
					  "  FROM pg_catalog.pg_operator o\n"
					  "       LEFT JOIN pg_catalog.pg_namespace n ON n.oid = o.oprnamespace\n",
					  _("operator"));
513 514 515
	processNamePattern(&buf, pattern, false, false,
					   "n.nspname", "o.oprname", NULL,
					   "pg_catalog.pg_operator_is_visible(o.oid)");
516

517
	/* Type description */
518
	appendPQExpBuffer(&buf,
Bruce Momjian's avatar
Bruce Momjian committed
519 520 521 522 523 524 525 526
					  "UNION ALL\n"
					  "  SELECT t.oid as oid, t.tableoid as tableoid,\n"
					  "  n.nspname as nspname,\n"
					  "  pg_catalog.format_type(t.oid, NULL) as name,"
					  "  CAST('%s' AS pg_catalog.text) as object\n"
					  "  FROM pg_catalog.pg_type t\n"
					  "       LEFT JOIN pg_catalog.pg_namespace n ON n.oid = t.typnamespace\n",
					  _("data type"));
527
	processNamePattern(&buf, pattern, false, false,
Bruce Momjian's avatar
Bruce Momjian committed
528
				"n.nspname", "pg_catalog.format_type(t.oid, NULL)", NULL,
529
					   "pg_catalog.pg_type_is_visible(t.oid)");
530

531
	/* Relation (tables, views, indexes, sequences) descriptions */
532
	appendPQExpBuffer(&buf,
Bruce Momjian's avatar
Bruce Momjian committed
533 534 535 536 537 538 539 540 541 542 543
					  "UNION ALL\n"
					  "  SELECT c.oid as oid, c.tableoid as tableoid,\n"
					  "  n.nspname as nspname,\n"
					  "  CAST(c.relname AS pg_catalog.text) as name,\n"
					  "  CAST(\n"
					  "    CASE c.relkind WHEN 'r' THEN '%s' WHEN 'v' THEN '%s' WHEN 'i' THEN '%s' WHEN 'S' THEN '%s' END"
					  "  AS pg_catalog.text) as object\n"
					  "  FROM pg_catalog.pg_class c\n"
					  "       LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace\n"
					  "  WHERE c.relkind IN ('r', 'v', 'i', 'S')\n",
					  _("table"), _("view"), _("index"), _("sequence"));
544 545 546
	processNamePattern(&buf, pattern, true, false,
					   "n.nspname", "c.relname", NULL,
					   "pg_catalog.pg_table_is_visible(c.oid)");
547

548
	/* Rule description (ignore rules for views) */
549
	appendPQExpBuffer(&buf,
Bruce Momjian's avatar
Bruce Momjian committed
550 551 552 553 554 555 556 557 558 559
					  "UNION ALL\n"
					  "  SELECT r.oid as oid, r.tableoid as tableoid,\n"
					  "  n.nspname as nspname,\n"
					  "  CAST(r.rulename AS pg_catalog.text) as name,"
					  "  CAST('%s' AS pg_catalog.text) as object\n"
					  "  FROM pg_catalog.pg_rewrite r\n"
			  "       JOIN pg_catalog.pg_class c ON c.oid = r.ev_class\n"
					  "       LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace\n"
					  "  WHERE r.rulename != '_RETURN'\n",
					  _("rule"));
560 561 562 563
	/* XXX not sure what to do about visibility rule here? */
	processNamePattern(&buf, pattern, true, false,
					   "n.nspname", "r.rulename", NULL,
					   "pg_catalog.pg_table_is_visible(c.oid)");
564

565
	/* Trigger description */
566
	appendPQExpBuffer(&buf,
Bruce Momjian's avatar
Bruce Momjian committed
567 568 569 570 571 572 573 574 575
					  "UNION ALL\n"
					  "  SELECT t.oid as oid, t.tableoid as tableoid,\n"
					  "  n.nspname as nspname,\n"
					  "  CAST(t.tgname AS pg_catalog.text) as name,"
					  "  CAST('%s' AS pg_catalog.text) as object\n"
					  "  FROM pg_catalog.pg_trigger t\n"
			   "       JOIN pg_catalog.pg_class c ON c.oid = t.tgrelid\n"
					  "       LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace\n",
					  _("trigger"));
576 577 578 579 580 581
	/* XXX not sure what to do about visibility rule here? */
	processNamePattern(&buf, pattern, false, false,
					   "n.nspname", "t.tgname", NULL,
					   "pg_catalog.pg_table_is_visible(c.oid)");

	appendPQExpBuffer(&buf,
Bruce Momjian's avatar
Bruce Momjian committed
582
					  ") AS tt\n"
583
					  "  JOIN pg_catalog.pg_description d ON (tt.oid = d.objoid AND tt.tableoid = d.classoid AND d.objsubid = 0)\n");
584 585

	appendPQExpBuffer(&buf, "ORDER BY 1, 2, 3;");
Bruce Momjian's avatar
Bruce Momjian committed
586

587
	res = PSQLexec(buf.data, false);
588
	termPQExpBuffer(&buf);
Bruce Momjian's avatar
Bruce Momjian committed
589 590 591 592
	if (!res)
		return false;

	myopt.nullPrint = NULL;
593
	myopt.title = _("Object descriptions");
Bruce Momjian's avatar
Bruce Momjian committed
594

595
	printQuery(res, &myopt, pset.queryFout);
Bruce Momjian's avatar
Bruce Momjian committed
596 597 598

	PQclear(res);
	return true;
599 600 601 602 603 604 605
}



/*
 * describeTableDetails (for \d)
 *
606 607
 * This routine finds the tables to be displayed, and calls
 * describeOneTableDetails for each one.
608 609
 *
 * verbose: if true, this is \d+
610
 */
611 612
bool
describeTableDetails(const char *pattern, bool verbose)
613
{
614 615 616
	PQExpBufferData buf;
	PGresult   *res;
	int			i;
Bruce Momjian's avatar
Bruce Momjian committed
617

618 619 620
	initPQExpBuffer(&buf);

	printfPQExpBuffer(&buf,
Bruce Momjian's avatar
Bruce Momjian committed
621 622 623 624 625
					  "SELECT c.oid,\n"
					  "  n.nspname,\n"
					  "  c.relname\n"
					  "FROM pg_catalog.pg_class c\n"
					  "     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace\n");
626 627 628 629 630 631 632

	processNamePattern(&buf, pattern, false, false,
					   "n.nspname", "c.relname", NULL,
					   "pg_catalog.pg_table_is_visible(c.oid)");

	appendPQExpBuffer(&buf, "ORDER BY 2, 3;");

633
	res = PSQLexec(buf.data, false);
634 635 636 637 638
	termPQExpBuffer(&buf);
	if (!res)
		return false;

	if (PQntuples(res) == 0)
Bruce Momjian's avatar
Bruce Momjian committed
639
	{
640 641 642 643 644
		if (!QUIET())
			fprintf(stderr, _("Did not find any relation named \"%s\".\n"),
					pattern);
		PQclear(res);
		return false;
Bruce Momjian's avatar
Bruce Momjian committed
645
	}
646

647 648 649 650 651 652 653 654 655
	for (i = 0; i < PQntuples(res); i++)
	{
		const char *oid;
		const char *nspname;
		const char *relname;

		oid = PQgetvalue(res, i, 0);
		nspname = PQgetvalue(res, i, 1);
		relname = PQgetvalue(res, i, 2);
656

657 658 659 660 661 662 663 664 665 666 667 668 669 670 671 672 673 674 675 676 677 678 679
		if (!describeOneTableDetails(nspname, relname, oid, verbose))
		{
			PQclear(res);
			return false;
		}
	}

	PQclear(res);
	return true;
}

/*
 * describeOneTableDetails (for \d)
 *
 * Unfortunately, the information presented here is so complicated that it
 * cannot be done in a single query. So we have to assemble the printed table
 * by hand and pass it to the underlying printTable() function.
 */
static bool
describeOneTableDetails(const char *schemaname,
						const char *relationname,
						const char *oid,
						bool verbose)
680
{
681
	PQExpBufferData buf;
682
	PGresult   *res = NULL;
683
	printTableOpt myopt = pset.popt.topt;
Bruce Momjian's avatar
Bruce Momjian committed
684
	int			i;
Bruce Momjian's avatar
Bruce Momjian committed
685
	char	   *view_def = NULL;
Bruce Momjian's avatar
Bruce Momjian committed
686
	const char *headers[5];
Bruce Momjian's avatar
Bruce Momjian committed
687 688 689
	char	  **cells = NULL;
	char	  **footers = NULL;
	char	  **ptr;
690
	PQExpBufferData title;
691 692 693
	PQExpBufferData tmpbuf;
	int			cols = 0;
	int			numrows = 0;
694 695 696 697
	struct
	{
		int16		checks;
		int16		triggers;
698 699
		char		relkind;
		bool		hasindex;
700
		bool		hasrules;
Bruce Momjian's avatar
Bruce Momjian committed
701
		bool		hasoids;
702
		Oid			tablespace;
703
	}			tableinfo;
704
	bool		show_modifiers = false;
705
	bool		retval;
706

707
	retval = false;
Bruce Momjian's avatar
Bruce Momjian committed
708

709 710
	initPQExpBuffer(&buf);
	initPQExpBuffer(&title);
711
	initPQExpBuffer(&tmpbuf);
712

Bruce Momjian's avatar
Bruce Momjian committed
713
	/* Get general table info */
714
	printfPQExpBuffer(&buf,
Bruce Momjian's avatar
Bruce Momjian committed
715 716
	"SELECT relhasindex, relkind, relchecks, reltriggers, relhasrules, \n"
					  "relhasoids %s \n"
717
					  "FROM pg_catalog.pg_class WHERE oid = '%s'",
718
					  pset.sversion >= 80000 ? ", reltablespace" : "",
719
					  oid);
720
	res = PSQLexec(buf.data, false);
721
	if (!res)
722
		goto error_return;
Bruce Momjian's avatar
Bruce Momjian committed
723 724 725 726

	/* Did we get anything? */
	if (PQntuples(res) == 0)
	{
727
		if (!QUIET())
728
			fprintf(stderr, _("Did not find any relation with OID %s.\n"),
729
					oid);
730
		goto error_return;
Bruce Momjian's avatar
Bruce Momjian committed
731 732
	}

733 734 735
	/* FIXME: check for null pointers here? */
	tableinfo.checks = atoi(PQgetvalue(res, 0, 2));
	tableinfo.triggers = atoi(PQgetvalue(res, 0, 3));
736 737
	tableinfo.relkind = *(PQgetvalue(res, 0, 1));
	tableinfo.hasindex = strcmp(PQgetvalue(res, 0, 0), "t") == 0;
738
	tableinfo.hasrules = strcmp(PQgetvalue(res, 0, 4), "t") == 0;
739
	tableinfo.hasoids = strcmp(PQgetvalue(res, 0, 5), "t") == 0;
740
	tableinfo.tablespace = (pset.sversion >= 80000) ?
Bruce Momjian's avatar
Bruce Momjian committed
741
		atooid(PQgetvalue(res, 0, 6)) : 0;
742
	PQclear(res);
Bruce Momjian's avatar
Bruce Momjian committed
743

744 745
	headers[0] = _("Column");
	headers[1] = _("Type");
746
	cols = 2;
Bruce Momjian's avatar
Bruce Momjian committed
747

748
	if (tableinfo.relkind == 'r' || tableinfo.relkind == 'v')
749
	{
750
		show_modifiers = true;
751
		cols++;
752
		headers[cols - 1] = _("Modifiers");
753
	}
Bruce Momjian's avatar
Bruce Momjian committed
754

755
	if (verbose)
Bruce Momjian's avatar
Bruce Momjian committed
756
	{
757
		cols++;
758
		headers[cols - 1] = _("Description");
Bruce Momjian's avatar
Bruce Momjian committed
759 760
	}

761
	headers[cols] = NULL;
762

763
	/* Get column info (index requires additional checks) */
764
	printfPQExpBuffer(&buf, "SELECT a.attname,");
765
	appendPQExpBuffer(&buf, "\n  pg_catalog.format_type(a.atttypid, a.atttypmod),"
Bruce Momjian's avatar
Bruce Momjian committed
766
	"\n  (SELECT substring(d.adsrc for 128) FROM pg_catalog.pg_attrdef d"
767 768
					  "\n   WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef),"
					  "\n  a.attnotnull, a.attnum");
769 770 771
	if (verbose)
		appendPQExpBuffer(&buf, ", pg_catalog.col_description(a.attrelid, a.attnum)");
	appendPQExpBuffer(&buf, "\nFROM pg_catalog.pg_attribute a");
772
	if (tableinfo.relkind == 'i')
773 774
		appendPQExpBuffer(&buf, ", pg_catalog.pg_index i");
	appendPQExpBuffer(&buf, "\nWHERE a.attrelid = '%s' AND a.attnum > 0 AND NOT a.attisdropped", oid);
775
	if (tableinfo.relkind == 'i')
776 777
		appendPQExpBuffer(&buf, " AND a.attrelid = i.indexrelid");
	appendPQExpBuffer(&buf, "\nORDER BY a.attnum");
778

779
	res = PSQLexec(buf.data, false);
780
	if (!res)
781
		goto error_return;
782
	numrows = PQntuples(res);
783 784

	/* Check if table is a view */
785
	if (tableinfo.relkind == 'v')
786 787 788
	{
		PGresult   *result;

789
		printfPQExpBuffer(&buf, "SELECT pg_catalog.pg_get_viewdef('%s'::pg_catalog.oid, true)", oid);
790
		result = PSQLexec(buf.data, false);
791
		if (!result)
792
			goto error_return;
793 794

		if (PQntuples(result) > 0)
795
			view_def = pg_strdup(PQgetvalue(result, 0, 0));
796

797 798
		PQclear(result);
	}
799 800

	/* Generate table cells to be printed */
801
	/* note: initialize all cells[] to NULL in case of error exit */
802
	cells = pg_malloc_zero((numrows * cols + 1) * sizeof(*cells));
803

804
	for (i = 0; i < numrows; i++)
Bruce Momjian's avatar
Bruce Momjian committed
805 806
	{
		/* Name */
807
#ifdef WIN32
Bruce Momjian's avatar
Bruce Momjian committed
808
		cells[i * cols + 0] = mbvalidate(PQgetvalue(res, i, 0), myopt.encoding);
809
#else
810 811
		cells[i * cols + 0] = PQgetvalue(res, i, 0);	/* don't free this
														 * afterwards */
812 813
#endif

Bruce Momjian's avatar
Bruce Momjian committed
814
		/* Type */
815
#ifdef WIN32
Bruce Momjian's avatar
Bruce Momjian committed
816
		cells[i * cols + 1] = mbvalidate(PQgetvalue(res, i, 1), myopt.encoding);
817
#else
818
		cells[i * cols + 1] = PQgetvalue(res, i, 1);	/* don't free this
819
														 * either */
820
#endif
821 822

		/* Extra: not null and default */
823
		if (show_modifiers)
824
		{
825
			resetPQExpBuffer(&tmpbuf);
826
			if (strcmp(PQgetvalue(res, i, 3), "t") == 0)
827
				appendPQExpBufferStr(&tmpbuf, "not null");
828 829

			/* handle "default" here */
830 831
			/* (note: above we cut off the 'default' string at 128) */
			if (strlen(PQgetvalue(res, i, 2)) != 0)
832
			{
833 834 835
				if (tmpbuf.len > 0)
					appendPQExpBufferStr(&tmpbuf, " ");
				appendPQExpBuffer(&tmpbuf, "default %s",
836
								  PQgetvalue(res, i, 2));
837
			}
838

839
#ifdef WIN32
840
			cells[i * cols + 2] = pg_strdup(mbvalidate(tmpbuf.data, myopt.encoding));
841
#else
842
			cells[i * cols + 2] = pg_strdup(tmpbuf.data);
843
#endif
844 845
		}

Bruce Momjian's avatar
Bruce Momjian committed
846
		/* Description */
847
		if (verbose)
848
#ifdef WIN32
Bruce Momjian's avatar
Bruce Momjian committed
849
			cells[i * cols + cols - 1] = mbvalidate(PQgetvalue(res, i, 5), myopt.encoding);
850
#else
851
			cells[i * cols + cols - 1] = PQgetvalue(res, i, 5);
852
#endif
Bruce Momjian's avatar
Bruce Momjian committed
853
	}
854

Bruce Momjian's avatar
Bruce Momjian committed
855
	/* Make title */
856 857 858
	switch (tableinfo.relkind)
	{
		case 'r':
859 860
			printfPQExpBuffer(&title, _("Table \"%s.%s\""),
							  schemaname, relationname);
861 862
			break;
		case 'v':
863 864
			printfPQExpBuffer(&title, _("View \"%s.%s\""),
							  schemaname, relationname);
865 866
			break;
		case 'S':
867 868
			printfPQExpBuffer(&title, _("Sequence \"%s.%s\""),
							  schemaname, relationname);
869 870
			break;
		case 'i':
871 872
			printfPQExpBuffer(&title, _("Index \"%s.%s\""),
							  schemaname, relationname);
873 874
			break;
		case 's':
875 876
			printfPQExpBuffer(&title, _("Special relation \"%s.%s\""),
							  schemaname, relationname);
877
			break;
878
		case 't':
879 880
			printfPQExpBuffer(&title, _("TOAST table \"%s.%s\""),
							  schemaname, relationname);
881
			break;
882 883 884 885
		case 'c':
			printfPQExpBuffer(&title, _("Composite type \"%s.%s\""),
							  schemaname, relationname);
			break;
886
		default:
887
			printfPQExpBuffer(&title, _("?%c? \"%s.%s\""),
Bruce Momjian's avatar
Bruce Momjian committed
888
							tableinfo.relkind, schemaname, relationname);
889
			break;
890
	}
Bruce Momjian's avatar
Bruce Momjian committed
891 892

	/* Make footers */
893 894
	if (tableinfo.relkind == 'i')
	{
895
		/* Footer information about an index */
896
		PGresult   *result;
Bruce Momjian's avatar
Bruce Momjian committed
897

898
		printfPQExpBuffer(&buf,
Bruce Momjian's avatar
Bruce Momjian committed
899 900
						  "SELECT i.indisunique, i.indisprimary, i.indisclustered, a.amname, c2.relname,\n"
				"  pg_catalog.pg_get_expr(i.indpred, i.indrelid, true)\n"
901 902
						  "FROM pg_catalog.pg_index i, pg_catalog.pg_class c, pg_catalog.pg_class c2, pg_catalog.pg_am a\n"
						  "WHERE i.indexrelid = c.oid AND c.oid = '%s' AND c.relam = a.oid\n"
903
						  "AND i.indrelid = c2.oid",
904
						  oid);
905

906
		result = PSQLexec(buf.data, false);
907 908 909 910 911 912 913
		if (!result)
			goto error_return;
		else if (PQntuples(result) != 1)
		{
			PQclear(result);
			goto error_return;
		}
914 915
		else
		{
916 917
			char	   *indisunique = PQgetvalue(result, 0, 0);
			char	   *indisprimary = PQgetvalue(result, 0, 1);
918 919 920 921
			char	   *indisclustered = PQgetvalue(result, 0, 2);
			char	   *indamname = PQgetvalue(result, 0, 3);
			char	   *indtable = PQgetvalue(result, 0, 4);
			char	   *indpred = PQgetvalue(result, 0, 5);
922
			int			count_footers = 0;
923

Peter Eisentraut's avatar
Peter Eisentraut committed
924
			if (strcmp(indisprimary, "t") == 0)
Peter Eisentraut's avatar
Peter Eisentraut committed
925
				printfPQExpBuffer(&tmpbuf, _("primary key, "));
Peter Eisentraut's avatar
Peter Eisentraut committed
926
			else if (strcmp(indisunique, "t") == 0)
Peter Eisentraut's avatar
Peter Eisentraut committed
927
				printfPQExpBuffer(&tmpbuf, _("unique, "));
Peter Eisentraut's avatar
Peter Eisentraut committed
928 929 930 931
			else
				resetPQExpBuffer(&tmpbuf);
			appendPQExpBuffer(&tmpbuf, "%s, ", indamname);

932 933 934 935
			/* we assume here that index and table are in same schema */
			appendPQExpBuffer(&tmpbuf, _("for table \"%s.%s\""),
							  schemaname, indtable);

Peter Eisentraut's avatar
Peter Eisentraut committed
936
			if (strlen(indpred))
937
				appendPQExpBuffer(&tmpbuf, _(", predicate (%s)"), indpred);
Peter Eisentraut's avatar
Peter Eisentraut committed
938

939
			if (strcmp(indisclustered, "t") == 0)
Peter Eisentraut's avatar
Peter Eisentraut committed
940
				appendPQExpBuffer(&tmpbuf, _(", clustered"));
941

942 943
			footers = pg_malloc_zero(4 * sizeof(*footers));
			footers[count_footers++] = pg_strdup(tmpbuf.data);
944
			add_tablespace_footer(tableinfo.relkind, tableinfo.tablespace,
Bruce Momjian's avatar
Bruce Momjian committed
945
								  footers, &count_footers, tmpbuf);
946 947
			footers[count_footers] = NULL;

948
		}
949 950

		PQclear(result);
951
	}
952
	else if (view_def)
Bruce Momjian's avatar
Bruce Momjian committed
953
	{
954 955 956 957
		PGresult   *result = NULL;
		int			rule_count = 0;
		int			count_footers = 0;

958
		/* count rules other than the view rule */
959
		if (tableinfo.hasrules)
960
		{
961
			printfPQExpBuffer(&buf,
962
							  "SELECT r.rulename, trim(trailing ';' from pg_catalog.pg_get_ruledef(r.oid, true))\n"
Bruce Momjian's avatar
Bruce Momjian committed
963 964 965
							  "FROM pg_catalog.pg_rewrite r\n"
				   "WHERE r.ev_class = '%s' AND r.rulename != '_RETURN'",
							  oid);
966
			result = PSQLexec(buf.data, false);
967
			if (!result)
968
				goto error_return;
969 970 971 972
			else
				rule_count = PQntuples(result);
		}

973
		/* Footer information about a view */
974 975
		footers = pg_malloc_zero((rule_count + 3) * sizeof(*footers));
		footers[count_footers] = pg_malloc(64 + strlen(view_def));
976
		snprintf(footers[count_footers], 64 + strlen(view_def),
977
				 _("View definition:\n%s"), view_def);
978 979 980
		count_footers++;

		/* print rules */
981
		if (rule_count > 0)
982
		{
983
			printfPQExpBuffer(&buf, _("Rules:"));
984
			footers[count_footers++] = pg_strdup(buf.data);
985 986 987
			for (i = 0; i < rule_count; i++)
			{
				const char *ruledef;
988

989 990 991
				/* Everything after "CREATE RULE" is echoed verbatim */
				ruledef = PQgetvalue(result, i, 1);
				ruledef += 12;
992

993 994
				printfPQExpBuffer(&buf, " %s", ruledef);

995
				footers[count_footers++] = pg_strdup(buf.data);
996 997
			}
			PQclear(result);
998 999 1000 1001
		}

		footers[count_footers] = NULL;

Bruce Momjian's avatar
Bruce Momjian committed
1002
	}
1003
	else if (tableinfo.relkind == 'r')
Bruce Momjian's avatar
Bruce Momjian committed
1004
	{
1005
		/* Footer information about a table */
1006 1007 1008
		PGresult   *result1 = NULL,
				   *result2 = NULL,
				   *result3 = NULL,
1009
				   *result4 = NULL,
1010 1011
				   *result5 = NULL,
				   *result6 = NULL;
1012 1013 1014
		int			check_count = 0,
					index_count = 0,
					foreignkey_count = 0,
1015
					rule_count = 0,
1016
					trigger_count = 0,
Bruce Momjian's avatar
Bruce Momjian committed
1017
					inherits_count = 0;
1018
		int			count_footers = 0;
1019

1020
		/* count indexes */
1021
		if (tableinfo.hasindex)
1022
		{
1023
			printfPQExpBuffer(&buf,
Bruce Momjian's avatar
Bruce Momjian committed
1024 1025
							  "SELECT c2.relname, i.indisprimary, i.indisunique, i.indisclustered, "
					"pg_catalog.pg_get_indexdef(i.indexrelid, 0, true)\n"
Bruce Momjian's avatar
Bruce Momjian committed
1026 1027 1028 1029
							  "FROM pg_catalog.pg_class c, pg_catalog.pg_class c2, pg_catalog.pg_index i\n"
							  "WHERE c.oid = '%s' AND c.oid = i.indrelid AND i.indexrelid = c2.oid\n"
							  "ORDER BY i.indisprimary DESC, i.indisunique DESC, c2.relname",
							  oid);
1030
			result1 = PSQLexec(buf.data, false);
1031
			if (!result1)
1032
				goto error_return;
1033 1034 1035 1036
			else
				index_count = PQntuples(result1);
		}

1037
		/* count table (and column) check constraints */
1038
		if (tableinfo.checks)
1039
		{
1040
			printfPQExpBuffer(&buf,
1041
							  "SELECT "
Bruce Momjian's avatar
Bruce Momjian committed
1042
						 "pg_catalog.pg_get_constraintdef(r.oid, true), "
1043
							  "conname\n"
Bruce Momjian's avatar
Bruce Momjian committed
1044
							  "FROM pg_catalog.pg_constraint r\n"
Bruce Momjian's avatar
Bruce Momjian committed
1045
						   "WHERE r.conrelid = '%s' AND r.contype = 'c'",
Bruce Momjian's avatar
Bruce Momjian committed
1046
							  oid);
1047
			result2 = PSQLexec(buf.data, false);
1048
			if (!result2)
1049 1050
			{
				PQclear(result1);
1051
				goto error_return;
1052
			}
1053
			else
1054
				check_count = PQntuples(result2);
1055 1056 1057
		}

		/* count rules */
1058
		if (tableinfo.hasrules)
1059
		{
1060
			printfPQExpBuffer(&buf,
1061
							  "SELECT r.rulename, trim(trailing ';' from pg_catalog.pg_get_ruledef(r.oid, true))\n"
Bruce Momjian's avatar
Bruce Momjian committed
1062 1063 1064
							  "FROM pg_catalog.pg_rewrite r\n"
							  "WHERE r.ev_class = '%s'",
							  oid);
1065
			result3 = PSQLexec(buf.data, false);
1066
			if (!result3)
1067 1068 1069
			{
				PQclear(result1);
				PQclear(result2);
1070
				goto error_return;
1071
			}
1072 1073 1074 1075
			else
				rule_count = PQntuples(result3);
		}

1076
		/* count triggers (but ignore foreign-key triggers) */
1077
		if (tableinfo.triggers)
1078
		{
1079
			printfPQExpBuffer(&buf,
Bruce Momjian's avatar
Bruce Momjian committed
1080
				 "SELECT t.tgname, pg_catalog.pg_get_triggerdef(t.oid)\n"
Bruce Momjian's avatar
Bruce Momjian committed
1081 1082
							  "FROM pg_catalog.pg_trigger t\n"
							  "WHERE t.tgrelid = '%s' "
1083
							  "AND (not tgisconstraint "
Bruce Momjian's avatar
Bruce Momjian committed
1084 1085 1086 1087 1088
							  " OR NOT EXISTS"
							  "  (SELECT 1 FROM pg_catalog.pg_depend d "
							  "   JOIN pg_catalog.pg_constraint c ON (d.refclassid = c.tableoid AND d.refobjid = c.oid) "
							  "   WHERE d.classid = t.tableoid AND d.objid = t.oid AND d.deptype = 'i' AND c.contype = 'f'))",
							  oid);
1089
			result4 = PSQLexec(buf.data, false);
1090
			if (!result4)
1091 1092 1093 1094
			{
				PQclear(result1);
				PQclear(result2);
				PQclear(result3);
1095
				goto error_return;
1096
			}
1097 1098 1099 1100
			else
				trigger_count = PQntuples(result4);
		}

Bruce Momjian's avatar
Bruce Momjian committed
1101
		/* count foreign-key constraints (there are none if no triggers) */
1102 1103 1104
		if (tableinfo.triggers)
		{
			printfPQExpBuffer(&buf,
Bruce Momjian's avatar
Bruce Momjian committed
1105
							  "SELECT conname,\n"
Bruce Momjian's avatar
Bruce Momjian committed
1106
			   "  pg_catalog.pg_get_constraintdef(oid, true) as condef\n"
Bruce Momjian's avatar
Bruce Momjian committed
1107 1108 1109
							  "FROM pg_catalog.pg_constraint r\n"
						   "WHERE r.conrelid = '%s' AND r.contype = 'f'",
							  oid);
1110
			result5 = PSQLexec(buf.data, false);
1111
			if (!result5)
1112 1113 1114 1115 1116
			{
				PQclear(result1);
				PQclear(result2);
				PQclear(result3);
				PQclear(result4);
1117
				goto error_return;
1118
			}
1119 1120 1121 1122
			else
				foreignkey_count = PQntuples(result5);
		}

1123 1124 1125 1126 1127 1128 1129 1130 1131
		/* count inherited tables */
		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);

		result6 = PSQLexec(buf.data, false);
		if (!result6)
			goto error_return;
		else
			inherits_count = PQntuples(result6);

1132
		footers = pg_malloc_zero((index_count + check_count + rule_count + trigger_count + foreignkey_count + inherits_count + 7 + 1)
1133
								 * sizeof(*footers));
1134

1135
		/* print indexes */
Bruce Momjian's avatar
Bruce Momjian committed
1136 1137
		if (index_count > 0)
		{
Bruce Momjian's avatar
Bruce Momjian committed
1138
			printfPQExpBuffer(&buf, _("Indexes:"));
1139
			footers[count_footers++] = pg_strdup(buf.data);
Bruce Momjian's avatar
Bruce Momjian committed
1140 1141 1142 1143
			for (i = 0; i < index_count; i++)
			{
				const char *indexdef;
				const char *usingpos;
Bruce Momjian's avatar
Bruce Momjian committed
1144

1145 1146
				/* Output index name */
				printfPQExpBuffer(&buf, _("    \"%s\""),
1147
								  PQgetvalue(result1, i, 0));
1148

Bruce Momjian's avatar
Bruce Momjian committed
1149 1150
				/* Label as primary key or unique (but not both) */
				appendPQExpBuffer(&buf,
Bruce Momjian's avatar
Bruce Momjian committed
1151
							  strcmp(PQgetvalue(result1, i, 1), "t") == 0
Peter Eisentraut's avatar
Peter Eisentraut committed
1152
								  ? " PRIMARY KEY," :
Bruce Momjian's avatar
Bruce Momjian committed
1153
							 (strcmp(PQgetvalue(result1, i, 2), "t") == 0
Peter Eisentraut's avatar
Peter Eisentraut committed
1154
							  ? " UNIQUE,"
Bruce Momjian's avatar
Bruce Momjian committed
1155
							  : ""));
Bruce Momjian's avatar
Bruce Momjian committed
1156
				/* Everything after "USING" is echoed verbatim */
1157
				indexdef = PQgetvalue(result1, i, 4);
Bruce Momjian's avatar
Bruce Momjian committed
1158 1159 1160
				usingpos = strstr(indexdef, " USING ");
				if (usingpos)
					indexdef = usingpos + 7;
1161

Bruce Momjian's avatar
Bruce Momjian committed
1162
				appendPQExpBuffer(&buf, " %s", indexdef);
1163

1164
				if (strcmp(PQgetvalue(result1, i, 3), "t") == 0)
Peter Eisentraut's avatar
Peter Eisentraut committed
1165
					appendPQExpBuffer(&buf, " CLUSTER");
1166

1167
				footers[count_footers++] = pg_strdup(buf.data);
Bruce Momjian's avatar
Bruce Momjian committed
1168
			}
1169 1170
		}

1171
		/* print check constraints */
Bruce Momjian's avatar
Bruce Momjian committed
1172 1173
		if (check_count > 0)
		{
1174
			printfPQExpBuffer(&buf, _("Check constraints:"));
1175
			footers[count_footers++] = pg_strdup(buf.data);
Bruce Momjian's avatar
Bruce Momjian committed
1176 1177
			for (i = 0; i < check_count; i++)
			{
1178
				printfPQExpBuffer(&buf, _("    \"%s\" %s"),
1179 1180
								  PQgetvalue(result2, i, 1),
								  PQgetvalue(result2, i, 0));
Bruce Momjian's avatar
Bruce Momjian committed
1181

1182
				footers[count_footers++] = pg_strdup(buf.data);
Bruce Momjian's avatar
Bruce Momjian committed
1183
			}
1184 1185
		}

1186
		/* print foreign key constraints */
Bruce Momjian's avatar
Bruce Momjian committed
1187 1188
		if (foreignkey_count > 0)
		{
1189
			printfPQExpBuffer(&buf, _("Foreign-key constraints:"));
1190
			footers[count_footers++] = pg_strdup(buf.data);
Bruce Momjian's avatar
Bruce Momjian committed
1191 1192 1193
			for (i = 0; i < foreignkey_count; i++)
			{
				printfPQExpBuffer(&buf, _("    \"%s\" %s"),
1194 1195 1196
								  PQgetvalue(result5, i, 0),
								  PQgetvalue(result5, i, 1));

1197
				footers[count_footers++] = pg_strdup(buf.data);
Bruce Momjian's avatar
Bruce Momjian committed
1198
			}
1199 1200
		}

1201
		/* print rules */
Bruce Momjian's avatar
Bruce Momjian committed
1202 1203
		if (rule_count > 0)
		{
Bruce Momjian's avatar
Bruce Momjian committed
1204
			printfPQExpBuffer(&buf, _("Rules:"));
1205
			footers[count_footers++] = pg_strdup(buf.data);
Bruce Momjian's avatar
Bruce Momjian committed
1206 1207 1208
			for (i = 0; i < rule_count; i++)
			{
				const char *ruledef;
1209

Bruce Momjian's avatar
Bruce Momjian committed
1210 1211 1212
				/* Everything after "CREATE RULE" is echoed verbatim */
				ruledef = PQgetvalue(result3, i, 1);
				ruledef += 12;
1213

Bruce Momjian's avatar
Bruce Momjian committed
1214 1215
				printfPQExpBuffer(&buf, "    %s", ruledef);

1216
				footers[count_footers++] = pg_strdup(buf.data);
Bruce Momjian's avatar
Bruce Momjian committed
1217
			}
1218 1219 1220
		}

		/* print triggers */
Bruce Momjian's avatar
Bruce Momjian committed
1221 1222
		if (trigger_count > 0)
		{
Bruce Momjian's avatar
Bruce Momjian committed
1223
			printfPQExpBuffer(&buf, _("Triggers:"));
1224
			footers[count_footers++] = pg_strdup(buf.data);
Bruce Momjian's avatar
Bruce Momjian committed
1225 1226 1227 1228
			for (i = 0; i < trigger_count; i++)
			{
				const char *tgdef;
				const char *usingpos;
1229

Bruce Momjian's avatar
Bruce Momjian committed
1230 1231 1232 1233 1234
				/* Everything after "TRIGGER" is echoed verbatim */
				tgdef = PQgetvalue(result4, i, 1);
				usingpos = strstr(tgdef, " TRIGGER ");
				if (usingpos)
					tgdef = usingpos + 9;
1235

Bruce Momjian's avatar
Bruce Momjian committed
1236 1237
				printfPQExpBuffer(&buf, "    %s", tgdef);

1238
				footers[count_footers++] = pg_strdup(buf.data);
Bruce Momjian's avatar
Bruce Momjian committed
1239
			}
1240 1241
		}

1242 1243 1244 1245 1246 1247 1248 1249 1250 1251 1252 1253
		/* print inherits */
		for (i = 0; i < inherits_count; i++)
		{
			char	   *s = _("Inherits");

			if (i == 0)
				printfPQExpBuffer(&buf, "%s: %s", s, PQgetvalue(result6, i, 0));
			else
				printfPQExpBuffer(&buf, "%*s  %s", (int) strlen(s), "", PQgetvalue(result6, i, 0));
			if (i < inherits_count - 1)
				appendPQExpBuffer(&buf, ",");

1254
			footers[count_footers++] = pg_strdup(buf.data);
1255 1256
		}

1257 1258
		if (verbose)
		{
Peter Eisentraut's avatar
Peter Eisentraut committed
1259
			char	   *s = _("Has OIDs");
Bruce Momjian's avatar
Bruce Momjian committed
1260

1261 1262 1263 1264 1265
			printfPQExpBuffer(&buf, "%s: %s", s,
							  (tableinfo.hasoids ? _("yes") : _("no")));
			footers[count_footers++] = pg_strdup(buf.data);
		}

1266
		add_tablespace_footer(tableinfo.relkind, tableinfo.tablespace,
Bruce Momjian's avatar
Bruce Momjian committed
1267
							  footers, &count_footers, buf);
1268 1269 1270 1271 1272 1273 1274
		/* end of list marker */
		footers[count_footers] = NULL;

		PQclear(result1);
		PQclear(result2);
		PQclear(result3);
		PQclear(result4);
1275
		PQclear(result5);
1276
		PQclear(result6);
1277
	}
1278

1279 1280 1281 1282 1283 1284 1285
	printTable(title.data, headers,
			   (const char **) cells, (const char **) footers,
			   "llll", &myopt, pset.queryFout);

	retval = true;

error_return:
1286

Bruce Momjian's avatar
Bruce Momjian committed
1287
	/* clean up */
1288 1289
	termPQExpBuffer(&buf);
	termPQExpBuffer(&title);
1290
	termPQExpBuffer(&tmpbuf);
1291

1292
	if (cells)
Bruce Momjian's avatar
Bruce Momjian committed
1293
	{
1294 1295 1296
		for (i = 0; i < numrows; i++)
		{
			if (show_modifiers)
1297
				free(cells[i * cols + 2]);
1298
		}
1299
		free(cells);
Bruce Momjian's avatar
Bruce Momjian committed
1300
	}
1301

1302 1303 1304 1305 1306 1307
	if (footers)
	{
		for (ptr = footers; *ptr; ptr++)
			free(*ptr);
		free(footers);
	}
1308

1309 1310 1311
	if (view_def)
		free(view_def);

1312 1313
	if (res)
		PQclear(res);
1314

1315
	return retval;
1316 1317 1318
}


1319
static void
Bruce Momjian's avatar
Bruce Momjian committed
1320 1321
add_tablespace_footer(char relkind, Oid tablespace, char **footers,
					  int *count, PQExpBufferData buf)
1322 1323
{
	/* relkinds for which we support tablespaces */
Bruce Momjian's avatar
Bruce Momjian committed
1324
	if (relkind == 'r' || relkind == 'i')
1325 1326 1327 1328 1329
	{
		/*
		 * We ignore the database default tablespace so that users not
		 * using tablespaces don't need to know about them.
		 */
Bruce Momjian's avatar
Bruce Momjian committed
1330
		if (tablespace != 0)
1331 1332
		{
			PGresult   *result1 = NULL;
Bruce Momjian's avatar
Bruce Momjian committed
1333

1334
			printfPQExpBuffer(&buf, "SELECT spcname FROM pg_tablespace \n"
Bruce Momjian's avatar
Bruce Momjian committed
1335
							  "WHERE oid = '%u';", tablespace);
1336
			result1 = PSQLexec(buf.data, false);
Bruce Momjian's avatar
Bruce Momjian committed
1337
			if (!result1)
1338 1339
				return;
			/* Should always be the case, but.... */
Bruce Momjian's avatar
Bruce Momjian committed
1340
			if (PQntuples(result1) > 0)
1341
			{
1342
				printfPQExpBuffer(&buf, _("Tablespace: \"%s\""),
Bruce Momjian's avatar
Bruce Momjian committed
1343
								  PQgetvalue(result1, 0, 0));
1344 1345 1346 1347 1348 1349 1350
				footers[(*count)++] = pg_strdup(buf.data);
			}
			PQclear(result1);
		}
	}
}

1351
/*
1352
 * \du
1353
 *
1354
 * Describes users.  Any schema portion of the pattern is ignored.
1355 1356
 */
bool
1357
describeUsers(const char *pattern)
1358
{
1359
	PQExpBufferData buf;
1360
	PGresult   *res;
1361
	printQueryOpt myopt = pset.popt;
1362

1363 1364 1365
	initPQExpBuffer(&buf);

	printfPQExpBuffer(&buf,
Bruce Momjian's avatar
Bruce Momjian committed
1366 1367 1368 1369 1370 1371
					  "SELECT u.usename AS \"%s\",\n"
					  "  u.usesysid AS \"%s\",\n"
					  "  CASE WHEN u.usesuper AND u.usecreatedb THEN CAST('%s' AS pg_catalog.text)\n"
			"       WHEN u.usesuper THEN CAST('%s' AS pg_catalog.text)\n"
		 "       WHEN u.usecreatedb THEN CAST('%s' AS pg_catalog.text)\n"
					  "       ELSE CAST('' AS pg_catalog.text)\n"
1372 1373
					  "  END AS \"%s\",\n"
					  "  ARRAY(SELECT g.groname FROM pg_catalog.pg_group g WHERE u.usesysid = ANY(g.grolist)) as \"%s\"\n"
Bruce Momjian's avatar
Bruce Momjian committed
1374 1375 1376 1377
					  "FROM pg_catalog.pg_user u\n",
					  _("User name"), _("User ID"),
					  _("superuser, create database"),
					  _("superuser"), _("create database"),
1378
					  _("Attributes"), _("Groups"));
1379 1380 1381 1382

	processNamePattern(&buf, pattern, false, false,
					   NULL, "u.usename", NULL, NULL);

1383
	appendPQExpBuffer(&buf, "ORDER BY 1;");
1384

1385
	res = PSQLexec(buf.data, false);
1386
	termPQExpBuffer(&buf);
1387 1388 1389
	if (!res)
		return false;

1390
	myopt.nullPrint = NULL;
Peter Eisentraut's avatar
Peter Eisentraut committed
1391
	myopt.title = _("List of users");
1392

1393
	printQuery(res, &myopt, pset.queryFout);
1394 1395 1396 1397 1398

	PQclear(res);
	return true;
}

1399

1400 1401 1402 1403 1404 1405 1406 1407 1408 1409 1410 1411 1412 1413 1414 1415 1416 1417 1418 1419 1420 1421 1422 1423 1424 1425 1426 1427 1428 1429 1430
/*
 * \dg
 *
 * Describes groups.
 */
bool
describeGroups(const char *pattern)
{
	PQExpBufferData buf;
	PGresult   *res;
	printQueryOpt myopt = pset.popt;

	initPQExpBuffer(&buf);

	printfPQExpBuffer(&buf,
					  "SELECT g.groname AS \"%s\",\n"
					  "  g.grosysid AS \"%s\"\n"
					  "FROM pg_catalog.pg_group g\n",
					  _("Group name"), _("Group ID"));

	processNamePattern(&buf, pattern, false, false,
					   NULL, "g.groname", NULL, NULL);

	appendPQExpBuffer(&buf, "ORDER BY 1;");

	res = PSQLexec(buf.data, false);
	termPQExpBuffer(&buf);
	if (!res)
		return false;

	myopt.nullPrint = NULL;
Peter Eisentraut's avatar
Peter Eisentraut committed
1431
	myopt.title = _("List of groups");
1432 1433 1434 1435 1436 1437 1438 1439

	printQuery(res, &myopt, pset.queryFout);

	PQclear(res);
	return true;
}


1440 1441 1442 1443 1444
/*
 * listTables()
 *
 * handler for \d, \dt, etc.
 *
1445
 * tabtypes is an array of characters, specifying what info is desired:
1446
 * t - tables
1447
 * i - indexes
1448 1449
 * v - views
 * s - sequences
1450
 * S - system tables (pg_catalog)
1451 1452 1453
 * (any order of the above is fine)
 */
bool
1454
listTables(const char *tabtypes, const char *pattern, bool verbose)
1455
{
1456 1457 1458 1459 1460
	bool		showTables = strchr(tabtypes, 't') != NULL;
	bool		showIndexes = strchr(tabtypes, 'i') != NULL;
	bool		showViews = strchr(tabtypes, 'v') != NULL;
	bool		showSeq = strchr(tabtypes, 's') != NULL;
	bool		showSystem = strchr(tabtypes, 'S') != NULL;
Bruce Momjian's avatar
Bruce Momjian committed
1461

1462
	PQExpBufferData buf;
Bruce Momjian's avatar
Bruce Momjian committed
1463
	PGresult   *res;
1464
	printQueryOpt myopt = pset.popt;
Bruce Momjian's avatar
Bruce Momjian committed
1465

1466
	if (!(showTables || showIndexes || showViews || showSeq))
1467 1468
		showTables = showViews = showSeq = true;

1469
	initPQExpBuffer(&buf);
1470

1471
	printfPQExpBuffer(&buf,
Bruce Momjian's avatar
Bruce Momjian committed
1472 1473 1474 1475 1476 1477 1478
					  "SELECT n.nspname as \"%s\",\n"
					  "  c.relname as \"%s\",\n"
					  "  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"
					  "  u.usename as \"%s\"",
					  _("Schema"), _("Name"),
					  _("table"), _("view"), _("index"), _("sequence"),
					  _("special"), _("Type"), _("Owner"));
1479

1480 1481 1482 1483 1484
	if (showIndexes)
		appendPQExpBuffer(&buf,
						  ",\n c2.relname as \"%s\"",
						  _("Table"));

1485
	if (verbose)
1486
		appendPQExpBuffer(&buf,
Bruce Momjian's avatar
Bruce Momjian committed
1487 1488
		  ",\n  pg_catalog.obj_description(c.oid, 'pg_class') as \"%s\"",
						  _("Description"));
1489

Bruce Momjian's avatar
Bruce Momjian committed
1490
	if (showIndexes)
1491
		appendPQExpBuffer(&buf,
1492
						  "\nFROM pg_catalog.pg_class c"
Bruce Momjian's avatar
Bruce Momjian committed
1493 1494 1495
			  "\n     JOIN pg_catalog.pg_index i ON i.indexrelid = c.oid"
			  "\n     JOIN pg_catalog.pg_class c2 ON i.indrelid = c2.oid"
		"\n     LEFT JOIN pg_catalog.pg_user u ON u.usesysid = c.relowner"
1496
						  "\n     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace\n");
1497 1498
	else
		appendPQExpBuffer(&buf,
1499
						  "\nFROM pg_catalog.pg_class c"
Bruce Momjian's avatar
Bruce Momjian committed
1500
		"\n     LEFT JOIN pg_catalog.pg_user u ON u.usesysid = c.relowner"
1501
						  "\n     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace\n");
1502

1503
	appendPQExpBuffer(&buf, "WHERE c.relkind IN (");
1504
	if (showTables)
1505
		appendPQExpBuffer(&buf, "'r',");
Bruce Momjian's avatar
Bruce Momjian committed
1506
	if (showViews)
1507
		appendPQExpBuffer(&buf, "'v',");
1508
	if (showIndexes)
1509
		appendPQExpBuffer(&buf, "'i',");
1510
	if (showSeq)
1511
		appendPQExpBuffer(&buf, "'S',");
Bruce Momjian's avatar
Bruce Momjian committed
1512
	if (showSystem && showTables)
1513
		appendPQExpBuffer(&buf, "'s',");
Bruce Momjian's avatar
Bruce Momjian committed
1514
	appendPQExpBuffer(&buf, "''");		/* dummy */
1515
	appendPQExpBuffer(&buf, ")\n");
Bruce Momjian's avatar
Bruce Momjian committed
1516

1517
	/*
1518
	 * If showSystem is specified, show only system objects (those in
Bruce Momjian's avatar
Bruce Momjian committed
1519 1520 1521
	 * pg_catalog).  Otherwise, suppress system objects, including those
	 * in pg_catalog and pg_toast.	(We don't want to hide temp tables
	 * though.)
1522
	 */
1523
	if (showSystem)
1524
		appendPQExpBuffer(&buf, "      AND n.nspname = 'pg_catalog'\n");
1525
	else
1526 1527 1528 1529 1530
		appendPQExpBuffer(&buf, "      AND n.nspname NOT IN ('pg_catalog', 'pg_toast')\n");

	processNamePattern(&buf, pattern, true, false,
					   "n.nspname", "c.relname", NULL,
					   "pg_catalog.pg_table_is_visible(c.oid)");
1531

1532
	appendPQExpBuffer(&buf, "ORDER BY 1,2;");
1533

1534
	res = PSQLexec(buf.data, false);
1535
	termPQExpBuffer(&buf);
Bruce Momjian's avatar
Bruce Momjian committed
1536 1537
	if (!res)
		return false;
1538

1539
	if (PQntuples(res) == 0 && !QUIET())
1540
	{
1541
		if (pattern)
1542
			fprintf(pset.queryFout, _("No matching relations found.\n"));
1543
		else
1544
			fprintf(pset.queryFout, _("No relations found.\n"));
1545
	}
Bruce Momjian's avatar
Bruce Momjian committed
1546 1547 1548
	else
	{
		myopt.nullPrint = NULL;
1549
		myopt.title = _("List of relations");
1550

1551
		printQuery(res, &myopt, pset.queryFout);
Bruce Momjian's avatar
Bruce Momjian committed
1552
	}
1553

Bruce Momjian's avatar
Bruce Momjian committed
1554 1555
	PQclear(res);
	return true;
1556
}
1557

1558

1559
/*
1560
 * \dD
1561
 *
1562
 * Describes domains.
1563 1564
 */
bool
1565
listDomains(const char *pattern)
1566
{
1567
	PQExpBufferData buf;
1568 1569 1570
	PGresult   *res;
	printQueryOpt myopt = pset.popt;

1571 1572 1573
	initPQExpBuffer(&buf);

	printfPQExpBuffer(&buf,
Bruce Momjian's avatar
Bruce Momjian committed
1574 1575 1576 1577 1578 1579 1580 1581 1582 1583 1584 1585 1586 1587 1588
					  "SELECT n.nspname as \"%s\",\n"
					  "       t.typname as \"%s\",\n"
					  "       pg_catalog.format_type(t.typbasetype, t.typtypmod) as \"%s\",\n"
					  "       CASE WHEN t.typnotnull AND t.typdefault IS NOT NULL THEN 'not null default '||t.typdefault\n"
					  "            WHEN t.typnotnull AND t.typdefault IS NULL THEN 'not null'\n"
					  "            WHEN NOT t.typnotnull AND t.typdefault IS NOT NULL THEN 'default '||t.typdefault\n"
					  "            ELSE ''\n"
					  "       END as \"%s\"\n"
					  "FROM pg_catalog.pg_type t\n"
	"     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = t.typnamespace\n"
					  "WHERE t.typtype = 'd'\n",
					  _("Schema"),
					  _("Name"),
					  _("Type"),
					  _("Modifier"));
1589 1590 1591 1592 1593 1594

	processNamePattern(&buf, pattern, true, false,
					   "n.nspname", "t.typname", NULL,
					   "pg_catalog.pg_type_is_visible(t.oid)");

	appendPQExpBuffer(&buf, "ORDER BY 1, 2;");
1595

1596
	res = PSQLexec(buf.data, false);
1597
	termPQExpBuffer(&buf);
1598 1599 1600 1601
	if (!res)
		return false;

	myopt.nullPrint = NULL;
1602
	myopt.title = _("List of domains");
1603 1604 1605 1606 1607 1608

	printQuery(res, &myopt, pset.queryFout);

	PQclear(res);
	return true;
}
1609

Bruce Momjian's avatar
Bruce Momjian committed
1610 1611 1612 1613 1614 1615 1616 1617 1618 1619 1620 1621 1622 1623 1624 1625 1626
/*
 * \dc
 *
 * Describes conversions.
 */
bool
listConversions(const char *pattern)
{
	PQExpBufferData buf;
	PGresult   *res;
	printQueryOpt myopt = pset.popt;

	initPQExpBuffer(&buf);

	printfPQExpBuffer(&buf,
					  "SELECT n.nspname AS \"%s\",\n"
					  "       c.conname AS \"%s\",\n"
Bruce Momjian's avatar
Bruce Momjian committed
1627 1628
	"       pg_catalog.pg_encoding_to_char(c.conforencoding) AS \"%s\",\n"
	"       pg_catalog.pg_encoding_to_char(c.contoencoding) AS \"%s\",\n"
Bruce Momjian's avatar
Bruce Momjian committed
1629
					  "       CASE WHEN c.condefault THEN '%s'\n"
1630
					  "       ELSE '%s' END AS \"%s\"\n"
Bruce Momjian's avatar
Bruce Momjian committed
1631
		   "FROM pg_catalog.pg_conversion c, pg_catalog.pg_namespace n\n"
Bruce Momjian's avatar
Bruce Momjian committed
1632 1633 1634 1635
					  "WHERE n.oid = c.connamespace\n",
					  _("Schema"),
					  _("Name"),
					  _("Source"),
1636 1637 1638 1639
					  _("Destination"),
					  _("yes"),
					  _("no"),
					  _("Default?"));
Bruce Momjian's avatar
Bruce Momjian committed
1640 1641 1642 1643 1644 1645 1646 1647 1648 1649 1650 1651 1652 1653 1654 1655 1656 1657 1658 1659 1660 1661 1662 1663 1664 1665 1666 1667 1668 1669 1670 1671 1672 1673 1674 1675

	processNamePattern(&buf, pattern, true, false,
					   "n.nspname", "c.conname", NULL,
					   "pg_catalog.pg_conversion_is_visible(c.oid)");

	appendPQExpBuffer(&buf, "ORDER BY 1, 2;");

	res = PSQLexec(buf.data, false);
	termPQExpBuffer(&buf);
	if (!res)
		return false;

	myopt.nullPrint = NULL;
	myopt.title = _("List of conversions");

	printQuery(res, &myopt, pset.queryFout);

	PQclear(res);
	return true;
}

/*
 * \dC
 *
 * Describes casts.
 */
bool
listCasts(const char *pattern)
{
	PQExpBufferData buf;
	PGresult   *res;
	printQueryOpt myopt = pset.popt;

	initPQExpBuffer(&buf);
/* NEED LEFT JOIN FOR BINARY CASTS */
	printfPQExpBuffer(&buf,
Bruce Momjian's avatar
Bruce Momjian committed
1676 1677
		   "SELECT pg_catalog.format_type(castsource, NULL) AS \"%s\",\n"
		   "       pg_catalog.format_type(casttarget, NULL) AS \"%s\",\n"
1678
					  "       CASE WHEN castfunc = 0 THEN '%s'\n"
Bruce Momjian's avatar
Bruce Momjian committed
1679 1680 1681 1682 1683 1684
					  "            ELSE p.proname\n"
					  "       END as \"%s\",\n"
					  "       CASE WHEN c.castcontext = 'e' THEN '%s'\n"
					  "            WHEN c.castcontext = 'a' THEN '%s'\n"
					  "            ELSE '%s'\n"
					  "       END as \"%s\"\n"
Bruce Momjian's avatar
Bruce Momjian committed
1685
			 "FROM pg_catalog.pg_cast c LEFT JOIN pg_catalog.pg_proc p\n"
1686 1687
					  "     ON c.castfunc = p.oid\n"
					  "ORDER BY 1, 2",
1688 1689 1690
					  _("Source type"),
					  _("Target type"),
					  _("(binary compatible)"),
Bruce Momjian's avatar
Bruce Momjian committed
1691
					  _("Function"),
1692 1693 1694 1695
					  _("no"),
					  _("in assignment"),
					  _("yes"),
					  _("Implicit?"));
Bruce Momjian's avatar
Bruce Momjian committed
1696 1697 1698 1699 1700 1701 1702 1703 1704 1705 1706 1707 1708 1709 1710

	res = PSQLexec(buf.data, false);
	termPQExpBuffer(&buf);
	if (!res)
		return false;

	myopt.nullPrint = NULL;
	myopt.title = _("List of casts");

	printQuery(res, &myopt, pset.queryFout);

	PQclear(res);
	return true;
}

1711 1712 1713 1714 1715 1716
/*
 * \dn
 *
 * Describes schemas (namespaces)
 */
bool
1717
listSchemas(const char *pattern, bool verbose)
1718 1719 1720 1721 1722 1723 1724
{
	PQExpBufferData buf;
	PGresult   *res;
	printQueryOpt myopt = pset.popt;

	initPQExpBuffer(&buf);
	printfPQExpBuffer(&buf,
Bruce Momjian's avatar
Bruce Momjian committed
1725 1726 1727 1728
					  "SELECT n.nspname AS \"%s\",\n"
					  "       u.usename AS \"%s\"",
					  _("Name"), _("Owner"));

1729 1730
	if (verbose)
		appendPQExpBuffer(&buf,
Bruce Momjian's avatar
Bruce Momjian committed
1731 1732 1733 1734
						  ",\n  n.nspacl as \"%s\","
		 "  pg_catalog.obj_description(n.oid, 'pg_namespace') as \"%s\"",
						  _("Access privileges"), _("Description"));

1735
	appendPQExpBuffer(&buf,
Bruce Momjian's avatar
Bruce Momjian committed
1736 1737 1738 1739
	  "\nFROM pg_catalog.pg_namespace n LEFT JOIN pg_catalog.pg_user u\n"
					  "       ON n.nspowner=u.usesysid\n"
				 "WHERE	(n.nspname NOT LIKE 'pg\\\\_temp\\\\_%%' OR\n"
	   "		 n.nspname = (pg_catalog.current_schemas(true))[1])\n");		/* temp schema is first */
1740

1741
	processNamePattern(&buf, pattern, true, false,
1742 1743 1744 1745 1746 1747 1748 1749 1750 1751 1752 1753 1754 1755 1756 1757 1758 1759 1760 1761
					   NULL, "n.nspname", NULL,
					   NULL);

	appendPQExpBuffer(&buf, "ORDER BY 1;");

	res = PSQLexec(buf.data, false);
	termPQExpBuffer(&buf);
	if (!res)
		return false;

	myopt.nullPrint = NULL;
	myopt.title = _("List of schemas");

	printQuery(res, &myopt, pset.queryFout);

	PQclear(res);
	return true;
}


1762 1763 1764 1765 1766 1767 1768 1769 1770 1771 1772 1773 1774 1775 1776
/*
 * processNamePattern
 *
 * Scan a wildcard-pattern option and generate appropriate WHERE clauses
 * to limit the set of objects returned.  The WHERE clauses are appended
 * to buf.
 *
 * pattern: user-specified pattern option to a \d command, or NULL if none.
 * have_where: true if caller already emitted WHERE.
 * force_escape: always quote regexp special characters, even outside quotes.
 * schemavar: name of WHERE variable to match against a schema-name pattern.
 * Can be NULL if no schema.
 * namevar: name of WHERE variable to match against an object-name pattern.
 * altnamevar: NULL, or name of an alternate variable to match against name.
 * visibilityrule: clause to use if we want to restrict to visible objects
Bruce Momjian's avatar
Bruce Momjian committed
1777
 * (for example, "pg_catalog.pg_table_is_visible(p.oid)").	Can be NULL.
1778 1779 1780 1781 1782 1783 1784 1785 1786 1787 1788 1789 1790 1791 1792 1793 1794 1795 1796 1797 1798 1799 1800 1801 1802 1803 1804 1805 1806 1807 1808 1809 1810 1811 1812 1813 1814 1815 1816 1817 1818 1819 1820 1821 1822 1823 1824 1825 1826 1827 1828 1829 1830 1831
 */
static void
processNamePattern(PQExpBuffer buf, const char *pattern,
				   bool have_where, bool force_escape,
				   const char *schemavar, const char *namevar,
				   const char *altnamevar, const char *visibilityrule)
{
	PQExpBufferData schemabuf;
	PQExpBufferData namebuf;
	bool		inquotes;
	const char *cp;
	int			i;

#define WHEREAND() \
	(appendPQExpBuffer(buf, have_where ? "      AND " : "WHERE "), have_where = true)

	if (pattern == NULL)
	{
		/* Default: select all visible objects */
		if (visibilityrule)
		{
			WHEREAND();
			appendPQExpBuffer(buf, "%s\n", visibilityrule);
		}
		return;
	}

	initPQExpBuffer(&schemabuf);
	initPQExpBuffer(&namebuf);

	/*
	 * Parse the pattern, converting quotes and lower-casing unquoted
	 * letters; we assume this was NOT done by scan_option.  Also, adjust
	 * shell-style wildcard characters into regexp notation.
	 */
	inquotes = false;
	cp = pattern;

	while (*cp)
	{
		if (*cp == '"')
		{
			if (inquotes && cp[1] == '"')
			{
				/* emit one quote */
				appendPQExpBufferChar(&namebuf, '"');
				cp++;
			}
			inquotes = !inquotes;
			cp++;
		}
		else if (!inquotes && isupper((unsigned char) *cp))
		{
			appendPQExpBufferChar(&namebuf,
1832
								  pg_tolower((unsigned char) *cp));
1833 1834 1835 1836 1837 1838 1839 1840 1841 1842 1843 1844 1845 1846 1847 1848 1849 1850 1851 1852 1853 1854 1855 1856 1857 1858
			cp++;
		}
		else if (!inquotes && *cp == '*')
		{
			appendPQExpBuffer(&namebuf, ".*");
			cp++;
		}
		else if (!inquotes && *cp == '?')
		{
			appendPQExpBufferChar(&namebuf, '.');
			cp++;
		}
		else if (!inquotes && *cp == '.')
		{
			/* Found schema/name separator, move current pattern to schema */
			resetPQExpBuffer(&schemabuf);
			appendPQExpBufferStr(&schemabuf, namebuf.data);
			resetPQExpBuffer(&namebuf);
			cp++;
		}
		else
		{
			/*
			 * Ordinary data character, transfer to pattern
			 *
			 * Inside double quotes, or at all times if parsing an operator
Bruce Momjian's avatar
Bruce Momjian committed
1859 1860 1861 1862 1863
			 * name, quote regexp special characters with a backslash to
			 * avoid regexp errors.  Outside quotes, however, let them
			 * pass through as-is; this lets knowledgeable users build
			 * regexp expressions that are more powerful than shell-style
			 * patterns.
1864 1865 1866 1867 1868 1869 1870 1871 1872 1873 1874 1875 1876 1877 1878 1879 1880 1881 1882 1883 1884 1885 1886 1887 1888 1889 1890 1891
			 */
			if ((inquotes || force_escape) &&
				strchr("|*+?()[]{}.^$\\", *cp))
				appendPQExpBuffer(&namebuf, "\\\\");

			/* Ensure chars special to string literals are passed properly */
			if (*cp == '\'' || *cp == '\\')
				appendPQExpBufferChar(&namebuf, *cp);

			i = PQmblen(cp, pset.encoding);
			while (i--)
			{
				appendPQExpBufferChar(&namebuf, *cp);
				cp++;
			}
		}
	}

	/*
	 * Now decide what we need to emit.
	 */
	if (schemabuf.len > 0)
	{
		/* We have a schema pattern, so constrain the schemavar */

		appendPQExpBufferChar(&schemabuf, '$');
		/* Optimize away ".*$", and possibly the whole pattern */
		if (schemabuf.len >= 3 &&
Bruce Momjian's avatar
Bruce Momjian committed
1892 1893
			strcmp(schemabuf.data + (schemabuf.len - 3), ".*$") == 0)
			schemabuf.data[schemabuf.len - 3] = '\0';
1894 1895 1896 1897 1898 1899 1900 1901 1902 1903 1904 1905 1906 1907 1908 1909 1910 1911 1912 1913 1914 1915 1916 1917 1918

		if (schemabuf.data[0] && schemavar)
		{
			WHEREAND();
			appendPQExpBuffer(buf, "%s ~ '^%s'\n",
							  schemavar, schemabuf.data);
		}
	}
	else
	{
		/* No schema pattern given, so select only visible objects */
		if (visibilityrule)
		{
			WHEREAND();
			appendPQExpBuffer(buf, "%s\n", visibilityrule);
		}
	}

	if (namebuf.len > 0)
	{
		/* We have a name pattern, so constrain the namevar(s) */

		appendPQExpBufferChar(&namebuf, '$');
		/* Optimize away ".*$", and possibly the whole pattern */
		if (namebuf.len >= 3 &&
Bruce Momjian's avatar
Bruce Momjian committed
1919 1920
			strcmp(namebuf.data + (namebuf.len - 3), ".*$") == 0)
			namebuf.data[namebuf.len - 3] = '\0';
1921 1922 1923 1924 1925 1926 1927 1928 1929 1930 1931 1932 1933 1934 1935 1936 1937 1938 1939 1940 1941 1942

		if (namebuf.data[0])
		{
			WHEREAND();
			if (altnamevar)
				appendPQExpBuffer(buf,
								  "(%s ~ '^%s'\n"
								  "        OR %s ~ '^%s')\n",
								  namevar, namebuf.data,
								  altnamevar, namebuf.data);
			else
				appendPQExpBuffer(buf,
								  "%s ~ '^%s'\n",
								  namevar, namebuf.data);
		}
	}

	termPQExpBuffer(&schemabuf);
	termPQExpBuffer(&namebuf);

#undef WHEREAND
}