describe.c

PostgreSQL 8.1.4的源码 适用于Linux下的开源数据库系统

			result = PSQLexec(buf.data, false);
			if (!result)
				goto error_return;
			else
				rule_count = PQntuples(result);
		}

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

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

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

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

				footers[count_footers++] = pg_strdup(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, i.indisclustered, "
							  "pg_catalog.pg_get_indexdef(i.indexrelid, 0, true), c2.reltablespace\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 r.conname, "
							  "pg_catalog.pg_get_constraintdef(r.oid, true)\n"
							  "FROM pg_catalog.pg_constraint r\n"
					"WHERE r.conrelid = '%s' AND r.contype = 'c' ORDER BY 1",
							  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, true))\n"
							  "FROM pg_catalog.pg_rewrite r\n"
							  "WHERE r.ev_class = '%s' ORDER BY 1",
							  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'))"
							  "   ORDER BY 1",
							  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, true) as condef\n"
							  "FROM pg_catalog.pg_constraint r\n"
					"WHERE r.conrelid = '%s' AND r.contype = 'f' ORDER BY 1",
							  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 = pg_malloc_zero((index_count + check_count + rule_count + trigger_count + foreignkey_count + inherits_count + 7 + 1)
								 * sizeof(*footers));

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

				/* 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, 4);
				usingpos = strstr(indexdef, " USING ");
				if (usingpos)
					indexdef = usingpos + 7;

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

				if (strcmp(PQgetvalue(result1, i, 3), "t") == 0)
					appendPQExpBuffer(&buf, " CLUSTER");

				/* Print tablespace of the index on the same line */
				count_footers += 1;
				initPQExpBuffer(&tmpbuf);
				if (add_tablespace_footer('i',
										  atooid(PQgetvalue(result1, i, 5)),
									 footers, &count_footers, tmpbuf, false))
				{
					appendPQExpBuffer(&buf, ", ");
					appendPQExpBuffer(&buf, tmpbuf.data);

					count_footers -= 2;
				}
				else
					count_footers -= 1;
				termPQExpBuffer(&tmpbuf);

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

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

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

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

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

		/* print rules */
		if (rule_count > 0)
		{
			printfPQExpBuffer(&buf, _("Rules:"));
			footers[count_footers++] = pg_strdup(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++] = pg_strdup(buf.data);
			}
		}

		/* print triggers */
		if (trigger_count > 0)
		{
			printfPQExpBuffer(&buf, _("Triggers:"));
			footers[count_footers++] = pg_strdup(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++] = pg_strdup(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++] = pg_strdup(buf.data);
		}

		if (verbose)
		{
			char	   *s = _("Has OIDs");

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

		add_tablespace_footer(tableinfo.relkind, tableinfo.tablespace,
							  footers, &count_footers, buf, true);
		/* 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, pset.logfile);

	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;
}


/*
 * Return true if the relation uses non default tablespace;
 * otherwise return false
 */
static bool
add_tablespace_footer(char relkind, Oid tablespace, char **footers,
					  int *count, PQExpBufferData buf, bool newline)
{
	/* relkinds for which we support tablespaces */
	if (relkind == 'r' || relkind == 'i')
	{
		/*
		 * We ignore the database default tablespace so that users not using
		 * tablespaces don't need to know about them.
		 */
		if (tablespace != 0)
		{
			PGresult   *result1 = NULL;

			printfPQExpBuffer(&buf, "SELECT spcname FROM pg_tablespace \n"
							  "WHERE oid = '%u';", tablespace);
			result1 = PSQLexec(buf.data, false);
			if (!result1)
				return false;
			/* Should always be the case, but.... */
			if (PQntuples(result1) > 0)
			{
				printfPQExpBuffer(&buf,
				  newline ? _("Tablespace: \"%s\"") : _("tablespace \"%s\""),
								  PQgetvalue(result1, 0, 0));

				footers[(*count)++] = pg_strdup(buf.data);
			}
			PQclear(result1);

			return true;
		}
	}

	return false;
}

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

	initPQExpBuffer(&buf);

	printfPQExpBuffer(&buf,
					  "SELECT r.rolname AS \"%s\",\n"
				"  CASE WHEN r.rolsuper THEN '%s' ELSE '%s' END AS \"%s\",\n"
		   "  CASE WHEN r.rolcreaterole THEN '%s' ELSE '%s' END AS \"%s\",\n"
			 "  CASE WHEN r.rolcreatedb THEN '%s' ELSE '%s' END AS \"%s\",\n"
		"  CASE WHEN r.rolconnlimit < 0 THEN CAST('%s' AS pg_catalog.text)\n"
					  "       ELSE CAST(r.rolconnlimit AS pg_catalog.text)\n"
					  "  END AS \"%s\", \n"
					  "  ARRAY(SELECT b.rolname FROM pg_catalog.pg_auth_members m JOIN pg_catalog.pg_roles b ON (m.roleid = b.oid) WHERE m.member = r.oid) as \"%s\"\n"
					  "FROM pg_catalog.pg_roles r\n",
					  _("Role name"),
					  _("yes"), _("no"), _("Superuser"),
					  _("yes"), _("no"), _("Create role"),
					  _("yes"), _("no"), _("Create DB"),
					  _("no limit"), _("Connections"),
					  _("Member of"));

	processNamePattern(&buf, pattern, false, false,
					   NULL, "r.rolname", 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 roles");

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

	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;