How to return a newly created ids array from a Postgres function?

Question

I am working to insert multiple rows in a table using postgres function. I have to return all the ids of inserted rows.

my function as follows

    CREATE OR REPLACE FUNCTION insert_multiple_rows(temp integer)
        RETURNS integer[]
        LANGUAGE 'plpgsql'
        VOLATILE
        PARALLEL UNSAFE
        COST 100
    AS $BODY$DECLARE
    company_ids_list integer[];
    BEGIN
    INSERT INTO company VALUES
    (default,now(),temp),
    (default,now(),temp),
    (default,now(),temp),
    (default,now(),temp)
     RETURNING id INTO company_ids_list;

    RETURN company_ids_list;
    END;
    $BODY$;

When i am trying to call this funtion using

 select insert_multiple_rows(58);

getting error

ERROR:  query returned more than one row
CONTEXT:  PL/pgSQL function create_company_budget_allocation(integer) 
line 4 at SQL statement
SQL state: P0003

Answer 1

SELECT .. INTO or INSERT ... RETURNING ... INTO can only fill a single variable. If the query returns more than one row, you will get the error you observe.

You can use the INSERT statement in a CTE and use array_agg to construct the result array:

WITH ins AS (
   INSERT INTO company VALUES
      (default,now(),temp),
      (default,now(),temp),
      (default,now(),temp),
      (default,now(),temp)
   RETURNING id)
SELECT array_agg(id) INTO company_ids_list
FROM ins;