6

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.

1 Answer 1

6

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');
4
  • But, I have been using such queries in MySQL in exactly the same way that I described in the question. Does it have something to do with PGSQL only ?
    – Skumar
    Jan 11, 2022 at 10:49
  • 1
    Well MySQL is a different database product than Postgres. If you want to return a result in Postgres, the most efficient way is to use a function. MySQL simply doesn't have set returning functions to begin with, so you need a workaround and (mis-)use a procedure for that.
    – user1822
    Jan 11, 2022 at 10:51
  • Okay. Another thing. If I use actionType instead of actiontype (notice the mixed-case), PGSQL throws a different error, stating that no colunm called actiontype exists. What is the reason for that?
    – Skumar
    Jan 11, 2022 at 10:55
  • 1
    Because actiontype is a different name than "actionType" - as soon as you use quoted identifiers they become case sensitive postgresql.org/docs/current/…
    – user1822
    Jan 11, 2022 at 10:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.