CODEWITHSUNDEEP
c#postgresql
Doug Kimzey
Doug Kimzey

Reputation: 1174

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

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:

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

Changed the definition of the fname parameter from:

 fname character varying,

To:

  fname varchar(255),

  1. Executed the modified script.

  2. 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#?

Upvotes: 1

Views: 6693

Answers (1)

JGH
JGH

Reputation: 17836

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.

Upvotes: 1

Related Questions