Why am I getting exceptions running a function (stored procedure) through npgsql in .Net?

Question

I have a .Net application which talks to a Postgresql DB using Npgsql for .Net. I have a postgresql function which I am attempting to call through Npgsql. The function runs fine when I run it through directly in pgadmin. However, when I run it from my .Net (C#) application, it throws an exception.

Here is "cleansed" version of the function:

CREATE OR REPLACE FUNCTION Function1(id_param integer, 
                                    date1_param date, 
                                    date2_param date)
RETURNS TABLE (
            field1 character varying,
            field2 character varying,
            field3 bigint)
AS $$
BEGIN

    RETURN QUERY 

        SELECT table1.field1,
            table2.field2,
            table3.field3
        FROM table1 
            LEFT JOIN table2 on table1.empno = table2.empno 
            LEFT JOIN public.table3 on table1.docnum = table3.number 
        WHERE table1.entrydate >= date1_param
            AND table2.date1 >= date1_param
            AND table2.date2 <= date2_param;
    END;
$$ LANGUAGE plpgsql;  

Here is the exception I get at runtime:

{"42P01: invalid reference to FROM-clause entry for table \"table1\""}

What is wrong with the JOIN statements in my function to cause this exception?

Thanks, JohnB

Answer 1

I can't say for sure what's wrong, but I can tell you my recipe for successful function calls in NpgSql and apply it to your example.

First of all, your code, as I best understand it:

NpgsqlCommand command = null; 
DataTable dtResults = new DataTable(); 
command = new NpgsqlCommand(); 
command.Connection = connection; 
command.CommandType = CommandType.StoredProcedure; 
command.CommandText = functionName; 
NpgsqlDataAdapter da = new NpgsqlDataAdapter(command); 
da.Fill(dtResults);

In PostgreSQL, there is a blurred line (in my best assessment) between a function and a stored procedure. In order to call a function, the simplest way to do this is with a select, even if it doesn't return anything (though yours does) and then use a normal DbDataReader or DbDataAdapter.

In addition, your example above warrants parameters, none of which appeared in the C# call.

Finally, the stored procedure does not show fully qualified tables. Is it possible it's running as a different user ID in psql as it is in the .NET application? Either way, there is no harm in adding the schema name.

Here is what I recommend for your .NET code:

NpgsqlCommand command = new NpgsqlCommand("select * from Function1(:ID, :DATE1, :DATE2)",
    connection);
command.Parameters.Add(new NpgsqlParameter("ID", NpgsqlDbType.Integer));
command.Parameters.Add(new NpgsqlParameter("DATE1", NpgsqlDbType.Date));
command.Parameters.Add(new NpgsqlParameter("DATE2", NpgsqlDbType.Date));

command.Parameters[0].Value = id;
command.Parameters[1].Value = dt1;
command.Parameters[2].Value = dt2;

DataTable dtResults = new DataTable();
NpgsqlDataAdapter da = new NpgsqlDataAdapter(command);
da.Fill(dtResults);

Again, even if you don't need them, I'd add the schema name in front of the tables and functions.