Dapper参数无法通过npgsql连接与PostgreSQL配合使用，是否支持postgres匿名函数参数化？

本文介绍了Dapper参数无法通过npgsql连接与PostgreSQL配合使用，是否支持postgres匿名函数参数化？的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我正在尝试使用dapper参数化我编写的用于执行upsert的postgres匿名函数。代码如下：

I'm trying to use dapper to parameterize a postgres anonymous function i've written to do an upsert. Here's the code:

private static int UpsertProductPrice( IDbConnection connection, Data.ProductPrice price, List<Data.ProductPriceTier> priceTiers) { string postgres = @"DO $$ BEGIN UPDATE product_price SET start_date = @StartDate, end_date = @EndDate, price_tier_type = @PriceTierType WHERE product_price_external_id = @Id; IF found THEN RETURN; END IF; BEGIN INSERT INTO product_price(product_price_external_id, start_date, end_date, price_tier_type) VALUES (@Id, @StartDate, @EndDate, @PriceTierType); RETURN; EXCEPTION WHEN unique_violation THEN END; END$$;"; int productPriceId = connection.Execute( postgres, new { Id = price.product_price_external_id, StartDate = price.start_date, EndDate = price.end_date, PriceTierType = price.price_tier_type });

Postgres将查询记录为未参数化：仍然有@符号，未插入任何值。这是日志。

Postgres logs the query as unparameterized: there are still @ signs and no values were inserted. Here's the log.

2015-07-15 14:57:58.570 EDT,"user","TestDb",8496,"::1:53953",55a6ad36.2130,1,"DO",2015-07-15 14:57:58 EDT,9/42,0,ERROR,42703,"column ""startdate"" does not exist",,,"UPDATE product_price SET start_date = @StartDate, end_date = @EndDate, price_tier_type = @PriceTierType WHERE product_price_external_id = @Id",52,"PL/pgSQL function inline_code_block line 3 at SQL statement","DO $$ BEGIN UPDATE product_price SET start_date = @StartDate, end_date = @EndDate, price_tier_type = @PriceTierType WHERE product_price_external_id = @Id; IF found THEN RETURN; END IF; BEGIN INSERT INTO product_price(product_price_external_id, start_date, end_date, price_tier_type) VALUES (@Id, @StartDate, @EndDate, @PriceTierType); RETURN; EXCEPTION WHEN unique_violation THEN END; END$$;",,,""

我做错了，还是这不支持？找不到任何人尝试在线进行此操作。

I'm I doing something wrong, or is this not supported? I couldn't find anyone trying to do this online.

推荐答案

不支持匿名代码块中的参数，这将是PostgreSQL功能而不是Npgsql功能。请参见此处的讨论： github / npgsql / npgsql / issues / 629

Parameters in anonymous code blocks aren't supported, this would be a PostgreSQL feature rather than an Npgsql feature. See discussion here: github/npgsql/npgsql/issues/629