VPjs1ZY1

1ALTER TABLE hdb_catalog.hdb_table DROP COLUMN is_enum;
2
3CREATE FUNCTION hdb_catalog.hdb_table_oid_check() RETURNS trigger AS
4  $function$
5BEGIN
6  IF (EXISTS (SELECT 1 FROM information_schema.tables st WHERE st.table_schema = NEW.table_schema AND st.table_name = NEW.table_name)) THEN
7    return NEW;
8  ELSE
9    RAISE foreign_key_violation using message = 'table_schema, table_name not in information_schema.tables';
10    return NULL;
11  END IF;
12END;
13$function$
14  LANGUAGE plpgsql;
15
16CREATE TRIGGER hdb_table_oid_check BEFORE INSERT OR UPDATE ON hdb_catalog.hdb_table
17  FOR EACH ROW EXECUTE PROCEDURE hdb_catalog.hdb_table_oid_check();
18
19
20DROP VIEW hdb_catalog.hdb_table_info_agg;
21DROP VIEW hdb_catalog.hdb_column;
22DROP VIEW hdb_catalog.hdb_foreign_key_constraint;
23
24CREATE VIEW hdb_catalog.hdb_foreign_key_constraint AS
25SELECT
26    q.table_schema :: text,
27    q.table_name :: text,
28    q.constraint_name :: text,
29    min(q.constraint_oid) :: integer as constraint_oid,
30    min(q.ref_table_table_schema) :: text as ref_table_table_schema,
31    min(q.ref_table) :: text as ref_table,
32    json_object_agg(ac.attname, afc.attname) as column_mapping,
33    min(q.confupdtype) :: text as on_update,
34    min(q.confdeltype) :: text as on_delete
35FROM
36    (SELECT
37        ctn.nspname AS table_schema,
38        ct.relname AS table_name,
39        r.conrelid AS table_id,
40        r.conname as constraint_name,
41        r.oid as constraint_oid,
42        cftn.nspname AS ref_table_table_schema,
43        cft.relname as ref_table,
44        r.confrelid as ref_table_id,
45        r.confupdtype,
46        r.confdeltype,
47        UNNEST (r.conkey) AS column_id,
48        UNNEST (r.confkey) AS ref_column_id
49    FROM
50        pg_catalog.pg_constraint r
51        JOIN pg_catalog.pg_class ct
52          ON r.conrelid = ct.oid
53        JOIN pg_catalog.pg_namespace ctn
54          ON ct.relnamespace = ctn.oid
55        JOIN pg_catalog.pg_class cft
56          ON r.confrelid = cft.oid
57        JOIN pg_catalog.pg_namespace cftn
58          ON cft.relnamespace = cftn.oid
59    WHERE
60        r.contype = 'f'
61    ) q
62    JOIN pg_catalog.pg_attribute ac
63      ON q.column_id = ac.attnum
64         AND q.table_id = ac.attrelid
65    JOIN pg_catalog.pg_attribute afc
66      ON q.ref_column_id = afc.attnum
67         AND q.ref_table_id = afc.attrelid
68 GROUP BY q.table_schema, q.table_name, q.constraint_name;
69
70CREATE VIEW hdb_catalog.hdb_table_info_agg AS (
71select
72  tables.table_name as table_name,
73  tables.table_schema as table_schema,
74  coalesce(columns.columns, '[]') as columns,
75  coalesce(pk.columns, '[]') as primary_key_columns,
76  coalesce(constraints.constraints, '[]') as constraints,
77  coalesce(views.view_info, 'null') as view_info
78from
79  information_schema.tables as tables
80  left outer join (
81    select
82      c.table_name,
83      c.table_schema,
84      json_agg(
85        json_build_object(
86          'name',
87          column_name,
88          'type',
89          udt_name,
90          'is_nullable',
91          is_nullable :: boolean
92        )
93      ) as columns
94    from
95      information_schema.columns c
96    group by
97      c.table_schema,
98      c.table_name
99  ) columns on (
100    tables.table_schema = columns.table_schema
101    AND tables.table_name = columns.table_name
102  )
103  left outer join (
104    select * from hdb_catalog.hdb_primary_key
105  ) pk on (
106    tables.table_schema = pk.table_schema
107    AND tables.table_name = pk.table_name
108  )
109  left outer join (
110    select
111      c.table_schema,
112      c.table_name,
113      json_agg(constraint_name) as constraints
114    from
115      information_schema.table_constraints c
116    where
117      c.constraint_type = 'UNIQUE'
118      or c.constraint_type = 'PRIMARY KEY'
119    group by
120      c.table_schema,
121      c.table_name
122  ) constraints on (
123    tables.table_schema = constraints.table_schema
124    AND tables.table_name = constraints.table_name
125  )
126  left outer join (
127    select
128      table_schema,
129      table_name,
130      json_build_object(
131        'is_updatable',
132        (is_updatable::boolean OR is_trigger_updatable::boolean),
133        'is_deletable',
134        (is_updatable::boolean OR is_trigger_deletable::boolean),
135        'is_insertable',
136        (is_insertable_into::boolean OR is_trigger_insertable_into::boolean)
137      ) as view_info
138    from
139      information_schema.views v
140  ) views on (
141    tables.table_schema = views.table_schema
142    AND tables.table_name = views.table_name
143  )
144);
145
146UPDATE hdb_catalog.hdb_version
147   SET version = '19'
148 WHERE version = '20';