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
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
|
CREATE OR REPLACE FUNCTION pgtap_version()
RETURNS NUMERIC AS 'SELECT 0.99;'
LANGUAGE SQL IMMUTABLE;
CREATE OR REPLACE FUNCTION pg_version_num()
RETURNS integer AS $$
SELECT current_setting('server_version_num')::integer;
$$ LANGUAGE SQL IMMUTABLE;
CREATE OR REPLACE FUNCTION _ident_array_to_sorted_string( name[], text )
RETURNS text AS $$
SELECT array_to_string(ARRAY(
SELECT quote_ident($1[i])
FROM generate_series(1, array_upper($1, 1)) s(i)
ORDER BY $1[i]
), $2);
$$ LANGUAGE SQL immutable;
CREATE OR REPLACE FUNCTION _array_to_sorted_string( name[], text )
RETURNS text AS $$
SELECT array_to_string(ARRAY(
SELECT $1[i]
FROM generate_series(1, array_upper($1, 1)) s(i)
ORDER BY $1[i]
), $2);
$$ LANGUAGE SQL immutable;
CREATE OR REPLACE FUNCTION _are ( text, name[], name[], TEXT )
RETURNS TEXT AS $$
DECLARE
what ALIAS FOR $1;
extras ALIAS FOR $2;
missing ALIAS FOR $3;
descr ALIAS FOR $4;
msg TEXT := '';
res BOOLEAN := TRUE;
BEGIN
IF extras[1] IS NOT NULL THEN
res = FALSE;
msg := E'\n' || diag(
' Extra ' || what || E':\n '
|| _ident_array_to_sorted_string( extras, E'\n ' )
);
END IF;
IF missing[1] IS NOT NULL THEN
res = FALSE;
msg := msg || E'\n' || diag(
' Missing ' || what || E':\n '
|| _ident_array_to_sorted_string( missing, E'\n ' )
);
END IF;
RETURN ok(res, descr) || msg;
END;
$$ LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION _areni ( text, text[], text[], TEXT )
RETURNS TEXT AS $$
DECLARE
what ALIAS FOR $1;
extras ALIAS FOR $2;
missing ALIAS FOR $3;
descr ALIAS FOR $4;
msg TEXT := '';
res BOOLEAN := TRUE;
BEGIN
IF extras[1] IS NOT NULL THEN
res = FALSE;
msg := E'\n' || diag(
' Extra ' || what || E':\n '
|| _array_to_sorted_string( extras, E'\n ' )
);
END IF;
IF missing[1] IS NOT NULL THEN
res = FALSE;
msg := msg || E'\n' || diag(
' Missing ' || what || E':\n '
|| _array_to_sorted_string( missing, E'\n ' )
);
END IF;
RETURN ok(res, descr) || msg;
END;
$$ LANGUAGE plpgsql;
-- Note: this fixes a bug in the 97->98 upgrade script
-- table_owner_is ( table, user, description )
/*
CREATE OR REPLACE FUNCTION table_owner_is ( NAME, NAME, TEXT )
RETURNS TEXT AS $$
DECLARE
owner NAME := _get_rel_owner('{r,p}'::char[], $1);
BEGIN
-- Make sure the table exists.
IF owner IS NULL THEN
RETURN ok(FALSE, $3) || E'\n' || diag(
E' Table ' || quote_ident($1) || ' does not exist'
);
END IF;
RETURN is(owner, $2, $3);
END;
$$ LANGUAGE plpgsql;
*/
-- _hasc( schema, table, constraint_type )
CREATE OR REPLACE FUNCTION _hasc ( NAME, NAME, CHAR )
RETURNS BOOLEAN AS $$
SELECT EXISTS(
SELECT true
FROM pg_catalog.pg_namespace n
JOIN pg_catalog.pg_class c ON c.relnamespace = n.oid
JOIN pg_catalog.pg_constraint x ON c.oid = x.conrelid
JOIN pg_catalog.pg_index i ON c.oid = i.indrelid
WHERE i.indisprimary = true
AND n.nspname = $1
AND c.relname = $2
AND x.contype = $3
);
$$ LANGUAGE sql;
-- _hasc( table, constraint_type )
CREATE OR REPLACE FUNCTION _hasc ( NAME, CHAR )
RETURNS BOOLEAN AS $$
SELECT EXISTS(
SELECT true
FROM pg_catalog.pg_class c
JOIN pg_catalog.pg_constraint x ON c.oid = x.conrelid
JOIN pg_catalog.pg_index i ON c.oid = i.indrelid
WHERE i.indisprimary = true
AND pg_table_is_visible(c.oid)
AND c.relname = $1
AND x.contype = $2
);
$$ LANGUAGE sql;
CREATE OR REPLACE VIEW tap_funky
AS SELECT p.oid AS oid,
n.nspname AS schema,
p.proname AS name,
pg_catalog.pg_get_userbyid(p.proowner) AS owner,
array_to_string(p.proargtypes::regtype[], ',') AS args,
CASE p.proretset WHEN TRUE THEN 'setof ' ELSE '' END
|| p.prorettype::regtype AS returns,
p.prolang AS langoid,
p.proisstrict AS is_strict,
p.prokind AS kind,
p.prosecdef AS is_definer,
p.proretset AS returns_set,
p.provolatile::char AS volatility,
pg_catalog.pg_function_is_visible(p.oid) AS is_visible
FROM pg_catalog.pg_proc p
JOIN pg_catalog.pg_namespace n ON p.pronamespace = n.oid
;
CREATE OR REPLACE FUNCTION _agg ( NAME, NAME, NAME[] )
RETURNS BOOLEAN AS $$
SELECT kind = 'a'
FROM tap_funky
WHERE schema = $1
AND name = $2
AND args = array_to_string($3, ',')
$$ LANGUAGE SQL;
CREATE OR REPLACE FUNCTION _agg ( NAME, NAME )
RETURNS BOOLEAN AS $$
SELECT kind = 'a' FROM tap_funky WHERE schema = $1 AND name = $2
$$ LANGUAGE SQL;
CREATE OR REPLACE FUNCTION _agg ( NAME, NAME[] )
RETURNS BOOLEAN AS $$
SELECT kind = 'a'
FROM tap_funky
WHERE name = $1
AND args = array_to_string($2, ',')
AND is_visible;
$$ LANGUAGE SQL;
CREATE OR REPLACE FUNCTION _agg ( NAME )
RETURNS BOOLEAN AS $$
SELECT kind = 'a' FROM tap_funky WHERE name = $1 AND is_visible;
$$ LANGUAGE SQL;
-- _hasc( schema, table, constraint_type )
CREATE OR REPLACE FUNCTION _hasc ( NAME, NAME, CHAR )
RETURNS BOOLEAN AS $$
SELECT EXISTS(
SELECT true
FROM pg_catalog.pg_namespace n
JOIN pg_catalog.pg_class c ON c.relnamespace = n.oid
JOIN pg_catalog.pg_constraint x ON c.oid = x.conrelid
WHERE n.nspname = $1
AND c.relname = $2
AND x.contype = $3
);
$$ LANGUAGE sql;
-- _hasc( table, constraint_type )
CREATE OR REPLACE FUNCTION _hasc ( NAME, CHAR )
RETURNS BOOLEAN AS $$
SELECT EXISTS(
SELECT true
FROM pg_catalog.pg_class c
JOIN pg_catalog.pg_constraint x ON c.oid = x.conrelid
WHERE pg_table_is_visible(c.oid)
AND c.relname = $1
AND x.contype = $2
);
$$ LANGUAGE sql;
|
