I am learning Npgsql and PostgreSQL. I am unable to define the output parameter correctly. What am I doing wrong?
Here is the function:
CREATE OR REPLACE FUNCTION Insert_Customer_WithOutputParameter( IN _FirstName character varying DEFAULT NULL::character varying, IN _LastName character varying DEFAULT NULL::character varying, OUT _CustomerID integer) RETURNS integer as $BODY$ BEGIN INSERT INTO Customers (FirstName, LastName) VALUES (_FirstName, _LastName); SELECT _CustomerID = lastval(); END $BODY$ LANGUAGE plpgsql VOLATILE COST 100;
Here is the code:
[Test] public void ExecuteNonQuerySproc() { NpgsqlConnection conn = new NpgsqlConnection("Host=localhost; Database=postgres; User ID=postgres; Password=password"); conn.Open(); IDbCommand command = conn.CreateCommand(); command.CommandText = "Insert_Customer_WithOutputParameter"; command.CommandType = CommandType.StoredProcedure; command.Parameters.Add(new NpgsqlParameter("@FirstName", "John")); command.Parameters.Add(new NpgsqlParameter("@LastName", "Smith")); NpgsqlParameter outParm = new NpgsqlParameter("@CustomerID", NpgsqlDbType.Integer) { Direction = ParameterDirection.Output }; command.Parameters.Add(outParm); command.ExecuteNonQuery(); conn.Close(); Console.WriteLine(outParm.Value); }
Here is the error message I am getting: Npgsql.NpgsqlException : ERROR: 42601: query has no destination for result data
The following doesn't work:
SELECT _CustomerID = lastval();
Replace it with a simple:
_CustomerID = lastval();
Note that Npgsql currently binds parameters by position only, and not by name. This means that the names you give in the NpgsqlParameter instances mean nothing - their order of addition must correspond to the function's declaration. Npgsql 3.1 will support named binding of function arguments (see