PostgreSQL. Materialized views may not be defined using bound parameters

Question

I'm trying to create materialized views inside stored function, which has date range as inbound parameters.

    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;

Function created with no issues, though when I run it:

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

it is failing with error

ERROR: materialized views may not be defined using bound parameters

So, I'm wondering if there's any possible variant to create materialized view with parameters?

Vao Tsun · Accepted Answer · 2017-05-10 07:10:19Z

3

try?:

    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;

answered May 10, 2017 at 7:10

Vao Tsun

52.5k13 gold badges114 silver badges149 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user7989901 Over a year ago

Magnificent! Worked fine. Thank you

Collectives™ on Stack Overflow

PostgreSQL. Materialized views may not be defined using bound parameters

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related