Tengo dos mesas. free_time y appointment . Me gustaría restar todos los registros de citas de los registros de tiempo libre. El resultado sería un conjunto de registros en los que queda tiempo libre. Se garantiza que las citas existen dentro del rango de free_time. Me gustaría hacer esta consulta WHERE doctor_id = 1 .
Tienen los siguientes registros:
CREATE TABLE free_time AS SELECT freetime::tsrange, doctor_id FROM ( VALUES ('[2017-04-19 09:00, 2017-04-19 12:30)', 1), ('[2017-04-19 13:30, 2017-04-19 15:30)', 1), ('[2017-04-20 08:30, 2017-04-20 16:30)', 1), ('[2017-04-19 09:00, 2017-04-19 16:30)', 2) ) AS t(freetime, doctor_id); CREATE TABLE appointment AS SELECT appointment::tsrange, doctor_id FROM ( VALUES ('[2017-04-19 10:30, 2017-04-19 11:30)', 1), ('[2017-04-19 13:30, 2017-04-19 14:30)', 1), ('[2017-04-20 10:30, 2017-04-20 13:30)', 1), ('[2017-04-20 14:30, 2017-04-20 16:30)', 1), ('[2017-04-19 10:30, 2017-04-19 11:30)', 2) ) AS t(appointment, doctor_id);El conjunto de resultados debería ser algo como:
["2017-04-19 09:00:00","2017-04-19 10:30:00"), ["2017-04-19 11:30:00","2017-04-19 12:30:00"), ["2017-04-19 14:30:00","2017-04-19 15:30:00"), ["2017-04-20 08:30:00","2017-04-20 10:30:00"), ["2017-04-20 13:30:00","2017-04-20 14:30:00"),Hay dos funciones (de mi respuesta anterior con una solución menor):
create or replace function range_exclude(anyelement, anyelement) returns anyarray as $$ declare r1 text; r2 text; begin -- Check input parameters if not pg_typeof($1) in ('numrange'::regtype, 'int8range'::regtype, 'daterange'::regtype, 'tsrange'::regtype, 'tstzrange'::regtype) then raise exception 'Function accepts only range types but got % type.', pg_typeof($1); end if; -- If result is single element if ($1 &< $2 or $1 &> $2) then return array[$1 - $2]; end if; -- Else build array of two intervals if lower_inc($1) then r1 := '['; else r1 := '('; end if; r1 := r1 || lower($1) || ',' || lower($2); if lower_inc($2) then r1 := r1 || ')'; else r1 := r1 || ']'; end if; if upper_inc($2) then r2 := '('; else r2 := '['; end if; r2 := r2 || upper($2) || ',' || upper($1); if upper_inc($1) then r2 := r2 || ']'; else r2 := r2 || ')'; end if; return array[r1, r2]; end $$ immutable language plpgsql; create or replace function range_exclude(anyelement, anyarray) returns anyarray as $$ declare i int; j int; begin -- Check input parameters if not pg_typeof($1) in ('numrange'::regtype, 'int8range'::regtype, 'daterange'::regtype, 'tsrange'::regtype, 'tstzrange'::regtype) then raise exception 'Function accepts only range types but got % type.', pg_typeof($1); end if; if array_length($2,1) is null then return array[$1]; end if; $0 := range_exclude($1,$2[array_lower($2,1)]); for i in array_lower($2,1) + 1 .. array_upper($2,1) loop select array(select x from (select unnest(range_exclude(x,$2[i])) from unnest($0) as t(x)) as t(x) where not isempty(x)) into $0; end loop; return $0; end $$ immutable language plpgsql;Después de eso, su consulta podría ser:
with t as ( select ft.doctor_id, freetime, range_exclude(freetime, array_agg(appointment)) as ex from free_time ft join appointment ap on (ft.doctor_id = ap.doctor_id) group by ft.doctor_id, freetime) select doctor_id, unnest(ex) from t order by 1,2;Resultado:
╔═══════════╤═════════════════════════════════════ ══════════╗ ║ doctor_id │ anular ║ ╠═══════════╪═════════════════════════════════════ ══════════╣ ║ 1 │ ["2017-04-19 09:00:00","2017-04-19 10:30:00") ║ ║ 1 │ ["2017-04-19 11:30:00","2017-04-19 12:30:00") ║ ║ 1 │ ["2017-04-19 14:30:00","2017-04-19 15:30:00") ║ ║ 1 │ ["2017-04-20 08:30:00","2017-04-20 10:30:00") ║ ║ 1 │ ["2017-04-20 13:30:00","2017-04-20 14:30:00") ║ ║ 2 │ ["2017-04-19 09:00:00","2017-04-19 10:30:00") ║ ║ 2 │ ["2017-04-19 11:30:00","2017-04-19 16:30:00") ║ ╚═══════════╧═════════════════════════════════════ ══════════╝