PG 14 FUNCTION with cursor bug

[avber]

Hello,

I copied the following code almost verbatim from https://github.com/jonwagner/Insight.Database/wiki/PostgreSQL-Provider and received 'Unable to cast object of type 'System.String' to type 'System.Int32'.'

Steps to reproduce

using (var connection = new Repository().GetDbConnection().OpenWithTransaction()) { connection.ExecuteSql("CREATE TABLE PostgreSQLTestTable (p int)"); connection.ExecuteSql(@" CREATE OR REPLACE FUNCTION PostgreSQLTestProc (i int) RETURNS SETOF refcursor AS $$ DECLARE rs refcursor; BEGIN

                    INSERT INTO PostgreSQLTestTable VALUES (@i);
                    OPEN rs FOR SELECT * FROM PostgreSQLTestTable;
                    RETURN NEXT rs;
                END;
                $$ LANGUAGE plpgsql;");
            var result = connection.Query<int>("PostgreSQLTestProc", new { i = 5 });

        }

• Dotnet version: [netcore6]
• Database: [PG 14]
• Library version: [6.3.10]

In general, I have troubles getting data from functions with cursors.

[jonwagner]

Given your error message of Unable to cast object of type 'System.String' to type 'System.Int32'.

My guess is that the table PostgreSQLTestTable contains strings not ints. Like .net in general, Insight will not automatically coerce incompatible types.

Check to ensure that you have not created another table with the same name / value, as test code can often confuse you.

If that is not the case, please update with a full set of code and stack trace.

[avber]

Jon,

I copied the code from the wiki. connection.ExecuteSql("CREATE TABLE PostgreSQLTestTable (p int)");

The only column contains integers.

Thanks

[stale[bot]]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. Thank you for your contributions.