Mercurial > pub > bltoolkit

comparison Data/Create Scripts/PostgreSQL.sql @ 0:f990fcb411a9

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Копия текущей версии из github
author cin
date Thu, 27 Mar 2014 21:46:09 +0400
parents
children
comparison
equal deleted inserted replaced
-1:000000000000 0:f990fcb411a9
1 DROP TABLE "Doctor"
2 GO
3
4 DROP TABLE "Patient"
5 GO
6
7 DROP TABLE "Person"
8 GO
9
10 CREATE TABLE "Person"
11 (
12 --PersonID INTEGER PRIMARY KEY DEFAULT NEXTVAL('Seq'),
13 "PersonID" SERIAL PRIMARY KEY,
14 "FirstName" VARCHAR(50) NOT NULL,
15 "LastName" VARCHAR(50) NOT NULL,
16 "MiddleName" VARCHAR(50),
17 "Gender" CHAR(1) NOT NULL
18 )
19 GO
20
21 INSERT INTO "Person" ("FirstName", "LastName", "Gender") VALUES ('John', 'Pupkin', 'M')
22 GO
23 INSERT INTO "Person" ("FirstName", "LastName", "Gender") VALUES ('Tester', 'Testerson', 'M')
24 GO
25
26 -- Doctor Table Extension
27
28 CREATE TABLE "Doctor"
29 (
30 "PersonID" INTEGER NOT NULL,
31 "Taxonomy" VARCHAR(50) NOT NULL
32 )
33 GO
34
35 INSERT INTO "Doctor" ("PersonID", "Taxonomy") VALUES (1, 'Psychiatry')
36 GO
37
38 -- Patient Table Extension
39
40 CREATE TABLE "Patient"
41 (
42 "PersonID" INTEGER NOT NULL,
43 "Diagnosis" VARCHAR(256) NOT NULL
44 )
45 GO
46
47 INSERT INTO "Patient" ("PersonID", "Diagnosis") VALUES (2, 'Hallucination with Paranoid Bugs'' Delirium of Persecution')
48 GO
49
50
51 CREATE OR REPLACE FUNCTION reverse(text) RETURNS text
52 AS $_$
53 DECLARE
54 original alias for $1;
55 reverse_str text;
56 i int4;
57 BEGIN
58 reverse_str := '';
59 FOR i IN REVERSE LENGTH(original)..1 LOOP
60 reverse_str := reverse_str || substr(original,i,1);
61 END LOOP;
62 RETURN reverse_str;
63 END;$_$
64 LANGUAGE plpgsql IMMUTABLE;
65 GO
66
67
68 DROP TABLE "Parent"
69 GO
70 DROP TABLE "Child"
71 GO
72 DROP TABLE "GrandChild"
73 GO
74
75 CREATE TABLE "Parent" ("ParentID" int, "Value1" int)
76 GO
77 CREATE TABLE "Child" ("ParentID" int, "ChildID" int)
78 GO
79 CREATE TABLE "GrandChild" ("ParentID" int, "ChildID" int, "GrandChildID" int)
80 GO
81
82
83 DROP TABLE "LinqDataTypes"
84 GO
85
86 CREATE TABLE "LinqDataTypes"
87 (
88 "ID" int,
89 "MoneyValue" decimal(10,4),
90 "DateTimeValue" timestamp,
91 "DateTimeValue2" timestamp,
92 "BoolValue" boolean,
93 "GuidValue" uuid,
94 "BinaryValue" bytea NULL,
95 "SmallIntValue" smallint,
96 "IntValue" int NULL,
97 "BigIntValue" bigint NULL
98 )
99 GO
100
101
102 DROP TABLE entity
103 GO
104
105 CREATE TABLE entity
106 (
107 the_name character varying(255) NOT NULL,
108 CONSTRAINT entity_name_key UNIQUE (the_name)
109 )
110 GO
111
112 CREATE OR REPLACE FUNCTION add_if_not_exists(p_name character varying)
113 RETURNS void AS
114 $BODY$
115 BEGIN
116 BEGIN
117 insert into entity(the_name) values(p_name);
118 EXCEPTION WHEN unique_violation THEN
119 -- is exists, do nothing
120 END;
121 END;
122 $BODY$
123 LANGUAGE plpgsql;
124 GO
125
126
127 DROP TABLE "SequenceTest1"
128 GO
129
130 DROP TABLE "SequenceTest2"
131 GO
132
133 DROP TABLE "SequenceTest3"
134 GO
135
136 DROP SEQUENCE SequenceTestSeq
137 GO
138
139 CREATE SEQUENCE SequenceTestSeq INCREMENT 1 START 1
140 GO
141
142 DROP SEQUENCE "SequenceTest2_ID_seq"
143 GO
144
145 CREATE SEQUENCE "SequenceTest2_ID_seq" INCREMENT 1 START 1
146 GO
147
148 CREATE TABLE "SequenceTest1"
149 (
150 "ID" INTEGER PRIMARY KEY,
151 "Value" VARCHAR(50)
152 )
153 GO
154
155 CREATE TABLE "SequenceTest2"
156 (
157 "ID" INTEGER PRIMARY KEY DEFAULT NEXTVAL('"SequenceTest2_ID_seq"'),
158 "Value" VARCHAR(50)
159 )
160 GO
161
162 CREATE TABLE "SequenceTest3"
163 (
164 "ID" INTEGER PRIMARY KEY DEFAULT NEXTVAL('SequenceTestSeq'),
165 "Value" VARCHAR(50)
166 )
167 GO
168
169
170 DROP TABLE "TestIdentity"
171 GO
172
173 DROP SEQUENCE "TestIdentity_ID_seq"
174 GO
175
176 CREATE SEQUENCE "TestIdentity_ID_seq" INCREMENT 1 START 1
177 GO
178
179 CREATE TABLE "TestIdentity" (
180 "ID" INTEGER PRIMARY KEY DEFAULT NEXTVAL('"TestIdentity_ID_seq"')
181 )
182 GO