· 6 years ago · Oct 29, 2019, 07:19 AM
1-- From 24 to 23
2ALTER TABLE hdb_catalog.hdb_table DROP COLUMN configuration;
3
4DROP VIEW IF EXISTS hdb_catalog.hdb_table_info_agg;
5DROP VIEW IF EXISTS hdb_catalog.hdb_column;
6
7CREATE VIEW hdb_catalog.hdb_column AS
8 WITH primary_key_references AS (
9 SELECT fkey.table_schema AS src_table_schema
10 , fkey.table_name AS src_table_name
11 , fkey.columns->>0 AS src_column_name
12 , json_agg(json_build_object(
13 'schema', fkey.ref_table_table_schema,
14 'name', fkey.ref_table
15 )) AS ref_tables
16 FROM hdb_catalog.hdb_foreign_key_constraint AS fkey
17 JOIN hdb_catalog.hdb_primary_key AS pkey
18 ON pkey.table_schema = fkey.ref_table_table_schema
19 AND pkey.table_name = fkey.ref_table
20 AND pkey.columns::jsonb = fkey.ref_columns::jsonb
21 WHERE json_array_length(fkey.columns) = 1
22 GROUP BY fkey.table_schema
23 , fkey.table_name
24 , fkey.columns->>0)
25 SELECT columns.table_schema
26 , columns.table_name
27 , columns.column_name AS name
28 , columns.udt_name AS type
29 , columns.is_nullable
30 , columns.ordinal_position
31 , coalesce(pkey_refs.ref_tables, '[]') AS primary_key_references
32 FROM information_schema.columns
33LEFT JOIN primary_key_references AS pkey_refs
34 ON columns.table_schema = pkey_refs.src_table_schema
35 AND columns.table_name = pkey_refs.src_table_name
36 AND columns.column_name = pkey_refs.src_column_name;
37
38CREATE VIEW hdb_catalog.hdb_table_info_agg AS (
39select
40 tables.table_name as table_name,
41 tables.table_schema as table_schema,
42 coalesce(columns.columns, '[]') as columns,
43 coalesce(pk.columns, '[]') as primary_key_columns,
44 coalesce(constraints.constraints, '[]') as constraints,
45 coalesce(views.view_info, 'null') as view_info
46from
47 information_schema.tables as tables
48 left outer join (
49 select
50 c.table_name,
51 c.table_schema,
52 json_agg(
53 json_build_object(
54 'name', name,
55 'type', type,
56 'is_nullable', is_nullable :: boolean,
57 'references', primary_key_references
58 )
59 ) as columns
60 from
61 hdb_catalog.hdb_column c
62 group by
63 c.table_schema,
64 c.table_name
65 ) columns on (
66 tables.table_schema = columns.table_schema
67 AND tables.table_name = columns.table_name
68 )
69 left outer join (
70 select * from hdb_catalog.hdb_primary_key
71 ) pk on (
72 tables.table_schema = pk.table_schema
73 AND tables.table_name = pk.table_name
74 )
75 left outer join (
76 select
77 c.table_schema,
78 c.table_name,
79 json_agg(constraint_name) as constraints
80 from
81 information_schema.table_constraints c
82 where
83 c.constraint_type = 'UNIQUE'
84 or c.constraint_type = 'PRIMARY KEY'
85 group by
86 c.table_schema,
87 c.table_name
88 ) constraints on (
89 tables.table_schema = constraints.table_schema
90 AND tables.table_name = constraints.table_name
91 )
92 left outer join (
93 select
94 table_schema,
95 table_name,
96 json_build_object(
97 'is_updatable',
98 (is_updatable::boolean OR is_trigger_updatable::boolean),
99 'is_deletable',
100 (is_updatable::boolean OR is_trigger_deletable::boolean),
101 'is_insertable',
102 (is_insertable_into::boolean OR is_trigger_insertable_into::boolean)
103 ) as view_info
104 from
105 information_schema.views v
106 ) views on (
107 tables.table_schema = views.table_schema
108 AND tables.table_name = views.table_name
109 )
110);
111
112DROP VIEW IF EXISTS hdb_catalog.hdb_function_info_agg;
113DROP VIEW IF EXISTS hdb_catalog.hdb_function_agg;
114
115CREATE VIEW hdb_catalog.hdb_function_agg AS
116(
117SELECT
118 p.proname::text AS function_name,
119 pn.nspname::text AS function_schema,
120
121 CASE
122 WHEN (p.provariadic = (0) :: oid) THEN false
123 ELSE true
124 END AS has_variadic,
125
126 CASE
127 WHEN (
128 (p.provolatile) :: text = ('i' :: character(1)) :: text
129 ) THEN 'IMMUTABLE' :: text
130 WHEN (
131 (p.provolatile) :: text = ('s' :: character(1)) :: text
132 ) THEN 'STABLE' :: text
133 WHEN (
134 (p.provolatile) :: text = ('v' :: character(1)) :: text
135 ) THEN 'VOLATILE' :: text
136 ELSE NULL :: text
137 END AS function_type,
138
139 pg_get_functiondef(p.oid) AS function_definition,
140
141 rtn.nspname::text AS return_type_schema,
142 rt.typname::text AS return_type_name,
143
144 CASE
145 WHEN ((rt.typtype) :: text = ('b' :: character(1)) :: text) THEN 'BASE' :: text
146 WHEN ((rt.typtype) :: text = ('c' :: character(1)) :: text) THEN 'COMPOSITE' :: text
147 WHEN ((rt.typtype) :: text = ('d' :: character(1)) :: text) THEN 'DOMAIN' :: text
148 WHEN ((rt.typtype) :: text = ('e' :: character(1)) :: text) THEN 'ENUM' :: text
149 WHEN ((rt.typtype) :: text = ('r' :: character(1)) :: text) THEN 'RANGE' :: text
150 WHEN ((rt.typtype) :: text = ('p' :: character(1)) :: text) THEN 'PSUEDO' :: text
151 ELSE NULL :: text
152 END AS return_type_type,
153 p.proretset AS returns_set,
154 ( SELECT
155 COALESCE(json_agg(q.type_name), '[]')
156 FROM
157 (
158 SELECT
159 pt.typname AS type_name,
160 pat.ordinality
161 FROM
162 unnest(
163 COALESCE(p.proallargtypes, (p.proargtypes) :: oid [])
164 ) WITH ORDINALITY pat(oid, ordinality)
165 LEFT JOIN pg_type pt ON ((pt.oid = pat.oid))
166 ORDER BY pat.ordinality ASC
167 ) q
168 ) AS input_arg_types,
169 to_json(COALESCE(p.proargnames, ARRAY [] :: text [])) AS input_arg_names,
170 p.pronargdefaults AS default_args
171FROM
172 pg_proc p
173 JOIN pg_namespace pn ON (pn.oid = p.pronamespace)
174 JOIN pg_type rt ON (rt.oid = p.prorettype)
175 JOIN pg_namespace rtn ON (rtn.oid = rt.typnamespace)
176WHERE
177 pn.nspname :: text NOT LIKE 'pg_%'
178 AND pn.nspname :: text NOT IN ('information_schema', 'hdb_catalog', 'hdb_views')
179 AND (NOT EXISTS (
180 SELECT
181 1
182 FROM
183 pg_aggregate
184 WHERE
185 ((pg_aggregate.aggfnoid) :: oid = p.oid)
186 )
187 )
188);
189
190CREATE VIEW hdb_catalog.hdb_function_info_agg AS (
191 SELECT
192 function_name,
193 function_schema,
194 row_to_json (
195 (
196 SELECT
197 e
198 FROM
199 (
200 SELECT
201 has_variadic,
202 function_type,
203 return_type_schema,
204 return_type_name,
205 return_type_type,
206 returns_set,
207 input_arg_types,
208 input_arg_names,
209 default_args,
210 exists(
211 SELECT
212 1
213 FROM
214 information_schema.tables
215 WHERE
216 table_schema = return_type_schema
217 AND table_name = return_type_name
218 ) AS returns_table
219 ) AS e
220 )
221 ) AS "function_info"
222 FROM
223 hdb_catalog.hdb_function_agg
224);
225
226UPDATE hdb_catalog.hdb_version
227 SET version = '22';