Thread: record OID to table

On Fri, Mar 03, 2006 at 11:31:40AM -0600, Yudie Pg wrote:
> How can I possible to find out what table a record belong to from record's
> oid?

You can't. An OID doesn't identify the record since it's not guarenteed
to be unique (usually). In a query you can use tableoid but that's
about it.

Hope this helps,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.

On Fri, Mar 03, 2006 at 11:31:40AM -0600, Yudie Pg wrote:
> How can I possible to find out what table a record belong to
> from record's oid?

You can't, short of querying every table that has an oid column.
However, you could determine the table from the record's tableoid
column.

Where are you getting the oid?  Could you tell us more about what
you're trying to do?

--
Michael Fuhr

[Please copy the mailing list on replies.]

On Fri, Mar 03, 2006 at 01:42:15PM -0600, Yudie Pg wrote:
> I was trying to make a indexing table that use fulltext indexing that could
> store all string values from the other tables in the database.
> I hope that can use record oid as the key and can be joined with the actual
> table.

The oid alone doesn't identify a row's table, and even the pair
(tableoid, oid) isn't guaranteed to be unique unless the table has
a primary key or unique index on oid.  The documentation discourages
the use of oids as primary keys, and they're disabled by default
as of 8.1.

You could identify rows and their tables with (tableoid, primary_key),
where primary_key is whatever primary key the tables use (including
oid if it's declared as the primary key).  You wouldn't be able to
join the lookup table against a row's source table with ordinary
SQL, but you could write a function that uses dynamic SQL to generate
and execute an appropriate query.

> The reason I want to do that way instead querying directly to the table is
> because most of the tables may contain only reference id not the actual
> string value. Joining the table is not my option because it will lead to
> complicated and heavy query cost.

Could you explain what you're trying to do without reference to how
you're trying to do it?  It sounds like the goal is to take an
arbitrary string and find out what rows in what tables contain that
string.  Is that right?  If so them I'm not sure how to best solve
that problem; maybe somebody else will have some ideas.

--
Michael Fuhr

On Fri, Mar 03, 2006 at 09:01:53PM -0700, Michael Fuhr wrote:

> Could you explain what you're trying to do without reference to how
> you're trying to do it?  It sounds like the goal is to take an
> arbitrary string and find out what rows in what tables contain that
> string.  Is that right?  If so them I'm not sure how to best solve
> that problem; maybe somebody else will have some ideas.
If it happens to be one text field per table it can be
solved with inheritance. Inherit the text field from a
parent table and search through that eventually deducing the
appropriate child table with tableoid where needed.

Karsten
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346