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?

Answer 1

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;
$$;