Search code examples
postgresqlstored-proceduresparameter-passingplpgsql

How to return a value from a stored procedure (not function)?

I have a Stored Procedure that inserts, updates or deletes tablerows. It was working fine while all parameters were used as input. However, I need to return the ID of last inserted row. For that I tried using an INOUT parameter and RETURNING after the INSERT statement to return the ID.

However, I am not sure how to bind the returned ID to the INOUT parameter. Following is the code for stored procedure:

CREATE OR REPLACE PROCEDURE public.spproductinsertupdatedelete(
_ser integer,
_subcategid integer,
_inrprice numeric,
_usdprice numeric,
_colour integer,
_size integer,
_qty integer,
_prodid integer DEFAULT NULL::integer,
inout _pid integer default null
 )
LANGUAGE 'plpgsql'
AS $BODY$

BEGIN
  if _ser=1 then --- Insert
    INSERT INTO product (prod_subcateg_id,prod_inr_price,prod_usd_price,prod_colour,prod_size,prod_qty)
    VALUES (_subcategID, _inrprice, _usdprice, _colour, _size, _qty)
    RETURNING prod_id;

ELSEIF _ser=2 THEN
    
    UPDATE PRODUCT SET
    prod_subcateg_id = _subcategid,
    prod_inr_price = _inrprice,
    prod_usd_price = _usdprice,
    prod_size = _size,
    prod_colour = _colour,
    prod_qty=_qty
    where prod_id = _prodID;

ELSEIF _ser=3 THEN ---- Delete
    UPDATE PRODUCT SET prod_datetill = now()
    WHERE prod_id = _prodID;
    
end if;

END
$BODY$;

On executing above stored procedure, I receive this error:

ERROR:  query has no destination for result data
Solution

  • Proof of concept

    A PROCEDURE can return values, but in a very limited fashion (as of Postgres 13).

    The manual on CALL:

    CALL executes a procedure.

    If the procedure has any output parameters, then a result row will be returned, containing the values of those parameters.

    The manual on CREATE PROCEDURE:

    argmode

    The mode of an argument: IN, INOUT, or VARIADIC. If omitted, the default is IN. (OUT arguments are currently not supported for procedures. Use INOUT instead.)

    So your use of the INOUT mode is correct. But the assignment in the function body is missing. And some other things are wrong / suboptimal. I suggest:

    CREATE OR REPLACE PROCEDURE public.spproductinsertupdatedelete(
  _ser        int
, _subcategid int
, _inrprice   numeric
, _usdprice   numeric
, _colour     int
, _size       int
, _qty        int
, INOUT _prod_id int DEFAULT NULL
)
  LANGUAGE plpgsql AS
$proc$
BEGIN
   CASE _ser    -- simpler than IF
   WHEN 1 THEN  -- INSERT
      INSERT INTO product
             (prod_subcateg_id, prod_inr_price, prod_usd_price, prod_colour, prod_size, prod_qty)
      VALUES (_subcategid     , _inrprice     , _usdprice     , _colour    , _size    , _qty    )
      RETURNING prod_id
      INTO _prod_id;   -- !!!

   WHEN 2 THEN  -- UPDATE
      UPDATE product
      SET   (prod_subcateg_id, prod_inr_price, prod_usd_price, prod_size, prod_colour, prod_qty)
          = (_subcategid     , _inrprice     , _usdprice     , _size    , _colour    , _qty)
      WHERE  prod_id = _prod_id;

   WHEN 3 THEN  -- soft-DELETE
      UPDATE product
      SET    prod_datetill = now()
      WHERE  prod_id = _prod_id;

   ELSE
      RAISE EXCEPTION 'Unexpected _ser value: %', _ser;
   END CASE;
END
$proc$;

    db<>fiddle here

    Take this as proof of concept. But I see nothing in the question warranting the use of a PROCEDURE in the first place.

    You probably want a FUNCTION

    A FUNCTION offers more options to return values, doesn't need to be run separately with CALL, and can be integrated in bigger queries. Chances are, that's what you wanted in the first place, and you were just being mislead by the widespread misnomer "stored procedure". See:

    Moreover, in the current form, you have to provide many noise parameters if you want to update or soft-delete a row. Plain SQL commands might do the job. Or separate functions ...

    The rule of thumb: if you don't need to manage transactions from within, you probably want to use a function instead of a procedure. Later, Postgres procedures may be extended to be able and return multiple result sets (per SQL standard), but not yet (pg 13).

    See: