CODEWITHSUNDEEP
c#postgresqlfunction
LavsTo
LavsTo

Reputation: 129

42883: function does not exist - Calling Postgres Functions in Entity Framework C# Npgsql

I have the following function in my Postgres Database

CREATE OR REPLACE FUNCTION "thisSearchList"(IN searchstring text)
 RETURNS TABLE(q_description text, q_barcode text) AS
$BODY$
    SELECT q_description, q_barcode 
    FROM q_product
    WHERE q_description like $1
OR q_barcode like $1    
$BODY$
LANGUAGE sql VOLATILE
COST 100
ROWS 1000;

In my C# code I have the following code to utilize said function 

NpgsqlConnection connection = new NpgsqlConnection("Host=192.168.0.52;Database=bolo;Username=western;Password=western");
connection.Open(); /*OPEN DATABASE CONNECTION*/

NpgsqlCommand cmd = new NpgsqlCommand("thisSearchList", connection);
cmd.CommandType = CommandType.StoredProcedure;

cmd.Parameters.Add("@string", NpgsqlTypes.NpgsqlDbType.Text);
cmd.Parameters["@string"].Value = searchValue.ToUpper();

NpgsqlDataReader dr = cmd.ExecuteReader();

while (dr.Read())
{
var prod = new ProductViewModel();
prod.q_description = dr["q_description"].ToString();
prod.q_barcode = dr["q_barcode"].ToString();               

model.Add(prod);  
}

On running the application - user types in search textbox and clicks search to trigger the function and return a list of products to display on view/page: I get the following error

42883: function thissearchlist(string := text) does not exist

with the source at 

NpgsqlDataReader dr = cmd.ExecuteReader();

From my understanding through googling, Postgres might have an issue with strings and text!? Where its looking for a function that had a parameter string rather than text as defined in db (can only define text (not string) in postgres). But then wouldnt this line

cmd.Parameters.Add("@string", NpgsqlTypes.NpgsqlDbType.Text);

would have solved the issue when passing the parameter with the function, so it gets the appropriate function (with text parameter) when called? What am I missing at this point, or its not not possible to call postgres functions this way?

Upvotes: 3

Views: 10532

Answers (1)

JGH
JGH

Reputation: 17836

You are using double quotes and capital letters when creating the function so its name is now case sensitive.

CREATE OR REPLACE FUNCTION "thisSearchList"

When you call the function in the C# code, you are quoting a string but the content of this string does not contain the required quotes for the function name (which is case sensitive), and it is why the error message you get displays the function name with lower case letters

new NpgsqlCommand("thisSearchList", connection);

The painful way is to add the 2nd quotes:

new NpgsqlCommand("\"thisSearchList\"", connection);

The easy way, over the long term, is to get rid of the case sensitivity of your function by removing the quotes: 

CREATE OR REPLACE FUNCTION thisSearchList

** EDIT **

You are using a named parameter but the name does not match. You should use @searchstring instead of @string, or don't use any name at all to use positional parameter matching.

Upvotes: 3

Related Questions