Hasura - query has no destination for result data

Question

In Hasura, I'm trying write a plpgsql based function which should return the t_documents. The function should accept various optional arguments, based on the argument values it should run appropriate SQL (using IF conditional checks) and return the records. This is my goal.

I've started with simple function with no arguments, the function created and tracked successfully in Hasura, however when try to query it throws "query has no destination for result data"

Any help to achieve the goal is highly appreciated

CREATE OR REPLACE FUNCTION dms.fngetdocs()
 RETURNS SETOF dms.t_documents
 LANGUAGE plpgsql
 STABLE
AS $function$
BEGIN
SELECT *
    FROM dms.t_documents;
END;    
$function$

Answer 1

Every SQL statement returning rows (like SELECT, but also INSERT with RETURNING etc.) must have a target for the resulting data. That's what the error message tells you. Either assign variables or return to the caller. To discard results instead, use PERFORM instead of SELECT. See:

• SELECT raises exception in PL/pgSQL function
• PL/pgSQL: PERFORM vs CALL
• SELECT or PERFORM in a PL/pgSQL function

But you obviously want to return rows. Your PL/pgSQL function would work like this:

CREATE OR REPLACE FUNCTION dms.fngetdocs()
  RETURNS SETOF dms.t_documents
  LANGUAGE plpgsql STABLE PARALLEL SAFE AS
$func$
BEGIN
   RETURN QUERY
   SELECT * FROM dms.t_documents;
END
$func$

See:

• How to return result of a SELECT inside a function in PostgreSQL?

Makes very little sense, of course. Instead of calling that function you would just use SELECT * FROM dms.t_documents; directly.

Or use a simpler SQL function, where the result of a SELECT is returned automatically:

CREATE OR REPLACE FUNCTION dms.fngetdocs()
  RETURNS SETOF dms.t_documents
  LANGUAGE sql STABLE PARALLEL SAFE AS
$func$
SELECT * FROM dms.t_documents;
$func$;

See the manual about the added PARALLEL SAFE.