CODEWITHSUNDEEP
sqlpostgresqlinsert
eomeroff
eomeroff

Reputation: 9915

Insertion of auto-incremented id in postgress

I want to crate insert script for table sd_roles. Some of the rows need to have values of other row's id.

sd_roles table is created with this script:

-- Table: sd_roles

-- DROP TABLE sd_roles;

CREATE TABLE sd_roles
(
  id integer NOT NULL DEFAULT nextval('roles_serials'::regclass),
  masterid integer,
  rname character varying(150) NOT NULL,
  CONSTRAINT sd_roles_pkey PRIMARY KEY (id),
  CONSTRAINT rname_unique UNIQUE (rname)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE sd_roles
  OWNER TO postgres;
GRANT ALL ON TABLE sd_roles TO postgres;
GRANT ALL ON TABLE sd_roles TO eomeroff;

-- Trigger: lowecase_roles_on_insert_trigger on sd_roles

-- DROP TRIGGER lowecase_roles_on_insert_trigger ON sd_roles;

CREATE TRIGGER lowecase_roles_on_insert_trigger
  BEFORE INSERT OR UPDATE
  ON sd_roles
  FOR EACH ROW
  EXECUTE PROCEDURE lowecase_roles_on_insert();

I have this code:

    DELETE FROM sd_roles;


    WITH appconfiguration AS (
        INSERT INTO sd_roles
            (masterid, rname)
        VALUES
            (null, 'Appconfiguration')
        RETURNING id
    )
    INSERT INTO sd_roles (masterid, rname)
    VALUES ((SELECT id FROM appconfiguration), 'GroupsandRolesConfig');

Now I need to add code for insert another row that will have "masterid" field equal to id of row that have "rname" value equal to "GroupsandRolesConfig". In the same fashion as row where "rname" field is eqaul to "GroupsandRolesConfig" has "masterid" value that is id of row which "rname" field is eqaul to "Appconfiguration".

My problem is that inser clause that follows with cluase cannot remember id of inserted value thus I could reuse it.

How to do that?

Upvotes: 0

Views: 102

Answers (2)

Jakub Kania
Jakub Kania

Reputation: 16487

You can have more than one CTE, you can chain them as much as you need.

     WITH appconfiguration AS (
        INSERT INTO sd_roles
            (masterid, rname)
        VALUES
            (null, 'Appconfiguration')
        RETURNING id
    ), groups AS (
      INSERT INTO sd_roles (masterid, rname)
        VALUES ((SELECT id FROM appconfiguration), 'GroupsandRolesConfig')
      RETURNING id
    )
    INSERT INTO sd_roles (masterid, rname)
        VALUES ((SELECT id FROM groups), 'foobar');

http://sqlfiddle.com/#!15/3cebb/3

Upvotes: 1

eomeroff
eomeroff

Reputation: 9915

This code works: 

DELETE FROM sd_roles;

    INSERT INTO sd_roles (masterid, rname)
    VALUES (null, 'Superadmin');

    WITH appconfiguration AS (
        INSERT INTO sd_roles
            (masterid, rname)
        VALUES
            (null, 'Appconfiguration')
        RETURNING id
    )
    INSERT INTO sd_roles (masterid, rname)
    VALUES ((SELECT id FROM appconfiguration), 'Database'),
            ((SELECT id FROM appconfiguration), 'GroupsandRolesConfig');

    WITH groupsandRolesConfig AS (
         SELECT * FROM sd_roles where rname = LOWER('GroupsandRolesConfig')
    )
    INSERT INTO sd_roles (masterid, rname)
    VALUES ((SELECT id FROM groupsandRolesConfig), 'AddDeleteGroups'),
            ((SELECT id FROM groupsandRolesConfig), 'AssignRolesToGroups');

Upvotes: 0

Related Questions