How correctly create multiple entries by arrays in PostgreSQL?

Question

In PostgreSQL database I have table which looks like this:

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

Second table which called factors_questions_relationship looks like this:

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

I am trying to create function which would create multiple rows and return array of ids of new created entries. How correctly to make such 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 13:28:31Z

You can simply unnest them in columns

select 
     unnest(array['quick','brown','fox']) as question_text, 
     unnest(array[1,2,3]) as widget_id

Whereas putting them in FROM clause, would result to cartesian product:

select question_text, widget_id
from 
unnest(array['quick','brown','fox']) as question_text, 
unnest(array[1,2,3]) as widget_id

Output:

To obtain the Ids generated from newly-inserted records, use return query + returning id combo. Sample test:

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

create or replace function insert_multiple_test()
returns table (someid int) as
$$
begin
    return query
    with resulting_rows as
    (
        insert into z(question_text) values
        ('hello'),
        ('你好'),
        ('hola')
        returning id
    )
    select id from resulting_rows;
end;
$$ language 'plpgsql';


select * from insert_multiple_test();

Output:

Here's for SETOF:

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

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
    )
    select id from resulting_rows;
end;
$$ language 'plpgsql';


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

Output:

If you don't need to run multiple queries, you can just use LANGUAGE 'sql', it's simpler:

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

create or replace function insert_multiple_test()
returns setof int as
$$
    insert into z(question_text) values
    ('hello'),
    ('你好'),
    ('hola')
    returning id;
$$ language 'sql';


select x.id_here from insert_multiple_test() as x(id_here);

Output:

Hello! Thank you for detailed answer. Can I ask you one more question?! Inside function I am trying to use array of questions ids in insert statement to second table. Can you check my post again please?! What can you recommend to do?

Collectives™ on Stack Overflow

How correctly create multiple entries by arrays in PostgreSQL?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related