-- down the graph: eg from chromosome to contig CREATE OR REPLACE FUNCTION project_point_up(bigint,bigint,bigint,bigint) RETURNS bigint 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(bigint,bigint,bigint,bigint) RETURNS bigint AS 'SELECT CASE WHEN $4<0 THEN $3-$1 ELSE $1+$2 END AS p' LANGUAGE 'sql'; CREATE OR REPLACE FUNCTION project_featureloc_up(bigint,bigint) 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 BIGINT; nu_fmax BIGINT; 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(bigint,bigint,bigint) RETURNS BIGINT AS ' DECLARE in_p alias for $1; srcf_id alias for $2; t_id alias for $3; e_floc featureloc%ROWTYPE; out_p BIGINT; exon_cvterm_id BIGINT; 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';