(FRONT) FRONT (2024)

Recursive CTE Supabase function is key feature for built this blog.


Key feature of moving this blog to Cloud was a recursive CTE function, I have uploaded this function to Github https://github.com/Alex1998100/SupabaseUploaderAndCteFunction

For build site or blog we need to implement hierarchy folder/file structure in relative DB storage, like this.



As you can see on screen above - my blog has a couple of root folder, and on the screen you can see folder included to root with id "1M88r7TS5uZkDrWj-3SZ9AoQQtBXOfyPD", this is exactly implement this folder structure:



And we need a simple way to receive FileID for any complex file path like this "/root/folder1/folde2/ .... /foldern/file" with one call Supabase function.



There are only one way to solve this problem - recursive CTE function.


   1:  DROP FUNCTION path1(character varying,character varying);
   2:  CREATE
   3:  OR REPLACE FUNCTION path1 (
   4:    enter CHARACTER VARYING,
   5:    request CHARACTER VARYING
   6:  ) RETURNS TABLE (
   7:    reti INTEGER,
   8:    retid CHARACTER VARYING,
   9:    retname CHARACTER VARYING,
  10:    retmime CHARACTER VARYING,
  11:    retpath text
  12:  ) AS $$
  13:  BEGIN
  14:    RETURN QUERY
  15:    WITH RECURSIVE x AS (
  16:      SELECT i, parent, id, name, type, '' AS path
  17:      FROM entry 
  18:      WHERE name = enter
  19:      UNION ALL
  20:      SELECT e.i, e.parent, e.id, e.name, e.type, (x.path || '/' || e.name) AS path
  21:      FROM entry e, x 
  22:      WHERE x.id = e.parent
  23:    )
  24:    SELECT x.i AS reti, x.id AS retid, x.name AS retname, types.mime AS retmime, x.path AS retpath
  25:    FROM x 
  26:    JOIN types ON types.i = x.type
  27:    WHERE x.path = request;
  28:  END;
  29:  $$ LANGUAGE plpgsql;
  30:   
  31:  GRANT EXECUTE ON FUNCTION  path1(enter character varying, request character varying) TO public;

This way allow us to receive FileID with one database request for any complex path "/root/folder1/folde2/ .... /foldern/file".

I used CTE request in many my projects, look for example:



Comments ( )
Link to this page: http://www.vb-net.com/SupabaseCTE/Index.htm
< THANKS ME>