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.

7
  • Sounds like an X-Y Problem™. Why do you think you need to do that from a function?
    – mustaccio
    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.
    – Héctor
    Commented Mar 3, 2023 at 19:44
  • 1
    Perhaps you could try WITH NO DATA and refresh them separately.
    – mustaccio
    Commented Mar 3, 2023 at 19:58
  • Why not use a procedure?
    – user1822
    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.
    – Héctor
    Commented Mar 5, 2023 at 11:21

1 Answer 1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.