Postgres 13

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
)
LANGUAGE SQL
AS $BODY$
  SELECT
    generate_series AS index_id
  FROM
    generate_series(1, amount)
$BODY$;

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
    id
$BODY$;

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(
        SELECT
          id
        FROM
          new_accounts
      )
    )
  ORDER BY
    id
$BODY$;

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
          account_inits
      ) AS account_init[]
    ) 
  )
ORDER BY
  id ASC
;


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:       (
               ^

index_id	random_string
1	818BCD5255A8C457BB98
2	912A410746F43B554245
3	129431E9072B1C78079C
4	CFC4204CF4BD6C95719F
5	640C48EEC0C329976FA4
6	76E1DC5E6446068AA238
7	81605FC44F7D03EC8F95
8	80EA7C43F3B3EADD0FA4
9	702FE73B926AAD36846B
10	4113BC80878DEC0F2AB1