--TEST 1
CREATE TABLE p839.image_average AS
SELECT ST_MapAlgebra(ARRAY[ROW(rast, 1)]::rastbandarg[], 'st_mean4ma(double precision[][][], integer[][], text[])'::regprocedure, '32BF', null, null, 1,1
            ) as rast
    FROM p839.image_normal;
CREATE INDEX idx_image_average_rast ON p839.image_average USING GIST (ST_ConvexHull(rast));
--TIME = 51:40
--ARTIFACT TILES PRESENT = TRUE

--TEST 2
CREATE TABLE p839.image_average AS
SELECT ST_MapAlgebra(ARRAY[ROW(rast, 1)]::rastbandarg[], 'ImageAnalysis_Average(float[][][], text, text[])'::regprocedure, '32BF', null, null, 1,1
            ) as rast
    FROM p839.image_normal;
CREATE INDEX idx_image_average_rast ON p839.image_average USING GIST (ST_ConvexHull(rast));
--TIME = 23:53
--ARTIFACT TILES PRESENT = TRUE

--TEST 3
CREATE TABLE p839.image_average AS
SELECT ST_MapAlgebra(ARRAY[ROW(ST_Union(rast), 1)]::rastbandarg[], 'ImageAnalysis_Average(float[][][], text, text[])'::regprocedure, '32BF', null, null, 1,1
            ) as rast
    FROM p839.image_normal;
CREATE INDEX idx_image_average_rast ON p839.image_average USING GIST (ST_ConvexHull(rast));
--TIME = MEMORY ALLOCATION ERROR
--ARTIFACT TILES PRESENT = N/A


-- THE CUSTOM IMAGE AVERAGE FUNCTION
CREATE OR REPLACE FUNCTION imageanalysis_average(
    IN matrix double precision[],
    IN nodatamode text,
    VARIADIC args text[])
  RETURNS double precision AS
$BODY$
    DECLARE
    _matrix float[][];
        sum float;
    BEGIN
    _matrix := matrix;
        sum := 0;
        FOR x in array_lower(matrix, 1)..array_upper(matrix, 1) LOOP
            FOR y in array_lower(matrix, 2)..array_upper(matrix, 2) LOOP
                sum := sum + _matrix[1][x][y];
            END LOOP;
        END LOOP;
        RETURN (sum*1.0/(array_upper(matrix,1)*array_upper(matrix,2) ))::float ;
    END;
    $BODY$
  LANGUAGE plpgsql IMMUTABLE
  COST 100;