| 1 |
-- down the graph: eg from chromosome to contig |
| 2 |
CREATE OR REPLACE FUNCTION project_point_up(bigint,bigint,bigint,bigint) |
| 3 |
RETURNS bigint AS |
| 4 |
'SELECT |
| 5 |
CASE WHEN $4<0 |
| 6 |
THEN $3-$1 -- rev strand |
| 7 |
ELSE $1-$2 -- fwd strand |
| 8 |
END AS p' |
| 9 |
LANGUAGE 'sql'; |
| 10 |
|
| 11 |
-- down the graph: eg from contig to chromosome |
| 12 |
CREATE OR REPLACE FUNCTION project_point_down(bigint,bigint,bigint,bigint) |
| 13 |
RETURNS bigint AS |
| 14 |
'SELECT |
| 15 |
CASE WHEN $4<0 |
| 16 |
THEN $3-$1 |
| 17 |
ELSE $1+$2 |
| 18 |
END AS p' |
| 19 |
LANGUAGE 'sql'; |
| 20 |
|
| 21 |
CREATE OR REPLACE FUNCTION project_featureloc_up(bigint,bigint) |
| 22 |
RETURNS featureloc AS |
| 23 |
' |
| 24 |
DECLARE |
| 25 |
in_featureloc_id alias for $1; |
| 26 |
up_srcfeature_id alias for $2; |
| 27 |
in_featureloc featureloc%ROWTYPE; |
| 28 |
up_featureloc featureloc%ROWTYPE; |
| 29 |
nu_featureloc featureloc%ROWTYPE; |
| 30 |
nu_fmin BIGINT; |
| 31 |
nu_fmax BIGINT; |
| 32 |
nu_strand INT; |
| 33 |
BEGIN |
| 34 |
SELECT INTO in_featureloc |
| 35 |
featureloc.* |
| 36 |
FROM featureloc |
| 37 |
WHERE featureloc_id = in_featureloc_id; |
| 38 |
|
| 39 |
SELECT INTO up_featureloc |
| 40 |
up_fl.* |
| 41 |
FROM featureloc AS in_fl |
| 42 |
INNER JOIN featureloc AS up_fl |
| 43 |
ON (in_fl.srcfeature_id = up_fl.feature_id) |
| 44 |
WHERE |
| 45 |
in_fl.featureloc_id = in_featureloc_id AND |
| 46 |
up_fl.srcfeature_id = up_srcfeature_id; |
| 47 |
|
| 48 |
IF up_featureloc.strand IS NULL |
| 49 |
THEN RETURN NULL; |
| 50 |
END IF; |
| 51 |
|
| 52 |
IF up_featureloc.strand < 0 |
| 53 |
THEN |
| 54 |
nu_fmin = project_point_up(in_featureloc.fmax, |
| 55 |
up_featureloc.fmin,up_featureloc.fmax,-1); |
| 56 |
nu_fmax = project_point_up(in_featureloc.fmin, |
| 57 |
up_featureloc.fmin,up_featureloc.fmax,-1); |
| 58 |
nu_strand = -in_featureloc.strand; |
| 59 |
ELSE |
| 60 |
nu_fmin = project_point_up(in_featureloc.fmin, |
| 61 |
up_featureloc.fmin,up_featureloc.fmax,1); |
| 62 |
nu_fmax = project_point_up(in_featureloc.fmax, |
| 63 |
up_featureloc.fmin,up_featureloc.fmax,1); |
| 64 |
nu_strand = in_featureloc.strand; |
| 65 |
END IF; |
| 66 |
in_featureloc.fmin = nu_fmin; |
| 67 |
in_featureloc.fmax = nu_fmax; |
| 68 |
in_featureloc.strand = nu_strand; |
| 69 |
in_featureloc.srcfeature_id = up_featureloc.srcfeature_id; |
| 70 |
RETURN in_featureloc; |
| 71 |
END |
| 72 |
' |
| 73 |
LANGUAGE 'plpgsql'; |
| 74 |
|
| 75 |
CREATE OR REPLACE FUNCTION project_point_g2t(bigint,bigint,bigint) |
| 76 |
RETURNS BIGINT AS ' |
| 77 |
DECLARE |
| 78 |
in_p alias for $1; |
| 79 |
srcf_id alias for $2; |
| 80 |
t_id alias for $3; |
| 81 |
e_floc featureloc%ROWTYPE; |
| 82 |
out_p BIGINT; |
| 83 |
exon_cvterm_id BIGINT; |
| 84 |
BEGIN |
| 85 |
SELECT INTO exon_cvterm_id get_feature_type_id(''exon''); |
| 86 |
SELECT INTO out_p |
| 87 |
CASE |
| 88 |
WHEN strand<0 THEN fmax-p |
| 89 |
ELSE p-fmin |
| 90 |
END AS p |
| 91 |
FROM featureloc |
| 92 |
INNER JOIN feature USING (feature_id) |
| 93 |
INNER JOIN feature_relationship ON (feature.feature_id=subject_id) |
| 94 |
WHERE |
| 95 |
object_id = t_id AND |
| 96 |
feature.type_id = exon_cvterm_id AND |
| 97 |
featureloc.srcfeature_id = srcf_id AND |
| 98 |
in_p >= fmin AND |
| 99 |
in_p <= fmax; |
| 100 |
RETURN in_featureloc; |
| 101 |
END |
| 102 |
' |
| 103 |
LANGUAGE 'plpgsql'; |
| 104 |
|