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
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
|
CREATE OR REPLACE FUNCTION store_feature
(INT,INT,INT,INT,
INT,INT,VARCHAR,VARCHAR,INT,BOOLEAN)
RETURNS INT AS
'DECLARE
v_srcfeature_id ALIAS FOR $1;
v_fmin ALIAS FOR $2;
v_fmax ALIAS FOR $3;
v_strand ALIAS FOR $4;
v_dbxref_id ALIAS FOR $5;
v_organism_id ALIAS FOR $6;
v_name ALIAS FOR $7;
v_uniquename ALIAS FOR $8;
v_type_id ALIAS FOR $9;
v_is_analysis ALIAS FOR $10;
v_feature_id INT;
v_featureloc_id INT;
BEGIN
IF v_dbxref_id IS NULL THEN
SELECT INTO v_feature_id feature_id
FROM feature
WHERE uniquename=v_uniquename AND
organism_id=v_organism_id AND
type_id=v_type_id;
ELSE
SELECT INTO v_feature_id feature_id
FROM feature
WHERE dbxref_id=v_dbxref_id;
END IF;
IF NOT FOUND THEN
INSERT INTO feature
( dbxref_id ,
organism_id ,
name ,
uniquename ,
type_id ,
is_analysis )
VALUES
( v_dbxref_id ,
v_organism_id ,
v_name ,
v_uniquename ,
v_type_id ,
v_is_analysis );
v_feature_id = currval(''feature_feature_id_seq'');
ELSE
UPDATE feature SET
dbxref_id = v_dbxref_id ,
organism_id = v_organism_id ,
name = v_name ,
uniquename = v_uniquename ,
type_id = v_type_id ,
is_analysis = v_is_analysis
WHERE
feature_id=v_feature_id;
END IF;
PERFORM store_featureloc(v_feature_id,
v_srcfeature_id,
v_fmin,
v_fmax,
v_strand,
0,
0);
RETURN v_feature_id;
END;
' LANGUAGE 'plpgsql';
CREATE OR REPLACE FUNCTION store_featureloc
(INT,INT,INT,INT,INT,INT,INT)
RETURNS INT AS
'DECLARE
v_feature_id ALIAS FOR $1;
v_srcfeature_id ALIAS FOR $2;
v_fmin ALIAS FOR $3;
v_fmax ALIAS FOR $4;
v_strand ALIAS FOR $5;
v_rank ALIAS FOR $6;
v_locgroup ALIAS FOR $7;
v_featureloc_id INT;
BEGIN
IF v_feature_id IS NULL THEN RAISE EXCEPTION ''feature_id cannot be null'';
END IF;
SELECT INTO v_featureloc_id featureloc_id
FROM featureloc
WHERE feature_id=v_feature_id AND
rank=v_rank AND
locgroup=v_locgroup;
IF NOT FOUND THEN
INSERT INTO featureloc
( feature_id,
srcfeature_id,
fmin,
fmax,
strand,
rank,
locgroup)
VALUES
( v_feature_id,
v_srcfeature_id,
v_fmin,
v_fmax,
v_strand,
v_rank,
v_locgroup);
v_featureloc_id = currval(''featureloc_featureloc_id_seq'');
ELSE
UPDATE featureloc SET
feature_id = v_feature_id,
srcfeature_id = v_srcfeature_id,
fmin = v_fmin,
fmax = v_fmax,
strand = v_strand,
rank = v_rank,
locgroup = v_locgroup
WHERE
featureloc_id=v_featureloc_id;
END IF;
RETURN v_featureloc_id;
END;
' LANGUAGE 'plpgsql';
CREATE OR REPLACE FUNCTION store_feature_synonym
(INT,VARCHAR,INT,BOOLEAN,BOOLEAN,INT)
RETURNS INT AS
'DECLARE
v_feature_id ALIAS FOR $1;
v_syn ALIAS FOR $2;
v_type_id ALIAS FOR $3;
v_is_current ALIAS FOR $4;
v_is_internal ALIAS FOR $5;
v_pub_id ALIAS FOR $6;
v_synonym_id INT;
v_feature_synonym_id INT;
BEGIN
IF v_feature_id IS NULL THEN RAISE EXCEPTION ''feature_id cannot be null'';
END IF;
SELECT INTO v_synonym_id synonym_id
FROM synonym
WHERE name=v_syn AND
type_id=v_type_id;
IF NOT FOUND THEN
INSERT INTO synonym
( name,
synonym_sgml,
type_id)
VALUES
( v_syn,
v_syn,
v_type_id);
v_synonym_id = currval(''synonym_synonym_id_seq'');
END IF;
SELECT INTO v_feature_synonym_id feature_synonym_id
FROM feature_synonym
WHERE feature_id=v_feature_id AND
synonym_id=v_synonym_id AND
pub_id=v_pub_id;
IF NOT FOUND THEN
INSERT INTO feature_synonym
( feature_id,
synonym_id,
pub_id,
is_current,
is_internal)
VALUES
( v_feature_id,
v_synonym_id,
v_pub_id,
v_is_current,
v_is_internal);
v_feature_synonym_id = currval(''feature_synonym_feature_synonym_id_seq'');
ELSE
UPDATE feature_synonym
SET is_current=v_is_current, is_internal=v_is_internal
WHERE feature_synonym_id=v_feature_synonym_id;
END IF;
RETURN v_feature_synonym_id;
END;
' LANGUAGE 'plpgsql';
|
