Multiply elements of equally sized postgresql array

Question

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.

Answer 1

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.

• Noxville: Excellent - exactly what I wanted. I'd have said zip-like, but wasn't sure if that'd dissuade some more cunning answers.
• Evan Carroll: @Noxville if yo uwant to make it faster, see the update ;)