-
Notifications
You must be signed in to change notification settings - Fork 1
Expand file tree
/
Copy path_types.sql
More file actions
61 lines (61 loc) · 1.58 KB
/
Copy path_types.sql
File metadata and controls
61 lines (61 loc) · 1.58 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
create function schema._types(_schemas text[])
returns table (
type text,
schema text,
name text,
comment text,
definition text
)
language sql
as
$$
select
sub.type,
sub.schema,
sub.name,
sub.comment,
case when sub.comment is null then sub.definition else
concat(
sub.definition,
E'\n',
'COMMENT ON TYPE ',
schema._ident(sub.schema, sub.name),
' IS ',
schema._quote(sub.comment),
';'
)
end as definition
from (
select
'type' as type,
n.nspname::text as schema,
t.typname::text as name,
pg_catalog.obj_description(t.oid, 'pg_type') as comment,
concat(
'CREATE TYPE ',
schema._ident(n.nspname, t.typname),
E' AS (\n',
a.definition,
E'\n);'
) as definition
from
pg_catalog.pg_type t
join pg_catalog.pg_namespace n on n.oid = t.typnamespace
join pg_catalog.pg_class c on t.typrelid = c.oid and c.relkind = 'c'
join lateral (
select string_agg(
concat(
' ',
quote_ident(a.attname),
' ',
pg_catalog.format_type(a.atttypid, a.atttypmod)
), E',\n' order by a.attnum
) as definition
from pg_catalog.pg_attribute a
where
a.attrelid = t.typrelid and a.attisdropped is false
) a on true
where
n.nspname = any(_schemas)
) sub;
$$;