PostgreSQL存储函数中如何区分FOUND为假：无匹配ID与版本过期

针对你遇到的问题，这里提供几种实用的解决方案，适配Npgsql与.NET场景：

方案一：返回多状态标识（推荐，原子性强） #

将函数返回值从布尔改为整数，用不同数值区分三种结果：

• 0：指定ID的记录不存在
• 1：成功删除
• 2：记录存在但版本不匹配

优化后的存储函数（原子性实现） #

CREATE OR REPLACE FUNCTION institution_delete_by_id(IN in_id UUID, IN in_version BIGINT) RETURNS INT
    LANGUAGE plpgsql
AS
$$
DECLARE
    delete_count INT;
    exists_count INT;
BEGIN
    -- 用CTE原子性完成「检查存在」和「执行删除」，避免并发中间态
    WITH check_exists AS (
        SELECT COUNT(1) FROM institution WHERE id = in_id
    ), delete_op AS (
        DELETE FROM institution 
        WHERE id = in_id AND version = in_version
        RETURNING 1
    )
    SELECT (SELECT COUNT(1) FROM check_exists), (SELECT COUNT(1) FROM delete_op) INTO exists_count, delete_count;
    
    IF exists_count = 0 THEN
        RETURN 0;
    ELSIF delete_count > 0 THEN
        RETURN 1;
    ELSE
        RETURN 2;
    END IF;
END
$$;

.NET端处理逻辑 #

using var conn = new NpgsqlConnection("你的连接字符串");
await conn.OpenAsync();

using var cmd = new NpgsqlCommand("SELECT institution_delete_by_id(@id, @version)", conn);
cmd.Parameters.AddWithValue("id", targetId);
cmd.Parameters.AddWithValue("version", targetVersion);

var result = (int)await cmd.ExecuteScalarAsync();

switch(result)
{
    case 0:
        return NotFound();
    case 1:
        return NoContent();
    case 2:
        return Conflict("版本不匹配，记录已被其他操作修改");
    default:
        return BadRequest();
}

方案二：抛出自定义异常 #

利用PostgreSQL自定义异常功能，给不同错误分配专属SQL状态码，.NET端捕获后区分处理。

存储函数实现 #

CREATE OR REPLACE FUNCTION institution_delete_by_id(IN in_id UUID, IN in_version BIGINT) RETURNS BOOLEAN
    LANGUAGE plpgsql
AS
$$
DECLARE
    record_exists BOOLEAN;
BEGIN
    SELECT EXISTS(SELECT 1 FROM institution WHERE id = in_id) INTO record_exists;
    
    IF NOT record_exists THEN
        -- 自定义「记录不存在」异常，使用PostgreSQL用户预留状态码P0001
        RAISE EXCEPTION '记录不存在' USING ERRCODE = 'P0001';
    END IF;
    
    DELETE FROM institution WHERE id = in_id AND version = in_version;
    
    IF NOT FOUND THEN
        -- 自定义「版本冲突」异常，状态码P0002
        RAISE EXCEPTION '版本不匹配' USING ERRCODE = 'P0002';
    END IF;
    
    RETURN TRUE;
END
$$;

.NET端异常捕获 #

try
{
    using var conn = new NpgsqlConnection("你的连接字符串");
    await conn.OpenAsync();

    using var cmd = new NpgsqlCommand("SELECT institution_delete_by_id(@id, @version)", conn);
    cmd.Parameters.AddWithValue("id", targetId);
    cmd.Parameters.AddWithValue("version", targetVersion);

    await cmd.ExecuteScalarAsync();
    return NoContent();
}
catch(NpgsqlException ex)
{
    if(ex.SqlState == "P0001")
    {
        return NotFound();
    }
    else if(ex.SqlState == "P0002")
    {
        return Conflict("版本不匹配，记录已被修改");
    }
    else
    {
        return StatusCode(500, "数据库操作失败");
    }
}

方案三：返回复合类型（适合需多返回字段场景） #

定义PostgreSQL复合类型，同时返回操作状态与描述信息，适合需要额外返回数据的场景。

步骤1：定义复合类型 #

CREATE TYPE delete_result AS (
    success BOOLEAN,
    status_code INT, -- 0=不存在,1=成功,2=版本冲突
    message TEXT
);

步骤2：修改存储函数 #

CREATE OR REPLACE FUNCTION institution_delete_by_id(IN in_id UUID, IN in_version BIGINT) RETURNS delete_result
    LANGUAGE plpgsql
AS
$$
DECLARE
    result delete_result;
    exists_count INT;
    delete_count INT;
BEGIN
    WITH check_exists AS (
        SELECT COUNT(1) FROM institution WHERE id = in_id
    ), delete_op AS (
        DELETE FROM institution 
        WHERE id = in_id AND version = in_version
        RETURNING 1
    )
    SELECT (SELECT COUNT(1) FROM check_exists), (SELECT COUNT(1) FROM delete_op) INTO exists_count, delete_count;
    
    IF exists_count = 0 THEN
        result.success := FALSE;
        result.status_code := 0;
        result.message := "记录不存在";
    ELSIF delete_count > 0 THEN
        result.success := TRUE;
        result.status_code := 1;
        result.message := "删除成功";
    ELSE
        result.success := FALSE;
        result.status_code := 2;
        result.message := "版本不匹配";
    END IF;
    
    RETURN result;
END
$$;

.NET端读取复合类型 #

using var conn = new NpgsqlConnection("你的连接字符串");
await conn.OpenAsync();

using var cmd = new NpgsqlCommand("SELECT * FROM institution_delete_by_id(@id, @version)", conn);
cmd.Parameters.AddWithValue("id", targetId);
cmd.Parameters.AddWithValue("version", targetVersion);

using var reader = await cmd.ExecuteReaderAsync();
if(await reader.ReadAsync())
{
    var statusCode = reader.GetInt32(1);
    
    switch(statusCode)
    {
        case 0:
            return NotFound();
        case 1:
            return NoContent();
        case 2:
            return Conflict(reader.GetString(2));
        default:
            return BadRequest();
    }
}

内容的提问来源于stack exchange，提问作者STHA