0

I am getting an error on this create function code in Postgresql. The error says it is happening around Line 2 at DELETE, but it happens at WITH if I remove that line so I think it is a problem with the format of my Creat Function

create or replace function retention_data(shopId integer) returns void as $$
    delete from retention where shop_id = shopId;

    WITH ret_grid_step1 as (
        select * from (

        SELECT 
            order_id as order_name,
            cust_name as cust_name,
            email as email,
            date(order_date) as created_at,
            count(*) as num_items_in_order,
            sum(total_price) as sales ,
            rank() over (partition BY order_id ORDER BY cust_name ASC) as rnk_shipping_name,
            rank() over (partition BY order_id ORDER BY email ASC) as rnk_email
        FROM orders 
         WHERE shop_id = shopId
         and order_date is not null and order_date > now()::date - 365 and order_date < now()::date + 1
         group by 1,2,3,4
        ) x
        where rnk_shipping_name = 1 and rnk_email = 1
    )
        insert into retention(shop_id, cust_name, email, last_purchase_dt, total_sales, num_orders, days_since_last_order)
            select
            shopId as shop_id,
            coalesce(b.cust_name,'null') as cust_name,
            a.email,
            a.last_purchase_dt,
            total_sales,
            num_orders,
            current_date - last_purchase_dt as days_since_last_order
        from (
            select
                email,
                max(created_at) as last_purchase_dt,
                count(*) as num_orders,
                sum(sales) as total_sales
            from ret_grid_step1
            group by 1
        ) as a
        left join (
            select
                email,
                cust_name,
                rank() over (partition BY email ORDER BY created_at DESC) as rnk
            from ret_grid_step1
            --where cust_name is not null
            group by 1,2,created_at
        ) as b
        on a.email = b.email
        where b.rnk = 1
        and a.email <> '';
$$ language plpgsql;
3
  • 5
    language plpgsql requires a begin ... end; but for your function language sql is sufficient. Commented Oct 10, 2016 at 5:46
  • 2
    Side note: If that 365 is supposed to be a year, try order_date > now()::date - interval '1 year' instead. Commented Oct 10, 2016 at 6:11
  • begin,end fixed it, thanks! I think I had it in there before but had the 'begin' after the delete Commented Oct 10, 2016 at 15:39

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.