How to execute PostgreSQL functions with PSQL-specific arguments as stored procedures with Npgsql 6?

Question

I'm having some trouble porting C# code from Npgsql 2.2.7 to Npgsql 6.0.5 (PostgreSQL is version 12.x). Following code works just fine with Npgsql 2.2.7:

using (var conn = OpenConnection())
using (var cmd = conn.CreateCommand())
{
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.CommandText = "plainto_tsquery";
    cmd.Parameters.Add(new NpgsqlParameter(null, "german"));
    cmd.Parameters.Add(new NpgsqlParameter(null, "some test text"));
    var result = cmd.ExecuteScalar();
    Console.WriteLine(result);
    //result is string "'som' & 'test' & 'text'"
}

It doesn't work with Npgsql 6, ExecuteScalar throws following exception (translated, maybe not exactly the same text):

Npgsql.PostgresException: '42883: Function plainto_tsquery(text, text) doesn't exists.

I've read about the breaking changes in Npgsql and yes, the behavior is more ore less expected, as some implicit conversions doesn't occur anymore. But how to specify the first argument of type regconfig to match the function signature? Following throws an exception:

cmd.Parameters.Add(null, NpgsqlDbType.Regconfig).Value = "german";

System.InvalidCastException: 'Can't write CLR type System.String with handler type UInt32Handler'

Changing the CommandType to Text and issuing a SELECT statement works, but I'm not fully happy with it:

using (var conn = OpenConnection())
using (var cmd = conn.CreateCommand())
{
    cmd.CommandType = CommandType.Text;
    cmd.CommandText = "SELECT plainto_tsquery($1::regconfig, $2)";
    cmd.Parameters.AddWithValue(NpgsqlDbType.Text, "german");
    cmd.Parameters.AddWithValue(NpgsqlDbType.Text, "some test text");
    var result = cmd.ExecuteScalar();
    Console.WriteLine(result);
    //result is NpgsqlTypes.NpgsqlTsQueryAnd, ToString() gives: "'som' & 'test' & 'text'"
}

Is there a way to call such functions with PostgreSQL-specific type arguments as stored procedures and not as SELECT statements? How did Npgsql 2.x convert the parameters and/or command text?

Answer 1

Looked deeper into it and this is unfortunately more complicated than it seems - there's indeed a fundamental issue here.

PG supports two ways of transferring parameter data: text and binary. The binary encoding of regconfig is a simple int (IIRC an oid key in the pg_catalog.pg_ts_config table). So sending a parameterized regconfig in binary would mean that you need to look up your regconfig by name in pg_ts_config, get the oid (13395 for german), and then send that instead of the textual representation ("german").

Now, PG does have an implicit conversion from text to regconfig: if you specify a literal "german" in a context where regconfig is required, PG will do that lookup for you behind the scenes. However, this implicit conversion doesn't happen when the type is explicitly specified:

SELECT plainto_tsquery('german', 'some test text'); -- works
SELECT plainto_tsquery('german'::text, 'some test text'); --fails

Modern Npgsql always uses binary encoding for its parameters, and always explicitly specifies types on them, which is why no implicit conversion happens and the query fails.

Old versions of Npgsql sometimes interpolated parameters into the query string (while providing escaping to protect again SQL injection), which is why it may have worked in old versions.

Beyond all the technicalities, I agree that this is an unfortunate situation... Your solution with the explicit cast to regconfig is probably the best one, unless you want to download and cache pg_ts_config, in which case you can send the oid (but that seems like a lot of trouble for nothing).