Re: [HACKERS] How do I find the table that an oid belongs to

>>>>> "Roland" == Roland Roberts <roberts(at)panix(dot)com> writes:

Roland> ... given an oid, how can I figure out what table it
Roland> belongs to?

I posted the above a few days ago, but got no takers. So, I'll post
my own solution. If someone can point me to a better way, please let
me know. I particularly dislike looping over all the tables to find
the OID, but don't know how else to do this....

The code below is written for PHP, but if you know Perl, you'll have
no problem following it.

# Given a query handle, find the primary key of the row just inserted.
function db_insertid ($r) {
global $conn;

$oid = pg_GetLastOID ($r);
if (!$oid) {
echo pg_ErrorMessage($dbh) . "\n";
return "";
}

# This will search through all the tables in database keystone to find
# the row with OID $oid, then return the value of the primary key column
# for that row. This is not exactly what mysql_insertid does, but it
# fits with the use of db_insertid.

# Search the system catalogues to find all the tables.
$query = "SELECT c.relname "
. " FROM pg_class c "
. " WHERE c.relkind = 'r'"
. " AND c.relname !~ '^pg_'";

$res1 = pg_Exec ($conn, $query);

if (!$res1) {
echo pg_ErrorMessage ($conn) . "\n";
} else {
# For each table, query to see if the OID is present.
for ($idx = 0; $idx < pg_NumRows ($res1); $idx++) {
$row = pg_Fetch_Row ($res1, $idx);
$table = $row[0];
$res2 = pg_Exec ($conn, "select oid from $table where oid = $oid");
if (pg_NumRows ($res2) != 0) {
# We found the OID in the current table, now find the name of the primary key column
$query = "SELECT a.attname FROM pg_class c, pg_attribute a, pg_index i, pg_class c2 "
. " WHERE c.relname = '$table' "
. " AND i.indrelid = c.oid "
. " AND a.attrelid = c.oid "
. " AND c2.oid = i.indexrelid "
. " AND i.indkey[0] = a.attnum "
. " AND c2.relname ~ '_pkey$'";
$res2 = pg_Exec ($conn, $query);
if ($res2) {
$pkname = pg_Result ($res2, 0, 0);
$res2 = pg_Exec ("select $pkname from $table where oid = $oid");
$row = pg_Fetch_Row ($res2, 0);
return $row[0];
} else {
return 0;
}
break;
}
}
}
};

roland
--
Roland B. Roberts, PhD Custom Software Solutions
roberts(at)panix(dot)com 101 West 15th St #4NN
New York, NY 10011