如何在SQL编辑器中调用含自定义类型数组参数的函数

问题背景 #

我已创建如下自定义复合类型：

CREATE TYPE my_schema.sample_type
AS
(
    id int,
    categories int[], 
    body text
);

该类型被用作函数的数组参数，函数定义如下：

CREATE OR REPLACE FUNCTION my_schema.bulk_operation(
    array_content my_schema.sample_type[]
)
RETURNS SETOF my_schema.sample_result_type
AS $$
DECLARE
-- 
BEGIN
--
--
END;
$$ LANGUAGE plpgsql;

我尝试在SQL编辑器中调用这个函数，但不知道如何构造该数组参数。最近的尝试代码如下，想先定义一个my_schema.sample_type类型的实例变量，再将其加入数组调用函数，但这段代码在变量声明部分报错：

DO
 LANGUAGE plpgsql $$
DECLARE
    my_instance my_schema.sample_type;
BEGIN
    SELECT 
      1 AS id,
      '{10, 15}':: int[] AS categories,
      'some content' as body
    INTO my_instance;
END

错误原因 #

你的DO代码块缺少闭合的$$;标记，另外构造复合类型实例的写法可以更简洁，同时还需要将实例转为数组后传入函数。

正确的调用方式 #

这里提供几种可行的方法来构造数组参数并调用函数：

方法1：直接构造数组字面量调用 #

无需使用PL/pgSQL块，直接用数组字面量构造复合类型数组，直接调用函数：

SELECT * FROM my_schema.bulk_operation(
    ARRAY[
        (1, '{10,15}'::int[], 'some content')::my_schema.sample_type,
        (2, '{20,25}'::int[], 'another content')::my_schema.sample_type
    ]
);

方法2：在PL/pgSQL块中构造并调用 #

修正你的DO块，补充闭合标记，同时完成实例构造、数组组装和函数调用：

DO
LANGUAGE plpgsql $$
DECLARE
    my_instance my_schema.sample_type;
    my_array my_schema.sample_type[];
    result my_schema.sample_result_type;
BEGIN
    -- 方式1：直接赋值构造单个实例
    my_instance := (1, '{10,15}'::int[], 'some content')::my_schema.sample_type;
    
    -- 方式2：用SELECT INTO构造，注意字段顺序要和类型定义完全一致
    -- SELECT 1, '{10,15}'::int[], 'some content' INTO my_instance;
    
    -- 将单个实例转为数组
    my_array := ARRAY[my_instance];
    -- 追加更多实例到数组
    my_array := my_array || (2, '{20,25}'::int[], 'another content')::my_schema.sample_type;
    
    -- 调用函数并遍历处理结果
    FOR result IN SELECT * FROM my_schema.bulk_operation(my_array) LOOP
        RAISE NOTICE '返回结果：%', result;
    END LOOP;
END
$$;

方法3：用row()函数构造复合类型 #

也可以使用row()函数来创建复合类型实例，再组装成数组调用：

SELECT * FROM my_schema.bulk_operation(
    ARRAY[
        row(1, '{10,15}'::int[], 'some content')::my_schema.sample_type,
        row(2, '{20,25}'::int[], 'another content')::my_schema.sample_type
    ]
);

关键注意事项 #

• 构造复合类型实例时，字段的顺序必须和类型定义时的顺序完全一致，不能错位。
• 数组中的每个元素需要明确转换为my_schema.sample_type类型，或者整个数组转为对应数组类型。
• PL/pgSQL的DO块必须用$$包裹完整逻辑，结尾要加;。

内容的提问来源于stack exchange，提问作者juliano.net