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



  • 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.



  • 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');
    

Log in to reply
 


Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2