Postgres: see query used to create materialized view?

Question

I'm working in Postgres 9.4. Is there any way I can see the query that was used to create a materialized view?

Doing:

# \d my_view;

just shows me the column names and indexes of the view, not the command used to create it. And I can't see anything in the docs about this.

Thanks for your help.

select pg_get_viewdef('my_view') ? (Haven't tested it on a matview) — Craig Ringer
– Craig Ringer, Commented Mar 27, 2015 at 12:02
Possible duplicate of How do I discover the underlying query of a materialized view I created? — Bergi
– Bergi, Commented Sep 26, 2019 at 19:08
At least in recent versions, you can do \d+ my_view although it may abbreviate it. I haven't tried in 9.4. — combinatorist
– combinatorist, Commented May 7, 2020 at 19:43

user330315 · Accepted Answer · 2015-03-27 13:31:06Z

6

This is stored in pg_matviews:

select definition
from pg_matview
where matviewname = 'my_view'
and schemaname = 'public';

You can also use pg_get_viewdef() as Craig has suggested:

select pg_get_viewdef('public.my_view', true);

answered Mar 27, 2015 at 13:31

user330315

Sign up to request clarification or add additional context in comments.

1 Answer 1