Postgres user-defined aggregate function does not correctly assign value

Question

I am fairly new to using postgresql and have attempted to write my first user-defined aggregate function. The select function sends the aggregate function the past 20 values for each row. The aggregate function provides an initial values in the form of a numeric array of length 4 {counter, 'null', down, up}. The null value is where the previous value will be stored during iterations. The 'down' and 'up' are counters and initialized to zero.

The state transition function compares the current value to the previous value. If the current value is higher, then add the difference to up. If lower, add the differ to down. The new array is passed to the next iteration where the current value is now the old value (second value in the array) and the counters for up and down should be passed.

The problem seems to be somewhere in the IF statements. If I increment the "down" or "up" by 1, the values seem to be passed to the next iteration. If I add the difference " + diff", the values for "down" and "up" are zero and never seem to change.

Code that only increments values by 1:

CREATE OR REPLACE FUNCTION RSI_STATE(rsi_mat NUMERIC[], close_val numeric)
    RETURNS NUMERIC[]
LANGUAGE plpgsql
AS $$
DECLARE
    downs INTEGER;
    ups INTEGER;
    counter INTEGER;
    cl NUMERIC;
    diff NUMERIC;
BEGIN
    downs := rsi_mat[3];
    ups := rsi_mat[4];
    diff := abs(rsi_mat[2] - close_val);
    RAISE NOTICE 'Beginning Variable seed: %,  close: %,  down: %,  up: %,  diff: %', 
 rsi_mat[2], close_val, downs, ups, diff;

    IF rsi_mat[2] > close_val then
        RAISE NOTICE 'IF LESS down: %,  up: %,  diff: %', downs, ups, diff;
        downs := rsi_mat[3] + 1;
    ELSEIF rsi_mat[2] < close_val then
        RAISE NOTICE 'IF GREATER down: %,  up: %,  diff: %', downs, ups, diff;
        ups := rsi_mat[4] + 1;
    END IF;
    cl := close_val;
    counter := rsi_mat[1] + 1;
    RAISE NOTICE 'End Variable down: %,  up: %,  diff: %', downs, ups, diff;
    RETURN ARRAY[counter,cl, downs, ups];
END;
$$;

CREATE OR REPLACE FUNCTION RSI_FINAL(rsi_mat numeric[])
RETURNS NUMERIC
LANGUAGE PLPGSQL
AS $$
DECLARE
     RS NUMERIC;
BEGIN
     IF rsi_mat[3] = 0 then
         RS = 1000000;
     ELSE
         RS = rsi_mat[4]/rsi_mat[3];
     END IF;
     return (100 - (100/(1 + RS))); 
END;
$$;

SELECT RSI((dailyhist.dhist::json->>'close')::numeric)
OVER(PARTITION BY dailyhist.cusip ORDER BY dailyhist.datetime ROWS BETWEEN 19 PRECEDING AND CURRENT ROW) AS RSI
FROM dailyhist
limit 15

CREATE AGGREGATE RSI(NUMERIC)(
     SFUNC = RSI_STATE,
     STYPE = NUMERIC[],
     FINALFUNC = RSI_FINAL,
     INITCOND = '{1,null,0,0}'
);

Results when adding 1 which seems to update values on each iteration:

NOTICE: Beginning Variable seed: <NULL>, close: 3.37517, down: 0, up: 0, diff: <NULL> 
NOTICE: End Variable down: 0, up: 0, diff: <NULL> 
NOTICE: Beginning Variable seed: 3.37517, close: 3.41684, down: 0, up: 0, diff: 0.04167 
NOTICE: IF GREATER down: 0, up: 0, diff: 0.04167 
NOTICE: End Variable down: 0, up: 1, diff: 0.04167 
NOTICE: Beginning Variable seed: 3.41684, close: 3.396, down: 0, up: 1, diff: 0.02084 
NOTICE: IF LESS down: 0, up: 1, diff: 0.02084 
NOTICE: End Variable down: 1, up: 1, diff: 0.02084 
NOTICE: Beginning Variable seed: 3.396, close: 3.41684, down: 1, up: 1, diff: 0.02084 
NOTICE: IF GREATER down: 1, up: 1, diff: 0.02084 
NOTICE: End Variable down: 1, up: 2, diff: 0.02084 
NOTICE: Beginning Variable seed: 3.41684, close: 3.45851, down: 1, up: 2, diff: 0.04167 
NOTICE: IF GREATER down: 1, up: 2, diff: 0.04167 
NOTICE: End Variable down: 1, up: 3, diff: 0.04167 
NOTICE: Beginning Variable seed: 3.45851, close: 3.41684, down: 1, up: 3, diff: 0.04167 
NOTICE: IF LESS down: 1, up: 3, diff: 0.04167 
NOTICE: End Variable down: 2, up: 3, diff: 0.04167 
NOTICE: Beginning Variable seed: 3.41684, close: 3.29183, down: 2, up: 3, diff: 0.12501 
NOTICE: IF LESS down: 2, up: 3, diff: 0.12501 
NOTICE: End Variable down: 3, up: 3, diff: 0.12501 
NOTICE: Beginning Variable seed: 3.29183, close: 3.29183, down: 3, up: 3, diff: 0.00000 
NOTICE: End Variable down: 3, up: 3, diff: 0.00000 
NOTICE: Beginning Variable seed: 3.29183, close: 3.54184, down: 3, up: 3, diff: 0.25001 
NOTICE: IF GREATER down: 3, up: 3, diff: 0.25001 
NOTICE: End Variable down: 3, up: 4, diff: 0.25001 
NOTICE: Beginning Variable seed: 3.54184, close: 3.83352, down: 3, up: 4, diff: 0.29168 
NOTICE: IF GREATER down: 3, up: 4, diff: 0.29168 
NOTICE: End Variable down: 3, up: 5, diff: 0.29168 
NOTICE: Beginning Variable seed: 3.83352, close: 3.89603, down: 3, up: 5, diff: 0.06251 
NOTICE: IF GREATER down: 3, up: 5, diff: 0.06251 
NOTICE: End Variable down: 3, up: 6, diff: 0.06251 
NOTICE: Beginning Variable seed: 3.89603, close: 3.83352, down: 3, up: 6, diff: 0.06251 
NOTICE: IF LESS down: 3, up: 6, diff: 0.06251 
NOTICE: End Variable down: 4, up: 6, diff: 0.06251 
NOTICE: Beginning Variable seed: 3.83352, close: 3.83352, down: 4, up: 6, diff: 0.00000 
NOTICE: End Variable down: 4, up: 6, diff: 0.00000 
NOTICE: Beginning Variable seed: 3.83352, close: 4.04187, down: 4, up: 6, diff: 0.20835 
NOTICE: IF GREATER down: 4, up: 6, diff: 0.20835 
NOTICE: End Variable down: 4, up: 7, diff: 0.20835 
NOTICE: Beginning Variable seed: 4.04187, close: 4.25021, down: 4, up: 7, diff: 0.20834 
NOTICE: IF GREATER down: 4, up: 7, diff: 0.20834 
NOTICE: End Variable down: 4, up: 8, diff: 0.20834

Successfully run. Total query runtime: 274 msec. 15 rows affected.

Problems occurs when trying to add the difference. The IF statement has been changed from +1 to + diff:

CREATE OR REPLACE FUNCTION RSI_STATE(rsi_mat NUMERIC[], close_val numeric)
    RETURNS NUMERIC[]
LANGUAGE plpgsql
AS $$
DECLARE
    downs INTEGER;
    ups INTEGER;
    counter INTEGER;
    cl NUMERIC;
    diff NUMERIC;
BEGIN
    downs := rsi_mat[3];
    ups := rsi_mat[4];
    diff := abs(rsi_mat[2] - close_val);
    RAISE NOTICE 'Beginning Variable seed: %,  close: %,  down: %,  up: %,  diff: %', rsi_mat[2], close_val, downs, ups, diff;

    IF rsi_mat[2] > close_val then
        RAISE NOTICE 'IF LESS down: %,  up: %,  diff: %', downs, ups, diff;
        downs := rsi_mat[3] + diff;
    ELSEIF rsi_mat[2] < close_val then
        RAISE NOTICE 'IF GREATER down: %,  up: %,  diff: %', downs, ups, diff;
        ups := rsi_mat[4] + diff;
    END IF;
    cl := close_val;
    counter := rsi_mat[1] + 1;
    RAISE NOTICE 'End Variable down: %,  up: %,  diff: %', downs, ups, diff;
    RETURN ARRAY[counter,cl, downs, ups];
END;
$$;

Results show the values for up and down do not change:

NOTICE:  Beginning Variable seed: <NULL>,  close: 3.37517,  down: 0,  up: 0,  diff: <NULL>
NOTICE:  End Variable down: 0,  up: 0,  diff: <NULL>
NOTICE:  Beginning Variable seed: 3.37517,  close: 3.41684,  down: 0,  up: 0,  diff: 0.04167
NOTICE:  IF GREATER down: 0,  up: 0,  diff: 0.04167
NOTICE:  End Variable down: 0,  up: 0,  diff: 0.04167
NOTICE:  Beginning Variable seed: 3.41684,  close: 3.396,  down: 0,  up: 0,  diff: 0.02084
NOTICE:  IF LESS down: 0,  up: 0,  diff: 0.02084
NOTICE:  End Variable down: 0,  up: 0,  diff: 0.02084
NOTICE:  Beginning Variable seed: 3.396,  close: 3.41684,  down: 0,  up: 0,  diff: 0.02084
NOTICE:  IF GREATER down: 0,  up: 0,  diff: 0.02084
NOTICE:  End Variable down: 0,  up: 0,  diff: 0.02084
NOTICE:  Beginning Variable seed: 3.41684,  close: 3.45851,  down: 0,  up: 0,  diff: 0.04167
NOTICE:  IF GREATER down: 0,  up: 0,  diff: 0.04167
NOTICE:  End Variable down: 0,  up: 0,  diff: 0.04167
NOTICE:  Beginning Variable seed: 3.45851,  close: 3.41684,  down: 0,  up: 0,  diff: 0.04167
NOTICE:  IF LESS down: 0,  up: 0,  diff: 0.04167
NOTICE:  End Variable down: 0,  up: 0,  diff: 0.04167
NOTICE:  Beginning Variable seed: 3.41684,  close: 3.29183,  down: 0,  up: 0,  diff: 0.12501
NOTICE:  IF LESS down: 0,  up: 0,  diff: 0.12501
NOTICE:  End Variable down: 0,  up: 0,  diff: 0.12501
NOTICE:  Beginning Variable seed: 3.29183,  close: 3.29183,  down: 0,  up: 0,  diff: 0.00000
NOTICE:  End Variable down: 0,  up: 0,  diff: 0.00000
NOTICE:  Beginning Variable seed: 3.29183,  close: 3.54184,  down: 0,  up: 0,  diff: 0.25001
NOTICE:  IF GREATER down: 0,  up: 0,  diff: 0.25001
NOTICE:  End Variable down: 0,  up: 0,  diff: 0.25001
NOTICE:  Beginning Variable seed: 3.54184,  close: 3.83352,  down: 0,  up: 0,  diff: 0.29168
NOTICE:  IF GREATER down: 0,  up: 0,  diff: 0.29168
NOTICE:  End Variable down: 0,  up: 0,  diff: 0.29168
NOTICE:  Beginning Variable seed: 3.83352,  close: 3.89603,  down: 0,  up: 0,  diff: 0.06251
NOTICE:  IF GREATER down: 0,  up: 0,  diff: 0.06251
NOTICE:  End Variable down: 0,  up: 0,  diff: 0.06251
NOTICE:  Beginning Variable seed: 3.89603,  close: 3.83352,  down: 0,  up: 0,  diff: 0.06251
NOTICE:  IF LESS down: 0,  up: 0,  diff: 0.06251
NOTICE:  End Variable down: 0,  up: 0,  diff: 0.06251
NOTICE:  Beginning Variable seed: 3.83352,  close: 3.83352,  down: 0,  up: 0,  diff: 0.00000
NOTICE:  End Variable down: 0,  up: 0,  diff: 0.00000
NOTICE:  Beginning Variable seed: 3.83352,  close: 4.04187,  down: 0,  up: 0,  diff: 0.20835
NOTICE:  IF GREATER down: 0,  up: 0,  diff: 0.20835
NOTICE:  End Variable down: 0,  up: 0,  diff: 0.20835
NOTICE:  Beginning Variable seed: 4.04187,  close: 4.25021,  down: 0,  up: 0,  diff: 0.20834
NOTICE:  IF GREATER down: 0,  up: 0,  diff: 0.20834
NOTICE:  End Variable down: 0,  up: 0,  diff: 0.20834

Successfully run. Total query runtime: 226 msec.
15 rows affected.

I'm not sure if the issue is logic, syntax, or boneheaded-ness.

Laurenz Albe · Accepted Answer · 2020-03-04 07:27:12Z

1

Your mistake is to define ups and downs as integer. This way, the difference is rounded to an integer on assignment.

Declare the variables as numeric, and your problem will be solved.

answered Mar 4, 2020 at 7:27

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

rlm Over a year ago

yes this was my problem, so simple but just needed another set of eyes. Thank you so much.

Collectives™ on Stack Overflow

Postgres user-defined aggregate function does not correctly assign value

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related