Recursive function in postgres based on query result

Question

I try to do recursive function in postgres. As an argument to the fucntion I provide id, then I need to query table to get detail information about it. One of columns parent_id has a tree structure. Lowwer numer are higher in a tree structure. I would like to ahead higher results in my tree structure. This is my sql query:

create or replace function provide_city_name(id_offer int)
returns int
language plpgsql
as
$$
declare
   city_name varchar;
begin
   select id, type, parent_id, name into city_name
   from location
   where id = id_offer;
   if type < 6 then
    return city_name;
   else
    return provide_city_name(parent_id);
   end if;
end;
$$;

I got following message: ':=', <analytic clause>, '=', WITHIN or '[' expected, got ';'. It occcured in lines with

else
provide_city_name(parent_id);

Answer 1

You can do it with no need to call function recursively (opening new cursor at each recursive call for each row), but use recursive CTE to get tabular result in batch.

db<>fiddle here

create function f_get_par_name(p_ids int[])
returns table(
  id int,
  name varchar(100)
)
language plpgsql
as $$
begin

return query

with recursive rec (id, parent_id, type_, name, lvl, is_leaf, orig_name) as (
  /*Start the recursion from requested nodes*/
  select l.id, l.parent_id, l.type_, l.name,
    0 as lvl,
    /*The node is leaf if it has no parent or has type = 6*/
    (l.type_ = 6 or l.parent_id is null) as is_leaf,
    l.name as orig_name
  from location as l
  where l.id = any(p_ids)
  
  union all
  
  select
    rec.id,
    l.parent_id,
    coalesce(l.type_, rec.type_),
    coalesce(l.name, rec.name),
    rec.lvl + 1,
    (l.type_ = 6 or l.parent_id is null) as is_final,
    rec.name
  from rec
    left join location as l
      on rec.parent_id = l.id
        and rec.type_ != 6
  /*New step is only for nodes which have parent*/
  where rec.is_leaf = false
    
)
select rec.id, rec.name
from rec
where rec.is_leaf = true;

end;
$$

With this setup

id	parent_id	type_	name
1	null	1	1
2	1	1	1.1
3	2	6	1.2
4	3	1	1.2.1
5	4	1	1.2.1.1
6	3	1	1.2.2
7	6	6	1.2.2.1
8	7	1	1.2.2.1.1
9	6	1	1.2.2.2
10	6	6	1.2.2.3

You'll get this:

select *
from f_get_par_name(array[1,3,5,7,9,10])
order by id asc

id | name   
-: | :------
 1 | 1      
 3 | 1.2    
 5 | 1.2    
 7 | 1.2.2.1
 9 | 1.2    
10 | 1.2.2.3

Answer 2

There are many problems with your function:

1. You are trying to return city_name which is of type varchar. But your function definition declares a return type int
2. You cannot put a single column output into a variable this way. The INTO clause puts the entire SELECT output into the variable, so this must be of type record in that case
3. You are fetching the type and other columns in your query but they are not stored for further usages. This is why type is currently unknown in your IF clause. So, here it makes sense, too, to store the output into a record variable, which enables you to re-use the value here.

Finally your function could look like this:

demo:db<>fiddle

create or replace function provide_city_name(id_offer int)
returns varchar                                       -- change output type
language plpgsql
as
$$
declare
   city_record record;                                -- declare record var
begin
   select id, type, parent_id, name 
   into city_record                                   -- put entire record into var
   from location
   where id = id_offer;
   
   if city_record.type < 6 then                       -- check type value of record
    return city_record.name;                          -- return name value of record
   else
    return provide_city_name(city_record.parent_id);  -- recursion with parent_id of record
   end if;
end;
$$;