From: | Olleg <splarv(at)ya(dot)ru> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Dependencies on the system view |
Date: | 2024-09-20 11:32:48 |
Message-ID: | c8ec7a00-c5c6-48e2-ae25-14a02278d52b@ya.ru |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Hi all.
One of our programmer created a view based on the system view. I tried
to explain him, that he created a dependency from the ordinary database
to the system object and this is a bad idea. But he is not smart enough.
So I need a guru opinion. Is this permissible or here will be a troubles
with, for instance, pg_upgrade?
CREATE OR REPLACE VIEW public.all_tables
AS SELECT n.nspname AS schemaname,
c.relname AS tablename,
pg_get_userbyid(c.relowner) AS tableowner,
c.reltuples AS num_rows,
c.relkind,
CASE c.relkind
WHEN 'f'::"char" THEN 'Foreign table'::text
WHEN 'r'::"char" THEN 'Relation'::text
WHEN 'i'::"char" THEN 'Index'::text
WHEN 'S'::"char" THEN 'Sequence'::text
WHEN 't'::"char" THEN 'TOAST'::text
WHEN 'v'::"char" THEN 'View'::text
WHEN 'm'::"char" THEN 'Materialized view'::text
WHEN 'c'::"char" THEN 'Composite type'::text
WHEN 'p'::"char" THEN 'Partitioned table'::text
WHEN 'I'::"char" THEN 'partitioned Index'::text
ELSE NULL::text
END AS rel_type,
c.relpersistence,
CASE c.relpersistence
WHEN 'p'::"char" THEN 'permanent'::text
WHEN 'u'::"char" THEN 'unlogged'::text
WHEN 't'::"char" THEN 'temporary'::text
WHEN 'c'::"char" THEN 'constant'::text
ELSE NULL::text
END AS persistence,
t.spcname AS tablespace,
c.relhasindex AS hasindexes,
c.relhasrules AS hasrules,
c.relhastriggers AS hastriggers
FROM pg_class c
LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
LEFT JOIN pg_tablespace t ON t.oid = c.reltablespace;
--
Olleg
From | Date | Subject | |
---|---|---|---|
Next Message | Laurenz Albe | 2024-09-20 12:33:41 | Re: pg_locks in production |
Previous Message | Olleg | 2024-09-20 11:32:14 | Dependencies on the system view |