Когда данные из процедуры существуют в PostgreSQL, но отображается сообщение, что они не существуют?

 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;
    }`

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("Хранимая процедура не существует.");
                }
            }

Ответ

Проблема, с которой вы столкнулись, связана с тем, как вы вызываете хранимую процедуру и с тем, как PostgreSQL обрабатывает имена функций и процедур. Давайте рассмотрим возможные причины возникновения ошибки и способы их устранения.

Основные моменты для проверки

1. Имена и Неймеры: PostgreSQL чувствителен к регистру имен. Если вы создаете процедуру с использованием кавычек (например, "app_perm_sp_pkg$get_all_tenants"), то вам нужно точно использовать это имя (включая регистр) в ваших запросах.

2. Область видимости схемы: Убедитесь, что вы правильно указываете требуемую схему при вызове процедуры. В вашем случае, например, используйте полное имя процедуры, включая схему: app_sec."app_perm_sp_pkg$get_all_tenants".

3. Использование PERFORM: При использовании команды PERFORM в блоке DO, убедитесь, что вы используете правильное количество параметров и их типы.

Исправленный код

Вот как должен выглядеть ваш код вызова процедуры:

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

    try
    {
        DataSet ds = new DataSet();
        OpenDB();

        string sqlstr = @"
        DO $$
        DECLARE
            p_tenant_cur REFCURSOR;
            p_status INTEGER;
            p_status_desc TEXT;
        BEGIN
            -- Вызов хранимой процедуры для инициализации курсора
            CALL app_sec.""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)
    {
        // Обработка исключений
        response.ErrorMessage = ex.Message; // Сохраняем сообщение об ошибке для дальнейшей диагностики
    }

    return response;
}

Описание изменений:

• Используйте CALL вместо PERFORM для вызова процедуры. CALL следует использовать для выполнения хранимых процедур, когда вам нужно вернуть данные через OUT параметры.
• Убедитесь, что вы используете правильные кавычки при указании имени процедуры.
• Добавлен простой механизм обработки ошибок, чтобы можно было отследить, что именно пошло не так, если возникает исключение.

Проверка существования процедуры

Ваш код для проверки существования процедуры выглядит правильно, но убедитесь, что вы используете правильный синтаксис:

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("Stored procedure does not exist.");
    }
}

Такое выражение должно правильно проверить существование процедуры. Однако используйте одинарные кавычки для строковых литералов в SQL-запросах.

Заключение

Следуя вышеуказанным рекомендациям, вы сможете устранить проблему с вызовом хранимой процедуры в PostgreSQL из C#. Убедитесь, что все имена и схемы указаны корректно, и используйте правильные методы для вызова процедур.