Re: oid's in views.

Hey, Dado,

> Hi Josh!
> Once you have your usq, how do you get more info about that row?
> How do you know which table it came from?

Well, if you have to go at it from that angle (hey, I have this USQ,
where did it come from) then you're in trouble. However, I never use it
that way. Let me give you an example of USQ use:

Modifications table

TABLE candidates
usq INT4 DEFAULT NEXTVAL ('universal_sq'),
first_name VARCHAR NULL,
etc.

TABLE orders
usq INT4 DEFAULT NEXTVAL ('universal_sq'),
client_usq INT4 NOT NULL REFERENCES clients(usq),
etc.

TABLE mod_data
ref_usq INT4 NOT NULL PRIMARY KEY,
entry_date DATETIME NOT NULL,
entry_user INT4 NOT NULL references users(usq),
mod_date DATETIME NOT NULL,
mod_user INT4 NOT NULL references users(usq)

Thus I effectively have a One-to-One relationship between all of the
tables posessing USQs and the mod_data table. This means I can use one
function to update this timestamp information, regardless of table,
whenever a record is inserted or updated.

When I'm retrieving modification information, I never start with the
mod_data table. To do so would be asking the question, "What records,
in any table, were modified by Josh on Decemebr 12th?" which really
isn't useful and would be very difficult (but possible) to query.

Instead, the question I'm usually asking is, "When and by who was the
current record on the screen modified?" Which means that I am
retrieving a single, unique, row from mod_data (SELECT * FROM mod_data
WHERE ref_usq = 451).

This whole scheme, which has been very convenient for me, would not have
been possible without a good way of insuring USQ uniqueness between
tables, which, thankfully, our core team was foresighted enough to
supply. Unfortunately, that does mean that this solution is not
portable to other RDBMSs, but as PostgreSQL grows in market share,
that's less of a concern.

-Josh Berkus

______AGLIO DATABASE SOLUTIONS___________________________
Josh Berkus
Complete information technology josh(at)agliodbs(dot)com
and data management solutions (415) 565-7293
for law firms, small businesses fax 621-2533
and non-profit organizations. San Francisco