如何将自定义类型数组传递给Postgres函数

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?

如何在postgres查询和PHP中调用它?

2 个解决方案

#1

You can use the alternative syntax with a string 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:

您可以使用替代语法与字符串文字，而不是数组构造函数，这是一个Postgres函数式的构造，当您需要传递值时可能会造成麻烦——就像准备好的语句:

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 here. That's legal.

我在数组中的两个行类型之间添加了换行符，以便在这里显示。这是合法的。

How to find the correct syntax for any literal?

Here is a demo:

这是一个演示:

CREATE TEMP 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)"}

It probably should be noted that any table (including temporary tables) implicitly creates a row type of the same name.

应该注意的是，任何表(包括临时表)都隐式地创建同名的行类型。

#2

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.

仍然需要解决这个问题的PHP部分，仍然不确定如何调用使用自定义数组参数填充的函数。