3

I need to execute CREATE MATERIALIZED VIEW ... WITH DATA from a function/procedure but I'm getting ERROR: CREATE MATERIALIZED VIEW ... WITH DATA cannot be executed from a function.

There is any workaround for this apart from making a function return the creation query text and copy paste it to execute it?

Seems really silly but I suppose there is a good reason behind it... Anyway, any workaround for this limitation?

I'm running postgresql 15.

Improve this question
7
  • Sounds like an X-Y Problem™. Why do you think you need to do that from a function? Commented Mar 3, 2023 at 19:36
  • Maybe... I want to automate the creation of some materialized views. The source tables have hundreds of columns, each table needs 6 materialized views and each column needs a different aggregation function (AVG or MODE, MIN or BOOL_AND, MAX or BOOL_OR...) on the materialized view. The aggregation functions needed for each column are determined by info stored on other tables. We are talking about some thousands of lines that I would really like to avoid to write by hand, so I was creating a plpgsql function/procedure to automate this and future tables. Commented Mar 3, 2023 at 19:44
  • 1
    Perhaps you could try WITH NO DATA and refresh them separately. Commented Mar 3, 2023 at 19:58
  • Why not use a procedure? Commented Mar 4, 2023 at 14:22
  • @a_horse_with_no_name A procedure returns the same error. WITH NO DATA it's a workaround that does the job even if you have to populate it manually later. Commented Mar 5, 2023 at 11:21

1 Answer 1

Reset to default
5

This is to ensure atomicity and consistency of operations. As stated in the source code comment, some statements

must not run inside a transaction block, typically because they have non-rollback-able side effects or do internal commits. [...] issue an error if we appear to be running inside a user-defined function (which could issue more commands and possibly cause a failure after the statement completes).

Since CREATE MATERIALIZED VIEW ... WITH DATA does at least two things: creates a table, then populates it with data, it's possible that it has an internal commit, hence the error.

Improve this answer

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.