是否可以将refcursor作为参数直接传递给Npgsql上的FETCH？ [英] Is it possible to pass a refcursor as a parameter directly to FETCH on Npgsql?

问题描述

在针对PostgreSQL使用Npgsql库时，我找不到任何将光标ref传递给 FETCH 命令作为参数的方法。

I can't find any way to pass a cursor ref into a FETCH command as a parameter, when using the Npgsql library against PostgreSQL. Is this possible?

using (DbConnection connection = new NpgsqlConnection(connectionString))
{
    connection.Open();
    using (DbTransaction transaction = connection.BeginTransaction())
    {
        var outCursor = new NpgsqlParameter("cursor", NpgsqlTypes.NpgsqlDbType.Refcursor);
        outCursor.Direction = ParameterDirection.Output;

        // get a refcursor from somewhere
        using (DbCommand commandGet = connection.CreateCommand())
        {
            commandGet.CommandText = "get_cursor";
            commandGet.CommandType = CommandType.StoredProcedure;
            commandGet.Parameters.Add(outCursor);
            commandGet.Connection = connection;
            commandGet.ExecuteNonQuery();
        }

        // try to use it
        using (DbCommand commandFetch = connection.CreateCommand())
        {
            var inCursor = new NpgsqlParameter("cursor", NpgsqlTypes.NpgsqlDbType.Refcursor);
            inCursor.Direction = ParameterDirection.Input;
            inCursor.Value = outCursor.Value;

            // This commented out line using string interpolation works fine.
            // Can it be done with a parameter, as I'm trying to do below?
            //commandFetch.CommandText = $"FETCH 100 FROM \"{outCursor.Value}\"";

            // The same inCursor pattern used here works fine when the cursor is being passed
            // on to a function, but does not work for FETCH
            commandFetch.CommandText = $"FETCH 100 FROM :cursor";
            commandFetch.Parameters.Add(inCursor);

            commandFetch.Connection = connection;

            // This line fails for param-based version; 
            // works fine with string-interpolation version
            using (var reader = commandFetch.ExecuteReader())
            {
                while (reader.Read())
                {
                    int a = (int)reader[0];
                }
            }
        }

        // close it
        using (DbCommand commandClose = connection.CreateCommand())
        {
            // I would like to be able to pass the cursor as a true parameter here, too
            commandClose.CommandText = $"CLOSE \"{outCursor.Value}\"";
            commandClose.Connection = connection;
            var reader = commandClose.ExecuteNonQuery();
        }
    }
}

请注意注释行：我可以使代码正常工作，我只想找到一种使光标ref作为参数传递的方法。

Note the commented out line: I can make the code work, I would just like to find a way to make it work passing the cursor ref back as a parameter.

我得到的异常是 Npgsql.PostgresException：42601： $ 1或附近的语法错误。

The exception I get is Npgsql.PostgresException : 42601: syntax error at or near "$1".

传递纯字符串值的参数（看起来像

Passing a plain string valued parameter (which looks like it might work...) also fails, with the same exception.

上面的从输出游标创建输入游标的模式如果输入游标是

The above pattern for creating an input cursor from an output cursor works fine if the input cursor is being passed to a function.

推荐答案

此堆栈溢出答案及其后续注释基本上可以回答，由于底层数据库的限制，无法执行我想做的事情（至少在没有创建和执行动态SQL的情况下）

This Stack Overflow answer plus its follow up comments basically answers that it is not possible to do what I am trying to do (at least, not without creating and executing dynamic SQL), due to a limitiation in the underlying database.

您不能e FETCH语句中的变量。使用动态SQL：EXECUTE格式（ FETCH ALL FROM％I，foo）；

you cannot use a variable in a FETCH statement. Use dynamic SQL: EXECUTE format('FETCH ALL FROM %I', foo);

因此，这不是Npgsql的限制，另一个答案中建议的解决方法也可以在Npgsql中应用。或者，您也可以只将字符串插入SQL中，尽管这在某种程度上丑陋（至少在我看来），但实际上是绝对安全的。

So this is not a limitation of Npgsql, and the workarounds suggested in the other answer could be applied in Npgsql as well. Or you could just live with interpolating the string into the SQL, which though in some way 'ugly' (to my eyes, at least), is actually perfectly safe.

（在这种情况下-但是将值直接内插到SQL中通常是一个不好的主意，至少没有第二和第三种思考原因，以及关于即使在任何给定的有限用例中，在所有可能的情况下，它是否真的可以安全地防止注入攻击。 ）

(IN THIS CASE - but interpolating values directly into SQL is generally a BAD idea, without at least second and third thoughts about why, and about whether even in any given limited use-case it is REALLY safe against injection attacks under all conceivable circumstances.)

这篇关于是否可以将refcursor作为参数直接传递给Npgsql上的FETCH？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！