DECLARE
symbolz character varying(10);
i int;
buy numeric;
sell numeric;
total_1min numeric;
total_1h numeric;
total_24h numeric;
total_1m_vs_total_24h numeric;
total_1h_vs_total_24h numeric;
total_week numeric;
total_percent_net_vol_1h_vs_24h numeric;
total_percent_net_vol_1min_vs_vo_l24h numeric;
total_percent_net_vol_1min_vs_1h numeric;
total_full_1min numeric;
total_full_24h numeric;
total_full_1h numeric;
tablenamefor_full text = 'public.ticker2' ;
tablenamefor text = 'ticker2' ;
tablenamefor_col text = 'symbol';
tablename1 text;
curs1 refcursor;
len int;
ttt text;'
BEGIN
EXECUTE 'SELECT reltuples::bigint AS EstimatedCount FROM  pg_class
WHERE  oid = '||quote_literal(tablenamefor_full)||'::regclass' into len;
FOR i IN 1..len LOOP OPEN curs1 FOR EXECUTE 'SELECT '||quote_ident(tablenamefor_col)||
' from '||quote_ident(tablenamefor)||
'  order by '||quote_ident(tablenamefor_col)||'  ASC';
FETCH ABSOLUTE i FROM curs1 INTO symbolz;
tablename1 = 'ctbh_trades_'||lower(symbolz);
IF NOT EXISTS (SELECT * FROM pg_tables WHERE TABLENAME=tablename1) THEN
NULL;
ELSE

EXECUTE 'SELECT COALESCE(SUM(quantity),0) sum
FROM '||quote_ident(tablename1)||' 
WHERE '||quote_ident(tablename1)||'.timestamp >=
CURRENT_TIMESTAMP - interval ''1 minutes'' and '||quote_ident(tablename1)||'.side = ''buy''' into buy;
EXECUTE 'SELECT COALESCE(SUM(quantity),0) sum
FROM '||quote_ident(tablename1)||' 
WHERE '||quote_ident(tablename1)||'.timestamp >=
CURRENT_TIMESTAMP - interval ''1 minutes'' and '||quote_ident(tablename1)||'.side = ''sell''' into sell;
total_1min = buy - sell;
total_full_1min = buy + sell;

EXECUTE 'SELECT COALESCE(SUM(quantity),0) sum
FROM '||quote_ident(tablename1)||' 
WHERE '||quote_ident(tablename1)||'.timestamp >=
CURRENT_TIMESTAMP - interval ''1 hour'' and '||quote_ident(tablename1)||'.side = ''buy''' into buy;
EXECUTE 'SELECT COALESCE(SUM(quantity),0) sum
FROM '||quote_ident(tablename1)||' 
WHERE '||quote_ident(tablename1)||'.timestamp >=
CURRENT_TIMESTAMP - interval ''1 hour'' and '||quote_ident(tablename1)||'.side = ''sell''' into sell;
total_1h = buy - sell;
total_full_1h = buy + sell;

EXECUTE 'SELECT COALESCE(SUM(quantity),0) sum
FROM '||quote_ident(tablename1)||' 
WHERE '||quote_ident(tablename1)||'.timestamp >=
CURRENT_TIMESTAMP - interval ''24 hour'' and '||quote_ident(tablename1)||'.side = ''buy''' into buy;
EXECUTE 'SELECT COALESCE(SUM(quantity),0) sum
FROM '||quote_ident(tablename1)||' 
WHERE '||quote_ident(tablename1)||'.timestamp >=
CURRENT_TIMESTAMP - interval ''24 hour'' and '||quote_ident(tablename1)||'.side = ''sell''' into sell;
total_24h = buy - sell;
total_full_24h = buy + sell;

total_1m_vs_total_24h = total_1min - total_24h;

total_1h_vs_total_24h = total_1h - total_24h;

EXECUTE 'SELECT COALESCE(SUM(quantity),0) sum
FROM '||quote_ident(tablename1)||' 
WHERE '||quote_ident(tablename1)||'.timestamp >=
CURRENT_TIMESTAMP - interval ''168 hour'' and '||quote_ident(tablename1)||'.side = ''buy''' into buy;
EXECUTE 'SELECT COALESCE(SUM(quantity),0) sum
FROM '||quote_ident(tablename1)||' 
WHERE '||quote_ident(tablename1)||'.timestamp >=
CURRENT_TIMESTAMP - interval ''168 hour'' and '||quote_ident(tablename1)||'.side = ''sell''' into sell;
total_week = buy - sell;


total_percent_net_vol_1h_vs_24h  = round(COALESCE((total_1h * 100) / NULLIF((total_1h - total_24h),0), 0), 2);


total_percent_net_vol_1min_vs_vo_l24h = round(COALESCE((total_1min * 100) / NULLIF((total_1min - total_24h),0), 0) ,2);


total_percent_net_vol_1min_vs_1h = round(COALESCE((total_1min * 100) / NULLIF((total_1min - total_1h),0), 0) , 2);

UPDATE ctbh_atotal SET  net_vol_1min = total_1min, net_vol_1h = total_1h,
timestamp = current_timestamp, net_vol_24h = total_24h, net_vol1min_vs_vol_24h = total_1m_vs_total_24h, 
net_vol1h_vs_vol_24h = total_1h_vs_total_24h, net_vol_week = total_week,
percent_net_vol_1h_vs_vol_24h = total_percent_net_vol_1h_vs_24h,
percent_net_vol_1min_vs_vo_l24h = total_percent_net_vol_1min_vs_vo_l24h,
percent_net_vol_1min_vs_1h = total_percent_net_vol_1min_vs_1h,
total_vol_1min = total_full_1min, total_vol_24h = total_full_24h  WHERE symbol = symbolz;
    IF found THEN
  ELSE
        INSERT INTO 
        ctbh_atotal(symbol,net_vol_1min,net_vol_1h,timestamp,net_vol_24h,
                        net_vol1min_vs_vol_24h,net_vol1h_vs_vol_24h,
                        net_vol_week,percent_net_vol_1h_vs_vol_24h,
                        percent_net_vol_1min_vs_vo_l24h,percent_net_vol_1min_vs_1h,
                        total_vol_1min,total_vol_24h) 
        VALUES (symbolz, total_1min, total_1h, current_timestamp, total_24h, total_1m_vs_total_24h,
               total_1h_vs_total_24h, total_week, total_percent_net_vol_1h_vs_24h,
               total_percent_net_vol_1min_vs_vo_l24h, total_percent_net_vol_1min_vs_1h,
               total_full_1min, total_full_24h);
       END IF;
        END IF;
    CLOSE curs1;
END LOOP;
END;'

EXECUTE 'CREATE INDEX index_'||quote_ident(tablename1)||' ON '||quote_ident(tablename1)||' (id, timestamp)';