bxiykX7t

1CREATE TABLE IF NOT EXISTS settings(
2    id SERIAL PRIMARY KEY,
3    namespace character varying,
4    code character varying NOT NULL,
5    value character varying NOT NULL
6);
7
8CREATE TABLE IF NOT EXISTS device_settings(
9    device_id integer NOT NULL,
10    setting_id smallint NOT NULL,
11    CONSTRAINT device_settings_pk PRIMARY KEY (device_id, setting_id),
12    CONSTRAINT device_settings_to_settings_fk FOREIGN KEY (setting_id) REFERENCES settings (id) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE NO ACTION
13);
14
15--
16-- Function: get_setting(code, namespace)
17--
18
19CREATE OR REPLACE FUNCTION get_setting(_code character varying, _namespace character varying DEFAULT '') RETURNS TABLE(value character varying)
20    LANGUAGE plpgsql
21    AS $$
22    BEGIN
23	RETURN QUERY
24	select
25		s.value
26	from settings s
27	where
28		s.code = _code and
29		s.namespace = _namespace
30	order by s.id desc;
31    END;
32$$;
33
34
35ALTER FUNCTION public.get_setting(_code character varying, _namespace character varying) OWNER TO postgres;
36
37--
38-- Function: get_device_setting(device_id, code, namespace)
39--
40
41CREATE OR REPLACE FUNCTION get_device_setting(_device_id integer, _code character varying, _namespace character varying DEFAULT '') RETURNS TABLE(value character varying)
42    LANGUAGE plpgsql
43    AS $$
44    BEGIN
45	RETURN QUERY
46	select
47	    s.value
48	from device_settings ds
49	    left join settings s on ds.setting_id = s.id
50	where
51	    ds.device_id = _device_id and
52	    s.code = _code and
53	    s.namespace = _namespace
54	order by s.id desc;
55    END;
56$$;
57
58
59ALTER FUNCTION public.get_device_setting(_device_id integer, _code character varying, _namespace character varying) OWNER TO postgres;