我使用SQL函数执行多插入，但由于它们不能接受记录集作为参数，我必须先将它们转换为数组。它适用于基元数组，因为它们可以简单地用CAST (${value} as primitive_type[])进行强制转换并使用它完成。
但是，多插入查询需要复合类型数组，而且CAST()似乎不适用于它们，因为它需要一列输入
所有查询都显示在此fiddle上：https://dbfiddle.uk/w_Qbq-lw
表格和类型
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$;
Init数据
const account_inits = [
{
login:"EC4A42323F",
password: "3DF1542F23A29B73281EEC5EBB55FFE18C253A7E800E7A541B"
},
{
login:"1D771C1E52",
password: "2817029563CC722FBC3D53F9F29F0000898F9843518D882E4A",
email: "a@b"
},
{
login:"FB66381D3A",
password: "C8F865AC1D54CFFA56DEBDEEB671C8EF110991BBB3B9EE57D2",
email: null
}
]
用法
--- insert data
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，然后将其转换为CTE中的记录集，然后在第二个CTE中将其转换为复合类型数组，作为参数传递给函数。将对象数组传递给函数参数似乎需要做大量的工作。我尝试过在没有:json转换的情况下工作，但遇到了与array[]相关或malformed object literal语法错误。
事实并非如此。"；适当的"；备选方案需要知道输入数组中对象(以及所有嵌套对象/对象数组(的预期键，以便构造正确的元组数组
这基本上需要某种运行时引用模式，即使实现得很好，也仍然需要为每个新的查询文件编写样板代码。充其量，它不会比helpers模块中的函数或查询中的json -> record set -> composite type array强制转换更好。