Home/Questions/How to fix the ''query has no destination for result data'' error?
Next
Answered
clarissa
  • 3
Asked: 2022-07-22T02:24:52+00:00 In:

How to fix the ”query has no destination for result data” error?

  • 3

When I created using the Procedure ->Create Procedure in PGAdmin. However, an error ”query has no destination for result data” occurs while using EXEC to test it:

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

Here is my 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$;

And I tried the following after reading some other responses on this:

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

and

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

END IF;

It isn’t work! The second one produced yet another problem, this time stating that a RETURN statement inside a stored procedure cannot have any parameters.

Can someone help me?
Share

1 Answer

  1. Best Answer
    Expert

    The cause: I think the cause is that the select’s result cannot be returned because a procedure isn’t designed to do so.

    Solution: To solve the problem, you should 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');
    • 3

Leave an answer

You must login to add an answer.

Need An Account,