I am writing a function that requires some comparison between the difference date and time parts of the timestamp data type. However it keeps running into syntax errors and I cannot find out what is going on. I looked into this post and keep having alternating errors like
psql:ERROR: operator does not exist: time without time zone >= timestamp with time zone
plpgsql: No function matches the given name and argument types. You might need to add explicit type casts
Can anyone enlighten me what is wrong and what could be a direction I can head towards?
create or replace function riders_available_at_date_time(specified timestamp) returns table(riderId integer) as $$ begin select F.riderId from FullTimeRiders F where specified::date between F.startDate and F.startDate+27 and exists ( select 1 from MWSWorkDays M join ShiftsInformation S on (S.shiftNumber = M.shift) where M.riderId = F.riderId and (specified::time without time zone between to_timestamp(to_char(firstStartHour, '99'), 'HH24') and to_timestamp(to_char(firstEndHour, '99'), 'HH24')) or (specified::time without time zone between to_timestamp(to_char(secondStartHour, '99'), 'HH24') and to_timestamp(to_char(secondEndHour, '99'), 'HH24')) ) union select riderId from PartTimeWorkingHourIntervals P where specified between calculate_start_date(P.dayIndex, P.startDate)+P.startHour and calculate_start_date(P.dayIndex, P.startDate) + P.endHour; end; $$ language plpgsql; create or replace function calculate_start_date(index int, starting date) returns date as $$ declare position integer; begin select extract(dow from starting::date) into position; index = (index - position)%7; return starting + index; end; $$ language plpgsql;