Postgres 15

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
 CREATE TABLE IF NOT EXISTS public."Coor"
(
    id integer NOT NULL GENERATED BY DEFAULT AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1 ),
    name Text COLLATE pg_catalog."default",
    lat numeric,
     lng numeric,
    CONSTRAINT pk__coor PRIMARY KEY ( id)
);
   
INSERT INTO "Coor"
    ( "name", "lat", "lng")
VALUES
    ( '"Chennai"', 13.08, 80.27),
    ( '"Kochi"', 9.93, 76.26),
    ( '"Mysore"', 12.29, 76.63)
;

CREATE TABLE



INSERT 0 3


 CREATE TABLE IF NOT EXISTS public."distance"
(
     
   fromname TEXT COLLATE pg_catalog."default",
    toname TEXT COLLATE pg_catalog."default",
     distancemiles float,
     diskm  float 
)

CREATE TABLE


CREATE OR REPLACE FUNCTION calculate_distance
  (fromname  Text ,lat1 float, lon1 float, toname text,lat2 float, lon2 float, units varchar)
RETURNS SETOF "distance" AS $$
    DECLARE
        dist float = 0;
  distkm float = 0;
  distMiles float = 0;
        radlat1 float;
        radlat2 float;
        theta float;
        radtheta float;
    BEGIN
        IF lat1 = lat2 AND lon1 = lon2
            THEN RETURN QUERY SELECT fromname,toname,distMiles,distkm ;
        ELSE
            radlat1 = pi() * lat1 / 180;
            radlat2 = pi() * lat2 / 180;
            theta = lon1 - lon2;
            radtheta = pi() * theta / 180;
            dist = sin(radlat1) * sin(radlat2) + cos(radlat1) * cos(radlat2) * cos(radtheta);

            IF dist > 1 THEN dist = 1; END IF;

            dist = acos(dist);
            dist = dist * 180 / pi();
            dist = dist * 60 * 1.1515;

            distkm = dist * 1.609344; 
            distMiles = dist * 0.8684;

            RETURN QUERY SELECT fromname,toname ,distMiles,distkm  ;
        END IF;
    END;
$$ LANGUAGE plpgsql;

CREATE FUNCTION


create or replace function fn_list() 
  returns table(fromname Text,toname Text,distMiles Float,distkm Float) 
as $$
begin
  -- do some work....
  return query
WITH CTE As ( 
  SELECT c1.name as Fromname, c1.lat lat1, c1.lng lon1,
  c2.name toname, c2. lat lat2, c2.lng lon2
  FROM "Coor" c1 JOIN "Coor" c2 ON c1.id < c2.id)
SELECT CTE.Fromname, CTE.toname, 
  acos(sin(pi() * lat1 / 180) * sin(pi() * lat2 / 180) 
  + cos(pi() * lat1 / 180) * cos(pi() * lat2 / 180) * cos(pi() * (lon1 - lon2) / 180)) * 180 / pi() * 60 * 1.1515 * 0.8684 as distancemiles
  ,  acos(sin(pi() * lat1 / 180) * sin(pi() * lat2 / 180) 
  + cos(pi() * lat1 / 180) * cos(pi() * lat2 / 180) * cos(pi() * (lon1 - lon2) / 180)) * 180 / pi() * 60 * 1.1515 * 1.609344 as distanceKm 
FROM CTE;
end;
$$ language plpgsql;

CREATE FUNCTION


SELECT * FROM fn_list()


fromname
toname
distmiles
distkm


"Chennai"
"Kochi"
302.13074782483
559.9174415331682

"Chennai"
"Mysore"
218.26626129516504
404.49734916836263

"Kochi"
"Mysore"
143.26021902027512
265.49398194261363



SELECT 3


SELECT * FROM  calculate_distance(13.08, 80.27, 12.29, 76.63, 'M');
SELECT * FROM calculate_distance(13.08, 80.27, 12.29, 76.63, 'K');

ERROR:  function calculate_distance(numeric, numeric, numeric, numeric, unknown) does not exist
LINE 1: SELECT * FROM  calculate_distance(13.08, 80.27, 12.29, 76.63...
                       ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
fromname	toname	distmiles	distkm
"Chennai"	"Kochi"	302.13074782483	559.9174415331682
"Chennai"	"Mysore"	218.26626129516504	404.49734916836263
"Kochi"	"Mysore"	143.26021902027512	265.49398194261363