How to loop an array and sum values until condition is met?

Question

I have a relational table where I need to loop through and sum amount values until a specified amount criteria is met. The data looks like this:

Company	Account	Amount
Coastal	A384	500
Coastal	C940	200
Coastal	Z934	200
American	U202	200
American	I034	300
Tester	P034	300

I want to have a function where I can loop through this data and specify an amount criteria that either returns 1 or more accounts once the threshold is met for each company. If the threshold is not met there should be some output that specifies the criteria was not met and the largest account value should be returned. I am thinking I would need to convert Account + Amount into an array to loop through. Pseudo code would look like:

var threshold = 500
var amount_total = 0
var array_result = {}
for each item in account_array
     array_result(account: amount)
     amount_total += amount
     if amount_total = threshold
        return array_result

My expected output from this would be:

Company	array_result	status
Coastal	{"A384": 500}	"Met Criteria"
American	{"U202": 200, "I034": 300}	"Met Criteria"
Tester	{"P034": 300}	"Did Not Meet Criteria"

Ideally, I would be able to have this as as a function to be able to use in a select statement:

Select Company, find_accounts_that_meet_threshold(account_array_field, threshold) from company_table

Answer 1

A set-based solution instead of looping:

SELECT company
     , CASE WHEN sum(amount) >= 500
         THEN json_agg(json_build_object(account, amount))
         ELSE json_agg(json_build_object(account, amount)) FILTER (WHERE amount_rn = 1) END AS json_array
     , CASE WHEN sum(amount) >= 500
         THEN array_agg((account, amount))
         ELSE array_agg((account, amount)) FILTER (WHERE amount_rn = 1) END AS plain_array
     , CASE WHEN sum(amount) >= 500 THEN 'Met' ELSE 'Failed' END AS status
FROM  (
   SELECT *
        , sum(amount) OVER (PARTITION BY company ORDER BY id ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS running_sum
        , row_number() OVER (PARTITION BY company ORDER BY amount DESC NULLS LAST, id) AS amount_rn
   FROM   tbl
   ORDER  BY company, id
   ) sub
WHERE  running_sum >= 500 IS NOT TRUE
GROUP  BY 1;

db<>fiddle here

I added two variants for your array to pick from.

In the subquery, form running sums per company with a window function. For lack of information, assuming an id column that defines sort order. Substitute with your actual order. Exclude the current row from the running sum, so we can conveniently filter rows WHERE running_sum >= 500 IS NOT TRUE in the outer query. That includes rows until the required sum is reached.
Also determine the row with biggest amount with row_number(). Using id to split ties if any. amount_rn = 1 is the winner - for the failed case.

The total sum indicates whether the criteria was met. If it was, return an array of contributing "account: amount". Else, just the one chosen by amount_rn.