PotgresSQL INSERT INTO SELECT CASE problem

Question

I have the the following query and I'd like to insert any number of rows from the sub queries. Of course I'm getting an error SQL Error [21000]: ERROR: more than one row returned by a sub query used as an expression since some of the sub queries return multiple rows

insert into logs (_timestap, _message, _mode, _user_id)
select :_timestamp, :_message, :_mode,
case :_mode

    -- select all users that have the given grade. NOTE that this sub query returns multiple rows
    when 'byGrade' then (select u.id from users u where grade = :_grade)

    -- The user id value is already passed
    when 'byIndividual' then (select :_user_id limit 1)

    -- This sub query also returns multiple rows (all infact) from the users table
    when 'everyone' then (select id from users)
end

PostgreSQL version 10.

How can I solve this?

score 2 · Accepted Answer · answered Apr 25 '20 at 11:35

union all can to help:

insert into logs (_timestap, _message, _mode, _user_id)

select :_timestamp, :_message, :_mode, id
from users
where :_mode = 'byGrade' and grade = :_grade

union all

select :_timestamp, :_message, :_mode, :_user_id
where :_mode = 'byIndividual'

union all

select :_timestamp, :_message, :_mode, id
from users
where :_mode = 'everyone';

score 0 · Answer 2 · answered Apr 25 '20 at 11:19

This needs procedural code, either on database client-side in Your Favourite Programming Language (TM) or database server-side procedural language. Plain SQL is declarative language and does not have advanced conditionals. SQL CASE is not enough for the job.

In Pl/PgSQL it would look like:

CREATE FUNCTION do_the_thing(
  p_timestap timestamptz,
  p_message text,
  p_mode text,
  p_user_id integer)
RETURNS VOID
LANGUAGE plpgsql AS $definition$
BEGIN
if p_mode = 'byGrade' then
  insert into logs (_timestap, _message, _mode, _user_id)
  select p_timestamp, p_message, p_mode, u.id from users u where grade = p_grade;
elsif p_mode = 'byIndividual' then
  insert into logs (_timestap, _message, _mode, _user_id)
  select p_timestamp, p_message, p_mode, p_user_id;
elsif p_mode = 'everyone' then
  insert into logs (_timestap, _message, _mode, _user_id)
  select p_timestamp, p_message, p_mode, id from users;
end if;
END; 
$definition$;

PotgresSQL INSERT INTO SELECT CASE problem

2 Answers2