Debian Codegraph @

modules/sequence/functions/feature_ids_fx.plpgsql

raw
1
2 ---bad precedence: change customed type name
3 ---drop here to remove old function
4 --DROP TYPE feature_by_cvt_type CASCADE;
5 --DROP TYPE fxgsfids_type CASCADE;
6
7 --DROP TYPE feature_by_fx_type CASCADE;
8 CREATE TYPE feature_by_fx_type AS (
9 feature_id bigint,
10 depth INT
11 );
12
13 CREATE OR REPLACE FUNCTION get_sub_feature_ids(text) RETURNS SETOF feature_by_fx_type AS
14 '
15 DECLARE
16 sql alias for $1;
17 myrc feature_by_fx_type%ROWTYPE;
18 myrc2 feature_by_fx_type%ROWTYPE;
19
20 BEGIN
21 FOR myrc IN EXECUTE sql LOOP
22 FOR myrc2 IN SELECT * FROM get_sub_feature_ids(myrc.feature_id) LOOP
23 RETURN NEXT myrc2;
24 END LOOP;
25 END LOOP;
26 RETURN;
27 END;
28 '
29 LANGUAGE 'plpgsql';
30
31 CREATE OR REPLACE FUNCTION get_up_feature_ids(text) RETURNS SETOF feature_by_fx_type AS
32 '
33 DECLARE
34 sql alias for $1;
35 myrc feature_by_fx_type%ROWTYPE;
36 myrc2 feature_by_fx_type%ROWTYPE;
37
38 BEGIN
39 FOR myrc IN EXECUTE sql LOOP
40 FOR myrc2 IN SELECT * FROM get_up_feature_ids(myrc.feature_id) LOOP
41 RETURN NEXT myrc2;
42 END LOOP;
43 END LOOP;
44 RETURN;
45 END;
46 '
47 LANGUAGE 'plpgsql';
48
49 CREATE OR REPLACE FUNCTION get_feature_ids(text) RETURNS SETOF feature_by_fx_type AS
50 '
51 DECLARE
52 sql alias for $1;
53 myrc feature_by_fx_type%ROWTYPE;
54 myrc2 feature_by_fx_type%ROWTYPE;
55 myrc3 feature_by_fx_type%ROWTYPE;
56
57 BEGIN
58
59 FOR myrc IN EXECUTE sql LOOP
60 RETURN NEXT myrc;
61 FOR myrc2 IN SELECT * FROM get_up_feature_ids(myrc.feature_id) LOOP
62 RETURN NEXT myrc2;
63 END LOOP;
64 FOR myrc3 IN SELECT * FROM get_sub_feature_ids(myrc.feature_id) LOOP
65 RETURN NEXT myrc3;
66 END LOOP;
67 END LOOP;
68 RETURN;
69 END;
70 '
71 LANGUAGE 'plpgsql';
72
73
74 CREATE OR REPLACE FUNCTION get_sub_feature_ids(bigint) RETURNS SETOF feature_by_fx_type AS
75 '
76 DECLARE
77 root alias for $1;
78 myrc feature_by_fx_type%ROWTYPE;
79 myrc2 feature_by_fx_type%ROWTYPE;
80
81 BEGIN
82 FOR myrc IN SELECT DISTINCT subject_id AS feature_id FROM feature_relationship WHERE object_id = root LOOP
83 RETURN NEXT myrc;
84 FOR myrc2 IN SELECT * FROM get_sub_feature_ids(myrc.feature_id) LOOP
85 RETURN NEXT myrc2;
86 END LOOP;
87 END LOOP;
88 RETURN;
89 END;
90 '
91 LANGUAGE 'plpgsql';
92
93 CREATE OR REPLACE FUNCTION get_up_feature_ids(bigint) RETURNS SETOF feature_by_fx_type AS
94 '
95 DECLARE
96 leaf alias for $1;
97 myrc feature_by_fx_type%ROWTYPE;
98 myrc2 feature_by_fx_type%ROWTYPE;
99 BEGIN
100 FOR myrc IN SELECT DISTINCT object_id AS feature_id FROM feature_relationship WHERE subject_id = leaf LOOP
101 RETURN NEXT myrc;
102 FOR myrc2 IN SELECT * FROM get_up_feature_ids(myrc.feature_id) LOOP
103 RETURN NEXT myrc2;
104 END LOOP;
105 END LOOP;
106 RETURN;
107 END;
108 '
109 LANGUAGE 'plpgsql';
110
111 CREATE OR REPLACE FUNCTION get_sub_feature_ids(bigint, integer) RETURNS SETOF feature_by_fx_type AS
112 '
113 DECLARE
114 root alias for $1;
115 depth alias for $2;
116 myrc feature_by_fx_type%ROWTYPE;
117 myrc2 feature_by_fx_type%ROWTYPE;
118
119 BEGIN
120 FOR myrc IN SELECT DISTINCT subject_id AS feature_id, depth FROM feature_relationship WHERE object_id = root LOOP
121 RETURN NEXT myrc;
122 FOR myrc2 IN SELECT * FROM get_sub_feature_ids(myrc.feature_id,depth+1) LOOP
123 RETURN NEXT myrc2;
124 END LOOP;
125 END LOOP;
126 RETURN;
127 END;
128 '
129 LANGUAGE 'plpgsql';
130
131 --- depth is reversed and meanless when union with results from get_sub_feature_ids
132 CREATE OR REPLACE FUNCTION get_up_feature_ids(bigint, integer) RETURNS SETOF feature_by_fx_type AS
133 '
134 DECLARE
135 leaf alias for $1;
136 depth alias for $2;
137 myrc feature_by_fx_type%ROWTYPE;
138 myrc2 feature_by_fx_type%ROWTYPE;
139 BEGIN
140 FOR myrc IN SELECT DISTINCT object_id AS feature_id, depth FROM feature_relationship WHERE subject_id = leaf LOOP
141 RETURN NEXT myrc;
142 FOR myrc2 IN SELECT * FROM get_up_feature_ids(myrc.feature_id,depth+1) LOOP
143 RETURN NEXT myrc2;
144 END LOOP;
145 END LOOP;
146 RETURN;
147 END;
148 '
149 LANGUAGE 'plpgsql';
150
151 --- children feature ids only (not include itself--parent) for SO type and range (src)
152 CREATE OR REPLACE FUNCTION get_sub_feature_ids_by_type_src(cvterm.name%TYPE,feature.uniquename%TYPE,char(1)) RETURNS SETOF feature_by_fx_type AS
153 '
154 DECLARE
155 gtype alias for $1;
156 src alias for $2;
157 is_an alias for $3;
158 query text;
159 myrc feature_by_fx_type%ROWTYPE;
160 myrc2 feature_by_fx_type%ROWTYPE;
161
162 BEGIN
163
164 query := ''SELECT DISTINCT f.feature_id FROM feature f INNER join cvterm t ON (f.type_id = t.cvterm_id)
165 INNER join featureloc fl
166 ON (f.feature_id = fl.feature_id) INNER join feature src ON (src.feature_id = fl.srcfeature_id)
167 WHERE t.name = '' || quote_literal(gtype) || '' AND src.uniquename = '' || quote_literal(src)
168 || '' AND f.is_analysis = '' || quote_literal(is_an) || '';'';
169
170 IF (STRPOS(gtype, ''%'') > 0) THEN
171 query := ''SELECT DISTINCT f.feature_id FROM feature f INNER join cvterm t ON (f.type_id = t.cvterm_id)
172 INNER join featureloc fl
173 ON (f.feature_id = fl.feature_id) INNER join feature src ON (src.feature_id = fl.srcfeature_id)
174 WHERE t.name like '' || quote_literal(gtype) || '' AND src.uniquename = '' || quote_literal(src)
175 || '' AND f.is_analysis = '' || quote_literal(is_an) || '';'';
176 END IF;
177 FOR myrc IN SELECT * FROM get_sub_feature_ids(query) LOOP
178 RETURN NEXT myrc;
179 END LOOP;
180 RETURN;
181 END;
182 '
183 LANGUAGE 'plpgsql';
184
185 --- by SO type, usefull for tRNA, ncRNA, etc
186 CREATE OR REPLACE FUNCTION get_feature_ids_by_type(cvterm.name%TYPE, char(1)) RETURNS SETOF feature_by_fx_type AS
187 '
188 DECLARE
189 gtype alias for $1;
190 is_an alias for $2;
191 query TEXT;
192 myrc feature_by_fx_type%ROWTYPE;
193 myrc2 feature_by_fx_type%ROWTYPE;
194
195 BEGIN
196
197 query := ''SELECT DISTINCT f.feature_id
198 FROM feature f, cvterm t WHERE t.cvterm_id = f.type_id AND t.name = '' || quote_literal(gtype) ||
199 '' AND f.is_analysis = '' || quote_literal(is_an) || '';'';
200 IF (STRPOS(gtype, ''%'') > 0) THEN
201 query := ''SELECT DISTINCT f.feature_id
202 FROM feature f, cvterm t WHERE t.cvterm_id = f.type_id AND t.name like ''
203 || quote_literal(gtype) || '' AND f.is_analysis = '' || quote_literal(is_an) || '';'';
204 END IF;
205
206 FOR myrc IN SELECT * FROM get_feature_ids(query) LOOP
207 RETURN NEXT myrc;
208 END LOOP;
209 RETURN;
210 END;
211 '
212 LANGUAGE 'plpgsql';
213
214 CREATE OR REPLACE FUNCTION get_feature_ids_by_type_src(cvterm.name%TYPE, feature.uniquename%TYPE, char(1)) RETURNS SETOF feature_by_fx_type AS
215 '
216 DECLARE
217 gtype alias for $1;
218 src alias for $2;
219 is_an alias for $3;
220 query TEXT;
221 myrc feature_by_fx_type%ROWTYPE;
222 myrc2 feature_by_fx_type%ROWTYPE;
223
224 BEGIN
225
226 query := ''SELECT DISTINCT f.feature_id
227 FROM feature f INNER join cvterm t ON (f.type_id = t.cvterm_id) INNER join featureloc fl
228 ON (f.feature_id = fl.feature_id) INNER join feature src ON (src.feature_id = fl.srcfeature_id)
229 WHERE t.name = '' || quote_literal(gtype) || '' AND src.uniquename = '' || quote_literal(src)
230 || '' AND f.is_analysis = '' || quote_literal(is_an) || '';'';
231
232 IF (STRPOS(gtype, ''%'') > 0) THEN
233 query := ''SELECT DISTINCT f.feature_id
234 FROM feature f INNER join cvterm t ON (f.type_id = t.cvterm_id) INNER join featureloc fl
235 ON (f.feature_id = fl.feature_id) INNER join feature src ON (src.feature_id = fl.srcfeature_id)
236 WHERE t.name like '' || quote_literal(gtype) || '' AND src.uniquename = '' || quote_literal(src)
237 || '' AND f.is_analysis = '' || quote_literal(is_an) || '';'';
238 END IF;
239
240 FOR myrc IN SELECT * FROM get_feature_ids(query) LOOP
241 RETURN NEXT myrc;
242 END LOOP;
243 RETURN;
244 END;
245 '
246 LANGUAGE 'plpgsql';
247
248 CREATE OR REPLACE FUNCTION get_feature_ids_by_type_name(cvterm.name%TYPE, feature.uniquename%TYPE, char(1)) RETURNS SETOF feature_by_fx_type AS
249 '
250 DECLARE
251 gtype alias for $1;
252 name alias for $2;
253 is_an alias for $3;
254 query TEXT;
255 myrc feature_by_fx_type%ROWTYPE;
256 myrc2 feature_by_fx_type%ROWTYPE;
257
258 BEGIN
259
260 query := ''SELECT DISTINCT f.feature_id
261 FROM feature f INNER join cvterm t ON (f.type_id = t.cvterm_id)
262 WHERE t.name = '' || quote_literal(gtype) || '' AND (f.uniquename = '' || quote_literal(name)
263 || '' OR f.name = '' || quote_literal(name) || '') AND f.is_analysis = '' || quote_literal(is_an) || '';'';
264
265 IF (STRPOS(name, ''%'') > 0) THEN
266 query := ''SELECT DISTINCT f.feature_id
267 FROM feature f INNER join cvterm t ON (f.type_id = t.cvterm_id)
268 WHERE t.name = '' || quote_literal(gtype) || '' AND (f.uniquename like '' || quote_literal(name)
269 || '' OR f.name like '' || quote_literal(name) || '') AND f.is_analysis = '' || quote_literal(is_an) || '';'';
270 END IF;
271
272 FOR myrc IN SELECT * FROM get_feature_ids(query) LOOP
273 RETURN NEXT myrc;
274 END LOOP;
275 RETURN;
276 END;
277 '
278 LANGUAGE 'plpgsql';
279
280 --- get all feature ids (including children) for feature that has an ontology term (say GO function)
281 CREATE OR REPLACE FUNCTION get_feature_ids_by_ont(cv.name%TYPE,cvterm.name%TYPE) RETURNS SETOF feature_by_fx_type AS
282 '
283 DECLARE
284 aspect alias for $1;
285 term alias for $2;
286 query TEXT;
287 myrc feature_by_fx_type%ROWTYPE;
288 myrc2 feature_by_fx_type%ROWTYPE;
289
290 BEGIN
291
292 query := ''SELECT DISTINCT fcvt.feature_id
293 FROM feature_cvterm fcvt, cv, cvterm t WHERE cv.cv_id = t.cv_id AND
294 t.cvterm_id = fcvt.cvterm_id AND cv.name = '' || quote_literal(aspect) ||
295 '' AND t.name = '' || quote_literal(term) || '';'';
296 IF (STRPOS(term, ''%'') > 0) THEN
297 query := ''SELECT DISTINCT fcvt.feature_id
298 FROM feature_cvterm fcvt, cv, cvterm t WHERE cv.cv_id = t.cv_id AND
299 t.cvterm_id = fcvt.cvterm_id AND cv.name = '' || quote_literal(aspect) ||
300 '' AND t.name like '' || quote_literal(term) || '';'';
301 END IF;
302
303 FOR myrc IN SELECT * FROM get_feature_ids(query) LOOP
304 RETURN NEXT myrc;
305 END LOOP;
306 RETURN;
307 END;
308 '
309 LANGUAGE 'plpgsql';
310
311 CREATE OR REPLACE FUNCTION get_feature_ids_by_ont_root(cv.name%TYPE,cvterm.name%TYPE) RETURNS SETOF feature_by_fx_type AS
312 '
313 DECLARE
314 aspect alias for $1;
315 term alias for $2;
316 query TEXT;
317 subquery TEXT;
318 myrc feature_by_fx_type%ROWTYPE;
319 myrc2 feature_by_fx_type%ROWTYPE;
320
321 BEGIN
322
323 subquery := ''SELECT t.cvterm_id FROM cv, cvterm t WHERE cv.cv_id = t.cv_id
324 AND cv.name = '' || quote_literal(aspect) || '' AND t.name = '' || quote_literal(term) || '';'';
325 IF (STRPOS(term, ''%'') > 0) THEN
326 subquery := ''SELECT t.cvterm_id FROM cv, cvterm t WHERE cv.cv_id = t.cv_id
327 AND cv.name = '' || quote_literal(aspect) || '' AND t.name like '' || quote_literal(term) || '';'';
328 END IF;
329 query := ''SELECT DISTINCT fcvt.feature_id
330 FROM feature_cvterm fcvt INNER JOIN (SELECT cvterm_id FROM get_it_sub_cvterm_ids('' || quote_literal(subquery) || '')) AS ont ON (fcvt.cvterm_id = ont.cvterm_id);'';
331
332 FOR myrc IN SELECT * FROM get_feature_ids(query) LOOP
333 RETURN NEXT myrc;
334 END LOOP;
335 RETURN;
336 END;
337 '
338 LANGUAGE 'plpgsql';
339
340 --- get all feature ids (including children) for feature with the property (type, val)
341 CREATE OR REPLACE FUNCTION get_feature_ids_by_property(cvterm.name%TYPE,varchar) RETURNS SETOF feature_by_fx_type AS
342 '
343 DECLARE
344 p_type alias for $1;
345 p_val alias for $2;
346 query TEXT;
347 myrc feature_by_fx_type%ROWTYPE;
348 myrc2 feature_by_fx_type%ROWTYPE;
349
350 BEGIN
351
352 query := ''SELECT DISTINCT fprop.feature_id
353 FROM featureprop fprop, cvterm t WHERE t.cvterm_id = fprop.type_id AND t.name = '' ||
354 quote_literal(p_type) || '' AND fprop.value = '' || quote_literal(p_val) || '';'';
355 IF (STRPOS(p_val, ''%'') > 0) THEN
356 query := ''SELECT DISTINCT fprop.feature_id
357 FROM featureprop fprop, cvterm t WHERE t.cvterm_id = fprop.type_id AND t.name = '' ||
358 quote_literal(p_type) || '' AND fprop.value like '' || quote_literal(p_val) || '';'';
359 END IF;
360
361 FOR myrc IN SELECT * FROM get_feature_ids(query) LOOP
362 RETURN NEXT myrc;
363 END LOOP;
364 RETURN;
365 END;
366 '
367 LANGUAGE 'plpgsql';
368
369 --- get all feature ids (including children) for feature with the property val
370 CREATE OR REPLACE FUNCTION get_feature_ids_by_propval(varchar) RETURNS SETOF feature_by_fx_type AS
371 '
372 DECLARE
373 p_val alias for $1;
374 query TEXT;
375 myrc feature_by_fx_type%ROWTYPE;
376 myrc2 feature_by_fx_type%ROWTYPE;
377
378 BEGIN
379
380 query := ''SELECT DISTINCT fprop.feature_id
381 FROM featureprop fprop WHERE fprop.value = '' || quote_literal(p_val) || '';'';
382 IF (STRPOS(p_val, ''%'') > 0) THEN
383 query := ''SELECT DISTINCT fprop.feature_id
384 FROM featureprop fprop WHERE fprop.value like '' || quote_literal(p_val) || '';'';
385 END IF;
386
387 FOR myrc IN SELECT * FROM get_feature_ids(query) LOOP
388 RETURN NEXT myrc;
389 END LOOP;
390 RETURN;
391 END;
392 '
393 LANGUAGE 'plpgsql';
394
395
396 ---4 args: ptype, ctype, count, operator (valid SQL number comparison operator), and is_analysis
397 ---get feature ids for any node with type = ptype whose child node type = ctype
398 ---and child node feature count comparing (using operator) to ccount
399 CREATE OR REPLACE FUNCTION get_feature_ids_by_child_count(cvterm.name%TYPE, cvterm.name%TYPE, INTEGER, varchar, char(1)) RETURNS SETOF feature_by_fx_type AS
400 '
401 DECLARE
402 ptype alias for $1;
403 ctype alias for $2;
404 ccount alias for $3;
405 operator alias for $4;
406 is_an alias for $5;
407 query TEXT;
408 myrc feature_by_fx_type%ROWTYPE;
409 myrc2 feature_by_fx_type %ROWTYPE;
410
411 BEGIN
412
413 query := ''SELECT DISTINCT f.feature_id
414 FROM feature f INNER join (select count(*) as c, p.feature_id FROM feature p
415 INNER join cvterm pt ON (p.type_id = pt.cvterm_id) INNER join feature_relationship fr
416 ON (p.feature_id = fr.object_id) INNER join feature c ON (c.feature_id = fr.subject_id)
417 INNER join cvterm ct ON (c.type_id = ct.cvterm_id)
418 WHERE pt.name = '' || quote_literal(ptype) || '' AND ct.name = '' || quote_literal(ctype)
419 || '' AND p.is_analysis = '' || quote_literal(is_an) || '' group by p.feature_id) as cq
420 ON (cq.feature_id = f.feature_id) WHERE cq.c '' || operator || ccount || '';'';
421 ---RAISE NOTICE ''%'', query;
422
423 FOR myrc IN SELECT * FROM get_feature_ids(query) LOOP
424 RETURN NEXT myrc;
425 END LOOP;
426 RETURN;
427 END;
428 '
429 LANGUAGE 'plpgsql';