1

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?

2
  • Variable declarations use Locations%ROWTYPE (locations - with "s" at the end), while SELECT * FROM Location INTO statements reference Location table (without "s"). Commented Dec 2, 2013 at 12:56
  • Another error: return planetDist-DriverDist; references Plan**e**tDist while declaration says plantDist integer; (without "e") Commented Dec 2, 2013 at 13:00

1 Answer 1

1

There are two errors in this code:

Variable declarations use Locations%ROWTYPE (location*s* - with "s" at the end),
while SELECT * FROM Location INTO statements reference Location table (without "s").

return planetDist-DriverDist; references Plan**e**tDist
while declaration says plantDist integer; (without "e")



After fixing these two errors the function compiles and works fine,
see this demo: http://www.sqlfiddle.com/#!1/38d68/1

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 Locations INTO driverLoc
    WHERE Name=Drive_name;
    SELECT * FROM Locations INTO fromLoc
    WHERE Name=FromPlanet;
    SELECT * FROM Locations 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 plantDist-DriverDist;
    end;
    $$language plpgsql/
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.