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
|
-- dependency_on: [sequtil,sequence-cv-helper]
CREATE OR REPLACE FUNCTION subsequence(INT,INT,INT,INT)
RETURNS TEXT AS
'SELECT
CASE WHEN $4<0
THEN reverse_complement(substring(srcf.residues,$2+1,($3-$2)))
ELSE substring(residues,$2+1,($3-$2))
END AS residues
FROM feature AS srcf
WHERE
srcf.feature_id=$1'
LANGUAGE 'sql';
CREATE OR REPLACE FUNCTION subsequence_by_featureloc(INT)
RETURNS TEXT AS
'SELECT
CASE WHEN strand<0
THEN reverse_complement(substring(srcf.residues,fmin+1,(fmax-fmin)))
ELSE substring(srcf.residues,fmin+1,(fmax-fmin))
END AS residues
FROM feature AS srcf
INNER JOIN featureloc ON (srcf.feature_id=featureloc.srcfeature_id)
WHERE
featureloc_id=$1'
LANGUAGE 'sql';
CREATE OR REPLACE FUNCTION subsequence_by_feature(INT,INT,INT)
RETURNS TEXT AS
'SELECT
CASE WHEN strand<0
THEN reverse_complement(substring(srcf.residues,fmin+1,(fmax-fmin)))
ELSE substring(srcf.residues,fmin+1,(fmax-fmin))
END AS residues
FROM feature AS srcf
INNER JOIN featureloc ON (srcf.feature_id=featureloc.srcfeature_id)
WHERE
featureloc.feature_id=$1 AND
featureloc.rank=$2 AND
featureloc.locgroup=$3'
LANGUAGE 'sql';
CREATE OR REPLACE FUNCTION subsequence_by_feature(INT)
RETURNS TEXT AS 'SELECT subsequence_by_feature($1,0,0)'
LANGUAGE 'sql';
-- based on subfeature sets:
-- constrained by feature_relationship.type_id
-- (allows user to construct queries that only get subsequences of
-- part_of subfeatures)
CREATE OR REPLACE FUNCTION subsequence_by_subfeatures(INT,INT,INT,INT)
RETURNS TEXT AS '
DECLARE v_feature_id ALIAS FOR $1;
DECLARE v_rtype_id ALIAS FOR $2;
DECLARE v_rank ALIAS FOR $3;
DECLARE v_locgroup ALIAS FOR $4;
DECLARE subseq TEXT;
DECLARE seqrow RECORD;
BEGIN
subseq = '''';
FOR seqrow IN
SELECT
CASE WHEN strand<0
THEN reverse_complement(substring(srcf.residues,fmin+1,(fmax-fmin)))
ELSE substring(srcf.residues,fmin+1,(fmax-fmin))
END AS residues
FROM feature AS srcf
INNER JOIN featureloc ON (srcf.feature_id=featureloc.srcfeature_id)
INNER JOIN feature_relationship AS fr
ON (fr.subject_id=featureloc.feature_id)
WHERE
fr.object_id=v_feature_id AND
fr.type_id=v_rtype_id AND
featureloc.rank=v_rank AND
featureloc.locgroup=v_locgroup
ORDER BY fr.rank
LOOP
subseq = subseq || seqrow.residues;
END LOOP;
RETURN subseq;
END
'
LANGUAGE 'plpgsql';
CREATE OR REPLACE FUNCTION subsequence_by_subfeatures(INT,INT)
RETURNS TEXT AS
'SELECT subsequence_by_subfeatures($1,$2,0,0)'
LANGUAGE 'sql';
CREATE OR REPLACE FUNCTION subsequence_by_subfeatures(INT)
RETURNS TEXT AS
'
SELECT subsequence_by_subfeatures($1,get_feature_relationship_type_id(''part_of''),0,0)
'
LANGUAGE 'sql';
-- constrained by subfeature.type_id (eg exons of a transcript)
CREATE OR REPLACE FUNCTION subsequence_by_typed_subfeatures(INT,INT,INT,INT)
RETURNS TEXT AS '
DECLARE v_feature_id ALIAS FOR $1;
DECLARE v_ftype_id ALIAS FOR $2;
DECLARE v_rank ALIAS FOR $3;
DECLARE v_locgroup ALIAS FOR $4;
DECLARE subseq TEXT;
DECLARE seqrow RECORD;
BEGIN
subseq = '''';
FOR seqrow IN
SELECT
CASE WHEN strand<0
THEN reverse_complement(substring(srcf.residues,fmin+1,(fmax-fmin)))
ELSE substring(srcf.residues,fmin+1,(fmax-fmin))
END AS residues
FROM feature AS srcf
INNER JOIN featureloc ON (srcf.feature_id=featureloc.srcfeature_id)
INNER JOIN feature AS subf ON (subf.feature_id=featureloc.feature_id)
INNER JOIN feature_relationship AS fr ON (fr.subject_id=subf.feature_id)
WHERE
fr.object_id=v_feature_id AND
subf.type_id=v_ftype_id AND
featureloc.rank=v_rank AND
featureloc.locgroup=v_locgroup
ORDER BY fr.rank
LOOP
subseq = subseq || seqrow.residues;
END LOOP;
RETURN subseq;
END
'
LANGUAGE 'plpgsql';
CREATE OR REPLACE FUNCTION subsequence_by_typed_subfeatures(INT,INT)
RETURNS TEXT AS
'SELECT subsequence_by_typed_subfeatures($1,$2,0,0)'
LANGUAGE 'sql';
|
