1-- 2-- SELECT_INTO 3-- 4 5SELECT * 6 INTO TABLE tmp1 7 FROM onek 8 WHERE onek.unique1 < 2; 9 10DROP TABLE tmp1; 11 12SELECT * 13 INTO TABLE tmp1 14 FROM onek2 15 WHERE onek2.unique1 < 2; 16 17DROP TABLE tmp1; 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, but hide the output since it won't 89-- be stable. 90DO $$ 91BEGIN 92 EXECUTE 'EXPLAIN ANALYZE SELECT * INTO TABLE easi FROM int8_tbl'; 93END$$; 94 95DROP TABLE created_table; 96DROP TABLE easi; 97 98-- 99-- Disallowed uses of SELECT ... INTO. All should fail 100-- 101DECLARE foo CURSOR FOR SELECT 1 INTO b; 102COPY (SELECT 1 INTO frak UNION SELECT 2) TO 'blob'; 103SELECT * FROM (SELECT 1 INTO f) bar; 104CREATE VIEW foo AS SELECT 1 INTO b; 105INSERT INTO b SELECT 1 INTO f; 106