public inbox for [email protected]  

help / color / mirror / Atom feed

From: Olleg <[email protected]>
To: [email protected]
Subject: Dependencies on the system view
Date: Fri, 20 Sep 2024 14:32:14 +0300
Message-ID: <[email protected]> (raw)

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 will here be a trouble 
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

view thread (2+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected]
  Subject: Re: Dependencies on the system view
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox