Postgres 16

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create extension postgis;
CREATE TABLE driver_orders (
  id SERIAL PRIMARY KEY,
  car_id INTEGER NOT NULL,
  start_point text,
  end_point text,
  location_from GEOGRAPHY  -- generated
    GENERATED ALWAYS AS
    (('POINT'||start_point)::geography)
      stored,
  location_to GEOGRAPHY    -- generated
    GENERATED ALWAYS AS
    (('POINT'||end_point)::geography)
      stored
);

CREATE EXTENSION



CREATE TABLE


CREATE OR REPLACE FUNCTION create_driver_order(car_id INTEGER,
                                               s_point VARCHAR,
                                               e_point VARCHAR)
  RETURNS TABLE (id INTEGER, 
                 location_from GEOGRAPHY, 
                 location_to GEOGRAPHY)
  LANGUAGE plpgsql AS $f$
DECLARE
    active_order_count INTEGER;
BEGIN
    -- 1. count active orders
    --     ... omitted unrelated parts
    -- 2. check if active orders are less than two
    --     ... omitted unrelated parts

    -- 4. create order
    RETURN QUERY INSERT INTO driver_orders AS d (
        car_id,
        start_point,
        end_point
      ) VALUES (
        car_id,
        s_point,
        e_point
      ) RETURNING d.id, 
                  d.location_from, 
                  d.location_to;
END $f$;

CREATE FUNCTION


select * from create_driver_order(
    889,
  '(5581326278118 29.220418907676738)'::VARCHAR,
  '(5581326274318 29.220548907676738)'::VARCHAR
)


id
location_from
location_to


1
0101000020E610000000000000000045C042E49F5F6D383D40
0101000020E61000000000000000805D40DEA3A9E475383D40



SELECT 1