Syntax Error in PostgresSQL function definition

by SPillai   Last Updated October 14, 2018 18:06 PM

The following is the body of a Postgres function I'm trying to make

 -- logic to convert 53N 1E 8 to 4530108 and 53 N1 W8 to 2530108
  DECLARE 
        dir integer;
    "twn" ALIAS FOR $1;
    "range" ALIAS FOR $2;
    "section" ALIAS FOR $3;
BEGIN
  IF "range" LIKE '%E' THEN
    SET dir=4; 
    SET "range"=CAST(trim(both 'E' from "range") as varchar(5));
  ELSE
        SET dir=2; 
    SET "range"=trim(both 'W' from "range");
  END IF
  SET twn = trim(both 'N' from twn);
  IF CAST("range" AS integer) < 10
    SET "range" = '0' || "range"
  END IF
  IF CAST("section" AS integer) < 10
    SET "section" = '0' || "section"
  END IF
  IF CAST(twn AS integer) < 10
    SET twn = '0' || twn
  END IF
  RETURN dir || twn || "range" || "section";
END;

I get an error on the first CAST, note that I will be using that CAST in all the SET statements that use trim()...but the error is on the first CAST currently. Params are 3 varchar(5) return is varchar.



Related Questions





Array of template type in PL/pgSQL function using %TYPE

Updated February 25, 2016 01:02 AM