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
|
-- down the graph: eg from chromosome to contig
CREATE OR REPLACE FUNCTION project_point_up(int,int,int,int)
RETURNS int AS
'SELECT
CASE WHEN $4<0
THEN $3-$1 -- rev strand
ELSE $1-$2 -- fwd strand
END AS p'
LANGUAGE 'sql';
-- down the graph: eg from contig to chromosome
CREATE OR REPLACE FUNCTION project_point_down(int,int,int,int)
RETURNS int AS
'SELECT
CASE WHEN $4<0
THEN $3-$1
ELSE $1+$2
END AS p'
LANGUAGE 'sql';
CREATE OR REPLACE FUNCTION project_featureloc_up(int,int)
RETURNS featureloc AS
'
DECLARE
in_featureloc_id alias for $1;
up_srcfeature_id alias for $2;
in_featureloc featureloc%ROWTYPE;
up_featureloc featureloc%ROWTYPE;
nu_featureloc featureloc%ROWTYPE;
nu_fmin INT;
nu_fmax INT;
nu_strand INT;
BEGIN
SELECT INTO in_featureloc
featureloc.*
FROM featureloc
WHERE featureloc_id = in_featureloc_id;
SELECT INTO up_featureloc
up_fl.*
FROM featureloc AS in_fl
INNER JOIN featureloc AS up_fl
ON (in_fl.srcfeature_id = up_fl.feature_id)
WHERE
in_fl.featureloc_id = in_featureloc_id AND
up_fl.srcfeature_id = up_srcfeature_id;
IF up_featureloc.strand IS NULL
THEN RETURN NULL;
END IF;
IF up_featureloc.strand < 0
THEN
nu_fmin = project_point_up(in_featureloc.fmax,
up_featureloc.fmin,up_featureloc.fmax,-1);
nu_fmax = project_point_up(in_featureloc.fmin,
up_featureloc.fmin,up_featureloc.fmax,-1);
nu_strand = -in_featureloc.strand;
ELSE
nu_fmin = project_point_up(in_featureloc.fmin,
up_featureloc.fmin,up_featureloc.fmax,1);
nu_fmax = project_point_up(in_featureloc.fmax,
up_featureloc.fmin,up_featureloc.fmax,1);
nu_strand = in_featureloc.strand;
END IF;
in_featureloc.fmin = nu_fmin;
in_featureloc.fmax = nu_fmax;
in_featureloc.strand = nu_strand;
in_featureloc.srcfeature_id = up_featureloc.srcfeature_id;
RETURN in_featureloc;
END
'
LANGUAGE 'plpgsql';
CREATE OR REPLACE FUNCTION project_point_g2t(int,int,int)
RETURNS INT AS '
DECLARE
in_p alias for $1;
srcf_id alias for $2;
t_id alias for $3;
e_floc featureloc%ROWTYPE;
out_p INT;
exon_cvterm_id INT;
BEGIN
SELECT INTO exon_cvterm_id get_feature_type_id(''exon'');
SELECT INTO out_p
CASE
WHEN strand<0 THEN fmax-p
ELSE p-fmin
END AS p
FROM featureloc
INNER JOIN feature USING (feature_id)
INNER JOIN feature_relationship ON (feature.feature_id=subject_id)
WHERE
object_id = t_id AND
feature.type_id = exon_cvterm_id AND
featureloc.srcfeature_id = srcf_id AND
in_p >= fmin AND
in_p <= fmax;
RETURN in_featureloc;
END
'
LANGUAGE 'plpgsql';
|
