Syntax for function looping through array elements?

Question

This is my first time trying to build a function in Postgres, and first time working with arrays. And first question on Stackoverflow!

There's a 2 level numeric array of {{a1,b1},{a2,b2}...{ai,bi}}. Trying to calculate 3 things:

Would like to loop through and multiply each subarray ie (a1 * b1) and then accumulate within the array, until a certain figure (x) is reached ie (a1 * b1) + (a2 * b2)... + ...(ai * bi) >= x. So ai * bi is the first subarray that pushes the accumulation above x. I want to return this accumulated figure.
Cumulative b. ie (b1 + b2 +...+ bi).
ai.

Outside of the loop, I want to do some calculation involving these 3 figures.

Haven't been able to include a DO to localise the variables, but that would be great, if possible.

Looking at around 1M rows. Each row could contain hundreds of subarray elements in the main array but I would be unlikely to need more than 8 subarrays multiplied and summed per row.

Postgres 9.6.

I've looked at foreach and slice in arrays, but not sure if good to use in this case. Not sure if unnest would be any good either. Currently no indexes.

CREATE OR REPLACE FUNCTION accum (numeric[i],numeric[i]) RETURNS numeric AS 
$$

DECLARE
x integer := 100;
y numeric;
z numeric;

BEGIN
  LOOP

-- Accumulate subarray products
  y := SELECT(ANYARRAY)[i:i][1:1]*SELECT(ANYARRAY)[i:i][2:2] + y;

-- Accumulate 2nd element of subarrays
  z := SELECT(ANYARRAY)[i:i][2:2] + z;

EXIT WHEN y > x; 

END LOOP;

RETURN y,
       z,
       -- Return first element of subarray that makes y > x
       SELECT(ANYARRAY)[i:i][1:1];

"Calculation involving output"

END
$$
LANGUAGE plpgsql
;

I'm unsure as to how to return multiple outputs from a loop. Aiming to get a single numeral output from this function. Any help with the syntax, or the method, of how I'm going about this would be much appreciated! Is it even possible to do all this without a function? Thanks for reading.

Hi Kaushik, thanks for answering. Your answer did not work with my array data unfortunately. I commented on your answer when you wrote it. It did get me most of the way there though, and I have just recently got the finished function. I can't upvote your answer yet (rep too low). But your answer is internally consistent and solves a similar question, so I will accept. Cheers. — James Johnson
– James Johnson, Commented Feb 19, 2019 at 11:25

Kaushik Nayak · Accepted Answer · 2018-12-31 07:29:01Z

0

A multi-dimensional array in Postgres is not straight-forward to implement because Sub-arrays are not "elements".I'd recommend you to use a type of (a,b) and then pass an array of that type. It is easier to refer the elements that way.

create the type

create type abtype AS( a numeric, b numeric);

Function

CREATE OR REPLACE FUNCTION accum (abtyparray abtype[]) RETURNS numeric AS 
$$

DECLARE
x integer := 100;
ab abtype;
y numeric := 0;
z numeric := 0;

BEGIN

   FOREACH ab IN ARRAY abtyparray 
   LOOP
     y := y + ab.a * ab.b; --(a1 * b1) + (a2 * b2)... + ...(ai * bi) 
     z := z + ab.b;  --Cumulative b
     EXIT WHEN y > x; 
   END LOOP;
     RAISE NOTICE 'y = %, z= %,ai = % ', y,z,ab.a;
                                            --^ai
    RETURN y;

END
$$
LANGUAGE plpgsql;

Execution

knayak=# DO $$
knayak$# BEGIN
knayak$# PERFORM  accum (ARRAY[(3,4),(10,9),(17,19)] ::abtype[] ) ;
knayak$# END $$;
NOTICE:  y = 102, z= 13,ai = 10
DO

Demo

answered Dec 31, 2018 at 7:29

Kaushik Nayak

32k6 gold badges36 silver badges54 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

James Johnson Over a year ago

Thanks Kaushik, that's very helpful. However, I'm getting an error: "cannot cast type numeric[] to abtype[]". The array I have is generated by ARRAY[[a,b],[c,d]] rather than ARRAY[(a,b),(c,d)]. Is there an easy fix? Cheers...

Collectives™ on Stack Overflow

Syntax for function looping through array elements?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related