Datadictionary OpenWave 3.1.25

View: vwadm_objectcount_generate

Schema

public

Owner

waveuser005

Descriptions

There is no description for view vwadm_objectcount_generate

Fields

Rules

Indices

There are no indices for table vwadm_objectcount_generate

Definition

CREATE VIEW public.vwadm_objectcount_generate (
   dvquery)
AS
SELECT (((((((((('insert into tbadm_objects(dvname, dvtype, dvversion, dvdata_type, dnmax_length) values ('::text
   || ''''::text) || upper(adm_objects_generate.dvname)) || ''', '''::text) || adm_objects_generate.dvtype) || ''', '''::text) || '1.6'::character varying(10)::text) || ''', '::text) || adm_objects_generate.dvdata_type::text) || ', '::text) || adm_objects_generate.dnmax_length::text) || ');'::text AS dvquery
FROM (
   SELECT pg_constraint.conname::text AS dvname,
           'CONSTRAINT_CHECK'::text AS dvtype,
           '02'::character varying(2) AS dvorder,
           'null'::character varying AS dvdata_type,
           'null'::character varying(10) AS dnmax_length
   FROM pg_constraint
   WHERE pg_constraint.contype = 'c'::"char" AND
       upper(pg_constraint.conname::text) ~~ 'CHK_%'::text
   GROUP BY pg_constraint.conname
   UNION
   SELECT pg_constraint.conname::text AS dvname,
           'CONSTRAINT_NOTNULL'::text AS dvtype,
           '04'::character varying(2) AS dvorder,
           'null'::character varying AS dvdata_type,
           'null'::character varying(10) AS dnmax_length
   FROM pg_constraint
   WHERE pg_constraint.contype = 'c'::"char" AND
       upper(pg_constraint.conname::text) ~~ 'CNN_%'::text
   GROUP BY pg_constraint.conname
   UNION
   SELECT (pg_class.relname::text || '.'::text) || pg_attribute.attname::text
       AS dvname,
           'DEFAULT'::text AS dvtype,
           '06'::character varying(2) AS dvorder,
           'null'::character varying AS dvdata_type,
           'null'::character varying(10) AS dnmax_length
   FROM pg_class,
           pg_attribute
   WHERE pg_class.oid = pg_attribute.attrelid AND
       upper(pg_class.relname::text) ~~ 'TB%'::text AND pg_class.relkind = 'r'::"char" AND pg_attribute.attnum > 0 AND pg_attribute.attstattarget < 0 AND pg_attribute.atthasdef = true AND pg_attribute.attnotnull = false
   GROUP BY ((pg_class.relname::text || '.'::text) || pg_attribute.attname::text)
   UNION
   SELECT pg_constraint.conname::text AS dvname,
           'FOREIGNKEY'::text AS dvtype,
           '08'::character varying(2) AS dvorder,
           'null'::character varying AS dvdata_type,
           'null'::character varying(10) AS dnmax_length
   FROM pg_constraint
   WHERE pg_constraint.contype = 'f'::"char" AND
       upper(pg_constraint.conname::text) ~~ 'CFK_%'::text
   GROUP BY pg_constraint.conname
   UNION
   SELECT p.proname::text AS dvname,
           'FUNCTION'::text AS dvtype,
           '10'::character varying(2) AS dvorder,
           'null'::character varying AS dvdata_type,
           'null'::character varying(10) AS dnmax_length
   FROM pg_namespace n
            JOIN pg_proc p ON p.pronamespace = n.oid
   WHERE n.nspname = 'public'::name AND (upper(p.proname::text) ~~
       'FN_%'::text OR upper(p.proname::text) = 'INSTR'::text OR p.probin IS NOT NULL)
   GROUP BY p.proname
   UNION
   SELECT pg_class.relname::text AS dvname,
           'INDEX'::text AS dvtype,
           '12'::character varying(2) AS dvorder,
           'null'::character varying AS dvdata_type,
           'null'::character varying(10) AS dnmax_length
   FROM pg_class
   WHERE pg_class.relkind = 'i'::"char" AND upper(pg_class.relname::text) ~~
       'IDX_%'::text
   GROUP BY pg_class.relname
   UNION
   SELECT pg_class.relname::text AS dvname,
           'INDEX_UNIQUE'::text AS dvtype,
           '14'::character varying(2) AS dvorder,
           'null'::character varying AS dvdata_type,
           'null'::character varying(10) AS dnmax_length
   FROM pg_class
   WHERE pg_class.relkind = 'i'::"char" AND upper(pg_class.relname::text) ~~
       'IDU_%'::text
   GROUP BY pg_class.relname
   UNION
   SELECT pg_constraint.conname::text AS dvname,
           'PRIMARYKEY'::text AS dvtype,
           '16'::character varying(2) AS dvorder,
           'null'::character varying AS dvdata_type,
           'null'::character varying(10) AS dnmax_length
   FROM pg_constraint
   WHERE pg_constraint.contype = 'p'::"char" AND
       upper(pg_constraint.conname::text) ~~ 'CPK_%'::text
   GROUP BY pg_constraint.conname
   UNION
   SELECT pg_class.relname::text AS dvname,
           'SEQUENCE'::text AS dvtype,
           '18'::character varying(2) AS dvorder,
           'null'::character varying AS dvdata_type,
           'null'::character varying(10) AS dnmax_length
   FROM pg_class
   WHERE pg_class.relkind = 'S'::"char" AND upper(pg_class.relname::text) ~~
       'TB%'::text
   GROUP BY pg_class.relname
   UNION
   SELECT b.relname::text AS dvname,
           'TABLE'::text AS dvtype,
           '20'::character varying(2) AS dvorder,
           'null'::character varying AS dvdata_type,
           'null'::character varying(10) AS dnmax_length
   FROM pg_class b
   WHERE b.relkind = 'r'::"char" AND upper(b.relname::text) ~~ 'TB%'::text
   GROUP BY b.relname
   UNION
   SELECT (b.relname::text || '.'::text) || a.attname::text AS dvname,
           'TABLECOLUMN'::text AS dvtype,
           '22'::character varying(2) AS dvorder,
           (''''::text || max(COALESCE(columns.data_type::character varying,
               ''::character varying)::text)) || ''''::text AS dvdata_type,
           COALESCE(max(columns.character_maximum_length::integer)::character
               varying(10), 'null'::character varying(10)) AS dnmax_length
   FROM pg_attribute a
            JOIN pg_class b ON b.oid = a.attrelid
            JOIN information_schema.columns ON b.relname =
                columns.table_name::name AND a.attname = columns.column_name::name
   WHERE upper(b.relname::text) ~~ 'TB%'::text AND b.relkind = 'r'::"char" AND
       a.attnum > 0 AND a.attstattarget < 0
   GROUP BY ((b.relname::text || '.'::text) || a.attname::text), b.oid, a.attnum
   UNION
   SELECT b.relname::text AS dvname,
           'VIEW'::text AS dvtype,
           '24'::character varying(2) AS dvorder,
           'null'::character varying AS dvdata_type,
           'null'::character varying(10) AS dnmax_length
   FROM pg_class b
   WHERE b.relkind = 'v'::"char" AND upper(b.relname::text) ~~ 'VW%'::text
   GROUP BY b.relname
   UNION
   SELECT (b.relname::text || '.'::text) || a.attname::text AS dvname,
           'VIEWCOLUMN'::text AS dvtype,
           '26'::character varying(2) AS dvorder,
           (''''::text || max(COALESCE(columns.data_type::character varying,
               ''::character varying)::text)) || ''''::text AS dvdata_type,
           COALESCE(max(columns.character_maximum_length::integer)::character
               varying(10), 'null'::character varying(10)) AS dnmax_length
   FROM pg_attribute a
            JOIN pg_class b ON b.oid = a.attrelid
            JOIN information_schema.columns ON b.relname =
                columns.table_name::name AND a.attname = columns.column_name::name
   WHERE upper(b.relname::text) ~~ 'VW%'::text AND b.relkind = 'v'::"char" AND
       a.attnum > 0 AND a.attstattarget < 0
   GROUP BY ((b.relname::text || '.'::text) || a.attname::text), b.oid, a.attnum
   ) adm_objects_generate
ORDER BY adm_objects_generate.dvtype, adm_objects_generate.dvname;

This file was generated with SQL Manager for PostgreSQL (www.pgsqlmanager.com) at 5-1-2022 14:13