0

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.

1 Answer 1

2

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

3
  • Is there a specific for CROSS JOIN versus just FROM account_inits AS ai, create_accounts(ai.accounts) AS ca? Output looks the same. Also what is the syntax for array_agg() if I want to aggregate the data from recordset/json? The random strings in the example are there so the data can be shown in the fiddle, the real usage will either interpolate into the query or get the values from another record. Oct 19, 2022 at 11:29
  • 1
    I never use a comma in the FROM clause. I want to make it obvious that a cross join is used. This query answers the question you have asked, if that is not the problem you need to solve you should have asked a different question.
    – user1822
    Oct 19, 2022 at 11:46
  • The question wasn't about the right query, but how to make postgresql arrays with composite types for the purpose of passing them to functions. Obviously I can't bring an entire stack to fiddle and wasn't even sure array_agg() was the right call to begin with. And I don't think "How do I array_agg()?" is worthy of a separate question. Oct 19, 2022 at 12:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.