How to pass a php array to a postgres function

Question

I'm trying with little success to pass a PHP array to the following function.

$recipients = array();
$recipients['6c2f7451-bac8-4cdd-87ce-45d55d152078'] = 5.00;
$recipients['8c2f3421-bac8-4cdd-87ce-45d55d152074'] = 10.00;

$originator = '5630cc6d-f994-43ff-abec-1ebb74d39a3f';
$params = array($originator, $recipients);

pg_query_params(_conn, 'SELECT widgetallocationrequest($1, $2)', $params);

$results = pg_fetch_all(pg_query_params);
...

The function accepts an array of a custom type:

CREATE TYPE widgetallocationrequest AS (id uuid, amount numeric(13,4));

and enumeraties each item and performs an action:

CREATE FUNCTION distributeWidgets(pid uuid, precipients widgetallocationrequest[])
 RETURNS BOOLEAN AS
 $BODY$
{
FOREACH v_recipient IN ARRAY precipients
LOOP
    ...do something
END LOOP;
}
  RETURN TRUE;
END;
$BODY$
LANGUAGE plpgsql VOLATILE STRICT
COST 100;

***(if the specific code sample contains errors it's only pseudocode, i'm really just looking for the best way to pass a php array to a postgres custom type array as a parameter so it can be enumerated in the postgres function)***

Update: I'm able to successfully call the function from postgres directly (not from PHP) with the following:

SELECT distributeWidgets('5630cc6d-f994-43ff-abec-1ebb74d39a3f',
ARRAY[('ac747f0e-93d4-43a9-bc5b-09df06593239', '5.00'), ('8c2f3421-bac8-4cdd-87ce-45d55d152074', '10.00')]::widgetallocationrequest[]);

But still not sure how to translate from this postgres example back into PHP

I've tried suggestions below and the output from the referenced functions yields the following error:

enter image description here

string from function is as follows:

'SELECT account.hldtoexpalloc('0d6311cc-0d74-4a32-8cf9-87835651e1ee', '0124a045-b2e8-4a9f-b8c4-43b1e4cf638d', '{{\"6c2f7451-bac8-4cdd-87ce-45d55d152078\",5.00},{\"8c2f3421-bac8-4cdd-87ce-45d55d152074\",10.00}}')'

How to pass a php array to a postgres function

2 Answers