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:

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}}')'

Answer 1

UPDATE: I just noticed that you don't just need arrays, you need to use arrays of composite types. Ick. I've never needed to work with them, so I had to do a bit of checking.

It seems that the correct PostgreSQL syntax for an array of widgetallocationrequest would be:

'{"(8c2f3421-bac8-4cdd-87ce-45d55d152074,10.0000)","(6c2f7451-bac8-4cdd-87ce-45d55d152078,5.0000)"}'::widgetallocationrequest[]

See how each composite-type row is enclosed in "(col1,col2)" within the array {a,b,c} container?

Here's a PostgreSQL SQL example of how I created the value:

-- Create the array of composites from a VALUES() statement
--
SELECT array_agg(x::widgetallocationrequest) 
FROM (VALUES 
    ('8c2f3421-bac8-4cdd-87ce-45d55d152074',10.00),
    ('6c2f7451-bac8-4cdd-87ce-45d55d152078',5.00)
) x;

... and how I verified it was valid:

-- Unpack it back into a row-set of columns
SELECT * FROM unnest('{"(8c2f3421-bac8-4cdd-87ce-45d55d152074,10.0000)","(6c2f7451-bac8-4cdd-87ce-45d55d152078,5.0000)"}'::widgetallocationrequest[]);

Now, PHP's driver for Pg doesn't even support arrays, let alone arrays of composite types, so you're going to have to find someone else who wrote what you want or write it yourself. Writing a reliable parser will be "fun" and not a productive use of time.

Let's take another approach: produce a query that lets you call the function sanely by doing the conversion to a widgetallocationrequest[] inside PostgreSQL.

Here's a dummy function with the same arguments as your real one that we'll use as a call target:

CREATE OR REPLACE FUNCTION distributeWidgets(pid uuid, precipients widgetallocationrequest[]) RETURNS boolean AS $$
SELECT 't'::boolean;
$$ LANGUAGE 'sql';

You can see that it can be called with the array-of-composites syntax that's giving you so much trouble:

SELECT distributewidgets(null, '{"(8c2f3421-bac8-4cdd-87ce-45d55d152074,10.0000)","(6c2f7451-bac8-4cdd-87ce-45d55d152078,5.0000)"}');

... but ideally you want to avoid producing anything that horrible from PHP, and the driver is missing important features so it can't do it for you.

Instead, you can use a TEMPORARY table to produce the arguments, INSERT each argument row into the table with regular parameterized INSERTs, and then execute a query to execute the function.

BEGIN;

CREATE TEMPORARY TABLE dw_args ( id uuid, amount numeric(13,4) );

-- Use proper parameterized INSERTs from PHP, this is just an example
INSERT INTO dw_args(id,amount) VALUES ('8c2f3421-bac8-4cdd-87ce-45d55d152074',10.00);
INSERT INTO dw_args(id,amount) VALUES ('6c2f7451-bac8-4cdd-87ce-45d55d152078',5.00);

SELECT distributewidgets(null, array_agg(ROW(x.*)::widgetallocationrequest)) 
FROM dw_args x;

DROP TABLE dw_args;

COMMIT;

WARNING: The following is vulnerable to SQL injection if not handled very carefully. Use the above temp table approach if at all possible. Don't be bobby's next victim; read the PHP docs on SQL injection.

If for some reason it's absolutely necessary to run it all in one statement, you can instead produce a query with a VALUES set from PHP and convert that into a widgetallocationrequest[] using a PostgreSQL query. I demonstrated it above, but here's how to combine it with a call to distributeWidgets(...):

SELECT distributewidgets(null, array_agg(x::widgetallocationrequest)) 
FROM (VALUES 
        ('8c2f3421-bac8-4cdd-87ce-45d55d152074',10.00),
        ('6c2f7451-bac8-4cdd-87ce-45d55d152078',5.00)
) x;

That's something you can build pretty easily in PHP using string manipulation, so long as you're really careful about SQL injection.

Please use the temporary table approach if at all possible.

See also PHP array to postgres array