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@agliodbs.com
   and data management solutions       (415) 565-7293
  for law firms, small businesses        fax 621-2533
    and non-profit organizations.      San Francisco