php - How to pass custom type array to Postgres function

Question

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?

Answer 1

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.

How to find the correct syntax for any literal?

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.