Npgsql.Postgresql Exception 42883 function does not exist when called from C# - is there a working example available?

Question

This seems to be a very common issue. I have gone through a number of the posts on PostgresException: 42883. Most of these are related to a mismatch in the function signature in the PostgreSQL database and the call from C#.

I am really not seeing the issue. Could someone enlighten me?

My PostgreSQL function is:

CREATE OR REPLACE FUNCTION public.getimportkey(
    instid integer,
    fname character varying,
    fhash text)
    RETURNS bigint
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE PARALLEL UNSAFE
AS $BODY$
declare
        impId bigint;
        begin
        
            select id 
            into impId
            from importlog
            where instrumentid = instId and
                  filename = fname and
                  filehash = fhash;
                  
            return impId;
            
        end;
$BODY$;

The C# code that generates the PostgresException: 42883 exception is below.

 using (NpgsqlTransaction transaction = connection.BeginTransaction())
        {
            NpgsqlCommand command = new NpgsqlCommand("getimportkey", connection);
                                                       
            command.CommandType = System.Data.CommandType.StoredProcedure;

            command.Parameters.AddWithValue("@instid",NpgsqlTypes.NpgsqlDbType.Integer,instrumentId);
            command.Parameters.AddWithValue("@fName",NpgsqlTypes.NpgsqlDbType.Varchar, fileInfo.Name);
            command.Parameters.AddWithValue("@fHash", NpgsqlTypes.NpgsqlDbType.Text, fileHash);

            int? impId = (int?)command.ExecuteScalar();
            if (impId != null) imported = true;

        }

I noticed that varchar(255) and character varying must be considered as equivalent types. I tried the following:

Dropped the getimportkey function from the database. I refreshed the function list and confirmed that getimportkey is gone.
Modified the CREATE script as follows:

Changed the definition of the fname parameter from:

 fname character varying,

To:

  fname varchar(255),

Executed the modified script.
Refreshed the function list. getimportkey is back, but the fname parameter type is shown as character varying.

I am guessing that varchar(255) is considered the same type as character varying.

Can anyone direct me to a working example of calling a PostgreSQL function with parameters from C#?

Looks like you type is char (one character) instead of an array. See : postgresql.org/docs/9.1/… — jdweng
– jdweng, Commented May 6, 2021 at 15:56

JGH · Accepted Answer · 2021-05-06 17:47:49Z

1

The function is declared with lower-case parameter names, but the c# code is using two capitalized names, so they don't match (@fName and @fHash)

You should use lowecase names in the calling code too, or ditch the names to rely on positional parameters only.

answered May 6, 2021 at 17:47

JGH

18.2k5 gold badges40 silver badges57 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Npgsql.Postgresql Exception 42883 function does not exist when called from C# - is there a working example available?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related