How I can Delete All User Defined Views From PostgreSQL using a Query? Like we can delete All functions using query :
SELECT 'DROP FUNCTION ' || ns.nspname || '.' || proname
|| '(' || oidvectortypes(proargtypes) || ');'
FROM pg_proc INNER JOIN pg_namespace ns ON (pg_proc.pronamespace = ns.oid)
WHERE ns.nspname = 'my_messed_up_schema' order by proname;
Script for deleting all views in a certain schema:
SELECT 'DROP VIEW ' || t.oid::regclass || ';' -- CASCADE?
FROM pg_class t
JOIN pg_namespace n ON n.oid = t.relnamespace
WHERE t.relkind = 'v'
AND n.nspname = 'my_messed_up_schema -- select by schema(s)
ORDER BY 1;
The cast to regclass (t.oid::regclass) prevents SQLi, because otherwise illegal names are quoted automatically. You could also use quote_ident().
Your example is inherently unsafe.
Do it right away:
DO
$$
DECLARE
sql text;
BEGIN
SELECT INTO sql
string_agg('DROP VIEW ' || t.oid::regclass || ';', ' ') -- CASCADE?
FROM pg_class t
JOIN pg_namespace n ON n.oid = t.relnamespace
WHERE t.relkind = 'v'
AND n.nspname = 'my_messed_up_schema';
IF sql IS NOT NULL THEN
-- RAISE NOTICE '%', sql; -- to debug
EXECUTE sql;
ELSE
RAISE NOTICE 'No views found. Nothing dropped.';
END IF;
END
$$
DO requires PostgreSQL 9.0 or later.
The IF construct avoids an exception if no views are found.
If you have views referencing other views, you'll have to add the keyword CASCADE or drop views in their hierarchical order from top to bottom.
Always check what you are going to drop before you do it, or you might nuke yourself. If you are unsure, start a transaction, drop the bomb, check if all is good and then either commit or roll back.
BEGIN;
DO$$
...
$$;
-- check ..
ROLLBACK; -- if something wrong
COMMIT; -- else
Note that you cannot COMMIT or ROLLBACK inside the plpgsql block. Only outside.
Use table pg_class.
You need relkind = 'v'
