Skip to content

Instantly share code, notes, and snippets.

@andrewxhill
Created January 23, 2012 22:01
Show Gist options
  • Star 1 You must be signed in to star a gist
  • Fork 4 You must be signed in to fork a gist
  • Save andrewxhill/1665767 to your computer and use it in GitHub Desktop.
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]
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