Text files can be written and read using PL/pgSQL.
To perform these file operations, the directory for the operation target must be registered in the UTL_FILE.UTL_FILE_DIR table beforehand. Use the INSERT statement as the database administrator or a user who has INSERT privileges to register the directory. Also, if the directory is no longer necessary, delete it from the same table. Refer to "9.5.2.1 Registering and Deleting Directories" for information on the how to register and delete the directory.
Refer to "C.1 UTL_FILE.UTL_FILE_DIR" for information on the UTL_FILE.UTL_FILE_DIR table.
Declare the file handler explained hereafter as follows in PL/pgSQL:
DECLARE f UTL_FILE.FILE_TYPE;
Feature | Description |
|---|---|
FCLOSE | Closes the specified file. |
FCLOSE_ALL | Closes all files open in a session. |
FCOPY | Copies a file. |
FFLUSH | Flushes the buffer. |
FGETATTR | Acquires the file existence and the size information. |
FOPEN | Opens a file. |
FRENAME | Changes the name of the file. |
GET_LINE | Reads one line from a text file. |
IS_OPEN | Checks if there are open files. |
NEW_LINE | Writes a newline. |
PUT | Writes a character string. |
PUT_LINE | Adds a string and a newline and writes them. |
PUTF | Writes a formatted character string. |
The examples in this sections are for Linux. In Windows(R), use a forward slash (/) as the separator for the directory.
Windows(R) example: 'c:/symfo'
Registering the directory
Check if the directory is already registered (if it is, then step 2 is not necessary).
SELECT * FROM UTL_FILE.UTL_FILE_DIR WHERE dir='/home/symfo';
Register the directory.
INSERT INTO UTL_FILE.UTL_FILE_DIR VALUES('/home/symfo');Deleting the directory
DELETE FROM UTL_FILE.UTL_FILE_DIR WHERE dir='/home/symfo';
This section explains the procedures available in the UTL_FILE package.
FCLOSE closes a file that is open.
Specify an open file handler as the file handler.
The value returned is a NULL value.
Example
f := UTL_FILE.FCLOSE(f);
FCLOSE_ALL closes all files open in a session.
Files closed with FCLOSE_ALL can no longer be read or written.
Example
PERFORM UTL_FILE.FCLOSE_ALL();
FCOPY copies from a copy source file to a copy destination file.
Specify the directory where the copy source file exists as the copy source directory name.
Specify the copy source file name as the file name of the copy source.
Specify the directory where the copy destination file is stored as the copy destination directory name.
Specify the copy destination file name as the file name of the copy destination.
Specify a slash (/) or a backslash (\) as a separator in the copy source directory name and the copy destination directory name.
Specify the starting line number of the file to be copied as the beginning line. If specifying, specify a value greater than 0. The starting line number is 1 if this is not specified.
Specify the end line number of the file to be copied as the end line. The last line number of the file is used if this is not specified.
Example
Linux
PERFORM UTL_FILE.FCOPY('/home/symfo', 'regress_symfo.txt', '/home/symfo', 'regress_symfo2.txt');Windows(R)
PERFORM UTL_FILE.FCOPY('c:/symfo', 'regress_symfo.txt', 'c:/symfo', 'regress_symfo2.txt');FFLUSH forcibly writes the buffer data to a file.
Specify an open file handler as the file handler.
Example
PERFORM UTL_FILE.FFLUSH(f);
FGETATTR extracts the file existence, file size, and information about the block size of the file.
Specify the directory where the relevant file exists as the directory name.
Specify a slash (/) or a backslash (\) as a separator in the directory name.
Specify the relevant file name as the file name.
The information extracted is acquired as the values of the fexists, file_length, and blocksize columns with the SELECT statement.
fexists is a BOOLEAN type. If the file exists, the logical value is "true". If the file does not exist, the logical value is "false".
file_length is an INTEGER type. The length of the file is set in bytes. If the file does not exist, the value is NULL.
blocksize is an INTEGER type. The block size of the file is set in bytes. If the file does not exist, the value is NULL.
Example
Linux
SELECT fexists, file_length, blocksize INTO file_flag, file_chack_length, size FROM UTL_FILE.FGETATTR('/home/symfo', 'regress_symfo.txt');Windows(R)
SELECT fexists, file_length, blocksize INTO file_flag, file_chack_length, size FROM UTL_FILE.FGETATTR('c:/symfo', 'regress_symfo.txt');FOPEN opens a file.
Specify the directory where the relevant file exists as the directory name.
Specify a slash (/) or a backslash (\) as a separator in the directory name.
Specify the relevant file name as the file name.
Specify the mode for opening the file at open method. Specify the following:
r: Read
w: Write
a: Add
Specify the maximum string length (in bytes) that can be processed with one operation. If omitted, the default is "1024". Specify in the range from 1 to 32767.
Up to 50 files per session can be open at the same time.
Example
Linux
f := UTL_FILE.FOPEN('/home/symfo','regress_symfo.txt','r',1024);Windows(R)
f := UTL_FILE.FOPEN('c:/symfo','regress_symfo.txt','r',1024);FRENAME changes the name of an existing file.
The directory name before change is the directory where the file was before change.
The file name before change is the file name before change.
The directory name after change is the directory where the file was created after change.
The file name after change is the file name after change.
Specify a slash (/) or a backslash (\) as a separator in the directory name.
Specify whether to overwrite a file with changes, if one exists in the directory with the logical value. If the "true" logical value is specified, files with changes are overwritten even if they exist. If the "false" logical value is specified, an error occurs if files with changes exist. If this is omitted, the logical value for "False" will be set.
See
Refer to "Boolean Type" in "The SQL Language" in the PostgreSQL Documentation for information on logical values.
Example
Linux
PERFORM UTL_FILE.FRENAME('/home/symfo', 'regress_symfo.txt', '/home/symfo',
'regress_symfo2.txt', TRUE);Windows(R)
PERFORM UTL_FILE.FRENAME('c:/symfo', 'regress_symfo.txt', 'c:/symfo',
'regress_symfo2.txt', TRUE);GET_LINE reads one line from a file.
Specify an open file handler as the file handler. Specify the file handler returned by FOPEN using the r (read) mode.
The load size is the number of bytes to be loaded from the file. If not specified, it will be the maximum string length of FOPEN.
The return value is the buffer that receives the line loaded from the file.
Newline characters are not loaded to the buffer.
An empty string is returned if a blank line is loaded.
Specify the maximum size(in bytes) of the data to be loaded at load size. Specify in the range from 1 to 32767. If a load size is not specified, then the maximum string length specified at FOPEN is set when a maximum string length is specified at FOPEN. 1024 is set if a maximum string length is not set at FOPEN.
If the line length is greater than the load size, the load size is loaded and the remaining is loaded upon the next call.
The NO_DATA_FOUND will be raised when trying to read past the last line.
Example
buff := UTL_FILE.GET_LINE(f);
IS_OPEN checks if there are open files.
Specify the file handler to be verified as the file handler.
The return value is a BOOLEAN type. A "true" logical value represents the open state and the "false" logical value represents the closed state.
See
Refer to "Boolean Type" under "The SQL Language" in the PostgreSQL Documentation for information on logical values.
Example
IF UTL_FILE.IS_OPEN(f) THEN PERFORM UTL_FILE.FCLOSE(f); END IF;
NEW_LINE writes one or more newlines.
Specify an open file handler as the file handler.
The number of newlines is the number of newlines to be written to the file. If omitted, "1" is set.
Example
PERFORM UTL_FILE.NEW_LINE(f, 2);
PUT writes a string to a file.
Specify an open file handler as the file handler. Specify the file handler that was opened with w (write) or a (append) with the FOPEN opening method.
Specify the character string to be written to the file for the character string.
The maximum size(in bytes) of the string is the maximum string length specified at FOPEN.
The return value is a TEXT type and is the buffer that receives the line loaded from the file.
Example
PERFORM UTL_FILE.PUT(f, 'ABC');
PUT_LINE adds a newline to a string and writes it.
Specify an open file handler as the file handler. Specify the file handler that was opened with w (write) or a (append) with the FOPEN opening method.
The logical value specifies whether to forcibly write to the file. If the "true" logical value is specified, file writing is forced. If the "false" logical value is specified, file writing is asynchronous. If this is omitted, the logical value for "False" will be set.
The maximum size of the string (in bytes) is the maximum string length value specified at FOPEN.
Example
PERFORM UTL_FILE.PUT_LINE(f, 'ABC', TRUE);
PUTF writes a string that uses formatting.
Specify an open file handler as the file handler. Specify the file handler that was opened with w (write) or a (append) with the FOPEN opening method.
Format is a string that includes the formatting characters \n and %s.
The \n in the format is code for a newline.
Specify the same number of input values as there are %s in the format. Up to a maximum of five input values can be specified. The %s in the format are replaced with the corresponding input characters. If an input value corresponding to %s is not specified, it is replaced with an empty string.
Example
PERFORM UTL_FILE.PUTF(f, '[1=%s, 2=%s, 3=%s, 4=%s, 5=%s]\n', '1', '2', '3', '4', '5');
The procedure when using UTL_FILE, and a usage example, are shown below.
Preparation
Before starting a new job that uses UTL_FILE, register the directory in the UTL_FILE.UTL_FILE_DIR table.
Refer to "9.5.2.1 Registering and Deleting Directories" for information on how to register the directory.
Performing a job
Perform a job that uses UTL_FILE. The example is shown below.
CREATE OR REPLACE FUNCTION gen_file(mydir TEXT, infile TEXT, outfile TEXT, copyfile TEXT) RETURNS void AS $$
DECLARE
v1 VARCHAR(32767);
inf UTL_FILE.FILE_TYPE;
otf UTL_FILE.FILE_TYPE;
BEGIN
inf := UTL_FILE.FOPEN(mydir, infile,'r',256);
otf := UTL_FILE.FOPEN(mydir, outfile,'w');
v1 := UTL_FILE.GET_LINE(inf,256);
PERFORM UTL_FILE.PUT_LINE(otf,v1,TRUE);
v1 := UTL_FILE.GET_LINE(inf,256);
PERFORM UTL_FILE.PUTF(otf,'%s\n',v1);
v1 := UTL_FILE.GET_LINE(inf, 256);
PERFORM UTL_FILE.PUT(otf,v1);
PERFORM UTL_FILE.NEW_LINE(otf);
PERFORM UTL_FILE.FFLUSH(otf);
inf := UTL_FILE.FCLOSE(inf);
otf := UTL_FILE.FCLOSE(otf);
PERFORM UTL_FILE.FCOPY(mydir, outfile, mydir, copyfile, 2, 3);
PERFORM UTL_FILE.FRENAME(mydir, outfile, mydir, 'rename.txt');
END;
$$ LANGUAGE plpgsql;
/* Linux */
SELECT gen_file('/home/symfo', 'input.txt', 'output.txt', 'copyfile.txt');
/* Windows(R) */
SELECT gen_file('c:/symfo', 'input.txt', 'output.txt', 'copyfile.txt');Post-processing
If you remove a job that uses UTL_FILE, delete the directory information from the UTL_FILE.UTL_FILE_DIR table. Ensure that the directory information is not being used by another job before deleting it.
Refer to "9.5.2.1 Registering and Deleting Directories" for information on how to delete the directory.