Thread: Cast varchar to interval in plpgsql

Cast varchar to interval in plpgsql

From
"Sergei Pohilko"
Date:
Hi,

I need to create a PL/pgSQL function to add months  (something like this):

CREATE
Function        ADD_MONTHS
  ( timestamp,                         -- start date
    int4  )                                -- number of months (>0 or <0)
RETURNS  timestamp           -- new date
AS '
DECLARE
   v_arg1    ALIAS FOR   $1;
   v_arg2    ALIAS FOR   $2;
   v_line2      varchar(100);
   v_qq         timestamp;
BEGIN
      v_line2   := ltrim(to_char(v_arg2,''999999''),'' '')||'' month'';

       select v_arg1+v_line2::interval
       into v_qq;

    RETURN v_qq;
END; -- Function ADD_MONTHS
' LANGUAGE 'plpgsql';
----------------------------------------

But get error message when try to call it:

>select add_months(now(),3);

>Cannot cast type 'varchar' to 'interval'


Any ideas?

Thanks.
Sergei Pohilko