I am using SQL functions to perform multi-inserts, but because they can't accept recordsets as arguments I have to convert them to an array first. It works fine for array of primitives because they can simply be cast with CAST (${value} as primitive_type[])
and be done with it.
However multi-insert queries require composite type arrays and it doesn't look like CAST()
works with them, since it expects one-column input.
All queries are shown on this 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$;
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
;
Currently I interpolate it as :json
and then convert it to recordset in the CTE, which then gets converted to a composite type array in the second CTE to pass as an argument to the function. This seems to be awfully a lot of work for passing the array of objects to the function arguments. I've tried to work without :json
conversion but either encountered array[]
related or malformed object literal
syntax errors.
Turns there isn't. The "proper" alternative would need to know the expected keys of the objects in the input array (and for all nested objects/arrays of objects) in order to construct the proper array of tuples.
This basically requires some sort of runtime referential schema which, even if implemented perfectly, will still require to write boilerplate code for each new query file. At best it will not be better than the functions in helpers
module or the json -> record set -> composite type array
casting within queries.