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
|
set client_min_messages = 'warning';
-- VERSION 1.3.0
BEGIN;
DROP TYPE IF EXISTS location_class_item CASCADE;
CREATE TYPE location_class_item AS (
id int,
class text,
authoritative bool,
entity_classes int[]
);
DROP FUNCTION IF EXISTS location_list_class();
CREATE OR REPLACE FUNCTION location_list_class()
RETURNS SETOF location_class_item AS
$$
SELECT l.*, as_array(e.entity_class)
FROM location_class l
JOIN location_class_to_entity_class e
ON (l.id = e.location_class)
GROUP BY l.id, l.class, l.authoritative
ORDER BY l.id;
$$ language sql;
COMMENT ON FUNCTION location_list_class() IS
$$ Lists location classes, by default in order entered.$$;
CREATE OR REPLACE FUNCTION location_list_country()
RETURNS SETOF country AS
$$
SELECT * FROM country ORDER BY name;
$$ language sql;
COMMENT ON FUNCTION location_list_country() IS
$$ Lists countries, by default in alphabetical order.$$;
CREATE OR REPLACE FUNCTION location_save
(in_location_id int, in_address1 text, in_address2 text, in_address3 text,
in_city text, in_state text, in_zipcode text, in_country int)
returns integer AS
$$
DECLARE
location_id integer;
location_row RECORD;
BEGIN
IF in_location_id IS NULL THEN
SELECT id INTO location_id FROM location
WHERE line_one = in_address1 AND line_two = in_address2
AND line_three = in_address3 AND in_city = city
AND in_state = state AND in_zipcode = mail_code
AND in_country = country_id
LIMIT 1;
IF NOT FOUND THEN
-- Straight insert.
location_id = nextval('location_id_seq');
INSERT INTO location (
id,
line_one,
line_two,
line_three,
city,
state,
mail_code,
country_id)
VALUES (
location_id,
in_address1,
in_address2,
in_address3,
in_city,
in_state,
in_zipcode,
in_country
);
END IF;
return location_id;
ELSE
RAISE NOTICE 'Overwriting location id %', in_location_id;
-- Test it.
SELECT * INTO location_row FROM location WHERE id = in_location_id;
IF NOT FOUND THEN
-- Tricky users are lying to us.
RAISE EXCEPTION 'location_save called with nonexistant location ID %', in_location_id;
ELSE
-- Okay, we're good.
UPDATE location SET
line_one = in_address1,
line_two = in_address2,
line_three = in_address3,
city = in_city,
state = in_state,
mail_code = in_zipcode,
country_id = in_country
WHERE id = in_location_id;
return in_location_id;
END IF;
END IF;
END;
$$ LANGUAGE PLPGSQL;
COMMENT ON function location_save
(in_location_id int, in_address1 text, in_address2 text, in_address3 text,
in_city text, in_state text, in_zipcode text, in_country int) IS
$$ Note that this does NOT override the data in the database unless in_location_id is specified.
Instead we search for locations matching the desired specifications and if none
are found, we insert one. Either way, the return value of the location can be
used for mapping to other things. This is necessary because locations are
only loosly coupled with entities, etc.$$;
CREATE OR REPLACE FUNCTION location__get (in_id integer) returns location AS
$$
SELECT * FROM location WHERE id = in_id;
$$ language sql;
COMMENT ON FUNCTION location__get (in_id integer) IS
$$ Returns the location specified by in_id.$$;
CREATE OR REPLACE FUNCTION location_delete (in_id integer) RETURNS VOID AS
$$
DELETE FROM location WHERE id = in_id;
$$ language sql;
COMMENT ON FUNCTION location_delete (in_id integer)
IS $$ DELETES the location specified by in_id. Does not return a value.$$;
DROP TYPE IF EXISTS location_result CASCADE;
CREATE TYPE location_result AS (
id int,
line_one text,
line_two text,
line_three text,
city text,
state text,
mail_code text,
country_id int,
country text,
location_class int,
class text
);
update defaults set value = 'yes' where setting_key = 'module_load_ok';
COMMIT;
|
