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

Question

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?

Using the same user "western", can you successfully call the stored procedure in the plsql console / pgadmin? (i.e. does western has the execution rights on the function) — JGH
– JGH, Commented Jun 28, 2017 at 13:21

JGH · Accepted Answer · 2017-07-13 11:59:51Z

3

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.

edited Jul 13, 2017 at 11:59

answered Jun 28, 2017 at 11:13

JGH

18.2k5 gold badges40 silver badges57 bronze badges

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

4 Comments

LavsTo Over a year ago

I got rid of the qoutes in the function, renamed the function in all lower case and I am still getting the same error.

LavsTo Over a year ago

@JHG I have changed code to use searchstring. No errors are popping up and I am getting to the search result page, but its empty and not showing records from a string that should otherwise contain records. Could the return type and view/cshtml code be wrong in this case?

JGH Over a year ago

It's a different question... though you are missing the % associated with the like. Same as here.

LavsTo Over a year ago

thanks for the assist as always @JGH, I will take a look at that link. Cheers.

Collectives™ on Stack Overflow

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

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related