How to convert recursive PostgreSQL request to function - Stack Overflow

I have write PostgreSQL query what working perfectly:

I try to convert this query to 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;

and call it

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

But I get trouble

 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 going wrong? How I can convert my function correctly?

I have write PostgreSQL query what working perfectly:

I try to convert this query to 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;

and call it

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

But I get trouble

 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 going wrong? How I can convert my function correctly?

• postgresql

Share Improve this question Follow edited Nov 16, 2024 at 7:17 Alex161 asked Nov 15, 2024 at 19:43 Alex161Alex161 7199 bronze badges 6

• 1 You need to be careful with terms, a Procedure is a different thing then a Function. Also an SQL procedure/function is different then what you are using which is plpgsql. Edit the question to get your terms correct. – Adrian Klaver Commented Nov 15, 2024 at 19:50
• Thanks @AdrianKlaver. Is this question correct now? But this term not solve my problem. I can not convert query to Function and can not upload Function to Supabase, because Table is absent as parameters in Supabase uploading function panel. – Alex161 Commented Nov 15, 2024 at 21:02
• I don't use Supabase so I can't check my self. The YT video here Supabase Functions shows creation of functions directly in the SQL editor. Might take a look and see if that would work. – Adrian Klaver Commented Nov 15, 2024 at 21:18
• Thanks, @AdrianKlaver. It's critical advice that SQL editor doing the same that dedicated Supabase form – Alex161 Commented Nov 16, 2024 at 6:15
• I don't understand what that means? – Adrian Klaver Commented Nov 16, 2024 at 16:13

 |  Show 1 more comment

1 Answer 1

Sorted by: Reset to default 0

So, firstly, Supabase SQL editor allow create function, don't need to use special form "New Function" in Supabase. Function visible in Supabase locally and remotelly:

And secondary, after some experiment I understand troubles. 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