SQL/PSQL function to get latest update from inner joined table-CodePudding

I have 2 tables: drivers and drivers_locations. I want to create a computed field in the drivers table that returns the last inserted location of a driver.

The drivers_locations table has these columns:

- id: uuid
- driver_id: uuid
- location: geography
- timestamp: bigint

The timestamp is in milliseconds since the unix epoch. (e.g. 1673129623999).

Here is my noobish failed attempt at achieving this:

CREATE OR REPLACE FUNCTION public.fn_driver_last_location(driver_row drivers)
    RETURNS geometry
 LANGUAGE sql
 IMMUTABLE
AS $function$
    SELECT loc.location 
    FROM driver_locations loc
    inner join drivers drv
    on loc.driver_id  = drv.id
    group by loc.location_recorded_time_unix, loc.location, drv.id
    HAVING loc.location_recorded_time_unix = MAX(loc.location_recorded_time_unix) and drv.id = driver_row.id;
$function$
;;

Any ideas?

CodePudding user response：

You can sort the locations by the time and limit the result to one row. You also don't need the join.

CREATE OR REPLACE FUNCTION public.fn_driver_last_location(driver_row drivers)
 RETURNS geometry
 LANGUAGE sql
 STABLE
AS $function$
    SELECT loc.location
    FROM driver_locations loc
    where loc.driver_id = driver_row.id
    order by loc.location_recorded_time_unix desc 
    limit 1;
$function$
;

A function that selects from a database tables should never be declared as immutable as it can return different results even when called with the same parameter.