Get count of all materialized views

Question

I am trying to get a count from all materialized views in my database. This is what I have so far, but it is not returning the count -

DO
$$
DECLARE
    rec record;
    my_pk_new integer;
BEGIN
    FOR rec IN 
        SELECT matviewname
        FROM pg_matviews  
        limit 2
    LOOP
    EXECUTE ' SELECT count(*) from' || rec.matviewname
    INTO my_pk_new;
    END LOOP;
    RAISE NOTICE 'Calling (%)', my_pk_new;

END;
$$
LANGUAGE plpgsql;

This is what I would expect to see -

Matview A    2432
Matview B    453984

You can use the same strategy as here: https://stackoverflow.com/a/38684225/330315 — , Aug 30 '19 at 16:54

score 1 · Accepted Answer · answered Aug 30 '19 at 15:46

1

1) After from a space is missing. So you are executing the query

SELECT count(*) frommy_view

instead of

SELECT count(*) from my_view

So there is an error. On my system a default int value returned (1)

2) Your RAISE NOTICE is outside the loop. So you are noticing only the very last query result. Put this into the loop body and it works.

DO $$
DECLARE
    rec record;
    my_pk_new integer;
BEGIN
    FOR rec IN 
        SELECT matviewname
        FROM pg_matviews  
        limit 2
    LOOP
        EXECUTE 'SELECT count(*) FROM ' || rec.matviewname
        INTO my_pk_new;

        RAISE NOTICE 'Calling (%)', my_pk_new;   
    END LOOP;       
END;
$$
LANGUAGE plpgsql;

answered Aug 30 '19 at 15:46

S-Man

22,521
7
40
63

Thank you, but I am still getting just the last value. – Doe Aug 30 '19 at 15:50
Are you sure, that you moved the RAISE NOTICE into the LOOP? – S-Man Aug 30 '19 at 15:52
Because I don't know any fiddle engine which supports "Raise Notice", I converted your anonymous function into a normal function. But it does the same: https://dbfiddle.uk/?rdbms=postgres_11&fiddle=44213c55f9d554718a2c2833a81c0d7b – S-Man Aug 30 '19 at 16:00
How do I select the result into a table? I would much rather do that instead of raising a notice. – Doe Aug 30 '19 at 16:00
see the fiddle: https://dbfiddle.uk/?rdbms=postgres_11&fiddle=44213c55f9d554718a2c2833a81c0d7b – S-Man Aug 30 '19 at 16:01
How do I get the matviewname returned as well? Right now it is only giving me the counts and I don't know for what mat view they are. – Doe Aug 30 '19 at 16:18
changed the fiddle https://dbfiddle.uk/?rdbms=postgres_11&fiddle=62b77565ceab1e8c01c3d65cc3823098 – S-Man Aug 30 '19 at 16:41
Your new fiddle is giving me this error - `set-valued function called in context that cannot accept a set. ` – Doe Aug 30 '19 at 18:56
The fiddle itself? Or your code? The fiddle works for me without any problem. Please try to reproduce it with a fiddle as well – S-Man Aug 30 '19 at 22:06

Get count of all materialized views

1 Answers1