1--
2-- SELECT_INTO
3--
4SELECT *
5   INTO TABLE sitmp1
6   FROM onek
7   WHERE onek.unique1 < 2;
8DROP TABLE sitmp1;
9SELECT *
10   INTO TABLE sitmp1
11   FROM onek2
12   WHERE onek2.unique1 < 2;
13DROP TABLE sitmp1;
14--
15-- SELECT INTO and INSERT permission, if owner is not allowed to insert.
16--
17CREATE SCHEMA selinto_schema;
18CREATE USER regress_selinto_user;
19ALTER DEFAULT PRIVILEGES FOR ROLE regress_selinto_user
20	  REVOKE INSERT ON TABLES FROM regress_selinto_user;
21GRANT ALL ON SCHEMA selinto_schema TO public;
22SET SESSION AUTHORIZATION regress_selinto_user;
23SELECT * INTO TABLE selinto_schema.tmp1
24	  FROM pg_class WHERE relname like '%a%';	-- Error
25ERROR:  permission denied for table tmp1
26SELECT oid AS clsoid, relname, relnatts + 10 AS x
27	  INTO selinto_schema.tmp2
28	  FROM pg_class WHERE relname like '%b%';	-- Error
29ERROR:  permission denied for table tmp2
30CREATE TABLE selinto_schema.tmp3 (a,b,c)
31	   AS SELECT oid,relname,relacl FROM pg_class
32	   WHERE relname like '%c%';	-- Error
33ERROR:  permission denied for table tmp3
34RESET SESSION AUTHORIZATION;
35ALTER DEFAULT PRIVILEGES FOR ROLE regress_selinto_user
36	  GRANT INSERT ON TABLES TO regress_selinto_user;
37SET SESSION AUTHORIZATION regress_selinto_user;
38SELECT * INTO TABLE selinto_schema.tmp1
39	  FROM pg_class WHERE relname like '%a%';	-- OK
40SELECT oid AS clsoid, relname, relnatts + 10 AS x
41	  INTO selinto_schema.tmp2
42	  FROM pg_class WHERE relname like '%b%';	-- OK
43CREATE TABLE selinto_schema.tmp3 (a,b,c)
44	   AS SELECT oid,relname,relacl FROM pg_class
45	   WHERE relname like '%c%';	-- OK
46RESET SESSION AUTHORIZATION;
47DROP SCHEMA selinto_schema CASCADE;
48NOTICE:  drop cascades to 3 other objects
49DETAIL:  drop cascades to table selinto_schema.tmp1
50drop cascades to table selinto_schema.tmp2
51drop cascades to table selinto_schema.tmp3
52DROP USER regress_selinto_user;
53-- Tests for WITH NO DATA and column name consistency
54CREATE TABLE ctas_base (i int, j int);
55INSERT INTO ctas_base VALUES (1, 2);
56CREATE TABLE ctas_nodata (ii, jj, kk) AS SELECT i, j FROM ctas_base; -- Error
57ERROR:  too many column names were specified
58CREATE TABLE ctas_nodata (ii, jj, kk) AS SELECT i, j FROM ctas_base WITH NO DATA; -- Error
59ERROR:  too many column names were specified
60CREATE TABLE ctas_nodata (ii, jj) AS SELECT i, j FROM ctas_base; -- OK
61CREATE TABLE ctas_nodata_2 (ii, jj) AS SELECT i, j FROM ctas_base WITH NO DATA; -- OK
62CREATE TABLE ctas_nodata_3 (ii) AS SELECT i, j FROM ctas_base; -- OK
63CREATE TABLE ctas_nodata_4 (ii) AS SELECT i, j FROM ctas_base WITH NO DATA; -- OK
64SELECT * FROM ctas_nodata;
65 ii | jj
66----+----
67  1 |  2
68(1 row)
69
70SELECT * FROM ctas_nodata_2;
71 ii | jj
72----+----
73(0 rows)
74
75SELECT * FROM ctas_nodata_3;
76 ii | j
77----+---
78  1 | 2
79(1 row)
80
81SELECT * FROM ctas_nodata_4;
82 ii | j
83----+---
84(0 rows)
85
86DROP TABLE ctas_base;
87DROP TABLE ctas_nodata;
88DROP TABLE ctas_nodata_2;
89DROP TABLE ctas_nodata_3;
90DROP TABLE ctas_nodata_4;
91--
92-- CREATE TABLE AS/SELECT INTO as last command in a SQL function
93-- have been known to cause problems
94--
95CREATE FUNCTION make_table() RETURNS VOID
96AS $$
97  CREATE TABLE created_table AS SELECT * FROM int8_tbl;
98$$ LANGUAGE SQL;
99SELECT make_table();
100 make_table
101------------
102
103(1 row)
104
105SELECT * FROM created_table;
106        q1        |        q2
107------------------+-------------------
108              123 |               456
109              123 |  4567890123456789
110 4567890123456789 |               123
111 4567890123456789 |  4567890123456789
112 4567890123456789 | -4567890123456789
113(5 rows)
114
115-- Try EXPLAIN ANALYZE SELECT INTO and EXPLAIN ANALYZE CREATE TABLE AS
116-- WITH NO DATA, but hide the outputs since they won't be stable.
117DO $$
118BEGIN
119	EXECUTE 'EXPLAIN ANALYZE SELECT * INTO TABLE easi FROM int8_tbl';
120	EXECUTE 'EXPLAIN ANALYZE CREATE TABLE easi2 AS SELECT * FROM int8_tbl WITH NO DATA';
121END$$;
122DROP TABLE created_table;
123DROP TABLE easi, easi2;
124--
125-- Disallowed uses of SELECT ... INTO.  All should fail
126--
127DECLARE foo CURSOR FOR SELECT 1 INTO b;
128ERROR:  SELECT ... INTO is not allowed here
129LINE 1: DECLARE foo CURSOR FOR SELECT 1 INTO b;
130                                             ^
131COPY (SELECT 1 INTO frak UNION SELECT 2) TO 'blob';
132ERROR:  COPY (SELECT INTO) is not supported
133SELECT * FROM (SELECT 1 INTO f) bar;
134ERROR:  SELECT ... INTO is not allowed here
135LINE 1: SELECT * FROM (SELECT 1 INTO f) bar;
136                                     ^
137CREATE VIEW foo AS SELECT 1 INTO b;
138ERROR:  views must not contain SELECT INTO
139INSERT INTO b SELECT 1 INTO f;
140ERROR:  SELECT ... INTO is not allowed here
141LINE 1: INSERT INTO b SELECT 1 INTO f;
142                                    ^
143