如何将插值对象数组转换为PostgreSQL复合类型数组？

我通过SQL函数执行批量插入操作，但该函数无法直接接收记录集作为参数，必须先将数据转换为数组。基本类型数组可以通过CAST (${value} as primitive_type[])直接转换，效果正常。但批量插入需要传入复合类型数组，而CAST()无法完成这种转换——它仅支持单列输入。

目前我的实现流程是：将数据以${account_inits:json}形式插值到SQL中，通过CTE先转成记录集，再聚合为复合类型数组后传入函数。这套步骤过于繁琐，尝试跳过JSON转换时，又会触发array[]或malformed object literal语法错误，希望找到更简洁的转换方式。

表与自定义类型 #

CREATE TABLE accounts (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  created_at timestamptz NOT NULL DEFAULT CURRENT_TIMESTAMP,
  login text NOT NULL,
  password text NOT NULL,
  email text
);

CREATE TYPE account_init AS (
  login text,
  password text,
  email text
);

函数定义 #

CREATE FUNCTION get_accounts(
  pagination_limit bigint DEFAULT 25,
  pagination_offset bigint DEFAULT 0,
  account_ids bigint[] DEFAULT NULL
)
RETURNS TABLE (
  id bigint,
  created_at timestamptz,
  login text,
  password text,
  email text
)
LANGUAGE SQL
AS $BODY$
  WITH input_accounts AS (
    SELECT
      id,
      created_at,
      login,
      password,
      email
    FROM
      accounts
    WHERE
      account_ids IS NULL OR id = ANY (account_ids)
    ORDER BY
      id
    LIMIT pagination_limit
    OFFSET pagination_offset
  )
  SELECT
    id,
    created_at,
    login,
    password,
    email
  FROM
    input_accounts
  ORDER BY
    id
$BODY$;

CREATE FUNCTION create_accounts(
  account_inits account_init[]
)
RETURNS TABLE (
  id bigint,
  created_at timestamptz,
  login text,
  password text,
  email text
)
LANGUAGE SQL
AS $BODY$
  WITH new_accounts AS (
    INSERT INTO accounts ( 
      login, 
      password, 
      email 
    )
    SELECT 
      login, 
      password, 
      email
    FROM 
      unnest(account_inits)
    RETURNING
      id
  )
  SELECT
    id,
    created_at,
    login,
    password,
    email
  FROM
    get_accounts(
      NULL,
      NULL,
      ARRAY(
        SELECT
          id
        FROM
          new_accounts
      )
    )
  ORDER BY
    id
$BODY$;

待插入数据 #

const account_inits = [
  {
    login:"EC4A42323F", 
    password: "3DF1542F23A29B73281EEC5EBB55FFE18C253A7E800E7A541B"
  },
  {
    login:"1D771C1E52", 
    password: "2817029563CC722FBC3D53F9F29F0000898F9843518D882E4A", 
    email: "a@b"
  },
  {
    login:"FB66381D3A", 
    password: "C8F865AC1D54CFFA56DEBDEEB671C8EF110991BBB3B9EE57D2", 
    email: null
  }
]

当前实现方式 #

--- 插入数据
WITH input_inits AS (
  SELECT
    login,
    password,
    email
  FROM
    json_to_recordset(${account_inits:json}) AS input_init(
      login text,
      password text,
      email text
    )
),
input_data AS (
  SELECT
    array_agg(
      CAST (
        (
          login,
          password,
          email
        ) AS account_init
      )
    ) AS account_inits
  FROM
    input_inits
)
SELECT
  new_accounts.id,
  new_accounts.created_at,
  new_accounts.login,
  new_accounts.password,
  new_accounts.email
FROM
  input_data
  CROSS JOIN
  create_accounts(input_data.account_inits) AS new_accounts
ORDER BY
  new_accounts.id ASC
;

优化方案 #

可以直接构造复合类型数组，跳过JSON转换步骤，核心是为每个待插入对象生成(login, password, email)::account_init格式的复合类型，再组合成数组传入函数。

示例代码 #

假设你的数据插值后能生成如下SQL结构：

SELECT
  new_accounts.id,
  new_accounts.created_at,
  new_accounts.login,
  new_accounts.password,
  new_accounts.email
FROM
  create_accounts(
    ARRAY[
      ('EC4A42323F', '3DF1542F23A29B73281EEC5EBB55FFE18C253A7E800E7A541B', NULL)::account_init,
      ('1D771C1E52', '2817029563CC722FBC3D53F9F29F0000898F9843518D882E4A', 'a@b')::account_init,
      ('FB66381D3A', 'C8F865AC1D54CFFA56DEBDEEB671C8EF110991BBB3B9EE57D2', NULL)::account_init
    ]
  ) AS new_accounts
ORDER BY
  new_accounts.id ASC;

关键点说明 #

• 每个元组必须严格匹配account_init类型的字段顺序和类型
• 空值用NULL显式表示，避免语法错误
• 数组中的每个元素都要通过::account_init显式转换为自定义复合类型，PostgreSQL才能正确识别数组类型

这种方式省去了JSON解析和CTE聚合的步骤，直接构造目标数组传入函数，效率更高也更简洁。

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