Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / PostgreSQL Новый топик    Ответить
 Помогите разобрать функцию.  [new]
Moneta13
Member

Откуда:
Сообщений: 57
Добрый день. Хочу разобарться, как работает это функция

CREATE OR REPLACE FUNCTION public.rewards_report(min_monthly_purchases integer, min_dollar_amount_purchased numeric)
RETURNS SETOF customer
LANGUAGE plpgsql
SECURITY DEFINER
AS $function$
DECLARE
last_month_start DATE;
last_month_end DATE;
rr RECORD;
tmpSQL TEXT;
BEGIN
/* Some sanity checks... */
IF min_monthly_purchases = 0 THEN
RAISE EXCEPTION 'Minimum monthly purchases parameter must be > 0';
END IF;
IF min_dollar_amount_purchased = 0.00 THEN
RAISE EXCEPTION 'Minimum monthly dollar amount purchased parameter must be > $0.00';
END IF;
last_month_start := DATE_TRUNC ('month', (CURRENT_DATE - '4 year'::INTERVAL - '1 month'::INTERVAL));
last_month_end := LAST_DAY(last_month_start);
/*
Create a temporary storage area for Customer IDs.
*/
CREATE TEMPORARY TABLE tmpCustomer (customer_id INTEGER NOT NULL PRIMARY KEY);
/*
Find all customers meeting the monthly purchase requirements
*/
tmpSQL := 'INSERT INTO tmpCustomer (customer_id)
SELECT p.customer_id
FROM payment p
WHERE DATE(p.payment_date) BETWEEN '||quote_nullable(last_month_start) ||' AND '|| quote_nullable(last_month_end) || '
GROUP BY customer_id
HAVING SUM(p.amount) > '|| $2|| '
AND COUNT(customer_id) > ' ||$1 ;
EXECUTE tmpSQL;
/*
Output ALL customer information of matching rewardees.
Customize output as needed.
*/
FOR rr IN EXECUTE 'SELECT c.* FROM tmpCustomer AS t INNER JOIN customer AS c ON t.customer_id = c.customer_id' LOOP
RETURN NEXT rr;
END LOOP;
/* Clean up */
tmpSQL := 'DROP TABLE tmpCustomer';
EXECUTE tmpSQL;
RETURN;
END
$function$
;

Меня интересует момент, что результаты временной таблицы не смогут храниться в другом запросе, именно поэтому мы сохраняем их в tmpSQL? Далее из временной таблицы для каждого значения мы делаем другой запрос SELECT c.* FROM tmpCustomer AS t INNER JOIN customer AS c ON t.customer_id = c.customer_id и записываем его в rr RECORD так же с помощью динамического sql? И можно обойтись без динамического sql?
2 май 21, 15:54    [22317519]     Ответить | Цитировать Сообщить модератору
 Re: Помогите разобрать функцию.  [new]
Swa111
Member

Откуда:
Сообщений: 230
Moneta13, Используйте, пожалуйста, соответствующие теги подсветки блоков кода.

В таком виде, можно обойтись без динамики. Я бы даже сказал что он здесь лишняя.

+
CREATE OR REPLACE FUNCTION public.rewards_report(min_monthly_purchases integer, min_dollar_amount_purchased numeric)
RETURNS SETOF customer
LANGUAGE plpgsql
SECURITY DEFINER
AS $function$
DECLARE
last_month_start DATE;
last_month_end DATE;
rr RECORD;
tmpSQL TEXT;
BEGIN
/* Some sanity checks... */
IF min_monthly_purchases = 0 THEN
RAISE EXCEPTION 'Minimum monthly purchases parameter must be > 0';
END IF;
IF min_dollar_amount_purchased = 0.00 THEN
RAISE EXCEPTION 'Minimum monthly dollar amount purchased parameter must be > $0.00';
END IF;
last_month_start := DATE_TRUNC ('month', (CURRENT_DATE - '4 year'::INTERVAL - '1 month'::INTERVAL));
last_month_end := LAST_DAY(last_month_start);

return query
with tmpCustomer  as (SELECT p.customer_id
FROM payment p
WHERE DATE(p.payment_date) BETWEEN last_month_start AND last_month_end 
GROUP BY customer_id
HAVING SUM(p.amount) > min_dollar_amount_purchased 
AND COUNT(customer_id) > min_monthly_purchases )
SELECT c.* FROM tmpCustomer AS t INNER JOIN customer AS c ON t.customer_id = c.customer_id;


RETURN;
END;
$function$
;
3 май 21, 01:27    [22317685]     Ответить | Цитировать Сообщить модератору
 Re: Помогите разобрать функцию.  [new]
Moneta13
Member

Откуда:
Сообщений: 57
Swa111, Спасибо, учту насчет подстветки. А зачем вообще применялась переменная tmpSQL TEXT? Без динамического SQL, она вообще не надо, как я понял, как и rr RECORD. Но я не могу понять, зачем они используются в динамическом SQL
5 май 21, 13:55    [22318766]     Ответить | Цитировать Сообщить модератору
 Re: Помогите разобрать функцию.  [new]
Moneta13
Member

Откуда:
Сообщений: 57
Moneta13, Я предпологаю, что эта переменная хранит запрос, правильно думаю?
5 май 21, 14:04    [22318768]     Ответить | Цитировать Сообщить модератору
Все форумы / PostgreSQL Ответить