I am trying to create a CTE which establishes a parent child relationship and return the ids of the required childs. The Childs can also be the parent of their respective children. I have tried writing a postgresql function for that but I am facing an error, for which I am not able to understand the cause. Might be a very silly reason or syntax error, so far I have no clue. The error is 'ERROR: syntax error at or near "return" LINE 22: return query ^ SQL state: 42601 Character: 472'
create or replace function fn_Get_All_Child_For_Process(
client_id integer,process_id integer,level integer)
returns table (id integer)
language 'plpgsql'
as
$$
begin
WITH CTE_Child AS
(
SELECT Id,level,ParentId
FROM ClientProcesses
WHERE ID = process_id and ClientId=client_id and Level < level
UNION ALL
SELECT t.Id,t.level,t.ParentId
FROM ClientProcesses t,CTE_Child C
where t.ParentID = C.ID and t.Level < level and t.ClientId=client_id
)
return query
SELECT C.Id FROM CTE_Child C where C.Id not in (process_id) ;
end;
$$;
return query
needs to go before theWITH
. But you don't need PL/pgSQL for simple wrapper around a SELECT - a_horse_with_no_name