Deductive functionality within PostgreSQL
An attempt to create something that reads like Prolog in PostgreSQL.
More information
During my database course, we had to implement a variety of interesting use cases: time series databases, geospatial databases and deductive databases, as well as others.
What I did
After realizing that MySQL might not be the best suited solution for this, instead I went with PostgreSQL. Its more advanced procedural language and custom data type support promised to make doing most things a bit more palatable, but in practice still meant struggling against how most SQL should be written. You see, trying to create a deductive database within a general purpose RDBMS, entirely within procedural SQL code ended up creating a bit of a Frankenstein's monster, that only was sort of like what I was aiming for.
What I learnt
Regardless, this was a useful learning experience. For example, I created a solution that'd simulate a submarine: whether there's enough air in the room, whether there's no water and whether the temperature is good. Then, as the crew would move throughout it, you could query this environmental information.
While it was a fun domain, the system was still a bit of a mess. Did you know that you can return queries from functions? What about complicated type casts? What about LATERAL JOINs? It was interesting, if nothing else:
do $$
begin
drop function if exists is_crewman_ok(varchar);
create function is_crewman_ok(identity_crewman varchar)
returns setof is_crewman_ok_result as $func$
begin
return query
select
(
(air::is_room_air_ok_result).result = true
and (water::is_room_water_ok_result).result = true
and (temperature::is_room_temperature_ok_result).result = true
) as result,
concat(crewman.first_name, ' ', crewman.last_name)::varchar as identity_crewman,
crewman.*::crewmen,
room.*::rooms,
(water::is_room_water_ok_result).value_water as value_water,
(air::is_room_air_ok_result).value_air as value_air
from
crewmen crewman
inner join rooms room
on crewman.id_room = room.id,
lateral (select is_room_air_ok(room.name)) as aliased(air),
lateral (select is_room_water_ok(room.name)) as aliased2(water),
lateral (select is_room_temperature_ok(room.name)) as aliased3(temperature)
where
concat(crewman.first_name, ' ', crewman.last_name) similar to case
when identity_crewman = '_'
then '%'
else '%(' || identity_crewman || ')%'
end;
end; $func$ language 'plpgsql';
end
$$;