function result type must be integer because of OUT parameters in postgresql

Question

In the below query when I execute it throws an error function result type must be integer because of OUT parameters.Pls anyone help me to overocme this issue.

CREATE OR REPLACE FUNCTION funinsertupdateproduct(i_productid integer, t_productname text, t_productdesc text, i_brandid integer, i_varianceid integer, i_packsizeid integer, i_clientid integer, i_userid integer,out i_prodid integer )
  RETURNS void  AS
$BODY$ 
BEGIN 
    IF EXISTS (SELECT 1 FROM  "product" WHERE product_id = i_productid) THEN
        UPDATE "product" SET "product_name" = t_productname,  "product_desc" = t_productdesc, "brand_id"=i_brandid,variance_id=i_varianceid,pack_size_id=i_packsizeid  WHERE product_id = i_productid;
    ELSE
        INSERT INTO "product"("product_name", "product_desc", "create_by","client_id",variance_id,brand_id,pack_size_id ) VALUES (  t_productname, t_productdesc,i_userid,i_clientid,i_varianceid,i_brandid,i_packsizeid) returning product_id INTO i_prodid;

END IF;
  RETURN;
END;  
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION funinsertupdateproduct(integer, text, text, integer, integer, integer, integer, integer,integer) OWNER TO postgres;

Answer 1

Postgres functions take some getting used to. In addition to being triggers and stored procedures, the way they handled returned values differs from other languages.

In essence, the OUT parameters represent the record that is being returned by the function. There is no separate method of a returning a value, so the void is redundant . . . and, in fact, incompatible with the OUT parameters.

The solution is simple. Just remove the RETURNS clause:

CREATE OR REPLACE FUNCTION funinsertupdateproduct (
    i_productid integer,
    t_productname text,
    t_productdesc text,
    i_brandid integer,
    i_varianceid integer,
    i_packsizeid integer,
    i_clientid integer,
    i_userid integer,
    out i_prodid integer
) AS
$BODY$ 
. . . 

Except for not defining i_prodid, this is equivalent to:

CREATE OR REPLACE FUNCTION funinsertupdateproduct (
    i_productid integer,
    t_productname text,
    t_productdesc text,
    i_brandid integer,
    i_varianceid integer,
    i_packsizeid integer,
    i_clientid integer,
    i_userid integer
) 
RETURNS integer AS
$BODY$ 
. . . 

Answer 2

Generally we define function with return type and procedure with OUT parameters.

If you want to define function, try with this

CREATE OR REPLACE FUNCTION funinsertupdateproduct(i_productid integer, t_productname text, t_productdesc text, i_brandid integer, i_varianceid integer, i_packsizeid integer, i_clientid integer, i_userid integer )
  RETURNS integer  AS
$BODY$ 
declare
i_prodid integer;
BEGIN 
    IF EXISTS (SELECT 1 FROM  "product" WHERE product_id = i_productid) THEN
        UPDATE "product" SET "product_name" = t_productname,  "product_desc" = t_productdesc, "brand_id"=i_brandid,variance_id=i_varianceid,pack_size_id=i_packsizeid  WHERE product_id = i_productid;
        i_prodid:=i_productid;
    ELSE
        INSERT INTO "product"("product_name", "product_desc", "create_by","client_id",variance_id,brand_id,pack_size_id ) VALUES (  t_productname, t_productdesc,i_userid,i_clientid,i_varianceid,i_brandid,i_packsizeid) returning product_id INTO i_prodid;

END IF;
  RETURN i_prodid;
END;  
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

Alternately you can use PROCEDURE with out parameters

CREATE OR REPLACE PROCEDURE  funinsertupdateproduct(i_productid integer, t_productname text, t_productdesc text, i_brandid integer, i_varianceid integer, i_packsizeid integer, i_clientid integer, i_userid integer ,out i_prodid integer)
    AS

BEGIN 
    IF EXISTS (SELECT 1 FROM  "product" WHERE product_id = i_productid) THEN
        UPDATE "product" SET "product_name" = t_productname,  "product_desc" = t_productdesc, "brand_id"=i_brandid,variance_id=i_varianceid,pack_size_id=i_packsizeid  WHERE product_id = i_productid;
        i_prodid:=i_productid;
    ELSE
        INSERT INTO "product"("product_name", "product_desc", "create_by","client_id",variance_id,brand_id,pack_size_id ) VALUES (  t_productname, t_productdesc,i_userid,i_clientid,i_varianceid,i_brandid,i_packsizeid) returning product_id INTO i_prodid;

END IF;
END; 

Hope it helps.

Answer 3

I got the same error below. *The error below occurs in a PL/pgSQL function and SQL function:

ERROR: function result type must be integer because of OUT parameters

Because, the types of an OUT parameter (INTEGER) and RETURNS <type> clause (VOID) are not the same in the PL/pgSQL function as shown below:

CREATE FUNCTION my_func(OUT value INTEGER) RETURNS VOID AS $$
BEGIN                             -- ↑ Here      -- ↑ Here
END;
$$ LANGUAGE plpgsql;

So, I set INTEGER to RETURNS <type> clause as shown below, then the error was solved:

CREATE FUNCTION my_func(OUT value INTEGER) RETURNS INTEGER AS $$
BEGIN                                              -- ↑ Here
END;
$$ LANGUAGE plpgsql;

Or, I unset RETURNS <type> clause as shown below, then the error was solved:

CREATE FUNCTION my_func(OUT value INTEGER) /* RETURNS VOID */ AS $$
BEGIN                                         -- ↑ Unset ↑
END;
$$ LANGUAGE plpgsql;