postgresql - 如何在 postgres 函数中创建临时表

Question

这里的问题是，当我转到 UPDATE 代码块时，我无法再访问子查询中的数据

我尝试了多种创建临时表的变体，并从 deleted_rows 中选择进入而不是子查询作为 WITH 语句的一部分，但它不喜欢我尝试的任何东西，尤其不喜欢我尝试创建一个表在初始 with 子句之后

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$;

如果我删除更新语句，它会完美地工作，并且我可能会使用触发器来进行更新，但如果我离我所拥有的解决方案不远，我宁愿不这样做

Answer 1

知道了，我必须在 WITH 上方创建表，然后在第一次插入之前填充它，然后将临时表用于以下两个代码块，如

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$;