wehub-resource-sync
481 lines
13 KiB
PL/PgSQL
481 lines
13 KiB
PL/PgSQL
-- Downloaded from: https://github.com/Mistral-war2ru/PG-connect/blob/ba0e4f62b6b9b5799cf645fdc072d59f727b9ffe/lab9/TestDB.sql
|
||
--
|
||
-- PostgreSQL database dump
|
||
--
|
||
|
||
-- Dumped from database version 15.2
|
||
-- Dumped by pg_dump version 15.2
|
||
|
||
-- Started on 2023-04-19 19:40:00
|
||
|
||
SET statement_timeout = 0;
|
||
SET lock_timeout = 0;
|
||
SET idle_in_transaction_session_timeout = 0;
|
||
SET client_encoding = 'UTF8';
|
||
SET standard_conforming_strings = on;
|
||
SELECT pg_catalog.set_config('search_path', '', false);
|
||
SET check_function_bodies = false;
|
||
SET xmloption = content;
|
||
SET client_min_messages = warning;
|
||
SET row_security = off;
|
||
|
||
--
|
||
-- TOC entry 3370 (class 1262 OID 25411)
|
||
-- Name: StudentsDB; Type: DATABASE; Schema: -; Owner: postgres
|
||
--
|
||
|
||
CREATE DATABASE "StudentsDB" WITH TEMPLATE = template0 ENCODING = 'UTF8' LOCALE_PROVIDER = libc LOCALE = 'Russian_Russia.1251';
|
||
|
||
|
||
ALTER DATABASE "StudentsDB" OWNER TO postgres;
|
||
|
||
\connect "StudentsDB"
|
||
|
||
SET statement_timeout = 0;
|
||
SET lock_timeout = 0;
|
||
SET idle_in_transaction_session_timeout = 0;
|
||
SET client_encoding = 'UTF8';
|
||
SET standard_conforming_strings = on;
|
||
SELECT pg_catalog.set_config('search_path', '', false);
|
||
SET check_function_bodies = false;
|
||
SET xmloption = content;
|
||
SET client_min_messages = warning;
|
||
SET row_security = off;
|
||
|
||
--
|
||
-- TOC entry 3371 (class 0 OID 0)
|
||
-- Name: StudentsDB; Type: DATABASE PROPERTIES; Schema: -; Owner: postgres
|
||
--
|
||
|
||
ALTER ROLE postgres IN DATABASE "StudentsDB" SET effective_cache_size TO '65536';
|
||
|
||
|
||
\connect "StudentsDB"
|
||
|
||
SET statement_timeout = 0;
|
||
SET lock_timeout = 0;
|
||
SET idle_in_transaction_session_timeout = 0;
|
||
SET client_encoding = 'UTF8';
|
||
SET standard_conforming_strings = on;
|
||
SELECT pg_catalog.set_config('search_path', '', false);
|
||
SET check_function_bodies = false;
|
||
SET xmloption = content;
|
||
SET client_min_messages = warning;
|
||
SET row_security = off;
|
||
|
||
--
|
||
-- TOC entry 223 (class 1255 OID 25412)
|
||
-- Name: get_fiit_2019(); Type: FUNCTION; Schema: public; Owner: postgres
|
||
--
|
||
|
||
CREATE FUNCTION public.get_fiit_2019() RETURNS text
|
||
LANGUAGE plpgsql
|
||
AS $$
|
||
declare
|
||
ret text default '';
|
||
rec record;
|
||
curs refcursor;
|
||
begin
|
||
--call proc1(curs, 2019, 'ФИИТ');
|
||
call proc1(curs, 2018, 'ФИИТ');
|
||
loop
|
||
fetch curs into rec;
|
||
exit when not found;
|
||
ret := ret || '{' || rec.SpecialtyName || ',' || rec.SetName|| ',' || rec.GroupName || ',' || rec.SetYear|| '}';
|
||
end loop;
|
||
close curs;
|
||
return ret;
|
||
end; $$;
|
||
|
||
|
||
ALTER FUNCTION public.get_fiit_2019() OWNER TO postgres;
|
||
|
||
--
|
||
-- TOC entry 224 (class 1255 OID 25413)
|
||
-- Name: proc1(refcursor); Type: PROCEDURE; Schema: public; Owner: postgres
|
||
--
|
||
|
||
CREATE PROCEDURE public.proc1(INOUT curs refcursor)
|
||
LANGUAGE plpgsql
|
||
AS $$
|
||
DECLARE
|
||
lcurs refcursor;
|
||
BEGIN
|
||
open lcurs for
|
||
select SpecialtyName, SetName, GroupName, SetYear
|
||
from Groups inner join (Sets inner join Specialities on Sets.SpecialtyID = Specialities.SpecialtyID) on Sets.SetID = Groups.SetID
|
||
where Sets.SetYear = 2019 AND Specialities.SpecialtyCode = 'ФИИТ';
|
||
curs = lcurs;
|
||
END $$;
|
||
|
||
|
||
ALTER PROCEDURE public.proc1(INOUT curs refcursor) OWNER TO postgres;
|
||
|
||
--
|
||
-- TOC entry 225 (class 1255 OID 25414)
|
||
-- Name: proc1(refcursor, integer, text); Type: PROCEDURE; Schema: public; Owner: postgres
|
||
--
|
||
|
||
CREATE PROCEDURE public.proc1(INOUT curs refcursor, IN yr integer, IN spec text)
|
||
LANGUAGE plpgsql
|
||
AS $$
|
||
DECLARE
|
||
lcurs refcursor;
|
||
BEGIN
|
||
open lcurs for
|
||
select SpecialtyName, SetName, GroupName, SetYear
|
||
from Groups inner join (Sets inner join Specialities on Sets.SpecialtyID = Specialities.SpecialtyID) on Sets.SetID = Groups.SetID
|
||
where Sets.SetYear = yr AND Specialities.SpecialtyCode = spec;
|
||
curs = lcurs;
|
||
END $$;
|
||
|
||
|
||
ALTER PROCEDURE public.proc1(INOUT curs refcursor, IN yr integer, IN spec text) OWNER TO postgres;
|
||
|
||
SET default_tablespace = '';
|
||
|
||
SET default_table_access_method = heap;
|
||
|
||
--
|
||
-- TOC entry 214 (class 1259 OID 25415)
|
||
-- Name: groups; Type: TABLE; Schema: public; Owner: postgres
|
||
--
|
||
|
||
CREATE TABLE public.groups (
|
||
groupid integer NOT NULL,
|
||
setid integer NOT NULL,
|
||
groupname text NOT NULL,
|
||
teachformid integer NOT NULL
|
||
);
|
||
|
||
|
||
ALTER TABLE public.groups OWNER TO postgres;
|
||
|
||
--
|
||
-- TOC entry 215 (class 1259 OID 25420)
|
||
-- Name: groups_groupid_seq; Type: SEQUENCE; Schema: public; Owner: postgres
|
||
--
|
||
|
||
ALTER TABLE public.groups ALTER COLUMN groupid ADD GENERATED ALWAYS AS IDENTITY (
|
||
SEQUENCE NAME public.groups_groupid_seq
|
||
START WITH 1
|
||
INCREMENT BY 1
|
||
NO MINVALUE
|
||
NO MAXVALUE
|
||
CACHE 1
|
||
);
|
||
|
||
|
||
--
|
||
-- TOC entry 221 (class 1259 OID 25462)
|
||
-- Name: labs; Type: TABLE; Schema: public; Owner: postgres
|
||
--
|
||
|
||
CREATE TABLE public.labs (
|
||
"ID" integer NOT NULL,
|
||
"Name" text NOT NULL
|
||
);
|
||
|
||
|
||
ALTER TABLE public.labs OWNER TO postgres;
|
||
|
||
--
|
||
-- TOC entry 222 (class 1259 OID 25469)
|
||
-- Name: labs_ID_seq; Type: SEQUENCE; Schema: public; Owner: postgres
|
||
--
|
||
|
||
ALTER TABLE public.labs ALTER COLUMN "ID" ADD GENERATED ALWAYS AS IDENTITY (
|
||
SEQUENCE NAME public."labs_ID_seq"
|
||
START WITH 1
|
||
INCREMENT BY 1
|
||
NO MINVALUE
|
||
NO MAXVALUE
|
||
CACHE 1
|
||
);
|
||
|
||
|
||
--
|
||
-- TOC entry 216 (class 1259 OID 25421)
|
||
-- Name: sets; Type: TABLE; Schema: public; Owner: postgres
|
||
--
|
||
|
||
CREATE TABLE public.sets (
|
||
setid integer NOT NULL,
|
||
setname text NOT NULL,
|
||
setyear integer NOT NULL,
|
||
specialtyid integer NOT NULL
|
||
);
|
||
|
||
|
||
ALTER TABLE public.sets OWNER TO postgres;
|
||
|
||
--
|
||
-- TOC entry 217 (class 1259 OID 25426)
|
||
-- Name: sets_setid_seq; Type: SEQUENCE; Schema: public; Owner: postgres
|
||
--
|
||
|
||
ALTER TABLE public.sets ALTER COLUMN setid ADD GENERATED ALWAYS AS IDENTITY (
|
||
SEQUENCE NAME public.sets_setid_seq
|
||
START WITH 1
|
||
INCREMENT BY 1
|
||
NO MINVALUE
|
||
NO MAXVALUE
|
||
CACHE 1
|
||
);
|
||
|
||
|
||
--
|
||
-- TOC entry 218 (class 1259 OID 25427)
|
||
-- Name: specialities; Type: TABLE; Schema: public; Owner: postgres
|
||
--
|
||
|
||
CREATE TABLE public.specialities (
|
||
specialtyid integer NOT NULL,
|
||
specialtyname text NOT NULL,
|
||
specialtycode text NOT NULL,
|
||
descript text
|
||
);
|
||
|
||
|
||
ALTER TABLE public.specialities OWNER TO postgres;
|
||
|
||
--
|
||
-- TOC entry 219 (class 1259 OID 25432)
|
||
-- Name: specialities_specialtyid_seq; Type: SEQUENCE; Schema: public; Owner: postgres
|
||
--
|
||
|
||
ALTER TABLE public.specialities ALTER COLUMN specialtyid ADD GENERATED ALWAYS AS IDENTITY (
|
||
SEQUENCE NAME public.specialities_specialtyid_seq
|
||
START WITH 1
|
||
INCREMENT BY 1
|
||
NO MINVALUE
|
||
NO MAXVALUE
|
||
CACHE 1
|
||
);
|
||
|
||
|
||
--
|
||
-- TOC entry 220 (class 1259 OID 25458)
|
||
-- Name: v1; Type: VIEW; Schema: public; Owner: postgres
|
||
--
|
||
|
||
CREATE VIEW public.v1 AS
|
||
SELECT groups.groupname,
|
||
sets.setname,
|
||
sets.setyear,
|
||
specialities.specialtycode
|
||
FROM (public.groups
|
||
JOIN (public.sets
|
||
JOIN public.specialities ON ((specialities.specialtyid = sets.specialtyid))) ON ((sets.setid = groups.setid)));
|
||
|
||
|
||
ALTER TABLE public.v1 OWNER TO postgres;
|
||
|
||
--
|
||
-- TOC entry 3357 (class 0 OID 25415)
|
||
-- Dependencies: 214
|
||
-- Data for Name: groups; Type: TABLE DATA; Schema: public; Owner: postgres
|
||
--
|
||
|
||
INSERT INTO public.groups OVERRIDING SYSTEM VALUE VALUES (3, 2, 'ПИ-20-а', 1);
|
||
INSERT INTO public.groups OVERRIDING SYSTEM VALUE VALUES (4, 2, 'ПИ-20-б', 1);
|
||
INSERT INTO public.groups OVERRIDING SYSTEM VALUE VALUES (7, 4, 'ФИИТ-16-а', 1);
|
||
INSERT INTO public.groups OVERRIDING SYSTEM VALUE VALUES (8, 4, 'ФИИТ-16-б', 1);
|
||
INSERT INTO public.groups OVERRIDING SYSTEM VALUE VALUES (9, 5, 'ФИИТ-18-а', 1);
|
||
INSERT INTO public.groups OVERRIDING SYSTEM VALUE VALUES (10, 5, 'ФИИТ-18-б', 1);
|
||
INSERT INTO public.groups OVERRIDING SYSTEM VALUE VALUES (12, 6, 'ФИИТ-20-а', 1);
|
||
INSERT INTO public.groups OVERRIDING SYSTEM VALUE VALUES (13, 6, 'ФИИТ-20-б', 1);
|
||
INSERT INTO public.groups OVERRIDING SYSTEM VALUE VALUES (11, 5, 'ФИИТ-18-в', 2);
|
||
INSERT INTO public.groups OVERRIDING SYSTEM VALUE VALUES (14, 6, 'ФИИТ-20-в', 2);
|
||
INSERT INTO public.groups OVERRIDING SYSTEM VALUE VALUES (15, 1, 'ПМ-20-1', 2);
|
||
INSERT INTO public.groups OVERRIDING SYSTEM VALUE VALUES (16, 1, 'ПМ-20-2', 2);
|
||
INSERT INTO public.groups OVERRIDING SYSTEM VALUE VALUES (18, 2, 'test', 2);
|
||
|
||
|
||
--
|
||
-- TOC entry 3363 (class 0 OID 25462)
|
||
-- Dependencies: 221
|
||
-- Data for Name: labs; Type: TABLE DATA; Schema: public; Owner: postgres
|
||
--
|
||
|
||
INSERT INTO public.labs OVERRIDING SYSTEM VALUE VALUES (1, 'lab1');
|
||
INSERT INTO public.labs OVERRIDING SYSTEM VALUE VALUES (2, 'lab2');
|
||
|
||
|
||
--
|
||
-- TOC entry 3359 (class 0 OID 25421)
|
||
-- Dependencies: 216
|
||
-- Data for Name: sets; Type: TABLE DATA; Schema: public; Owner: postgres
|
||
--
|
||
|
||
INSERT INTO public.sets OVERRIDING SYSTEM VALUE VALUES (1, 'ПИ-15', 2015, 1);
|
||
INSERT INTO public.sets OVERRIDING SYSTEM VALUE VALUES (2, 'ПИ-20', 2020, 2);
|
||
INSERT INTO public.sets OVERRIDING SYSTEM VALUE VALUES (3, 'ФИИТ-15', 2015, 3);
|
||
INSERT INTO public.sets OVERRIDING SYSTEM VALUE VALUES (4, 'ФИИТ-16', 2016, 3);
|
||
INSERT INTO public.sets OVERRIDING SYSTEM VALUE VALUES (5, 'ФИИТ-18', 2018, 3);
|
||
INSERT INTO public.sets OVERRIDING SYSTEM VALUE VALUES (6, 'ФИИТ-20', 2019, 3);
|
||
INSERT INTO public.sets OVERRIDING SYSTEM VALUE VALUES (7, 'name12', 2016, 10);
|
||
|
||
|
||
--
|
||
-- TOC entry 3361 (class 0 OID 25427)
|
||
-- Dependencies: 218
|
||
-- Data for Name: specialities; Type: TABLE DATA; Schema: public; Owner: postgres
|
||
--
|
||
|
||
INSERT INTO public.specialities OVERRIDING SYSTEM VALUE VALUES (3, 'Фундаментальная информатика и ИТ', 'ФИИТ', 'Кафедра ВТ');
|
||
INSERT INTO public.specialities OVERRIDING SYSTEM VALUE VALUES (1, 'Прикладная информатика', 'ПИ', 'Кафедра ПМИ');
|
||
INSERT INTO public.specialities OVERRIDING SYSTEM VALUE VALUES (2, 'Прикладная математика и информатика', 'ПМИ', 'Кафедра ПМИ');
|
||
INSERT INTO public.specialities OVERRIDING SYSTEM VALUE VALUES (10, 'test1', 'test2', 'test3');
|
||
|
||
|
||
--
|
||
-- TOC entry 3372 (class 0 OID 0)
|
||
-- Dependencies: 215
|
||
-- Name: groups_groupid_seq; Type: SEQUENCE SET; Schema: public; Owner: postgres
|
||
--
|
||
|
||
SELECT pg_catalog.setval('public.groups_groupid_seq', 18, true);
|
||
|
||
|
||
--
|
||
-- TOC entry 3373 (class 0 OID 0)
|
||
-- Dependencies: 222
|
||
-- Name: labs_ID_seq; Type: SEQUENCE SET; Schema: public; Owner: postgres
|
||
--
|
||
|
||
SELECT pg_catalog.setval('public."labs_ID_seq"', 10, true);
|
||
|
||
|
||
--
|
||
-- TOC entry 3374 (class 0 OID 0)
|
||
-- Dependencies: 217
|
||
-- Name: sets_setid_seq; Type: SEQUENCE SET; Schema: public; Owner: postgres
|
||
--
|
||
|
||
SELECT pg_catalog.setval('public.sets_setid_seq', 7, true);
|
||
|
||
|
||
--
|
||
-- TOC entry 3375 (class 0 OID 0)
|
||
-- Dependencies: 219
|
||
-- Name: specialities_specialtyid_seq; Type: SEQUENCE SET; Schema: public; Owner: postgres
|
||
--
|
||
|
||
SELECT pg_catalog.setval('public.specialities_specialtyid_seq', 10, true);
|
||
|
||
|
||
--
|
||
-- TOC entry 3196 (class 2606 OID 25434)
|
||
-- Name: groups groups_groupid_key; Type: CONSTRAINT; Schema: public; Owner: postgres
|
||
--
|
||
|
||
ALTER TABLE ONLY public.groups
|
||
ADD CONSTRAINT groups_groupid_key UNIQUE (groupid);
|
||
|
||
|
||
--
|
||
-- TOC entry 3211 (class 2606 OID 25468)
|
||
-- Name: labs labs_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
|
||
--
|
||
|
||
ALTER TABLE ONLY public.labs
|
||
ADD CONSTRAINT labs_pkey PRIMARY KEY ("ID");
|
||
|
||
|
||
--
|
||
-- TOC entry 3199 (class 2606 OID 25436)
|
||
-- Name: groups pkgroups; Type: CONSTRAINT; Schema: public; Owner: postgres
|
||
--
|
||
|
||
ALTER TABLE ONLY public.groups
|
||
ADD CONSTRAINT pkgroups PRIMARY KEY (groupid);
|
||
|
||
|
||
--
|
||
-- TOC entry 3202 (class 2606 OID 25438)
|
||
-- Name: sets pksets; Type: CONSTRAINT; Schema: public; Owner: postgres
|
||
--
|
||
|
||
ALTER TABLE ONLY public.sets
|
||
ADD CONSTRAINT pksets PRIMARY KEY (setid);
|
||
|
||
|
||
--
|
||
-- TOC entry 3206 (class 2606 OID 25440)
|
||
-- Name: specialities pkspecialities; Type: CONSTRAINT; Schema: public; Owner: postgres
|
||
--
|
||
|
||
ALTER TABLE ONLY public.specialities
|
||
ADD CONSTRAINT pkspecialities PRIMARY KEY (specialtyid);
|
||
|
||
|
||
--
|
||
-- TOC entry 3204 (class 2606 OID 25442)
|
||
-- Name: sets sets_setid_key; Type: CONSTRAINT; Schema: public; Owner: postgres
|
||
--
|
||
|
||
ALTER TABLE ONLY public.sets
|
||
ADD CONSTRAINT sets_setid_key UNIQUE (setid);
|
||
|
||
|
||
--
|
||
-- TOC entry 3208 (class 2606 OID 25444)
|
||
-- Name: specialities specialities_specialtyid_key; Type: CONSTRAINT; Schema: public; Owner: postgres
|
||
--
|
||
|
||
ALTER TABLE ONLY public.specialities
|
||
ADD CONSTRAINT specialities_specialtyid_key UNIQUE (specialtyid);
|
||
|
||
|
||
--
|
||
-- TOC entry 3197 (class 1259 OID 25445)
|
||
-- Name: igroupsteachformid; Type: INDEX; Schema: public; Owner: postgres
|
||
--
|
||
|
||
CREATE INDEX igroupsteachformid ON public.groups USING btree (teachformid);
|
||
|
||
|
||
--
|
||
-- TOC entry 3200 (class 1259 OID 25446)
|
||
-- Name: isetssetyear; Type: INDEX; Schema: public; Owner: postgres
|
||
--
|
||
|
||
CREATE INDEX isetssetyear ON public.sets USING btree (setyear);
|
||
|
||
|
||
--
|
||
-- TOC entry 3209 (class 1259 OID 25447)
|
||
-- Name: specialtyindex; Type: INDEX; Schema: public; Owner: postgres
|
||
--
|
||
|
||
CREATE UNIQUE INDEX specialtyindex ON public.specialities USING btree (specialtyid);
|
||
|
||
ALTER TABLE public.specialities CLUSTER ON specialtyindex;
|
||
|
||
|
||
--
|
||
-- TOC entry 3212 (class 2606 OID 25448)
|
||
-- Name: groups fkgroupssets; Type: FK CONSTRAINT; Schema: public; Owner: postgres
|
||
--
|
||
|
||
ALTER TABLE ONLY public.groups
|
||
ADD CONSTRAINT fkgroupssets FOREIGN KEY (setid) REFERENCES public.sets(setid) ON UPDATE CASCADE ON DELETE CASCADE;
|
||
|
||
|
||
--
|
||
-- TOC entry 3213 (class 2606 OID 25453)
|
||
-- Name: sets fksetsspecialities; Type: FK CONSTRAINT; Schema: public; Owner: postgres
|
||
--
|
||
|
||
ALTER TABLE ONLY public.sets
|
||
ADD CONSTRAINT fksetsspecialities FOREIGN KEY (specialtyid) REFERENCES public.specialities(specialtyid) ON UPDATE CASCADE;
|
||
|
||
|
||
-- Completed on 2023-04-19 19:40:00
|
||
|
||
--
|
||
-- PostgreSQL database dump complete
|
||
--
|
||
|