QuerySql.cs

// auto-generated by sqlc - do not edit
// ReSharper disable UseObjectOrCollectionInitializer
// ReSharper disable UseAwaitUsing
// ReSharper disable ConvertToUsingDeclaration
// ReSharper disable NotAccessedPositionalProperty.Global
// ReSharper disable UnusedAutoPropertyAccessor.Global
using Dapper;
using NodaTime;
using NodaTime.Extensions;
using Npgsql;
using NpgsqlTypes;
using System;
using System.Collections.Generic;
using System.Data;
using System.Net;
using System.Net.NetworkInformation;
using System.Text.Json;
using System.Threading;
using System.Threading.Tasks;
using System.Xml;

namespace NpgsqlDapperExampleGen;
public class QuerySql : IDisposable
{
    public QuerySql()
    {
        Utils.ConfigureSqlMapper();
        DefaultTypeMap.MatchNamesWithUnderscores = true;
    }

    public QuerySql(string connectionString) : this()
    {
        this.ConnectionString = connectionString;
        _dataSource = new Lazy<NpgsqlDataSource>(() => NpgsqlDataSource.Create(connectionString!), LazyThreadSafetyMode.ExecutionAndPublication);
    }

    private QuerySql(NpgsqlTransaction transaction) : this()
    {
        this.Transaction = transaction;
    }

    public static QuerySql WithTransaction(NpgsqlTransaction transaction)
    {
        return new QuerySql(transaction);
    }

    private NpgsqlTransaction? Transaction { get; }
    private string? ConnectionString { get; }

    private readonly Lazy<NpgsqlDataSource>? _dataSource;
    private NpgsqlDataSource GetDataSource()
    {
        if (_dataSource == null)
            throw new InvalidOperationException("ConnectionString is required when not using a transaction");
        return _dataSource.Value;
    }

    public void Dispose()
    {
        GC.SuppressFinalize(this);
        if (_dataSource?.IsValueCreated == true)
            _dataSource.Value.Dispose();
    }

    private const string GetAuthorSql = @"SELECT id, name, bio FROM authors
                                          WHERE name = @name LIMIT 1";
    public class GetAuthorRow
    {
        public required long Id { get; init; }
        public required string Name { get; init; }
        public string? Bio { get; init; }
    };
    public class GetAuthorArgs
    {
        public required string Name { get; init; }
    };
    public async Task<GetAuthorRow?> GetAuthorAsync(GetAuthorArgs args)
    {
        var queryParams = new Dictionary<string, object?>();
        queryParams.Add("name", args.Name);
        if (this.Transaction == null)
        {
            using (var connection = await GetDataSource().OpenConnectionAsync())
            {
                var result = await connection.QueryFirstOrDefaultAsync<GetAuthorRow?>(GetAuthorSql, queryParams);
                return result;
            }
        }

        if (this.Transaction?.Connection == null || this.Transaction?.Connection.State != ConnectionState.Open)
            throw new InvalidOperationException("Transaction is provided, but its connection is null.");
        return await this.Transaction.Connection.QueryFirstOrDefaultAsync<GetAuthorRow?>(GetAuthorSql, queryParams, transaction: this.Transaction);
    }

    private const string ListAuthorsSql = @"SELECT id, name, bio 
                                            FROM authors
                                            ORDER BY name
                                            LIMIT @limit
                                            OFFSET @offset";
    public class ListAuthorsRow
    {
        public required long Id { get; init; }
        public required string Name { get; init; }
        public string? Bio { get; init; }
    };
    public class ListAuthorsArgs
    {
        public required int Offset { get; init; }
        public required int Limit { get; init; }
    };
    public async Task<List<ListAuthorsRow>> ListAuthorsAsync(ListAuthorsArgs args)
    {
        var queryParams = new Dictionary<string, object?>();
        queryParams.Add("offset", args.Offset);
        queryParams.Add("limit", args.Limit);
        if (this.Transaction == null)
        {
            using (var connection = await GetDataSource().OpenConnectionAsync())
            {
                var result = await connection.QueryAsync<ListAuthorsRow>(ListAuthorsSql, queryParams);
                return result.AsList();
            }
        }

        if (this.Transaction?.Connection == null || this.Transaction?.Connection.State != ConnectionState.Open)
            throw new InvalidOperationException("Transaction is provided, but its connection is null.");
        return (await this.Transaction.Connection.QueryAsync<ListAuthorsRow>(ListAuthorsSql, queryParams, transaction: this.Transaction)).AsList();
    }

    private const string CreateAuthorSql = "INSERT INTO authors (id, name, bio) VALUES (@id, @name, @bio) RETURNING id, name, bio";
    public class CreateAuthorRow
    {
        public required long Id { get; init; }
        public required string Name { get; init; }
        public string? Bio { get; init; }
    };
    public class CreateAuthorArgs
    {
        public required long Id { get; init; }
        public required string Name { get; init; }
        public string? Bio { get; init; }
    };
    public async Task<CreateAuthorRow?> CreateAuthorAsync(CreateAuthorArgs args)
    {
        var queryParams = new Dictionary<string, object?>();
        queryParams.Add("id", args.Id);
        queryParams.Add("name", args.Name);
        queryParams.Add("bio", args.Bio);
        if (this.Transaction == null)
        {
            using (var connection = await GetDataSource().OpenConnectionAsync())
            {
                var result = await connection.QueryFirstOrDefaultAsync<CreateAuthorRow?>(CreateAuthorSql, queryParams);
                return result;
            }
        }

        if (this.Transaction?.Connection == null || this.Transaction?.Connection.State != ConnectionState.Open)
            throw new InvalidOperationException("Transaction is provided, but its connection is null.");
        return await this.Transaction.Connection.QueryFirstOrDefaultAsync<CreateAuthorRow?>(CreateAuthorSql, queryParams, transaction: this.Transaction);
    }

    private const string CreateAuthorReturnIdSql = "INSERT INTO authors (name, bio) VALUES (@name, @bio) RETURNING id";
    public class CreateAuthorReturnIdRow
    {
        public required long Id { get; init; }
    };
    public class CreateAuthorReturnIdArgs
    {
        public required string Name { get; init; }
        public string? Bio { get; init; }
    };
    public async Task<long> CreateAuthorReturnIdAsync(CreateAuthorReturnIdArgs args)
    {
        var queryParams = new Dictionary<string, object?>();
        queryParams.Add("name", args.Name);
        queryParams.Add("bio", args.Bio);
        if (this.Transaction == null)
        {
            using (var connection = await GetDataSource().OpenConnectionAsync())
            {
                return await connection.QuerySingleAsync<long>(CreateAuthorReturnIdSql, queryParams);
            }
        }

        if (this.Transaction?.Connection == null || this.Transaction?.Connection.State != ConnectionState.Open)
            throw new InvalidOperationException("Transaction is provided, but its connection is null.");
        return await this.Transaction.Connection.QuerySingleAsync<long>(CreateAuthorReturnIdSql, queryParams, transaction: this.Transaction);
    }

    private const string GetAuthorByIdSql = @"SELECT id, name, bio FROM authors
                                              WHERE id = @id LIMIT 1";
    public class GetAuthorByIdRow
    {
        public required long Id { get; init; }
        public required string Name { get; init; }
        public string? Bio { get; init; }
    };
    public class GetAuthorByIdArgs
    {
        public required long Id { get; init; }
    };
    public async Task<GetAuthorByIdRow?> GetAuthorByIdAsync(GetAuthorByIdArgs args)
    {
        var queryParams = new Dictionary<string, object?>();
        queryParams.Add("id", args.Id);
        if (this.Transaction == null)
        {
            using (var connection = await GetDataSource().OpenConnectionAsync())
            {
                var result = await connection.QueryFirstOrDefaultAsync<GetAuthorByIdRow?>(GetAuthorByIdSql, queryParams);
                return result;
            }
        }

        if (this.Transaction?.Connection == null || this.Transaction?.Connection.State != ConnectionState.Open)
            throw new InvalidOperationException("Transaction is provided, but its connection is null.");
        return await this.Transaction.Connection.QueryFirstOrDefaultAsync<GetAuthorByIdRow?>(GetAuthorByIdSql, queryParams, transaction: this.Transaction);
    }

    private const string GetAuthorByNamePatternSql = @"SELECT id, name, bio FROM authors
                                                       WHERE name LIKE COALESCE(@name_pattern, '%')";
    public class GetAuthorByNamePatternRow
    {
        public required long Id { get; init; }
        public required string Name { get; init; }
        public string? Bio { get; init; }
    };
    public class GetAuthorByNamePatternArgs
    {
        public string? NamePattern { get; init; }
    };
    public async Task<List<GetAuthorByNamePatternRow>> GetAuthorByNamePatternAsync(GetAuthorByNamePatternArgs args)
    {
        var queryParams = new Dictionary<string, object?>();
        queryParams.Add("name_pattern", args.NamePattern);
        if (this.Transaction == null)
        {
            using (var connection = await GetDataSource().OpenConnectionAsync())
            {
                var result = await connection.QueryAsync<GetAuthorByNamePatternRow>(GetAuthorByNamePatternSql, queryParams);
                return result.AsList();
            }
        }

        if (this.Transaction?.Connection == null || this.Transaction?.Connection.State != ConnectionState.Open)
            throw new InvalidOperationException("Transaction is provided, but its connection is null.");
        return (await this.Transaction.Connection.QueryAsync<GetAuthorByNamePatternRow>(GetAuthorByNamePatternSql, queryParams, transaction: this.Transaction)).AsList();
    }

    private const string DeleteAuthorSql = @"DELETE FROM authors
                                             WHERE name = @name";
    public class DeleteAuthorArgs
    {
        public required string Name { get; init; }
    };
    public async Task DeleteAuthorAsync(DeleteAuthorArgs args)
    {
        var queryParams = new Dictionary<string, object?>();
        queryParams.Add("name", args.Name);
        if (this.Transaction == null)
        {
            using (var connection = await GetDataSource().OpenConnectionAsync())
            {
                await connection.ExecuteAsync(DeleteAuthorSql, queryParams);
                return;
            }
        }

        if (this.Transaction?.Connection == null || this.Transaction?.Connection.State != ConnectionState.Open)
            throw new InvalidOperationException("Transaction is provided, but its connection is null.");
        await this.Transaction.Connection.ExecuteAsync(DeleteAuthorSql, queryParams, transaction: this.Transaction);
    }

    private const string TruncateAuthorsSql = "TRUNCATE TABLE authors CASCADE";
    public async Task TruncateAuthorsAsync()
    {
        if (this.Transaction == null)
        {
            using (var connection = await GetDataSource().OpenConnectionAsync())
            {
                await connection.ExecuteAsync(TruncateAuthorsSql);
                return;
            }
        }

        if (this.Transaction?.Connection == null || this.Transaction?.Connection.State != ConnectionState.Open)
            throw new InvalidOperationException("Transaction is provided, but its connection is null.");
        await this.Transaction.Connection.ExecuteAsync(TruncateAuthorsSql, transaction: this.Transaction);
    }

    private const string UpdateAuthorsSql = @"UPDATE authors
                                              SET bio = @bio
                                              WHERE bio IS NOT NULL";
    public class UpdateAuthorsArgs
    {
        public string? Bio { get; init; }
    };
    public async Task<long> UpdateAuthorsAsync(UpdateAuthorsArgs args)
    {
        var queryParams = new Dictionary<string, object?>();
        queryParams.Add("bio", args.Bio);
        if (this.Transaction == null)
        {
            using (var connection = await GetDataSource().OpenConnectionAsync())
            {
                return await connection.ExecuteAsync(UpdateAuthorsSql, queryParams);
            }
        }

        if (this.Transaction?.Connection == null || this.Transaction?.Connection.State != ConnectionState.Open)
            throw new InvalidOperationException("Transaction is provided, but its connection is null.");
        return await this.Transaction.Connection.ExecuteAsync(UpdateAuthorsSql, queryParams, transaction: this.Transaction);
    }

    private const string GetAuthorsByIdsSql = @"SELECT id, name, bio FROM authors
                                                WHERE id = ANY(@longArr_1::BIGINT [])";
    public class GetAuthorsByIdsRow
    {
        public required long Id { get; init; }
        public required string Name { get; init; }
        public string? Bio { get; init; }
    };
    public class GetAuthorsByIdsArgs
    {
        public required long[] LongArr1 { get; init; }
    };
    public async Task<List<GetAuthorsByIdsRow>> GetAuthorsByIdsAsync(GetAuthorsByIdsArgs args)
    {
        var queryParams = new Dictionary<string, object?>();
        queryParams.Add("longArr_1", args.LongArr1);
        if (this.Transaction == null)
        {
            using (var connection = await GetDataSource().OpenConnectionAsync())
            {
                var result = await connection.QueryAsync<GetAuthorsByIdsRow>(GetAuthorsByIdsSql, queryParams);
                return result.AsList();
            }
        }

        if (this.Transaction?.Connection == null || this.Transaction?.Connection.State != ConnectionState.Open)
            throw new InvalidOperationException("Transaction is provided, but its connection is null.");
        return (await this.Transaction.Connection.QueryAsync<GetAuthorsByIdsRow>(GetAuthorsByIdsSql, queryParams, transaction: this.Transaction)).AsList();
    }

    private const string GetAuthorsByIdsAndNamesSql = @"SELECT id, name, bio
                                                        FROM authors
                                                        WHERE id = ANY(@longArr_1::BIGINT []) AND name = ANY(@stringArr_2::TEXT [])";
    public class GetAuthorsByIdsAndNamesRow
    {
        public required long Id { get; init; }
        public required string Name { get; init; }
        public string? Bio { get; init; }
    };
    public class GetAuthorsByIdsAndNamesArgs
    {
        public required long[] LongArr1 { get; init; }
        public required string[] StringArr2 { get; init; }
    };
    public async Task<List<GetAuthorsByIdsAndNamesRow>> GetAuthorsByIdsAndNamesAsync(GetAuthorsByIdsAndNamesArgs args)
    {
        var queryParams = new Dictionary<string, object?>();
        queryParams.Add("longArr_1", args.LongArr1);
        queryParams.Add("stringArr_2", args.StringArr2);
        if (this.Transaction == null)
        {
            using (var connection = await GetDataSource().OpenConnectionAsync())
            {
                var result = await connection.QueryAsync<GetAuthorsByIdsAndNamesRow>(GetAuthorsByIdsAndNamesSql, queryParams);
                return result.AsList();
            }
        }

        if (this.Transaction?.Connection == null || this.Transaction?.Connection.State != ConnectionState.Open)
            throw new InvalidOperationException("Transaction is provided, but its connection is null.");
        return (await this.Transaction.Connection.QueryAsync<GetAuthorsByIdsAndNamesRow>(GetAuthorsByIdsAndNamesSql, queryParams, transaction: this.Transaction)).AsList();
    }

    private const string CreateBookSql = "INSERT INTO books (name, author_id) VALUES (@name, @author_id) RETURNING id";
    public class CreateBookRow
    {
        public required Guid Id { get; init; }
    };
    public class CreateBookArgs
    {
        public required string Name { get; init; }
        public required long AuthorId { get; init; }
    };
    public async Task<Guid> CreateBookAsync(CreateBookArgs args)
    {
        var queryParams = new Dictionary<string, object?>();
        queryParams.Add("name", args.Name);
        queryParams.Add("author_id", args.AuthorId);
        if (this.Transaction == null)
        {
            using (var connection = await GetDataSource().OpenConnectionAsync())
            {
                return await connection.QuerySingleAsync<Guid>(CreateBookSql, queryParams);
            }
        }

        if (this.Transaction?.Connection == null || this.Transaction?.Connection.State != ConnectionState.Open)
            throw new InvalidOperationException("Transaction is provided, but its connection is null.");
        return await this.Transaction.Connection.QuerySingleAsync<Guid>(CreateBookSql, queryParams, transaction: this.Transaction);
    }

    private const string ListAllAuthorsBooksSql = @"SELECT
                                                        authors.id, authors.name, authors.bio,
                                                        books.id, books.name, books.author_id, books.description
                                                    FROM authors
                                                    INNER JOIN books ON authors.id = books.author_id
                                                    ORDER BY authors.name";
    public class ListAllAuthorsBooksRow
    {
        public required Author? Author { get; init; }
        public required Book? Book { get; init; }
    };
    public async Task<List<ListAllAuthorsBooksRow>> ListAllAuthorsBooksAsync()
    {
        if (this.Transaction == null)
        {
            using (var connection = await GetDataSource().OpenConnectionAsync())
            {
                using (var command = connection.CreateCommand())
                {
                    command.CommandText = ListAllAuthorsBooksSql;
                    using (var reader = await command.ExecuteReaderAsync())
                    {
                        var result = new List<ListAllAuthorsBooksRow>();
                        while (await reader.ReadAsync())
                            result.Add(new ListAllAuthorsBooksRow { Author = new Author { Id = reader.GetInt64(0), Name = reader.GetString(1), Bio = reader.IsDBNull(2) ? null : reader.GetString(2) }, Book = new Book { Id = reader.GetFieldValue<Guid>(3), Name = reader.GetString(4), AuthorId = reader.GetInt64(5), Description = reader.IsDBNull(6) ? null : reader.GetString(6) } });
                        return result;
                    }
                }
            }
        }

        if (this.Transaction?.Connection == null || this.Transaction?.Connection.State != ConnectionState.Open)
            throw new InvalidOperationException("Transaction is provided, but its connection is null.");
        using (var command = this.Transaction.Connection.CreateCommand())
        {
            command.CommandText = ListAllAuthorsBooksSql;
            command.Transaction = this.Transaction;
            using (var reader = await command.ExecuteReaderAsync())
            {
                var result = new List<ListAllAuthorsBooksRow>();
                while (await reader.ReadAsync())
                    result.Add(new ListAllAuthorsBooksRow { Author = new Author { Id = reader.GetInt64(0), Name = reader.GetString(1), Bio = reader.IsDBNull(2) ? null : reader.GetString(2) }, Book = new Book { Id = reader.GetFieldValue<Guid>(3), Name = reader.GetString(4), AuthorId = reader.GetInt64(5), Description = reader.IsDBNull(6) ? null : reader.GetString(6) } });
                return result;
            }
        }
    }

    private const string GetDuplicateAuthorsSql = @"SELECT
                                                        authors1.id, authors1.name, authors1.bio,
                                                        authors2.id, authors2.name, authors2.bio
                                                    FROM authors AS authors1
                                                    INNER JOIN authors AS authors2 ON authors1.name = authors2.name
                                                    WHERE authors1.id < authors2.id";
    public class GetDuplicateAuthorsRow
    {
        public required Author? Author { get; init; }
        public required Author? Author2 { get; init; }
    };
    public async Task<List<GetDuplicateAuthorsRow>> GetDuplicateAuthorsAsync()
    {
        if (this.Transaction == null)
        {
            using (var connection = await GetDataSource().OpenConnectionAsync())
            {
                using (var command = connection.CreateCommand())
                {
                    command.CommandText = GetDuplicateAuthorsSql;
                    using (var reader = await command.ExecuteReaderAsync())
                    {
                        var result = new List<GetDuplicateAuthorsRow>();
                        while (await reader.ReadAsync())
                            result.Add(new GetDuplicateAuthorsRow { Author = new Author { Id = reader.GetInt64(0), Name = reader.GetString(1), Bio = reader.IsDBNull(2) ? null : reader.GetString(2) }, Author2 = new Author { Id = reader.GetInt64(3), Name = reader.GetString(4), Bio = reader.IsDBNull(5) ? null : reader.GetString(5) } });
                        return result;
                    }
                }
            }
        }

        if (this.Transaction?.Connection == null || this.Transaction?.Connection.State != ConnectionState.Open)
            throw new InvalidOperationException("Transaction is provided, but its connection is null.");
        using (var command = this.Transaction.Connection.CreateCommand())
        {
            command.CommandText = GetDuplicateAuthorsSql;
            command.Transaction = this.Transaction;
            using (var reader = await command.ExecuteReaderAsync())
            {
                var result = new List<GetDuplicateAuthorsRow>();
                while (await reader.ReadAsync())
                    result.Add(new GetDuplicateAuthorsRow { Author = new Author { Id = reader.GetInt64(0), Name = reader.GetString(1), Bio = reader.IsDBNull(2) ? null : reader.GetString(2) }, Author2 = new Author { Id = reader.GetInt64(3), Name = reader.GetString(4), Bio = reader.IsDBNull(5) ? null : reader.GetString(5) } });
                return result;
            }
        }
    }

    private const string GetAuthorsByBookNameSql = @"SELECT
                                                         authors.id, authors.name, authors.bio,
                                                         books.id, books.name, books.author_id, books.description
                                                     FROM authors INNER JOIN books ON authors.id = books.author_id
                                                     WHERE books.name = @name";
    public class GetAuthorsByBookNameRow
    {
        public required long Id { get; init; }
        public required string Name { get; init; }
        public string? Bio { get; init; }
        public required Book? Book { get; init; }
    };
    public class GetAuthorsByBookNameArgs
    {
        public required string Name { get; init; }
    };
    public async Task<List<GetAuthorsByBookNameRow>> GetAuthorsByBookNameAsync(GetAuthorsByBookNameArgs args)
    {
        if (this.Transaction == null)
        {
            using (var connection = await GetDataSource().OpenConnectionAsync())
            {
                using (var command = connection.CreateCommand())
                {
                    command.CommandText = GetAuthorsByBookNameSql;
                    command.Parameters.AddWithValue("@name", args.Name);
                    using (var reader = await command.ExecuteReaderAsync())
                    {
                        var result = new List<GetAuthorsByBookNameRow>();
                        while (await reader.ReadAsync())
                            result.Add(new GetAuthorsByBookNameRow { Id = reader.GetInt64(0), Name = reader.GetString(1), Bio = reader.IsDBNull(2) ? null : reader.GetString(2), Book = new Book { Id = reader.GetFieldValue<Guid>(3), Name = reader.GetString(4), AuthorId = reader.GetInt64(5), Description = reader.IsDBNull(6) ? null : reader.GetString(6) } });
                        return result;
                    }
                }
            }
        }

        if (this.Transaction?.Connection == null || this.Transaction?.Connection.State != ConnectionState.Open)
            throw new InvalidOperationException("Transaction is provided, but its connection is null.");
        using (var command = this.Transaction.Connection.CreateCommand())
        {
            command.CommandText = GetAuthorsByBookNameSql;
            command.Transaction = this.Transaction;
            command.Parameters.AddWithValue("@name", args.Name);
            using (var reader = await command.ExecuteReaderAsync())
            {
                var result = new List<GetAuthorsByBookNameRow>();
                while (await reader.ReadAsync())
                    result.Add(new GetAuthorsByBookNameRow { Id = reader.GetInt64(0), Name = reader.GetString(1), Bio = reader.IsDBNull(2) ? null : reader.GetString(2), Book = new Book { Id = reader.GetFieldValue<Guid>(3), Name = reader.GetString(4), AuthorId = reader.GetInt64(5), Description = reader.IsDBNull(6) ? null : reader.GetString(6) } });
                return result;
            }
        }
    }

    private const string CreateExtendedBioSql = "INSERT INTO extended.bios (author_name, name, bio_type) VALUES (@author_name, @name, @bio_type)";
    public class CreateExtendedBioArgs
    {
        public required string AuthorName { get; init; }
        public required string Name { get; init; }
        public ExtendedBioType? BioType { get; init; }
    };
    public async Task CreateExtendedBioAsync(CreateExtendedBioArgs args)
    {
        var queryParams = new Dictionary<string, object?>();
        queryParams.Add("author_name", args.AuthorName);
        queryParams.Add("name", args.Name);
        queryParams.Add("bio_type", args.BioType != null ? args.BioType.Value.Stringify() : null);
        if (this.Transaction == null)
        {
            using (var connection = await GetDataSource().OpenConnectionAsync())
            {
                await connection.ExecuteAsync(CreateExtendedBioSql, queryParams);
                return;
            }
        }

        if (this.Transaction?.Connection == null || this.Transaction?.Connection.State != ConnectionState.Open)
            throw new InvalidOperationException("Transaction is provided, but its connection is null.");
        await this.Transaction.Connection.ExecuteAsync(CreateExtendedBioSql, queryParams, transaction: this.Transaction);
    }

    private const string GetFirstExtendedBioByTypeSql = "SELECT author_name, name, bio_type FROM extended.bios WHERE bio_type = @bio_type LIMIT 1";
    public class GetFirstExtendedBioByTypeRow
    {
        public required string AuthorName { get; init; }
        public required string Name { get; init; }
        public ExtendedBioType? BioType { get; init; }
    };
    public class GetFirstExtendedBioByTypeArgs
    {
        public ExtendedBioType? BioType { get; init; }
    };
    public async Task<GetFirstExtendedBioByTypeRow?> GetFirstExtendedBioByTypeAsync(GetFirstExtendedBioByTypeArgs args)
    {
        var queryParams = new Dictionary<string, object?>();
        queryParams.Add("bio_type", args.BioType != null ? args.BioType.Value.Stringify() : null);
        if (this.Transaction == null)
        {
            using (var connection = await GetDataSource().OpenConnectionAsync())
            {
                var result = await connection.QueryFirstOrDefaultAsync<GetFirstExtendedBioByTypeRow?>(GetFirstExtendedBioByTypeSql, queryParams);
                return result;
            }
        }

        if (this.Transaction?.Connection == null || this.Transaction?.Connection.State != ConnectionState.Open)
            throw new InvalidOperationException("Transaction is provided, but its connection is null.");
        return await this.Transaction.Connection.QueryFirstOrDefaultAsync<GetFirstExtendedBioByTypeRow?>(GetFirstExtendedBioByTypeSql, queryParams, transaction: this.Transaction);
    }

    private const string TruncateExtendedBiosSql = "TRUNCATE TABLE extended.bios";
    public async Task TruncateExtendedBiosAsync()
    {
        if (this.Transaction == null)
        {
            using (var connection = await GetDataSource().OpenConnectionAsync())
            {
                await connection.ExecuteAsync(TruncateExtendedBiosSql);
                return;
            }
        }

        if (this.Transaction?.Connection == null || this.Transaction?.Connection.State != ConnectionState.Open)
            throw new InvalidOperationException("Transaction is provided, but its connection is null.");
        await this.Transaction.Connection.ExecuteAsync(TruncateExtendedBiosSql, transaction: this.Transaction);
    }

    private const string GetPostgresFunctionsSql = @"SELECT
                                                         MAX(c_integer) AS max_integer,
                                                         MAX(c_varchar) AS max_varchar,
                                                         MAX(c_timestamp) AS max_timestamp
                                                     FROM postgres_datetime_types
                                                     CROSS JOIN postgres_numeric_types
                                                     CROSS JOIN postgres_string_types";
    public class GetPostgresFunctionsRow
    {
        public int? MaxInteger { get; init; }
        public string? MaxVarchar { get; init; }
        public required DateTime MaxTimestamp { get; init; }
    };
    public async Task<GetPostgresFunctionsRow?> GetPostgresFunctionsAsync()
    {
        if (this.Transaction == null)
        {
            using (var connection = await GetDataSource().OpenConnectionAsync())
            {
                var result = await connection.QueryFirstOrDefaultAsync<GetPostgresFunctionsRow?>(GetPostgresFunctionsSql);
                return result;
            }
        }

        if (this.Transaction?.Connection == null || this.Transaction?.Connection.State != ConnectionState.Open)
            throw new InvalidOperationException("Transaction is provided, but its connection is null.");
        return await this.Transaction.Connection.QueryFirstOrDefaultAsync<GetPostgresFunctionsRow?>(GetPostgresFunctionsSql, transaction: this.Transaction);
    }

    private const string InsertPostgresNumericTypesSql = @"
                                                           INSERT INTO postgres_numeric_types
                                                           (
                                                               c_boolean,
                                                               c_bit,
                                                               c_smallint,
                                                               c_integer,
                                                               c_bigint,
                                                               c_decimal,
                                                               c_numeric,
                                                               c_real,
                                                               c_double_precision,
                                                               c_money
                                                           )
                                                           VALUES (@c_boolean, @c_bit, @c_smallint, @c_integer, @c_bigint, @c_decimal, @c_numeric, @c_real, @c_double_precision, @c_money)";
    public class InsertPostgresNumericTypesArgs
    {
        public bool? CBoolean { get; init; }
        public byte[]? CBit { get; init; }
        public short? CSmallint { get; init; }
        public int? CInteger { get; init; }
        public long? CBigint { get; init; }
        public decimal? CDecimal { get; init; }
        public decimal? CNumeric { get; init; }
        public float? CReal { get; init; }
        public double? CDoublePrecision { get; init; }
        public decimal? CMoney { get; init; }
    };
    public async Task InsertPostgresNumericTypesAsync(InsertPostgresNumericTypesArgs args)
    {
        var queryParams = new Dictionary<string, object?>();
        queryParams.Add("c_boolean", args.CBoolean);
        queryParams.Add("c_bit", args.CBit);
        queryParams.Add("c_smallint", args.CSmallint);
        queryParams.Add("c_integer", args.CInteger);
        queryParams.Add("c_bigint", args.CBigint);
        queryParams.Add("c_decimal", args.CDecimal);
        queryParams.Add("c_numeric", args.CNumeric);
        queryParams.Add("c_real", args.CReal);
        queryParams.Add("c_double_precision", args.CDoublePrecision);
        queryParams.Add("c_money", args.CMoney);
        if (this.Transaction == null)
        {
            using (var connection = await GetDataSource().OpenConnectionAsync())
            {
                await connection.ExecuteAsync(InsertPostgresNumericTypesSql, queryParams);
                return;
            }
        }

        if (this.Transaction?.Connection == null || this.Transaction?.Connection.State != ConnectionState.Open)
            throw new InvalidOperationException("Transaction is provided, but its connection is null.");
        await this.Transaction.Connection.ExecuteAsync(InsertPostgresNumericTypesSql, queryParams, transaction: this.Transaction);
    }

    private const string GetPostgresNumericTypesSql = "SELECT c_boolean, c_bit, c_smallint, c_integer, c_bigint, c_decimal, c_numeric, c_real, c_double_precision, c_money FROM postgres_numeric_types LIMIT 1";
    public class GetPostgresNumericTypesRow
    {
        public bool? CBoolean { get; init; }
        public byte[]? CBit { get; init; }
        public short? CSmallint { get; init; }
        public int? CInteger { get; init; }
        public long? CBigint { get; init; }
        public decimal? CDecimal { get; init; }
        public decimal? CNumeric { get; init; }
        public float? CReal { get; init; }
        public double? CDoublePrecision { get; init; }
        public decimal? CMoney { get; init; }
    };
    public async Task<GetPostgresNumericTypesRow?> GetPostgresNumericTypesAsync()
    {
        if (this.Transaction == null)
        {
            using (var connection = await GetDataSource().OpenConnectionAsync())
            {
                var result = await connection.QueryFirstOrDefaultAsync<GetPostgresNumericTypesRow?>(GetPostgresNumericTypesSql);
                return result;
            }
        }

        if (this.Transaction?.Connection == null || this.Transaction?.Connection.State != ConnectionState.Open)
            throw new InvalidOperationException("Transaction is provided, but its connection is null.");
        return await this.Transaction.Connection.QueryFirstOrDefaultAsync<GetPostgresNumericTypesRow?>(GetPostgresNumericTypesSql, transaction: this.Transaction);
    }

    private const string TruncatePostgresNumericTypesSql = "TRUNCATE TABLE postgres_numeric_types";
    public async Task TruncatePostgresNumericTypesAsync()
    {
        if (this.Transaction == null)
        {
            using (var connection = await GetDataSource().OpenConnectionAsync())
            {
                await connection.ExecuteAsync(TruncatePostgresNumericTypesSql);
                return;
            }
        }

        if (this.Transaction?.Connection == null || this.Transaction?.Connection.State != ConnectionState.Open)
            throw new InvalidOperationException("Transaction is provided, but its connection is null.");
        await this.Transaction.Connection.ExecuteAsync(TruncatePostgresNumericTypesSql, transaction: this.Transaction);
    }

    private const string GetPostgresNumericTypesCntSql = @"SELECT
                                                               c_boolean,
                                                               c_bit,
                                                               c_smallint,
                                                               c_integer,
                                                               c_bigint,
                                                               c_decimal,
                                                               c_numeric,
                                                               c_real,
                                                               c_double_precision,
                                                               c_money,
                                                               COUNT(*) AS cnt
                                                           FROM postgres_numeric_types
                                                           GROUP BY
                                                               c_boolean,
                                                               c_bit,
                                                               c_smallint,
                                                               c_integer,
                                                               c_bigint,
                                                               c_decimal,
                                                               c_numeric,
                                                               c_real,
                                                               c_double_precision,
                                                               c_money
                                                           LIMIT 1";
    public class GetPostgresNumericTypesCntRow
    {
        public bool? CBoolean { get; init; }
        public byte[]? CBit { get; init; }
        public short? CSmallint { get; init; }
        public int? CInteger { get; init; }
        public long? CBigint { get; init; }
        public decimal? CDecimal { get; init; }
        public decimal? CNumeric { get; init; }
        public float? CReal { get; init; }
        public double? CDoublePrecision { get; init; }
        public decimal? CMoney { get; init; }
        public required long Cnt { get; init; }
    };
    public async Task<GetPostgresNumericTypesCntRow?> GetPostgresNumericTypesCntAsync()
    {
        if (this.Transaction == null)
        {
            using (var connection = await GetDataSource().OpenConnectionAsync())
            {
                var result = await connection.QueryFirstOrDefaultAsync<GetPostgresNumericTypesCntRow?>(GetPostgresNumericTypesCntSql);
                return result;
            }
        }

        if (this.Transaction?.Connection == null || this.Transaction?.Connection.State != ConnectionState.Open)
            throw new InvalidOperationException("Transaction is provided, but its connection is null.");
        return await this.Transaction.Connection.QueryFirstOrDefaultAsync<GetPostgresNumericTypesCntRow?>(GetPostgresNumericTypesCntSql, transaction: this.Transaction);
    }

    private const string InsertPostgresNumericTypesBatchSql = "COPY postgres_numeric_types (c_boolean, c_bit, c_smallint, c_integer, c_bigint, c_decimal, c_numeric, c_real, c_double_precision, c_money) FROM STDIN (FORMAT BINARY)";
    public class InsertPostgresNumericTypesBatchArgs
    {
        public bool? CBoolean { get; init; }
        public byte[]? CBit { get; init; }
        public short? CSmallint { get; init; }
        public int? CInteger { get; init; }
        public long? CBigint { get; init; }
        public decimal? CDecimal { get; init; }
        public decimal? CNumeric { get; init; }
        public float? CReal { get; init; }
        public double? CDoublePrecision { get; init; }
        public decimal? CMoney { get; init; }
    };
    public async Task InsertPostgresNumericTypesBatchAsync(List<InsertPostgresNumericTypesBatchArgs> args)
    {
        using (var connection = await GetDataSource().OpenConnectionAsync())
        {
            using (var writer = await connection.BeginBinaryImportAsync(InsertPostgresNumericTypesBatchSql))
            {
                foreach (var row in args)
                {
                    await writer.StartRowAsync();
                    await writer.WriteAsync(row.CBoolean);
                    await writer.WriteAsync(row.CBit);
                    await writer.WriteAsync(row.CSmallint);
                    await writer.WriteAsync(row.CInteger);
                    await writer.WriteAsync(row.CBigint);
                    await writer.WriteAsync(row.CDecimal);
                    await writer.WriteAsync(row.CNumeric);
                    await writer.WriteAsync(row.CReal);
                    await writer.WriteAsync(row.CDoublePrecision);
                    await writer.WriteAsync(row.CMoney, NpgsqlDbType.Money);
                }

                await writer.CompleteAsync();
            }
        }
    }

    private const string InsertPostgresStringTypesSql = @"
                                                          INSERT INTO postgres_string_types
                                                          (
                                                              c_char,
                                                              c_varchar,
                                                              c_character_varying,
                                                              c_bpchar,
                                                              c_text
                                                          )
                                                          VALUES (@c_char, @c_varchar, @c_character_varying, @c_bpchar, @c_text)";
    public class InsertPostgresStringTypesArgs
    {
        public string? CChar { get; init; }
        public string? CVarchar { get; init; }
        public string? CCharacterVarying { get; init; }
        public string? CBpchar { get; init; }
        public string? CText { get; init; }
    };
    public async Task InsertPostgresStringTypesAsync(InsertPostgresStringTypesArgs args)
    {
        var queryParams = new Dictionary<string, object?>();
        queryParams.Add("c_char", args.CChar);
        queryParams.Add("c_varchar", args.CVarchar);
        queryParams.Add("c_character_varying", args.CCharacterVarying);
        queryParams.Add("c_bpchar", args.CBpchar);
        queryParams.Add("c_text", args.CText);
        if (this.Transaction == null)
        {
            using (var connection = await GetDataSource().OpenConnectionAsync())
            {
                await connection.ExecuteAsync(InsertPostgresStringTypesSql, queryParams);
                return;
            }
        }

        if (this.Transaction?.Connection == null || this.Transaction?.Connection.State != ConnectionState.Open)
            throw new InvalidOperationException("Transaction is provided, but its connection is null.");
        await this.Transaction.Connection.ExecuteAsync(InsertPostgresStringTypesSql, queryParams, transaction: this.Transaction);
    }

    private const string InsertPostgresStringTypesBatchSql = "COPY postgres_string_types (c_char, c_varchar, c_character_varying, c_bpchar, c_text) FROM STDIN (FORMAT BINARY)";
    public class InsertPostgresStringTypesBatchArgs
    {
        public string? CChar { get; init; }
        public string? CVarchar { get; init; }
        public string? CCharacterVarying { get; init; }
        public string? CBpchar { get; init; }
        public string? CText { get; init; }
    };
    public async Task InsertPostgresStringTypesBatchAsync(List<InsertPostgresStringTypesBatchArgs> args)
    {
        using (var connection = await GetDataSource().OpenConnectionAsync())
        {
            using (var writer = await connection.BeginBinaryImportAsync(InsertPostgresStringTypesBatchSql))
            {
                foreach (var row in args)
                {
                    await writer.StartRowAsync();
                    await writer.WriteAsync(row.CChar);
                    await writer.WriteAsync(row.CVarchar);
                    await writer.WriteAsync(row.CCharacterVarying);
                    await writer.WriteAsync(row.CBpchar);
                    await writer.WriteAsync(row.CText);
                }

                await writer.CompleteAsync();
            }
        }
    }

    private const string GetPostgresStringTypesSql = "SELECT c_char, c_varchar, c_character_varying, c_bpchar, c_text FROM postgres_string_types LIMIT 1";
    public class GetPostgresStringTypesRow
    {
        public string? CChar { get; init; }
        public string? CVarchar { get; init; }
        public string? CCharacterVarying { get; init; }
        public string? CBpchar { get; init; }
        public string? CText { get; init; }
    };
    public async Task<GetPostgresStringTypesRow?> GetPostgresStringTypesAsync()
    {
        if (this.Transaction == null)
        {
            using (var connection = await GetDataSource().OpenConnectionAsync())
            {
                var result = await connection.QueryFirstOrDefaultAsync<GetPostgresStringTypesRow?>(GetPostgresStringTypesSql);
                return result;
            }
        }

        if (this.Transaction?.Connection == null || this.Transaction?.Connection.State != ConnectionState.Open)
            throw new InvalidOperationException("Transaction is provided, but its connection is null.");
        return await this.Transaction.Connection.QueryFirstOrDefaultAsync<GetPostgresStringTypesRow?>(GetPostgresStringTypesSql, transaction: this.Transaction);
    }

    private const string TruncatePostgresStringTypesSql = "TRUNCATE TABLE postgres_string_types";
    public async Task TruncatePostgresStringTypesAsync()
    {
        if (this.Transaction == null)
        {
            using (var connection = await GetDataSource().OpenConnectionAsync())
            {
                await connection.ExecuteAsync(TruncatePostgresStringTypesSql);
                return;
            }
        }

        if (this.Transaction?.Connection == null || this.Transaction?.Connection.State != ConnectionState.Open)
            throw new InvalidOperationException("Transaction is provided, but its connection is null.");
        await this.Transaction.Connection.ExecuteAsync(TruncatePostgresStringTypesSql, transaction: this.Transaction);
    }

    private const string GetPostgresStringTypesCntSql = @"SELECT
                                                              c_char,
                                                              c_varchar,
                                                              c_character_varying,
                                                              c_bpchar,
                                                              c_text,
                                                              COUNT(*) AS cnt
                                                          FROM postgres_string_types
                                                          GROUP BY
                                                              c_char,
                                                              c_varchar,
                                                              c_character_varying,
                                                              c_bpchar,
                                                              c_text
                                                          LIMIT 1";
    public class GetPostgresStringTypesCntRow
    {
        public string? CChar { get; init; }
        public string? CVarchar { get; init; }
        public string? CCharacterVarying { get; init; }
        public string? CBpchar { get; init; }
        public string? CText { get; init; }
        public required long Cnt { get; init; }
    };
    public async Task<GetPostgresStringTypesCntRow?> GetPostgresStringTypesCntAsync()
    {
        if (this.Transaction == null)
        {
            using (var connection = await GetDataSource().OpenConnectionAsync())
            {
                var result = await connection.QueryFirstOrDefaultAsync<GetPostgresStringTypesCntRow?>(GetPostgresStringTypesCntSql);
                return result;
            }
        }

        if (this.Transaction?.Connection == null || this.Transaction?.Connection.State != ConnectionState.Open)
            throw new InvalidOperationException("Transaction is provided, but its connection is null.");
        return await this.Transaction.Connection.QueryFirstOrDefaultAsync<GetPostgresStringTypesCntRow?>(GetPostgresStringTypesCntSql, transaction: this.Transaction);
    }

    private const string GetPostgresStringTypesTextSearchSql = @"WITH txt_query AS (
                                                                     SELECT 
                                                                         c_text, 
                                                                         to_tsquery('english', @to_tsquery) AS query,
                                                                         to_tsvector('english', c_text) AS tsv
                                                                     FROM postgres_string_types 
                                                                     WHERE c_text @@ to_tsquery('english', @to_tsquery)
                                                                 )
                                                                 
                                                                 SELECT txt_query.c_text, txt_query.query, txt_query.tsv, ts_rank(tsv, query) AS rnk
                                                                 FROM txt_query
                                                                 ORDER BY rnk DESC
                                                                 LIMIT 1";
    public class GetPostgresStringTypesTextSearchRow
    {
        public string? CText { get; init; }
        public required NpgsqlTsQuery Query { get; init; }
        public required NpgsqlTsVector Tsv { get; init; }
        public required float Rnk { get; init; }
    };
    public class GetPostgresStringTypesTextSearchArgs
    {
        public required string ToTsquery { get; init; }
    };
    public async Task<GetPostgresStringTypesTextSearchRow?> GetPostgresStringTypesTextSearchAsync(GetPostgresStringTypesTextSearchArgs args)
    {
        var queryParams = new Dictionary<string, object?>();
        queryParams.Add("to_tsquery", args.ToTsquery);
        if (this.Transaction == null)
        {
            using (var connection = await GetDataSource().OpenConnectionAsync())
            {
                var result = await connection.QueryFirstOrDefaultAsync<GetPostgresStringTypesTextSearchRow?>(GetPostgresStringTypesTextSearchSql, queryParams);
                return result;
            }
        }

        if (this.Transaction?.Connection == null || this.Transaction?.Connection.State != ConnectionState.Open)
            throw new InvalidOperationException("Transaction is provided, but its connection is null.");
        return await this.Transaction.Connection.QueryFirstOrDefaultAsync<GetPostgresStringTypesTextSearchRow?>(GetPostgresStringTypesTextSearchSql, queryParams, transaction: this.Transaction);
    }

    private const string InsertPostgresDateTimeTypesSql = @"
                                                            INSERT INTO postgres_datetime_types
                                                            (
                                                                c_date,
                                                                c_time,
                                                                c_timestamp,
                                                                c_timestamp_with_tz,
                                                                c_interval,
                                                                c_timestamp_noda_instant_override
                                                            ) VALUES (@c_date, @c_time, @c_timestamp, @c_timestamp_with_tz, @c_interval, @c_timestamp_noda_instant_override)";
    public class InsertPostgresDateTimeTypesArgs
    {
        public DateTime? CDate { get; init; }
        public TimeSpan? CTime { get; init; }
        public DateTime? CTimestamp { get; init; }
        public DateTime? CTimestampWithTz { get; init; }
        public TimeSpan? CInterval { get; init; }
        public Instant? CTimestampNodaInstantOverride { get; init; }
    };
    public async Task InsertPostgresDateTimeTypesAsync(InsertPostgresDateTimeTypesArgs args)
    {
        var queryParams = new Dictionary<string, object?>();
        queryParams.Add("c_date", args.CDate);
        queryParams.Add("c_time", args.CTime);
        queryParams.Add("c_timestamp", args.CTimestamp);
        queryParams.Add("c_timestamp_with_tz", args.CTimestampWithTz);
        queryParams.Add("c_interval", args.CInterval);
        queryParams.Add("c_timestamp_noda_instant_override", args.CTimestampNodaInstantOverride is null ? null : (DateTime? )DateTime.SpecifyKind(args.CTimestampNodaInstantOverride.Value.ToDateTimeUtc(), DateTimeKind.Unspecified));
        if (this.Transaction == null)
        {
            using (var connection = await GetDataSource().OpenConnectionAsync())
            {
                await connection.ExecuteAsync(InsertPostgresDateTimeTypesSql, queryParams);
                return;
            }
        }

        if (this.Transaction?.Connection == null || this.Transaction?.Connection.State != ConnectionState.Open)
            throw new InvalidOperationException("Transaction is provided, but its connection is null.");
        await this.Transaction.Connection.ExecuteAsync(InsertPostgresDateTimeTypesSql, queryParams, transaction: this.Transaction);
    }

    private const string GetPostgresDateTimeTypesSql = "SELECT c_date, c_time, c_timestamp, c_timestamp_with_tz, c_interval, c_timestamp_noda_instant_override FROM postgres_datetime_types LIMIT 1";
    public class GetPostgresDateTimeTypesRow
    {
        public DateTime? CDate { get; init; }
        public TimeSpan? CTime { get; init; }
        public DateTime? CTimestamp { get; init; }
        public DateTime? CTimestampWithTz { get; init; }
        public TimeSpan? CInterval { get; init; }
        public Instant? CTimestampNodaInstantOverride { get; init; }
    };
    public async Task<GetPostgresDateTimeTypesRow?> GetPostgresDateTimeTypesAsync()
    {
        if (this.Transaction == null)
        {
            using (var connection = await GetDataSource().OpenConnectionAsync())
            {
                var result = await connection.QueryFirstOrDefaultAsync<GetPostgresDateTimeTypesRow?>(GetPostgresDateTimeTypesSql);
                return result;
            }
        }

        if (this.Transaction?.Connection == null || this.Transaction?.Connection.State != ConnectionState.Open)
            throw new InvalidOperationException("Transaction is provided, but its connection is null.");
        return await this.Transaction.Connection.QueryFirstOrDefaultAsync<GetPostgresDateTimeTypesRow?>(GetPostgresDateTimeTypesSql, transaction: this.Transaction);
    }

    private const string TruncatePostgresDateTimeTypesSql = "TRUNCATE TABLE postgres_datetime_types";
    public async Task TruncatePostgresDateTimeTypesAsync()
    {
        if (this.Transaction == null)
        {
            using (var connection = await GetDataSource().OpenConnectionAsync())
            {
                await connection.ExecuteAsync(TruncatePostgresDateTimeTypesSql);
                return;
            }
        }

        if (this.Transaction?.Connection == null || this.Transaction?.Connection.State != ConnectionState.Open)
            throw new InvalidOperationException("Transaction is provided, but its connection is null.");
        await this.Transaction.Connection.ExecuteAsync(TruncatePostgresDateTimeTypesSql, transaction: this.Transaction);
    }

    private const string GetPostgresDateTimeTypesCntSql = @"SELECT
                                                                c_date,
                                                                c_time,
                                                                c_timestamp,
                                                                c_timestamp_with_tz,
                                                                c_interval,
                                                                COUNT(*) AS cnt
                                                            FROM postgres_datetime_types
                                                            GROUP BY
                                                                c_date,
                                                                c_time,
                                                                c_timestamp,
                                                                c_timestamp_with_tz,
                                                                c_interval
                                                            LIMIT 1";
    public class GetPostgresDateTimeTypesCntRow
    {
        public DateTime? CDate { get; init; }
        public TimeSpan? CTime { get; init; }
        public DateTime? CTimestamp { get; init; }
        public DateTime? CTimestampWithTz { get; init; }
        public TimeSpan? CInterval { get; init; }
        public required long Cnt { get; init; }
    };
    public async Task<GetPostgresDateTimeTypesCntRow?> GetPostgresDateTimeTypesCntAsync()
    {
        if (this.Transaction == null)
        {
            using (var connection = await GetDataSource().OpenConnectionAsync())
            {
                var result = await connection.QueryFirstOrDefaultAsync<GetPostgresDateTimeTypesCntRow?>(GetPostgresDateTimeTypesCntSql);
                return result;
            }
        }

        if (this.Transaction?.Connection == null || this.Transaction?.Connection.State != ConnectionState.Open)
            throw new InvalidOperationException("Transaction is provided, but its connection is null.");
        return await this.Transaction.Connection.QueryFirstOrDefaultAsync<GetPostgresDateTimeTypesCntRow?>(GetPostgresDateTimeTypesCntSql, transaction: this.Transaction);
    }

    private const string InsertPostgresDateTimeTypesBatchSql = "COPY postgres_datetime_types (c_date, c_time, c_timestamp, c_timestamp_with_tz, c_interval) FROM STDIN (FORMAT BINARY)";
    public class InsertPostgresDateTimeTypesBatchArgs
    {
        public DateTime? CDate { get; init; }
        public TimeSpan? CTime { get; init; }
        public DateTime? CTimestamp { get; init; }
        public DateTime? CTimestampWithTz { get; init; }
        public TimeSpan? CInterval { get; init; }
    };
    public async Task InsertPostgresDateTimeTypesBatchAsync(List<InsertPostgresDateTimeTypesBatchArgs> args)
    {
        using (var connection = await GetDataSource().OpenConnectionAsync())
        {
            using (var writer = await connection.BeginBinaryImportAsync(InsertPostgresDateTimeTypesBatchSql))
            {
                foreach (var row in args)
                {
                    await writer.StartRowAsync();
                    await writer.WriteAsync(row.CDate, NpgsqlDbType.Date);
                    await writer.WriteAsync(row.CTime, NpgsqlDbType.Time);
                    await writer.WriteAsync(row.CTimestamp, NpgsqlDbType.Timestamp);
                    await writer.WriteAsync(row.CTimestampWithTz, NpgsqlDbType.TimestampTz);
                    await writer.WriteAsync(row.CInterval, NpgsqlDbType.Interval);
                }

                await writer.CompleteAsync();
            }
        }
    }

    private const string InsertPostgresNetworkTypesSql = @"
                                                           INSERT INTO postgres_network_types
                                                           (
                                                               c_cidr,
                                                               c_inet,
                                                               c_macaddr,
                                                               c_macaddr8
                                                           ) VALUES (
                                                               @c_cidr, 
                                                               @c_inet, 
                                                               @c_macaddr, 
                                                               @c_macaddr8::macaddr8
                                                           )";
    public class InsertPostgresNetworkTypesArgs
    {
        public NpgsqlCidr? CCidr { get; init; }
        public IPAddress? CInet { get; init; }
        public PhysicalAddress? CMacaddr { get; init; }
        public string? CMacaddr8 { get; init; }
    };
    public async Task InsertPostgresNetworkTypesAsync(InsertPostgresNetworkTypesArgs args)
    {
        var queryParams = new Dictionary<string, object?>();
        queryParams.Add("c_cidr", args.CCidr);
        queryParams.Add("c_inet", args.CInet);
        queryParams.Add("c_macaddr", args.CMacaddr);
        queryParams.Add("c_macaddr8", args.CMacaddr8);
        if (this.Transaction == null)
        {
            using (var connection = await GetDataSource().OpenConnectionAsync())
            {
                await connection.ExecuteAsync(InsertPostgresNetworkTypesSql, queryParams);
                return;
            }
        }

        if (this.Transaction?.Connection == null || this.Transaction?.Connection.State != ConnectionState.Open)
            throw new InvalidOperationException("Transaction is provided, but its connection is null.");
        await this.Transaction.Connection.ExecuteAsync(InsertPostgresNetworkTypesSql, queryParams, transaction: this.Transaction);
    }

    private const string GetPostgresNetworkTypesSql = @"SELECT
                                                            c_cidr,
                                                            c_inet,
                                                            c_macaddr,
                                                            c_macaddr8::TEXT AS c_macaddr8
                                                        FROM postgres_network_types
                                                        LIMIT 1";
    public class GetPostgresNetworkTypesRow
    {
        public NpgsqlCidr? CCidr { get; init; }
        public IPAddress? CInet { get; init; }
        public PhysicalAddress? CMacaddr { get; init; }
        public string? CMacaddr8 { get; init; }
    };
    public async Task<GetPostgresNetworkTypesRow?> GetPostgresNetworkTypesAsync()
    {
        if (this.Transaction == null)
        {
            using (var connection = await GetDataSource().OpenConnectionAsync())
            {
                var result = await connection.QueryFirstOrDefaultAsync<GetPostgresNetworkTypesRow?>(GetPostgresNetworkTypesSql);
                return result;
            }
        }

        if (this.Transaction?.Connection == null || this.Transaction?.Connection.State != ConnectionState.Open)
            throw new InvalidOperationException("Transaction is provided, but its connection is null.");
        return await this.Transaction.Connection.QueryFirstOrDefaultAsync<GetPostgresNetworkTypesRow?>(GetPostgresNetworkTypesSql, transaction: this.Transaction);
    }

    private const string TruncatePostgresNetworkTypesSql = "TRUNCATE TABLE postgres_network_types";
    public async Task TruncatePostgresNetworkTypesAsync()
    {
        if (this.Transaction == null)
        {
            using (var connection = await GetDataSource().OpenConnectionAsync())
            {
                await connection.ExecuteAsync(TruncatePostgresNetworkTypesSql);
                return;
            }
        }

        if (this.Transaction?.Connection == null || this.Transaction?.Connection.State != ConnectionState.Open)
            throw new InvalidOperationException("Transaction is provided, but its connection is null.");
        await this.Transaction.Connection.ExecuteAsync(TruncatePostgresNetworkTypesSql, transaction: this.Transaction);
    }

    private const string GetPostgresNetworkTypesCntSql = @"SELECT
                                                               c_cidr,
                                                               c_inet,
                                                               c_macaddr,
                                                               COUNT(*) AS cnt
                                                           FROM postgres_network_types
                                                           GROUP BY
                                                               c_cidr,
                                                               c_inet,
                                                               c_macaddr
                                                           LIMIT 1";
    public class GetPostgresNetworkTypesCntRow
    {
        public NpgsqlCidr? CCidr { get; init; }
        public IPAddress? CInet { get; init; }
        public PhysicalAddress? CMacaddr { get; init; }
        public required long Cnt { get; init; }
    };
    public async Task<GetPostgresNetworkTypesCntRow?> GetPostgresNetworkTypesCntAsync()
    {
        if (this.Transaction == null)
        {
            using (var connection = await GetDataSource().OpenConnectionAsync())
            {
                var result = await connection.QueryFirstOrDefaultAsync<GetPostgresNetworkTypesCntRow?>(GetPostgresNetworkTypesCntSql);
                return result;
            }
        }

        if (this.Transaction?.Connection == null || this.Transaction?.Connection.State != ConnectionState.Open)
            throw new InvalidOperationException("Transaction is provided, but its connection is null.");
        return await this.Transaction.Connection.QueryFirstOrDefaultAsync<GetPostgresNetworkTypesCntRow?>(GetPostgresNetworkTypesCntSql, transaction: this.Transaction);
    }

    private const string InsertPostgresNetworkTypesBatchSql = "COPY postgres_network_types (c_cidr, c_inet, c_macaddr) FROM STDIN (FORMAT BINARY)";
    public class InsertPostgresNetworkTypesBatchArgs
    {
        public NpgsqlCidr? CCidr { get; init; }
        public IPAddress? CInet { get; init; }
        public PhysicalAddress? CMacaddr { get; init; }
    };
    public async Task InsertPostgresNetworkTypesBatchAsync(List<InsertPostgresNetworkTypesBatchArgs> args)
    {
        using (var connection = await GetDataSource().OpenConnectionAsync())
        {
            using (var writer = await connection.BeginBinaryImportAsync(InsertPostgresNetworkTypesBatchSql))
            {
                foreach (var row in args)
                {
                    await writer.StartRowAsync();
                    await writer.WriteAsync(row.CCidr);
                    await writer.WriteAsync(row.CInet);
                    await writer.WriteAsync(row.CMacaddr);
                }

                await writer.CompleteAsync();
            }
        }
    }

    private const string InsertPostgresSpecialTypesSql = @"
                                                           INSERT INTO postgres_special_types
                                                           (
                                                               c_json,
                                                               c_json_string_override,
                                                               c_jsonb,
                                                               c_jsonpath,
                                                               c_xml,
                                                               c_xml_string_override,
                                                               c_uuid,
                                                               c_enum,
                                                               c_qualified_enum
                                                           )
                                                           VALUES (
                                                               @c_json, 
                                                               @c_json_string_override::json, 
                                                               @c_jsonb,
                                                               @c_jsonpath::jsonpath,
                                                               @c_xml::xml,
                                                               @c_xml_string_override::xml,
                                                               @c_uuid,
                                                               @c_enum::c_enum,
                                                               @c_qualified_enum::c_enum
                                                           )";
    public class InsertPostgresSpecialTypesArgs
    {
        public JsonElement? CJson { get; init; }
        public string? CJsonStringOverride { get; init; }
        public JsonElement? CJsonb { get; init; }
        public string? CJsonpath { get; init; }
        public XmlDocument? CXml { get; init; }
        public string? CXmlStringOverride { get; init; }
        public Guid? CUuid { get; init; }
        public CEnum? CEnum { get; init; }
        public CEnum? CQualifiedEnum { get; init; }
    };
    public async Task InsertPostgresSpecialTypesAsync(InsertPostgresSpecialTypesArgs args)
    {
        var queryParams = new Dictionary<string, object?>();
        queryParams.Add("c_json", args.CJson.HasValue ? (object)args.CJson.Value : null);
        queryParams.Add("c_json_string_override", args.CJsonStringOverride);
        queryParams.Add("c_jsonb", args.CJsonb.HasValue ? (object)args.CJsonb.Value : null);
        queryParams.Add("c_jsonpath", args.CJsonpath);
        queryParams.Add("c_xml", args.CXml != null ? args.CXml.OuterXml : null);
        queryParams.Add("c_xml_string_override", args.CXmlStringOverride);
        queryParams.Add("c_uuid", args.CUuid);
        queryParams.Add("c_enum", args.CEnum != null ? args.CEnum.Value.Stringify() : null);
        queryParams.Add("c_qualified_enum", args.CQualifiedEnum != null ? args.CQualifiedEnum.Value.Stringify() : null);
        if (this.Transaction == null)
        {
            using (var connection = await GetDataSource().OpenConnectionAsync())
            {
                await connection.ExecuteAsync(InsertPostgresSpecialTypesSql, queryParams);
                return;
            }
        }

        if (this.Transaction?.Connection == null || this.Transaction?.Connection.State != ConnectionState.Open)
            throw new InvalidOperationException("Transaction is provided, but its connection is null.");
        await this.Transaction.Connection.ExecuteAsync(InsertPostgresSpecialTypesSql, queryParams, transaction: this.Transaction);
    }

    private const string InsertPostgresNotNullTypesSql = @"INSERT INTO postgres_not_null_types
                                                           (
                                                               c_enum_not_null
                                                           )
                                                           VALUES (
                                                               @c_enum_not_null::c_enum
                                                           )";
    public class InsertPostgresNotNullTypesArgs
    {
        public required CEnum CEnumNotNull { get; init; }
    };
    public async Task InsertPostgresNotNullTypesAsync(InsertPostgresNotNullTypesArgs args)
    {
        var queryParams = new Dictionary<string, object?>();
        queryParams.Add("c_enum_not_null", args.CEnumNotNull.Stringify());
        if (this.Transaction == null)
        {
            using (var connection = await GetDataSource().OpenConnectionAsync())
            {
                await connection.ExecuteAsync(InsertPostgresNotNullTypesSql, queryParams);
                return;
            }
        }

        if (this.Transaction?.Connection == null || this.Transaction?.Connection.State != ConnectionState.Open)
            throw new InvalidOperationException("Transaction is provided, but its connection is null.");
        await this.Transaction.Connection.ExecuteAsync(InsertPostgresNotNullTypesSql, queryParams, transaction: this.Transaction);
    }

    private const string GetPostgresNotNullTypesSql = @"SELECT
                                                            c_enum_not_null
                                                        FROM postgres_not_null_types 
                                                        LIMIT 1";
    public class GetPostgresNotNullTypesRow
    {
        public required CEnum CEnumNotNull { get; init; }
    };
    public async Task<GetPostgresNotNullTypesRow?> GetPostgresNotNullTypesAsync()
    {
        if (this.Transaction == null)
        {
            using (var connection = await GetDataSource().OpenConnectionAsync())
            {
                var result = await connection.QueryFirstOrDefaultAsync<GetPostgresNotNullTypesRow?>(GetPostgresNotNullTypesSql);
                return result;
            }
        }

        if (this.Transaction?.Connection == null || this.Transaction?.Connection.State != ConnectionState.Open)
            throw new InvalidOperationException("Transaction is provided, but its connection is null.");
        return await this.Transaction.Connection.QueryFirstOrDefaultAsync<GetPostgresNotNullTypesRow?>(GetPostgresNotNullTypesSql, transaction: this.Transaction);
    }

    private const string TruncatePostgresNotNullTypesSql = "TRUNCATE TABLE postgres_not_null_types";
    public async Task TruncatePostgresNotNullTypesAsync()
    {
        if (this.Transaction == null)
        {
            using (var connection = await GetDataSource().OpenConnectionAsync())
            {
                await connection.ExecuteAsync(TruncatePostgresNotNullTypesSql);
                return;
            }
        }

        if (this.Transaction?.Connection == null || this.Transaction?.Connection.State != ConnectionState.Open)
            throw new InvalidOperationException("Transaction is provided, but its connection is null.");
        await this.Transaction.Connection.ExecuteAsync(TruncatePostgresNotNullTypesSql, transaction: this.Transaction);
    }

    private const string GetPostgresSpecialTypesSql = @"SELECT
                                                            c_json,
                                                            c_json_string_override,
                                                            c_jsonb,
                                                            c_jsonpath,
                                                            c_xml,
                                                            c_xml_string_override,
                                                            c_uuid,
                                                            c_enum,
                                                            c_qualified_enum
                                                        FROM postgres_special_types 
                                                        LIMIT 1";
    public class GetPostgresSpecialTypesRow
    {
        public JsonElement? CJson { get; init; }
        public string? CJsonStringOverride { get; init; }
        public JsonElement? CJsonb { get; init; }
        public string? CJsonpath { get; init; }
        public XmlDocument? CXml { get; init; }
        public string? CXmlStringOverride { get; init; }
        public Guid? CUuid { get; init; }
        public CEnum? CEnum { get; init; }
        public CEnum? CQualifiedEnum { get; init; }
    };
    public async Task<GetPostgresSpecialTypesRow?> GetPostgresSpecialTypesAsync()
    {
        if (this.Transaction == null)
        {
            using (var connection = await GetDataSource().OpenConnectionAsync())
            {
                var result = await connection.QueryFirstOrDefaultAsync<GetPostgresSpecialTypesRow?>(GetPostgresSpecialTypesSql);
                return result;
            }
        }

        if (this.Transaction?.Connection == null || this.Transaction?.Connection.State != ConnectionState.Open)
            throw new InvalidOperationException("Transaction is provided, but its connection is null.");
        return await this.Transaction.Connection.QueryFirstOrDefaultAsync<GetPostgresSpecialTypesRow?>(GetPostgresSpecialTypesSql, transaction: this.Transaction);
    }

    private const string TruncatePostgresSpecialTypesSql = "TRUNCATE TABLE postgres_special_types";
    public async Task TruncatePostgresSpecialTypesAsync()
    {
        if (this.Transaction == null)
        {
            using (var connection = await GetDataSource().OpenConnectionAsync())
            {
                await connection.ExecuteAsync(TruncatePostgresSpecialTypesSql);
                return;
            }
        }

        if (this.Transaction?.Connection == null || this.Transaction?.Connection.State != ConnectionState.Open)
            throw new InvalidOperationException("Transaction is provided, but its connection is null.");
        await this.Transaction.Connection.ExecuteAsync(TruncatePostgresSpecialTypesSql, transaction: this.Transaction);
    }

    private const string InsertPostgresSpecialTypesBatchSql = "COPY postgres_special_types (c_uuid, c_json, c_jsonb) FROM STDIN (FORMAT BINARY)";
    public class InsertPostgresSpecialTypesBatchArgs
    {
        public Guid? CUuid { get; init; }
        public JsonElement? CJson { get; init; }
        public JsonElement? CJsonb { get; init; }
    };
    public async Task InsertPostgresSpecialTypesBatchAsync(List<InsertPostgresSpecialTypesBatchArgs> args)
    {
        using (var connection = await GetDataSource().OpenConnectionAsync())
        {
            using (var writer = await connection.BeginBinaryImportAsync(InsertPostgresSpecialTypesBatchSql))
            {
                foreach (var row in args)
                {
                    await writer.StartRowAsync();
                    await writer.WriteAsync(row.CUuid);
                    await writer.WriteAsync(row.CJson.HasValue ? (object)row.CJson.Value : (object)DBNull.Value, NpgsqlDbType.Json);
                    await writer.WriteAsync(row.CJsonb.HasValue ? (object)row.CJsonb.Value : (object)DBNull.Value, NpgsqlDbType.Jsonb);
                }

                await writer.CompleteAsync();
            }
        }
    }

    private const string GetPostgresSpecialTypesCntSql = @"WITH grouped_json_types AS (
                                                               SELECT
                                                                   c_uuid,
                                                                   c_json::text AS c_json,
                                                                   c_jsonb::text AS c_jsonb,
                                                                   COUNT(*) AS cnt
                                                               FROM postgres_special_types
                                                               GROUP BY
                                                                   c_uuid,
                                                                   c_json::text,
                                                                   c_jsonb::text
                                                           )
                                                           
                                                           SELECT 
                                                               c_uuid, 
                                                               c_json::json AS c_json, 
                                                               c_jsonb::jsonb AS c_jsonb, 
                                                               cnt
                                                           FROM grouped_json_types
                                                           LIMIT 1";
    public class GetPostgresSpecialTypesCntRow
    {
        public Guid? CUuid { get; init; }
        public JsonElement? CJson { get; init; }
        public JsonElement? CJsonb { get; init; }
        public required long Cnt { get; init; }
    };
    public async Task<GetPostgresSpecialTypesCntRow?> GetPostgresSpecialTypesCntAsync()
    {
        if (this.Transaction == null)
        {
            using (var connection = await GetDataSource().OpenConnectionAsync())
            {
                var result = await connection.QueryFirstOrDefaultAsync<GetPostgresSpecialTypesCntRow?>(GetPostgresSpecialTypesCntSql);
                return result;
            }
        }

        if (this.Transaction?.Connection == null || this.Transaction?.Connection.State != ConnectionState.Open)
            throw new InvalidOperationException("Transaction is provided, but its connection is null.");
        return await this.Transaction.Connection.QueryFirstOrDefaultAsync<GetPostgresSpecialTypesCntRow?>(GetPostgresSpecialTypesCntSql, transaction: this.Transaction);
    }

    private const string InsertPostgresArrayTypesSql = @"
                                                         INSERT INTO postgres_array_types
                                                         (
                                                             c_bytea,
                                                             c_boolean_array,
                                                             c_text_array,
                                                             c_integer_array,
                                                             c_decimal_array,
                                                             c_date_array,
                                                             c_timestamp_array
                                                         )
                                                         VALUES (@c_bytea, @c_boolean_array, @c_text_array, @c_integer_array, @c_decimal_array, @c_date_array, @c_timestamp_array)";
    public class InsertPostgresArrayTypesArgs
    {
        public byte[]? CBytea { get; init; }
        public bool[]? CBooleanArray { get; init; }
        public string[]? CTextArray { get; init; }
        public int[]? CIntegerArray { get; init; }
        public decimal[]? CDecimalArray { get; init; }
        public DateTime[]? CDateArray { get; init; }
        public DateTime[]? CTimestampArray { get; init; }
    };
    public async Task InsertPostgresArrayTypesAsync(InsertPostgresArrayTypesArgs args)
    {
        var queryParams = new Dictionary<string, object?>();
        queryParams.Add("c_bytea", args.CBytea);
        queryParams.Add("c_boolean_array", args.CBooleanArray);
        queryParams.Add("c_text_array", args.CTextArray);
        queryParams.Add("c_integer_array", args.CIntegerArray);
        queryParams.Add("c_decimal_array", args.CDecimalArray);
        queryParams.Add("c_date_array", args.CDateArray);
        queryParams.Add("c_timestamp_array", args.CTimestampArray);
        if (this.Transaction == null)
        {
            using (var connection = await GetDataSource().OpenConnectionAsync())
            {
                await connection.ExecuteAsync(InsertPostgresArrayTypesSql, queryParams);
                return;
            }
        }

        if (this.Transaction?.Connection == null || this.Transaction?.Connection.State != ConnectionState.Open)
            throw new InvalidOperationException("Transaction is provided, but its connection is null.");
        await this.Transaction.Connection.ExecuteAsync(InsertPostgresArrayTypesSql, queryParams, transaction: this.Transaction);
    }

    private const string GetPostgresArrayTypesSql = "SELECT c_bytea, c_boolean_array, c_text_array, c_integer_array, c_decimal_array, c_date_array, c_timestamp_array FROM postgres_array_types LIMIT 1";
    public class GetPostgresArrayTypesRow
    {
        public byte[]? CBytea { get; init; }
        public bool[]? CBooleanArray { get; init; }
        public string[]? CTextArray { get; init; }
        public int[]? CIntegerArray { get; init; }
        public decimal[]? CDecimalArray { get; init; }
        public DateTime[]? CDateArray { get; init; }
        public DateTime[]? CTimestampArray { get; init; }
    };
    public async Task<GetPostgresArrayTypesRow?> GetPostgresArrayTypesAsync()
    {
        if (this.Transaction == null)
        {
            using (var connection = await GetDataSource().OpenConnectionAsync())
            {
                var result = await connection.QueryFirstOrDefaultAsync<GetPostgresArrayTypesRow?>(GetPostgresArrayTypesSql);
                return result;
            }
        }

        if (this.Transaction?.Connection == null || this.Transaction?.Connection.State != ConnectionState.Open)
            throw new InvalidOperationException("Transaction is provided, but its connection is null.");
        return await this.Transaction.Connection.QueryFirstOrDefaultAsync<GetPostgresArrayTypesRow?>(GetPostgresArrayTypesSql, transaction: this.Transaction);
    }

    private const string InsertPostgresArrayTypesBatchSql = "COPY postgres_array_types (c_bytea, c_boolean_array, c_text_array, c_integer_array, c_decimal_array, c_timestamp_array) FROM STDIN (FORMAT BINARY)";
    public class InsertPostgresArrayTypesBatchArgs
    {
        public byte[]? CBytea { get; init; }
        public bool[]? CBooleanArray { get; init; }
        public string[]? CTextArray { get; init; }
        public int[]? CIntegerArray { get; init; }
        public decimal[]? CDecimalArray { get; init; }
        public DateTime[]? CTimestampArray { get; init; }
    };
    public async Task InsertPostgresArrayTypesBatchAsync(List<InsertPostgresArrayTypesBatchArgs> args)
    {
        using (var connection = await GetDataSource().OpenConnectionAsync())
        {
            using (var writer = await connection.BeginBinaryImportAsync(InsertPostgresArrayTypesBatchSql))
            {
                foreach (var row in args)
                {
                    await writer.StartRowAsync();
                    await writer.WriteAsync(row.CBytea);
                    await writer.WriteAsync(row.CBooleanArray);
                    await writer.WriteAsync(row.CTextArray);
                    await writer.WriteAsync(row.CIntegerArray);
                    await writer.WriteAsync(row.CDecimalArray);
                    await writer.WriteAsync(row.CTimestampArray);
                }

                await writer.CompleteAsync();
            }
        }
    }

    private const string GetPostgresArrayTypesCntSql = @"SELECT
                                                             c_bytea,
                                                             c_boolean_array,
                                                             c_text_array,
                                                             c_integer_array,
                                                             c_decimal_array,
                                                             c_timestamp_array,
                                                             COUNT(*) AS cnt
                                                         FROM postgres_array_types
                                                         GROUP BY
                                                             c_bytea,
                                                             c_boolean_array,
                                                             c_text_array,
                                                             c_integer_array,
                                                             c_decimal_array,
                                                             c_timestamp_array
                                                         LIMIT 1";
    public class GetPostgresArrayTypesCntRow
    {
        public byte[]? CBytea { get; init; }
        public bool[]? CBooleanArray { get; init; }
        public string[]? CTextArray { get; init; }
        public int[]? CIntegerArray { get; init; }
        public decimal[]? CDecimalArray { get; init; }
        public DateTime[]? CTimestampArray { get; init; }
        public required long Cnt { get; init; }
    };
    public async Task<GetPostgresArrayTypesCntRow?> GetPostgresArrayTypesCntAsync()
    {
        if (this.Transaction == null)
        {
            using (var connection = await GetDataSource().OpenConnectionAsync())
            {
                var result = await connection.QueryFirstOrDefaultAsync<GetPostgresArrayTypesCntRow?>(GetPostgresArrayTypesCntSql);
                return result;
            }
        }

        if (this.Transaction?.Connection == null || this.Transaction?.Connection.State != ConnectionState.Open)
            throw new InvalidOperationException("Transaction is provided, but its connection is null.");
        return await this.Transaction.Connection.QueryFirstOrDefaultAsync<GetPostgresArrayTypesCntRow?>(GetPostgresArrayTypesCntSql, transaction: this.Transaction);
    }

    private const string TruncatePostgresArrayTypesSql = "TRUNCATE TABLE postgres_array_types";
    public async Task TruncatePostgresArrayTypesAsync()
    {
        if (this.Transaction == null)
        {
            using (var connection = await GetDataSource().OpenConnectionAsync())
            {
                await connection.ExecuteAsync(TruncatePostgresArrayTypesSql);
                return;
            }
        }

        if (this.Transaction?.Connection == null || this.Transaction?.Connection.State != ConnectionState.Open)
            throw new InvalidOperationException("Transaction is provided, but its connection is null.");
        await this.Transaction.Connection.ExecuteAsync(TruncatePostgresArrayTypesSql, transaction: this.Transaction);
    }

    private const string InsertPostgresGeoTypesSql = @"
                                                       INSERT INTO postgres_geometric_types (
                                                           c_point, 
                                                           c_line, 
                                                           c_lseg, 
                                                           c_box, 
                                                           c_path, 
                                                           c_polygon, 
                                                           c_circle
                                                       )
                                                       VALUES (@c_point, @c_line, @c_lseg, @c_box, @c_path, @c_polygon, @c_circle)";
    public class InsertPostgresGeoTypesArgs
    {
        public NpgsqlPoint? CPoint { get; init; }
        public NpgsqlLine? CLine { get; init; }
        public NpgsqlLSeg? CLseg { get; init; }
        public NpgsqlBox? CBox { get; init; }
        public NpgsqlPath? CPath { get; init; }
        public NpgsqlPolygon? CPolygon { get; init; }
        public NpgsqlCircle? CCircle { get; init; }
    };
    public async Task InsertPostgresGeoTypesAsync(InsertPostgresGeoTypesArgs args)
    {
        var queryParams = new Dictionary<string, object?>();
        queryParams.Add("c_point", args.CPoint);
        queryParams.Add("c_line", args.CLine);
        queryParams.Add("c_lseg", args.CLseg);
        queryParams.Add("c_box", args.CBox);
        queryParams.Add("c_path", args.CPath);
        queryParams.Add("c_polygon", args.CPolygon);
        queryParams.Add("c_circle", args.CCircle);
        if (this.Transaction == null)
        {
            using (var connection = await GetDataSource().OpenConnectionAsync())
            {
                await connection.ExecuteAsync(InsertPostgresGeoTypesSql, queryParams);
                return;
            }
        }

        if (this.Transaction?.Connection == null || this.Transaction?.Connection.State != ConnectionState.Open)
            throw new InvalidOperationException("Transaction is provided, but its connection is null.");
        await this.Transaction.Connection.ExecuteAsync(InsertPostgresGeoTypesSql, queryParams, transaction: this.Transaction);
    }

    private const string InsertPostgresGeoTypesBatchSql = "COPY postgres_geometric_types (c_point, c_line, c_lseg, c_box, c_path, c_polygon, c_circle) FROM STDIN (FORMAT BINARY)";
    public class InsertPostgresGeoTypesBatchArgs
    {
        public NpgsqlPoint? CPoint { get; init; }
        public NpgsqlLine? CLine { get; init; }
        public NpgsqlLSeg? CLseg { get; init; }
        public NpgsqlBox? CBox { get; init; }
        public NpgsqlPath? CPath { get; init; }
        public NpgsqlPolygon? CPolygon { get; init; }
        public NpgsqlCircle? CCircle { get; init; }
    };
    public async Task InsertPostgresGeoTypesBatchAsync(List<InsertPostgresGeoTypesBatchArgs> args)
    {
        using (var connection = await GetDataSource().OpenConnectionAsync())
        {
            using (var writer = await connection.BeginBinaryImportAsync(InsertPostgresGeoTypesBatchSql))
            {
                foreach (var row in args)
                {
                    await writer.StartRowAsync();
                    await writer.WriteAsync(row.CPoint);
                    await writer.WriteAsync(row.CLine);
                    await writer.WriteAsync(row.CLseg);
                    await writer.WriteAsync(row.CBox);
                    await writer.WriteAsync(row.CPath);
                    await writer.WriteAsync(row.CPolygon);
                    await writer.WriteAsync(row.CCircle);
                }

                await writer.CompleteAsync();
            }
        }
    }

    private const string GetPostgresGeoTypesSql = "SELECT c_point, c_line, c_lseg, c_box, c_path, c_polygon, c_circle FROM postgres_geometric_types LIMIT 1";
    public class GetPostgresGeoTypesRow
    {
        public NpgsqlPoint? CPoint { get; init; }
        public NpgsqlLine? CLine { get; init; }
        public NpgsqlLSeg? CLseg { get; init; }
        public NpgsqlBox? CBox { get; init; }
        public NpgsqlPath? CPath { get; init; }
        public NpgsqlPolygon? CPolygon { get; init; }
        public NpgsqlCircle? CCircle { get; init; }
    };
    public async Task<GetPostgresGeoTypesRow?> GetPostgresGeoTypesAsync()
    {
        if (this.Transaction == null)
        {
            using (var connection = await GetDataSource().OpenConnectionAsync())
            {
                var result = await connection.QueryFirstOrDefaultAsync<GetPostgresGeoTypesRow?>(GetPostgresGeoTypesSql);
                return result;
            }
        }

        if (this.Transaction?.Connection == null || this.Transaction?.Connection.State != ConnectionState.Open)
            throw new InvalidOperationException("Transaction is provided, but its connection is null.");
        return await this.Transaction.Connection.QueryFirstOrDefaultAsync<GetPostgresGeoTypesRow?>(GetPostgresGeoTypesSql, transaction: this.Transaction);
    }

    private const string TruncatePostgresGeoTypesSql = "TRUNCATE TABLE postgres_geometric_types";
    public async Task TruncatePostgresGeoTypesAsync()
    {
        if (this.Transaction == null)
        {
            using (var connection = await GetDataSource().OpenConnectionAsync())
            {
                await connection.ExecuteAsync(TruncatePostgresGeoTypesSql);
                return;
            }
        }

        if (this.Transaction?.Connection == null || this.Transaction?.Connection.State != ConnectionState.Open)
            throw new InvalidOperationException("Transaction is provided, but its connection is null.");
        await this.Transaction.Connection.ExecuteAsync(TruncatePostgresGeoTypesSql, transaction: this.Transaction);
    }
}