时间戳

首页 > 解决方案 > 有人可以帮助我理解 Postgres 中的歧义吗？

sql - 有人可以帮助我理解 Postgres 中的歧义吗？

问题描述

所以我几天来一直在尝试 PG，特别是通过 dotnet 核心中的 NpgSQL，但我认为这与我的问题无关。我一直在写几个更新函数。第一个很简单：

    CREATE OR REPLACE FUNCTION "Api"."UpdateExpenseReceipt" ( "vReceiptID" UUID  , "vTotal" DOUBLE PRECISION , "vTaxPercent" DOUBLE PRECISION , "vShippingCost" DOUBLE PRECISION , "vReceiptDate" TIMESTAMP , "vReference" VARCHAR , "vCurrentToken" UUID )
        RETURNS TABLE ( ReceiptID UUID , Total DOUBLE PRECISION , TaxPercent DOUBLE PRECISION , ShippingCost DOUBLE PRECISION , Reference VARCHAR(96) , ReceiptDate TIMESTAMP )
        LANGUAGE PLPGSQL
        AS $$
        DECLARE "iValidReceipt" INTEGER;
        DECLARE "iValidUser" INTEGER;
        BEGIN
        
            "iValidReceipt" := ( SELECT COUNT("ReceiptID") FROM "Users"."ExpenseReceipt" WHERE "ReceiptID" = "vReceiptID" );
            "iValidUser"  := ( SELECT COUNT("AccountID") FROM "Users"."Account" WHERE "CurrentToken" = "vCurrentToken" LIMIT 1 );
        
            IF "iValidUser" = 0 THEN
                RAISE 'Error' USING ERRCODE = '10001';
            END IF;
        
            IF "iValidReceipt" > 0 THEN
                UPDATE "Users"."ExpenseReceipt" SET
                    "Total" = COALESCE( "vTotal" , "Total" )
                    , "TaxPercent" = COALESCE( "vTaxPercent" , "TaxPercent" )
                    , "ShippingCost" = COALESCE( "vShippingCost" , "ShippingCost" )
                    , "Reference" = COALESCE( CAST( "vReference" AS VARCHAR ) , "Reference" )
                    , "ReceiptDate" = COALESCE( "vReceiptDate" , "ReceiptDate" )
                    , "EditDate" = current_timestamp at time zone 'utc'
                WHERE "ReceiptID" = "vReceiptID";
                
                RETURN QUERY
                SELECT
                    "ReceiptID"
                    , "Total"
                    , "TaxPercent"
                    , "ShippingCost"
                    , "Reference"
                    , "ReceiptDate"
                FROM "Users"."ExpenseReceipt"
                WHERE "ReceiptID" = "vReceiptID";
            ELSE
                RAISE 'Error' USING ERRCODE = '10101';
            END IF;
        
        
        END; $$

--I'll include the table itself in case its relevant
CREATE TABLE "Users"."ExpenseReceipt"
(
    "ReceiptID" UUID NOT NULL DEFAULT uuid_generate_v4(),
    "AccountID" UUID NOT NULL ,
    "Total" DOUBLE PRECISION NOT NULL ,
    "TaxPercent" DOUBLE PRECISION DEFAULT 0.0 ,
    "ShippingCost" DOUBLE PRECISION DEFAULT 0.0 ,
    "Reference" VARCHAR(96) ,
    "ReceiptDate" TIMESTAMP ,
    "EditDate" TIMESTAMP DEFAULT ( current_timestamp at time zone 'utc' )
);

简单的更新功能，如果 API 调用没有设置它们，则使用 coalesce 不更新值。一切正常（在处理了我在 postgres 中遇到的众多命名问题之后，我认为 NpgSQL 是相关的）。我知道如何正确处理。我做了第二个，基本一模一样：

CREATE OR REPLACE FUNCTION "Api"."UpdateSupplyItem" ( "vItemID" UUID , "vDescription" VARCHAR , "vSize" VARCHAR , "vNetCost" DOUBLE PRECISION , "vPackageQuantity" DOUBLE PRECISION , "vNetWeight" DOUBLE PRECISION , "vCurrentToken" UUID )
RETURNS TABLE ( "ItemID" UUID , "Description" VARCHAR , "Size" VARCHAR , "NetCost" DOUBLE PRECISION , "PackageQuantity" DOUBLE PRECISION , "NetWeight" DOUBLE PRECISION )
LANGUAGE PLPGSQL
AS $$
DECLARE "iValidItem" INTEGER;
DECLARE "iValidUser" INTEGER;
BEGIN

    "iValidItem" := ( SELECT COUNT(usi."ItemID") FROM "Users"."SupplyItem" usi WHERE usi."ItemID" = "vItemID" );
    "iValidUser" := ( SELECT COUNT("AccountID") FROM "Users"."Account" WHERE "CurrentToken" = "vCurrentToken" LIMIT 1 );

    IF "iValidUser" = 0 THEN
        RAISE 'Error' USING ERRCODE = '10001';
    END IF;

    IF "iValidItem" > 0 THEN

        UPDATE "Users"."SupplyItem"
        SET 
             "Description" = COALESCE( "vDescription" , "Users"."SupplyItem"."Description" )
            , "Size" = COALESCE( "vSize" , "Users"."SupplyItem"."Size" ) 
            , "NetCost" = COALESCE( "vNetCost" ,  "Users"."SupplyItem"."NetCost" ) 
            , "PackageQuantity" = COALESCE( "vPackageQuantity" ,  "Users"."SupplyItem"."PackageQuantity") 
            , "NetWeight" = COALESCE( "vNetWeight" ,  "Users"."SupplyItem"."NetWeight" )
        WHERE  "Users"."SupplyItem"."ItemID" = "vItemID";

        RETURN QUERY SELECT usi."ItemID" , usi."Description" , usi."Size" , usi."NetCost" , usi."PackageQuantity" , usi."NetWeight" FROM "Users"."SupplyItem" usi WHERE usi."ItemID" = "vItemID" LIMIT 1;

    ELSE
        RAISE 'Error' USING ERRCODE = '10401';
    END IF;

END; $$


--Again, the table in case it helps

CREATE TABLE "Users"."SupplyItem"
(
    "ItemID" UUID NOT NULL DEFAULT uuid_generate_v4() ,
    "AccountID" UUID NOT NULL ,
    "Description" VARCHAR , 
    "Size" VARCHAR ,
    "NetCost" DOUBLE PRECISION , 
    "PackageQuantity" DOUBLE PRECISION ,
    "NetWeight" DOUBLE PRECISION
);

但它完全不同。您可以清楚地看到，我必须完全限定每个 equals 的右侧（之前的函数不需要）。我在更新声明中一直模棱两可。它从 ItemID 开始，然后是 Description，然后是 Size……我认为的每个属性。我做的第一件事是给桌子起别名

UPDATE usi [...] FROM "Users"."SupplyItem" usi

但这失败了，因为您不能在 PG 中的 UPDATE 中进行短别名（关系 usi。“[...]”不存在“），这实际上有点糟糕。我只发现它需要完全限定有人问了类似的问题，答案是“这一定是 RETURNS TABLE 的怪癖”。

那么为什么我的第二次更新是“怪癖”，但我的第一次更新却完美无缺？我在 PG 上度过了一段艰难的时光（而且我不是一个懒散的人），但是拥有两个看起来相同但结果完全不同的函数（在运行时也不少）让我感到不舒服。我在这里发帖是因为我知道这两个功能必须明显不同；99.9% 的情况下，不存在“怪癖”。我需要了解一些事情以解决将来避免的问题。我在第二个 UPDATE 函数中错过的“陷阱”是什么？

标签： sqlpostgresqlstored-proceduressql-updatenpgsql

解决方案

问题是您有一个函数变量"Size"（在子句中OUT定义的参数）和. 因此，您必须限定参考以表明您的意思。RETURNS TABLE"Size""Users"."SupplyItem"

为简单起见，我建议使用别名：

UPDATE "Users"."SupplyItem" AS si
   SET "Size" = COALESCE("vSize" , si."Size")
...

在您的第一个示例中没有这种歧义，因为您没有双引号 parameter TaxPercent，所以它被折叠到taxpercent并且与 column 不同"TaxPercent"。

推荐阅读