By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
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 TABLE
CREATE TYPE
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
CREATE FUNCTION
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,
CREATE FUNCTION
/*
Tests.
*/
--- insert data
WITH input_inits AS (
SELECT
login,
password,
email
FROM
-- assume this is `${account_inits}` interpolation
json_to_recordset(
'[{"login":"EC4A42323F", "password": "3DF1542F23A29B73281EEC5EBB55FFE18C253A7E800E7A541B"},{"login":"1D771C1E52", "password": "2817029563CC722FBC3D53F9F29F0000898F9843518D882E4A", "email": "a@b"},{"login":"FB66381D3A", "password": "C8F865AC1D54CFFA56DEBDEEB671C8EF110991BBB3B9EE57D2", "email": null}]'
) 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,
| id | created_at | login | password | |
|---|---|---|---|---|
| 1 | 2022-10-20 09:46:00.489996+01 | EC4A42323F | 3DF1542F23A29B73281EEC5EBB55FFE18C253A7E800E7A541B | null |
| 2 | 2022-10-20 09:46:00.489996+01 | 1D771C1E52 | 2817029563CC722FBC3D53F9F29F0000898F9843518D882E4A | a@b |
| 3 | 2022-10-20 09:46:00.489996+01 | FB66381D3A | C8F865AC1D54CFFA56DEBDEEB671C8EF110991BBB3B9EE57D2 | null |
SELECT 3