describe.c

PostgreSQL7.4.6 for Linux

			footers[1] = NULL;
		}

		PQclear(result);
	}
	else if (view_def)
	{
		PGresult   *result = NULL;
		int			rule_count = 0;
		int			count_footers = 0;

		/* count rules other than the view rule */
		if (tableinfo.hasrules)
		{
			printfPQExpBuffer(&buf,
							  "SELECT r.rulename\n"
							  "FROM pg_catalog.pg_rewrite r\n"
				   "WHERE r.ev_class = '%s' AND r.rulename != '_RETURN'",
							  oid);
			result = PSQLexec(buf.data, false);
			if (!result)
				goto error_return;
			else
				rule_count = PQntuples(result);
		}

		/* Footer information about a view */
		footers = xmalloczero((rule_count + 2) * sizeof(*footers));
		footers[count_footers] = xmalloc(64 + strlen(view_def));
		snprintf(footers[count_footers], 64 + strlen(view_def),
				 _("View definition:\n%s"), view_def);
		count_footers++;

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

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

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

		footers[count_footers] = NULL;

	}
	else if (tableinfo.relkind == 'r')
	{
		/* Footer information about a table */
		PGresult   *result1 = NULL,
				   *result2 = NULL,
				   *result3 = NULL,
				   *result4 = NULL,
				   *result5 = NULL,
				   *result6 = NULL;
		int			check_count = 0,
					index_count = 0,
					foreignkey_count = 0,
					rule_count = 0,
					trigger_count = 0,
					inherits_count = 0;
		int			count_footers = 0;

		/* count indexes */
		if (tableinfo.hasindex)
		{
			printfPQExpBuffer(&buf,
					 "SELECT c2.relname, i.indisprimary, i.indisunique, "
							  "pg_catalog.pg_get_indexdef(i.indexrelid)\n"
							  "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);
			result1 = PSQLexec(buf.data, false);
			if (!result1)
				goto error_return;
			else
				index_count = PQntuples(result1);
		}

		/* count table (and column) check constraints */
		if (tableinfo.checks)
		{
			printfPQExpBuffer(&buf,
							  "SELECT "
							  "pg_catalog.pg_get_constraintdef(r.oid, true), "
							  "conname\n"
							  "FROM pg_catalog.pg_constraint r\n"
							  "WHERE r.conrelid = '%s' AND r.contype = 'c'",
							  oid);
			result2 = PSQLexec(buf.data, false);
			if (!result2)
			{
				PQclear(result1);
				goto error_return;
			}
			else
				check_count = PQntuples(result2);
		}

		/* count rules */
		if (tableinfo.hasrules)
		{
			printfPQExpBuffer(&buf,
							  "SELECT r.rulename, trim(trailing ';' from pg_catalog.pg_get_ruledef(r.oid))\n"
							  "FROM pg_catalog.pg_rewrite r\n"
							  "WHERE r.ev_class = '%s'",
							  oid);
			result3 = PSQLexec(buf.data, false);
			if (!result3)
			{
				PQclear(result1);
				PQclear(result2);
				goto error_return;
			}
			else
				rule_count = PQntuples(result3);
		}

		/* count triggers (but ignore foreign-key triggers) */
		if (tableinfo.triggers)
		{
			printfPQExpBuffer(&buf,
				 "SELECT t.tgname, pg_catalog.pg_get_triggerdef(t.oid)\n"
							  "FROM pg_catalog.pg_trigger t\n"
							  "WHERE t.tgrelid = '%s' "
							  "AND (not tgisconstraint "
							  " 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);
			result4 = PSQLexec(buf.data, false);
			if (!result4)
			{
				PQclear(result1);
				PQclear(result2);
				PQclear(result3);
				goto error_return;
			}
			else
				trigger_count = PQntuples(result4);
		}

		/* count foreign-key constraints (there are none if no triggers) */
		if (tableinfo.triggers)
		{
			printfPQExpBuffer(&buf,
							  "SELECT conname,\n"
					 "  pg_catalog.pg_get_constraintdef(oid) as condef\n"
							  "FROM pg_catalog.pg_constraint r\n"
						   "WHERE r.conrelid = '%s' AND r.contype = 'f'",
							  oid);
			result5 = PSQLexec(buf.data, false);
			if (!result5)
			{
				PQclear(result1);
				PQclear(result2);
				PQclear(result3);
				PQclear(result4);
				goto error_return;
			}
			else
				foreignkey_count = PQntuples(result5);
		}

		/* 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);

		footers = xmalloczero((index_count + check_count + rule_count + trigger_count + foreignkey_count + inherits_count + 6)
							  * sizeof(*footers));

		/* print indexes */
		if (index_count > 0)
		{
			printfPQExpBuffer(&buf, _("Indexes:"));
			footers[count_footers++] = xstrdup(buf.data);
			for (i = 0; i < index_count; i++)
			{
				const char *indexdef;
				const char *usingpos;

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

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

				/* Everything after "USING" is echoed verbatim */
				indexdef = PQgetvalue(result1, i, 3);
				usingpos = strstr(indexdef, " USING ");
				if (usingpos)
					indexdef = usingpos + 7;

				appendPQExpBuffer(&buf, " %s", indexdef);

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

		/* print check constraints */
		if (check_count > 0)
		{
			printfPQExpBuffer(&buf, _("Check constraints:"));
			footers[count_footers++] = xstrdup(buf.data);
			for (i = 0; i < check_count; i++)
			{
				printfPQExpBuffer(&buf, _("    \"%s\" %s"),
								  PQgetvalue(result2, i, 1),
								  PQgetvalue(result2, i, 0));

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

		/* print foreign key constraints */
		if (foreignkey_count > 0)
		{
			printfPQExpBuffer(&buf, _("Foreign-key constraints:"));
			footers[count_footers++] = xstrdup(buf.data);
			for (i = 0; i < foreignkey_count; i++)
			{
				printfPQExpBuffer(&buf, _("    \"%s\" %s"),
								  PQgetvalue(result5, i, 0),
								  PQgetvalue(result5, i, 1));

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

		/* print rules */
		if (rule_count > 0)
		{
			printfPQExpBuffer(&buf, _("Rules:"));
			footers[count_footers++] = xstrdup(buf.data);
			for (i = 0; i < rule_count; i++)
			{
				const char *ruledef;

				/* Everything after "CREATE RULE" is echoed verbatim */
				ruledef = PQgetvalue(result3, i, 1);
				ruledef += 12;

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

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

		/* print triggers */
		if (trigger_count > 0)
		{
			printfPQExpBuffer(&buf, _("Triggers:"));
			footers[count_footers++] = xstrdup(buf.data);
			for (i = 0; i < trigger_count; i++)
			{
				const char *tgdef;
				const char *usingpos;

				/* Everything after "TRIGGER" is echoed verbatim */
				tgdef = PQgetvalue(result4, i, 1);
				usingpos = strstr(tgdef, " TRIGGER ");
				if (usingpos)
					tgdef = usingpos + 9;

				printfPQExpBuffer(&buf, "    %s", tgdef);

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

		/* 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, ",");

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

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

		PQclear(result1);
		PQclear(result2);
		PQclear(result3);
		PQclear(result4);
		PQclear(result5);
		PQclear(result6);
	}

	printTable(title.data, headers,
			   (const char **) cells, (const char **) footers,
			   "llll", &myopt, pset.queryFout);

	retval = true;

error_return:

	/* clean up */
	termPQExpBuffer(&buf);
	termPQExpBuffer(&title);
	termPQExpBuffer(&tmpbuf);

	if (cells)
	{
		for (i = 0; i < numrows; i++)
		{
			if (show_modifiers)
				free(cells[i * cols + 2]);
		}
		free(cells);
	}

	if (footers)
	{
		for (ptr = footers; *ptr; ptr++)
			free(*ptr);
		free(footers);
	}

	if (view_def)
		free(view_def);

	if (res)
		PQclear(res);

	return retval;
}


/*
 * \du
 *
 * Describes users.  Any schema portion of the pattern is ignored.
 */
bool
describeUsers(const char *pattern)
{
	PQExpBufferData buf;
	PGresult   *res;
	printQueryOpt myopt = pset.popt;

	initPQExpBuffer(&buf);

	printfPQExpBuffer(&buf,
					  "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"
					  "  END AS \"%s\"\n"
					  "FROM pg_catalog.pg_user u\n",
					  _("User name"), _("User ID"),
					  _("superuser, create database"),
					  _("superuser"), _("create database"),
					  _("Attributes"));

	processNamePattern(&buf, pattern, false, false,
					   NULL, "u.usename", 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 database users");

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

	PQclear(res);
	return true;
}


/*
 * listTables()
 *
 * handler for \d, \dt, etc.
 *
 * tabtypes is an array of characters, specifying what info is desired:
 * t - tables
 * i - indexes
 * v - views
 * s - sequences
 * S - system tables (pg_catalog)
 * (any order of the above is fine)
 */
bool
listTables(const char *tabtypes, const char *pattern, bool verbose)
{
	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;

	PQExpBufferData buf;
	PGresult   *res;
	printQueryOpt myopt = pset.popt;

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

	initPQExpBuffer(&buf);

	printfPQExpBuffer(&buf,
					  "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"));

	if (verbose)
		appendPQExpBuffer(&buf,
		  ",\n  pg_catalog.obj_description(c.oid, 'pg_class') as \"%s\"",
						  _("Description"));

	if (showIndexes)