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:
- 2024 My ticket lottery engine based on SQL CTE procedure
- 2022 SQL CTE expression to obtain Docker parent image
- 2011 Хранение в MS SQL маршрутной топологии и отборы маршрутов рекурсивными CTE-процедурами
Cloudflare context:
JavascriptProjects context:
Sql context:
Supabase context:
)
|
|