Subject: Re: problem with query

From: Laurenz Albe <[email-masked]>
Date: Tue, 21 May 2024 13:14:01 +0200

On Tue, 2024-05-21 at 12:49 +0200, Sašo Gantar wrote:
> thanks for the info, but is there any solution, given that it's system tables?

We still don't know the query.

Yours,
Laurenz Albe

---

From: David Rowley <[email-masked]>
Date: Tue, 21 May 2024 23:16:55 +1200

> We still don't know the query.

hmm, it was posted on this thread:
https://postgr.es/m/CAGB0_6600w5C=[email-masked]

David

---

From: Sašo Gantar <[email-masked]>
Date: Tue, 21 May 2024 13:17:35 +0200

sorry...

SELECT COALESCE(Json_agg(Row_to_json(info)), '[]' :: JSON) AS TABLES
FROM
  (WITH partitions AS
     (SELECT array
        (WITH partitioned_tables AS
           (SELECT array
              (SELECT oid
               FROM pg_class
               WHERE relkind = 'p') AS parent_tables) SELECT child.relname
AS PARTITION
         FROM partitioned_tables, pg_inherits
         JOIN pg_class child ON pg_inherits.inhrelid = child.oid
         JOIN pg_namespace nmsp_child ON nmsp_child.oid = child.relnamespace
         WHERE
         ((nmsp_child.nspname='servicedesk'))
           AND pg_inherits.inhparent = ANY
(partitioned_tables.parent_tables) ) AS NAMES) SELECT pgn.nspname AS
table_schema,

                     pgc.relname AS TABLE_NAME,

                     CASE

                         WHEN pgc.relkind = 'r' THEN 'TABLE'

                         WHEN pgc.relkind = 'f' THEN 'FOREIGN TABLE'

                         WHEN pgc.relkind = 'v' THEN 'VIEW'

                         WHEN pgc.relkind = 'm' THEN 'MATERIALIZED VIEW'

                         WHEN pgc.relkind = 'p' THEN 'PARTITIONED TABLE'

                     END AS table_type,

                     obj_description(pgc.oid) AS COMMENT,

                     COALESCE(json_agg(DISTINCT row_to_json(isc) :: JSONB
|| jsonb_build_object('comment', col_description(pga.attrelid,
pga.attnum))) filter (

       WHERE isc.column_name IS NOT NULL), '[]' :: JSON) AS columns,

                     COALESCE(json_agg(DISTINCT row_to_json(ist) :: JSONB
|| jsonb_build_object('comment', obj_description(pgt.oid))) filter (

                                                                  WHERE
ist.trigger_name IS NOT NULL), '[]' :: JSON) AS TRIGGERS,

                     row_to_json(isv) AS view_info
   FROM partitions,
        pg_class AS pgc
   INNER JOIN pg_namespace AS pgn ON pgc.relnamespace = pgn.oid /* columns
*/ /* This is a simplified version of how information_schema.columns was
  ** implemented in postgres 9.5, but modified to support materialized
** views.     */
   LEFT OUTER JOIN pg_attribute AS pga ON pga.attrelid = pgc.oid
   LEFT OUTER JOIN
     (SELECT nc.nspname AS table_schema,
             c.relname AS TABLE_NAME,
             a.attname AS COLUMN_NAME,
             a.attnum AS ordinal_position,
             pg_get_expr(ad.adbin, ad.adrelid) AS column_default,
             CASE
                 WHEN a.attnotnull
                      OR (t.typtype = 'd'
                          AND t.typnotnull) THEN 'NO'
                 ELSE 'YES'
             END AS is_nullable,
             CASE
                 WHEN t.typtype = 'd' THEN CASE
                                               WHEN bt.typelem <> 0
                                                    AND bt.typlen = -1 THEN
'ARRAY'
                                               WHEN nbt.nspname =
'pg_catalog' THEN format_type(t.typbasetype, NULL)
                                               ELSE 'USER-DEFINED'
                                           END
                 ELSE CASE
                          WHEN t.typelem <> 0
                               AND t.typlen = -1 THEN 'ARRAY'
                          WHEN nt.nspname = 'pg_catalog' THEN
format_type(a.atttypid, NULL)
                          ELSE 'USER-DEFINED'
                      END
             END AS data_type,
             coalesce(bt.typname, t.typname) AS data_type_name,
             CASE
                 WHEN a.attidentity = 'd' THEN TRUE
                 WHEN a.attidentity = 'a' THEN TRUE
                 ELSE FALSE
             END AS is_identity
      FROM (pg_attribute a
            LEFT JOIN pg_attrdef ad ON attrelid = adrelid
            AND attnum = adnum)
      JOIN (pg_class c
            JOIN pg_namespace nc ON (c.relnamespace = nc.oid)) ON
a.attrelid = c.oid
      JOIN (pg_type t
            JOIN pg_namespace nt ON (t.typnamespace = nt.oid)) ON
a.atttypid = t.oid
      LEFT JOIN (pg_type bt
                 JOIN pg_namespace nbt ON (bt.typnamespace = nbt.oid)) ON
(t.typtype = 'd'

 AND t.typbasetype = bt.oid)
      LEFT JOIN (pg_collation co
                 JOIN pg_namespace nco ON (co.collnamespace = nco.oid)) ON
a.attcollation = co.oid
      AND (nco.nspname,
           co.collname) <> ('pg_catalog',
                            'default')
      WHERE (NOT pg_is_other_temp_schema(nc.oid))
        AND a.attnum > 0
        AND NOT a.attisdropped
        AND c.relkind in ('r',
                          'v',
                          'm',
                          'f',
                          'p')
        AND (pg_has_role(c.relowner, 'USAGE')
             OR has_column_privilege(c.oid, a.attnum, 'SELECT, INSERT,
UPDATE, REFERENCES')) ) AS isc ON isc.table_schema = pgn.nspname
   AND isc.table_name = pgc.relname
   AND isc.column_name = pga.attname /* triggers */
   LEFT OUTER JOIN pg_trigger AS pgt ON pgt.tgrelid = pgc.oid
   LEFT OUTER JOIN information_schema.triggers AS ist ON
ist.event_object_schema = pgn.nspname
   AND ist.event_object_table = pgc.relname
   AND ist.trigger_name = pgt.tgname /* This is a simplified version of how
information_schema.views was     ** implemented in postgres 9.5, but
modified to support materialized     ** views.     */
   LEFT OUTER JOIN
     (SELECT nc.nspname AS table_schema,
             c.relname AS TABLE_NAME,
             CASE
                 WHEN pg_has_role(c.relowner, 'USAGE') THEN
pg_get_viewdef(c.oid)
                 ELSE NULL
             END AS view_definition,
             CASE
                 WHEN pg_relation_is_updatable(c.oid, FALSE) & 20 = 20 THEN
'YES'
                 ELSE 'NO'
             END AS is_updatable,
             CASE
                 WHEN pg_relation_is_updatable(c.oid, FALSE) & 8 = 8 THEN
'YES'
                 ELSE 'NO'
             END AS is_insertable_into,
             CASE
                 WHEN EXISTS
                        (SELECT 1
                         FROM pg_trigger
                         WHERE tgrelid = c.oid
                           AND tgtype & 81 = 81) THEN 'YES'
                 ELSE 'NO'
             END AS is_trigger_updatable,
             CASE
                 WHEN EXISTS
                        (SELECT 1
                         FROM pg_trigger
                         WHERE tgrelid = c.oid
                           AND tgtype & 73 = 73) THEN 'YES'
                 ELSE 'NO'
             END AS is_trigger_deletable,
             CASE
                 WHEN EXISTS
                        (SELECT 1
                         FROM pg_trigger
                         WHERE tgrelid = c.oid
                           AND tgtype & 69 = 69) THEN 'YES'
                 ELSE 'NO'
             END AS is_trigger_insertable_into
      FROM pg_namespace nc,
           pg_class c
      WHERE c.relnamespace = nc.oid
        AND c.relkind in ('v',
                          'm')
        AND (NOT pg_is_other_temp_schema(nc.oid))
        AND (pg_has_role(c.relowner, 'USAGE')
             OR has_table_privilege(c.oid, 'SELECT, INSERT, UPDATE, DELETE,
TRUNCATE, REFERENCES, TRIGGER')
             OR has_any_column_privilege(c.oid, 'SELECT, INSERT, UPDATE,
REFERENCES')) ) AS isv ON isv.table_schema = pgn.nspname
   AND isv.table_name = pgc.relname
   WHERE
   pgc.relkind IN ('r',
                         'v',
                         'f',
                         'm',
                         'p')

     AND ((pgn.nspname='servicedesk'))

   GROUP BY pgc.oid,
            pgn.nspname,
            pgc.relname,
            table_type,
            isv.*) AS info;