Re: returning inserted rows, derived tables design

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.