Error - "query has no destination for result data" in Stored Procedure PGSQL

Question

I have the following stored procedure :-

-- PROCEDURE: public.master_todo(text, text)

-- DROP PROCEDURE IF EXISTS public.master_todo(text, text);

CREATE OR REPLACE PROCEDURE public.master_todo(
    "actiontype" text,
    "actionvalue" text)
LANGUAGE 'plpgsql'
AS $BODY$
BEGIN
IF(actiontype = 'getAllTodo') THEN
  SELECT * FROM todo_list;
  
END IF;
END
$BODY$;

which I created using the Procedure ->Create Procedure in PGAdmin. However, while testing it, using EXEC, an error appears :-

ERROR:  query has no destination for result data
HINT:  If you want to discard the results of a SELECT, use PERFORM instead.
CONTEXT:  PL/pgSQL function master_todo(text,text) line 4 at SQL statement
SQL state: 42601

After reading some other answers regarding this, I tried :-

IF(actiontype = 'getAllTodo') THEN
  SELECT * FROM todo_list;
  RETURN;
END IF;

and

IF(actiontype = 'getAllTodo') THEN
  RETURN SELECT * FROM todo_list;
  
END IF;

Neither worked. The second one threw another error that said that RETURN statement cannot have any parameters inside Stored Procedure.

So far all the answers previously mentioned in this topic are based on functions. But, I cannot relate it to Stored procedure.

Answer 1

A procedure isn't meant to return something, so the result of the select can't be returned.

You need to use a set-returning function:

CREATE OR REPLACE function public.master_todo("actiontype" text, "actionvalue" text)
  returns setof todo_list --<< this defines the structure of the result 
  LANGUAGE plpgsql
AS 
$BODY$
BEGIN
  IF (actiontype = 'getAllTodo') THEN
    return query 
       SELECT * FROM todo_list;
  END IF;
  
  -- what do you want to return if a different action type was passed? 
  return query 
    SELECT * 
    FROM todo_list
    WHERE ????;
END
$BODY$;

Then use it like a table:

select *
from master_todo('getAllTodo', '42');