pgsql.waldvogel@xxxxxxx wrote:
The situation: While porting an existing application from Sybase ASA
to PostgreSQL 7.4 I need to access connection specific login data
(user data). In the Sybase solution I used a temporary table (CREATE
TEMPORARY TABLE). Each connection than had the table on connect; each
client could then perform the INSERT statement without creating
table. PostgreSQL differs from the behaviour as documented. A lot of
(very) different clients rely upon this this standard behaviour.
The question: Is there any feature/technique that a) can create
temporary tables as in the SQL Standard defined
CREATE TEMPORARY TABLE, as per the SQL spec probably. PostgreSQL is
pretty standards compliant, I'd be surprised if it isn't in this respect.
You seem to want a temporary table that is persistent across
transactions. Isn't that a violation of the SQL spec instead?
b) something like "ON CONNECT DO BEGIN .... END"? So that the
temporary table can be automatically created upon connect?
No, but why do you use a _temporary_ table?
If you'd just use a normal table and never commit your inserts, data
will only be visible to the current transaction. It would be a good idea
to delete those records afterwards, though.
This has the drawback that the same counts for all inserts and updates,
but you'd be safe if you delete the user data record before committing
everything. If you need finer grained transaction handling than that,
I'd look at PostgreSQL 8, which has savepoints.
I suppose the login information is also available by other means, in
that case you might want to create a view around the appropriate
functionality instead of your temporary table trick.
Just a few ideas...
--
Alban Hertroys
alban@xxxxxxxxxxxxxxxxx
magproductions b.v.
T: ++31(0)534346874
F: ++31(0)534346876
M:
I: www.magproductions.nl
A: Postbus 416
7500 AK Enschede
//Showing your Vision to the World//
