Nicer way to insert on multiple tables, multiple retuned values from SQL WITH statement

Question

Ok,the title might be look a little bit wierd, but this is my situation. I'm using PostgreSQL, latest version.

I have a schema called

schema_ex

I have 3 tables called

A B C

Table A has a unique id (serial) assigned automatically on insertion called

id

I want to insert 40 records

First 22

• 22 records on table A, containing 3 values for 3 columns: NULL, '1', 1..22
• 22 in table B, containing 2 values for 2 columns returned from table A from the previous insert: id, 1..22
• 5 records in table C for every record I insert in table A, containing 2 values for 2 columns returned from table A from the first insert: id, 1..22

Next 18

• 18 records on table A, containing 3 values for 3 columns: NULL, '2', 23..40
• 18 in table B, containing 2 values for 2 columns returned from table A from the previous insert: id, 23..40
• 5 records in table C for every record I insert in table A, containing 2 values for 2 columns returned from table A from the first insert: id, 23..40

41..N ...

• (cut for convenience)

My code so far is this

SET schema 'schema_ex';
DO
$do$
BEGIN 
FOR j IN 1..22 LOOP
  WITH i1 AS (
    INSERT INTO A (col_a, col_b, col_c) VALUES (NULL, '2', j) RETURNING id, col_c
  )
,  i2 AS (
    INSERT INTO C (id, col_c)
    SELECT id, col_c FROM i1
    )
,  i3 AS (
    INSERT INTO C (id, col_c)
    SELECT id, col_c FROM i1
    )
,  i4 AS (
    INSERT INTO C (id, col_c)
    SELECT id, col_c FROM i1
    )
,  i5 AS (
    INSERT INTO C (id, col_c)
    SELECT id, col_c FROM i1
    )
,  i6 AS (
    INSERT INTO C (id, col_c)
    SELECT id, col_c FROM i1
    )
INSERT INTO B (id, col_c)
SELECT id, col_c FROM i1;
END LOOP;
END
$do$;

DO
$do$
BEGIN 
FOR j IN 23..40 LOOP
  WITH i1 AS (
    INSERT INTO A (col_a, col_b, col_c) VALUES (NULL, '2', j) RETURNING id, col_c
  )
,  i2 AS (
    INSERT INTO C (id, col_c)
    SELECT id, col_c FROM i1
    )
,  i3 AS (
    INSERT INTO C (id, col_c)
    SELECT id, col_c FROM i1
    )
,  i4 AS (
    INSERT INTO C (id, col_c)
    SELECT id, col_c FROM i1
    )
,  i5 AS (
    INSERT INTO C (id, col_c)
    SELECT id, col_c FROM i1
    )
,  i6 AS (
    INSERT INTO C (id, col_c)
    SELECT id, col_c FROM i1
    )
INSERT INTO B (id, col_c)
SELECT id, col_c FROM i1;
END LOOP;
END
$do$;

It works but it's not so elegant, so I made a function

CREATE OR REPLACE FUNCTION test.tmp()
RETURNS text AS
$BODY$
DECLARE
    rec tmp_table%ROWTYPE;
BEGIN
    FOR i IN 1..22 LOOP
        WITH last AS (
        INSERT INTO schema_ex.A (col_a, col_b, col_c)
        VALUES (NULL, '1', i) RETURNING id, col_c
    )
    INSERT INTO tmp_table
    SELECT id, col_c FROM last;
    END LOOP;

    FOR i IN 23..40 LOOP
        WITH last AS (
        INSERT INTO schema_ex.A (col_a, col_b, col_c)
        VALUES (NULL, '2', i) RETURNING id, col_c
    )
    INSERT INTO tmp_table
    SELECT id, col_c FROM last;
    END LOOP;

    FOR rec IN EXECUTE('SELECT * FROM tmp_table') LOOP
    INSERT INTO schema_ex.B (id, col_c)
    VALUES (rec.id, rec.col_c);

    FOR j IN 1..5 LOOP
        INSERT INTO schema_ex.C (text_id, col_c)
        VALUES (rec.id, rec.col_c);
    END LOOP;
    END LOOP;

    RETURN 'ok';
END;
$BODY$
LANGUAGE plpgsql VOLATILE

But even if it works I find it a little bit wierd, is there any simple/elegant way to accomplish my needs?

Answer 1

AS far as I know is there a special function for that

with last as (
  insert into schema_ex.A select null, '1', i from generate_series( 1, 22 ) as i returning id, col_c;
)

only as a example.

Does this work for you?