describe.c 27.4 KB
Newer Older
Peter Eisentraut's avatar
Peter Eisentraut committed
1 2 3
/*
 * psql - the PostgreSQL interactive terminal
 *
4
 * Copyright 2000 by PostgreSQL Global Development Group
Peter Eisentraut's avatar
Peter Eisentraut committed
5
 *
6
 * $Header: /cvsroot/pgsql/src/bin/psql/describe.c,v 1.48 2002/04/05 11:52:38 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 13 14 15 16 17

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

18 19
#define _(x) gettext((x))

20 21 22 23 24

/*----------------
 * Handlers for various slash commands displaying some sort of list
 * of things in the database.
 *
25
 * If you add something here, try to format the query to look nice in -E output.
26 27 28 29
 *----------------
 */

/* the maximal size of regular expression we'll accept here */
30 31
/* (it is safe to just change this here) */
#define REGEXP_CUTOFF (10 * NAMEDATALEN)
32 33 34 35 36 37


/* \da
 * takes an optional regexp to match specific aggregates by name
 */
bool
38
describeAggregates(const char *name)
39
{
40
	char		buf[384 + REGEXP_CUTOFF];
Bruce Momjian's avatar
Bruce Momjian committed
41
	PGresult   *res;
42
	printQueryOpt myopt = pset.popt;
Bruce Momjian's avatar
Bruce Momjian committed
43 44 45 46 47

	/*
	 * There are two kinds of aggregates: ones that work on particular
	 * types ones that work on all
	 */
48 49 50 51 52 53
	snprintf(buf, sizeof(buf),
			 "SELECT a.aggname AS \"%s\",\n"
			 "  CASE a.aggbasetype\n"
			 "    WHEN 0 THEN CAST('%s' AS text)\n"
			 "    ELSE format_type(a.aggbasetype, NULL)\n"
			 "  END AS \"%s\",\n"
54
			 "  obj_description(a.oid, 'pg_aggregate') as \"%s\"\n"
55 56
			 "FROM pg_aggregate a\n",
			 _("Name"), _("(all types)"),
57
			 _("Data type"), _("Description"));
58

Bruce Momjian's avatar
Bruce Momjian committed
59 60
	if (name)
	{
61
		strcat(buf, "WHERE a.aggname ~ '^");
62 63
		strncat(buf, name, REGEXP_CUTOFF);
		strcat(buf, "'\n");
Bruce Momjian's avatar
Bruce Momjian committed
64 65
	}

66
	strcat(buf, "ORDER BY 1, 2;");
Bruce Momjian's avatar
Bruce Momjian committed
67

68
	res = PSQLexec(buf);
Bruce Momjian's avatar
Bruce Momjian committed
69 70 71 72
	if (!res)
		return false;

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

75
	printQuery(res, &myopt, pset.queryFout);
Bruce Momjian's avatar
Bruce Momjian committed
76 77 78

	PQclear(res);
	return true;
79 80 81 82
}


/* \df
83
 * Takes an optional regexp to narrow down the function name
84 85
 */
bool
86
describeFunctions(const char *name, bool verbose)
87
{
88
	char		buf[384 + REGEXP_CUTOFF];
Bruce Momjian's avatar
Bruce Momjian committed
89
	PGresult   *res;
90
	printQueryOpt myopt = pset.popt;
Bruce Momjian's avatar
Bruce Momjian committed
91 92 93 94 95

	/*
	 * we skip in/out funcs by excluding functions that take some
	 * arguments, but have no types defined for those arguments
	 */
96 97 98 99 100
	snprintf(buf, sizeof(buf),
			 "SELECT format_type(p.prorettype, NULL) as \"%s\",\n"
			 "  p.proname as \"%s\",\n"
			 "  oidvectortypes(p.proargtypes) as \"%s\"",
			 _("Result data type"), _("Name"),
101
			 _("Argument data types"));
102

103
	if (verbose)
104 105 106 107
		snprintf(buf + strlen(buf), sizeof(buf) - strlen(buf),
				 ",\n  u.usename as \"%s\",\n"
				 "  l.lanname as \"%s\",\n"
				 "  p.prosrc as \"%s\",\n"
108
				 "  obj_description(p.oid, 'pg_proc') as \"%s\"",
109
				 _("Owner"), _("Language"),
110
				 _("Source code"), _("Description"));
111

112 113
	if (!verbose)
		strcat(buf,
114
			   "\nFROM pg_proc p\n"
115
			   "WHERE p.prorettype <> 0 AND (pronargs = 0 OR oidvectortypes(p.proargtypes) <> '')\n");
116 117
	else
		strcat(buf,
118 119
			   "\nFROM pg_proc p,  pg_language l, pg_user u\n"
			   "WHERE p.prolang = l.oid AND p.proowner = u.usesysid\n"
120
			   "  AND p.prorettype <> 0 AND (pronargs = 0 OR oidvectortypes(p.proargtypes) <> '')\n");
121

Bruce Momjian's avatar
Bruce Momjian committed
122 123
	if (name)
	{
Peter Eisentraut's avatar
Peter Eisentraut committed
124
		strcat(buf, "  AND p.proname ~ '^");
125 126
		strncat(buf, name, REGEXP_CUTOFF);
		strcat(buf, "'\n");
Bruce Momjian's avatar
Bruce Momjian committed
127
	}
128
	strcat(buf, "ORDER BY 2, 1, 3;");
Bruce Momjian's avatar
Bruce Momjian committed
129

130
	res = PSQLexec(buf);
Bruce Momjian's avatar
Bruce Momjian committed
131 132 133 134
	if (!res)
		return false;

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

137
	printQuery(res, &myopt, pset.queryFout);
Bruce Momjian's avatar
Bruce Momjian committed
138 139 140

	PQclear(res);
	return true;
141 142 143 144 145
}



/*
146 147
 * \dT
 * describe types
148 149
 */
bool
150
describeTypes(const char *name, bool verbose)
151
{
152
	char		buf[384 + 2 * REGEXP_CUTOFF];
Bruce Momjian's avatar
Bruce Momjian committed
153
	PGresult   *res;
154
	printQueryOpt myopt = pset.popt;
Bruce Momjian's avatar
Bruce Momjian committed
155

156 157
	snprintf(buf, sizeof(buf),
			 "SELECT format_type(t.oid, NULL) AS \"%s\",\n",
158
			 _("Name"));
159
	if (verbose)
160 161 162 163 164 165
		snprintf(buf + strlen(buf), sizeof(buf) - strlen(buf),
				 "  t.typname AS \"%s\",\n"
				 "  CASE WHEN t.typlen = -1\n"
				 "    THEN CAST('var' AS text)\n"
				 "    ELSE CAST(t.typlen AS text)\n"
				 "  END AS \"%s\",\n",
166 167
				 _("Internal name"), _("Size"));
	snprintf(buf + strlen(buf), sizeof(buf) - strlen(buf),
168
			 "  obj_description(t.oid, 'pg_type') as \"%s\"\n",
169
			 _("Description"));
170 171 172 173 174

	/*
	 * do not include array types (start with underscore), do not include
	 * user relations (typrelid!=0)
	 */
175
	strcat(buf, "FROM pg_type t\nWHERE t.typrelid = 0 AND t.typname !~ '^_.*'\n");
Bruce Momjian's avatar
Bruce Momjian committed
176 177 178

	if (name)
	{
179 180 181 182
		/* accept either internal or external type name */
		strcat(buf, "  AND (format_type(t.oid, NULL) ~ '^");
		strncat(buf, name, REGEXP_CUTOFF);
		strcat(buf, "' OR t.typname ~ '^");
183
		strncat(buf, name, REGEXP_CUTOFF);
184
		strcat(buf, "')\n");
Bruce Momjian's avatar
Bruce Momjian committed
185
	}
186
	strcat(buf, "ORDER BY 1;");
Bruce Momjian's avatar
Bruce Momjian committed
187

188
	res = PSQLexec(buf);
Bruce Momjian's avatar
Bruce Momjian committed
189 190 191 192
	if (!res)
		return false;

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

195
	printQuery(res, &myopt, pset.queryFout);
Bruce Momjian's avatar
Bruce Momjian committed
196 197 198

	PQclear(res);
	return true;
199 200 201 202 203 204 205
}



/* \do
 */
bool
206
describeOperators(const char *name)
207
{
208
	char		buf[384 + REGEXP_CUTOFF];
Bruce Momjian's avatar
Bruce Momjian committed
209
	PGresult   *res;
210
	printQueryOpt myopt = pset.popt;
Bruce Momjian's avatar
Bruce Momjian committed
211

212 213 214 215
	snprintf(buf, sizeof(buf),
			 "SELECT o.oprname AS \"%s\",\n"
			 "  CASE WHEN o.oprkind='l' THEN NULL ELSE format_type(o.oprleft, NULL) END AS \"%s\",\n"
			 "  CASE WHEN o.oprkind='r' THEN NULL ELSE format_type(o.oprright, NULL) END AS \"%s\",\n"
216 217 218
			 "  format_type(o.oprresult, NULL) AS \"%s\",\n"
			 "  obj_description(o.oprcode, 'pg_proc') AS \"%s\"\n"
			 "FROM pg_operator o\n",
219
			 _("Name"), _("Left arg type"), _("Right arg type"),
220
			 _("Result type"), _("Description"));
Bruce Momjian's avatar
Bruce Momjian committed
221 222
	if (name)
	{
223
		strcat(buf, "WHERE o.oprname = '");
224 225
		strncat(buf, name, REGEXP_CUTOFF);
		strcat(buf, "'\n");
Bruce Momjian's avatar
Bruce Momjian committed
226 227
	}

228
	strcat(buf, "ORDER BY 1, 2, 3, 4;");
Bruce Momjian's avatar
Bruce Momjian committed
229

230
	res = PSQLexec(buf);
Bruce Momjian's avatar
Bruce Momjian committed
231 232 233 234
	if (!res)
		return false;

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

237
	printQuery(res, &myopt, pset.queryFout);
Bruce Momjian's avatar
Bruce Momjian committed
238 239 240

	PQclear(res);
	return true;
241 242 243 244 245 246 247 248 249
}


/*
 * listAllDbs
 *
 * for \l, \list, and -l switch
 */
bool
250
listAllDbs(bool desc)
251
{
Bruce Momjian's avatar
Bruce Momjian committed
252
	PGresult   *res;
253
	char		buf[1024];
254
	printQueryOpt myopt = pset.popt;
255

256 257 258 259
	snprintf(buf, sizeof(buf),
			 "SELECT d.datname as \"%s\",\n"
			 "       u.usename as \"%s\"",
			 _("Name"), _("Owner"));
260
#ifdef MULTIBYTE
261 262 263
	snprintf(buf + strlen(buf), sizeof(buf) - strlen(buf),
			 ",\n       pg_encoding_to_char(d.encoding) as \"%s\"",
			 _("Encoding"));
264
#endif
265
	if (desc)
266
		snprintf(buf + strlen(buf), sizeof(buf) - strlen(buf),
267
			 ",\n       obj_description(d.oid, 'pg_database') as \"%s\"",
268
				 _("Description"));
269
	strcat(buf,
270
	"\nFROM pg_database d LEFT JOIN pg_user u ON d.datdba = u.usesysid\n"
271
		   "ORDER BY 1;");
272

273
	res = PSQLexec(buf);
Bruce Momjian's avatar
Bruce Momjian committed
274 275
	if (!res)
		return false;
276

Bruce Momjian's avatar
Bruce Momjian committed
277
	myopt.nullPrint = NULL;
278
	myopt.title = _("List of databases");
279

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

Bruce Momjian's avatar
Bruce Momjian committed
282 283
	PQclear(res);
	return true;
284 285 286
}


287 288
/*
 * List Tables Grant/Revoke Permissions
289 290 291
 * \z (now also \dp -- perhaps more mnemonic)
 */
bool
292
permissionsList(const char *name)
293
{
294
	char		buf[256 + REGEXP_CUTOFF];
Bruce Momjian's avatar
Bruce Momjian committed
295
	PGresult   *res;
296
	printQueryOpt myopt = pset.popt;
Bruce Momjian's avatar
Bruce Momjian committed
297 298

	/* Currently, we ignore indexes since they have no meaningful rights */
299 300 301 302 303 304 305
	snprintf(buf, sizeof(buf),
			 "SELECT relname as \"%s\",\n"
			 "       relacl as \"%s\"\n"
			 "FROM   pg_class\n"
			 "WHERE  relkind in ('r', 'v', 'S') AND\n"
			 "       relname NOT LIKE 'pg$_%%' ESCAPE '$'\n",
			 _("Table"), _("Access privileges"));
Bruce Momjian's avatar
Bruce Momjian committed
306 307
	if (name)
	{
308 309 310
		strcat(buf, "  AND relname ~ '^");
		strncat(buf, name, REGEXP_CUTOFF);
		strcat(buf, "'\n");
Bruce Momjian's avatar
Bruce Momjian committed
311
	}
312
	strcat(buf, "ORDER BY 1;");
313

314
	res = PSQLexec(buf);
Bruce Momjian's avatar
Bruce Momjian committed
315 316 317
	if (!res)
		return false;

318
	myopt.nullPrint = NULL;
319 320
	sprintf(buf, _("Access privileges for database \"%s\""), PQdb(pset.db));
	myopt.title = buf;
321

322
	printQuery(res, &myopt, pset.queryFout);
Bruce Momjian's avatar
Bruce Momjian committed
323 324 325

	PQclear(res);
	return true;
326 327 328 329 330 331 332 333 334 335 336 337 338
}



/*
 * 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
339
objectDescription(const char *object)
340
{
341
	char		descbuf[2048 + REGEXP_CUTOFF];
Bruce Momjian's avatar
Bruce Momjian committed
342
	PGresult   *res;
343
	printQueryOpt myopt = pset.popt;
Bruce Momjian's avatar
Bruce Momjian committed
344

345 346 347 348
	snprintf(descbuf, sizeof(descbuf),
			 "SELECT DISTINCT tt.name AS \"%s\", tt.object AS \"%s\", d.description AS \"%s\"\n"
			 "FROM (\n"

349
	/* Aggregate descriptions */
350
			 "  SELECT a.oid as oid, a.tableoid as tableoid,\n"
351
	  "  CAST(a.aggname AS text) as name, CAST('%s' AS text) as object\n"
352 353
			 "  FROM pg_aggregate a\n"

354
	/* Function descriptions (except in/outs for datatypes) */
355
			 "UNION ALL\n"
356
			 "  SELECT p.oid as oid, p.tableoid as tableoid,\n"
357
	  "  CAST(p.proname AS text) as name, CAST('%s' AS text) as object\n"
358
			 "  FROM pg_proc p\n"
359
		"  WHERE p.pronargs = 0 or oidvectortypes(p.proargtypes) <> ''\n"
360

361
	/* Operator descriptions (must get comment via associated function) */
362
			 "UNION ALL\n"
363 364
			 "  SELECT RegprocToOid(o.oprcode) as oid,\n"
			 "  (SELECT oid FROM pg_class WHERE relname = 'pg_proc') as tableoid,\n"
365
	  "  CAST(o.oprname AS text) as name, CAST('%s' AS text) as object\n"
366 367
			 "  FROM pg_operator o\n"

368
	/* Type description */
369
			 "UNION ALL\n"
370
			 "  SELECT t.oid as oid, t.tableoid as tableoid,\n"
371
	 "  format_type(t.oid, NULL) as name, CAST('%s' AS text) as object\n"
372 373
			 "  FROM pg_type t\n"

374
	/* Relation (tables, views, indexes, sequences) descriptions */
375
			 "UNION ALL\n"
376 377
			 "  SELECT c.oid as oid, c.tableoid as tableoid,\n"
			 "  CAST(c.relname AS text) as name,\n"
378 379 380 381 382
			 "  CAST(\n"
			 "    CASE c.relkind WHEN 'r' THEN '%s' WHEN 'v' THEN '%s' WHEN 'i' THEN '%s' WHEN 'S' THEN '%s' END"
			 "  AS text) as object\n"
			 "  FROM pg_class c\n"

383
	/* Rule description (ignore rules for views) */
384
			 "UNION ALL\n"
385
			 "  SELECT r.oid as oid, r.tableoid as tableoid,\n"
386
	 "  CAST(r.rulename AS text) as name, CAST('%s' AS text) as object\n"
387 388 389
			 "  FROM pg_rewrite r\n"
			 "  WHERE r.rulename !~ '^_RET'\n"

390
	/* Trigger description */
391
			 "UNION ALL\n"
392
			 "  SELECT t.oid as oid, t.tableoid as tableoid,\n"
393
	   "  CAST(t.tgname AS text) as name, CAST('%s' AS text) as object\n"
394 395 396 397
			 "  FROM pg_trigger t\n"

			 ") AS tt,\n"
			 "pg_description d\n"
398
			 "WHERE tt.oid = d.objoid and tt.tableoid = d.classoid and d.objsubid = 0\n",
399 400 401 402 403 404 405

			 _("Name"), _("Object"), _("Description"),
			 _("aggregate"), _("function"), _("operator"),
			 _("data type"), _("table"), _("view"),
			 _("index"), _("sequence"), _("rule"),
			 _("trigger")
		);
Bruce Momjian's avatar
Bruce Momjian committed
406 407 408

	if (object)
	{
409
		strcat(descbuf, "  AND tt.name ~ '^");
Bruce Momjian's avatar
Bruce Momjian committed
410 411 412
		strncat(descbuf, object, REGEXP_CUTOFF);
		strcat(descbuf, "'\n");
	}
413
	strcat(descbuf, "ORDER BY 1;");
Bruce Momjian's avatar
Bruce Momjian committed
414 415


416
	res = PSQLexec(descbuf);
Bruce Momjian's avatar
Bruce Momjian committed
417 418 419 420
	if (!res)
		return false;

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

423
	printQuery(res, &myopt, pset.queryFout);
Bruce Momjian's avatar
Bruce Momjian committed
424 425 426

	PQclear(res);
	return true;
427 428 429 430 431 432 433
}



/*
 * describeTableDetails (for \d)
 *
434
 * Unfortunately, the information presented here is so complicated that it cannot
435 436 437 438
 * be done in a single query. So we have to assemble the printed table by hand
 * and pass it to the underlying printTable() function.
 *
 */
439

Bruce Momjian's avatar
Bruce Momjian committed
440 441
static void *
xmalloc(size_t size)
442
{
Bruce Momjian's avatar
Bruce Momjian committed
443 444 445 446 447
	void	   *tmp;

	tmp = malloc(size);
	if (!tmp)
	{
448
		psql_error("out of memory\n");
Bruce Momjian's avatar
Bruce Momjian committed
449 450 451
		exit(EXIT_FAILURE);
	}
	return tmp;
452 453 454 455
}


bool
456
describeTableDetails(const char *name, bool desc)
457
{
458
	char		buf[512 + INDEX_MAX_KEYS * NAMEDATALEN];
459
	PGresult   *res = NULL;
460
	printTableOpt myopt = pset.popt.topt;
Bruce Momjian's avatar
Bruce Momjian committed
461
	int			i;
462
	const char *view_def = NULL;
Bruce Momjian's avatar
Bruce Momjian committed
463
	const char *headers[5];
Bruce Momjian's avatar
Bruce Momjian committed
464 465 466 467 468
	char	  **cells = NULL;
	char	   *title = NULL;
	char	  **footers = NULL;
	char	  **ptr;
	unsigned int cols;
469 470 471 472 473 474 475 476 477 478 479 480 481 482
	struct
	{
		bool		hasindex;
		char		relkind;
		int16		checks;
		int16		triggers;
		bool		hasrules;
	}			tableinfo;
	bool		error = false;

	/* truncate table name */
	if (strlen(name) > NAMEDATALEN)
	{
		char	   *my_name = xmalloc(NAMEDATALEN + 1);
Bruce Momjian's avatar
Bruce Momjian committed
483

484 485 486 487
		strncpy(my_name, name, NAMEDATALEN);
		my_name[NAMEDATALEN] = '\0';
		name = my_name;
	}
488

Bruce Momjian's avatar
Bruce Momjian committed
489
	/* Get general table info */
490 491 492 493 494 495 496
	sprintf(buf,
	 "SELECT relhasindex, relkind, relchecks, reltriggers, relhasrules\n"
			"FROM pg_class WHERE relname='%s'",
			name);
	res = PSQLexec(buf);
	if (!res)
		return false;
Bruce Momjian's avatar
Bruce Momjian committed
497 498 499 500

	/* Did we get anything? */
	if (PQntuples(res) == 0)
	{
501
		if (!QUIET())
502
			fprintf(stderr, _("Did not find any relation named \"%s\".\n"), name);
Bruce Momjian's avatar
Bruce Momjian committed
503 504 505 506
		PQclear(res);
		return false;
	}

507 508 509 510 511 512 513
	/* FIXME: check for null pointers here? */
	tableinfo.hasindex = strcmp(PQgetvalue(res, 0, 0), "t") == 0;
	tableinfo.relkind = *(PQgetvalue(res, 0, 1));
	tableinfo.checks = atoi(PQgetvalue(res, 0, 2));
	tableinfo.triggers = atoi(PQgetvalue(res, 0, 3));
	tableinfo.hasrules = strcmp(PQgetvalue(res, 0, 4), "t") == 0;
	PQclear(res);
Bruce Momjian's avatar
Bruce Momjian committed
514 515


516 517
	headers[0] = _("Column");
	headers[1] = _("Type");
518
	cols = 2;
Bruce Momjian's avatar
Bruce Momjian committed
519

520
	if (tableinfo.relkind == 'r' || tableinfo.relkind == 'v')
521 522
	{
		cols++;
523
		headers[cols - 1] = _("Modifiers");
524
	}
Bruce Momjian's avatar
Bruce Momjian committed
525

526
	if (desc)
Bruce Momjian's avatar
Bruce Momjian committed
527
	{
528
		cols++;
529
		headers[cols - 1] = _("Description");
Bruce Momjian's avatar
Bruce Momjian committed
530 531
	}

532
	headers[cols] = NULL;
533 534


535 536 537 538 539 540
	/* Get column info (index requires additional checks) */
	if (tableinfo.relkind == 'i')
		strcpy(buf, "SELECT\n  CASE i.indproc WHEN ('-'::regproc) THEN a.attname\n  ELSE SUBSTR(pg_get_indexdef(attrelid),\n  POSITION('(' in pg_get_indexdef(attrelid)))\n  END, ");
	else
		strcpy(buf, "SELECT a.attname, ");
	strcat(buf, "format_type(a.atttypid, a.atttypmod), a.attnotnull, a.atthasdef, a.attnum");
541
	if (desc)
542
		strcat(buf, ", col_description(a.attrelid, a.attnum)");
543 544 545 546
	strcat(buf, "\nFROM pg_class c, pg_attribute a");
	if (tableinfo.relkind == 'i')
		strcat(buf, ", pg_index i");
	strcat(buf, "\nWHERE c.relname = '");
547
	strncat(buf, name, NAMEDATALEN);
548 549 550 551
	strcat(buf, "'\n  AND a.attnum > 0 AND a.attrelid = c.oid");
	if (tableinfo.relkind == 'i')
		strcat(buf, " AND a.attrelid = i.indexrelid");
	strcat(buf, "\nORDER BY a.attnum");
552

553
	res = PSQLexec(buf);
554 555 556 557
	if (!res)
		return false;

	/* Check if table is a view */
558 559 560 561 562 563 564 565 566 567 568 569 570 571 572 573 574
	if (tableinfo.hasrules)
	{
		PGresult   *result;

		sprintf(buf, "SELECT definition FROM pg_views WHERE viewname = '%s'", name);
		result = PSQLexec(buf);
		if (!result)
		{
			PQclear(res);
			PQclear(result);
			return false;
		}

		if (PQntuples(result) > 0)
			view_def = xstrdup(PQgetvalue(result, 0, 0));
		PQclear(result);
	}
575 576 577 578


	/* Generate table cells to be printed */
	cells = xmalloc((PQntuples(res) * cols + 1) * sizeof(*cells));
579
	cells[PQntuples(res) * cols] = NULL;		/* end of list */
580

Bruce Momjian's avatar
Bruce Momjian committed
581 582 583
	for (i = 0; i < PQntuples(res); i++)
	{
		/* Name */
584 585
		cells[i * cols + 0] = PQgetvalue(res, i, 0);	/* don't free this
														 * afterwards */
Bruce Momjian's avatar
Bruce Momjian committed
586
		/* Type */
587
		cells[i * cols + 1] = PQgetvalue(res, i, 1);	/* don't free this
588
														 * either */
589 590 591

		/* Extra: not null and default */
		/* (I'm cutting off the 'default' string at 128) */
592
		if (tableinfo.relkind == 'r' || tableinfo.relkind == 'v')
593 594 595
		{
			cells[i * cols + 2] = xmalloc(128 + 128);
			cells[i * cols + 2][0] = '\0';
596
			if (strcmp(PQgetvalue(res, i, 2), "t") == 0)
597 598 599
				strcat(cells[i * cols + 2], "not null");

			/* handle "default" here */
600
			if (strcmp(PQgetvalue(res, i, 3), "t") == 0)
601 602 603 604 605
			{
				PGresult   *result;

				sprintf(buf, "SELECT substring(d.adsrc for 128) FROM pg_attrdef d, pg_class c\n"
						"WHERE c.relname = '%s' AND c.oid = d.adrelid AND d.adnum = %s",
606
						name, PQgetvalue(res, i, 4));
607 608 609 610 611 612 613 614 615 616 617 618 619 620 621 622 623 624

				result = PSQLexec(buf);
				if (!result)
					error = true;
				else
				{
					if (cells[i * cols + 2][0])
						strcat(cells[i * cols + 2], " ");
					strcat(cells[i * cols + 2], "default ");
					strcat(cells[i * cols + 2], PQgetvalue(result, 0, 0));
					PQclear(result);
				}
			}
		}

		if (error)
			break;

Bruce Momjian's avatar
Bruce Momjian committed
625
		/* Description */
626
		if (desc)
627
			cells[i * cols + cols - 1] = PQgetvalue(res, i, 5);
Bruce Momjian's avatar
Bruce Momjian committed
628
	}
629

Bruce Momjian's avatar
Bruce Momjian committed
630
	/* Make title */
631
	title = xmalloc(32 + NAMEDATALEN);
632 633 634
	switch (tableinfo.relkind)
	{
		case 'r':
635
			snprintf(title, 32 + NAMEDATALEN, _("Table \"%s\""), name);
636 637
			break;
		case 'v':
638
			snprintf(title, 32 + NAMEDATALEN, _("View \"%s\""), name);
639 640
			break;
		case 'S':
641
			snprintf(title, 32 + NAMEDATALEN, _("Sequence \"%s\""), name);
642 643
			break;
		case 'i':
644
			snprintf(title, 32 + NAMEDATALEN, _("Index \"%s\""), name);
645 646
			break;
		case 's':
647
			snprintf(title, 32 + NAMEDATALEN, _("Special relation \"%s\""), name);
648
			break;
649 650 651
		case 't':
			snprintf(title, 32 + NAMEDATALEN, _("TOAST table \"%s\""), name);
			break;
652
		default:
653
			snprintf(title, 32 + NAMEDATALEN, _("?%c? \"%s\""), tableinfo.relkind, name);
654
			break;
655
	}
Bruce Momjian's avatar
Bruce Momjian committed
656 657

	/* Make footers */
658 659
	if (tableinfo.relkind == 'i')
	{
660
		/* Footer information about an index */
661
		PGresult   *result;
662 663 664 665 666
		sprintf(buf, "SELECT i.indisunique, i.indisprimary, a.amname, c2.relname,\n"
				"pg_get_expr(i.indpred,i.indrelid)\n"
				"FROM pg_index i, pg_class c, pg_class c2, pg_am a\n"
				"WHERE i.indexrelid = c.oid AND c.relname = '%s' AND c.relam = a.oid\n"
				"AND i.indrelid = c2.oid",
667 668 669
				name);

		result = PSQLexec(buf);
670
		if (!result || PQntuples(result) != 1)
671 672 673
			error = true;
		else
		{
674 675 676
			char	   *indisunique = PQgetvalue(result, 0, 0);
			char	   *indisprimary = PQgetvalue(result, 0, 1);
			char	   *indamname = PQgetvalue(result, 0, 2);
677 678
			char	   *indtable = PQgetvalue(result, 0, 3);
			char	   *indpred = PQgetvalue(result, 0, 4);
679

680
			footers = xmalloc(2 * sizeof(*footers));
Peter Eisentraut's avatar
Peter Eisentraut committed
681
			/* XXX This construction is poorly internationalized. */
682 683 684
			footers[0] = xmalloc(NAMEDATALEN*4 + 128);
			snprintf(footers[0], NAMEDATALEN*4 + 128, "%s%s for %s \"%s\"%s%s",
					 strcmp(indisprimary, "t") == 0 ? _("primary key ") : 
685
					 strcmp(indisunique, "t") == 0 ? _("unique ") : "",
686 687 688
					 indamname, _("table"), indtable, 
					 strlen(indpred) ? " WHERE " : "",indpred);
			footers[1] = NULL;
689
		}
690 691

		PQclear(result);
692
	}
693
	else if (view_def)
Bruce Momjian's avatar
Bruce Momjian committed
694
	{
695 696 697 698 699 700 701 702 703 704 705 706 707 708 709 710 711 712 713 714
		PGresult   *result = NULL;
		int			rule_count = 0;
		int			count_footers = 0;

		/* count rules */
		if (!error && tableinfo.hasrules)
		{
			sprintf(buf,
					"SELECT r.rulename\n"
					"FROM pg_rewrite r, pg_class c\n"
					"WHERE c.relname='%s' AND c.oid = r.ev_class\n"
					"AND r.rulename NOT LIKE '_RET%%'",
					name);
			result = PSQLexec(buf);
			if (!result)
				error = true;
			else
				rule_count = PQntuples(result);
		}

715
		/* Footer information about a view */
716 717 718
		footers = xmalloc((rule_count + 2) * sizeof(*footers));
		footers[count_footers] = xmalloc(64 + strlen(view_def));
		snprintf(footers[count_footers], 64 + strlen(view_def),
719
				 _("View definition: %s"), view_def);
720 721 722 723 724 725 726 727 728 729 730 731 732 733 734 735 736 737 738 739
		count_footers++;

		/* print rules */
		for (i = 0; i < rule_count; i++)
		{
			char	   *s = _("Rules");

			if (i == 0)
				snprintf(buf, sizeof(buf), "%s: %s", s, PQgetvalue(result, i, 0));
			else
				snprintf(buf, sizeof(buf), "%*s  %s", (int) strlen(s), "", PQgetvalue(result, i, 0));
			if (i < rule_count - 1)
				strcat(buf, ",");

			footers[count_footers++] = xstrdup(buf);
		}
		PQclear(result);

		footers[count_footers] = NULL;

Bruce Momjian's avatar
Bruce Momjian committed
740
	}
741
	else if (tableinfo.relkind == 'r')
Bruce Momjian's avatar
Bruce Momjian committed
742
	{
743
		/* Footer information about a table */
744 745 746
		PGresult   *result1 = NULL,
				   *result2 = NULL,
				   *result3 = NULL,
747
				   *result4 = NULL;
748
		int			index_count = 0,
749 750 751
					constr_count = 0,
					rule_count = 0,
					trigger_count = 0;
752
		int			count_footers = 0;
753

754
		/* count indexes */
755 756
		if (!error && tableinfo.hasindex)
		{
757 758 759
			sprintf(buf, "SELECT c2.relname, i.indisprimary, i.indisunique,\n"
					"SUBSTR(pg_get_indexdef(i.indexrelid),\n"
					"POSITION('USING ' IN pg_get_indexdef(i.indexrelid))+5)\n"
760 761
					"FROM pg_class c, pg_class c2, pg_index i\n"
					"WHERE c.relname = '%s' AND c.oid = i.indrelid AND i.indexrelid = c2.oid\n"
762
					"ORDER BY i.indisprimary DESC, i.indisunique DESC, c2.relname",
763 764 765 766 767 768 769 770 771 772 773
					name);
			result1 = PSQLexec(buf);
			if (!result1)
				error = true;
			else
				index_count = PQntuples(result1);
		}

		/* count table (and column) constraints */
		if (!error && tableinfo.checks)
		{
774
			sprintf(buf, "SELECT rcsrc, rcname\n"
775 776 777 778 779 780 781 782 783 784 785 786 787 788 789 790 791 792 793 794 795 796 797 798 799 800
					"FROM pg_relcheck r, pg_class c\n"
					"WHERE c.relname='%s' AND c.oid = r.rcrelid",
					name);
			result2 = PSQLexec(buf);
			if (!result2)
				error = true;
			else
				constr_count = PQntuples(result2);
		}

		/* count rules */
		if (!error && tableinfo.hasrules)
		{
			sprintf(buf,
					"SELECT r.rulename\n"
					"FROM pg_rewrite r, pg_class c\n"
					"WHERE c.relname='%s' AND c.oid = r.ev_class",
					name);
			result3 = PSQLexec(buf);
			if (!result3)
				error = true;
			else
				rule_count = PQntuples(result3);
		}

		/* count triggers */
801
		if (!error && tableinfo.triggers)
802 803 804 805 806 807 808 809 810 811 812 813 814
		{
			sprintf(buf,
					"SELECT t.tgname\n"
					"FROM pg_trigger t, pg_class c\n"
					"WHERE c.relname='%s' AND c.oid = t.tgrelid",
					name);
			result4 = PSQLexec(buf);
			if (!result4)
				error = true;
			else
				trigger_count = PQntuples(result4);
		}

815
		footers = xmalloc((index_count + constr_count + rule_count + trigger_count + 1)
816
						  * sizeof(*footers));
817

818
		/* print indexes */
819 820
		for (i = 0; i < index_count; i++)
		{
821
			char	   *s = _("Indexes");
822
	
823 824 825
			if (i == 0)
				snprintf(buf, sizeof(buf), "%s: %s", s, PQgetvalue(result1, i, 0));
			else
826
				snprintf(buf, sizeof(buf), "%*s  %s", (int) strlen(s), "", PQgetvalue(result1, i, 0));
827

828 829 830
			/* Label as primary key or unique (but not both) */
			strcat(buf, strcmp(PQgetvalue(result1,i,1),"t") == 0 ? 
				   _(" primary key") : strcmp(PQgetvalue(result1,i,2),"t") == 0 ? _(" unique") : "");
831

832 833
			/* Everything after "USING" is echoed verbatim */
			strcat(buf, PQgetvalue(result1,i,3));
834

835
			if (i < index_count - 1)
836 837 838 839 840 841 842 843 844
				strcat(buf, ",");

			footers[count_footers++] = xstrdup(buf);
		}


		/* print constraints */
		for (i = 0; i < constr_count; i++)
		{
845
			char	   *s = _("Check constraints");
846 847 848

			if (i == 0)
				snprintf(buf, sizeof(buf), _("%s: \"%s\" %s"), s,
849
				   PQgetvalue(result2, i, 1), PQgetvalue(result2, i, 0));
850
			else
851 852
				snprintf(buf, sizeof(buf), _("%*s  \"%s\" %s"), (int) strlen(s), "",
				   PQgetvalue(result2, i, 1), PQgetvalue(result2, i, 0));
853 854 855 856 857 858
			footers[count_footers++] = xstrdup(buf);
		}

		/* print rules */
		for (i = 0; i < rule_count; i++)
		{
859
			char	   *s = _("Rules");
860 861 862 863

			if (i == 0)
				snprintf(buf, sizeof(buf), "%s: %s", s, PQgetvalue(result3, i, 0));
			else
864
				snprintf(buf, sizeof(buf), "%*s  %s", (int) strlen(s), "", PQgetvalue(result3, i, 0));
865 866 867 868 869 870 871 872 873
			if (i < rule_count - 1)
				strcat(buf, ",");

			footers[count_footers++] = xstrdup(buf);
		}

		/* print triggers */
		for (i = 0; i < trigger_count; i++)
		{
874
			char	   *s = _("Triggers");
875 876 877 878

			if (i == 0)
				snprintf(buf, sizeof(buf), "%s: %s", s, PQgetvalue(result4, i, 0));
			else
879
				snprintf(buf, sizeof(buf), "%*s  %s", (int) strlen(s), "", PQgetvalue(result4, i, 0));
880 881 882 883 884 885 886 887 888 889 890 891 892
			if (i < trigger_count - 1)
				strcat(buf, ",");

			footers[count_footers++] = xstrdup(buf);
		}

		/* end of list marker */
		footers[count_footers] = NULL;

		PQclear(result1);
		PQclear(result2);
		PQclear(result3);
		PQclear(result4);
893
	}
894

895 896 897 898
	if (!error)
		printTable(title, headers,
				   (const char **) cells, (const char **) footers,
				   "llll", &myopt, pset.queryFout);
899

Bruce Momjian's avatar
Bruce Momjian committed
900 901
	/* clean up */
	free(title);
902

Bruce Momjian's avatar
Bruce Momjian committed
903 904
	for (i = 0; i < PQntuples(res); i++)
	{
905
		if (tableinfo.relkind == 'r' || tableinfo.relkind == 'v')
906
			free(cells[i * cols + 2]);
Bruce Momjian's avatar
Bruce Momjian committed
907 908
	}
	free(cells);
909

Bruce Momjian's avatar
Bruce Momjian committed
910 911 912
	for (ptr = footers; footers && *ptr; ptr++)
		free(*ptr);
	free(footers);
913

Bruce Momjian's avatar
Bruce Momjian committed
914
	PQclear(res);
915

916
	return !error;
917 918 919
}


920 921 922 923 924 925 926 927
/*
 * \du [user]
 *
 * Describes users, possibly based on a simplistic prefix search on the
 * argument.
 */

bool
928
describeUsers(const char *name)
929 930 931
{
	char		buf[384 + REGEXP_CUTOFF];
	PGresult   *res;
932
	printQueryOpt myopt = pset.popt;
933

934 935 936
	snprintf(buf, sizeof(buf),
			 "SELECT u.usename AS \"%s\",\n"
			 "  u.usesysid AS \"%s\",\n"
937
	 "  CASE WHEN u.usesuper AND u.usecreatedb THEN CAST('%s' AS text)\n"
938 939 940 941 942 943 944 945
			 "       WHEN u.usesuper THEN CAST('%s' AS text)\n"
			 "       WHEN u.usecreatedb THEN CAST('%s' AS text)\n"
			 "       ELSE CAST('' AS text)\n"
			 "  END AS \"%s\"\n"
			 "FROM pg_user u\n",
			 _("User name"), _("User ID"),
			 _("superuser, create database"),
			 _("superuser"), _("create database"),
946
			 _("Attributes"));
947 948
	if (name)
	{
949
		strcat(buf, "WHERE u.usename ~ '^");
950 951 952
		strncat(buf, name, REGEXP_CUTOFF);
		strcat(buf, "'\n");
	}
953
	strcat(buf, "ORDER BY 1;");
954 955 956 957 958

	res = PSQLexec(buf);
	if (!res)
		return false;

959 960
	myopt.nullPrint = NULL;
	myopt.title = _("List of database users");
961

962
	printQuery(res, &myopt, pset.queryFout);
963 964 965 966 967

	PQclear(res);
	return true;
}

968 969 970 971 972 973 974 975

/*
 * listTables()
 *
 * handler for \d, \dt, etc.
 *
 * The infotype is an array of characters, specifying what info is desired:
 * t - tables
976
 * i - indexes
977 978
 * v - views
 * s - sequences
979
 * S - systems tables (~ '^pg_')
980
 * (any order of the above is fine)
981 982 983 984
 *
 * Note: For some reason it always happens to people that their tables have owners
 * that are no longer in pg_user; consequently they wouldn't show up here. The code
 * tries to fix this the painful way, hopefully outer joins will be done sometime.
985 986
 */
bool
987
listTables(const char *infotype, const char *name, bool desc)
988
{
Bruce Momjian's avatar
Bruce Momjian committed
989
	bool		showTables = strchr(infotype, 't') != NULL;
990
	bool		showIndexes = strchr(infotype, 'i') != NULL;
Bruce Momjian's avatar
Bruce Momjian committed
991 992 993 994
	bool		showViews = strchr(infotype, 'v') != NULL;
	bool		showSeq = strchr(infotype, 's') != NULL;
	bool		showSystem = strchr(infotype, 'S') != NULL;

995
	char		buf[3072 + 8 * REGEXP_CUTOFF];
Bruce Momjian's avatar
Bruce Momjian committed
996
	PGresult   *res;
997
	printQueryOpt myopt = pset.popt;
Bruce Momjian's avatar
Bruce Momjian committed
998

999
	if (showSystem && !(showSeq || showIndexes || showViews || showTables))
1000 1001 1002
		showTables = showViews = showSeq = true;


1003
	buf[0] = '\0';
Bruce Momjian's avatar
Bruce Momjian committed
1004

1005 1006 1007 1008 1009 1010
	snprintf(buf, sizeof(buf),
			 "SELECT 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\"",
			 _("Name"), _("table"), _("view"), _("index"), _("sequence"),
			 _("special"), _("Type"), _("Owner"));
1011

1012 1013
	if (desc)
		snprintf(buf + strlen(buf), sizeof(buf) - strlen(buf),
1014
				 ",\n  obj_description(c.oid, 'pg_class') as \"%s\"",
1015
				 _("Description"));
1016 1017 1018 1019 1020 1021 1022 1023 1024 1025 1026 1027 1028
    if (showIndexes) {
		snprintf(buf + strlen(buf), sizeof(buf) - strlen(buf),
				 ",\n c2.relname as \"%s\"",
				 _("Table"));
		strcat(buf, "\nFROM pg_class c, pg_class c2, pg_index i, pg_user u\n"
			   "WHERE c.relowner = u.usesysid\n"
			   "AND i.indrelid = c2.oid AND i.indexrelid = c.oid\n");
	}
	else {
		strcat(buf, "\nFROM pg_class c, pg_user u\n"
			   "WHERE c.relowner = u.usesysid\n");
	}
	strcat(buf, "AND c.relkind IN (");
1029 1030
	if (showTables)
		strcat(buf, "'r',");
Bruce Momjian's avatar
Bruce Momjian committed
1031
	if (showViews)
1032 1033 1034 1035 1036
		strcat(buf, "'v',");
	if (showIndexes)
		strcat(buf, "'i',");
	if (showSeq)
		strcat(buf, "'S',");
Bruce Momjian's avatar
Bruce Momjian committed
1037
	if (showSystem && showTables)
1038
		strcat(buf, "'s',");
1039
	strcat(buf, "''");			/* dummy */
1040
	strcat(buf, ")\n");
Bruce Momjian's avatar
Bruce Momjian committed
1041

1042 1043 1044 1045
	if (showSystem)
		strcat(buf, "  AND c.relname ~ '^pg_'\n");
	else
		strcat(buf, "  AND c.relname !~ '^pg_'\n");
1046

1047 1048 1049 1050 1051
	if (name)
	{
		strcat(buf, "  AND c.relname ~ '^");
		strncat(buf, name, REGEXP_CUTOFF);
		strcat(buf, "'\n");
1052 1053
	}

1054
	strcat(buf, "ORDER BY 1;");
1055

1056
	res = PSQLexec(buf);
Bruce Momjian's avatar
Bruce Momjian committed
1057 1058
	if (!res)
		return false;
1059

1060
	if (PQntuples(res) == 0 && !QUIET())
1061 1062
	{
		if (name)
1063
			fprintf(pset.queryFout, _("No matching relations found.\n"));
1064
		else
1065
			fprintf(pset.queryFout, _("No relations found.\n"));
1066
	}
Bruce Momjian's avatar
Bruce Momjian committed
1067 1068 1069
	else
	{
		myopt.nullPrint = NULL;
1070
		myopt.title = _("List of relations");
1071

1072
		printQuery(res, &myopt, pset.queryFout);
Bruce Momjian's avatar
Bruce Momjian committed
1073
	}
1074

Bruce Momjian's avatar
Bruce Momjian committed
1075 1076
	PQclear(res);
	return true;
1077
}
1078 1079 1080 1081 1082 1083 1084 1085 1086 1087 1088 1089 1090 1091 1092 1093 1094

/*
 * \dD [domain]
 *
 * Describes domains, possibly based on a simplistic prefix search on the
 * argument.
 */

bool
listDomains(const char *name)
{
	char		buf[512 + REGEXP_CUTOFF];
	PGresult   *res;
	printQueryOpt myopt = pset.popt;

	snprintf(buf, sizeof(buf),
		 "SELECT t.typname as \"%s\",\n"
Tom Lane's avatar
Tom Lane committed
1095
		 "       format_type( t.typbasetype, t.typtypmod) as \"%s\",\n"
1096 1097 1098 1099 1100 1101 1102 1103 1104 1105 1106 1107 1108 1109 1110 1111 1112 1113 1114 1115 1116 1117 1118 1119 1120 1121 1122 1123 1124 1125
		 "       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_type t\n"
		 "WHERE t.typtype = 'd'\n",
		 _("Name"),
		 _("Type"),
		 _("Modifier"));
	if (name)
	{
		strcat(buf, "AND t.typname ~ '^");
		strncat(buf, name, REGEXP_CUTOFF);
		strcat(buf, "'\n");
	}
	strcat(buf, "ORDER BY 1;");

	res = PSQLexec(buf);
	if (!res)
		return false;

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

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

	PQclear(res);
	return true;
}