解决postgresql -- ERROR: 42601: query has no destination for result data

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

 

修改前：

-- Function: sp_updatesecurity(integer, character, character, character, character, integer, integer, character, integer)

-- DROP FUNCTION sp_updatesecurity(integer, character, character, character, character, integer, integer, character, integer);

CREATE OR REPLACE FUNCTION sp_updatesecurity(
    OUT sqlcode_out integer,
    IN parastaffid integer,
    IN parafunctioncode character,
    IN paraviewflag character,
    IN paramodifyflag character,
    IN paraadvanceflag character,
    IN paralocalversion integer,
    IN original_staffid integer,
    IN original_functioncode character,
    IN original_localversion integer)
  RETURNS integer AS
$BODY$                                                              
DECLARE SQLCODE_OUT_TMP INT DEFAULT 0;
begin
IF (paraVIEWFLAG IS NULL) OR ((paraVIEWFLAG <> '1') AND (paraVIEWFLAG <> '0')) THEN
    set SQLCODE_OUT_TMP = -1;
END IF;

IF (paraMODIFYFLAG IS NULL) OR ((paraMODIFYFLAG <> '1') AND (paraMODIFYFLAG <> '0')) THEN
    SET SQLCODE_OUT_TMP = -2;
END IF;

IF (paraADVANCEFLAG IS NULL) OR ((paraADVANCEFLAG <> '1') AND (paraADVANCEFLAG <> '0')) THEN
    SET SQLCODE_OUT_TMP = -3;
END IF;

IF NOT EXISTS (SELECT *
               FROM DMS.CM_STAFF
               WHERE STAFFPKID = paraSTAFFID AND STATUS = '10' AND (DELETED IS NULL OR DELETED = '0')) THEN
    SET SQLCODE_OUT_TMP = -4;
END IF;

IF NOT EXISTS (SELECT *
               FROM DMS.MM_FUNCTION
               WHERE FUNCTIONCODE = paraFUNCTIONCODE ) THEN
    SET SQLCODE_OUT_TMP = -5;
END IF;

UPDATE DMS.MM_SECURITY
    SET STAFFID = paraSTAFFID
    , FUNCTIONCODE = paraFUNCTIONCODE
    , VIEWFLAG = paraVIEWFLAG
    , MODIFYFLAG = paraMODIFYFLAG
    , ADVANCEFLAG = paraADVANCEFLAG
    , INPUTTIME = CURRENT_TIMESTAMP
    , LOCALVERSION = LOCALVERSION + 1
WHERE (STAFFID = original_STAFFID) AND (FUNCTIONCODE = original_FUNCTIONCODE) AND (LOCALVERSION = original_LOCALVERSION);
SELECT LOCALVERSION INTO paraLOCALVERSION FROM MM_SECURITY
WHERE (STAFFID = paraSTAFFID) AND (FUNCTIONCODE = paraFUNCTIONCODE);
SELECT SQLCODE_OUT_TMP;

end
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION sp_updatesecurity(integer, character, character, character, character, integer, integer, character, integer)
  OWNER TO postgres;

 

修改后：

-- Function: sp_updatesecurity(integer, character, character, character, character, integer, integer, character, integer)

-- DROP FUNCTION sp_updatesecurity(integer, character, character, character, character, integer, integer, character, integer);

CREATE OR REPLACE FUNCTION sp_updatesecurity(
    OUT sqlcode_out integer,
    IN parastaffid integer,
    IN parafunctioncode character,
    IN paraviewflag character,
    IN paramodifyflag character,
    IN paraadvanceflag character,
    IN paralocalversion integer,
    IN original_staffid integer,
    IN original_functioncode character,
    IN original_localversion integer)
  RETURNS integer AS
$BODY$                                                              
DECLARE SQLCODE_OUT_TMP INT DEFAULT 0;
begin
IF (paraVIEWFLAG IS NULL) OR ((paraVIEWFLAG <> '1') AND (paraVIEWFLAG <> '0')) THEN
    set SQLCODE_OUT_TMP = -1;
END IF;

IF (paraMODIFYFLAG IS NULL) OR ((paraMODIFYFLAG <> '1') AND (paraMODIFYFLAG <> '0')) THEN
    SET SQLCODE_OUT_TMP = -2;
END IF;

IF (paraADVANCEFLAG IS NULL) OR ((paraADVANCEFLAG <> '1') AND (paraADVANCEFLAG <> '0')) THEN
    SET SQLCODE_OUT_TMP = -3;
END IF;

IF NOT EXISTS (SELECT *
               FROM DMS.CM_STAFF
               WHERE STAFFPKID = paraSTAFFID AND STATUS = '10' AND (DELETED IS NULL OR DELETED = '0')) THEN
    SET SQLCODE_OUT_TMP = -4;
END IF;

IF NOT EXISTS (SELECT *
               FROM DMS.MM_FUNCTION
               WHERE FUNCTIONCODE = paraFUNCTIONCODE ) THEN
    SET SQLCODE_OUT_TMP = -5;
END IF;

UPDATE DMS.MM_SECURITY
    SET STAFFID = paraSTAFFID
    , FUNCTIONCODE = paraFUNCTIONCODE
    , VIEWFLAG = paraVIEWFLAG
    , MODIFYFLAG = paraMODIFYFLAG
    , ADVANCEFLAG = paraADVANCEFLAG
    , INPUTTIME = CURRENT_TIMESTAMP
    , LOCALVERSION = LOCALVERSION + 1
WHERE (STAFFID = original_STAFFID) AND (FUNCTIONCODE = original_FUNCTIONCODE) AND (LOCALVERSION = original_LOCALVERSION);
SELECT LOCALVERSION INTO paraLOCALVERSION FROM MM_SECURITY
WHERE (STAFFID = paraSTAFFID) AND (FUNCTIONCODE = paraFUNCTIONCODE);
sqlcode_out = SQLCODE_OUT_TMP;

end
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION sp_updatesecurity(integer, character, character, character, character, integer, integer, character, integer)
  OWNER TO postgres;