Why is this function not able to return an integer due to no destination for result?

Question

I have a simple function that needs to return an integer from a select statement. This is it:

CREATE OR REPLACE FUNCTION validate(_identityid integer, _postid integer) RETURNS integer
    LANGUAGE plpgsql
AS
$$
BEGIN
    SELECT postid
    FROM post 
    WHERE
            postid = _postid
      AND
            identityid = _identityid;
END;
$$;

If I run the above function I get the following error:

[42601] ERROR: query has no destination for result data Hint: If you want to discard the results of a SELECT, use PERFORM instead.

Why is there no destination for the result data? Why doesn't it just return the SELECT statement?

Returning the same value that you pass as the parameter seems rather senseless to me to begin with — , Jan 14 '21 at 18:07
@a_horse_with_no_name I suppose I just need something/anything to be returned for the function to report that the `postid` was validated so why not the `postid` itself. — volume one, Jan 14 '21 at 18:38
I would prefer a function that returns `true` or `false` rather than a value or `null`. — , Jan 14 '21 at 19:25

score 2 · Accepted Answer · answered Jan 14 '21 at 18:03

As documented in the manual the result of a query needs to be stored somewhere.

CREATE OR REPLACE FUNCTION validate(_identityid integer, _postid integer) RETURNS integer
    LANGUAGE plpgsql
AS
$$
DECLARE
  l_result integer;
BEGIN
    SELECT postid
      into l_result
    FROM post 
    WHERE
            postid = _postid
      AND
            identityid = _identityid;
  return l_result;
END;
$$;

But to wrap a simple SELECT like that, using a language sql function is much better anyway and there you can return the result directly.

CREATE OR REPLACE FUNCTION validate(_identityid integer, _postid integer) 
  RETURNS integer
  LANGUAGE sql
  stable
AS
$$
    SELECT postid
    FROM post 
    WHERE
            postid = _postid
      AND
            identityid = _identityid;
$$;

score 1 · Answer 2 · answered Jan 14 '21 at 20:31

If you want to do this using plpgsql without using RETURN you need an OUT parameter:

CREATE OR REPLACE FUNCTION public.out_test(in_val integer, OUT out_val integer)
 LANGUAGE plpgsql
AS $function$
BEGIN
    SELECT INTO out_val in_val + 1;
END;
$function$

select out_test(3);
 out_test 
----------
        4

Per the docs Returning:

The return value of a function cannot be left undefined. If control reaches the end of the top-level block of the function without hitting a RETURN statement, a run-time error will occur. This restriction does not apply to functions with output parameters and functions returning void, however. In those cases a RETURN statement is automatically executed if the top-level block finishes.

Why is this function not able to return an integer due to no destination for result?

2 Answers2