PostgreSQL存储过程存在但C#调用报42883错误问题排查

我在PostgreSQL中创建了如下存储过程：

CREATE OR REPLACE PROCEDURE app_sec."app_perm_sp_pkg$get_all_tenants"(
    IN p_tenant_cur refcursor,
    OUT p_status integer,
    OUT p_status_desc text)

我尝试用以下C#代码调用该存储过程获取数据，但在执行cmd.ExecuteNonQuery()时出现错误：

42883: function app_perm_sp_pkg$get_all_tenants(refcursor, integer, text) does not exist

public ResponseStatus GetTenantList()
{
    ResponseStatus response = new ResponseStatus();

    try
    {
        DataSet ds = new DataSet();
        string cmdString = "";

        OpenDB();

        string sqlstr = @"
        DO $$
        DECLARE
            p_tenant_cur REFCURSOR;
            p_status INTEGER;
            p_status_desc TEXT;
        BEGIN
            -- 调用存储过程初始化游标
            PERFORM app_perm_sp_pkg$get_all_tenants(p_tenant_cur, p_status, p_status_desc);
        END $$;";

        using (var command = new NpgsqlCommand(sqlstr, postcon))
        {
            command.ExecuteNonQuery(); // 执行DO块以设置游标
        }

        // 从游标中获取结果
        string fetchSql = "FETCH ALL FROM p_tenant_cur;";

        using (var fetchCommand = new NpgsqlCommand(fetchSql, postcon))
        {
            using (NpgsqlDataAdapter adapter = new NpgsqlDataAdapter(fetchCommand))
            {
                adapter.TableMappings.Add("Table1", "p_tenant_cur");
                adapter.Fill(ds);
            }
        }

        response.ds = ds;

        CloseDB();
    }
    catch (Exception ex)
    {
    }

    return response;
}

我通过以下C#代码确认该存储过程确实存在：

string checkProcSql = @"
            SELECT proname
    FROM pg_proc
    JOIN pg_namespace ON pg_proc.pronamespace = pg_namespace.oid
    WHERE proname = 'app_perm_sp_pkg$get_all_tenants'
    AND nspname = 'app_sec'; ";

using (var checkCommand = new NpgsqlCommand(checkProcSql, postcon))
{
    var result = checkCommand.ExecuteScalar();

    if (result == null)
    {
        throw new Exception("存储过程不存在。");
    }
}

更新内容 #

我尝试了以下代码，但仍然失败并出现相同错误：

42883: function app_perm_sp_pkg$get_all_tenants(refcursor, integer, text) does not exist

public ResponseStatus GetTenantList()
        {
            ResponseStatus response = new ResponseStatus();
            try
            {
                DataSet ds = new DataSet();
                OpenDB();
              
                using (var command = new NpgsqlCommand("SELECT app_sec.\"app_perm_sp_pkg$get_all_tenants\"('p_tenant_cur', 'p_status', 'p_status_desc');", postcon))
                {
                    using (NpgsqlDataAdapter adapter = new NpgsqlDataAdapter(command))
                    {
                        adapter.TableMappings.Add("Table1", "p_tenant_cur");
                        adapter.Fill(ds);
                    }
                }

                response.ds = ds;

                CloseDB();
            }
            catch (Exception ex)
            {
                response.Status = "0";
                response.StatusDesc = Common.GetStringWithoutQuate(ex.Message);
            }
            return response;
        }

内容的提问来源于stack exchange，提问作者ahmed salah abd elaziz elbarba