485 lines
9.4 KiB
PL/PgSQL
485 lines
9.4 KiB
PL/PgSQL
--
|
|
-- PostgreSQL database dump
|
|
--
|
|
|
|
SET statement_timeout = 0;
|
|
SET client_encoding = 'UTF8';
|
|
SET standard_conforming_strings = off;
|
|
SET check_function_bodies = false;
|
|
SET client_min_messages = warning;
|
|
SET escape_string_warning = off;
|
|
|
|
--
|
|
-- Name: anotherschema; Type: SCHEMA; Schema: -; Owner: -
|
|
--
|
|
|
|
DROP SCHEMA IF EXISTS anotherschema CASCADE;
|
|
CREATE SCHEMA anotherschema;
|
|
|
|
SET search_path = anotherschema, pg_catalog;
|
|
|
|
SET default_tablespace = '';
|
|
|
|
SET default_with_oids = false;
|
|
|
|
--
|
|
-- Name: users; Type: TABLE; Schema: anotherschema; Owner: -; Tablespace:
|
|
--
|
|
DROP TABLE IF EXISTS users CASCADE;
|
|
CREATE TABLE users (
|
|
name character varying(30),
|
|
email character varying(50),
|
|
created_at timestamp without time zone DEFAULT now(),
|
|
id integer NOT NULL
|
|
);
|
|
|
|
|
|
--
|
|
-- Name: users_id_seq; Type: SEQUENCE; Schema: anotherschema; Owner: -
|
|
--
|
|
|
|
CREATE SEQUENCE users_id_seq
|
|
START WITH 1
|
|
INCREMENT BY 1
|
|
NO MINVALUE
|
|
NO MAXVALUE
|
|
CACHE 1;
|
|
|
|
|
|
--
|
|
-- Name: users_id_seq; Type: SEQUENCE OWNED BY; Schema: anotherschema; Owner: -
|
|
--
|
|
|
|
ALTER SEQUENCE users_id_seq OWNED BY users.id;
|
|
|
|
SET search_path = public, pg_catalog;
|
|
|
|
--
|
|
-- Name: seqnames; Type: TABLE; Schema: public; Owner: -; Tablespace:
|
|
--
|
|
DROP TABLE IF EXISTS seqnames CASCADE;
|
|
CREATE TABLE seqnames (
|
|
name character varying(30),
|
|
pk_id integer NOT NULL
|
|
);
|
|
|
|
|
|
--
|
|
-- Name: users_id_seq; Type: SEQUENCE; Schema: public; Owner: -
|
|
--
|
|
|
|
CREATE SEQUENCE seqnames_pk_id_seq
|
|
START WITH 1
|
|
INCREMENT BY 1
|
|
NO MINVALUE
|
|
NO MAXVALUE
|
|
CACHE 1;
|
|
|
|
--
|
|
-- Name: seqnames_pk_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
|
|
--
|
|
|
|
ALTER SEQUENCE seqnames_pk_id_seq OWNED BY seqnames.pk_id;
|
|
|
|
--
|
|
-- Name: empty_table; Type: TABLE; Schema: public; Owner: -; Tablespace:
|
|
--
|
|
DROP TABLE IF EXISTS empty_table CASCADE;
|
|
CREATE TABLE empty_table (
|
|
id integer NOT NULL,
|
|
field character varying
|
|
);
|
|
|
|
|
|
--
|
|
-- Name: empty_table_id_seq; Type: SEQUENCE; Schema: public; Owner: -
|
|
--
|
|
|
|
CREATE SEQUENCE empty_table_id_seq
|
|
START WITH 1
|
|
INCREMENT BY 1
|
|
NO MINVALUE
|
|
NO MAXVALUE
|
|
CACHE 1;
|
|
|
|
|
|
--
|
|
-- Name: empty_table_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
|
|
--
|
|
|
|
ALTER SEQUENCE empty_table_id_seq OWNED BY empty_table.id;
|
|
|
|
|
|
--
|
|
-- Name: groups; Type: TABLE; Schema: public; Owner: -; Tablespace:
|
|
--
|
|
|
|
DROP TABLE IF EXISTS groups CASCADE;
|
|
CREATE TABLE groups (
|
|
name character varying(50),
|
|
enabled boolean,
|
|
created_at timestamp without time zone DEFAULT now(),
|
|
id integer NOT NULL
|
|
);
|
|
|
|
|
|
--
|
|
-- Name: groups_id_seq; Type: SEQUENCE; Schema: public; Owner: -
|
|
--
|
|
|
|
CREATE SEQUENCE groups_id_seq
|
|
START WITH 1
|
|
INCREMENT BY 1
|
|
NO MAXVALUE
|
|
NO MINVALUE
|
|
CACHE 1;
|
|
|
|
|
|
--
|
|
-- Name: groups_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
|
|
--
|
|
|
|
ALTER SEQUENCE groups_id_seq OWNED BY groups.id;
|
|
|
|
|
|
--
|
|
-- Name: permissions; Type: TABLE; Schema: public; Owner: -; Tablespace:
|
|
--
|
|
|
|
DROP TABLE IF EXISTS permissions CASCADE;
|
|
CREATE TABLE permissions (
|
|
user_id integer,
|
|
group_id integer,
|
|
role character varying(10),
|
|
id integer NOT NULL
|
|
);
|
|
|
|
|
|
--
|
|
-- Name: permissions_id_seq; Type: SEQUENCE; Schema: public; Owner: -
|
|
--
|
|
|
|
CREATE SEQUENCE permissions_id_seq
|
|
START WITH 1
|
|
INCREMENT BY 1
|
|
NO MAXVALUE
|
|
NO MINVALUE
|
|
CACHE 1;
|
|
|
|
|
|
--
|
|
-- Name: permissions_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
|
|
--
|
|
|
|
ALTER SEQUENCE permissions_id_seq OWNED BY permissions.id;
|
|
|
|
|
|
--
|
|
-- Name: users; Type: TABLE; Schema: public; Owner: -; Tablespace:
|
|
--
|
|
|
|
DROP TABLE IF EXISTS users CASCADE;
|
|
CREATE TABLE users (
|
|
name character varying(30),
|
|
email character varying(50),
|
|
created_at timestamp without time zone DEFAULT now(),
|
|
id integer NOT NULL
|
|
);
|
|
|
|
|
|
--
|
|
-- Name: users_id_seq; Type: SEQUENCE; Schema: public; Owner: -
|
|
--
|
|
|
|
CREATE SEQUENCE users_id_seq
|
|
START WITH 1
|
|
INCREMENT BY 1
|
|
NO MAXVALUE
|
|
NO MINVALUE
|
|
CACHE 1;
|
|
|
|
|
|
--
|
|
-- Name: users_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
|
|
--
|
|
|
|
ALTER SEQUENCE users_id_seq OWNED BY users.id;
|
|
|
|
|
|
SET search_path = anotherschema, pg_catalog;
|
|
|
|
--
|
|
-- Name: id; Type: DEFAULT; Schema: anotherschema; Owner: -
|
|
--
|
|
|
|
ALTER TABLE ONLY users ALTER COLUMN id SET DEFAULT nextval('users_id_seq'::regclass);
|
|
|
|
|
|
SET search_path = public, pg_catalog;
|
|
|
|
--
|
|
-- Name:pk_id; Type: DEFAULT; Schema: public; Owner: -
|
|
--
|
|
|
|
ALTER TABLE ONLY seqnames ALTER COLUMN pk_id SET DEFAULT nextval('seqnames_pk_id_seq'::regclass);
|
|
|
|
|
|
--
|
|
-- Name: id; Type: DEFAULT; Schema: public; Owner: -
|
|
--
|
|
|
|
ALTER TABLE ONLY empty_table ALTER COLUMN id SET DEFAULT nextval('empty_table_id_seq'::regclass);
|
|
|
|
|
|
--
|
|
-- Name: id; Type: DEFAULT; Schema: public; Owner: -
|
|
--
|
|
|
|
ALTER TABLE ONLY groups ALTER COLUMN id SET DEFAULT nextval('groups_id_seq'::regclass);
|
|
|
|
|
|
--
|
|
-- Name: id; Type: DEFAULT; Schema: public; Owner: -
|
|
--
|
|
|
|
ALTER TABLE ONLY permissions ALTER COLUMN id SET DEFAULT nextval('permissions_id_seq'::regclass);
|
|
|
|
|
|
--
|
|
-- Name: id; Type: DEFAULT; Schema: public; Owner: -
|
|
--
|
|
|
|
ALTER TABLE ONLY users ALTER COLUMN id SET DEFAULT nextval('users_id_seq'::regclass);
|
|
|
|
|
|
SET search_path = anotherschema, pg_catalog;
|
|
|
|
--
|
|
-- Data for Name: users; Type: TABLE DATA; Schema: anotherschema; Owner: -
|
|
--
|
|
|
|
COPY users (name, email, created_at, id) FROM stdin;
|
|
andrew schemauser@example.org 2015-10-13 07:26:51.398693 1
|
|
\.
|
|
|
|
--
|
|
-- Name: users_id_seq; Type: SEQUENCE SET; Schema: anotherschema; Owner: -
|
|
--
|
|
|
|
SELECT pg_catalog.setval('users_id_seq', 1, true);
|
|
|
|
|
|
SET search_path = public, pg_catalog;
|
|
|
|
--
|
|
-- Data for Name: empty_table; Type: TABLE DATA; Schema: public; Owner: -
|
|
--
|
|
|
|
COPY empty_table (id, field) FROM stdin;
|
|
\.
|
|
|
|
--
|
|
-- Name: seqnames_pk_id_seq; Type: SEQUENCE SET; Schema: public; Owner: -
|
|
--
|
|
|
|
SELECT pg_catalog.setval('seqnames_pk_id_seq', 1, false);
|
|
|
|
|
|
--
|
|
-- Name: empty_table_id_seq; Type: SEQUENCE SET; Schema: public; Owner: -
|
|
--
|
|
|
|
SELECT pg_catalog.setval('empty_table_id_seq', 1, false);
|
|
|
|
|
|
--
|
|
-- Data for Name: groups; Type: TABLE DATA; Schema: public; Owner: -
|
|
--
|
|
|
|
COPY groups (name, enabled, created_at, id) FROM stdin;
|
|
coders t 2012-02-02 22:33:30.807 1
|
|
jazzman f 2012-02-02 22:33:35.271 2
|
|
\.
|
|
|
|
|
|
--
|
|
-- Name: groups_id_seq; Type: SEQUENCE SET; Schema: public; Owner: -
|
|
--
|
|
|
|
SELECT pg_catalog.setval('groups_id_seq', 2, true);
|
|
|
|
|
|
--
|
|
-- Data for Name: permissions; Type: TABLE DATA; Schema: public; Owner: -
|
|
--
|
|
|
|
COPY permissions (user_id, group_id, role, id) FROM stdin;
|
|
1 1 member 1
|
|
2 1 member 2
|
|
3 2 member 9
|
|
4 2 admin 10
|
|
\.
|
|
|
|
|
|
--
|
|
-- Name: permissions_id_seq; Type: SEQUENCE SET; Schema: public; Owner: -
|
|
--
|
|
|
|
SELECT pg_catalog.setval('permissions_id_seq', 10, true);
|
|
|
|
|
|
--
|
|
-- Data for Name: users; Type: TABLE DATA; Schema: public; Owner: -
|
|
--
|
|
|
|
COPY users (name, email, created_at, id) FROM stdin;
|
|
davert davert@mail.ua \N 1
|
|
nick nick@mail.ua 2012-02-02 22:30:31.748 2
|
|
miles miles@davis.com 2012-02-02 22:30:52.166 3
|
|
bird charlie@parker.com 2012-02-02 22:32:13.107 4
|
|
\.
|
|
|
|
|
|
--
|
|
-- Name: users_id_seq; Type: SEQUENCE SET; Schema: public; Owner: -
|
|
--
|
|
|
|
SELECT pg_catalog.setval('users_id_seq', 4, true);
|
|
|
|
|
|
SET search_path = anotherschema, pg_catalog;
|
|
|
|
--
|
|
-- Name: u1; Type: CONSTRAINT; Schema: anotherschema; Owner: -; Tablespace:
|
|
--
|
|
|
|
ALTER TABLE ONLY users
|
|
ADD CONSTRAINT u1 PRIMARY KEY (id);
|
|
|
|
|
|
SET search_path = public, pg_catalog;
|
|
|
|
--
|
|
-- Name: g1; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace:
|
|
--
|
|
|
|
ALTER TABLE ONLY seqnames
|
|
ADD CONSTRAINT s1 PRIMARY KEY (pk_id);
|
|
|
|
--
|
|
-- Name: g1; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace:
|
|
--
|
|
|
|
ALTER TABLE ONLY groups
|
|
ADD CONSTRAINT g1 PRIMARY KEY (id);
|
|
|
|
|
|
--
|
|
-- Name: p1; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace:
|
|
--
|
|
|
|
ALTER TABLE ONLY permissions
|
|
ADD CONSTRAINT p1 PRIMARY KEY (id);
|
|
|
|
|
|
--
|
|
-- Name: u1; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace:
|
|
--
|
|
|
|
ALTER TABLE ONLY users
|
|
ADD CONSTRAINT u1 PRIMARY KEY (id);
|
|
|
|
|
|
--
|
|
-- Name: pf1; Type: FK CONSTRAINT; Schema: public; Owner: -
|
|
--
|
|
|
|
ALTER TABLE ONLY permissions
|
|
ADD CONSTRAINT pf1 FOREIGN KEY (user_id) REFERENCES users(id);
|
|
|
|
|
|
--
|
|
-- Name: pg1; Type: FK CONSTRAINT; Schema: public; Owner: -
|
|
--
|
|
|
|
ALTER TABLE ONLY permissions
|
|
ADD CONSTRAINT pg1 FOREIGN KEY (group_id) REFERENCES groups(id);
|
|
|
|
|
|
--
|
|
-- start test for triggers with $$ syntax
|
|
--
|
|
INSERT INTO users (name, email) VALUES ('This $$ should work', 'user@example.org');
|
|
CREATE OR REPLACE FUNCTION upd_timestamp() RETURNS TRIGGER
|
|
LANGUAGE plpgsql
|
|
AS
|
|
$$
|
|
BEGIN
|
|
NEW.created_at = CURRENT_TIMESTAMP;
|
|
RETURN NEW;
|
|
END;
|
|
$$;
|
|
|
|
-- Test $$ opening quote when is not at the beginning of the line.
|
|
CREATE OR REPLACE FUNCTION upd_timestamp() RETURNS TRIGGER
|
|
LANGUAGE plpgsql
|
|
AS $$
|
|
BEGIN
|
|
NEW.created_at = CURRENT_TIMESTAMP;
|
|
RETURN NEW;
|
|
END;
|
|
$$;
|
|
|
|
INSERT INTO users (name, email) VALUES ('This should work as well', 'user2@example.org');
|
|
--
|
|
-- end test for triggers with $$ syntax
|
|
--
|
|
|
|
CREATE TABLE "composite_pk" (
|
|
"group_id" INTEGER NOT NULL,
|
|
"id" INTEGER NOT NULL,
|
|
"status" VARCHAR NOT NULL,
|
|
PRIMARY KEY ("group_id", "id")
|
|
);
|
|
|
|
CREATE TABLE "no_pk" (
|
|
"status" VARCHAR NOT NULL
|
|
);
|
|
|
|
CREATE TABLE "order" (
|
|
"id" INTEGER NOT NULL PRIMARY KEY,
|
|
"name" VARCHAR NOT NULL,
|
|
"status" VARCHAR NOT NULL
|
|
);
|
|
|
|
insert into "order"("id","name","status") values (1,'main', 'open');
|
|
|
|
-- Custom Types
|
|
DROP TYPE IF EXISTS composite_type;
|
|
CREATE TYPE composite_type AS (
|
|
a decimal,
|
|
b decimal
|
|
);
|
|
|
|
DROP TYPE IF EXISTS enum_type;
|
|
CREATE TYPE enum_type AS ENUM (
|
|
'Mon',
|
|
'Tue',
|
|
'Wed',
|
|
'Thu',
|
|
'Fri',
|
|
'Sat',
|
|
'Sun'
|
|
);
|
|
|
|
DROP TYPE IF EXISTS range_type;
|
|
CREATE TYPE range_type AS range (subtype = time);
|
|
|
|
DROP TYPE IF EXISTS base_type;
|
|
CREATE TYPE base_type;
|
|
|
|
-- --
|
|
-- PostgreSQL database dump complete
|
|
--
|
|
|