Emmanuel SARACCO <esaracco@noos.fr> writes:
> 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"));
If you're using a reasonably recent PG release, there's no need to join
against pg_rewrite. Just look for pg_class entries with relkind = 'v'.
> 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!
One must wonder why you create views at all, if you don't intend them
to stick around for long. Why not just write out the SELECTs in full?
(If you think there's some performance benefit to using a view, you are
quite mistaken.)
regards, tom lane