我有一个触发器函数,在表上执行插入/删除之后运行,但似乎只有当我在supabase控制台中直接将行添加到DB中时,它才能正常工作,但是当我通过supabase js客户端执行插入/删除时,它的行为不同,这可能是什么原因呢?
下面是我的函数&触发器:
CREATE OR REPLACE FUNCTION h2h_run_pool_entry_matrix()
RETURNS TRIGGER AS
$$
DECLARE
v_total_pool_amount numeric;
v_primary_entry_amount numeric;
v_alt_entry_amount numeric;
v_commission numeric;
v_primary_multiplier numeric;
v_alt_multiplier numeric;
v_entrant_count int;
BEGIN
IF TG_OP = 'INSERT' THEN
-- set counter
SELECT COUNT(*)
INTO v_entrant_count
FROM (
SELECT 1
FROM public.h2h_pool_entry
WHERE pool_id = NEW.pool_id
FOR SHARE
) AS subquery;
-- set commission
SELECT commission
INTO v_commission
FROM public.h2h_pool
WHERE id = NEW.pool_id;
-- set primary entry amount
SELECT COALESCE(SUM(wager), 0)
INTO v_primary_entry_amount
FROM (
SELECT wager
FROM public.h2h_pool_entry
WHERE pool_id = NEW.pool_id
AND primary_outcome = TRUE
FOR SHARE
) AS subquery;
-- set alt entry count
SELECT COALESCE(SUM(wager), 0)
INTO v_alt_entry_amount
FROM (
SELECT wager
FROM public.h2h_pool_entry
WHERE pool_id = NEW.pool_id
AND primary_outcome = FALSE
FOR SHARE
) AS subquery;
v_total_pool_amount := v_primary_entry_amount + v_alt_entry_amount;
PERFORM pg_advisory_xact_lock(NEW.pool_id);
v_primary_multiplier := (v_total_pool_amount - v_commission * v_total_pool_amount) / NULLIF(v_primary_entry_amount, 0);
v_alt_multiplier := (v_total_pool_amount - v_commission * v_total_pool_amount) / NULLIF(v_alt_entry_amount, 0);
UPDATE public.h2h_pool
SET primary_entry_amount = v_primary_entry_amount, alt_entry_amount = v_alt_entry_amount, primary_multiplier = COALESCE(v_primary_multiplier, 0.00), alt_multiplier = COALESCE(v_alt_multiplier, 0.00), entrant_count = v_entrant_count
WHERE id = NEW.pool_id;
PERFORM pg_advisory_unlock(NEW.pool_id);
RETURN NEW;
ELSIF TG_OP = 'DELETE' THEN
SELECT COUNT(*)
INTO v_entrant_count
FROM (
SELECT 1
FROM public.h2h_pool_entry
WHERE pool_id = OLD.pool_id
FOR SHARE
) AS subquery;
-- set commission
SELECT commission
INTO v_commission
FROM public.h2h_pool
WHERE id = OLD.pool_id;
-- set primary entry amount
SELECT COALESCE(SUM(wager), 0)
INTO v_primary_entry_amount
FROM (
SELECT wager
FROM public.h2h_pool_entry
WHERE pool_id = OLD.pool_id
AND primary_outcome = TRUE
FOR SHARE
) AS subquery;
-- set alt entry count
SELECT COALESCE(SUM(wager), 0)
INTO v_alt_entry_amount
FROM (
SELECT wager
FROM public.h2h_pool_entry
WHERE pool_id = OLD.pool_id
AND primary_outcome = FALSE
FOR SHARE
) AS subquery;
v_total_pool_amount := v_primary_entry_amount + v_alt_entry_amount;
PERFORM pg_advisory_xact_lock(OLD.pool_id);
v_primary_multiplier := (v_total_pool_amount - v_commission * v_total_pool_amount) / NULLIF(v_primary_entry_amount, 0);
v_alt_multiplier := (v_total_pool_amount - v_commission * v_total_pool_amount) / NULLIF(v_alt_entry_amount, 0);
UPDATE public.h2h_pool
SET primary_entry_amount = v_primary_entry_amount, alt_entry_amount = v_alt_entry_amount, primary_multiplier = COALESCE(v_primary_multiplier, 0.00), alt_multiplier = COALESCE(v_alt_multiplier, 0.00), entrant_count = v_entrant_count
WHERE id = OLD.pool_id;
PERFORM pg_advisory_unlock(OLD.pool_id);
RETURN OLD;
END IF;
END;
$$
LANGUAGE plpgsql;
DROP TRIGGER IF EXISTS h2h_pool_entry_matrix ON public.h2h_pool_entry;
CREATE TRIGGER h2h_pool_entry_matrix
AFTER INSERT OR DELETE ON public.h2h_pool_entry
FOR EACH ROW
EXECUTE FUNCTION h2h_run_pool_entry_matrix();
字符串
下面是执行插入的操作:
async ({ request, locals: { supabase, getSession } }) => {
const {
user: { id: userId }
} = await getSession();
const formData = await request.formData();
const poolId = formData.get('poolId');
const wager = formData.get('wager');
const primaryOutcome = formData.get('primaryOutcome');
const { data: entryData, error: entryErr } = await supabase.from('h2h_pool_entry').insert({
/* required fields */
});
}
型
1条答案
按热度按时间wfveoks01#
尝试在查询中使用聚合函数,而不是单独的子查询。这样,查询将考虑事务中的所有相关行。例如,在
字符串