{{ message }}
-
-
Notifications
You must be signed in to change notification settings - Fork 11
Expand file tree
/
Copy pathRoutineSourceQuery.cs
More file actions
187 lines (186 loc) · 10 KB
/
Copy pathRoutineSourceQuery.cs
File metadata and controls
187 lines (186 loc) · 10 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
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
namespace NpgsqlRest;
internal class RoutineSourceQuery
{
public const string Query = """
with _types as (
select
(quote_ident(n.nspname) || '.' || quote_ident(t.typname))::regtype::text as name,
a.attnum as att_pos,
quote_ident(a.attname) as att_name,
pg_catalog.format_type(a.atttypid, a.atttypmod) as att_type
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 in ('r', 'c')
join pg_catalog.pg_attribute a on t.typrelid = a.attrelid and a.attisdropped is false
where
nspname not like 'pg_%'
and nspname <> 'information_schema'
and has_schema_privilege(current_user, n.nspname, 'USAGE')
and a.attnum > 0
), _array_element_types as (
select
(quote_ident(n.nspname) || '.' || quote_ident(arr_t.typname))::regtype::text as array_type_name,
array_agg(quote_ident(a.attname) order by a.attnum) as field_names,
array_agg(pg_catalog.format_type(a.atttypid, a.atttypmod) order by a.attnum) as field_types
from
pg_catalog.pg_type arr_t
join pg_catalog.pg_namespace n on arr_t.typnamespace = n.oid
join pg_catalog.pg_type elem_t on arr_t.typelem = elem_t.oid
join pg_catalog.pg_class c on elem_t.typrelid = c.oid and c.relkind in ('r', 'c')
join pg_catalog.pg_attribute a on elem_t.typrelid = a.attrelid and a.attisdropped is false and a.attnum > 0
where
n.nspname not like 'pg_%'
and n.nspname <> 'information_schema'
and has_schema_privilege(current_user, n.nspname, 'USAGE')
and arr_t.typelem <> 0
group by n.nspname, arr_t.typname
), _schemas as (
select
array_agg(nspname) as schemas
from
pg_catalog.pg_namespace
where
nspname not like 'pg_%'
and nspname <> 'information_schema'
and has_schema_privilege(current_user, nspname, 'USAGE')
and ($1 is null or nspname similar to $1)
and ($2 is null or nspname not similar to $2)
and ($3 is null or nspname = any($3))
and ($4 is null or not nspname = any($4))
), _routines as (
select
r.routine_type,
r.specific_schema,
r.specific_name,
r.routine_name,
r.data_type,
r.type_udt_schema,
r.type_udt_name,
(r.type_udt_schema || '.' || r.type_udt_name)::regtype::text as return_type,
r.data_type = 'USER-DEFINED' as is_user_defined,
quote_ident(p.parameter_name::text) as param_name,
p.parameter_mode = 'IN' or p.parameter_mode = 'INOUT' as is_in_param,
p.parameter_mode = 'INOUT' or p.parameter_mode = 'OUT' as is_out_param,
row_number() over (partition by r.specific_schema, r.specific_name order by p.ordinal_position, t.att_pos) as param_position,
case when p.data_type = 'bit' then 'varbit' else (p.udt_schema || '.' || p.udt_name)::regtype::text end as param_type,
t.att_name as custom_type_name,
t.att_type as custom_type_type,
t.att_pos as custom_type_pos
from information_schema.routines r
join _schemas on r.specific_schema = any(_schemas.schemas)
left join information_schema.parameters p on r.specific_name = p.specific_name and r.specific_schema = p.specific_schema
left join _types t on
p.data_type = 'USER-DEFINED'
and (p.udt_schema || '.' || p.udt_name)::regtype::text = t.name
where
coalesce(r.type_udt_name, '') <> 'trigger'
and r.routine_type in ('FUNCTION', 'PROCEDURE')
and (r.type_udt_schema is null or has_schema_privilege(current_user, r.type_udt_schema, 'USAGE'))
and ($5 is null or r.routine_name similar to $5)
and ($6 is null or r.routine_name not similar to $6)
and ($7 is null or r.routine_name = any($7))
and ($8 is null or not r.routine_name = any($8))
and ($9 is null or lower(r.external_language) = any($9))
and ($10 is null or lower(r.external_language) = any($10) is false)
order by
r.specific_schema, r.specific_name, p.ordinal_position, t.att_pos
), _routine_aggs as (
select
lower(r.routine_type) as type,
quote_ident(r.specific_schema) as schema,
quote_ident(r.routine_name) as name,
proc.oid as oid,
r.specific_name,
des.description as comment,
proc.proisstrict as is_strict,
proc.provolatile as volatility_option,
proc.proretset as returns_set,
r.return_type,
coalesce(array_agg(r.param_name order by r.param_position) filter(where r.is_in_param), '{}'::text[]) as in_params,
coalesce((array_agg(r.param_name order by r.param_position) filter(where r.is_out_param)),'{}'::text[]) as out_params,
coalesce(array_agg(r.param_type order by r.param_position) filter(where r.is_in_param), '{}'::text[]) as in_param_types,
coalesce((array_agg(r.param_type order by r.param_position) filter(where r.is_out_param)), '{}'::text[]) as out_param_types,
pg_get_function_arguments(proc.oid) as arguments_def,
case when proc.provariadic <> 0 then true else false end as has_variadic,
r.type_udt_schema,
r.type_udt_name,
r.data_type,
coalesce(array_agg(r.custom_type_name order by r.param_position) filter(where r.is_in_param), '{}'::text[]) as custom_param_type_names,
coalesce(array_agg(r.custom_type_type order by r.param_position) filter(where r.is_in_param), '{}'::text[]) as custom_param_type_types,
coalesce(array_agg(r.custom_type_pos order by r.param_position) filter(where r.is_in_param), '{}'::smallint[]) as custom_param_type_positions,
coalesce(array_agg(r.custom_type_name order by r.param_position) filter(where r.is_out_param), '{}'::text[]) as custom_rec_type_names,
coalesce(array_agg(r.custom_type_type order by r.param_position) filter(where r.is_out_param), '{}'::text[]) as custom_rec_type_types,
coalesce(array_agg(distinct r.param_name order by r.param_name) filter(where r.is_out_param), '{}'::text[]) as distinct_out_param_names,
coalesce(array_agg(distinct r.param_type order by r.param_type) filter(where r.is_out_param), '{}'::text[]) as distinct_out_param_types
from
_routines r
join pg_catalog.pg_proc proc on r.specific_name = proc.proname || '_' || proc.oid
left join pg_catalog.pg_description des on proc.oid = des.objoid
group by
r.routine_type,
r.specific_schema,
r.routine_name,
r.specific_name,
r.data_type,
r.type_udt_schema,
r.type_udt_name,
r.return_type,
r.is_user_defined,
des.description,
proc.oid,
proc.proisstrict,
proc.procost,
proc.prorows,
proc.proparallel,
proc.provolatile,
proc.proretset,
proc.provariadic
)
select
type,
r1.schema,
r1.name,
comment,
is_strict,
volatility_option,
returns_set,
case when custom_types.col_name is not null then 'record' else coalesce(return_type, 'void') end as return_type,
array_length(coalesce(custom_types.col_name, case when array_length(r1.out_params, 1) is null then array[r1.name]::text[] else r1.out_params end), 1) as return_record_count,
coalesce(custom_types.col_name, case when array_length(r1.out_params, 1) is null then array[r1.name]::text[] else r1.out_params end) as return_record_names,
coalesce(custom_types.col_type, case when array_length(r1.out_param_types, 1) is null then array[return_type]::text[] else r1.out_param_types end) as return_record_types,
coalesce(custom_types.col_type, r1.out_params) = '{}' as is_unnamed_record,
array_length(in_params, 1) as param_count,
in_params as param_names,
in_param_types as param_types,
arguments_def,
has_variadic,
pg_get_functiondef(oid) as definition,
custom_param_type_names,
custom_param_type_types,
custom_param_type_positions,
case when custom_types.col_name is not null then null else custom_rec_type_names end as custom_rec_type_names,
case when custom_types.col_name is not null then null else custom_rec_type_types end as custom_rec_type_types,
case when custom_types.col_name is not null and array_length(r1.distinct_out_param_names, 1) > 0
then r1.distinct_out_param_names else null end as composite_out_param_names,
case when custom_types.col_name is not null and array_length(r1.distinct_out_param_types, 1) > 0
then r1.distinct_out_param_types else null end as composite_out_param_types,
array_composite_info.array_column_indices,
array_composite_info.array_field_names_json,
array_composite_info.array_field_types_json
from _routine_aggs r1
left join lateral (
select array_agg(t.att_name order by t.att_pos) as col_name, array_agg(t.att_type order by t.att_pos) as col_type
from _types t
where t.name = r1.return_type
) custom_types on true
left join lateral (
select
array_agg(idx::int order by idx) as array_column_indices,
json_agg(aet.field_names order by idx)::text as array_field_names_json,
json_agg(aet.field_types order by idx)::text as array_field_types_json
from unnest(r1.out_param_types) with ordinality as u(param_type, idx)
join _array_element_types aet on aet.array_type_name = u.param_type
) array_composite_info on true
""";
}
You can’t perform that action at this time.