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,
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
/*
Helper functions.
*/
-- random string generator
-- https://www.simononsoftware.com/random-string-in-postgresql/#combined-md5-and-sql
CREATE FUNCTION random_string(length integer)
RETURNS text
LANGUAGE SQL
AS $$
SELECT upper(
substring(
(
SELECT
string_agg(
md5(
CAST (random() AS text)
),
''
)
FROM
generate_series(
1,
CAST (CEIL(length / 32.) AS integer)
)
),
1,
length
)
);
$$;
--sequence generator
CREATE FUNCTION create_series(amount integer)
RETURNS TABLE (
index_id bigint
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION get_accounts(
pagination_limit bigint DEFAULT 25,
pagination_offset bigint DEFAULT 0,
account_ids bigint[] DEFAULT NULL
)
RETURNS TABLE (
id bigint,
login text,
password text,
email text
)
LANGUAGE SQL
AS $BODY$
WITH input_accounts AS (
SELECT
id,
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,
login,
password,
email
FROM
input_accounts
ORDER BY
CREATE FUNCTION
CREATE FUNCTION create_accounts(
account_inits account_init[]
)
RETURNS TABLE (
id bigint,
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,
login,
password,
email
FROM
get_accounts(
NULL,
NULL,
ARRAY(
CREATE FUNCTION
/*
Tests.
*/
SELECT
index_id,
random_string(20)
FROM
create_series(10)
ORDER BY
index_id ASC
;
-- create accounts
WITH account_inits AS (
SELECT
(random_string(10)) AS login,
(random_string(50)) AS password,
NULL AS email
FROM
create_series(10)
)
SELECT
id,
login,
password,
email
FROM
create_accounts(
CAST (
(
SELECT
login,
password,
email
FROM
| index_id | random_string |
|---|---|
| 1 | 818BCD5255A8C457BB98 |
| 2 | 912A410746F43B554245 |
| 3 | 129431E9072B1C78079C |
| 4 | CFC4204CF4BD6C95719F |
| 5 | 640C48EEC0C329976FA4 |
| 6 | 76E1DC5E6446068AA238 |
| 7 | 81605FC44F7D03EC8F95 |
| 8 | 80EA7C43F3B3EADD0FA4 |
| 9 | 702FE73B926AAD36846B |
| 10 | 4113BC80878DEC0F2AB1 |
SELECT 10
ERROR: subquery must return only one column LINE 31: ( ^