PostgreSQL – Multiply Elements of Equally Sized Arrays

arraypostgresql

In Postgresql 9.6, is there a clean way to multiply corresponding elements within two equally sized arrays?

For example 

a = [1,2,9,4,5]
b = [5,8,4,0,12]

function(a, b) would return [5, 16, 36, 0, 60].

I'd prefer it as a function like this, since it'd be used in multiple places.

Best Answer

Parallel Unnest

What you want is something like this with parallel-unnest. Here we use the ARRAY() constructor rather than array_agg()

SELECT ARRAY(
  SELECT a*b
  FROM unnest(
    ARRAY[1,2], -- ex1
    ARRAY[2,3]  -- ex2
  ) AS t(a,b)
);

Creating our own function

In functional programming, we call this zip, and you'd use that in a language with primitive functional support like Python, or the like. In languages with more advanced functional support, like Haskell, you'd use zipWith or Ramda.js R.zipWith()

This will return an array sized to the greater of ex1, or ex2. You can put this in a function like this,

CREATE FUNCTION array_zip_with_multiply( arr_q anyarray, arr_e anyarray )
RETURNS anyarray
AS $$
  SELECT ARRAY(
    SELECT a*b
    FROM unnest(
      arr_q, -- ex1
      arr_e  -- ex2
    ) AS t(a,b)
  );
$$ LANGUAGE sql
IMMUTABLE;

SELECT array_zip_with_multiply( ARRAY[1,2,9,4,5], ARRAY[5,8,4,0,12] );
 array_zip_with_multiply 
-------------------------
 {5,16,36,0,60}
(1 row)

Note: this won't work if there isn't an operator * defined for the type.

Related Question