Re: returning inserted rows, derived tables design

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

On Sat, Oct 23, 2004 at 12:30:07 +0200,
Ruediger Herrmann <ruediger(dot)herrmann(at)gmx(dot)de> wrote:
> 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?

Currently the sequence names can be derived from the table and serial
column names. If the names aren't too long, I think it is
tablename_serialname_seq. In 8.0 (unreleased) there is a function that returns
the name of the sequence associated with a serial column.

> 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.

I would avoid using inheritance and use views instead. Inheritance is currently
half-baked and you have to do too much working around limitations when using
it. (In particular having a unique constraint accross all derived tables is a
pain.)

I wouldn't repeat the columns in the actual tables you are using. Instead
the derived tables should refer to the base table using a foriegn key.
You can then use views (and rules if you need the views to be updateable.)
to make derived tables in queries with the columns from the base tables.