Materialized view not displayed at all within Views tree

[expired user #10283]'s profile image [expired user #10283] posted 7 years ago in General Permalink

Hi:

I have installed HeidiSQL recently, and tried to interact with it, so far it's simple, intuitive and easy to use.

However one glitch that I consider a big barrier to me, it could't list my Materialized views specifically. the database I've created on postgreSQL PgAdminIII editor.

at the beginning I thought it's a grant permission issue, but after investigation it seems not the issue.

Even though I tried to create a net new materialized view using HeidiSQL, it'll not show it in the Views tree, so I have to open PgAdminIII to editd or manipulte the materialized view.

** I found a post on this fourm who has the same issue before but without an answer so far.

Please advise.

Thanks

ansgar's profile image ansgar posted 7 years ago Permalink

HeidiSQL gets tables and views from information_schema.tables, with the column table_type for distincting between the two types.

I have no clue what materialized views are. Probably they are also listed in is.tables?

[expired user #10283]'s profile image [expired user #10283] posted 7 years ago Permalink

Oh I see, so you're referring to the ANSI (Information Schema) of PostgreSQL. Are materialized views considered as tables or can be listed within the info schema .tables? No, they behave like normal views, but are stored physically on the disk, for performance considerations.

So if you query against the PostgreSQL (pg_catalog), it will list them all, using the query:

SELECT c.relname, c.relkind
FROM pg_catalog.pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind = 'm'

So if you can add this functionality in HeidiSQL. It'll be a great feature.

arturm's profile image arturm posted 7 years ago Permalink

I have the same problem. Please consider to add this feature. Since pgAdmin 4 has arrived I strongly prefer Heidi :-)

Please login to leave a reply, or register at first.