m1z54J09

1DROP FUNCTION IF EXISTS remove_all();
2
3DROP EXTENSION IF EXISTS "uuid-ossp";
4
5CREATE FUNCTION remove_all() RETURNS void AS $$
6DECLARE
7    rec RECORD;
8    cmd text;
9BEGIN
10    cmd := '';
11
12    FOR rec IN SELECT
13            'DROP SEQUENCE ' || quote_ident(n.nspname) || '.'
14                || quote_ident(c.relname) || ' CASCADE;' AS name
15        FROM
16            pg_catalog.pg_class AS c
17        LEFT JOIN
18            pg_catalog.pg_namespace AS n
19        ON
20            n.oid = c.relnamespace
21        WHERE
22            relkind = 'S' AND
23            n.nspname NOT IN ('pg_catalog', 'pg_toast') AND
24            pg_catalog.pg_table_is_visible(c.oid)
25    LOOP
26        cmd := cmd || rec.name;
27    END LOOP;
28
29    FOR rec IN SELECT
30            'DROP TABLE ' || quote_ident(n.nspname) || '.'
31                || quote_ident(c.relname) || ' CASCADE;' AS name
32        FROM
33            pg_catalog.pg_class AS c
34        LEFT JOIN
35            pg_catalog.pg_namespace AS n
36        ON
37            n.oid = c.relnamespace WHERE relkind = 'r' AND
38            n.nspname NOT IN ('pg_catalog', 'pg_toast') AND
39            pg_catalog.pg_table_is_visible(c.oid)
40    LOOP
41        cmd := cmd || rec.name;
42    END LOOP;
43
44    FOR rec IN SELECT
45            'DROP FUNCTION ' || quote_ident(ns.nspname) || '.'
46                || quote_ident(proname) || '(' || oidvectortypes(proargtypes)
47                || ');' AS name
48        FROM
49            pg_proc
50        INNER JOIN
51            pg_namespace ns
52        ON
53            (pg_proc.pronamespace = ns.oid)
54        WHERE
55            ns.nspname =
56            'public'
57        ORDER BY
58            proname
59    LOOP
60        cmd := cmd || rec.name;
61    END LOOP;
62
63    EXECUTE cmd;
64    RETURN;
65END;
66$$ LANGUAGE plpgsql;
67
68SELECT remove_all();