Debian Codegraph @

modules/sequence/functions/subsequence.plpgsql

raw
1 -- dependency_on: [sequtil,sequence-cv-helper]
2
3 CREATE OR REPLACE FUNCTION subsequence(bigint,bigint,bigint,INT)
4 RETURNS TEXT AS
5 'SELECT
6 CASE WHEN $4<0
7 THEN reverse_complement(substring(srcf.residues,CAST(($2+1) as int),CAST(($3-$2) as int)))
8 ELSE substring(residues,CAST(($2+1) as int),CAST(($3-$2) as int))
9 END AS residues
10 FROM feature AS srcf
11 WHERE
12 srcf.feature_id=$1'
13 LANGUAGE 'sql';
14
15 CREATE OR REPLACE FUNCTION subsequence_by_featureloc(bigint)
16 RETURNS TEXT AS
17 'SELECT
18 CASE WHEN strand<0
19 THEN reverse_complement(substring(srcf.residues,CAST(fmin+1 as int),CAST((fmax-fmin) as int)))
20 ELSE substring(srcf.residues,CAST(fmin+1 as int),CAST((fmax-fmin) as int))
21 END AS residues
22 FROM feature AS srcf
23 INNER JOIN featureloc ON (srcf.feature_id=featureloc.srcfeature_id)
24 WHERE
25 featureloc_id=$1'
26 LANGUAGE 'sql';
27
28 CREATE OR REPLACE FUNCTION subsequence_by_feature(bigint,INT,INT)
29 RETURNS TEXT AS
30 'SELECT
31 CASE WHEN strand<0
32 THEN reverse_complement(substring(srcf.residues,CAST(fmin+1 as int),CAST((fmax-fmin) as int)))
33 ELSE substring(srcf.residues,CAST(fmin+1 as int),CAST((fmax-fmin) as int))
34 END AS residues
35 FROM feature AS srcf
36 INNER JOIN featureloc ON (srcf.feature_id=featureloc.srcfeature_id)
37 WHERE
38 featureloc.feature_id=$1 AND
39 featureloc.rank=$2 AND
40 featureloc.locgroup=$3'
41 LANGUAGE 'sql';
42
43 CREATE OR REPLACE FUNCTION subsequence_by_feature(bigint)
44 RETURNS TEXT AS 'SELECT subsequence_by_feature($1,0,0)'
45 LANGUAGE 'sql';
46
47 -- based on subfeature sets:
48
49 -- constrained by feature_relationship.type_id
50 -- (allows user to construct queries that only get subsequences of
51 -- part_of subfeatures)
52
53 CREATE OR REPLACE FUNCTION subsequence_by_subfeatures(bigint,bigint,INT,INT)
54 RETURNS TEXT AS '
55 DECLARE v_feature_id ALIAS FOR $1;
56 DECLARE v_rtype_id ALIAS FOR $2;
57 DECLARE v_rank ALIAS FOR $3;
58 DECLARE v_locgroup ALIAS FOR $4;
59 DECLARE subseq TEXT;
60 DECLARE seqrow RECORD;
61 BEGIN
62 subseq = '''';
63 FOR seqrow IN
64 SELECT
65 CASE WHEN strand<0
66 THEN reverse_complement(substring(srcf.residues,CAST(fmin+1 as int),CAST((fmax-fmin) as int)))
67 ELSE substring(srcf.residues,CAST(fmin+1 as int),CAST((fmax-fmin) as int))
68 END AS residues
69 FROM feature AS srcf
70 INNER JOIN featureloc ON (srcf.feature_id=featureloc.srcfeature_id)
71 INNER JOIN feature_relationship AS fr
72 ON (fr.subject_id=featureloc.feature_id)
73 WHERE
74 fr.object_id=v_feature_id AND
75 fr.type_id=v_rtype_id AND
76 featureloc.rank=v_rank AND
77 featureloc.locgroup=v_locgroup
78 ORDER BY fr.rank
79 LOOP
80 subseq = subseq || seqrow.residues;
81 END LOOP;
82 RETURN subseq;
83 END
84 '
85 LANGUAGE 'plpgsql';
86
87 CREATE OR REPLACE FUNCTION subsequence_by_subfeatures(bigint,bigint)
88 RETURNS TEXT AS
89 'SELECT subsequence_by_subfeatures($1,$2,0,0)'
90 LANGUAGE 'sql';
91
92 CREATE OR REPLACE FUNCTION subsequence_by_subfeatures(bigint)
93 RETURNS TEXT AS
94 '
95 SELECT subsequence_by_subfeatures($1,get_feature_relationship_type_id(''part_of''),0,0)
96 '
97 LANGUAGE 'sql';
98
99
100 -- constrained by subfeature.type_id (eg exons of a transcript)
101 CREATE OR REPLACE FUNCTION subsequence_by_typed_subfeatures(bigint,bigint,INT,INT)
102 RETURNS TEXT AS '
103 DECLARE v_feature_id ALIAS FOR $1;
104 DECLARE v_ftype_id ALIAS FOR $2;
105 DECLARE v_rank ALIAS FOR $3;
106 DECLARE v_locgroup ALIAS FOR $4;
107 DECLARE subseq TEXT;
108 DECLARE seqrow RECORD;
109 BEGIN
110 subseq = '''';
111 FOR seqrow IN
112 SELECT
113 CASE WHEN strand<0
114 THEN reverse_complement(substring(srcf.residues,CAST(fmin+1 as int),CAST((fmax-fmin) as int)))
115 ELSE substring(srcf.residues,CAST(fmin+1 as int),CAST((fmax-fmin) as int))
116 END AS residues
117 FROM feature AS srcf
118 INNER JOIN featureloc ON (srcf.feature_id=featureloc.srcfeature_id)
119 INNER JOIN feature AS subf ON (subf.feature_id=featureloc.feature_id)
120 INNER JOIN feature_relationship AS fr ON (fr.subject_id=subf.feature_id)
121 WHERE
122 fr.object_id=v_feature_id AND
123 subf.type_id=v_ftype_id AND
124 featureloc.rank=v_rank AND
125 featureloc.locgroup=v_locgroup
126 ORDER BY fr.rank
127 LOOP
128 subseq = subseq || seqrow.residues;
129 END LOOP;
130 RETURN subseq;
131 END
132 '
133 LANGUAGE 'plpgsql';
134
135 CREATE OR REPLACE FUNCTION subsequence_by_typed_subfeatures(bigint,bigint)
136 RETURNS TEXT AS
137 'SELECT subsequence_by_typed_subfeatures($1,$2,0,0)'
138 LANGUAGE 'sql';
139
140
141
142