Npgsql/ Postgresql: "function does not exist" error message when it does

Question

scratching my head on this. There's a similar question that might be related at "function does not exist," but I really think it does and PostgreSQL function does not exist but the answer(s) does not seem very obvious. PostgreSQL 9.5.

I have an Npgsql-based membership query that looks like this:

using (var conn = new NpgsqlConnection(ConnectionString))
{
    conn.Open();
    using (var comm = new NpgsqlCommand("get_user_by_username", conn))
    {
        comm.CommandType = CommandType.StoredProcedure;
        comm.Parameters.Add("_user_name", NpgsqlDbType.Varchar, 250).Value = username;
        comm.Parameters.Add("_application_name", NpgsqlDbType.Varchar, 250).Value = _ApplicationName;
        comm.Parameters.Add("_online", NpgsqlDbType.Boolean).Value = userIsOnline;
        using (var reader = comm.ExecuteReader())
        {
            return GetUsersFromReader(reader).OfType<MembershipUser>().FirstOrDefault();
        }
    }
}

This function exists in my postgresql db as:

CREATE OR REPLACE FUNCTION public.get_user_by_username(
    _user_name character varying,
    _application_name character varying,
    _online boolean)
  RETURNS SETOF user_record AS
$BODY$begin

if _online then
    return query
    update users
    set
        last_activity = current_timestamp
    where
        lower(application_name) = lower(_application_name)
        and lower(user_name) = lower(_user_name)
    returning
        user_id,
        user_name,
        last_activity,
        created,
        email,
        approved,
        last_lockout,
        last_login,
        last_password_changed,
        password_question,
        comment;
else
    return query
    select
        user_id,
        user_name,
        last_activity,
        created,
        email,
        approved,
        last_lockout,
        last_login,
        last_password_changed,
        password_question,
        comment
    from
        users
    where
        lower(application_name) = lower(_application_name)
        and lower(user_name) = lower(_user_name);
        end if;

end;

$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100
  ROWS 1000;
ALTER FUNCTION public.get_user_by_username(character varying, character varying, boolean)
  OWNER TO (configured db login);

I've checked, double-checked, and triple-checked the connection string... it's pointed to this db, with the proper login. The function executes fine from a pgAdmin window.

my connection string resembles this:

Server=localhost;Port=5432;Database=mysecuritydb;User Id=(configured db login);Password=(my password);Pooling=true;ConvertInfinityDateTime=true;

...with these credentials, I can see the function:

Yet, when I am using this as a referenced library in my asp.net project, I get the following message:

Server Error in '/' Application.

42883: function get_user_by_username(_user_name => character varying, _application_name => character varying, online => boolean) does not exist

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. 

Exception Details: Npgsql.PostgresException: 42883: function get_user_by_username(_user_name => character varying, _application_name => character varying, online => boolean) does not exist

Source Error: 

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

Stack Trace: 


[PostgresException (0x80004005): 42883: function get_user_by_username(_user_name => character varying, _application_name => character varying, online => boolean) does not exist]

I've used this library for a while, but this is the first time I've seen this message. Is there something I'm missing?

Answer 1

Note that postgres allows function overloading, so not only does the function NAME need to exist, but the types of the function parameters will also be used to determine which overload to use, e.g.

CREATE OR REPLACE FUNCTION public.get_user_by_username(varchar, varchar, boolean)   

Is not the same function as

CREATE OR REPLACE FUNCTION public.get_user_by_username(varchar, boolean, varchar)

etc.

When calling these functions, the parameter names, types and possibly orders must all match, else you'll get the

Npgsql.PostgresException: 42883: function does not exist

One additional gotcha which keeps biting me is Postgressql's case-sensitivity rules when defining functions. For example, without any surrounding "" quotes, the following function definition (using the default settings in pgAdmin 3):

CREATE FUNCTION MySchema.MyFunction(Parameter1 VARCHAR(40), parameTer2 VARCHAR(20))

registers the function with the signature: (use an IDE tool to verify this)

myschema.myfunction(parameter1 VARCHAR(40), parameter2 VARCHAR(20))

As a result, any attempt in C# to bind to

command.Parameters.Add("Parameter1", NpgsqlDbType.Varchar, 40);
command.Parameters.Add("parameTer2", NpgsqlDbType.Varchar, 20);

will fail with the error. Instead, you will need to bind against the all-lower-case parameters, i.e.

command.Parameters.Add("parameter1", NpgsqlDbType.Varchar, 40);
command.Parameters.Add("parameter2", NpgsqlDbType.Varchar, 20);

Unless you define the Function with Quotes:

CREATE FUNCTION "MySchema"."MyFunction"("Parameter1" VARCHAR(40), "parameTer2" VARCHAR(20))

That's why it's important for you to agree on a casing convention in your database / organisation, and then stick to it (all lowercase is quite common)

An alternative, albeit also prone to being fragile, is not to bind with named parameters at all, and instead use the ordinal position of the parameter, to bind it e.g.

var myParameter = new NpgsqlParameter
{
    // Leave `ParameterName` out entirely,
    Direction = ParameterDirection.Input,
    IsNullable = false,
    NpgsqlDbType = NpgsqlDbType.Varchar,
    Size = 20,
    Value = "SomeValue"
};
command.Parameters.Add(myParameter);
// Same for other parameter(s)

Answer 2

So @JGH caught the fact that the signature variable names in the error message are slightly different in the library than in the posted code... which shouldn't have happened, but I pulled down the source code, compiled it as a dependency project, and everything worked fine. So, the pre-compiled library has a problem, and I can work around it.

Thanks for the help!

Answer 3

I've just got the same problem and here is my finding:

The reason for this error is that after the function is created, PostgreSQL automatically converts the function name and parameter name to lowercase. When Npgsql calls this function, the function name is not case sensitive, but the parameter name is case sensitive.

I've spend half of the day looking for the error origin. Just have upgrated Npgsql from 2.2.5 to 4.1.5, PostgreSQL from 9 to 13 and my code stopped to work with 42883: function does not exist

I've made all parameters to be lowercase in the C# code

var data = dataConnection.QueryProc<FormOutput>("get_form_data", new[] {
    new DataParameter("countryid", countryId),
    new DataParameter("keyvalue", key),
    new DataParameter("maxqty", maxFormQty) });

to deal with this error

Function declaration is below

create or replace FUNCTION get_form_data (countryId varchar, key varchar, maxQty int)