describe.c

PostgreSQL7.4.6 for Linux

		appendPQExpBuffer(&buf,
						  ",\n c2.relname as \"%s\""
						  "\nFROM pg_catalog.pg_class c"
			  "\n     JOIN pg_catalog.pg_index i ON i.indexrelid = c.oid"
			  "\n     JOIN pg_catalog.pg_class c2 ON i.indrelid = c2.oid"
		"\n     LEFT JOIN pg_catalog.pg_user u ON u.usesysid = c.relowner"
						  "\n     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace\n",
						  _("Table"));
	else
		appendPQExpBuffer(&buf,
						  "\nFROM pg_catalog.pg_class c"
		"\n     LEFT JOIN pg_catalog.pg_user u ON u.usesysid = c.relowner"
						  "\n     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace\n");

	appendPQExpBuffer(&buf, "WHERE c.relkind IN (");
	if (showTables)
		appendPQExpBuffer(&buf, "'r',");
	if (showViews)
		appendPQExpBuffer(&buf, "'v',");
	if (showIndexes)
		appendPQExpBuffer(&buf, "'i',");
	if (showSeq)
		appendPQExpBuffer(&buf, "'S',");
	if (showSystem && showTables)
		appendPQExpBuffer(&buf, "'s',");
	appendPQExpBuffer(&buf, "''");		/* dummy */
	appendPQExpBuffer(&buf, ")\n");

	/*
	 * If showSystem is specified, show only system objects (those in
	 * pg_catalog).  Otherwise, suppress system objects, including those
	 * in pg_catalog and pg_toast.	(We don't want to hide temp tables
	 * though.)
	 */
	if (showSystem)
		appendPQExpBuffer(&buf, "      AND n.nspname = 'pg_catalog'\n");
	else
		appendPQExpBuffer(&buf, "      AND n.nspname NOT IN ('pg_catalog', 'pg_toast')\n");

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

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

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

	if (PQntuples(res) == 0 && !QUIET())
	{
		if (pattern)
			fprintf(pset.queryFout, _("No matching relations found.\n"));
		else
			fprintf(pset.queryFout, _("No relations found.\n"));
	}
	else
	{
		myopt.nullPrint = NULL;
		myopt.title = _("List of relations");

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

	PQclear(res);
	return true;
}


/*
 * \dD
 *
 * Describes domains.
 */
bool
listDomains(const char *pattern)
{
	PQExpBufferData buf;
	PGresult   *res;
	printQueryOpt myopt = pset.popt;

	initPQExpBuffer(&buf);

	printfPQExpBuffer(&buf,
					  "SELECT n.nspname as \"%s\",\n"
					  "       t.typname as \"%s\",\n"
					  "       pg_catalog.format_type(t.typbasetype, t.typtypmod) as \"%s\",\n"
					  "       CASE WHEN t.typnotnull AND t.typdefault IS NOT NULL THEN 'not null default '||t.typdefault\n"
					  "            WHEN t.typnotnull AND t.typdefault IS NULL THEN 'not null'\n"
					  "            WHEN NOT t.typnotnull AND t.typdefault IS NOT NULL THEN 'default '||t.typdefault\n"
					  "            ELSE ''\n"
					  "       END as \"%s\"\n"
					  "FROM pg_catalog.pg_type t\n"
	"     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = t.typnamespace\n"
					  "WHERE t.typtype = 'd'\n",
					  _("Schema"),
					  _("Name"),
					  _("Type"),
					  _("Modifier"));

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

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

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

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

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

	PQclear(res);
	return true;
}

/*
 * \dc
 *
 * Describes conversions.
 */
bool
listConversions(const char *pattern)
{
	PQExpBufferData buf;
	PGresult   *res;
	printQueryOpt myopt = pset.popt;

	initPQExpBuffer(&buf);

	printfPQExpBuffer(&buf,
					  "SELECT n.nspname AS \"%s\",\n"
					  "       c.conname AS \"%s\",\n"
	"       pg_catalog.pg_encoding_to_char(c.conforencoding) AS \"%s\",\n"
	"       pg_catalog.pg_encoding_to_char(c.contoencoding) AS \"%s\",\n"
					  "       CASE WHEN c.condefault THEN '%s'\n"
					  "       ELSE '%s' END AS \"%s\"\n"
		   "FROM pg_catalog.pg_conversion c, pg_catalog.pg_namespace n\n"
					  "WHERE n.oid = c.connamespace\n",
					  _("Schema"),
					  _("Name"),
					  _("Source"),
					  _("Destination"),
					  _("yes"),
					  _("no"),
					  _("Default?"));

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

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

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

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

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

	PQclear(res);
	return true;
}

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

	initPQExpBuffer(&buf);
/* NEED LEFT JOIN FOR BINARY CASTS */
	printfPQExpBuffer(&buf,
		   "SELECT pg_catalog.format_type(castsource, NULL) AS \"%s\",\n"
		   "       pg_catalog.format_type(casttarget, NULL) AS \"%s\",\n"
					  "       CASE WHEN castfunc = 0 THEN '%s'\n"
					  "            ELSE p.proname\n"
					  "       END as \"%s\",\n"
					  "       CASE WHEN c.castcontext = 'e' THEN '%s'\n"
					  "            WHEN c.castcontext = 'a' THEN '%s'\n"
					  "            ELSE '%s'\n"
					  "       END as \"%s\"\n"
			 "FROM pg_catalog.pg_cast c LEFT JOIN pg_catalog.pg_proc p\n"
					  "     ON c.castfunc = p.oid\n"
					  "ORDER BY 1, 2",
					  _("Source type"),
					  _("Target type"),
					  _("(binary compatible)"),
					  _("Function"),
					  _("no"),
					  _("in assignment"),
					  _("yes"),
					  _("Implicit?"));

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

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

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

	PQclear(res);
	return true;
}

/*
 * \dn
 *
 * Describes schemas (namespaces)
 */
bool
listSchemas(const char *pattern)
{
	PQExpBufferData buf;
	PGresult   *res;
	printQueryOpt myopt = pset.popt;

	initPQExpBuffer(&buf);
	printfPQExpBuffer(&buf,
					  "SELECT n.nspname AS \"%s\",\n"
					  "       u.usename AS \"%s\"\n"
		"FROM pg_catalog.pg_namespace n LEFT JOIN pg_catalog.pg_user u\n"
					  "       ON n.nspowner=u.usesysid\n",
					  _("Name"),
					  _("Owner"));

	processNamePattern(&buf, pattern, false, false,
					   NULL, "n.nspname", NULL,
					   NULL);

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

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

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

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

	PQclear(res);
	return true;
}


/*
 * processNamePattern
 *
 * Scan a wildcard-pattern option and generate appropriate WHERE clauses
 * to limit the set of objects returned.  The WHERE clauses are appended
 * to buf.
 *
 * pattern: user-specified pattern option to a \d command, or NULL if none.
 * have_where: true if caller already emitted WHERE.
 * force_escape: always quote regexp special characters, even outside quotes.
 * schemavar: name of WHERE variable to match against a schema-name pattern.
 * Can be NULL if no schema.
 * namevar: name of WHERE variable to match against an object-name pattern.
 * altnamevar: NULL, or name of an alternate variable to match against name.
 * visibilityrule: clause to use if we want to restrict to visible objects
 * (for example, "pg_catalog.pg_table_is_visible(p.oid)").	Can be NULL.
 */
static void
processNamePattern(PQExpBuffer buf, const char *pattern,
				   bool have_where, bool force_escape,
				   const char *schemavar, const char *namevar,
				   const char *altnamevar, const char *visibilityrule)
{
	PQExpBufferData schemabuf;
	PQExpBufferData namebuf;
	bool		inquotes;
	const char *cp;
	int			i;

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

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

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

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

	while (*cp)
	{
		if (*cp == '"')
		{
			if (inquotes && cp[1] == '"')
			{
				/* emit one quote */
				appendPQExpBufferChar(&namebuf, '"');
				cp++;
			}
			inquotes = !inquotes;
			cp++;
		}
		else if (!inquotes && isupper((unsigned char) *cp))
		{
			appendPQExpBufferChar(&namebuf,
								  tolower((unsigned char) *cp));
			cp++;
		}
		else if (!inquotes && *cp == '*')
		{
			appendPQExpBuffer(&namebuf, ".*");
			cp++;
		}
		else if (!inquotes && *cp == '?')
		{
			appendPQExpBufferChar(&namebuf, '.');
			cp++;
		}
		else if (!inquotes && *cp == '.')
		{
			/* Found schema/name separator, move current pattern to schema */
			resetPQExpBuffer(&schemabuf);
			appendPQExpBufferStr(&schemabuf, namebuf.data);
			resetPQExpBuffer(&namebuf);
			cp++;
		}
		else
		{
			/*
			 * Ordinary data character, transfer to pattern
			 *
			 * Inside double quotes, or at all times if parsing an operator
			 * name, quote regexp special characters with a backslash to
			 * avoid regexp errors.  Outside quotes, however, let them
			 * pass through as-is; this lets knowledgeable users build
			 * regexp expressions that are more powerful than shell-style
			 * patterns.
			 */
			if ((inquotes || force_escape) &&
				strchr("|*+?()[]{}.^$\\", *cp))
				appendPQExpBuffer(&namebuf, "\\\\");

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

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

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

		appendPQExpBufferChar(&schemabuf, '$');
		/* Optimize away ".*$", and possibly the whole pattern */
		if (schemabuf.len >= 3 &&
			strcmp(schemabuf.data + (schemabuf.len - 3), ".*$") == 0)
			schemabuf.data[schemabuf.len - 3] = '\0';

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

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

		appendPQExpBufferChar(&namebuf, '$');
		/* Optimize away ".*$", and possibly the whole pattern */
		if (namebuf.len >= 3 &&
			strcmp(namebuf.data + (namebuf.len - 3), ".*$") == 0)
			namebuf.data[namebuf.len - 3] = '\0';

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

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

#undef WHEREAND
}