Using npgsql to call a function that takes character as parameter

Question

I am trying to use Npgsql to invoke a function (stored procedure) that takes a CHARACTER as parameter, but it doesn't work. If I declare the same function without parameters, or with an INTEGER parameter, I get the result sets that I want. When I declare the parameter as CHARACTER, it stops working. What is wrong?

Here is the code of my function:

CREATE OR REPLACE FUNCTION testrefcursor1(in xxx character varying(10)) RETURNS SETOF refcursor AS
$$
DECLARE 
  ref1 refcursor;
  ref2 refcursor;
BEGIN

OPEN ref1 FOR 
 SELECT * FROM accounts;
RETURN NEXT ref1;

OPEN ref2 FOR 
 SELECT * FROM accounts;
RETURN NEXT ref2;

RETURN;
END;
$$
LANGUAGE plpgsql;

And here is the C# code that I am using:

var connection = new Npgsql.NpgsqlConnection(connectionString.ConnectionString);

connection.Open();
var trans = connection.BeginTransaction();

var command = new Npgsql.NpgsqlCommand("testrefcursor1", connection);
command.CommandType = System.Data.CommandType.StoredProcedure;

var parameter = command.CreateParameter();
parameter.ParameterName = "xxx";
parameter.DbType = System.Data.DbType.String;
parameter.Value = "10";
command.Parameters.Add(parameter);

var da = new Npgsql.NpgsqlDataAdapter(command);
var ds = new System.Data.DataSet();
da.Fill(ds);

trans.Commit();
connection.Close();

I already tried declaring the parameter as CHARACTER, CHARACTER(10), CHARACTER VARYING and CHARACTER VARYING(10)...

EDIT: I don't get any error message, but instead of getting the expected result set, I get an empty result set with a single column that has the same name as the function I am trying to call.

Answer 1

You're passing a Unicode argument to an ASCII parameter.

Change this line:

parameter.DbType = System.Data.DbType.String;

To:

parameter.DbType = System.Data.DbType.AnsiString;

Generally, Postgres's varchar columns are in Unicode, provided that the Unicode option on the database is enabled (see here). My guess is that it's not, and your parameter is unable to convert itself into the correct type to be passed through the function.