How to get data from stored procedure created on postgree sql ?

Question

I create stored procedure on postgree sql 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)

my issue how to get data on c# when calling sp above
it give me 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 check sp exist or not by using c# it is exist because result not return null

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

Naimish Makwana · Answer

It looks like you’re encountering an issue with calling your PostgreSQL stored procedure from C#. The error message suggests that the function signature might not be matching what PostgreSQL expects. Here are a few steps to troubleshoot and resolve this issue:

Ensure the Stored Procedure Signature Matches: Verify that the stored procedure’s signature in PostgreSQL matches exactly what you’re calling from C#. The error message indicates that PostgreSQL cannot find a function with the specified name and parameters.
Correct the Stored Procedure Call: When calling a stored procedure with refcursor parameters, you need to handle the cursor properly. Here’s an updated version of your C# code to call the stored procedure and fetch the data:

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

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

        // Start a transaction
        using (var transaction = postcon.BeginTransaction())
        {
            // Call the stored procedure
            using (var command = new NpgsqlCommand("CALL app_sec.\"app_perm_sp_pkg$get_all_tenants\"(@p_tenant_cur, @p_status, @p_status_desc);", postcon))
            {
                command.Parameters.Add(new NpgsqlParameter("p_tenant_cur", NpgsqlTypes.NpgsqlDbType.Refcursor) { Direction = ParameterDirection.InputOutput });
                command.Parameters.Add(new NpgsqlParameter("p_status", NpgsqlTypes.NpgsqlDbType.Integer) { Direction = ParameterDirection.Output });
                command.Parameters.Add(new NpgsqlParameter("p_status_desc", NpgsqlTypes.NpgsqlDbType.Text) { Direction = ParameterDirection.Output });

                command.ExecuteNonQuery();

                // Fetch the results from the cursor
                command.CommandText = "FETCH ALL FROM p_tenant_cur;";
                using (var adapter = new NpgsqlDataAdapter(command))
                {
                    adapter.Fill(ds);
                }
            }

            // Commit the transaction
            transaction.Commit();
        }

        response.ds = ds;
        CloseDB();
    }
    catch (Exception ex)
    {
        // Handle exception
    }

    return response;
}

Check the Stored Procedure Existence: You’ve already verified that the stored procedure exists. Ensure that the schema and procedure name are correctly specified.
Ensure Proper Permissions: Make sure the user account used to connect to the PostgreSQL database has the necessary permissions to execute the stored procedure.
Verify PostgreSQL Version Compatibility: Ensure that the version of Npgsql and PostgreSQL you’re using are compatible. Sometimes, certain features or behaviors might differ between versions.

By following these steps, you should be able to call the stored procedure and fetch the data correctly.

Thanks

How to get data from stored procedure created on postgree sql ?

Naimish Makwana