syntax error at or near "VARCHAR" LINE 1: CREATE OR REPLACE FUNCTION DATEADD (interval VARCHAR(4000),

Question

please help me to solve this error following is my code and after below my code error also written

CREATE OR REPLACE FUNCTION DATEADD (interval VARCHAR(4000), adding INT, entry_date TIMESTAMP(0))  
  RETURN TIMESTAMP(0) as 
$body$ 
declare 
  result TIMESTAMP(0); 
BEGIN 
   If (UPPER(interval) = 'D') OR (UPPER(interval) = 'Y') OR (UPPER(interval) ='W') OR (UPPER(interval) = 'DD') OR (UPPER(interval) = 'DDD') OR (UPPER(interval) = 'DAY')  THEN result := entry_date + adding; ELSIF (UPPER(interval) = 'WW') OR (UPPER(interval) = 'IW')  OR (UPPER(interval) = 'WEEK') THEN result := entry_date + (adding * 7); ELSIF  (UPPER(interval) = 'YYYY') OR (UPPER(interval) = 'YEAR') THEN result := add_months(entry_date,adding * 12); ELSIF (UPPER(interval) = 'Q') OR (UPPER(interval) = 'QUARTER') 
     THEN result := add_months(entry_date,adding * 3); 
   ELSIF(UPPER(interval)= 'M') OR (UPPER(interval) = 'MM') OR (UPPER(interval) = 'MONTH') THEN 
     result :=add_months(entry_date,adding); 
   ELSIF  (UPPER(interval) = 'H') OR (UPPER(interval) = 'HH') OR (UPPER(interval) = 'HOUR') THEN 
     result := entry_date+ (adding /24); 
   ELSIF  (UPPER(interval) = 'N') OR (UPPER(interval) = 'MI') OR (UPPER(interval) = 'MINUTE') THEN 
      result := entry_date+ (adding /24/60); 
   ELSIF  (UPPER(interval) = 'S') OR (UPPER(interval) = 'SS') OR (UPPER(interval) = 'SECOND') THEN 
      result := entry_date + (adding /24/60/60); 
   END IF; 
   RETURN result; 
exception when others then 
   raise_application_error('-20000',sqlerrm); 
end; 
$body$ 
LANGUAGE plpgsql;

ERROR: syntax error at or near "VARCHAR" LINE 1: CREATE OR REPLACE FUNCTION DATEADD (interval VARCHAR(4000), ...

Answer 1

interval is a reserved keyword (a data type), you need to either use double quotes "interval" or find a different name, e.g. you can use add_interval instead.

You can add a number to a timestamp in Postgres. You need to construct an interval from the passed value. The easiest way is to use make_interval() for that.

In PL/pgSQL it's returns XXX not return XXX.

You also need to replace raise_application_error('-20000',sqlerrm) with something else. But I don't see any benefit in obfuscating the original error, so my recommendation would be to simply remove the exception handler completely.

You can also simplify your IF conditions by using IN instead of multiple OR expressions.

So you wind up with something like this:

CREATE OR REPLACE FUNCTION DATEADD (add_type text, adding INT, entry_date TIMESTAMP(0))  
  RETURNS TIMESTAMP(0) as 
$body$ 
declare 
  result TIMESTAMP(0); 
BEGIN 
   If upper(add_type) IN ('D','Y','W','DD','DDD','DAY') THEN 
     result := entry_date + make_interval(days => adding); 
   ELSIF upper(add_type) IN ('WW', 'IW', 'WEEK') THEN 
     result := entry_date + make_interval(weeks => adding); 
   ELSIF  upper(add_type) IN ('YYYY', 'YEAR') THEN 
     result := add_months(entry_date, adding * 12); 
   ELSIF upper(add_type) IN ('Q', 'QUARTER') THEN  
     result := add_months(entry_date, adding * 3); 
   ELSIF upper(add_type) IN ('M', 'MM', 'MONTH') THEN 
     result := add_months(entry_date, adding); 
   ELSIF upper(add_type) IN ('H', 'HH', 'HOUR') THEN 
     result := entry_date + make_interval(hours => adding); 
   ELSIF upper(add_type) IN ('N', 'MI', 'MINUTE') THEN 
      result := entry_date + make_interval(minutes => adding);
   ELSIF upper(add_type) IN ('S', 'SS', 'SECOND') THEN 
      result := entry_date + make_interval(secs => adding); 
   END IF; 
   RETURN result; 
end; 
$body$ 
LANGUAGE plpgsql;