00-init.sql140 lines · main

1	-- We apply our test seedings to template1 so every new created db will have the same structure
2	\c template1
3
4	-- Tables for testing
5
6	CREATE TYPE public.user_status AS ENUM ('ACTIVE', 'INACTIVE');
7	CREATE TYPE composite_type_with_array_attribute AS (my_text_array text[]);
8
9	CREATE TABLE public.users (
10	id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
11	name text,
12	status user_status DEFAULT 'ACTIVE'
13	);
14	INSERT INTO
15	public.users (name)
16	VALUES
17	('Joe Bloggs'),
18	('Jane Doe');
19
20	CREATE TABLE public.todos (
21	id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
22	details text,
23	"user-id" bigint REFERENCES users NOT NULL
24	);
25
26	INSERT INTO
27	public.todos (details, "user-id")
28	VALUES
29	('Star the repo', 1),
30	('Watch the releases', 2);
31
32
33	CREATE FUNCTION add(integer, integer) RETURNS integer
34	AS 'select $1 + $2;'
35	LANGUAGE SQL
36	IMMUTABLE
37	RETURNS NULL ON NULL INPUT;
38
39	create table public.users_audit (
40	id BIGINT generated by DEFAULT as identity,
41	created_at timestamptz DEFAULT now(),
42	user_id bigint,
43	previous_value jsonb
44	);
45
46	create function public.audit_action()
47	returns trigger as $$
48	begin
49	insert into public.users_audit (user_id, previous_value)
50	values (old.id, row_to_json(old));
51
52	return new;
53	end;
54	$$ language plpgsql;
55
56	CREATE VIEW todos_view AS SELECT * FROM public.todos;
57	-- For testing typegen on view-to-view relationships
58	create view users_view as select * from public.users;
59
60	create materialized view todos_matview as select * from public.todos;
61
62	create function public.blurb(public.todos) returns text as
63	$$
64	select substring($1.details, 1, 3);
65	$$ language sql stable;
66
67	create function public.blurb_varchar(public.todos) returns character varying as
68	$$
69	select substring($1.details, 1, 3);
70	$$ language sql stable;
71
72	create function public.details_length(public.todos) returns integer as
73	$$
74	select length($1.details);
75	$$ language sql stable;
76
77	create function public.details_is_long(public.todos) returns boolean as
78	$$
79	select $1.details_length > 20;
80	$$ language sql stable;
81
82	create function public.details_words(public.todos) returns text[] as
83	$$
84	select string_to_array($1.details, ' ');
85	$$ language sql stable;
86
87	create extension postgres_fdw;
88	create server foreign_server foreign data wrapper postgres_fdw options (host 'localhost', port '5432', dbname 'postgres');
89	create user mapping for postgres server foreign_server options (user 'postgres', password 'postgres');
90	create foreign table foreign_table (
91	id int8 not null,
92	name text,
93	status user_status
94	) server foreign_server options (schema_name 'public', table_name 'users');
95
96	create or replace function public.function_returning_row()
97	returns public.users
98	language sql
99	stable
100	as $$
101	select * from public.users limit 1;
102	$$;
103
104	create or replace function public.function_returning_set_of_rows()
105	returns setof public.users
106	language sql
107	stable
108	as $$
109	select * from public.users;
110	$$;
111
112	create or replace function public.function_returning_table()
113	returns table (id int, name text)
114	language sql
115	stable
116	as $$
117	select id, name from public.users;
118	$$;
119
120	create or replace function public.polymorphic_function(text) returns void language sql as '';
121	create or replace function public.polymorphic_function(bool) returns void language sql as '';
122
123	create table user_details (
124	user_id int8 references users(id) primary key,
125	details text
126	);
127
128	create view a_view as select id from users;
129
130	create table empty();
131
132	create table table_with_other_tables_row_type (
133	col1 user_details,
134	col2 a_view
135	);
136
137	create table table_with_primary_key_other_than_id (
138	other_id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
139	name text
140	);