How to use result of INSERT statement in other INSERT statement?

Question

In PostgreSQL database I have 2 tables.

QUESTIONS

| question_id | question_text | widget | required | position |
|-------------|---------------|--------|----------|----------|
| int         | text          | int    | boolean  | int      |

FACTORS_QUESTIONS_RELATIONSHIP

| factor_id   | question_id   |
|-------------|---------------|
| int         | text          |

As you can see in this function I have 2 insert statements. First one return list of ids of new questions. I want to use that ids in the second insert statement. How to make it correctly? In the same time I want to return ids from first statement as result of the function.

CREATE OR REPLACE FUNCTION factorio(
    FACTOR_IDENTIFIER INT,
    TEXT_ARR VARCHAR[],
    WIDGET_ARR INT[],
    REQUIRED_ARR BOOLEAN[],
    POSITION_ARR INT[]
) RETURNS SETOF INT AS $$
    BEGIN
        RETURN QUERY
        WITH RESULT_SET AS (
            INSERT INTO QUESTIONS (TEXT, WIDGET, REQUIRED, POSITION) 
            SELECT 
                UNNEST(ARRAY[TEXT_ARR]) AS TEXT,
                UNNEST(ARRAY[WIDGET_ARR]) AS WIDGET,
                UNNEST(ARRAY[REQUIRED_ARR]) AS REQUIRED,
                UNNEST(ARRAY[POSITION_ARR]) AS POSITION
            RETURNING ID
        )
        --
        INSERT INTO factors_questions_relationship (FACTOR_ID, QUESTION_ID) 
        SELECT FACTOR_IDENTIFIER FACTOR_ID, QUESTION_ID FROM UNNEST(ARRAY[array_agg(SELECT ID FROM RESULT_SET)]) QUESTION_ID
        --
        SELECT ID FROM RESULT_SET;
    END;
$$ LANGUAGE plpgsql;

Michael Buen · Accepted Answer · 2019-04-06 16:10:47Z

Hmm.. it looks like there's no need to create a temporary table. You can just directly return the result of the returning id from return query, even if your second insert statement is dependent on the returned ids of first statement. And it's safe since the resulting_rows is not run twice even it is referenced twice in the query.

Anyway, you might still want to use the temporary table approach if the succeeding statements/queries are complex ones, e.g., using loops. Cannot embed loops in CTE.

create or replace function insert_multiple_test()
returns setof int
as           
$$   
begin
    return query
    with resulting_rows as
    (
        insert into z(question_text) values
        ('hello'),
        ('你好'),
        ('hola')
        returning id
    ),
    generate_question as 
    (
        insert into q_factory(q_id, some_text)
        select id, q.another
        from resulting_rows
        cross join unnest(array[
           'answer to life and everything', 
           'are multiple and awesome'
        ]) as q(another)                
    )
    select id from resulting_rows;

end;
$$ language 'plpgsql';

Test:

select * from insert_multiple_test() as x(the_id);

Proof that resulting_rows is not run twice even it is referenced twice in the query. It's still 3 rows:

Test:

select * from q_factory;

Output:

Michael Buen · Accepted Answer · 2019-04-06 15:49:56Z

I would just put the ids on temporary table of ids first. Then issue another insert. Then return the temporary table of ids.

Here's an example:

DDL

create table z
(
    id int generated by default as identity primary key, 
    question_text text not null
);

create table q_factory
(
    id int generated by default as identity primary key, 
    q_id int not null references z(id),
    some_text text not null
);

Function

create or replace function insert_multiple_test()
returns setof int
as           
$$   
begin
    create temporary table x_t on commit drop
    as
    with resulting_rows as
    (
        insert into z(question_text) values
        ('hello'),
        ('你好'),
        ('hola')
        returning id
    )   
    select id from resulting_rows;

    insert into q_factory(q_id, some_text)
    select id, q.another
    from x_t
    cross join unnest(array[
        'answer to life and everything', 
        'are great and awesome'
    ]) as q(another);

    return query select id from x_t;        
end;
$$ language 'plpgsql'

Test:

select * from insert_multiple_test();

Output:

Test:

select * from q_factory;

Output:

Collectives™ on Stack Overflow

How to use result of INSERT statement in other INSERT statement?

2 Answers 2

Comments

DDL

Function

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

DDL

Function

Comments

Your Answer

Sign up or log in

Post as a guest

Related