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