ERROR: CREATE MATERIALIZED VIEW ... WITH DATA cannot be executed from a function

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.

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.

StackExchange Database Administrators Q#324351, answer score: 5