diff options
Diffstat (limited to 'contrib/findoidjoins/findoidjoins.c')
| -rw-r--r-- | contrib/findoidjoins/findoidjoins.c | 212 |
1 files changed, 124 insertions, 88 deletions
diff --git a/contrib/findoidjoins/findoidjoins.c b/contrib/findoidjoins/findoidjoins.c index f83e5da1893..a829a58190a 100644 --- a/contrib/findoidjoins/findoidjoins.c +++ b/contrib/findoidjoins/findoidjoins.c @@ -1,109 +1,145 @@ /* - * findoidjoins.c, requires src/interfaces/libpgeasy + * findoidjoins.c * + * Copyright 2002 by PostgreSQL Global Development Group + * + * $Header: /cvsroot/pgsql/contrib/findoidjoins/Attic/findoidjoins.c,v 1.18 2002/09/05 19:57:32 tgl Exp $ */ #include "postgres_fe.h" #include "libpq-fe.h" -#include "halt.h" -#include "libpgeasy.h" +#include "pqexpbuffer.h" -PGresult *attres, - *relres; int main(int argc, char **argv) { - char query[4000]; - char relname[256]; - char relname2[256]; - char attname[256]; - char typname[256]; - int count; - char optstr[256]; + 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) - halt("Usage: %s database\n", argv[0]); - - snprintf(optstr, 256, "dbname=%s", argv[1]); - connectdb(optstr); - - on_error_continue(); - on_error_stop(); - - doquery("BEGIN WORK"); - doquery("\ - DECLARE c_attributes BINARY CURSOR FOR \ - SELECT typname, relname, a.attname \ - FROM pg_class c, pg_attribute a, pg_type t \ - WHERE a.attnum > 0 AND \ - relkind = 'r' AND \ - (typname = 'oid' OR \ - typname = 'regproc' OR \ - typname = 'regclass' OR \ - typname = 'regtype') AND \ - a.attrelid = c.oid AND \ - a.atttypid = t.oid \ - ORDER BY 2, a.attnum ; \ - "); - doquery("FETCH ALL IN c_attributes"); - attres = get_result(); - - doquery("\ - DECLARE c_relations BINARY CURSOR FOR \ - SELECT relname \ - FROM pg_class c \ - WHERE relkind = 'r' AND relhasoids \ - ORDER BY 1; \ - "); - doquery("FETCH ALL IN c_relations"); - relres = get_result(); - - set_result(attres); - while (fetch(typname, relname, attname) != END_OF_TUPLES) { - set_result(relres); - reset_fetch(); - while (fetch(relname2) != END_OF_TUPLES) + 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", + "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) " + "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++) { - unset_result(relres); - if (strcmp(typname, "oid") == 0) - snprintf(query, 4000, "\ - DECLARE c_matches BINARY CURSOR FOR \ - SELECT count(*)::int4 \ - FROM \"%s\" t1, \"%s\" t2 \ - WHERE t1.\"%s\" = t2.oid ", - relname, relname2, attname); - else - sprintf(query, 4000, "\ - DECLARE c_matches BINARY CURSOR FOR \ - SELECT count(*)::int4 \ - FROM \"%s\" t1, \"%s\" t2 \ - WHERE t1.\"%s\"::oid = t2.oid ", - relname, relname2, attname); - - doquery(query); - doquery("FETCH ALL IN c_matches"); - fetch(&count); - if (count != 0) - printf("Join %s.%s => %s.oid\n", relname, attname, relname2); - doquery("CLOSE c_matches"); - set_result(relres); + 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); } - set_result(attres); } - set_result(relres); - doquery("CLOSE c_relations"); - PQclear(relres); - - set_result(attres); - doquery("CLOSE c_attributes"); - PQclear(attres); - unset_result(attres); + PQclear(pkrel_res); + PQclear(fkrel_res); + PQfinish(conn); - doquery("COMMIT WORK"); + termPQExpBuffer(&sql); - disconnectdb(); - return 0; + exit(EXIT_SUCCESS); } |