Having trouble getting a "WITH" query to run as a function
I have a query stored in my SQL editor that returns the data correctly. It's a recursive join, returning the chain of ancestor Tasks using the "parent" foreign key (which also points to a Task Id in the Task table):
WITH RECURSIVE task_tree
AS(
-- anchor member
SELECT id, parent FROM "public"."Task" WHERE id = 5
UNION
-- recursive term
SELECT t.id, t.parent
FROM "public"."Task" t
INNER JOIN task_tree tt on t.id = tt.parent
)
SELECT * FROM task_tree;
I would like to set this up as a function that takes in a Task Id parameter, but I can't seem to get it to work in plpgsql. I'm not especially familiar with plpgsql, but I get a syntax error on "WITH". Is this not supported in plpgsql or in supabase?
WITH RECURSIVE task_tree
AS(
-- anchor member
SELECT id, parent FROM "public"."Task" WHERE id = 5
UNION
-- recursive term
SELECT t.id, t.parent
FROM "public"."Task" t
INNER JOIN task_tree tt on t.id = tt.parent
)
SELECT * FROM task_tree;
I would like to set this up as a function that takes in a Task Id parameter, but I can't seem to get it to work in plpgsql. I'm not especially familiar with plpgsql, but I get a syntax error on "WITH". Is this not supported in plpgsql or in supabase?
