0

I have this situation:

I have a table called Airports that needs to have an array of Airlines representing what airlines have flight in said airport. Naturally I'd like to have every value of such array to match with an existing Airline.

Is there a way to accomplish this using arrays? And if so, what would be a clean way to do it?

papablo
  • 21
  • 4
  • 1
    Have you considered creating a `airlines_by_airport` mapping table? What would prevent you from doing so? See also https://stackoverflow.com/questions/41054507/postgresql-array-of-elements-that-each-are-a-foreign-key – thibautg May 06 '18 at 16:55
  • Take a look at this discussion about foreign keys and arrays: https://stackoverflow.com/questions/41054507/postgresql-array-of-elements-that-each-are-a-foreign-key – Vicctor May 06 '18 at 16:58
  • 1
    Thank you both! You refenced me to the same post :D – papablo May 06 '18 at 17:03

1 Answers1

0

I found a way to solve this, using a stored function that'll check if every element in the array correponds to an already existing Airline.

In code

-- tipo para aeropuerto
create type taeropuerto as (
    nombre varchar(90),
    ubicacion tubicacionAeorpuerto,
    medidas tmedidasPista,
    aerolineas oid[]
);

-- Aerolineas
create type taerolinea as (
    nombre varchar(100)
);

-- tabla correspondiente
create table aerolinea of taerolinea
(primary key(oid))
with oids;
-- function para chequear que cada aerolinea insertada sea correcta
create or replace function check_aerolineas(aerolineas oid[]) returns boolean as 
$$
declare
    aerolineas_tbl record;
    o oid;
    valido boolean;
begin
    valido = true;
    foreach o in array aerolineas loop
        valido = valido and (o in (select oid from aerolinea));
    end loop;
    return valido;
end;
$$ language plpgsql;

create table aeropuerto of taeropuerto (
    constraint aerolineas_check check(check_aerolineas(aerolineas))
);

In this way, each time you insert or update an airport, the airlines will be checked. Failing if there is a non existent airline in the array

papablo
  • 21
  • 4