Npgsql / Postgresql：“功能不存在”出现错误消息 [英] Npgsql/ Postgresql: "function does not exist" error message when it does

问题描述

为此我挠头。在

但是，当我将其用作asp.net项目中的引用库时，收到以下消息：

/应用程序中的服务器错误。

42883：函数get_user_by_username（_user_name =>字符变化，_application_name =>字符变化，在线=>布尔值）不存在

描述：发生未处理的异常在执行当前Web请求期间。请查看堆栈跟踪，以获取有关错误及其在代码中起源的更多信息。

异常详细信息：Npgsql.PostgresException：42883：函数get_user_by_username（_user_name =>字符变化，_application_name =>字符变化，在线=>布尔值）不存在

源错误：

在执行当前Web请求的过程中生成了未处理的异常。可以使用下面的异常堆栈跟踪来标识有关异常的来源和位置的信息。

堆栈跟踪：


[PostgresException（0x80004005）：42883：函数get_user_by_username（_user_name =>字符变化，_application_name =>字符变化，在线= >布尔值不存在]


我已经使用了一段时间了，但这是我第一次看到此消息。我缺少什么吗？

解决方案

所以@JGH捕获到以下事实：错误消息中的签名变量名称略有变化库中的内容与发布的代码中的内容有所不同……这本不应该发生的，但是我拉下了源代码，将其编译为依赖项项目，并且一切正常。因此，预编译的库有问题，我可以解决它。

感谢您的帮助！

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?

解决方案

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!

这篇关于Npgsql / Postgresql：“功能不存在”出现错误消息的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！