when get data from procedure exist on PostgreSQL it show not exist although it exist?

Question

I create stored procedure on PostgreSQL as below

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)

I try to get data by call sp from PostgreSQL using c# as below

but I get error

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

on cmd.executenonquery()

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
            -- Call the stored procedure to initialize the cursor
            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(); // Execute the DO block to set up the cursor
            }
            // Now fetch the results from the cursor
            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;
    }`

I try to confirm if stored procedure not exist or created using c# but it show me it exist using

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.");

                }

            }

Answer 1

asp.net web forms with ado.net

Answer 2

I also do not use PostgreSQL, but to do a fetch on a second command you need to call conn.BeginTransaction(), to turn off implicit commits, so the cursor stays open. also you can just use the same command.

            var tran = postcon.BeginTransaction();
            using (var command = new NpgsqlCommand(sqlstr, postcon))
            {
                command.ExecuteNonQuery(); // Execute the DO block to set up the cursor
                command.Text = "FETCH ALL FROM p_tenant_cur;";
                command.CommandType = CommandType.Text;
                using (NpgsqlDataAdapter adapter = new NpgsqlDataAdapter(command))
                {
                    adapter.TableMappings.Add("Table1", "p_tenant_cur");
                    adapter.Fill(ds);
                }
            }
            tran.Commit();
            response.ds = ds;

not sure how your connection (postcon) get created, but it should have a using statement.