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