-
Notifications
You must be signed in to change notification settings - Fork 16
/
Copy pathcreate-view.html
135 lines (125 loc) · 7.17 KB
/
create-view.html
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>9.12. CREATE VIEW — Presto 335 Documentation</title>
<link rel="stylesheet" href="../_static/presto.css" type="text/css" />
<link rel="stylesheet" href="../_static/pygments.css" type="text/css" />
<script type="text/javascript">
var DOCUMENTATION_OPTIONS = {
URL_ROOT: '../',
VERSION: '335',
COLLAPSE_INDEX: false,
FILE_SUFFIX: '.html',
HAS_SOURCE: true,
SOURCELINK_SUFFIX: '.txt'
};
</script>
<script type="text/javascript" src="../_static/jquery.js"></script>
<script type="text/javascript" src="../_static/underscore.js"></script>
<script type="text/javascript" src="../_static/doctools.js"></script>
<link rel="index" title="Index" href="../genindex.html" />
<link rel="search" title="Search" href="../search.html" />
<link rel="next" title="9.13. DEALLOCATE PREPARE" href="deallocate-prepare.html" />
<link rel="prev" title="9.11. CREATE TABLE AS" href="create-table-as.html" />
<script async src="https://www.googletagmanager.com/gtag/js?id=UA-133457846-1"></script>
<script>
window.dataLayer = window.dataLayer || [];
function gtag(){dataLayer.push(arguments);}
gtag('js', new Date());
gtag('config', 'UA-133457846-1');
</script>
</head>
<body>
<div class="header">
<h1 class="heading"><a href="../index.html">
<span>Presto 335 Documentation</span></a></h1>
<h2 class="heading"><span>9.12. CREATE VIEW</span></h2>
</div>
<div class="topnav">
<p class="nav">
<span class="left">
« <a href="create-table-as.html">9.11. CREATE TABLE AS</a>
</span>
<span class="right">
<a href="deallocate-prepare.html">9.13. DEALLOCATE PREPARE</a> »
</span>
</p>
</div>
<div class="content">
<div class="section" id="create-view">
<h1>9.12. CREATE VIEW<a class="headerlink" href="#create-view" title="Permalink to this headline">#</a></h1>
<div class="section" id="synopsis">
<h2>Synopsis<a class="headerlink" href="#synopsis" title="Permalink to this headline">#</a></h2>
<div class="highlight-none"><div class="highlight"><pre><span></span>CREATE [ OR REPLACE ] VIEW view_name
[ SECURITY { DEFINER | INVOKER } ]
AS query
</pre></div>
</div>
</div>
<div class="section" id="description">
<h2>Description<a class="headerlink" href="#description" title="Permalink to this headline">#</a></h2>
<p>Create a new view of a <a class="reference internal" href="select.html"><span class="doc">SELECT</span></a> query. The view is a logical table
that can be referenced by future queries. Views do not contain any data.
Instead, the query stored by the view is executed everytime the view is
referenced by another query.</p>
<p>The optional <code class="docutils literal"><span class="pre">OR</span> <span class="pre">REPLACE</span></code> clause causes the view to be replaced if it
already exists rather than raising an error.</p>
</div>
<div class="section" id="security">
<h2>Security<a class="headerlink" href="#security" title="Permalink to this headline">#</a></h2>
<p>In the default <code class="docutils literal"><span class="pre">DEFINER</span></code> security mode, tables referenced in the view
are accessed using the permissions of the view owner (the <em>creator</em> or
<em>definer</em> of the view) rather than the user executing the query. This
allows providing restricted access to the underlying tables, for which
the user may not be allowed to access directly.</p>
<p>In the <code class="docutils literal"><span class="pre">INVOKER</span></code> security mode, tables referenced in the view are accessed
using the permissions of the user executing the query (the <em>invoker</em> of the view).
A view created in this mode is simply a stored query.</p>
<p>Regardless of the security mode, the <code class="docutils literal"><span class="pre">current_user</span></code> function will
always return the user executing the query and thus may be used
within views to filter out rows or otherwise restrict access.</p>
</div>
<div class="section" id="examples">
<h2>Examples<a class="headerlink" href="#examples" title="Permalink to this headline">#</a></h2>
<p>Create a simple view <code class="docutils literal"><span class="pre">test</span></code> over the <code class="docutils literal"><span class="pre">orders</span></code> table:</p>
<div class="highlight-sql"><div class="highlight"><pre><span></span><span class="k">CREATE</span> <span class="k">VIEW</span> <span class="n">test</span> <span class="k">AS</span>
<span class="k">SELECT</span> <span class="n">orderkey</span><span class="p">,</span> <span class="n">orderstatus</span><span class="p">,</span> <span class="n">totalprice</span> <span class="o">/</span> <span class="mi">2</span> <span class="k">AS</span> <span class="n">half</span>
<span class="k">FROM</span> <span class="n">orders</span>
</pre></div>
</div>
<p>Create a view <code class="docutils literal"><span class="pre">orders_by_date</span></code> that summarizes <code class="docutils literal"><span class="pre">orders</span></code>:</p>
<div class="highlight-sql"><div class="highlight"><pre><span></span><span class="k">CREATE</span> <span class="k">VIEW</span> <span class="n">orders_by_date</span> <span class="k">AS</span>
<span class="k">SELECT</span> <span class="n">orderdate</span><span class="p">,</span> <span class="k">sum</span><span class="p">(</span><span class="n">totalprice</span><span class="p">)</span> <span class="k">AS</span> <span class="n">price</span>
<span class="k">FROM</span> <span class="n">orders</span>
<span class="k">GROUP</span> <span class="k">BY</span> <span class="n">orderdate</span>
</pre></div>
</div>
<p>Create a view that replaces an existing view:</p>
<div class="highlight-sql"><div class="highlight"><pre><span></span><span class="k">CREATE</span> <span class="k">OR</span> <span class="k">REPLACE</span> <span class="k">VIEW</span> <span class="n">test</span> <span class="k">AS</span>
<span class="k">SELECT</span> <span class="n">orderkey</span><span class="p">,</span> <span class="n">orderstatus</span><span class="p">,</span> <span class="n">totalprice</span> <span class="o">/</span> <span class="mi">4</span> <span class="k">AS</span> <span class="n">quarter</span>
<span class="k">FROM</span> <span class="n">orders</span>
</pre></div>
</div>
</div>
<div class="section" id="see-also">
<h2>See Also<a class="headerlink" href="#see-also" title="Permalink to this headline">#</a></h2>
<p><a class="reference internal" href="drop-view.html"><span class="doc">DROP VIEW</span></a>, <a class="reference internal" href="show-create-view.html"><span class="doc">SHOW CREATE VIEW</span></a></p>
</div>
</div>
</div>
<div class="bottomnav">
<p class="nav">
<span class="left">
« <a href="create-table-as.html">9.11. CREATE TABLE AS</a>
</span>
<span class="right">
<a href="deallocate-prepare.html">9.13. DEALLOCATE PREPARE</a> »
</span>
</p>
</div>
<div class="footer" role="contentinfo">
</div>
</body>
</html>