Search code examples
sqlpostgresqlplpgsql

Query returned more than one row from INSERT ... RETURNING in function

I'm trying to write a function that inserts multiple records from a SELECT statement and then returns the inserted ids to the caller, but I keep getting the error

ERROR: query returned more than one row, CONTEXT:

Here's a simplified version of the function:

CREATE OR REPLACE FUNCTION schema.copy_data(arg_client_id CHAR(30))
RETURNS TABLE (dest_id CHAR(30))
LANGUAGE plpgsql
SECURITY INVOKER
AS $$
DECLARE 
    -- Declare a variable to store the inserted IDs
    inserted_ids CHAR(30)[];
BEGIN
    WITH
    cte_1 AS (...),

    cte_2 AS (...)

    INSERT INTO schema.dest
    (client_id, fk_id, ...)
    SELECT client_id, fk_id, ...
    FROM
        cte_1
    WHERE
        NOT EXISTS(
            SELECT 1 FROM cte_2
            WHERE
                cte_1.client_id = cte_2.client_id
                AND cte_1.fk_id = cte_2.fk_id
                AND ...
        )
    RETURNING schema.dest.dest_id INTO inserted_ids;

    RETURN QUERY SELECT dest_id FROM UNNEST(inserted_ids) AS dest_id;
END
$$
;

I'm honestly stumped, I couldn't find anything relevant in the Postgres docs or on SO. And ChatGPT doesn't seem to be able to solve this one either (:

Solution

  • You are trying to shoehorn a set into an array variable, which does not work like that.

    Instead, return the set directly. While being at it, simplify to a plain SQL function, no need for PL/pgSQL here.

    CREATE OR REPLACE FUNCTION my_schema.copy_data(arg_client_id char(30))
  RETURNS TABLE (dest_id char(30))  -- avoid char(n)
  LANGUAGE sql AS  -- SECURITY INVOKER was noise - default anyway
$func$
WITH cte_1 AS (...)
   , cte_2 AS (...)
INSERT INTO my_schema.dest (client_id, fk_id, ...)
SELECT client_id, fk_id, ...
FROM   cte_1
WHERE  NOT EXISTS (
   SELECT FROM cte_2  -- SELECT list can stay empty
   WHERE  cte_1.client_id = cte_2.client_id
   AND    cte_1.fk_id = cte_2.fk_id
   AND ...
  )
RETURNING dest.dest_id;  -- don't prefix schema-name!
$func$;

    You most probably don't need CTEs either, and the whole query may be even simpler, yet.

    Matching your given char(30) - which is almost always a bad choice for a data type: