DatabaseProgramming: AddBaggage.sql

create procedure AddBaggage(
    TicketId int,
    BaggageTypeId int,
    Weight int,
    Height int,
    Length int,
    Width int
)
as $$
declare
    FlightID_P int;
    MaxWeight_P int;
    MaxHeight_P int;
    MaxLength_P int;
    MaxWidth_P int;
begin
    select maxweight, maxheight, maxlength, maxwidth
    into MaxWeight_P, MaxHeight_P, MaxLength_P, MaxWidth_P
    from baggagetype
    where id = BaggageTypeId;

    select flight.id
    into FlightID_P
    from flight
    join ticket on flight.id = ticket.flightid
    where ticket.id = TicketId;

    if exists(select * from canceledflight where flightid = FlightID_P) then
        raise exception 'Flight has been canceled!';
    end if;

    if not FOUND then
        raise exception 'Invalid Baggage Type!';
    end if;

    if Weight > MaxWeight_P then
        raise exception 'Invalid weight!';
    end if;

    if Height > MaxHeight_P then
        raise exception 'Invalid height!';
    end if;

    if Length > MaxLength_P then
        raise exception 'Invalid length!';
    end if;

    if Width > MaxWidth_P then
        raise exception 'Invalid width!';
    end if;

    insert into baggage (baggagetypeid, weight, height, length, width, ticketid)
    values (AddBaggage.BaggageTypeId, AddBaggage.Weight,
            AddBaggage.Height, AddBaggage.Length, AddBaggage.Width, AddBaggage.TicketId);
end;
$$ language plpgsql;