如何用Npgsql从C#向PostgreSQL存储过程传入任意类型数组？anyarray是否可用？

嘿，我来帮你搞定这个问题！在C#里用Npgsql给PostgreSQL存储过程传任意类型数组其实并不复杂，而且anyarray和anyelement确实能帮上大忙，尤其是在你想让存储过程更通用的时候。下面分情况给你详细说明：

一、针对特定类型数组的基础传参方法 #

如果你的业务场景只需要处理某一种固定类型的数组（比如int数组、字符串数组），直接为该类型创建存储过程，然后在C#代码里指定对应的数组参数类型即可。

1. 创建PostgreSQL存储过程 #

CREATE OR REPLACE PROCEDURE process_int_array(p_ids int[])
LANGUAGE plpgsql
AS $$
BEGIN
    -- 示例逻辑：遍历数组并打印元素
    FOREACH id IN ARRAY p_ids LOOP
        RAISE NOTICE 'Processing ID: %', id;
    END LOOP;
END;
$$;

2. C#代码调用（传int数组） #

using Npgsql;

var connectionString = "Host=localhost;Username=postgres;Password=your_password;Database=your_db";
using var conn = new NpgsqlConnection(connectionString);
conn.Open();

// 调用存储过程
using var cmd = new NpgsqlCommand("CALL process_int_array(@p_ids)", conn);
// 指定参数类型为数组+整数类型
cmd.Parameters.Add("@p_ids", NpgsqlDbType.Array | NpgsqlDbType.Integer).Value = new int[] { 1, 2, 3, 4 };

cmd.ExecuteNonQuery();

二、用anyarray实现通用存储过程 #

如果需要让存储过程兼容任意类型的数组，anyarray就派上用场了——它是PostgreSQL的通用数组类型占位符，能接收任何类型的数组参数，不用为每种类型单独写存储过程。

1. 创建通用存储过程 #

CREATE OR REPLACE PROCEDURE process_any_array(p_array anyarray)
LANGUAGE plpgsql
AS $$
DECLARE
    elem anyelement; -- 用anyelement接收数组中的任意类型元素
BEGIN
    IF p_array IS NULL THEN
        RAISE EXCEPTION 'Array cannot be null';
    END IF;

    -- 打印数组的元素类型
    RAISE NOTICE 'Array element type: %', pg_typeof(p_array);

    -- 遍历数组处理每个元素
    FOREACH elem IN ARRAY p_array LOOP
        RAISE NOTICE 'Element: % (type: %)', elem, pg_typeof(elem);
    END LOOP;
END;
$$;

2. C#代码传不同类型数组 #

不管是字符串数组、日期数组还是自定义类型数组，都可以用同一个存储过程处理：

传字符串数组

using var cmd = new NpgsqlCommand("CALL process_any_array(@p_array)", conn);
cmd.Parameters.Add("@p_array", NpgsqlDbType.Array | NpgsqlDbType.Text).Value = new string[] { "apple", "banana", "cherry" };
cmd.ExecuteNonQuery();

传日期时间数组

var dates = new DateTime[] { DateTime.Now, DateTime.Now.AddDays(1) };
using var cmd = new NpgsqlCommand("CALL process_any_array(@p_array)", conn);
cmd.Parameters.Add("@p_array", NpgsqlDbType.Array | NpgsqlDbType.Timestamp).Value = dates;
cmd.ExecuteNonQuery();

传自定义类型数组（需提前映射）

如果是自定义的.NET类，需要先将其映射到PostgreSQL的复合类型：

// 假设你有一个.NET类
public class Product
{
    public int Id { get; set; }
    public string Name { get; set; }
}

// 注册类型映射（在连接前执行）
NpgsqlConnection.GlobalTypeMapper.MapComposite<Product>("product"); // "product"是PostgreSQL中定义的复合类型名

// 传自定义类型数组
var products = new Product[] { new Product { Id = 1, Name = "Laptop" }, new Product { Id = 2, Name = "Phone" } };
using var cmd = new NpgsqlCommand("CALL process_any_array(@p_array)", conn);
cmd.Parameters.Add("@p_array", NpgsqlDbType.Array | NpgsqlDbType.Composite).Value = products;
cmd.ExecuteNonQuery();

三、anyelement的补充作用 #

anyelement是PostgreSQL的通用元素类型占位符，常和anyarray配合使用：

• 在遍历anyarray时，用anyelement接收数组中的单个元素
• 可以用来定义通用函数，比如返回数组的第一个元素：

CREATE OR REPLACE FUNCTION get_first_element(p_array anyarray)
RETURNS anyelement
LANGUAGE plpgsql
AS $$
BEGIN
    IF p_array IS NULL OR array_length(p_array, 1) = 0 THEN
        RETURN NULL;
    END IF;
    RETURN p_array[1];
END;
$$;

C#调用这个通用函数：

using var cmd = new NpgsqlCommand("SELECT get_first_element(@p_array)", conn);
cmd.Parameters.Add("@p_array", NpgsqlDbType.Array | NpgsqlDbType.Integer).Value = new int[] { 10, 20, 30 };
var result = cmd.ExecuteScalar();
Console.WriteLine($"First element: {result}"); // 输出10

注意事项 #

• Npgsql会自动映射常见的.NET类型到PostgreSQL类型，但自定义类型必须提前注册映射
• 使用anyarray时，存储过程内部要注意类型兼容性，比如不要尝试对字符串数组做数值运算，否则会抛出类型错误
• 确保PostgreSQL版本支持anyarray和anyelement（PostgreSQL 8.0及以上版本都支持）

内容的提问来源于stack exchange，提问作者Mohit Verma