sql - PostgreSQL。不能使用绑定(bind)参数定义物化 View

Question

我正在尝试在具有日期的存储函数中创建物化 View 范围作为入站参数。

    CREATE OR REPLACE FUNCTION public.create_view_for_reporting(
prev_date timestamp without time zone,
curr_date timestamp without time zone)
RETURNS void AS
$BODY$
BEGIN
    DROP MATERIALIZED VIEW public.messages_prev_day;    
    CREATE MATERIALIZED VIEW public.messages_prev_day AS 
    SELECT * FROM messages
    WHERE messages.date >= prev_date AND messages.date < curr_date
    WITH NO DATA;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;

函数创建没有问题，但当我运行它时:

select * from public.create_view_for_reporting ('2017-05-08','2017-05-09')

因为错误而失败

ERROR: materialized views may not be defined using bound parameters

所以，我想知道是否有任何可能的变体来创建带参数的物化 View ？

Answer 1

试试？

    CREATE OR REPLACE FUNCTION public.create_view_for_reporting(
prev_date timestamp without time zone,
curr_date timestamp without time zone)
RETURNS void AS
$BODY$
BEGIN
    DROP MATERIALIZED VIEW public.messages_prev_day;    
    execute format('CREATE MATERIALIZED VIEW public.messages_prev_day AS 
    SELECT * FROM messages
    WHERE messages.date >= %L AND messages.date < %L
    WITH NO DATA',prev_date,curr_date);
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;