· 7 years ago · Nov 13, 2018, 05:02 PM
1--
2-- PostgreSQL database dump
3--
4
5-- Dumped from database version 9.6.6
6-- Dumped by pg_dump version 9.6.6
7
8SET statement_timeout = 0;
9SET lock_timeout = 0;
10SET idle_in_transaction_session_timeout = 0;
11SET client_encoding = 'UTF8';
12SET standard_conforming_strings = on;
13SET check_function_bodies = false;
14SET client_min_messages = warning;
15SET row_security = off;
16
17--
18-- Name: plpgsql; Type: EXTENSION; Schema: -; Owner:
19--
20
21CREATE EXTENSION IF NOT EXISTS plpgsql WITH SCHEMA pg_catalog;
22
23
24--
25-- Name: EXTENSION plpgsql; Type: COMMENT; Schema: -; Owner:
26--
27
28COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language';
29
30
31SET search_path = public, pg_catalog;
32
33--
34-- Name: updateuserraiting(); Type: FUNCTION; Schema: public; Owner: me
35--
36
37CREATE FUNCTION updateuserraiting() RETURNS trigger
38 LANGUAGE plpgsql
39 AS $$
40BEGIN
41 update users set raiting=raiting+NEW.points where id=NEW.userid;
42 return NULL;
43END; $$;
44
45
46ALTER FUNCTION public.updateuserraiting() OWNER TO me;
47
48SET default_tablespace = '';
49
50SET default_with_oids = false;
51
52--
53-- Name: games; Type: TABLE; Schema: public; Owner: me
54--
55
56CREATE TABLE games (
57 id integer NOT NULL,
58 date timestamp without time zone DEFAULT now() NOT NULL,
59 userid integer NOT NULL,
60 points integer NOT NULL,
61 CONSTRAINT games_points_check CHECK ((points = ANY (ARRAY[3, 1, '-3'::integer])))
62);
63
64
65ALTER TABLE games OWNER TO me;
66
67--
68-- Name: games_id_seq; Type: SEQUENCE; Schema: public; Owner: me
69--
70
71CREATE SEQUENCE games_id_seq
72 START WITH 1
73 INCREMENT BY 1
74 NO MINVALUE
75 NO MAXVALUE
76 CACHE 1;
77
78
79ALTER TABLE games_id_seq OWNER TO me;
80
81--
82-- Name: games_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: me
83--
84
85ALTER SEQUENCE games_id_seq OWNED BY games.id;
86
87
88--
89-- Name: games_userid_seq; Type: SEQUENCE; Schema: public; Owner: me
90--
91
92CREATE SEQUENCE games_userid_seq
93 START WITH 1
94 INCREMENT BY 1
95 NO MINVALUE
96 NO MAXVALUE
97 CACHE 1;
98
99
100ALTER TABLE games_userid_seq OWNER TO me;
101
102--
103-- Name: games_userid_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: me
104--
105
106ALTER SEQUENCE games_userid_seq OWNED BY games.userid;
107
108
109--
110-- Name: users; Type: TABLE; Schema: public; Owner: me
111--
112
113CREATE TABLE users (
114 id integer NOT NULL,
115 name character varying(200),
116 regdate timestamp without time zone DEFAULT now(),
117 raiting integer DEFAULT 0 NOT NULL,
118 CONSTRAINT users_id_check CHECK ((id >= 0))
119);
120
121
122ALTER TABLE users OWNER TO me;
123
124--
125-- Name: users_id_seq; Type: SEQUENCE; Schema: public; Owner: me
126--
127
128CREATE SEQUENCE users_id_seq
129 START WITH 1
130 INCREMENT BY 1
131 NO MINVALUE
132 NO MAXVALUE
133 CACHE 1;
134
135
136ALTER TABLE users_id_seq OWNER TO me;
137
138--
139-- Name: users_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: me
140--
141
142ALTER SEQUENCE users_id_seq OWNED BY users.id;
143
144
145--
146-- Name: games id; Type: DEFAULT; Schema: public; Owner: me
147--
148
149ALTER TABLE ONLY games ALTER COLUMN id SET DEFAULT nextval('games_id_seq'::regclass);
150
151
152--
153-- Name: games userid; Type: DEFAULT; Schema: public; Owner: me
154--
155
156ALTER TABLE ONLY games ALTER COLUMN userid SET DEFAULT nextval('games_userid_seq'::regclass);
157
158
159--
160-- Name: users id; Type: DEFAULT; Schema: public; Owner: me
161--
162
163ALTER TABLE ONLY users ALTER COLUMN id SET DEFAULT nextval('users_id_seq'::regclass);
164
165
166--
167-- Data for Name: games; Type: TABLE DATA; Schema: public; Owner: me
168--
169
170COPY games (id, date, userid, points) FROM stdin;
171\.
172
173
174--
175-- Name: games_id_seq; Type: SEQUENCE SET; Schema: public; Owner: me
176--
177
178SELECT pg_catalog.setval('games_id_seq', 831, true);
179
180
181--
182-- Name: games_userid_seq; Type: SEQUENCE SET; Schema: public; Owner: me
183--
184
185SELECT pg_catalog.setval('games_userid_seq', 1, false);
186
187
188--
189-- Data for Name: users; Type: TABLE DATA; Schema: public; Owner: me
190--
191
192COPY users (id, name, regdate, raiting) FROM stdin;
193\.
194
195
196--
197-- Name: users_id_seq; Type: SEQUENCE SET; Schema: public; Owner: me
198--
199
200SELECT pg_catalog.setval('users_id_seq', 3, true);
201
202
203--
204-- Name: games games_pkey; Type: CONSTRAINT; Schema: public; Owner: me
205--
206
207ALTER TABLE ONLY games
208 ADD CONSTRAINT games_pkey PRIMARY KEY (id);
209
210
211--
212-- Name: users users_pkey; Type: CONSTRAINT; Schema: public; Owner: me
213--
214
215ALTER TABLE ONLY users
216 ADD CONSTRAINT users_pkey PRIMARY KEY (id);
217
218
219--
220-- Name: games uprainting; Type: TRIGGER; Schema: public; Owner: me
221--
222
223CREATE TRIGGER uprainting AFTER INSERT ON games FOR EACH ROW EXECUTE PROCEDURE updateuserraiting();
224
225
226--
227-- Name: games games_userid_fkey; Type: FK CONSTRAINT; Schema: public; Owner: me
228--
229
230ALTER TABLE ONLY games
231 ADD CONSTRAINT games_userid_fkey FOREIGN KEY (userid) REFERENCES users(id);
232
233
234--
235-- PostgreSQL database dump complete
236--