Thread: How do I find the table that an oid belongs to

I'm trying to get Keystone (http://www.stonekeep.com/) to work and
have to emulate some MySQL functions.  One of these is the ability to
return the value of a column that is an auto-increment column after a
row was inserted, updated, or deleted.  The interface I have to work
with is PHP.  PHP has a function, pg_getlastoid, that lets me find the
oid of the last insert, update, or delete.  I've looked at psql.c to
find out how to get the column attributes so I can figure out which
one(s) are auto increment (or rather, which ones are coming from a
sequence definition via "default nextval ('seqname')").

What I can't figure out is, given an oid, how can I figure out what
table it belongs to?

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

>>>>> "Roland" == Roland Roberts <roberts@panix.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@panix.com                           101 West 15th St #4NN
                                               New York, NY 10011