Use Just Inserted Id In Postgres

I want to create insert script for table Postgres database. The table should look like this. id | refid name ----------------------------------------------

Solution 1:

Instead of nesting, just chain them:

INSERTINTO table_name( refid, name )
VALUES( id, 'admin' );

INSERTINTO sd_roles( refid, name )
VALUES( null, 'moderator' )
RETURNING id;

INSERTINTO table_name( refid, name )
VALUES( id, 'readonly' ); -- using id of moderatorINSERTINTO table_name( refid, name )
VALUES( id, 'groupadmin' ) -- using id of readonly
RETURNING id;

INSERTINTO table_name( refid, name )
VALUES( id, 'rolesadmin' ) -- using id of groupadmin
RETURNING id;

INSERTINTO table_name( refid, name )
VALUES( id, 'users' ) -- using id of rolesadmin
RETURNING id;

INSERTINTO table_name( refid, name )
VALUES( id, null ); -- using id of users

Solution 2:

You can always define a variable and return the returning id inside it. However if you do not want to use variables. Then you can use lastval()

http://www.postgresql.org/docs/9.3/static/functions-sequence.html

"lastval: Return the value most recently returned by nextval in the current session. This function is identical to currval, except that instead of taking the sequence name as an argument it fetches the value of the last sequence used by nextval in the current session. It is an error to call lastval if nextval has not yet been called in the current session."

INSERTINTO table_name
    (refid, name)
VALUES
    (null, 'moderator');

INSERTINTO sd_roles 
    (refid,rname)
VALUES   
    (LASTVAL(), 'groupadmin')

Solution 3:

You have to use SCOPE_IDENTITY()

DECLARE@idintINSERTINTO table_name
        (refid, name)
    VALUES
        (null, 'moderator')

    SET@id=   SCOPE_IDENTITY()

    INSERTINTO sd_roles (refid,rname)
VALUES   (@id, 'groupadmin')