SQLize Online / PHPize Online  /  SQLtest Online

CREATE TABLE reservation_location_entity( location_id uuid PRIMARY KEY, capacity integer NOT NULL, reservation_time INTEGER NOT NULL ); CREATE TABLE reservation_hours_entity( location_id uuid, dotw INTEGER, opening_time time without time zone NOT NULL, closing_time time without time zone NOT NULL, PRIMARY KEY(location_id, dotw, opening_time) ); CREATE TYPE reservation_status_enum AS ENUM ('open', 'accepted', 'denied'); CREATE TABLE reservation_entity( id uuid PRIMARY KEY, location_id uuid NOT NULL, starttime timestamp without time zone NOT NULL, guest_name CHARACTER VARYING(255) NOT NULL, guest_phone CHARACTER VARYING(255) NOT NULL, guest_email CHARACTER VARYING(255) NOT NULL, guest_count INTEGER NOT NULL, status reservation_status_enum NOT NULL ); INSERT INTO reservation_location_entity VALUES ( 'd3885069-4690-4587-a693-5bb724867335', 20, 50 ), ( '3cdf40b9-030d-45f8-9a5f-e17ee9545edd', 10, 90 ); SELECT * from reservation_location_entity; INSERT INTO reservation_hours_entity VALUES ( '3cdf40b9-030d-45f8-9a5f-e17ee9545edd', 1, '9:00', '23:00' ), ( '3cdf40b9-030d-45f8-9a5f-e17ee9545edd', 2, '9:00', '23:00' ); SELECT * FROM reservation_hours_entity; INSERT INTO reservation_entity VALUES ( '693c7af9-2f66-4810-9764-636eecec2441', '3cdf40b9-030d-45f8-9a5f-e17ee9545edd', '2022-02-28 12:00:00', 'bla', '0175 bla', 'bla@bla', 5, 'open' ), ( '32413a05-f7d4-4df8-b5ad-d1c592bfef29', '3cdf40b9-030d-45f8-9a5f-e17ee9545edd', '2022-02-28 12:15:00', 'bla', '0175 bla', 'bla@bla', 2, 'accepted' ), ( 'b04fe287-ce84-4def-99c8-6e42c6b048d8', '3cdf40b9-030d-45f8-9a5f-e17ee9545edd', '2022-02-28 11:45:00', 'bla', '0175 bla', 'bla@bla', 8, 'accepted' ), ( '5a2710ff-6257-4edb-b0a0-01e987abf36f', '3cdf40b9-030d-45f8-9a5f-e17ee9545edd', '2022-02-27 11:45:00', 'bla', '0175 bla', 'bla@bla', 20, 'accepted' ); SELECT * FROM reservation_entity; CREATE FUNCTION get_reservation_load(timestamp) RETURNS TABLE (location_id uuid, load int) AS $$ SELECT location_id, sum(guest_count) FROM reservation_entity JOIN reservation_location_entity USING (location_id) WHERE status = 'accepted' AND NOT ( starttime > ( $1 + reservation_time * interval '1 minute' ) OR $1 > ( starttime + reservation_time * interval '1 minute' ) ) GROUP BY location_id $$ LANGUAGE SQL; CREATE OR REPLACE FUNCTION get_reservation_load(timestamp) RETURNS TABLE (location_id uuid, load int) AS $$ SELECT location_id, sum(guest_count) FROM reservation_entity JOIN reservation_location_entity USING (location_id) WHERE status = 'accepted' AND NOT ( starttime, starttime + reservation_time * interval '1 minute' ) OVERLAPS ( $1, $1 + reservation_time * interval '1 minute' ) GROUP BY location_id $$ LANGUAGE SQL; CREATE FUNCTION get_reservation_load_of(timestamp, uuid) RETURNS integer AS $$ SELECT load FROM get_reservation_load($1) WHERE location_id = $2 $$ LANGUAGE SQL; SELECT * FROM reservation_entity JOIN reservation_location_entity USING (location_id) WHERE NOT ( starttime, starttime + reservation_time * interval '1 minute' ) OVERLAPS ( timestamp '2022-02-28 12:45:00', timestamp '2022-02-28 12:45:00' + reservation_time * interval '1 minute' ); SELECT * FROM get_reservation_load('2022-02-28 12:45:00'); SELECT * FROM reservation_entity JOIN reservation_location_entity USING (location_id) WHERE status = 'accepted' AND ( starttime, starttime + reservation_time * interval '1 minute' ) OVERLAPS ( timestamp '2022-02-28 12:45:00', timestamp '2022-02-28 12:45:00' + reservation_time * interval '1 minute' );