How to convert a recursive query to a function?

Question

I have written a PostgreSQL query that's working perfectly:

WITH RECURSIVE x AS (
   SELECT i, parent, id, name, type, '' AS path
   FROM entry 
   WHERE name = 'JS-VBNET-2'
   UNION ALL
   SELECT e.i, e.parent, e.id, e.name, e.type, (x.path || '/' || e.name) AS path
   FROM entry e, x 
   WHERE x.id = e.parent
   )
SELECT x.i as reti, x.id as retid, x.name as retname, types.mime as retmime, x.path as retpath
FROM x 
JOIN types ON types.i = x.type
WHERE x.path = '/Index.htm';

I tried to convert this query to a function:

-- DROP FUNCTION path1(character varying,character varying);
CREATE OR REPLACE FUNCTION path1(enter character varying, request character varying)
  RETURNS TABLE (
    reti INTEGER,
    retid character varying,
    retname character varying,
    retmime character varying,
    retpath character varying
  ) AS $$
BEGIN
   WITH RECURSIVE x AS (
      SELECT i, parent, id, name, type, '' AS path
      FROM entry 
      WHERE name = enter
      UNION ALL
      SELECT e.i, e.parent, e.id, e.name, e.type, (x.path || '/' || e.name) AS path
      FROM entry e, x 
      WHERE x.id = e.parent
   )
   SELECT x.i as reti, x.id as retid, x.name as retname, types.mime as retmime, x.path as retpath
   FROM x 
   JOIN types ON types.i = x.type
   WHERE x.path = request;
END;
$$ LANGUAGE plpgsql;

GRANT EXECUTE ON FUNCTION  path1(enter character varying, request character varying) TO public;

Call:

select * from public.PATH1 ('JS-VBNET-2', '/Index.htm');

But I get an error:

ERROR:  42601: 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 path1(character varying,character varying) line 3 at SQL statement

AI hint is:

Add a destination for the result data, such as using INTO or returning the results in a SELECT statement.

What is going wrong? How I can convert my function correctly?

Answer 1

Correct workable functions is

  DROP FUNCTION path1(character varying,character varying);
  CREATE
  OR REPLACE FUNCTION path1 (
    enter CHARACTER VARYING,
    request CHARACTER VARYING
  ) RETURNS TABLE (
    reti INTEGER,
    retid CHARACTER VARYING,
    retname CHARACTER VARYING,
    retmime CHARACTER VARYING,
    retpath text
  ) AS $$
  BEGIN
    RETURN QUERY
    WITH RECURSIVE x AS (
      SELECT i, parent, id, name, type, '' AS path
      FROM entry 
      WHERE name = enter
      UNION ALL
      SELECT e.i, e.parent, e.id, e.name, e.type, (x.path || '/' || e.name)           AS path
 FROM entry e, x 
 WHERE x.id = e.parent
    )
    SELECT x.i AS reti, x.id AS retid, x.name AS retname, types.mime AS      retmime, x.path AS retpath
    FROM x 
    JOIN types ON types.i = x.type
    WHERE x.path = request;
  END;
  $$ LANGUAGE plpgsql;
  GRANT EXECUTE ON FUNCTION  path1(enter character varying, request character varying) TO public;

Key feature of changing is strange, when PostgreSQL combined character varying fields - result is "text", not character varying.
and strange response was because I miss "Return Query" before CTE definition

Answer 2

Immediate issue

The immediate issue was the missing RETURN QUERY. Your answer already fixes that. Wouldn't be worth another answer. See:

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

Much faster solution

Your query is hugely expensive to begin with. It generates all possible paths for the given entry before filtering the request (if it's there). Doesn't show in your over-simplified example with a single node, but the cost is multiplied with every additional node.

Only values from the leaf row are returned, the rest of the path just has to be there. This can be checked with a simple recursive function:

CREATE OR REPLACE FUNCTION public.f_path_exists (_id int, _nodes text[])
  RETURNS bool
  LANGUAGE sql STABLE PARALLEL SAFE AS
$func$
SELECT _nodes = '{}'
    OR (
   SELECT public.f_path_exists (e.parent, _nodes[2:])
   FROM   entry e
   WHERE  e.id = _id
   AND    e.name = _nodes[1]
   )
$func$;

This follows the nodes of the path (backwards) and returns true if if found a match for every step - when the array is empty '{}' eventually. This way we only follow the path for matching names, eliminating the rest immediately.

Main function:

CREATE OR REPLACE FUNCTION public.path1(_enter text, _request text)
  RETURNS TABLE (reti int
               , retid text
               , retname text
               , retmime text
               , retpath text
                )
  LANGUAGE sql STABLE PARALLEL SAFE STRICT AS
$func$
WITH input(nodes) AS (  -- inverted array of request nodes
   SELECT ARRAY (
      SELECT node
      FROM   string_to_table(_enter || _request, '/') WITH ORDINALITY r(node, ord)
      ORDER  BY ord DESC
      )
   )
SELECT e.i, e.id, e.name, t.mime, _request
FROM   input i
JOIN   entry e ON e.name = i.nodes[1]
JOIN   types t ON t.i = e.type
AND    public.f_path_exists(e.parent, i.nodes[2:]);
$func$;

Call:

SELECT * FROM public.path1('root', '/to/Index.htm');

fiddle

The main function can be replaced by executing the nested SELECT directly. I kept the function wrapper to make this a drop-in replacement.

_entry could just be merged as first node of the _request. Ideally, you call with the array of inverted nodes, and strip preprocessing in the CTE input entirely. But, again, I kept your signature.

Related answer with recursive function:

• How to turn a set of flat trees into a single tree with multiple leaves?

Aside: By replacing your PL/pgSQL functions with SQL functions, the original error also goes away, coincidentally.