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

Laurenz Albe · Accepted Answer · 2018-05-23 07:06:06Z

11

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;

answered May 23, 2018 at 7:06

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related