so, i'm trying create trigger receive values insert action, join tables , insert it's result table.
i don't know if query complex trigger, keeps raising error:
error: syntax error @ or near "create" line 2: create or replace function tg_portfolio() returns trigger the code below, if can me i'd glad.
create or replace function tg_portfolio() returns trigger $tg_portfolio$ begin lv_monthly_revenue ( select ptf1.enterprise_id, ptf1.monthly_revenue_current beta_portfolio ptf1 left join beta_portfolio ptf2 on (ptf1.enterprise_id = ptf2.enterprise_id , ptf1._id < ptf2._id) ptf2._id null ), lv_monthly_recurrence ( select ptf1.enterprise_id, ptf1.monthly_recurrence_current beta_portfolio ptf1 left join beta_portfolio ptf2 on (ptf1.enterprise_id = ptf2.enterprise_id , ptf1._id < ptf2._id) ptf2._id null ), lv_monthly_coupon ( select ptf1.enterprise_id, ptf1.monthly_coupon_current beta_portfolio ptf1 left join beta_portfolio ptf2 on (ptf1.enterprise_id = ptf2.enterprise_id , ptf1._id < ptf2._id) ptf2._id null ), lv_seats ( select ptf1.enterprise_id, ptf1.seats_current beta_portfolio ptf1 left join beta_portfolio ptf2 on (ptf1.enterprise_id = ptf2.enterprise_id , ptf1._id < ptf2._id) ptf2._id null ) insert beta_portfolio (enterprise_id, subscription_id, enterprise_lifeycle_id, payment_method, creatad_at, event_type, recurrence_frequency, plan_id, monthly_revenue, monthly_revenue_current, monthly_recurrence, monthly_recurrence_current, monthly_coupon, monthly_coupon_current, seats, seats_current , has_coupon, teams_corporate) values ( new.enterprise_id, new.subscription_id, new.id, new.payment_method, new.created_at, new.event_type, new.recurrence_frequency, new.plan_id, /*monthly_revenue*/ (select monthly_fee_brl plans id = new.new_plan_id), /*monthly_revenue_current*/ (select monthly_fee_brl plans id = new.new_plan_id) + case when (select monthly_revenue_current lv_monthly_revenue enterprise_id = new.enterprise_id) null 0 else (select monthly_revenue_current lv_monthly_revenue enterprise_id = new.enterprise_id) end, /*monthly_recurrence*/ (select case when new.frequency = 1 plans.monthly_fee_brl when new.frequency = 6 plans.semiannually_fee_brl when new.frequency = 12 plans.annually_fee_brl end plans id = new.new_plan_id), /*monthly_recurrence_current*/ (select case when new.frequency = 1 plans.monthly_fee_brl when new.frequency = 6 plans.semiannually_fee_brl when new.frequency = 12 plans.annually_fee_brl end plans id = new.new_plan_id) + case when (select monthly_recurrence_current lv_monthly_recurrence enterprise_id = new.enterprise_id) null 0 else (select monthly_recurrence_current lv_monthly_recurrence enterprise_id = new.enterprise_id) end, /*monthly_coupon*/ (select case when subscriptions.recurrence_frequency = 1 (plans.monthly_fee_brl * ((100 - (case when coupon_instances.percentage_of_discount null or receipts.original_amount - receipts.discounted_amount = 0 0 else coupon_instances.percentage_of_discount end ) )/100))::integer when subscriptions.recurrence_frequency = 6 (plans.semiannually_fee_brl * ((100 - (case when coupon_instances.percentage_of_discount null or receipts.original_amount - receipts.discounted_amount = 0 0 else coupon_instances.percentage_of_discount end ) )/100::float)/6)::integer when subscriptions.recurrence_frequency = 12 (plans.annually_fe8e_brl * ((100 - (case when coupon_instances.percentage_of_discount null or receipts.original_amount - receipts.discounted_amount = 0 0 else coupon_instances.percentage_of_discount end ) )/100::float)/12)::integer end subscriptions left join plans on plans.id = subscriptions.id left join coupon_instances on coupon_instances.subscription_id = subscriptions.id left join receipts on receipts.subscription_id = subscriptions.id subscriptions.id = new.new_subscription_id ), /*monthly_coupon_current*/ (select case when subscriptions.recurrence_frequency = 1 (plans.monthly_fee_brl * ((100 - (case when coupon_instances.percentage_of_discount null or receipts.original_amount - receipts.discounted_amount = 0 0 else coupon_instances.percentage_of_discount end ) )/100))::integer when subscriptions.recurrence_frequency = 6 (plans.semiannually_fee_brl * ((100 - (case when coupon_instances.percentage_of_discount null or receipts.original_amount - receipts.discounted_amount = 0 0 else coupon_instances.percentage_of_discount end ) )/100::float)/6)::integer when subscriptions.recurrence_frequency = 12 (plans.annually_fe8e_brl * ((100 - (case when coupon_instances.percentage_of_discount null or receipts.original_amount - receipts.discounted_amount = 0 0 else coupon_instances.percentage_of_discount end ) )/100::float)/12)::integer end subscriptions left join plans on plans.id = subscriptions.id left join coupon_instances on coupon_instances.subscription_id = subscriptions.id left join receipts on receipts.subscription_id = subscriptions.id subscriptions.id = new.new_subscription_id ) + case when (select monthly_coupon_current lv_monthly_coupon enterprise_id = new.enterprise_id) null 0 else (select monthly_coupon_current lv_monthly_coupon enterprise_id = new.enterprise_id) end, /*monthly_coupon_current*/ (select max_users plans id = new.new_plan_id), /*monthly_coupon_current*/ (select max_users plans id = new.new_plan_id) + case when (select seats_current lv_seats enterprise_id = new.enterprise_id) null 0 else (select seats_current lv_seats enterprise_id = new.enterprise_id) end, /*has_coupon*/ (select case when coupon_instances.subscription_id null false else true end coupon_instances subscription_id = new.new_subscription_id), /*teams_corp*/ select case when select (substring(new.new_subscription_id::text,1,1))::integer == 7 , select (substring(new.previous_subscription_id::text,1,1))::integer == 6 true else false end ); return null; end; $tg_portfolio$ language plpgsql;
Comments
Post a Comment