|
-- |
|
-- PostgreSQL database dump |
|
-- |
|
|
|
-- Dumped from database version 10.13 (Ubuntu 10.13-1.pgdg18.04+1) |
|
-- Dumped by pg_dump version 12.3 (Ubuntu 12.3-1.pgdg18.04+1) |
|
|
|
SET statement_timeout = 0; |
|
SET lock_timeout = 0; |
|
SET idle_in_transaction_session_timeout = 0; |
|
SET client_encoding = 'UTF8'; |
|
SET standard_conforming_strings = on; |
|
SELECT pg_catalog.set_config('search_path', '', false); |
|
SET check_function_bodies = false; |
|
SET xmloption = content; |
|
SET client_min_messages = warning; |
|
SET row_security = off; |
|
|
|
-- |
|
-- Name: orders; Type: SCHEMA; Schema: -; Owner: pgsql_user_name |
|
-- |
|
|
|
CREATE SCHEMA orders; |
|
|
|
|
|
ALTER SCHEMA orders OWNER TO pgsql_user_name; |
|
|
|
SET default_tablespace = ''; |
|
|
|
-- |
|
-- Name: order_items; Type: TABLE; Schema: public; Owner: pgsql_user_name |
|
-- |
|
|
|
CREATE TABLE public.order_items ( |
|
id integer NOT NULL, |
|
order_id integer NOT NULL, |
|
product_id integer NOT NULL, |
|
quantity integer NOT NULL |
|
); |
|
|
|
|
|
ALTER TABLE public.order_items OWNER TO pgsql_user_name; |
|
|
|
-- |
|
-- Name: orders; Type: TABLE; Schema: public; Owner: pgsql_user_name |
|
-- |
|
|
|
CREATE TABLE public.orders ( |
|
id integer NOT NULL, |
|
user_id integer NOT NULL, |
|
created_at timestamp without time zone |
|
); |
|
|
|
|
|
ALTER TABLE public.orders OWNER TO pgsql_user_name; |
|
|
|
-- |
|
-- Name: products; Type: TABLE; Schema: public; Owner: pgsql_user_name |
|
-- |
|
|
|
CREATE TABLE public.products ( |
|
id integer NOT NULL, |
|
name character varying NOT NULL, |
|
price integer NOT NULL |
|
); |
|
|
|
|
|
ALTER TABLE public.products OWNER TO pgsql_user_name; |
|
|
|
-- |
|
-- Name: order_with_total_amount; Type: VIEW; Schema: public; Owner: pgsql_user_name |
|
-- |
|
|
|
CREATE VIEW public.order_with_total_amount AS |
|
SELECT ord.id, |
|
ord.user_id, |
|
( SELECT sum((oi_in.quantity * prd_in.price)) AS sum |
|
FROM ((public.orders ord_in |
|
LEFT JOIN public.order_items oi_in ON ((ord_in.id = oi_in.order_id))) |
|
LEFT JOIN public.products prd_in ON ((oi_in.product_id = prd_in.id))) |
|
WHERE (ord_in.id = ord.id)) AS total_amount |
|
FROM public.orders ord |
|
ORDER BY ( SELECT sum((oi_in.quantity * prd_in.price)) AS sum |
|
FROM ((public.orders ord_in |
|
LEFT JOIN public.order_items oi_in ON ((ord_in.id = oi_in.order_id))) |
|
LEFT JOIN public.products prd_in ON ((oi_in.product_id = prd_in.id))) |
|
WHERE (ord_in.id = ord.id)); |
|
|
|
|
|
ALTER TABLE public.order_with_total_amount OWNER TO pgsql_user_name; |
|
|
|
-- |
|
-- Data for Name: order_items; Type: TABLE DATA; Schema: public; Owner: pgsql_user_name |
|
-- |
|
|
|
COPY public.order_items (id, order_id, product_id, quantity) FROM stdin; |
|
1 1 1 1 |
|
2 2 1 1 |
|
3 2 2 1 |
|
4 2 3 2 |
|
5 3 3 1 |
|
6 3 1 1 |
|
\. |
|
|
|
|
|
-- |
|
-- Data for Name: orders; Type: TABLE DATA; Schema: public; Owner: pgsql_user_name |
|
-- |
|
|
|
COPY public.orders (id, user_id, created_at) FROM stdin; |
|
1 1 2009-12-12 00:00:00 |
|
2 2 2010-12-12 00:00:00 |
|
3 3 2011-12-12 00:00:00 |
|
\. |
|
|
|
|
|
-- |
|
-- Data for Name: products; Type: TABLE DATA; Schema: public; Owner: pgsql_user_name |
|
-- |
|
|
|
COPY public.products (id, name, price) FROM stdin; |
|
1 mobile 10000 |
|
2 keyboard 600 |
|
3 mouse 200 |
|
\. |
|
|
|
|
|
-- |
|
-- Name: order_items order_items_pkey; Type: CONSTRAINT; Schema: public; Owner: pgsql_user_name |
|
-- |
|
|
|
ALTER TABLE ONLY public.order_items |
|
ADD CONSTRAINT order_items_pkey PRIMARY KEY (id); |
|
|
|
|
|
-- |
|
-- Name: orders orders_pkey; Type: CONSTRAINT; Schema: public; Owner: pgsql_user_name |
|
-- |
|
|
|
ALTER TABLE ONLY public.orders |
|
ADD CONSTRAINT orders_pkey PRIMARY KEY (id); |
|
|
|
|
|
-- |
|
-- Name: products products_pkey; Type: CONSTRAINT; Schema: public; Owner: pgsql_user_name |
|
-- |
|
|
|
ALTER TABLE ONLY public.products |
|
ADD CONSTRAINT products_pkey PRIMARY KEY (id); |
|
|
|
|
|
-- |
|
-- PostgreSQL database dump complete |
|
-- |