DatabaseProgramming: CertificationExpiry.sql

create or replace function CertificationExpiry(
    DaysUntil int
) returns table (
    EmployeeId int,
    Name varchar(50),
    Surname varchar(50),
    Active bool,
    CertificationId int,
    CertificationType text,
    AirplaneModel varchar(100),
    ExpirationDate date,
    DaysUntilExpiration int,
    IsExpired bool
) as $$
begin
    if DaysUntil < 0 then
        raise exception 'Invalid number of days!';
    end if;

    return query
    select s.EmployeeId, s.name, s.Surname, s.Active,
           s.CertificationId, s.CertificationType, s.AirplaneModel, s.ExpirationDate,
           s.DaysUntilExpiration, s.IsExpired
    from certificationexpirystatus as s
    where s.DaysUntilExpiration <= DaysUntil and s.active = true
    order by s.DaysUntilExpiration, s.EmployeeId;
end;
$$ language plpgsql;