能否从.NET Core API向PostgreSQL函数传递表/列表/JSON并实现批量插入？

PostgreSQL完全支持通过函数接收批量参数实现批量插入，针对你的发票明细场景，推荐以下三种方案：

方案1：使用自定义表类型作为函数参数 #

先创建与发票明细结构匹配的自定义表类型，再编写接收该类型数组的函数，实现批量插入，这是最贴合你示例思路的方案。

SQL代码 #

-- 1. 创建对应发票明细的自定义表类型
CREATE TYPE invoice_detail AS (
    line_item_no INT,
    product_id INT,
    quantity NUMERIC,
    unit_price NUMERIC,
    amount NUMERIC
);

-- 2. 创建批量插入函数
CREATE OR REPLACE FUNCTION insert_invoice_details(p_invoice_id BIGINT, p_details invoice_detail[])
RETURNS TABLE(detail_id BIGINT) AS $$
BEGIN
    RETURN QUERY
    INSERT INTO invoice_details (invoice_id, line_item_no, product_id, quantity, unit_price, amount)
    SELECT p_invoice_id, d.line_item_no, d.product_id, d.quantity, d.unit_price, d.amount
    FROM UNNEST(p_details) d
    RETURNING detail_id;
END;
$$ LANGUAGE plpgsql;

.NET Core调用代码（基于Npgsql） #

// 定义明细实体类
public class InvoiceDetail
{
    public int LineItemNo { get; set; }
    public int ProductId { get; set; }
    public decimal Quantity { get; set; }
    public decimal UnitPrice { get; set; }
    public decimal Amount { get; set; }
}

// 批量插入逻辑
using var conn = new NpgsqlConnection("你的数据库连接字符串");
await conn.OpenAsync();

var invoiceId = 123; // 已保存的发票ID
var details = new List<InvoiceDetail>
{
    new() { LineItemNo = 1, ProductId = 456, Quantity = 2, UnitPrice = 100, Amount = 200 },
    new() { LineItemNo = 2, ProductId = 789, Quantity = 1, UnitPrice = 50, Amount = 50 }
};

// 将明细转换为PostgreSQL自定义类型数组
var npgsqlDetails = details.Select(d => new NpgsqlCompositeRecord(
    new[] { NpgsqlDbType.Integer, NpgsqlDbType.Integer, NpgsqlDbType.Numeric, NpgsqlDbType.Numeric, NpgsqlDbType.Numeric },
    new object[] { d.LineItemNo, d.ProductId, d.Quantity, d.UnitPrice, d.Amount }
)).ToArray();

using var cmd = new NpgsqlCommand("SELECT * FROM insert_invoice_details(@invoiceId, @details)", conn);
cmd.Parameters.AddWithValue("invoiceId", invoiceId);
cmd.Parameters.AddWithValue("details", npgsqlDetails);
cmd.Parameters[1].NpgsqlDbType = NpgsqlDbType.Array | NpgsqlDbType.Composite;
cmd.Parameters[1].TypeName = "invoice_detail";

// 获取插入后的明细ID
using var reader = await cmd.ExecuteReaderAsync();
while (await reader.ReadAsync())
{
    var detailId = reader.GetInt64(0);
    // 处理返回的明细ID
}

方案2：传递JSON/JSONB参数 #

如果不想创建自定义类型，可以将明细序列化为JSON字符串传递给函数，在函数内解析后批量插入，灵活性更高。

SQL函数代码 #

CREATE OR REPLACE FUNCTION insert_invoice_details_json(p_invoice_id BIGINT, p_details JSONB)
RETURNS TABLE(detail_id BIGINT) AS $$
BEGIN
    RETURN QUERY
    INSERT INTO invoice_details (invoice_id, line_item_no, product_id, quantity, unit_price, amount)
    SELECT 
        p_invoice_id,
        (d->>'line_item_no')::INT,
        (d->>'product_id')::INT,
        (d->>'quantity')::NUMERIC,
        (d->>'unit_price')::NUMERIC,
        (d->>'amount')::NUMERIC
    FROM jsonb_array_elements(p_details) d
    RETURNING detail_id;
END;
$$ LANGUAGE plpgsql;

.NET Core调用代码 #

using var conn = new NpgsqlConnection("你的数据库连接字符串");
await conn.OpenAsync();

var invoiceId = 123;
var details = new List<InvoiceDetail> { /* 填充明细数据 */ };
var jsonDetails = JsonSerializer.Serialize(details);

using var cmd = new NpgsqlCommand("SELECT * FROM insert_invoice_details_json(@invoiceId, @details)", conn);
cmd.Parameters.AddWithValue("invoiceId", invoiceId);
cmd.Parameters.AddWithValue("details", jsonDetails, NpgsqlDbType.Jsonb);

using var reader = await cmd.ExecuteReaderAsync();
// 处理返回结果

方案3：直接使用批量复制（无需函数） #

如果不需要封装成函数，Npgsql提供的BeginBinaryImport是效率最高的批量插入方式，适合大规模明细数据的场景。

.NET Core代码 #

using var conn = new NpgsqlConnection("你的数据库连接字符串");
await conn.OpenAsync();

var invoiceId = 123;
var details = new List<InvoiceDetail> { /* 填充明细数据 */ };

// 启动批量复制
using var writer = conn.BeginBinaryImport(
    "COPY invoice_details (invoice_id, line_item_no, product_id, quantity, unit_price, amount) FROM STDIN BINARY");

foreach (var detail in details)
{
    writer.StartRow();
    writer.Write(invoiceId);
    writer.Write(detail.LineItemNo);
    writer.Write(detail.ProductId);
    writer.Write(detail.Quantity);
    writer.Write(detail.UnitPrice);
    writer.Write(detail.Amount);
}

writer.Complete();

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