-
-
Notifications
You must be signed in to change notification settings - Fork 10
Expand file tree
/
Copy pathRoutineSourceQuery.cs
More file actions
149 lines (131 loc) · 5.95 KB
/
Copy pathRoutineSourceQuery.cs
File metadata and controls
149 lines (131 loc) · 5.95 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
namespace NpgsqlRest;
internal class RoutineSourceQuery
{
public const string Query = """
with cte1 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.type_udt_schema || '.' || r.type_udt_name)::regtype::text as return_type,
coalesce(
array_agg(quote_ident(p.parameter_name::text) order by p.ordinal_position) filter(where p.parameter_mode = 'IN' or p.parameter_mode = 'INOUT'),
'{}'::text[]
) as in_params,
case when r.data_type = 'USER-DEFINED' then null
else
coalesce(
(array_agg(quote_ident(p.parameter_name::text) order by p.ordinal_position)
filter(where p.parameter_mode = 'INOUT' or p.parameter_mode = 'OUT')),
'{}'::text[]
)
end as out_params,
coalesce(
array_agg(
case when p.data_type = 'bit' then 'varbit' else (p.udt_schema || '.' || p.udt_name)::regtype::text end
order by p.ordinal_position
) filter(where p.parameter_mode = 'IN' or p.parameter_mode = 'INOUT'),
'{}'::text[]
) as in_param_types,
case when r.data_type = 'USER-DEFINED' then null
else
coalesce(
(array_agg(case when p.data_type = 'bit' then 'varbit' else (p.udt_schema || '.' || p.udt_name)::regtype::text end
order by p.ordinal_position)
filter(where p.parameter_mode = 'INOUT' or p.parameter_mode = 'OUT')),
'{}'::text[]
)
end as out_param_types,
coalesce(
array_agg(p.parameter_default order by p.ordinal_position) filter(where p.parameter_mode = 'IN' or p.parameter_mode = 'INOUT'),
'{}'::text[]
) as in_param_defaults,
case when proc.provariadic <> 0 then true else false end as has_variadic,
r.type_udt_schema,
r.type_udt_name,
r.data_type
from
information_schema.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
left join information_schema.parameters p on r.specific_name = p.specific_name and r.specific_schema = p.specific_schema
where
r.specific_schema = any(
select
nspname
from
pg_catalog.pg_namespace
where
nspname not like 'pg_%'
and nspname <> 'information_schema'
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))
)
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 proc.prokind in ('f', 'p')
and not lower(r.external_language) = any(array['c', 'internal'])
and coalesce(r.type_udt_name, '') <> 'trigger'
group by
r.routine_type, r.specific_schema, r.routine_name,
proc.oid, r.specific_name, des.description,
r.data_type, r.type_udt_schema, r.type_udt_name,
proc.proisstrict, proc.procost, proc.prorows, proc.proparallel, proc.provolatile,
proc.proretset, proc.provariadic
), cte2 as (
select
cte1.schema,
cte1.specific_name,
array_agg(quote_ident(col.column_name) order by col.ordinal_position) as out_params,
array_agg(
case when col.data_type = 'bit' then 'varbit' else (col.udt_schema || '.' || col.udt_name)::regtype::text end
order by col.ordinal_position
) as out_param_types
from
information_schema.columns col
join cte1 on
cte1.data_type = 'USER-DEFINED' and col.table_schema = cte1.type_udt_schema and col.table_name = cte1.type_udt_name
group by
cte1.schema, cte1.specific_name
)
select
type,
cte1.schema,
cte1.name,
comment,
is_strict,
volatility_option,
returns_set,
coalesce(return_type, 'void') as return_type,
coalesce(array_length(coalesce(cte1.out_params, cte2.out_params), 1), 1) as return_record_count,
case
when array_length(coalesce(cte1.out_params, cte2.out_params), 1) is null
then array[cte1.name]::text[]
else coalesce(cte1.out_params, cte2.out_params)
end as return_record_names,
case
when array_length(coalesce(cte1.out_param_types, cte2.out_param_types), 1) is null
then array[return_type]::text[]
else coalesce(cte1.out_param_types, cte2.out_param_types)
end as return_record_types,
coalesce(cte1.out_params, cte2.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,
in_param_defaults as param_defaults,
has_variadic,
pg_get_functiondef(oid) as definition
from cte1
left join cte2 on cte1.schema = cte2.schema and cte1.specific_name = cte2.specific_name
""";
}