gistfile1.sql

CREATE OR REPLACE FUNCTION t_visits_death() RETURNS "trigger" AS $_$
DECLARE
  r record;
BEGIN
  SELECT INTO r
    MAX(t_stamp) AS last_action,
    COUNT(pageview_id) > 1 AS interested,
    MAX(CASE WHEN p.type = 1 THEN 1 ELSE 0 END) = 1 AS has_product,
    EXISTS (SELECT 1 FROM public.carts c WHERE c.visit_id = v.visit_id AND c.total_revenue > 0) AS has_cart,
    EXISTS (SELECT 1 FROM public.orders o WHERE o.visit_id = v.visit_id) AS has_order
  FROM
    public.pageviews p
  WHERE
    visit_id = NEW.visit_id;

  IF FOUND THEN
    NEW.duration = GREATEST(r.last_action - NEW.t_stamp, '0s'::interval);
    IF r.has_order THEN
      NEW.visitor_funnel_segment := 'E';
    ELSE 
      -- Do something
    END IF;
  END IF;

  RETURN NEW;
END;
$_$ LANGUAGE plpgsql STRICT VOLATILE;