Op 12-3-2013 18:44, Fabian Groffen wrote:
On 12-03-2013 18:38:41 +0100, Niels Nes wrote:
On Tue, Mar 12, 2013 at 03:49:40PM +0100, Gijs Molenaar wrote:
Hi,
A typical question that is probably asked before, but I can't find an answer in the archives or in the doc.
Is there a SQL function for getting the last inserted ID for a auto increment or serial field? PostgreSQL has lastval(). I know it is returned in the query result, but I need to have the value inside a SQL function. Also I know that if you manually create a sequence for ID generation you can get the next value with:
SELECT NEXT VALUE FOR seq_freq INTO freqid;
But we are trying to keep our queries as generic as possible (close to impossible, but still) hence we want to use the SERIAL type.
Or is there a way to find out what is the name of the sequence made when giving a SERIAL type to a column? The values is returned through the mapi result. Alternatively, first retrieve the NEXT VALUE FOR <sequence> and use that value in your insert, instead of relying on the DEFAULT to call that for you.
yes I tried both solutions but it would make the query much more different than the PostgreSQL version and we want to maintain both. Is there a way to discover the name of the <sequence> without having to create it manually? I think i'll just have to accept that it is impossible to have a set of generic or quite similar SQL statements for both MonetDB and PostgreSQL.