I'm trying to create a function in postgresql, Some functions work and other genertes an error, The code is:
Create or replace function CalcScore(Drive_name varchar,FromPlanet varchar, ToPlanet varchar) returns integer as $$
declare
driverLoc Locations%ROWTYPE;
fromLoc Locations%ROWTYPE;
toLoc Locations%ROWTYPE;
plantDist integer;
driverDist integer;
begin
SELECT * FROM Location INTO driverLoc
WHERE Name=Drive_name;
SELECT * FROM Location INTO fromLoc
WHERE Name=FromPlanet;
SELECT * FROM Location INTO toLoc
WHERE Name=ToPlanet;
plantDist :=floor(sqrt(
(fromLoc.X - toLoc.X)*(fromLoc.X - toLoc.X) +
(fromLoc.Y - toLoc.Y)*(fromLoc.Y - toLoc.Y)+
(fromLoc.Z - toLoc.Z)*(fromLoc.Z - toLoc.Z)
));
driverDist :=floor(sqrt(
(fromLoc.X - driverLoc.X)*(fromLoc.X - driverLoc.X) +
(fromLoc.Y - driverLoc.Y)*(fromLoc.Y - driverLoc.Y)+
(fromLoc.Z - driverLoc.Z)*(fromLoc.Z - driverLoc.Z)
));
return planetDist-DriverDist;
end;
$$language plpgsql;
The error I get is:
57: ERROR: syntax error at or near "Create"
LINE 25: Create or replace function CalcScore(Drive_name varchar,From...
I get error for other functions as well. The Table 'Location' exist, any idea?
Locations%ROWTYPE(locations - with "s" at the end), whileSELECT * FROM Location INTOstatements reference Location table (without "s").return planetDist-DriverDist;referencesPlan**e**tDistwhile declaration saysplantDist integer;(without "e")