SET client_min_messages = NOTICE; \set VERBOSITY terse \set ECHO all CREATE OR REPLACE FUNCTION gen_file(dir text) RETURNS void AS $$ DECLARE f utl_file.file_type; BEGIN f := utl_file.fopen(dir, 'regress_orafce.txt', 'w'); PERFORM utl_file.put_line(f, 'ABC'); PERFORM utl_file.put_line(f, '123'::numeric); PERFORM utl_file.put_line(f, '-----'); PERFORM utl_file.new_line(f); PERFORM utl_file.put_line(f, '-----'); PERFORM utl_file.new_line(f, 0); PERFORM utl_file.put_line(f, '-----'); PERFORM utl_file.new_line(f, 2); PERFORM utl_file.put_line(f, '-----'); PERFORM utl_file.put(f, 'A'); PERFORM utl_file.put(f, 'B'); PERFORM utl_file.new_line(f); PERFORM utl_file.putf(f, '[1=%s, 2=%s, 3=%s, 4=%s, 5=%s]', '1', '2', '3', '4', '5'); PERFORM utl_file.new_line(f); PERFORM utl_file.put_line(f, '1234567890'); f := utl_file.fclose(f); END; $$ LANGUAGE plpgsql; /* Test functions utl_file.fflush(utl_file.file_type) and * utl_file.get_nextline(utl_file.file_type) * This function tests the positive test case of fflush by reading from the * file after flushing the contents to the file. */ CREATE OR REPLACE FUNCTION checkFlushFile(dir text) RETURNS void AS $$ DECLARE f utl_file.file_type; f1 utl_file.file_type; ret_val text; i integer; BEGIN f := utl_file.fopen(dir, 'regressflush_orafce.txt', 'a'); PERFORM utl_file.put_line(f, 'ABC'); PERFORM utl_file.new_line(f); PERFORM utl_file.put_line(f, '123'::numeric); PERFORM utl_file.new_line(f); PERFORM utl_file.putf(f, '[1=%s, 2=%s, 3=%s, 4=%s, 5=%s]', '1', '2', '3', '4', '5'); PERFORM utl_file.fflush(f); f1 := utl_file.fopen(dir, 'regressflush_orafce.txt', 'r'); ret_val=utl_file.get_nextline(f1); i:=1; WHILE ret_val IS NOT NULL LOOP RAISE NOTICE '[%] >>%<<', i,ret_val; ret_val := utl_file.get_nextline(f1); i:=i+1; END LOOP; RAISE NOTICE '>>%<<', ret_val; f1 := utl_file.fclose(f1); f := utl_file.fclose(f); END; $$ LANGUAGE plpgsql; CREATE OR REPLACE FUNCTION read_file(dir text) RETURNS void AS $$ DECLARE f utl_file.file_type; BEGIN f := utl_file.fopen(dir, 'regress_orafce.txt', 'r'); FOR i IN 1..11 LOOP RAISE NOTICE '[%] >>%<<', i, utl_file.get_line(f); END LOOP; RAISE NOTICE '>>%<<', utl_file.get_line(f, 4); RAISE NOTICE '>>%<<', utl_file.get_line(f, 4); RAISE NOTICE '>>%<<', utl_file.get_line(f); RAISE NOTICE '>>%<<', utl_file.get_line(f); EXCEPTION -- WHEN no_data_found THEN, 8.1 plpgsql doesn't know no_data_found WHEN others THEN RAISE NOTICE 'finish % ', sqlerrm; RAISE NOTICE 'is_open = %', utl_file.is_open(f); PERFORM utl_file.fclose_all(); RAISE NOTICE 'is_open = %', utl_file.is_open(f); END; $$ LANGUAGE plpgsql; SELECT EXISTS(SELECT * FROM pg_catalog.pg_class where relname='utl_file_dir') AS exists; exists -------- t (1 row) SELECT EXISTS(SELECT * FROM pg_catalog.pg_type where typname='file_type') AS exists; exists -------- t (1 row) -- Trying to access a file in path not registered SELECT utl_file.fopen(utl_file.tmpdir(),'sample.txt','r'); ERROR: UTL_FILE_INVALID_PATH -- Trying to access file in a non-existent directory INSERT INTO utl_file.utl_file_dir(dir) VALUES('test_tmp_dir'); SELECT utl_file.fopen('test_tmp_dir','file.txt.','w'); ERROR: UTL_FILE_INVALID_PATH DELETE FROM utl_file.utl_file_dir WHERE dir LIKE 'test_tmp_dir'; -- Add tmpdir() to utl_file_dir table INSERT INTO utl_file.utl_file_dir(dir) VALUES(utl_file.tmpdir()); SELECT count(*) from utl_file.utl_file_dir where dir <> ''; count ------- 1 (1 row) -- Trying to access non-existent file SELECT utl_file.fopen(utl_file.tmpdir(),'non_existent_file.txt','r'); ERROR: UTL_FILE_INVALID_PATH --Other test cases --run this under unprivileged user CREATE ROLE test_role_files LOGIN; SET ROLE TO test_role_files; -- should to fail, unpriviliged user cannot to change utl_file_dir INSERT INTO utl_file.utl_file_dir(dir) VALUES('test_tmp_dir'); ERROR: permission denied for relation utl_file_dir SELECT gen_file(utl_file.tmpdir()); gen_file ---------- (1 row) SELECT fexists FROM utl_file.fgetattr(utl_file.tmpdir(), 'regress_orafce.txt'); fexists --------- t (1 row) SELECT utl_file.fcopy(utl_file.tmpdir(), 'regress_orafce.txt', utl_file.tmpdir(), 'regress_orafce2.txt'); fcopy ------- (1 row) SELECT fexists FROM utl_file.fgetattr(utl_file.tmpdir(), 'regress_orafce2.txt'); fexists --------- t (1 row) SELECT utl_file.frename(utl_file.tmpdir(), 'regress_orafce2.txt', utl_file.tmpdir(), 'regress_orafce.txt', true); frename --------- (1 row) SELECT fexists FROM utl_file.fgetattr(utl_file.tmpdir(), 'regress_orafce.txt'); fexists --------- t (1 row) SELECT fexists FROM utl_file.fgetattr(utl_file.tmpdir(), 'regress_orafce2.txt'); fexists --------- f (1 row) SELECT read_file(utl_file.tmpdir()); NOTICE: [1] >>ABC<< NOTICE: [2] >>123<< NOTICE: [3] >>-----<< NOTICE: [4] >><< NOTICE: [5] >>-----<< NOTICE: [6] >>-----<< NOTICE: [7] >><< NOTICE: [8] >><< NOTICE: [9] >>-----<< NOTICE: [10] >>AB<< NOTICE: [11] >>[1=1, 2=2, 3=3, 4=4, 5=5]<< NOTICE: >>1234<< NOTICE: >>5678<< NOTICE: >>90<< NOTICE: finish no data found NOTICE: is_open = t NOTICE: is_open = f read_file ----------- (1 row) SELECT utl_file.fremove(utl_file.tmpdir(), 'regress_orafce.txt'); fremove --------- (1 row) SELECT fexists FROM utl_file.fgetattr(utl_file.tmpdir(), 'regress_orafce.txt'); fexists --------- f (1 row) SELECT checkFlushFile(utl_file.tmpdir()); NOTICE: [1] >>ABC<< NOTICE: [2] >><< NOTICE: [3] >>123<< NOTICE: [4] >><< NOTICE: [5] >>[1=1, 2=2, 3=3, 4=4, 5=5]<< NOTICE: >><< checkflushfile ---------------- (1 row) SELECT utl_file.fremove(utl_file.tmpdir(), 'regressflush_orafce.txt'); fremove --------- (1 row) SET ROLE TO DEFAULT; DROP ROLE test_role_files; DROP FUNCTION checkFlushFile(text); DELETE FROM utl_file.utl_file_dir; -- try to use named directory INSERT INTO utl_file.utl_file_dir(dir, dirname) VALUES(utl_file.tmpdir(), 'TMPDIR'); SELECT gen_file('TMPDIR'); gen_file ---------- (1 row) SELECT read_file('TMPDIR'); NOTICE: [1] >>ABC<< NOTICE: [2] >>123<< NOTICE: [3] >>-----<< NOTICE: [4] >><< NOTICE: [5] >>-----<< NOTICE: [6] >>-----<< NOTICE: [7] >><< NOTICE: [8] >><< NOTICE: [9] >>-----<< NOTICE: [10] >>AB<< NOTICE: [11] >>[1=1, 2=2, 3=3, 4=4, 5=5]<< NOTICE: >>1234<< NOTICE: >>5678<< NOTICE: >>90<< NOTICE: finish no data found NOTICE: is_open = t NOTICE: is_open = f read_file ----------- (1 row) SELECT utl_file.fremove('TMPDIR', 'regress_orafce.txt'); fremove --------- (1 row) DROP FUNCTION gen_file(text); DROP FUNCTION read_file(text); DELETE FROM utl_file.utl_file_dir;