the
lastval() function in PostgreSQL has similar behaviour as the
@@IDENTITY function in MS SQL Server.
To solve this kind of problem, MS have defined the
SCOPE_IDENTITY() function.
Unfortunately PostgreSQL didn't actually provide such functionality.
---
One possibility for solving this problem could be to change the declaration of your triggers that are apparently of kind
AFTER, to
BEFORE.
That way the trigger code will be executed before the insertion of the row in the table that contain the desired ID, and
lastval() will return the correct value.
But depending of your trigger's code, this could not be possible...
---
The second possibility that I can imagine is to use the RETURNING clause:
PostgreSQL 8.2 and above have introduced the RETURNING clause for INSERT, UPDATE and DELETE statements.
Exemple:
Code:
CREATE TABLE foo (id serial, textfield varchar);
INSERT INTO foo VALUES (default, 'my text') RETURNING id;
This returns the value of
foo.id for the inserted row.
You can try to modify the dialect for PostgreSQL 8.2 (PostgreSQL82Dialect.cs) by adding something like:
Code:
public override SqlString AddIdentitySelectToInsert(SqlString insertSql, string identityColumn, string tableName)
{
return insertSql.Append(" returning ").Append(identityColumn);
}
This should work for NHibernate 1.2.x
Apparently the
Dialect.AddIdentitySelectToInsert method from NH1.2x was replaced by this one in NH2.0alpha:
Code:
public virtual SqlString AppendIdentitySelectToInsert(SqlString insertString)
And this method does no more takes the
identityColumn parameter. Maybe there is another method to overload in NH2.0 that allow us to do the same thing. I haven't spent long time there.
ref:
SQL Server 2005 SCOPE_IDENTITY():
http://msdn.microsoft.com/en-us/library/ms190315.aspx
PostgreSQL 8.0 CREATE TRIGGER:
http://www.postgresql.org/docs/8.0/interactive/sql-createtrigger.html
PostgreSQL 8.2 INSERT:
http://www.postgresql.org/docs/8.2/interactive/sql-insert.html
Login
FAQ
Search