postgresql - 将一条记录从游标发送到另一个函数Postgres

Question

仅供引用:我完全不熟悉使用游标...所以我有一个函数是游标:

CREATE FUNCTION get_all_product_promos(refcursor, cursor_object_id integer) RETURNS refcursor AS '
BEGIN
    OPEN $1 FOR SELECT *
                FROM promos prom1
                JOIN promo_objects ON (prom1.promo_id = promo_objects.promotion_id)
                WHERE prom1.active = true AND now() BETWEEN prom1.start_date AND prom1.end_date
                    AND promo_objects.object_id = cursor_object_id
                UNION 
                SELECT prom2.promo_id 
                FROM promos prom2 
                JOIN promo_buy_objects ON (prom2.promo_id = 
                    promo_buy_objects.promo_id)
                LEFT JOIN promo_get_objects ON prom2.promo_id = promo_get_objects.promo_id
                WHERE (prom2.buy_quantity IS NOT NULL OR prom2.buy_quantity > 0) AND
                    prom2.active = true AND now() BETWEEN prom2.start_date AND  
                    prom2.end_date AND promo_buy_objects.object_id = cursor_object_id;
    RETURN $1;
END;
' LANGUAGE plpgsql;

然后在另一个函数中我调用它并需要处理它:

...
--Get the promotions from the cursor
        SELECT get_all_product_promos('promo_cursor', this_object_id)
        updated := FALSE;
        IF FOUND THEN
        --Then loop through your results
            LOOP
                FETCH promo_cursor into this_promotion
                --Preform comparison logic -this is necessary as this logic is used in other contexts from other functions
                SELECT * INTO best_promo_results FROM get_best_product_promos(this_promotion, this_object_id, get_free_promotion, get_free_promotion_value, current_promotion_value, current_promotion);
...

所以这里的想法是从游标中选择，使用 fetch 循环(假定 next 是正确的？)并将获取的记录放入 this_promotion。然后将this_promotion中的记录发送给另一个函数。我不知道在 get_best_product_promos 中声明 this_promotion 的类型是什么。这是我所拥有的:

CREATE OR REPLACE FUNCTION get_best_product_promos(this_promotion record, this_object_id integer, get_free_promotion integer, get_free_promotion_value numeric(10,2), current_promotion_value numeric(10,2), current_promotion integer)
  RETURNS...

它告诉我:错误:plpgsql 函数不能采用类型记录

好的，首先我尝试了:

CREATE OR REPLACE FUNCTION get_best_product_promos(this_promotion get_all_product_promos, this_object_id integer, get_free_promotion integer, get_free_promotion_value numeric(10,2), current_promotion_value numeric(10,2), current_promotion integer)
  RETURNS...

因为我在 Postgres 文档中看到一些语法显示正在创建一个带有类型为“表名”的输入参数的函数，但它必须是表名而不是函数 :( 我知道我很接近，我被告知要使用游标来传递记录。所以我研究了。请帮助。

Answer 1

一种可能是将 get_all_product_promos 中的查询定义为 all_product_promos View 。然后您将自动拥有在函数之间传递的“all_product_promos%rowtype”类型。

也就是说，类似于:

CREATE VIEW all_product_promos AS
SELECT promo_objects.object_id, prom1.*
FROM promos prom1
JOIN promo_objects ON (prom1.promo_id = promo_objects.promotion_id)
WHERE prom1.active = true AND now() BETWEEN prom1.start_date AND prom1.end_date
UNION ALL
SELECT promo_buy_objects.object_id, prom2.*
FROM promos prom2
     JOIN promo_buy_objects ON (prom2.promo_id = promo_buy_objects.promo_id)
     LEFT JOIN promo_get_objects ON prom2.promo_id = promo_get_objects.promo_id
WHERE (prom2.buy_quantity IS NOT NULL OR prom2.buy_quantity > 0)
      AND prom2.active = true
      AND now() BETWEEN prom2.start_date AND prom2.end_date

您应该能够使用 EXPLAIN 验证查询 SELECT * FROM all_product_promos WHERE object_id = ?需要 object_id将参数放入两个子查询中，而不是事后过滤。然后从另一个函数你可以写:

DECLARE
  this_promotion all_product_promos%ROWTYPE;
BEGIN
  FOR this_promotion IN
        SELECT * FROM all_product_promos WHERE object_id = this_object_id
  LOOP
     -- deal with promotion in this_promotion
  END LOOP;
END

TBH 我会避免使用游标在 PLPGSQL 中传递记录。事实上，我会避免在 PLPGSQL 句号中使用游标 - 除非出于某种原因需要将整个结果集传递给另一个函数。这种简单地循环语句的方法要简单得多，需要注意的是整个结果集首先被具体化到内存中。

此方法的另一个缺点是，如果您需要向 all_product_promos 添加一列，您将需要重新创建所有依赖于它的函数，因为您无法使用“更改 View ”向 View 添加列。 AFAICT 这会影响使用 CREATE TYPE 创建的命名类型也是，因为ALTER TYPE似乎也不允许您向类型添加列。

因此，您可以使用“CREATE TYPE”指定记录格式以在函数之间传递。任何关系都会自动指定一个名为 <relation>%ROWTYPE 的类型您也可以使用它。