postgresql 9.1 invalid input syntax for type numeric

Question

I am build a function that is a little complicated. Complicated my my standards. When I execute the code below, using this command: select * from populate_lt_downside_volatility( '02-Sept-2014' , '05-Sept-2014' , 5, 'df_1')

I get these messages:

NOTICE:  curr_sec_key: S5COND_INDEX
NOTICE:  curr_anchor_date.price_date: 2014-09-02

ERROR:  invalid input syntax for type numeric: "S5COND_INDEX   "
CONTEXT:  PL/pgSQL function "populate_lt_downside_volatility" line 26 at SQL statement

********** Error **********

ERROR: invalid input syntax for type numeric: "S5COND_INDEX   "
SQL state: 22P02
Context: PL/pgSQL function "populate_lt_downside_volatility" line 26 at SQL statement

Have spent hours but cannot figure it out. Your kind assistance is requested.

KD

Function source code:

-- Function: populate_lt_downside_volatility(date, date, integer, character)

CREATE OR REPLACE FUNCTION populate_lt_downside_volatility(start_date date, end_date date, look_back integer, df_series_in character)
  RETURNS numeric AS
$BODY$
DECLARE

   ---curr anchor_date     date;
   curr_sec_key         character(15);  
   rtn_ds_vol           numeric(20,12);
   max_pricedate        date;
   lookback_ln_return   numeric(20,12);
   today_date           date  :=  CURRENT_DATE ; 


   aSecKey character(15)[]  :=  array['S5COND_INDEX' ,  'S5CONS_INDEX' , 'S5ENRS_INDEX', 'S5FINL_INDEX', 'S5HLTH_INDEX', 'S5INDU_INDEX', 'S5INFT_INDEX',  'S5MATR_INDEX' ,  'S5TELS_INDEX' , 'S5UTIL_INDEX' ]  ;
   curPriceDates CURSOR  (curr_sec_key  character(15),  sDate date, eDate Date )  IS
            SELECT price_date from security_price where  sec_key = curr_sec_key  and price_date >= sDate and price_date <= eDate  ;   


BEGIN   

FOREACH curr_sec_key in ARRAY  aSecKey  LOOP
   raise notice 'curr_sec_key: %', curr_sec_key ;
   for curr_anchor_date  IN  curPriceDates( curr_sec_key , start_date,  end_date ) LOOP 

     raise notice 'curr_anchor_date.price_date: %', curr_anchor_date.price_date ;

     --  check if for this date to look back is in range.
     select sec_key, src_key, price_date , 
          LAG( ln_return_day_over_day, look_back,  null )  OVER ( PARTITION BY sec_key, src_key  ORDER BY sec_key ,  price_date ) into lookback_ln_return
     from security_price sp
     where sp.sec_key = curr_sec_key

       and  sp.price_date =  curr_anchor_date.price_date ;

     raise notice 'lookback_ln_return: %', lookback_ln_return; 


  End LOOP ;


END LOOP;


END   ;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION populate_lt_downside_volatility(date, date, integer, character)
  OWNER TO postgres;

Table definition:

CREATE TABLE security_price
(
  sec_key character(15) NOT NULL,
  price_date date NOT NULL,
  open_price numeric(18,8),
  high_price numeric(18,8),
  low_price numeric(18,8),
  last_price numeric(18,8),
  close_price numeric(18,8),
  src_key character(15),
  prior_open numeric(18,8),
  prior_last numeric(18,8),
  ln_open_close_t1 numeric(22,12),
  ln_high_low numeric(22,12),
  ln_close_open numeric(22,12),
  hl_vol_sum_term numeric(22,12),
  ln_return_day_over_day numeric(22,12),
  CONSTRAINT pk_security_price PRIMARY KEY (sec_key , price_date )
)
WITH (
  OIDS=FALSE
);
ALTER TABLE security_price
  OWNER TO postgres;

Answer 1

select 
    sec_key, src_key, price_date, 
    LAG( ln_return_day_over_day, look_back,  null )  
        OVER ( PARTITION BY sec_key, src_key  ORDER BY sec_key ,  price_date ) 
into lookback_ln_return
...

Variable lookback_ln_return is of type numeric, while select returns a row (i.e. a value of pseudo-type record). You cannot assign a record to a numeric variable. In such a case Postgres tries to assign the first value of the record, sec_key of type character(15). Unfortunately sec_key does not contain proper formatted numeric value, so the assignment cannot be accomplished.