SET client_min_messages = NOTICE;
SET
\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;
CREATE FUNCTION
/* 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 FUNCTION
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;
CREATE FUNCTION
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');
INSERT 0 1
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';
DELETE 1
-- Add tmpdir() to utl_file_dir table
INSERT INTO utl_file.utl_file_dir(dir) VALUES(utl_file.tmpdir());
INSERT 0 1
SELECT count(*) from utl_file.utl_file_dir where dir <> '';
 count 
-------
     0
(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 PASSWORD 'Test@123' LOGIN;
CREATE ROLE
SET SESSION AUTHORIZATION test_role_files PASSWORD 'Test@123';
SET
-- 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:  >><NULL><<
 checkflushfile 
----------------
 
(1 row)

SELECT utl_file.fremove(utl_file.tmpdir(), 'regressflush_orafce.txt');
 fremove 
---------
 
(1 row)

SET SESSION AUTHORIZATION DEFAULT;
SET
DROP ROLE test_role_files;
DROP ROLE
DROP FUNCTION checkFlushFile(text);
DROP FUNCTION
DELETE FROM utl_file.utl_file_dir;
DELETE 1
-- try to use named directory
INSERT INTO utl_file.utl_file_dir(dir, dirname) VALUES(utl_file.tmpdir(), 'TMPDIR');
INSERT 0 1
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
DROP FUNCTION read_file(text);
DROP FUNCTION
DELETE FROM utl_file.utl_file_dir;
DELETE 1