How to do an out parameter with Npgsql and PostgreSQL?

Question

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

See this: http://stackoverflow.com/questions/2085421/error-query-has-no-destination-for-result-data Not able to check but perhaps: _SELECT lastval() INTO _CustomerID_ ? — Steve, Aug 24 '15 at 16:36

score 1 · Accepted Answer · answered Aug 24 '15 at 18:48

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 this issue).

How to do an out parameter with Npgsql and PostgreSQL?

1 Answers1