如何从存储过程(不是函数)返回一个值? [英] How to return a value from a stored procedure (not function)?

问题描述

我有一个可以插入、更新或删除表格行的存储过程.当所有参数都用作输入时，它工作正常.但是，我需要返回最后插入行的 ID.为此，我尝试在 INSERT 语句之后使用 INOUT 参数和 RETURNING 来返回 ID.

但是，我不确定如何将返回的 ID 绑定到 INOUT 参数.以下是存储过程的代码:

创建或替换过程 public.spproductinsertupdatedelete(_ser 整数，_subcategid 整数，_inrprice 数字，_usdprice 数字，_color 整数，_size 整数，_qty 整数，_prodid 整数 DEFAULT NULL::integer,inout _pid 整数默认为空)语言'plpgsql'作为 $BODY$开始如果 _ser=1 那么 --- 插入插入产品 (prod_subcateg_id,prod_inr_price,prod_usd_price,prod_colour,prod_size,prod_qty)值(_subcategID、_inrprice、_usdprice、_colour、_size、_qty)返回 prod_id;ELSEIF _ser=2 那么更新产品集prod_subcateg_id = _subcategid,prod_inr_price = _inrprice,prod_usd_price = _usdprice,prod_size = _size,prod_colour = _colour,prod_qty=_qty其中 prod_id = _prodID;ELSEIF _ser=3 THEN ---- 删除更新产品集 prod_datetill = now()哪里 prod_id = _prodID;万一;结尾$身体$;

在执行上述存储过程时，我收到此错误:

<块引用>

错误:查询没有结果数据的目的地

解决方案

概念验证

A PROCEDURE 可以返回值，但以非常有限的方式(从 Postgres 13 开始).

CALL 手册:

<块引用>

CALL 执行一个过程.

如果过程有任何输出参数，那么结果行将是返回，包含这些参数的值.

CREATE 手册程序:

<块引用>

argmode

参数的模式:IN、INOUT 或 VARIADIC.如果省略，默认为 IN.(过程当前不支持 OUT 参数.请改用 INOUT.)

所以你使用INOUT模式是正确的.但是函数体中的赋值丢失了.还有其他一些事情是错误的/次优的.我建议:

创建或替换程序 public.spproductinsertupdatedelete(_ser 整数, _subcategid int, _inrprice 数字, _usdprice 数字, _color int, _size int, _qty 整数, INOUT _prod_id int DEFAULT NULL)语言 plpgsql AS$proc$开始CASE _ser -- 比 IF 简单当 1 然后 - 插入插入产品(prod_subcateg_id、prod_inr_price、prod_usd_price、prod_colour、prod_size、prod_qty)值(_subcategid、_inrprice、_usdprice、_colour、_size、_qty)返回 prod_idINTO _prod_id;——！！！当 2 然后 - 更新更新产品SET (prod_subcateg_id, prod_inr_price, prod_usd_price, prod_size, prod_colour, prod_qty)= (_subcategid, _inrprice, _usdprice, _size, _colour, _qty)哪里 prod_id = _prod_id;WHEN 3 THEN -- 软删除更新产品SET prod_datetill = 现在()哪里 prod_id = _prod_id;别的RAISE EXCEPTION '意外的 _ser 值:%', _ser;结束案例；结尾$proc$;

db<>fiddle 这里

以此作为概念证明.但我在问题中看不到任何保证使用 PROCEDURE 放在首位.

您可能想要一个 FUNCTION

A FUNCTION 提供了更多返回值的选项，不需要与 CALL 单独运行，并且可以集成到更大的查询中.很有可能，这就是您首先想要的，而您只是被广泛使用的误称存储过程"误导了.见:

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

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

经验法则:如果您不需要从内部管理事务，您可能希望使用函数而不是过程.稍后，Postgres 过程可能会扩展为能够并返回多个结果集(根据 SQL 标准)，但目前还不能(第 13 页).

见:

• 在 PostgreSQL 中，存储过程"与其他类型的函数有什么区别?立>
• 是否在数据库事务中运行存储过程在 Postgres 中?
• https://www.2ndquadrant.com/en/blog/postgresql-11-server-side-procedures-part-1/#comment-72
• 存储过程"和存储函数"有什么区别?

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

解决方案

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:

• How to get result set from PostgreSQL stored procedure?

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:

• In PostgreSQL, what is the difference between a "Stored Procedure" and other types of functions?
• Do stored procedures run in database transaction in 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"?

这篇关于如何从存储过程(不是函数)返回一个值?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！