Postgres function that accepts an array of composite types

create_message.sql

create or replace function public.create_message (
  message message_dto,
  recipients recipient_dto[]
)
returns bigint as 
$body$
  declare
    message_id bigint;
    
  begin

    -- insert message
    insert into message (
      message_id, 
      subject, 
      body_html, 
      body_text
    ) values (
      default, 
      message.subject, 
      message.body_html, 
      message.body_text
    )
    returning message.message_id 
    into message_id;

    -- insert addresses
    insert into address (
      name, 
      address
    )
    select
      a.name,
      a.address
    from 
      unnest(recipients) as a        
    where not exists (
      select 1
      from
        address as b
      where
            a.name = b.name
        and a.address = b.address
    )
    group by
      a.name,
      a.address
    ;

    -- insert recipients
    insert into recipient (
      message_id,
      recipient_type_id,
      address_id
    ) 
    select 
      message_id,
      a.recipient_type_id,
      b.address_id
    from 
      unnest(recipients) as a
    inner join address b on 
        a.name = b.name and 
        a.address = b.address
    group by 
      a.recipient_type_id, 
      b.address_id;

    return message_id;
    
  end;
$body$
language plpgsql;

Program.cs

var connection = "host=localhost;database=message_db;password=postgres;username=postgres";

var message = new Message
{
    Subject = "subject",
    BodyHtml = "Html",
    BodyText = "Text"
};

var recipients = new List<Recipient>
{
    new Recipient {Address = "[email protected]", Name = "Jake", RecipientTypeId = 1},
    new Recipient {Address = "[email protected]", Name = "Ben", RecipientTypeId = 2},
    new Recipient {Address = "[email protected]", Name = "Suki", RecipientTypeId = 3},
};

NpgsqlConnection.MapCompositeGlobally<Message>("message_dto");
NpgsqlConnection.MapCompositeGlobally<Recipient>("recipient_dto");

using (var connection = new NpgsqlConnection(connectionString))
{
    connection.Open();

    connection.Execute("delete from recipient;");
    connection.Execute("delete from address;");
    connection.Execute("delete from message;");


    NpgsqlCommand command = connection.CreateCommand();

    command.CommandType = CommandType.StoredProcedure;
    command.CommandText = "create_message";
    command.Parameters.AddWithValue("message", message);
    command.Parameters.AddWithValue("recipients", recipients);

    using (NpgsqlDataReader reader = command.ExecuteReader())
    {
        reader.Read();
        var messageId = reader.GetInt64(0);
        Log.Information("MessageId: {messageId}", messageId);
    }
}

schema.sql

-- drop functions
drop function if exists public.create_message (
  message message_dto,
  recipients recipient_dto[]
);

-- drop dtos
drop type if exists message_dto;
drop type if exists recipient_dto;

-- drop tables
drop table if exists recipient;
drop table if exists address;
drop table if exists recipient_type;
drop table if exists message;

-- dtos
create type message_dto as (
  subject text,
  body_html text,
  body_text text
);

create type recipient_dto as (
  name text,
  address text,
  recipient_type_id int
);

-- tables
create table recipient_type (
  recipient_type_id int not null,
  name text not null,
  description text not null,
  
  constraint pk_recipient_type primary key (recipient_type_id),
  constraint ak_recipient_type_name unique (name)
);

create table address (
  address_id bigserial not null,
  name varchar(64) not null,
  address varchar(320) not null,
  
  constraint pk_address primary key (address_id),
  constraint ak_address_unique unique (name, address)
);

create table message (
  message_id bigserial not null,
  subject text not null,
  body_html text not null,
  body_text text not null,
  
  constraint pk_message primary key (message_id)
);

create table recipient (
  message_id bigint not null,
  recipient_type_id bigint not null,
  address_id bigint not null,
  
  constraint pk_recipient primary key (message_id, recipient_type_id, address_id),
  
  constraint fk_recipient_address foreign key (address_id) references address (address_id),
  constraint fk_recipient_message foreign key (message_id) references message (message_id),
  constraint fk_recipient_recipient_type foreign key (recipient_type_id) references recipient_type (recipient_type_id)
);

seed.sql

-- seed tables
insert into recipient_type (recipient_type_id, name, description) values (1, 'To', 'To address'), (2, 'Cc', 'Cc address'),(3, 'Bcc', 'Bcc address');

test_sql.sql

-- test

select public.create_message(

  row('subject', 'text', 'html')::message_dto, 
  
  array[
    row('Jake', '[email protected]', 1),
    row('Ben', '[email protected]', 2),
    row('Suki', '[email protected]', 2)
  ]
  ::recipient_dto[]
  
);