How do you pass the array of composite type to the argument of an SQL function?

Question

Database example: https://dbfiddle.uk/sERgZPiB

Tables and types

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
);

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

Entity functions

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_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$;

Usage

WITH account_inits AS (
  SELECT
    index_id,
    (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
;

Current code returns

ERROR:  subquery must return only one column
LINE 31:       (

I've tried array_agg() and array() both of them returning different errors. I thought about using json type for the argument, but that will obscure the function signature both for reading and debugging so I'd rather not.

Answer 1

Aggregate the input data into an array in the CTE, then use that array directly.

WITH account_inits AS (
  SELECT array_agg((random_string(10), random_string(50), null)::account_init) as accounts
  FROM create_series(10)
)
SELECT ca.*
FROM account_inits ai
  cross join create_accounts(ai.accounts) ca
ORDER BY id ASC;

Online example