Re: Materializing a view by hand

Robert James <srobertjames(at)gmail(dot)com> wrote:

> I have a view which is very slow to computer, but doesn't change often.
>
> I'd like to materialize it. I thought I'd do a simple poor man's
> materialize by:
>
> 1) ALTER VIEW myview RENAME to _myview
> 2) SELECT * INTO myview FROM _myview
>
> The only problem is that all my other views, which are dependent on
> myview, automatically rename to _myview.  That would normally be very
> helpful but is exactly the opposite of what I want!
>
> Is there a work around?

The best I can think of would be to use pg_dump to do a of that one
view (-t myview), and run the GRANT and REVOKE statements from that
after the rename and materialization.

> I'm running Postgres 8.3 - upgrading is a possibility but difficult.

The upcoming 9.3 release will have minimal support for materialized
views, but there is no feature to transform a view into a
materialized view in that release, or on the roadmap.  I wonder
whether this comes up often enough to consider adding such a
feature to a future release.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company