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.

Héctor
  • 307
  • 5
  • 14

1 Answers1

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.

mustaccio
  • 28,207
  • 24
  • 60
  • 76