Hello again,
as I am new to PostgreSQL (great pice of softwork btw) this probably won't
be the last question.
I access PostgreSQL from Java via the PostgreSQL JDBC driver. I am
currently building a little framework that provides basic CRUD
operations by mapping class properties to database columns. All my
primary keys are artificial and built by sequences (datat type bigserial).
Now I need to (re-)read the row that was inserted/updated because triggers
may have changed the column values, row versions (CMAX, thanks to Tom Lane)
are different and so on.
For the update operation I "know" wich row to select since the primary key
alread exists. But what about the insert? When I know the sequence for
a particular primary key I could obtain its last value select the row
with this very primary key...
This would imply that my framework must "know" wich sequence belongs to
a primary key. I don't like this idea.
To come to an end, a RETURNNG clause for the insert/update statement would
be the perfect solution, but there isn't any, right?
Another uncertainty: Is it wise to have most tables derive from one base
table (concering performace, concurrence, maintainability, etc). Let's say
99% of my tables have an Id (artificial primary key), CreatedBy, UpdatedBy
column.
Should I put those columns in a base table and derive from it or let each
table have these columns by itself.
As for the Id column (bigserial) in the "derived solution" there would be
only one sequence wich makes it unique for the whole database. That sounds
very appealing from the application point of view.
Thanks for your patience
Ruediger
--
+++ GMX DSL Premiumtarife 3 Monate gratis* + WLAN-Router 0,- EUR* +++
Clevere DSL-Nutzer wechseln jetzt zu GMX: http://www.gmx.net/de/go/dsl
