PSQL: add case / if statement to `returning` statement

Question

I want to do the insert in all cases, but I only want to return ID if my_array (type varchar[])only has one element.

I've tried something like this:

with x_array_items as (
        select id from unnest(my_array) as id
    )
insert into table (name, description)
select name, description
from other_table ot,
x_array_items xa
where ot.id = xa.id
returning (case when array_length(my_array, 1) = 1 is false then id else null end) into my_variable;

But I'm getting a query returned more than one row - is there a solution? Thank you!

Answer 1

Even if you return NULL in your RETURNING clause, that will still be more than one NULLs if the INSERT inserts more than one row, which causes the error.

You could use more WITH clauses to do what you want:

WITH ids AS (
   INSERT INTO tab (name, description)
   SELECT name, description
   FROM other_table ot
   WHERE ARRAY[ot.id] <@ my_array
   RETURNING id
),
id_agg AS (
   SELECT min(id) AS id,
          count(*) AS c
   FROM ids
)
SELECT CASE WHEN c = 1
            THEN id
       END
       INTO my_variable
FROM id_agg;