D
D
dotruger372020-01-20 12:12:29
PHP
dotruger37, 2020-01-20 12:12:29

How to write a recursive function in PostgreSQL?

How to write a recursive function?
The function input is the movie id and the result is all the prequels for the movie.

spoiler
5e256ecccafe9155384671.png

Here is my version of the function, only it outputs all 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

Answer the question

In order to leave comments, you need to log in

3 answer(s)
S
Stalker_RED, 2019-04-12
@VicTHOR

(?<!^)(\p{Lu})/gumreplace with " $1"(space at the beginning)
https://regex101.com/r/nM45ZQ/1
Demo: https://ideone.com/cSH2eU

I
irishmann, 2020-01-20
@irishmann

https://habr.com/ru/post/269497/

Q
QuickJoey, 2020-01-22
@QuickJoey

Here vvs.ru/pg a person regularly builds pgAdmin 3 for new versions. At the moment, everything works without errors with PostgreSQL version 12.1

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question