WaaS4iy4

1--Table
2CREATE TABLE IF NOT EXISTS person (
3  id integer NOT NULL,
4  person_name character varying(40) NOT NULL,
5  updated_date date,
6  CONSTRAINT person_pkey PRIMARY KEY (id)
7);
8
9--Index
10CREATE INDEX IF NOT EXISTS idx_person_name ON person (person_name);
11
12--Sequence
13CREATE SEQUENCE IF NOT EXISTS seq_person_inc;
14
15--Function
16CREATE OR REPLACE FUNCTION simple_sum(a_integer int, b_integer int) RETURNS INT
17    AS $$ SELECT a_integer+b_integer $$
18LANGUAGE SQL;
19
20--View
21CREATE OR REPLACE VIEW vw_select_1 AS
22    SELECT 1;
23
24--Role
25DO $$
26BEGIN
27    CREATE ROLE rick_deckard;    
28EXCEPTION   
29    WHEN duplicate_object THEN
30        RAISE NOTICE 'Role already exists. Ignoring...';    
31END$$;
32
33--Simple insert
34INSERT INTO person (id, person_name) VALUES (1, 'HAL-9000'); 
35
36--Upsert (insert + update)
37INSERT INTO person (id, person_name) VALUES (1, 'Betrayer') ON CONFLICT ON CONSTRAINT person_pkey DO UPDATE SET person_name = EXCLUDED.person_name;
38
39--Upsert (ignoring duplicate error)
40INSERT INTO person (id, person_name) VALUES (1, 'HAL-9000') ON CONFLICT ON CONSTRAINT person_pkey DO NOTHING;
41
42--Upsert (ignoring any error)
43INSERT INTO person (id, person_name) VALUES (1, 'HAL-9000') ON CONFLICT DO NOTHING;
44
45--Field
46DO $$
47BEGIN
48    ALTER TABLE person ADD COLUMN id_another_person INTEGER;
49EXCEPTION   
50    WHEN duplicate_column THEN
51        RAISE NOTICE 'Field already exists. Ignoring...';
52END$$;
53
54--Constraint
55DO $$
56BEGIN
57    ALTER TABLE person ADD CONSTRAINT person_id_another_person_fkey FOREIGN KEY (id_another_person) REFERENCES person (id);
58EXCEPTION   
59    WHEN duplicate_object THEN
60        RAISE NOTICE 'Constraint already exists. Ignoring...';
61END$$;
62
63--Trigger
64CREATE OR REPLACE FUNCTION person_trigger_function() RETURNS trigger AS $BODY$
65BEGIN
66   --Something complex here =)
67   RETURN NEW; 
68END;
69$BODY$
70LANGUAGE plpgsql;
71
72DO $$
73BEGIN
74    CREATE TRIGGER person_trigger BEFORE INSERT OR UPDATE ON person FOR EACH ROW EXECUTE PROCEDURE person_trigger_function();
75EXCEPTION   
76    WHEN duplicate_object THEN
77        RAISE NOTICE 'Trigger already exists. Ignoring...';
78END$$;
79
80--Drop
81DROP TRIGGER IF EXISTS person_trigger ON person;
82DROP INDEX IF EXISTS idx_person_name;
83ALTER TABLE person DROP COLUMN IF EXISTS person_name;
84ALTER TABLE person DROP CONSTRAINT IF EXISTS person_id_another_person_fkey;
85DROP ROLE IF EXISTS rick_deckard;
86DROP VIEW IF EXISTS vw_select_1;
87DROP FUNCTION IF EXISTS simple_sum(integer, integer);
88DROP FUNCTION IF EXISTS person_trigger_function();
89DROP TABLE IF EXISTS person;
90DROP SEQUENCE IF EXISTS seq_person_inc;