sql - 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);

question from:https://stackoverflow.com/questions/65952122/recursive-function-in-postgres-based-on-query-result

Answer 1

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;
$$;