在Postgres中使用刚插入的ID [英] Use just inserted id in Postgres

问题描述

我想为表Postgres数据库创建插入脚本.该表应如下所示.

I want to create insert script for table Postgres database. The table should look like this.

  id    |    refid                  name
------------------------------------------------------------
autoinc |    null                |      admin
autoinc |    null                |      moderator
autoinc |    id of moderator     |      readonly
autoinc |    id of moderator     |      groupadmin
autoinc |    id of groupadmin    |      rolesadmin
autoinc |    id of rolesadmin    |      users
autoinc |    id of users         |      null

我的问题是我不知道如何使用插入的ID并在脚本的其余部分中重新使用它.例如，我要插入带有主持人名称的行，并在下面的2个插入中使用主持人的ID.

My problem is that I do not know how use just inserted id and re use it in the rest of the script. For example I want to insert row with moderator name and use moderator's id in 2 inserts below.

我该如何实现?

我尝试过

WITH moderator AS (
    INSERT INTO table_name
        (refid, name)
    VALUES
        (null, 'moderator')
    RETURNING id
) 
INSERT INTO sd_roles (refid,rname)
VALUES   ((SELECT id FROM moderator), 'groupadmin')

但是它越来越嵌套了，我需要用with编写.

But it is getting nested more and I would need to write with in with.

推荐答案

与其嵌套，不如将它们链接在一起:

Instead of nesting, just chain them:

INSERT INTO table_name( refid, name )
VALUES( id, 'admin' );

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

INSERT INTO table_name( refid, name )
VALUES( id, 'readonly' ); -- using id of moderator

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

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

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

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

这篇关于在Postgres中使用刚插入的ID的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！