概念证明

一个PROCEDURE * 可以 * 返回值，但以非常有限的方式（从Postgres 13开始）。
The manual on CALL :
CALL执行一个过程。
如果过程有任何输出参数，则将返回一个结果行，其中包含这些参数的值。
CREATE PROCEDURE上的手册：

argmode

参数的模式：IN、INOUT或VARIADIC。如果省略，默认值为IN。（过程当前不支持OUT参数。使用INOUT代替。）
所以你使用的INOUT模式是正确的。但是函数体中的赋值丢失了。还有一些事情是错误的/次优的。我建议：

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*

把这个当作概念的证明。但是我在问题中没有看到任何东西可以保证首先使用PROCEDURE。

你可能需要一个FUNCTION

FUNCTION提供了更多返回值的选项，不需要与CALL单独运行，并且可以集成到更大的查询中。很有可能，这正是您最初想要的，而您只是被广泛使用的“存储过程”误导了。参见：

• 如何从PostgreSQL存储过程中获取结果集？

此外，在当前表单中，如果要更新或软删除行，则必须提供许多噪声参数。普通的SQL命令可以完成这项工作。或者分开的功能…

rule of thumb：如果您不需要从内部管理事务，您可能希望使用函数而不是过程。稍后，Postgres过程可能会扩展到能够返回多个结果集（根据SQL标准），但还没有（第13页）。

参见：

• In PostgreSQL, what is the difference between a “Stored Procedure” and other types of functions?
• 存储过程在Postgres的数据库事务中运行吗？
• https://www.2ndquadrant.com/en/blog/postgresql-11-server-side-procedures-part-1/#comment-72
• What are the differences between “Stored Procedures” and “Stored Functions”?

我在我的过程中使用了inout参数，然后选择假设计数到那个inout变量中，我可以从过程中返回多个输出。