|
0
|
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
|
