Re: record OID to table

[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