CODEWITHSUNDEEP
postgresql
Greg Belyea
Greg Belyea

Reputation: 878

How to create temporary tables inside a postgres function

The problem here is that by the time i go to the UPDATE block of code, i no longer have access to the data that was in subquery

I tried many variations of creating a temporary table and a select into from deleted_rows instead of the subquery AS part of the WITH statement but it did not like anything i tried, and it especially didn't like me trying to create a table after the initial with clause

CREATE OR REPLACE FUNCTION public.aggregate_userviews(
    )
    RETURNS text
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE 
AS $BODY$BEGIN

WITH deleted_rows AS (
    DELETE FROM user_details_views 
    WHERE ts < (timezone('UTC', now() - interval '5 minutes')) RETURNING *
), subquery AS (SELECT DISTINCT username, DATE(ts) as day_of_month, COUNT(id) AS user_views
        FROM deleted_rows
        GROUP BY username, day_of_month
        ORDER BY day_of_month ASC)

INSERT INTO analytics_summary ( username, day_of_month, user_views)
    SELECT username, day_of_month, user_views
    FROM subquery           
ON CONFLICT (username ,day_of_month)
DO UPDATE SET user_views = analytics_summary.user_views + excluded.user_views;

UPDATE user_details u
    SET view_count = u.view_count + subquery.user_views
    FROM subquery
    WHERE u.username=subquery.username;

RETURN NULL;
END;$BODY$;

If i remove the update statement it works perfectly, and i could probably use a trigger to do the update but i would rather not if i am not far off from a solution with what i have

Upvotes: 1

Views: 105

Answers (1)

Greg Belyea
Greg Belyea

Reputation: 878

Got it, i had to create the table above the WITH and then fill it before the first insert and then use the temp table for the following two blocks of code like 

CREATE OR REPLACE FUNCTION public.aggregate_userviews(
    )
    RETURNS text
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE 
AS $BODY$BEGIN

create temporary table temp_userviews_table (username varchar, day_of_month date, user_views int);

WITH deleted_rows AS (
    DELETE FROM user_details_views 
    WHERE ts < (timezone('UTC', now() - interval '5 minutes')) RETURNING *
), subquery AS (SELECT DISTINCT username, DATE(ts) as day_of_month, COUNT(id) AS user_views
        FROM deleted_rows
        GROUP BY username, day_of_month
        ORDER BY day_of_month ASC)

INSERT INTO temp_userviews_table (username, day_of_month, user_views)
    SELECT username, day_of_month, user_views
    FROM subquery;

INSERT INTO analytics_summary ( username, day_of_month, user_views)
    SELECT username, day_of_month, user_views
    FROM temp_userviews_table           
ON CONFLICT (username ,day_of_month)
DO UPDATE SET user_views = analytics_summary.user_views + excluded.user_views;

UPDATE user_details u
    SET view_count = u.view_count + temp_userviews_table.user_views
    FROM temp_userviews_table
    WHERE u.username=temp_userviews_table.username;

drop table temp_userviews_table;
RETURN NULL;
END;$BODY$;

Upvotes: 2

Related Questions