Created
January 23, 2012 22:01
-
-
Save andrewxhill/1665767 to your computer and use it in GitHub Desktop.
Sum PostgreSQL arrays in place [1,2,3] + [2,3,4] = [3,5,7]
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
CREATE AGGREGATE vector_sum ( | |
sfunc = AXH_Vector_Sum, | |
basetype = int[], | |
stype = int[], | |
initcond = '{0}' | |
); | |
DROP FUNCTION IF EXISTS AXH_Vector_Sum(int[], int[]); | |
CREATE OR REPLACE FUNCTION AXH_Vector_Sum(int[], int[]) | |
RETURNS int[] AS $$ | |
DECLARE | |
arr1 ALIAS FOR $1; | |
arr2 ALIAS FOR $2; | |
ina int[]; | |
inb int[]; | |
arr1n int := array_length(arr1, 1); | |
arr2n int := array_length(arr2, 1); | |
BEGIN | |
--tbl := --our raster table name | |
IF arr1n < arr2n THEN | |
ina := arr2; | |
inb := arr1; | |
ELSE | |
ina := arr1; | |
inb := arr2; | |
END IF; | |
FOR i in array_lower(inb, 1) .. array_upper(inb, 1) LOOP | |
ina[i] := ina[i] + inb[i]; | |
END LOOP; | |
RETURN ina; | |
END; | |
$$ LANGUAGE 'plpgsql'; | |
SELECT AXH_Vector_Sum('{1,2,3,2,1}'::int[], '{7,2,3,2,1}'::int[]); |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment