0
votes

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 the WITH. But you don't need PL/pgSQL for simple wrapper around a SELECT - a_horse_with_no_name