plpgsql - postgresql 9.1 invalid input syntax for type numeric -


i build function little complicated. complicated my standards. when execute code below, using command: select * populate_lt_downside_volatility( '02-sept-2014' , '05-sept-2014' , 5, 'df_1')

i these messages:

notice:  curr_sec_key: s5cond_index notice:  curr_anchor_date.price_date: 2014-09-02  error:  invalid input syntax type numeric: "s5cond_index   " context:  pl/pgsql function "populate_lt_downside_volatility" line 26 @ sql statement  ********** error **********  error: invalid input syntax type numeric: "s5cond_index   " sql state: 22p02 context: pl/pgsql function "populate_lt_downside_volatility" line 26 @ sql statement

have spent hours cannot figure out. kind assistance 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 $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 )              select price_date security_price  sec_key = curr_sec_key  , price_date >= sdate , price_date <= edate  ;      begin     foreach curr_sec_key in array  aseckey  loop    raise notice 'curr_sec_key: %', curr_sec_key ;    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 date in range.      select sec_key, src_key, price_date ,            lag( ln_return_day_over_day, look_back,  null )  on ( partition sec_key, src_key  order sec_key ,  price_date ) lookback_ln_return      security_price sp      sp.sec_key = curr_sec_key         ,  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 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 ) ) (   oids=false ); alter table security_price   owner postgres; 

select      sec_key, src_key, price_date,      lag( ln_return_day_over_day, look_back,  null )           on ( partition sec_key, src_key  order sec_key ,  price_date )  lookback_ln_return ...

variable lookback_ln_return of type numeric, while select returns row (i.e. value of pseudo-type record). cannot assign record numeric variable. in such case postgres tries assign first value of record, sec_key of type character(15). unfortunately sec_key not contain proper formatted numeric value, assignment cannot accomplished.


Comments

Post a Comment

Popular posts from this blog

c# - Validate object ID from GET to POST -

i have method allows user access comment have made on site. have post method lets them update comment. my normal solution send entire comment model through, let them update it, update on database when post back. involve sending commentid through in hiddenfor , can manipulated. how can verify commentid sent in get method same i'm getting in post , not able alter comment wish? how can verify commentid sent in method same i'm getting in post? basically have validate following things in post - user logged-in user. authenticated users post comments. commentid in post , should valid commentid , should present in database. userid associated logged-in user should same userid associated comment . comment should contain userid column, can check @ time of update. to make sure update happens comment has been sent in get - hold commentid in session , in post action compare commentid value in session .
Read more

node.js - Custom Model Validator SailsJS -

when using custom model validator checks record within model. return within spread doesn't seem end control flow. //post.js model /** * custom validator **/ types: { isuservalid: function(user_id) { var promise = require('bluebird'); promise.all([ user.findone({id: user_id}) ]) .spread(function(user) { console.log(user); if (user === null || user === undefined) { console.log('failed'); return false; }else{ console.log('passed'); return true; } }); } }, my response standard validation failed response. { "error": "e_validation", "status": 400, "summary": "1 attribute invalid", "model": "post", "invalidattributes": { "owner": [ { "rule": "isuservalid", ...
Read more

php - Find a regex to take part of Email -

i have following emails need extract part from: bestellnummer xxx: 1 von xxx, 1er pack ------------- anfang der nachricht ------------- foo bar baz foo bar baz // <<<<< need text here ------------- ende der nachricht ------------- ------------- anfang der nachricht ------------- foo bar baz foo bar baz ------------- ende der nachricht ------------- there 0 unlimited occurences of ------------- anfang der nachricht ------------- ------------- ende der nachricht ------------- and i'm able extract first part regex: $re = "/------------- .*? -------------.?(.*?).?------------- .*? -------------/s"; but, i'm quite new on learning regex, i'm pretty sure there must better regex extract part (foo bar baz foo bar baz) of text between ------------- anfang der nachricht ------------- ------------- ende der nachricht ------------- as can in different languages, i'm using .? to match between hyphens. i...
Read more