DatabaseProgramming: GetFlightCrew.sql

create function GetFlightCrew(
    FlightID_P int
) returns table (
    EmployeeId int,
    Name varchar(50),
    Surname varchar(50),
    DateOfBirth date,
    Role text
) as $$
begin
    return query
    select employee.id, employee.name, employee.surname, employee.dateofbirth, 'Pilot'
    from pilot
    join employee on pilot.employeeid = employee.id
    join flight on flight.pilot = pilot.employeeid
    where flight.id = FlightID_P

    union all

    select employee.id, employee.name, employee.surname, employee.dateofbirth, 'Copilot'
    from pilot
    join employee on pilot.employeeid = employee.id
    join flight on flight.copilot = pilot.employeeid
    where flight.id = FlightID_P

    union all

    select employee.id, employee.name, employee.surname, employee.dateofbirth, 'Flight Attendant'
    from cabincrew
    join employee on cabincrew.flightattendantid = employee.id
    join flight on cabincrew.flightid = flight.id
    where flight.id = FlightID_P;
end;
$$ language plpgsql;