arrayspostgresqlpg-promisepostgresql-13composite-types

What is the proper way of converting interpolated array of objects to a postgresql array of composite type?


I am using SQL functions to perform multi-inserts, but because they can't accept recordsets as arguments I have to convert them to an array first. It works fine for array of primitives because they can simply be cast with CAST (${value} as primitive_type[]) and be done with it.
However multi-insert queries require composite type arrays and it doesn't look like CAST() works with them, since it expects one-column input.
All queries are shown on this fiddle: https://dbfiddle.uk/w_Qbq-lw

Tables and Types

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

Functions

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
  FROM
    input_accounts
  ORDER BY
    id
$BODY$;

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

Init data

const account_inits = [
  {
    login:"EC4A42323F", 
    password: "3DF1542F23A29B73281EEC5EBB55FFE18C253A7E800E7A541B"
  },
  {
    login:"1D771C1E52", 
    password: "2817029563CC722FBC3D53F9F29F0000898F9843518D882E4A", 
    email: "a@b"
  },
  {
    login:"FB66381D3A", 
    password: "C8F865AC1D54CFFA56DEBDEEB671C8EF110991BBB3B9EE57D2", 
    email: null
  }
]

Usage

--- insert data
WITH input_inits AS (
  SELECT
    login,
    password,
    email
  FROM
    json_to_recordset(${account_inits:json}) 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,
  new_accounts.created_at,
  new_accounts.login,
  new_accounts.password,
  new_accounts.email
FROM
  input_data
  CROSS JOIN
  create_accounts(input_data.account_inits) AS new_accounts
ORDER BY
  new_accounts.id ASC
;

Currently I interpolate it as :json and then convert it to recordset in the CTE, which then gets converted to a composite type array in the second CTE to pass as an argument to the function. This seems to be awfully a lot of work for passing the array of objects to the function arguments. I've tried to work without :json conversion but either encountered array[] related or malformed object literal syntax errors.


Solution

  • Turns there isn't. The "proper" alternative would need to know the expected keys of the objects in the input array (and for all nested objects/arrays of objects) in order to construct the proper array of tuples.
    This basically requires some sort of runtime referential schema which, even if implemented perfectly, will still require to write boilerplate code for each new query file. At best it will not be better than the functions in helpers module or the json -> record set -> composite type array casting within queries.