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
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
|
--
-- SELECT_INTO
--
SELECT *
INTO TABLE sitmp1
FROM onek
WHERE onek.unique1 < 2;
DROP TABLE sitmp1;
SELECT *
INTO TABLE sitmp1
FROM onek2
WHERE onek2.unique1 < 2;
DROP TABLE sitmp1;
--
-- SELECT INTO and INSERT permission, if owner is not allowed to insert.
--
CREATE SCHEMA selinto_schema;
CREATE USER regress_selinto_user;
ALTER DEFAULT PRIVILEGES FOR ROLE regress_selinto_user
REVOKE INSERT ON TABLES FROM regress_selinto_user;
GRANT ALL ON SCHEMA selinto_schema TO public;
SET SESSION AUTHORIZATION regress_selinto_user;
-- WITH DATA, passes.
CREATE TABLE selinto_schema.tbl_withdata1 (a)
AS SELECT generate_series(1,3) WITH DATA;
INSERT INTO selinto_schema.tbl_withdata1 VALUES (4);
ERROR: permission denied for table tbl_withdata1
EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
CREATE TABLE selinto_schema.tbl_withdata2 (a) AS
SELECT generate_series(1,3) WITH DATA;
QUERY PLAN
--------------------------------------
ProjectSet (actual rows=3 loops=1)
-> Result (actual rows=1 loops=1)
(2 rows)
-- WITH NO DATA, passes.
CREATE TABLE selinto_schema.tbl_nodata1 (a) AS
SELECT generate_series(1,3) WITH NO DATA;
EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
CREATE TABLE selinto_schema.tbl_nodata2 (a) AS
SELECT generate_series(1,3) WITH NO DATA;
QUERY PLAN
-------------------------------
ProjectSet (never executed)
-> Result (never executed)
(2 rows)
-- EXECUTE and WITH DATA, passes.
PREPARE data_sel AS SELECT generate_series(1,3);
CREATE TABLE selinto_schema.tbl_withdata3 (a) AS
EXECUTE data_sel WITH DATA;
EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
CREATE TABLE selinto_schema.tbl_withdata4 (a) AS
EXECUTE data_sel WITH DATA;
QUERY PLAN
--------------------------------------
ProjectSet (actual rows=3 loops=1)
-> Result (actual rows=1 loops=1)
(2 rows)
-- EXECUTE and WITH NO DATA, passes.
CREATE TABLE selinto_schema.tbl_nodata3 (a) AS
EXECUTE data_sel WITH NO DATA;
EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
CREATE TABLE selinto_schema.tbl_nodata4 (a) AS
EXECUTE data_sel WITH NO DATA;
QUERY PLAN
-------------------------------
ProjectSet (never executed)
-> Result (never executed)
(2 rows)
RESET SESSION AUTHORIZATION;
ALTER DEFAULT PRIVILEGES FOR ROLE regress_selinto_user
GRANT INSERT ON TABLES TO regress_selinto_user;
SET SESSION AUTHORIZATION regress_selinto_user;
RESET SESSION AUTHORIZATION;
DEALLOCATE data_sel;
DROP SCHEMA selinto_schema CASCADE;
NOTICE: drop cascades to 8 other objects
DETAIL: drop cascades to table selinto_schema.tbl_withdata1
drop cascades to table selinto_schema.tbl_withdata2
drop cascades to table selinto_schema.tbl_nodata1
drop cascades to table selinto_schema.tbl_nodata2
drop cascades to table selinto_schema.tbl_withdata3
drop cascades to table selinto_schema.tbl_withdata4
drop cascades to table selinto_schema.tbl_nodata3
drop cascades to table selinto_schema.tbl_nodata4
DROP USER regress_selinto_user;
-- Tests for WITH NO DATA and column name consistency
CREATE TABLE ctas_base (i int, j int);
INSERT INTO ctas_base VALUES (1, 2);
CREATE TABLE ctas_nodata (ii, jj, kk) AS SELECT i, j FROM ctas_base; -- Error
ERROR: too many column names were specified
CREATE TABLE ctas_nodata (ii, jj, kk) AS SELECT i, j FROM ctas_base WITH NO DATA; -- Error
ERROR: too many column names were specified
CREATE TABLE ctas_nodata (ii, jj) AS SELECT i, j FROM ctas_base; -- OK
CREATE TABLE ctas_nodata_2 (ii, jj) AS SELECT i, j FROM ctas_base WITH NO DATA; -- OK
CREATE TABLE ctas_nodata_3 (ii) AS SELECT i, j FROM ctas_base; -- OK
CREATE TABLE ctas_nodata_4 (ii) AS SELECT i, j FROM ctas_base WITH NO DATA; -- OK
SELECT * FROM ctas_nodata;
ii | jj
----+----
1 | 2
(1 row)
SELECT * FROM ctas_nodata_2;
ii | jj
----+----
(0 rows)
SELECT * FROM ctas_nodata_3;
ii | j
----+---
1 | 2
(1 row)
SELECT * FROM ctas_nodata_4;
ii | j
----+---
(0 rows)
DROP TABLE ctas_base;
DROP TABLE ctas_nodata;
DROP TABLE ctas_nodata_2;
DROP TABLE ctas_nodata_3;
DROP TABLE ctas_nodata_4;
--
-- CREATE TABLE AS/SELECT INTO as last command in a SQL function
-- have been known to cause problems
--
CREATE FUNCTION make_table() RETURNS VOID
AS $$
CREATE TABLE created_table AS SELECT * FROM int8_tbl;
$$ LANGUAGE SQL;
SELECT make_table();
make_table
------------
(1 row)
SELECT * FROM created_table;
q1 | q2
------------------+-------------------
123 | 456
123 | 4567890123456789
4567890123456789 | 123
4567890123456789 | 4567890123456789
4567890123456789 | -4567890123456789
(5 rows)
-- Try EXPLAIN ANALYZE SELECT INTO and EXPLAIN ANALYZE CREATE TABLE AS
-- WITH NO DATA, but hide the outputs since they won't be stable.
DO $$
BEGIN
EXECUTE 'EXPLAIN ANALYZE SELECT * INTO TABLE easi FROM int8_tbl';
EXECUTE 'EXPLAIN ANALYZE CREATE TABLE easi2 AS SELECT * FROM int8_tbl WITH NO DATA';
END$$;
DROP TABLE created_table;
DROP TABLE easi, easi2;
--
-- Disallowed uses of SELECT ... INTO. All should fail
--
DECLARE foo CURSOR FOR SELECT 1 INTO b;
ERROR: SELECT ... INTO is not allowed here
LINE 1: DECLARE foo CURSOR FOR SELECT 1 INTO b;
^
COPY (SELECT 1 INTO frak UNION SELECT 2) TO 'blob';
ERROR: COPY (SELECT INTO) is not supported
SELECT * FROM (SELECT 1 INTO f) bar;
ERROR: SELECT ... INTO is not allowed here
LINE 1: SELECT * FROM (SELECT 1 INTO f) bar;
^
CREATE VIEW foo AS SELECT 1 INTO b;
ERROR: views must not contain SELECT INTO
INSERT INTO b SELECT 1 INTO f;
ERROR: SELECT ... INTO is not allowed here
LINE 1: INSERT INTO b SELECT 1 INTO f;
^
-- Test CREATE TABLE AS ... IF NOT EXISTS
CREATE TABLE ctas_ine_tbl AS SELECT 1;
CREATE TABLE ctas_ine_tbl AS SELECT 1 / 0; -- error
ERROR: relation "ctas_ine_tbl" already exists
CREATE TABLE IF NOT EXISTS ctas_ine_tbl AS SELECT 1 / 0; -- ok
NOTICE: relation "ctas_ine_tbl" already exists, skipping
CREATE TABLE ctas_ine_tbl AS SELECT 1 / 0 WITH NO DATA; -- error
ERROR: relation "ctas_ine_tbl" already exists
CREATE TABLE IF NOT EXISTS ctas_ine_tbl AS SELECT 1 / 0 WITH NO DATA; -- ok
NOTICE: relation "ctas_ine_tbl" already exists, skipping
EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
CREATE TABLE ctas_ine_tbl AS SELECT 1 / 0; -- error
ERROR: relation "ctas_ine_tbl" already exists
EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
CREATE TABLE IF NOT EXISTS ctas_ine_tbl AS SELECT 1 / 0; -- ok
NOTICE: relation "ctas_ine_tbl" already exists, skipping
QUERY PLAN
------------
(0 rows)
EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
CREATE TABLE ctas_ine_tbl AS SELECT 1 / 0 WITH NO DATA; -- error
ERROR: relation "ctas_ine_tbl" already exists
EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
CREATE TABLE IF NOT EXISTS ctas_ine_tbl AS SELECT 1 / 0 WITH NO DATA; -- ok
NOTICE: relation "ctas_ine_tbl" already exists, skipping
QUERY PLAN
------------
(0 rows)
PREPARE ctas_ine_query AS SELECT 1 / 0;
EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
CREATE TABLE ctas_ine_tbl AS EXECUTE ctas_ine_query; -- error
ERROR: relation "ctas_ine_tbl" already exists
EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
CREATE TABLE IF NOT EXISTS ctas_ine_tbl AS EXECUTE ctas_ine_query; -- ok
NOTICE: relation "ctas_ine_tbl" already exists, skipping
QUERY PLAN
------------
(0 rows)
DROP TABLE ctas_ine_tbl;
|
