7 WHERE onek.unique1 < 2;
12 WHERE onek2.unique1 < 2;
15 -- SELECT INTO and INSERT permission, if owner is not allowed to insert.
17 CREATE SCHEMA selinto_schema;
18 CREATE USER regress_selinto_user;
19 ALTER DEFAULT PRIVILEGES FOR ROLE regress_selinto_user
20 REVOKE INSERT ON TABLES FROM regress_selinto_user;
21 GRANT ALL ON SCHEMA selinto_schema TO public;
22 SET SESSION AUTHORIZATION regress_selinto_user;
23 SELECT * INTO TABLE selinto_schema.tmp1
24 FROM pg_class WHERE relname like '%a%'; -- Error
25 ERROR: permission denied for relation tmp1
26 SELECT oid AS clsoid, relname, relnatts + 10 AS x
27 INTO selinto_schema.tmp2
28 FROM pg_class WHERE relname like '%b%'; -- Error
29 ERROR: permission denied for relation tmp2
30 CREATE TABLE selinto_schema.tmp3 (a,b,c)
31 AS SELECT oid,relname,relacl FROM pg_class
32 WHERE relname like '%c%'; -- Error
33 ERROR: permission denied for relation tmp3
34 RESET SESSION AUTHORIZATION;
35 ALTER DEFAULT PRIVILEGES FOR ROLE regress_selinto_user
36 GRANT INSERT ON TABLES TO regress_selinto_user;
37 SET SESSION AUTHORIZATION regress_selinto_user;
38 SELECT * INTO TABLE selinto_schema.tmp1
39 FROM pg_class WHERE relname like '%a%'; -- OK
40 SELECT oid AS clsoid, relname, relnatts + 10 AS x
41 INTO selinto_schema.tmp2
42 FROM pg_class WHERE relname like '%b%'; -- OK
43 CREATE TABLE selinto_schema.tmp3 (a,b,c)
44 AS SELECT oid,relname,relacl FROM pg_class
45 WHERE relname like '%c%'; -- OK
46 RESET SESSION AUTHORIZATION;
47 DROP SCHEMA selinto_schema CASCADE;
48 NOTICE: drop cascades to 3 other objects
49 DETAIL: drop cascades to table selinto_schema.tmp1
50 drop cascades to table selinto_schema.tmp2
51 drop cascades to table selinto_schema.tmp3
52 DROP USER regress_selinto_user;
53 -- Tests for WITH NO DATA and column name consistency
54 CREATE TABLE ctas_base (i int, j int);
55 INSERT INTO ctas_base VALUES (1, 2);
56 CREATE TABLE ctas_nodata (ii, jj, kk) AS SELECT i, j FROM ctas_base; -- Error
57 ERROR: too many column names were specified
58 CREATE TABLE ctas_nodata (ii, jj, kk) AS SELECT i, j FROM ctas_base WITH NO DATA; -- Error
59 ERROR: too many column names were specified
60 CREATE TABLE ctas_nodata (ii, jj) AS SELECT i, j FROM ctas_base; -- OK
61 CREATE TABLE ctas_nodata_2 (ii, jj) AS SELECT i, j FROM ctas_base WITH NO DATA; -- OK
62 CREATE TABLE ctas_nodata_3 (ii) AS SELECT i, j FROM ctas_base; -- OK
63 CREATE TABLE ctas_nodata_4 (ii) AS SELECT i, j FROM ctas_base WITH NO DATA; -- OK
64 SELECT * FROM ctas_nodata;
70 SELECT * FROM ctas_nodata_2;
75 SELECT * FROM ctas_nodata_3;
81 SELECT * FROM ctas_nodata_4;
87 DROP TABLE ctas_nodata;
88 DROP TABLE ctas_nodata_2;
89 DROP TABLE ctas_nodata_3;
90 DROP TABLE ctas_nodata_4;
92 -- CREATE TABLE AS/SELECT INTO as last command in a SQL function
93 -- have been known to cause problems
95 CREATE FUNCTION make_table() RETURNS VOID
97 CREATE TABLE created_table AS SELECT * FROM int8_tbl;
105 SELECT * FROM created_table;
107 ------------------+-------------------
109 123 | 4567890123456789
110 4567890123456789 | 123
111 4567890123456789 | 4567890123456789
112 4567890123456789 | -4567890123456789
115 -- Try EXPLAIN ANALYZE SELECT INTO, but hide the output since it won't
119 EXECUTE 'EXPLAIN ANALYZE SELECT * INTO TABLE easi FROM int8_tbl';
121 DROP TABLE created_table;
124 -- Disallowed uses of SELECT ... INTO. All should fail
126 DECLARE foo CURSOR FOR SELECT 1 INTO b;
127 ERROR: SELECT ... INTO is not allowed here
128 LINE 1: DECLARE foo CURSOR FOR SELECT 1 INTO b;
130 COPY (SELECT 1 INTO frak UNION SELECT 2) TO 'blob';
131 ERROR: COPY (SELECT INTO) is not supported
132 SELECT * FROM (SELECT 1 INTO f) bar;
133 ERROR: SELECT ... INTO is not allowed here
134 LINE 1: SELECT * FROM (SELECT 1 INTO f) bar;
136 CREATE VIEW foo AS SELECT 1 INTO b;
137 ERROR: views must not contain SELECT INTO
138 INSERT INTO b SELECT 1 INTO f;
139 ERROR: SELECT ... INTO is not allowed here
140 LINE 1: INSERT INTO b SELECT 1 INTO f;