CREATE OR REPLACE FUNCTION feature_subalignments(bigint) RETURNS SETOF featureloc AS ' DECLARE return_data featureloc%ROWTYPE; f_id ALIAS FOR $1; feature_data feature%rowtype; featureloc_data featureloc%rowtype; s text; fmin bigint; slen bigint; BEGIN --RAISE NOTICE ''feature_id is %'', featureloc_data.feature_id; SELECT INTO feature_data * FROM feature WHERE feature_id = f_id; FOR featureloc_data IN SELECT * FROM featureloc WHERE feature_id = f_id LOOP --RAISE NOTICE ''fmin is %'', featureloc_data.fmin; return_data.feature_id = f_id; return_data.srcfeature_id = featureloc_data.srcfeature_id; return_data.is_fmin_partial = featureloc_data.is_fmin_partial; return_data.is_fmax_partial = featureloc_data.is_fmax_partial; return_data.strand = featureloc_data.strand; return_data.phase = featureloc_data.phase; return_data.residue_info = featureloc_data.residue_info; return_data.locgroup = featureloc_data.locgroup; return_data.rank = featureloc_data.rank; s = feature_data.residues; fmin = featureloc_data.fmin; slen = char_length(s); WHILE char_length(s) LOOP --RAISE NOTICE ''residues is %'', s; --trim off leading match s = trim(leading ''|ATCGNatcgn'' from s); --if leading match detected IF slen > char_length(s) THEN return_data.fmin = fmin; return_data.fmax = featureloc_data.fmin + (slen - char_length(s)); --if the string started with a match, return it, --otherwise, trim the gaps first (ie do not return this iteration) RETURN NEXT return_data; END IF; --trim off leading gap s = trim(leading ''-'' from s); fmin = featureloc_data.fmin + (slen - char_length(s)); END LOOP; END LOOP; RETURN; END; ' LANGUAGE 'plpgsql';