retriving views name - Mailing list pgsql-general

hi,

I must retrieve views names in a very short time. what can I use.
for the moment, I use:

SELECT c.relname FROM pg_class c, pg_rewrite r WHERE (((c.relname !~
'^pg_'::text) AND (r.ev_class = c.oid)) AND (r.ev_type = '1'::"char"));

is there a more performant way to do this? (I have more than 50.000
views in my database (those views are generated on the fly to optimize a
search engine and keep results in memory)). I am running a sort of
"garbage collector" via cron/psql -c every minutes to destroy views
older than 10 minutes and I must do this very rapidly!

I there a way to know the creation date of a postgresql object (for the
moment, I concatenate hours/minutes in the view name and I use a
EXTRACT(MINUTE FROM (current_time -            TIME(substr(mviews.relname,
length(mviews.relname) - 3, 2) || ':' ||    substr(mviews.relname,
length(mviews.relname) - 1, 2))) in a FOR LOOP with a EXECUTE 'DROP
VIEW' || mviews.relname to remove each view corresponding to my criterium)?

here is my plpgsql function:

---------------------
DECLARE
    var_duree ALIAS FOR $1;
    mviews RECORD;

BEGIN
    FOR mviews IN SELECT relname FROM view_get_vstviews LOOP
        IF (
            EXTRACT(
                MINUTE FROM (
                    current_time -
                    TIME(substr(mviews.relname, length(mviews.relname) - 3, 2) || ':' ||
                    substr(mviews.relname, length(mviews.relname) - 1, 2))
                )
            ) >= var_duree
        ) THEN
            EXECUTE 'DROP VIEW "' || mviews.relname ||'"';
        END IF;
    END LOOP;
    RETURN 0;
END;
---------------------

if I do the same function in C language, will it be faster?

thanks


--
Emmanuel SARACCO
Email: esaracco@noos.fr