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
|
CREATE OR REPLACE FUNCTION store_organism (VARCHAR,VARCHAR,VARCHAR)
RETURNS INT AS
'DECLARE
v_genus ALIAS FOR $1;
v_species ALIAS FOR $2;
v_common_name ALIAS FOR $3;
v_organism_id INTEGER;
BEGIN
SELECT INTO v_organism_id organism_id
FROM organism
WHERE genus=v_genus AND
species=v_species;
IF NOT FOUND THEN
INSERT INTO organism
(genus,species,common_name)
VALUES
(v_genus,v_species,v_common_name);
RETURN currval(''organism_organism_id_seq'');
ELSE
UPDATE organism
SET common_name=v_common_name
WHERE organism_id = v_organism_id;
END IF;
RETURN v_organism_id;
END;
' LANGUAGE 'plpgsql';
|
