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.