Отправка одной записи из курсора в другую функцию 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 (далее предполагается, что он правильный?) И поместить выбранную запись в this_promotion. Затем отправьте запись в this_promotion в другую функцию. Я не могу понять, что объявлять тип this_promotion в get_best_product_promos. Вот что у меня есть:

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, показывал созданную функцию с входным параметром, который имел тип 'tablename', это работает, но это должно быть имя таблицы, а не функция:(Я знаю, что я так близко, я был сказал использовать курсоры для передачи записей. Так что я учился. Пожалуйста, помогите.

Answer 1

Одной из возможностей может быть определение запроса, который у вас есть в get_all_product_promos, как представления all_product_promos. Тогда у вас автоматически будет тип "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, вам нужно будет воссоздать все функции, которые зависят от него, так как вы не можете добавить столбцы в представление с помощью "alter view". AFAICT это влияет на именованные типы, созданные с CREATE TYPE тоже, так как ALTER TYPE Похоже, вы не можете добавлять столбцы к типу.

Таким образом, вы можете указать формат записи, используя "CREATE TYPE" для перехода между функциями. Любое отношение автоматически определяет тип с именем <relation>%ROWTYPE который вы также можете использовать.

Answer 2

Ответ:

Выберите определенные поля в функции курсора вместо *

затем:

CREATE TYPE get_all_product_promos as (buy_quantity integer, discount_amount numeric(10,2), get_quantity integer, discount_type integer, promo_id integer);

Тогда я могу сказать:

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...