1

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
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
Kartikeya
  • 53
  • 1
  • 1
  • 10
  • You might want to use `_pid = INSERT …`? – Bergi Jul 19 '20 at 14:35
  • You can't use OUT, you have to use INOUT:[SP](https://www.postgresql.org/docs/12/sql-createprocedure.html) "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.)" – Adrian Klaver Jul 19 '20 at 15:16
  • If you want to return something, use a function –  Jul 19 '20 at 16:32

2 Answers2

6

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:

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • Thanks for the solution, it worked. I am using 'Functions' wherever I have to return a result set and for rest of CRUD functions, I am using procedures. Is this approach not okay? The reason why I have not made different Update and soft-delete function is due to large numbers of tables. I thought to have one 'Procedure' to do Insert/ Update/ Delete for each table in order to have better management. Please suggest if I can improve in this. – Kartikeya Jul 19 '20 at 18:31
  • @Kartikeya: I added a rule of thumb and some more links to details. And I would probably split the function / procedure into three separate ones for INSERT / UPDATE / soft-DELETE - or simply use SQL commands directly. – Erwin Brandstetter Jul 19 '20 at 21:42
0

I used inout arguments in my procedure and then select suppose count into that inout variable i could have my multiple outputs returned from the procedure.

pndey
  • 71
  • 4