src/tools/findoidjoins/findoidjoins.c

/*
 * findoidjoins.c
 *
 * Copyright (c) 2002-2009, PostgreSQL Global Development Group
 *
 * $PostgreSQL: pgsql/src/tools/findoidjoins/findoidjoins.c,v 1.7 2009/01/01 17:24:05 momjian Exp $
 */
#include "postgres_fe.h"

#include "libpq-fe.h"
#include "pqexpbuffer.h"


int
main(int argc, char **argv)
{
	PGconn	   *conn;
	PQExpBufferData sql;
	PGresult   *res;
	PGresult   *pkrel_res;
	PGresult   *fkrel_res;
	char	   *fk_relname;
	char	   *fk_nspname;
	char	   *fk_attname;
	char	   *pk_relname;
	char	   *pk_nspname;
	int			fk,
				pk;				/* loop counters */

	if (argc != 2)
	{
		fprintf(stderr, "Usage:  %s database\n", argv[0]);
		exit(EXIT_FAILURE);
	}

	initPQExpBuffer(&sql);

	appendPQExpBuffer(&sql, "dbname=%s", argv[1]);

	conn = PQconnectdb(sql.data);
	if (PQstatus(conn) == CONNECTION_BAD)
	{
		fprintf(stderr, "connection error:  %s\n", PQerrorMessage(conn));
		exit(EXIT_FAILURE);
	}

	/* Get a list of relations that have OIDs */

	resetPQExpBuffer(&sql);

	appendPQExpBuffer(&sql, "%s",
					  "SET search_path = public;"
					  "SELECT c.relname, (SELECT nspname FROM "
		"pg_catalog.pg_namespace n WHERE n.oid = c.relnamespace) AS nspname "
					  "FROM pg_catalog.pg_class c "
					  "WHERE c.relkind = 'r' "
					  "AND c.relhasoids "
					  "ORDER BY nspname, c.relname"
		);

	res = PQexec(conn, sql.data);
	if (!res || PQresultStatus(res) != PGRES_TUPLES_OK)
	{
		fprintf(stderr, "sql error:  %s\n", PQerrorMessage(conn));
		exit(EXIT_FAILURE);
	}
	pkrel_res = res;

	/* Get a list of columns of OID type (or any OID-alias type) */

	resetPQExpBuffer(&sql);

	appendPQExpBuffer(&sql, "%s",
					  "SELECT c.relname, "
					  "(SELECT nspname FROM pg_catalog.pg_namespace n WHERE n.oid = c.relnamespace) AS nspname, "
					  "a.attname "
					  "FROM pg_catalog.pg_class c, pg_catalog.pg_attribute a "
					  "WHERE a.attnum > 0 AND c.relkind = 'r' "
					  "AND a.attrelid = c.oid "
					  "AND a.atttypid IN ('pg_catalog.oid'::regtype, "
					  " 'pg_catalog.regclass'::regtype, "
					  " 'pg_catalog.regoper'::regtype, "
					  " 'pg_catalog.regoperator'::regtype, "
					  " 'pg_catalog.regproc'::regtype, "
					  " 'pg_catalog.regprocedure'::regtype, "
					  " 'pg_catalog.regtype'::regtype, "
					  " 'pg_catalog.regconfig'::regtype, "
					  " 'pg_catalog.regdictionary'::regtype) "
					  "ORDER BY nspname, c.relname, a.attnum"
		);

	res = PQexec(conn, sql.data);
	if (!res || PQresultStatus(res) != PGRES_TUPLES_OK)
	{
		fprintf(stderr, "sql error:  %s\n", PQerrorMessage(conn));
		exit(EXIT_FAILURE);
	}
	fkrel_res = res;

	/*
	 * For each column and each relation-having-OIDs, look to see if the
	 * column contains any values matching entries in the relation.
	 */

	for (fk = 0; fk < PQntuples(fkrel_res); fk++)
	{
		fk_relname = PQgetvalue(fkrel_res, fk, 0);
		fk_nspname = PQgetvalue(fkrel_res, fk, 1);
		fk_attname = PQgetvalue(fkrel_res, fk, 2);

		for (pk = 0; pk < PQntuples(pkrel_res); pk++)
		{
			pk_relname = PQgetvalue(pkrel_res, pk, 0);
			pk_nspname = PQgetvalue(pkrel_res, pk, 1);

			resetPQExpBuffer(&sql);

			appendPQExpBuffer(&sql,
							  "SELECT	1 "
							  "FROM \"%s\".\"%s\" t1, "
							  "\"%s\".\"%s\" t2 "
							  "WHERE t1.\"%s\"::pg_catalog.oid = t2.oid "
							  "LIMIT 1",
				 fk_nspname, fk_relname, pk_nspname, pk_relname, fk_attname);

			res = PQexec(conn, sql.data);
			if (!res || PQresultStatus(res) != PGRES_TUPLES_OK)
			{
				fprintf(stderr, "sql error:  %s\n", PQerrorMessage(conn));
				exit(EXIT_FAILURE);
			}

			if (PQntuples(res) != 0)
				printf("Join %s.%s.%s => %s.%s.oid\n",
					   fk_nspname, fk_relname, fk_attname,
					   pk_nspname, pk_relname);

			PQclear(res);
		}
	}

	PQclear(pkrel_res);
	PQclear(fkrel_res);
	PQfinish(conn);

	termPQExpBuffer(&sql);

	exit(EXIT_SUCCESS);
}