解决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;