Is it possible to pass a refcursor as a parameter directly to FETCH on Npgsql?

Question

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();
        }
    }
}

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.

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.

Answer 1

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.

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

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.

(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.)