postgresql 函数 参数为复合类型

postgresql没有存储过程，但是函数功能很强大、

在近期开发的电商管理平台中，对于产品的类目管理，设计时有个属性字段，设为字符数组，但是EF不支持数组的操作，所以在添加和修改类目时，需要对属性的存储和维护进行特殊处理，导致代码量增大。

public static async Task AddCategory(MerchandiseCategory category)
        {
            using (MasonDB db = new MasonDB())
            {
                var p_attributes = new NpgsqlParameter("attributes", NpgsqlDbType.Array | NpgsqlDbType.Text);
                p_attributes.Value = category.Attributes;
                var p_parentid = new NpgsqlParameter { ParameterName = "parentid", Value = category.ParentID };
                var p_categoryname = new NpgsqlParameter { ParameterName = "categoryname", Value = category.CategoryName };
                var p_isvalid = new NpgsqlParameter { ParameterName = "isvalid", Value = category.IsValid };
                var p_operatorid = new NpgsqlParameter { ParameterName = "operatorid", Value = category.OperatorID };
                var p_operatortime = new NpgsqlParameter { ParameterName = "operatortime", Value = category.OperateTime };
                var p_sortweight = new NpgsqlParameter { ParameterName = "sortweight", Value = DateTime.Now.Subtract(DateTime.Parse("1970-1-1")).TotalMilliseconds };
                await db.Database.ExecuteSqlCommandAsync("INSERT INTO \"MerchandiseCategories\" (\"ParentID\", \"CategoryName\", \"IsValid\", \"OperatorID\", \"OperateTime\", \"SortWeight\",  \"Attributes\") VALUES(@parentid, @categoryname, @isvalid, @operatorid, @operatortime,@sortweight,@attributes); ", p_parentid, p_categoryname, p_isvalid, p_operatorid, p_operatortime, p_sortweight, p_attributes);
    ｝
｝

后期如果需要对数据库表加入新的字段或者是删除不需要的字段，SQL作为弱类型语言就会暴露出很多问题、编译无法通过，我们修改代码后重启服务器，这带来的后果有时是不可估量的。所以在开发中采用EF和LINQ。

为了减少代码量，以及便于维护，因此把插入和修改都用数据库函数实现、

postgresql函数的参数可以是表类型、即复合类型、要注意的是表类型要加“”，这个和oracle是一样的、

插入时我想得到插入的一行的数据的ID,代码实现如下：

CREATE OR REPLACE FUNCTION func_add_merchandisecategory(category "MerchandiseCategories")
  RETURNS integer AS
$BODY$ 
    INSERT INTO "MerchandiseCategories"(
            "ParentID", "CategoryName", "IsValid", "SortWeight", "OperatorID", 
            "OperateTime", "Attributes")
    VALUES (category."ParentID", category."CategoryName",category."IsValid", category."SortWeight", category."OperatorID", category."OperateTime", 
            category."Attributes")
        --select currval('MerchandiseCategories_ID_seq');
        returning "ID";

$BODY$
  LANGUAGE sql VOLATILE
  COST 100;
ALTER FUNCTION func_add_merchandisecategory("MerchandiseCategories")
  OWNER TO LujieZheng;

对于返回ID网上还有一种做法是

select currval('MerchandiseCategories_ID_seq');
但是一直提示找不到该序列，我在数据库中以创建该序列，这个问题暂时还没有解决

在调用该函数的时候也出现了很多问题，因为是复合类型，所以格式为：select * from func_add_merchandisecategory(( ))，需要加两个括号，括号内的复合类型的各个字段的在赋值时也有区别、对于boolean、character varying（）、timestamp without time zone，text类型都要加上单引号

category.ID = await db.Database.SqlQuery<int>($"select * from func_add_merchandisecategory((0,{category.ParentID},'{category.CategoryName}','{category.IsValid}',{category.SortWeight},{category.OperatorID},'{category.OperateTime}','{{{attrs}}}'))").FirstAsync();

注：{{{}}}可以输出｛｝

　　

转载于:https://www.cnblogs.com/sMKing/p/5443007.html