[lug] (OT) postgresql

[Bear Giles]

Sexton, George wrote:
> to read the value just inserted:
> 
> select CurrVal('users_seq') as newKey

This only works until you commit or abort the transaction.  I mention 
this only because some interface libraries do auto-commit - you'll 
either need to disable it so you can bring up the value for use in other 
tables, or create insertion triggers and/or rules that will 
automatically update those other tables.

This brings up an important difference between PostgreSQL and MySQL. 
PostgreSQL supports views, rules, triggers and a robust constraint set. 
In practice, if I have to update multiple tables I prefer to set up a 
view that combines all of this information, then a rule that performs 
the necessary insertions.  This decouples the business logic (which 
determines what needs to be stored in the database) from the database 
logic (which determines what goes where, etc.), and life is much simpler 
for everyone involved.

That's why I cringe every time I see some tool that requires you to 
specify a table and every column name for it to access the database. 
Besides being a real pain to maintain, it assumes that all of the data 
*is* in a single table.  I always set up a view for each application 
(which also allows me to control access to the rest of the database), 
but why can't the app just tell me what view and attributes to set up? 
It's not like I'll already have tables with the application name in them!