Postgres中如何实现普通表与composite type复合类型的JOIN关联操作

Postgres支持普通表和复合类型数据集的关联查询，你当前的写法存在两处核心错误导致运行报错：

• 入参定义为单个复合类型值，仅能存储一行数据，无法匹配DataTable的多行结构，需要将入参调整为复合类型数组类型
• 不能直接将复合类型/数组变量作为表对象直接JOIN，需要通过unnest()函数将数组拆分为多行结果集后再关联

修正实现方案 #

方案1：使用复合类型数组（性能最优） #

首先提前创建和C# DataTable结构匹配的复合类型：

CREATE TYPE public.entry_type AS (
    id int,
    name text
);

修正后的函数代码：

CREATE OR REPLACE FUNCTION public.get_matched_entries(
    input_entries public.entry_type[] -- 入参为复合类型数组，接收DataTable全部行数据
)
-- 按实际业务需要定义返回字段，和public.table的字段结构保持一致即可
RETURNS TABLE (
    id int,
    name text,
    other_col1 text,
    other_col2 int
)
LANGUAGE sql
AS $BODY$
SELECT ta.*
FROM public.table ta
INNER JOIN unnest(input_entries) t
    ON t.id = ta.id AND t.name = ta.name;
$BODY$;

C#端使用Npgsql驱动对接时，将DataTable遍历转换为对应结构的C#类对象数组即可，Npgsql会自动完成C#数组到Postgres复合类型数组的映射。

方案2：使用JSONB数组（无需提前定义复合类型） #

如果不想额外维护复合类型，可选择传入JSON格式的DataTable数据，函数示例如下：

CREATE OR REPLACE FUNCTION public.get_matched_entries(
    input_entries jsonb
)
RETURNS TABLE (
    id int,
    name text,
    other_col1 text,
    other_col2 int
)
LANGUAGE sql
AS $BODY$
SELECT ta.*
FROM public.table ta
INNER JOIN jsonb_to_recordset(input_entries) AS t(id int, name text)
    ON t.id = ta.id AND t.name = ta.name;
$BODY$;

C#端直接将DataTable序列化为JSON字符串传入即可，适合临时业务场景使用。

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