I have a custom type
CREATE TYPE mytype as (id uuid, amount numeric(13,4));
I want to pass it to a function with the following signature:
CREATE FUNCTION myschema.myfunction(id uuid, mytypes mytype[])
RETURNS BOOLEAN AS...
How can I call this in postgres query and inevitably from PHP?
You can use the alternative syntax with a array literal instead of the array constructor, which is a Postgres function-like construct and may cause trouble when you need to pass values - like in a prepared statement:
SELECT myschema.myfunc('0d6311cc-0d74-4a32-8cf9-87835651e1ee'
, '{"(0d6311cc-0d74-4a32-8cf9-87835651e1ee, 25)"
, "(6449fb3b-844e-440e-8973-31eb6bbefc81, 10)"}'::mytype[]);
I added a line break between the two row types in the array for display. That's legal.
Just ask Postgres. Here is a demo:
CREATE TABLE mytype (id uuid, amount numeric(13,4));
INSERT INTO mytype VALUES
('0d6311cc-0d74-4a32-8cf9-87835651e1ee', 25)
,('6449fb3b-844e-440e-8973-31eb6bbefc81', 10);
SELECT ARRAY(SELECT m FROM mytype m);
Returns:
{"(0d6311cc-0d74-4a32-8cf9-87835651e1ee,25.0000)","(6449fb3b-844e-440e-8973-31eb6bbefc81,10.0000)"}
db<>fiddle here
Any table (including temporary tables) implicitly creates a row type of the same name.
select myschema.myfunc('0d6311cc-0d74-4a32-8cf9-87835651e1ee'
, ARRAY[('ac747f0e-93d4-43a9-bc5b-09df06593239', '25.00')
, ('6449fb3b-844e-440e-8973-31eb6bbefc81', '10.00')]::mytype[]
);
Still need PHP portion of this resolved though, still not sure how to call a function populating with the custom array parameter.