How to write a recursive function in PostgreSQL?

How to write a recursive function?
The function is supplied the id of the film and the result displays all the prequels for the film.
spoiler
5e256ecccafe9155384671.png

Here is my version of the function, but it displays all the movies.
spoiler
create or replace function get_all_prequils(filmid int)
returns table (id int, filmname varchar prequelid int) as $$
 with recursive rec as (
 select f.FilmID, f.FilmName, f.PrequelID
 from Film as f
 where f.FilmID = filmid

union

 select r.FilmID, r.FilmName, r.PrequelID
 from rec as r
 join Film f on f.PrequelID = r.FilmID
)
select *
from rec
$$ language sql;

select get_all_prequils(1);

spoiler
5e256faa7df3c061224175.png
April 4th 20 at 12:59
2 answers
April 4th 20 at 13:01
The desired function is - Peter_Goodwin commented on April 4th 20 at 13:04
@Peter_Goodwin, Take a recursive query and make it function. None of your work will not make. At the link find info. - german.Roh commented on April 4th 20 at 13:07
@german.Roh, I have a recursive query, I rewrite a function, but it displays all the movies

create or replace function get_all_prequils(filmid int)
returns table (int filmid, filmname varchar prequelid int) as $$
 with recursive rec (filmid, filmname, prequelid) as (
 select f.FilmID, f.FilmName, f.PrequelID
 from Film as f
 where f.FilmID = filmid

 union all

 select Film.FilmID, Film.FilmName, Film.PrequelID
 from Film inner join rec
 on (Film.FilmID = rec.prequelid)
)
select *
from rec
$$ language sql;
- Peter_Goodwin commented on April 4th 20 at 13:10
use different names of the function parameters, fields/tables and output arguments. To do without adventures. - Mercedes51 commented on April 4th 20 at 13:13
@Mercedes51, it's just the same with work. Write to the Council separately, I'll mark as a solution - Peter_Goodwin commented on April 4th 20 at 13:16
April 4th 20 at 13:03
Here vvs.ru/pg people regularly collects pgAdmin 3 for new versions. At the moment, it works without errors with the version of PostgreSQL 12.1

Find more questions by tags PostgreSQLRecursion