malformed array literal exception
Posted: Tue 19 Mar 2013 11:42
Hi.
I want to run function which parameter is composite type array.
I have a table like this :
This is my composite type :
And this is function uses composite type's array as parameter :
on .net side I have a Poco object like this :
And this my method :
and i run like this :
and its give this error :
But i have no idea how to fix it.
Can you help me?
I want to run function which parameter is composite type array.
I have a table like this :
Code: Select all
CREATE TABLE super_tournament
(
s_tournament_id integer NOT NULL,
name character varying(100) NOT NULL,
active smallint NOT NULL);
Code: Select all
CREATE TYPE super_tournament_type AS
(_s_tournament_id integer,
_name character varying(100),
_active smallint);
And this is function uses composite type's array as parameter :
Code: Select all
CREATE OR REPLACE FUNCTION betradar_meta_integration.super_tournament_func(p_super_tournament super_tournament_type[])
RETURNS void AS
$BODY$
BEGIN
WITH upsert AS (
UPDATE super_tournament
SET
"name" = amc._name,
active = amc._active
FROM UNNEST (p_super_tournament) AS amc
WHERE s_tournament_id = amc._s_tournament_id
RETURNING amc.*
) INSERT INTO super_tournament
(
s_tournament_id,
"name",
active
)
SELECT * FROM UNNEST (p_super_tournament) AS amc WHERE NOT EXISTS (
SELECT
1
FROM
upsert
) ;
END
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
Code: Select all
public class super_tournament
{
public Int32 s_tournament_id { get; set; }
public string name { get; set; }
public Int32 active { get; set; }
}
Code: Select all
public static void Test<T>(List<T> data,string typeName,string parameterName,string functionName,PgSqlConnection connection) where T : new()
{
if (connection.State != ConnectionState.Open)
{
connection.Open();
}
var propertyInfos = typeof(T).GetProperties();
var array = new object[data.Count];
PgSqlRowType type = PgSqlRowType.GetRowType(typeName, connection);
for (var i = 0; i < data.Count; i++)
{
var row = new PgSqlRow(type);
int say = 0;
foreach (var propertyInfo in propertyInfos)
{
row[say] = data[i].GetType().GetProperty(propertyInfo.Name).GetValue(data[i], null);
say++;
}
array[i] = row;
}
var parray = new PgSqlArray(array, PgSqlType.Row, 1, array.Count());
using (var cmd = connection.CreateCommand())
{
cmd.CommandText = functionName;
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add( new PgSqlParameter { ParameterName = parameterName, PgSqlType = PgSqlType.Array, RowType = type, Value = parray, Direction = ParameterDirection.Input });
cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
cmd.Dispose();
}
}
Code: Select all
List<super_tournament> denemes = new List<super_tournament>();
denemes.Add(new super_tournament{active = 1,name = "Premiere League",s_tournament_id = 11});
denemes.Add(new super_tournament { active = 1, name = "Serie A", s_tournament_id = 12 });
denemes.Add(new super_tournament { active = 1, name = "Bundesliga", s_tournament_id = 13 });
Test(superTournamentListe, "super_tournament_type", "p_super_tournament", "super_tournament_func", connection);
i think string character must be in quote not double quote.{Error: 22P02: malformed array literal: "{"(11,"Premiere League",1)","(12,"Serie A",1)","(13,Bundesliga,1)"}"}
But i have no idea how to fix it.
Can you help me?