Commit | Line | Data |
---|---|---|
494792ab JM |
1 | -- --------------------------------------------------------- |
2 | -- envs.sql | |
3 | ||
4 | CREATE SCHEMA envs | |
5 | -- env table | |
6 | CREATE SEQUENCE env_id_seq | |
7 | CREATE TABLE env ( | |
8 | env_id integer NOT NULL DEFAULT nextval('envs.env_id_seq'), | |
9 | outer_id integer, | |
10 | data hstore | |
11 | ); | |
12 | ||
13 | ALTER TABLE envs.env ADD CONSTRAINT pk_env_id | |
14 | PRIMARY KEY (env_id); | |
15 | -- drop sequence when table dropped | |
16 | ALTER SEQUENCE envs.env_id_seq OWNED BY envs.env.env_id; | |
17 | ALTER TABLE envs.env ADD CONSTRAINT fk_env_outer_id | |
18 | FOREIGN KEY (outer_id) REFERENCES envs.env(env_id); | |
19 | ||
20 | -- ----------------------- | |
21 | ||
22 | -- envs.new | |
23 | CREATE FUNCTION envs.new(outer_env integer) RETURNS integer AS $$ | |
24 | DECLARE | |
25 | e integer; | |
26 | BEGIN | |
27 | INSERT INTO envs.env (outer_id) VALUES (outer_env) | |
28 | RETURNING env_id INTO e; | |
29 | --RAISE NOTICE 'env_new: e: %, outer_env: %', e, outer_env; | |
30 | RETURN e; | |
31 | END; $$ LANGUAGE plpgsql; | |
32 | ||
33 | -- envs.new with bindings | |
34 | CREATE FUNCTION envs.new(outer_env integer, | |
35 | binds integer, | |
36 | exprs integer[]) | |
37 | RETURNS integer AS $$ | |
38 | DECLARE | |
39 | bseq integer[]; | |
40 | env integer; | |
41 | i integer; | |
42 | bind integer; | |
43 | bsym varchar; | |
44 | expr integer; | |
45 | BEGIN | |
46 | env := envs.new(outer_env); | |
47 | bseq := types._valueToArray(binds); | |
48 | FOR i IN 1 .. COALESCE(array_length(bseq, 1), 0) LOOP | |
49 | bind := bseq[i]; | |
50 | bsym := types._valueToString(bind); | |
51 | expr := exprs[i]; | |
52 | --RAISE NOTICE 'i: %, bind: %, expr: %', i, bind, expr; | |
53 | IF bsym = '&' THEN | |
54 | bind := bseq[i+1]; | |
55 | PERFORM envs.set(env, bind, | |
56 | types._list(exprs[i:array_length(exprs, 1)])); | |
57 | RETURN env; | |
58 | END IF; | |
59 | PERFORM envs.vset(env, bsym, expr); | |
60 | END LOOP; | |
61 | RETURN env; | |
62 | END; $$ LANGUAGE plpgsql; | |
63 | ||
64 | ||
65 | -- envs.vset | |
66 | -- like envs.set but takes a varchar key instead of value_id | |
67 | CREATE FUNCTION envs.vset(env integer, name varchar, val integer) | |
68 | RETURNS integer AS $$ | |
69 | DECLARE | |
70 | e integer = env; | |
71 | d hstore; | |
72 | BEGIN | |
73 | SELECT data INTO d FROM envs.env WHERE env_id=e; | |
74 | IF d IS NULL THEN | |
75 | d := hstore(name, CAST(val AS varchar)); | |
76 | ELSE | |
77 | d := d || hstore(name, CAST(val AS varchar)); | |
78 | END IF; | |
79 | UPDATE envs.env SET data = d WHERE env_id=e; | |
80 | RETURN val; | |
81 | END; $$ LANGUAGE plpgsql; | |
82 | ||
83 | ||
84 | -- envs.set | |
85 | CREATE FUNCTION envs.set(env integer, key integer, val integer) | |
86 | RETURNS integer AS $$ | |
87 | DECLARE | |
88 | symkey varchar; | |
89 | BEGIN | |
90 | symkey := types._valueToString(key); | |
91 | RETURN envs.vset(env, symkey, val); | |
92 | END; $$ LANGUAGE plpgsql; | |
93 | ||
94 | -- envs.find | |
95 | CREATE FUNCTION envs.find(env integer, symkey varchar) RETURNS integer AS $$ | |
96 | DECLARE | |
97 | outer_id integer; | |
98 | d hstore; | |
99 | val integer; | |
100 | BEGIN | |
101 | SELECT e.data, e.outer_id INTO d, outer_id FROM envs.env e | |
102 | WHERE e.env_id = env; | |
103 | IF d ? symkey THEN | |
104 | RETURN env; | |
105 | ELSIF outer_id IS NOT NULL THEN | |
106 | RETURN envs.find(outer_id, symkey); | |
107 | ELSE | |
108 | RETURN NULL; | |
109 | END IF; | |
110 | END; $$ LANGUAGE plpgsql; | |
111 | ||
112 | ||
113 | -- envs.vget | |
114 | CREATE FUNCTION envs.vget(env integer, symkey varchar) RETURNS integer AS $$ | |
115 | DECLARE | |
116 | result integer; | |
117 | e integer; | |
118 | BEGIN | |
119 | e := envs.find(env, symkey); | |
120 | --RAISE NOTICE 'envs.find env: %, symkey: % -> e: %', env, symkey, e; | |
121 | IF e IS NULL THEN | |
122 | RAISE EXCEPTION '''%'' not found', symkey; | |
123 | ELSE | |
124 | SELECT data -> symkey INTO result FROM envs.env WHERE env_id = e; | |
125 | END IF; | |
126 | RETURN result; | |
127 | END; $$ LANGUAGE plpgsql; | |
128 | ||
129 | -- envs.get | |
130 | CREATE FUNCTION envs.get(env integer, key integer) RETURNS integer AS $$ | |
131 | BEGIN | |
132 | RETURN envs.vget(env, types._valueToString(key)); | |
133 | END; $$ LANGUAGE plpgsql; |