*A*and

*B*and want to do element wise operations, creating a new vector C, FORTRAN 90 (and many other languages now) has a concise notation for that. E.g.

*C = A + B*

does the element wise addition of

*A*and

*B*into

*C*.

Can we have that in PostgreSQL too?

CREATE OR REPLACE FUNCTION _vectoradd(

v1 anyarray,

v2 anyarray)

RETURNS anyarray AS

$BODY$

DECLARE

BEGIN

if array_ndims(v1) != 1 OR array_ndims(v2) != 1 Then

RAISE EXCEPTION 'Only vectors supported';

end if;

if array_length(v1,1) != array_length(v2,1) Then

RAISE EXCEPTION 'Vector size mismatch';

end if;

-- return array(select v.a + v.b from (select unnest(v1) as a, unnest(v2) as b) v); --9.4

return array(select a+b from unnest(v1,v2) v (a,b)); -- 9.5

END;

$BODY$

LANGUAGE plpgsql immutable strict;

CREATE OPERATOR + (

LEFTARG = anyarray,

RIGHTARG = anyarray,

PROCEDURE = _vectoradd,

COMMUTATOR = +);

select ARRAY[1,2,3,4] + ARRAY[1,2,3,4];

{2,4,6,8}

Yes, we can! :-)

FWIW - here is an extension that achieves similar goals - https://github.com/pramsey/pgsql-arraymath

ReplyDeleteDamn. Every time I come up with something, somebody else has already done it. :-)

DeleteAnd in C.

All I can say in defense is, that my example is 100% SQL and the use of unnest() instead of a slow element-by-index loop.

BTW: That C extension is probably a good candidate for true parallelization.