Postgres 13

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
CREATE TABLE product (
  prod_id          serial PRIMARY KEY
, prod_subcateg_id int
, prod_inr_price   numeric
, prod_usd_price   numeric
, prod_colour      int
, prod_size        int
, prod_qty         int
, prod_datetill    timestamptz
);

CREATE TABLE


CREATE OR REPLACE PROCEDURE pg_temp.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$;

CREATE PROCEDURE


CALL pg_temp.spproductinsertupdatedelete(
  _ser        => 1  -- insert
, _subcategid => 1
, _inrprice   => 1
, _usdprice   => 1
, _colour     => 1
, _size       => 1
, _qty        => 1
-- , _prod_id  --  leave NULL
);


_prod_id


1



CALL


TABLE product;


prod_id
prod_subcateg_id
prod_inr_price
prod_usd_price
prod_colour
prod_size
prod_qty
prod_datetill


1
1
1
1
1
1
1
null



SELECT 1


CALL pg_temp.spproductinsertupdatedelete(
  _ser        => 2  -- update
, _subcategid => 1
, _inrprice   => 1
, _usdprice   => 666
, _colour     => 1
, _size       => 1
, _qty        => 1
, _prod_id    => 1
);


_prod_id


1



CALL


TABLE product;


prod_id
prod_subcateg_id
prod_inr_price
prod_usd_price
prod_colour
prod_size
prod_qty
prod_datetill


1
1
1
666
1
1
1
null



SELECT 1


CALL pg_temp.spproductinsertupdatedelete(
  _ser        => 3  -- soft-delete
, _subcategid => 1
, _inrprice   => 1
, _usdprice   => 1
, _colour     => 1
, _size       => 1
, _qty        => 1
, _prod_id    => 1
);


_prod_id


1



CALL


TABLE product;


prod_id
prod_subcateg_id
prod_inr_price
prod_usd_price
prod_colour
prod_size
prod_qty
prod_datetill


1
1
1
666
1
1
1
2025-03-05 10:52:09.153688+00



SELECT 1