1-- 2-- SELECT_INTO 3-- 4 5SELECT * 6 INTO TABLE sitmp1 7 FROM onek 8 WHERE onek.unique1 < 2; 9 10DROP TABLE sitmp1; 11 12SELECT * 13 INTO TABLE sitmp1 14 FROM onek2 15 WHERE onek2.unique1 < 2; 16 17DROP TABLE sitmp1; 18 19-- 20-- SELECT INTO and INSERT permission, if owner is not allowed to insert. 21-- 22CREATE SCHEMA selinto_schema; 23CREATE USER regress_selinto_user; 24ALTER DEFAULT PRIVILEGES FOR ROLE regress_selinto_user 25 REVOKE INSERT ON TABLES FROM regress_selinto_user; 26GRANT ALL ON SCHEMA selinto_schema TO public; 27 28SET SESSION AUTHORIZATION regress_selinto_user; 29SELECT * INTO TABLE selinto_schema.tmp1 30 FROM pg_class WHERE relname like '%a%'; -- Error 31SELECT oid AS clsoid, relname, relnatts + 10 AS x 32 INTO selinto_schema.tmp2 33 FROM pg_class WHERE relname like '%b%'; -- Error 34CREATE TABLE selinto_schema.tmp3 (a,b,c) 35 AS SELECT oid,relname,relacl FROM pg_class 36 WHERE relname like '%c%'; -- Error 37RESET SESSION AUTHORIZATION; 38 39ALTER DEFAULT PRIVILEGES FOR ROLE regress_selinto_user 40 GRANT INSERT ON TABLES TO regress_selinto_user; 41 42SET SESSION AUTHORIZATION regress_selinto_user; 43SELECT * INTO TABLE selinto_schema.tmp1 44 FROM pg_class WHERE relname like '%a%'; -- OK 45SELECT oid AS clsoid, relname, relnatts + 10 AS x 46 INTO selinto_schema.tmp2 47 FROM pg_class WHERE relname like '%b%'; -- OK 48CREATE TABLE selinto_schema.tmp3 (a,b,c) 49 AS SELECT oid,relname,relacl FROM pg_class 50 WHERE relname like '%c%'; -- OK 51RESET SESSION AUTHORIZATION; 52 53DROP SCHEMA selinto_schema CASCADE; 54DROP USER regress_selinto_user; 55 56-- Tests for WITH NO DATA and column name consistency 57CREATE TABLE ctas_base (i int, j int); 58INSERT INTO ctas_base VALUES (1, 2); 59CREATE TABLE ctas_nodata (ii, jj, kk) AS SELECT i, j FROM ctas_base; -- Error 60CREATE TABLE ctas_nodata (ii, jj, kk) AS SELECT i, j FROM ctas_base WITH NO DATA; -- Error 61CREATE TABLE ctas_nodata (ii, jj) AS SELECT i, j FROM ctas_base; -- OK 62CREATE TABLE ctas_nodata_2 (ii, jj) AS SELECT i, j FROM ctas_base WITH NO DATA; -- OK 63CREATE TABLE ctas_nodata_3 (ii) AS SELECT i, j FROM ctas_base; -- OK 64CREATE TABLE ctas_nodata_4 (ii) AS SELECT i, j FROM ctas_base WITH NO DATA; -- OK 65SELECT * FROM ctas_nodata; 66SELECT * FROM ctas_nodata_2; 67SELECT * FROM ctas_nodata_3; 68SELECT * FROM ctas_nodata_4; 69DROP TABLE ctas_base; 70DROP TABLE ctas_nodata; 71DROP TABLE ctas_nodata_2; 72DROP TABLE ctas_nodata_3; 73DROP TABLE ctas_nodata_4; 74 75-- 76-- CREATE TABLE AS/SELECT INTO as last command in a SQL function 77-- have been known to cause problems 78-- 79CREATE FUNCTION make_table() RETURNS VOID 80AS $$ 81 CREATE TABLE created_table AS SELECT * FROM int8_tbl; 82$$ LANGUAGE SQL; 83 84SELECT make_table(); 85 86SELECT * FROM created_table; 87 88-- Try EXPLAIN ANALYZE SELECT INTO and EXPLAIN ANALYZE CREATE TABLE AS 89-- WITH NO DATA, but hide the outputs since they won't be stable. 90DO $$ 91BEGIN 92 EXECUTE 'EXPLAIN ANALYZE SELECT * INTO TABLE easi FROM int8_tbl'; 93 EXECUTE 'EXPLAIN ANALYZE CREATE TABLE easi2 AS SELECT * FROM int8_tbl WITH NO DATA'; 94END$$; 95 96DROP TABLE created_table; 97DROP TABLE easi, easi2; 98 99-- 100-- Disallowed uses of SELECT ... INTO. All should fail 101-- 102DECLARE foo CURSOR FOR SELECT 1 INTO b; 103COPY (SELECT 1 INTO frak UNION SELECT 2) TO 'blob'; 104SELECT * FROM (SELECT 1 INTO f) bar; 105CREATE VIEW foo AS SELECT 1 INTO b; 106INSERT INTO b SELECT 1 INTO f; 107