Call a postgres SP using ADO.NET with parameter as Custom Type

19 views Asked by At

I have an SP that accepts a Type as IN parameter and does insertion to a table. However, when I am trying to pass the parameter value from C#, I get below error.

{"22P02: malformed record literal: "ValueTableRow { //some data }}

Here is the struct that I am passing the values to

private readonly record struct ValueTableRow(
        DateTimeOffset Time,
        object Value,
        int vid,
        uint Qual,
        NpgsqlDbType DbType,
        string DbColumn)
    {
    }
private async Task WriteToDb(NpgsqlConnection connectionManager, IGrouping<string, ValueTableRow> dbColumnGroup)
    {       
        var parameters = new List<(string, NpgsqlDbType, object)>();
        try
        {
            parameters.Add(new("columnname", NpgsqlDbType.Text, dbColumnGroup.Key));
            parameters.Add(new("dbcolumngroup", NpgsqlDbType.Unknown,dbColumnGroup.FirstOrDefault()));
            await ExecuteProcedure(connectionManager, "call sp_handle_messages(@dbcolumngroup, @columnname)", parameters);
        }
        catch (Exception ex)
        {
            
        }        
    }
using (var command = new NpgsqlCommand(procedureName, connection))
        {
            //add all parameters
            foreach (var (name, dataType, value) in parameters)
            {
                command.Parameters.AddWithValue(name, dataType, value);
            }

            //execute
            await command.ExecuteNonQueryAsync();
        }

The Custom type is as below:

DO $$
BEGIN
    IF NOT EXISTS (SELECT 1 FROM pg_type WHERE typname = 'valuetablerow') THEN
       CREATE TYPE valuetablerow AS (
        Time timestamp with time zone,
        Value Text,
        vid integer,
        Qual integer,
        DbType Text,
        DbColumn Text
      ); 

    END IF;   
END$$;
CREATE OR REPLACE PROCEDURE sp_handle_messages(
  IN dbcolumngroup valuetablerow,
  IN columnname Text  
)
LANGUAGE plpgsql
AS $BODY$
DECLARE
            _query text;
            BEGIN
                -- Insert logic using the IN parameter
END;
$BODY$;
0

There are 0 answers