describe.c 27.5 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.50 2002/04/19 23:13:54 tgl 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

	/*
	 * There are two kinds of aggregates: ones that work on particular
46
	 * types and ones that work on all (denoted by input type = 0)
Bruce Momjian's avatar
Bruce Momjian committed
47
	 */
48
	snprintf(buf, sizeof(buf),
49 50
			 "SELECT p.proname AS \"%s\",\n"
			 "  CASE p.proargtypes[0]\n"
51
			 "    WHEN 0 THEN CAST('%s' AS text)\n"
52
			 "    ELSE format_type(p.proargtypes[0], NULL)\n"
53
			 "  END AS \"%s\",\n"
54 55 56
			 "  obj_description(p.oid, 'pg_proc') as \"%s\"\n"
			 "FROM pg_proc p\n"
			 "WHERE p.proisagg\n",
57
			 _("Name"), _("(all types)"),
58
			 _("Data type"), _("Description"));
59

Bruce Momjian's avatar
Bruce Momjian committed
60 61
	if (name)
	{
62
		strcat(buf, "  AND p.proname ~ '^");
63 64
		strncat(buf, name, REGEXP_CUTOFF);
		strcat(buf, "'\n");
Bruce Momjian's avatar
Bruce Momjian committed
65 66
	}

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

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

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

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

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


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

	/*
	 * we skip in/out funcs by excluding functions that take some
	 * arguments, but have no types defined for those arguments
	 */
97 98 99 100 101
	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"),
102
			 _("Argument data types"));
103

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

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

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

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

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

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

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



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

157 158
	snprintf(buf, sizeof(buf),
			 "SELECT format_type(t.oid, NULL) AS \"%s\",\n",
159
			 _("Name"));
160
	if (verbose)
161 162 163 164 165 166
		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",
167 168
				 _("Internal name"), _("Size"));
	snprintf(buf + strlen(buf), sizeof(buf) - strlen(buf),
169
			 "  obj_description(t.oid, 'pg_type') as \"%s\"\n",
170
			 _("Description"));
171 172 173 174 175

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

	if (name)
	{
180 181 182 183
		/* 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 ~ '^");
184
		strncat(buf, name, REGEXP_CUTOFF);
185
		strcat(buf, "')\n");
Bruce Momjian's avatar
Bruce Momjian committed
186
	}
187
	strcat(buf, "ORDER BY 1;");
Bruce Momjian's avatar
Bruce Momjian committed
188

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

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

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

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



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

213 214 215 216
	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"
217 218 219
			 "  format_type(o.oprresult, NULL) AS \"%s\",\n"
			 "  obj_description(o.oprcode, 'pg_proc') AS \"%s\"\n"
			 "FROM pg_operator o\n",
220
			 _("Name"), _("Left arg type"), _("Right arg type"),
221
			 _("Result type"), _("Description"));
Bruce Momjian's avatar
Bruce Momjian committed
222 223
	if (name)
	{
224
		strcat(buf, "WHERE o.oprname = '");
225 226
		strncat(buf, name, REGEXP_CUTOFF);
		strcat(buf, "'\n");
Bruce Momjian's avatar
Bruce Momjian committed
227 228
	}

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

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

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

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

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


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

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

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

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

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

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


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

	/* Currently, we ignore indexes since they have no meaningful rights */
300 301 302 303 304 305 306
	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
307 308
	if (name)
	{
309 310 311
		strcat(buf, "  AND relname ~ '^");
		strncat(buf, name, REGEXP_CUTOFF);
		strcat(buf, "'\n");
Bruce Momjian's avatar
Bruce Momjian committed
312
	}
313
	strcat(buf, "ORDER BY 1;");
314

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

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

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

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



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

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

350
	/* Aggregate descriptions */
351 352 353 354
			 "  SELECT p.oid as oid, p.tableoid as tableoid,\n"
	  "  CAST(p.proname AS text) as name, CAST('%s' AS text) as object\n"
			 "  FROM pg_proc p\n"
		"  WHERE p.proisagg\n"
355

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

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

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

376
	/* Relation (tables, views, indexes, sequences) descriptions */
377
			 "UNION ALL\n"
378 379
			 "  SELECT c.oid as oid, c.tableoid as tableoid,\n"
			 "  CAST(c.relname AS text) as name,\n"
380 381 382 383 384
			 "  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"

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

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

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

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

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


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

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

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

	PQclear(res);
	return true;
429 430 431 432 433 434 435
}



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

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

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


bool
458
describeTableDetails(const char *name, bool desc)
459
{
460
	char		buf[512 + INDEX_MAX_KEYS * NAMEDATALEN];
461
	PGresult   *res = NULL;
462
	printTableOpt myopt = pset.popt.topt;
Bruce Momjian's avatar
Bruce Momjian committed
463
	int			i;
464
	const char *view_def = NULL;
Bruce Momjian's avatar
Bruce Momjian committed
465
	const char *headers[5];
Bruce Momjian's avatar
Bruce Momjian committed
466 467 468 469 470
	char	  **cells = NULL;
	char	   *title = NULL;
	char	  **footers = NULL;
	char	  **ptr;
	unsigned int cols;
471 472 473 474 475 476 477 478 479 480 481 482 483 484
	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
485

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

Bruce Momjian's avatar
Bruce Momjian committed
491
	/* Get general table info */
492 493 494 495 496 497 498
	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
499 500 501 502

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

509 510 511 512 513 514 515
	/* 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
516 517


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

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

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

534
	headers[cols] = NULL;
535 536


537 538 539 540 541 542
	/* 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");
543
	if (desc)
544
		strcat(buf, ", col_description(a.attrelid, a.attnum)");
545 546 547 548
	strcat(buf, "\nFROM pg_class c, pg_attribute a");
	if (tableinfo.relkind == 'i')
		strcat(buf, ", pg_index i");
	strcat(buf, "\nWHERE c.relname = '");
549
	strncat(buf, name, NAMEDATALEN);
550 551 552 553
	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");
554

555
	res = PSQLexec(buf);
556 557 558 559
	if (!res)
		return false;

	/* Check if table is a view */
560 561 562 563 564 565 566 567 568 569 570 571 572 573 574 575 576
	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);
	}
577 578 579 580


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

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

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

			/* handle "default" here */
602
			if (strcmp(PQgetvalue(res, i, 3), "t") == 0)
603 604 605 606 607
			{
				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",
608
						name, PQgetvalue(res, i, 4));
609 610 611 612 613 614 615 616 617 618 619 620 621 622 623 624 625 626

				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
627
		/* Description */
628
		if (desc)
629
			cells[i * cols + cols - 1] = PQgetvalue(res, i, 5);
Bruce Momjian's avatar
Bruce Momjian committed
630
	}
631

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

	/* Make footers */
660 661
	if (tableinfo.relkind == 'i')
	{
662
		/* Footer information about an index */
663
		PGresult   *result;
664 665 666 667 668
		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",
669 670 671
				name);

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

682
			footers = xmalloc(2 * sizeof(*footers));
Peter Eisentraut's avatar
Peter Eisentraut committed
683
			/* XXX This construction is poorly internationalized. */
684 685 686
			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 ") : 
687
					 strcmp(indisunique, "t") == 0 ? _("unique ") : "",
688 689 690
					 indamname, _("table"), indtable, 
					 strlen(indpred) ? " WHERE " : "",indpred);
			footers[1] = NULL;
691
		}
692 693

		PQclear(result);
694
	}
695
	else if (view_def)
Bruce Momjian's avatar
Bruce Momjian committed
696
	{
697 698 699 700 701 702 703 704 705 706
		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"
707 708
					"WHERE c.relname = '%s' AND c.oid = r.ev_class\n"
					"AND r.rulename != '_RETURN'",
709 710 711 712 713 714 715 716
					name);
			result = PSQLexec(buf);
			if (!result)
				error = true;
			else
				rule_count = PQntuples(result);
		}

717
		/* Footer information about a view */
718 719 720
		footers = xmalloc((rule_count + 2) * sizeof(*footers));
		footers[count_footers] = xmalloc(64 + strlen(view_def));
		snprintf(footers[count_footers], 64 + strlen(view_def),
721
				 _("View definition: %s"), view_def);
722 723 724 725 726 727 728 729 730 731 732 733 734 735 736 737 738 739 740 741
		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
742
	}
743
	else if (tableinfo.relkind == 'r')
Bruce Momjian's avatar
Bruce Momjian committed
744
	{
745
		/* Footer information about a table */
746 747 748
		PGresult   *result1 = NULL,
				   *result2 = NULL,
				   *result3 = NULL,
749
				   *result4 = NULL;
750
		int			index_count = 0,
751 752 753
					constr_count = 0,
					rule_count = 0,
					trigger_count = 0;
754
		int			count_footers = 0;
755

756
		/* count indexes */
757 758
		if (!error && tableinfo.hasindex)
		{
759 760 761
			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"
762 763
					"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"
764
					"ORDER BY i.indisprimary DESC, i.indisunique DESC, c2.relname",
765 766 767 768 769 770 771 772 773 774 775
					name);
			result1 = PSQLexec(buf);
			if (!result1)
				error = true;
			else
				index_count = PQntuples(result1);
		}

		/* count table (and column) constraints */
		if (!error && tableinfo.checks)
		{
776
			sprintf(buf, "SELECT rcsrc, rcname\n"
777 778 779 780 781 782 783 784 785 786 787 788 789 790 791 792 793 794 795 796 797 798 799 800 801 802
					"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 */
803
		if (!error && tableinfo.triggers)
804 805 806 807 808 809 810 811 812 813 814 815 816
		{
			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);
		}

817
		footers = xmalloc((index_count + constr_count + rule_count + trigger_count + 1)
818
						  * sizeof(*footers));
819

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

830 831 832
			/* 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") : "");
833

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

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

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


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

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

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

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

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

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

			if (i == 0)
				snprintf(buf, sizeof(buf), "%s: %s", s, PQgetvalue(result4, i, 0));
			else
881
				snprintf(buf, sizeof(buf), "%*s  %s", (int) strlen(s), "", PQgetvalue(result4, i, 0));
882 883 884 885 886 887 888 889 890 891 892 893 894
			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);
895
	}
896

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

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

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

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

Bruce Momjian's avatar
Bruce Momjian committed
916
	PQclear(res);
917

918
	return !error;
919 920 921
}


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

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

936 937 938
	snprintf(buf, sizeof(buf),
			 "SELECT u.usename AS \"%s\",\n"
			 "  u.usesysid AS \"%s\",\n"
939
	 "  CASE WHEN u.usesuper AND u.usecreatedb THEN CAST('%s' AS text)\n"
940 941 942 943 944 945 946 947
			 "       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"),
948
			 _("Attributes"));
949 950
	if (name)
	{
951
		strcat(buf, "WHERE u.usename ~ '^");
952 953 954
		strncat(buf, name, REGEXP_CUTOFF);
		strcat(buf, "'\n");
	}
955
	strcat(buf, "ORDER BY 1;");
956 957 958 959 960

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

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

964
	printQuery(res, &myopt, pset.queryFout);
965 966 967 968 969

	PQclear(res);
	return true;
}

970 971 972 973 974 975 976 977

/*
 * listTables()
 *
 * handler for \d, \dt, etc.
 *
 * The infotype is an array of characters, specifying what info is desired:
 * t - tables
978
 * i - indexes
979 980
 * v - views
 * s - sequences
981
 * S - systems tables (~ '^pg_')
982
 * (any order of the above is fine)
983 984 985 986
 *
 * 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.
987 988
 */
bool
989
listTables(const char *infotype, const char *name, bool desc)
990
{
Bruce Momjian's avatar
Bruce Momjian committed
991
	bool		showTables = strchr(infotype, 't') != NULL;
992
	bool		showIndexes = strchr(infotype, 'i') != NULL;
Bruce Momjian's avatar
Bruce Momjian committed
993 994 995 996
	bool		showViews = strchr(infotype, 'v') != NULL;
	bool		showSeq = strchr(infotype, 's') != NULL;
	bool		showSystem = strchr(infotype, 'S') != NULL;

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

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


1005
	buf[0] = '\0';
Bruce Momjian's avatar
Bruce Momjian committed
1006

1007 1008 1009 1010 1011 1012
	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"));
1013

1014 1015
	if (desc)
		snprintf(buf + strlen(buf), sizeof(buf) - strlen(buf),
1016
				 ",\n  obj_description(c.oid, 'pg_class') as \"%s\"",
1017
				 _("Description"));
1018 1019 1020 1021 1022 1023 1024 1025 1026 1027 1028 1029 1030
    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 (");
1031 1032
	if (showTables)
		strcat(buf, "'r',");
Bruce Momjian's avatar
Bruce Momjian committed
1033
	if (showViews)
1034 1035 1036 1037 1038
		strcat(buf, "'v',");
	if (showIndexes)
		strcat(buf, "'i',");
	if (showSeq)
		strcat(buf, "'S',");
Bruce Momjian's avatar
Bruce Momjian committed
1039
	if (showSystem && showTables)
1040
		strcat(buf, "'s',");
1041
	strcat(buf, "''");			/* dummy */
1042
	strcat(buf, ")\n");
Bruce Momjian's avatar
Bruce Momjian committed
1043

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

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

1056
	strcat(buf, "ORDER BY 1;");
1057

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

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

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

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

/*
 * \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
1097
		 "       format_type( t.typbasetype, t.typtypmod) as \"%s\",\n"
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 1126 1127
		 "       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;
}