The pgpro_bfile extension introduces a composite type bfile
that implements an Oracle-like technique to access an
external file.
The bfile type contains two fields:
dir_id — the directory identifier in the
bfile_directories table
file_name — the name of the external file
Information on directories is stored in the bfile_directories table,
which has the following columns:
dir_id — a unique directory identifier of the int32 type.
dir_alias — a unique directory alias.
dir_path — path to the directory.
Although having two unique identifiers dir_id and
dir_alias is excessive, dir_id is
best for storing in bfile because it requires less storage
and ensures faster search, while working with dir_alias
is more convenient for a user migrating from Oracle, especially to
better understand command semantics.
Information on directory access rights is stored in the bfile_directory_roles
table, which has the following columns:
dr_dir_id — a directory identifier of the int32 type,
that is, an external reference to the dir_id column in the
bfile_directories table.
dr_role_id — ID of a user/role that has
access rights on the directory.
dr_rights — a bit array of access rights on the
directory (1 means read access, 2 means write access). Choice of a bit array assumes
that a need to grant new access rights can arise, such as a need in a separate delete right.
Functions to work with directories and to manage directory access rights are implemented in PL/PgSQL,
while functions to work with bfile values are implemented in C for better performance.
Table F.30. Functions for Working with Directories
Function Description |
|---|
Creates a directory for the given alias and path and returns the ID of the created directory. Superuser privileges are required to call this function. |
Deletes the directory with the given alias. Superuser privileges are required to call this function. |
Changes the alias of the directory. Superuser privileges are required to call this function. |
Changes the path to the directory with the given alias. Superuser privileges are required to call this function. |
Returns the path to the directory with the given alias. |
Returns the path to the directory with the given ID. |
Returns the alias of the directory with the given ID. |
Returns the ID of the directory with the given alias. |
Table F.31. Functions for Granting and Revoking Directory Access Rights
Function Description |
|---|
Grants access rights on the |
Revokes access rights on the |
Removes information on directory access rights of deleted users/roles from the |
Table F.32. Functions for Working with bfile Values
Function Description |
|---|
Returns the |
Returns the |
Opens the file for the given |
Closes the file for the given descriptor returned by the
|
Closes all the files that were opened earlier by the |
Returns the size of the open file with the given descriptor. |
From the open file with the descriptor |
Writes the given buffer |
Returns |
Returns the file size for the given |
For the given |
For the given |
Deletes the file for the given |
When |
Calculates an MD5 hash for the specified |
The following example illustrates usage of the bfile type.
We will run a script on the server file system. But first, let's create a directory
to store bfile data:
mkdir "/tmp/bfiles"
Now we will use psql to run the script below:
-- Create the pgpro_bfile extension:
CREATE EXTENSION pgpro_bfile;
-- Create a directory in the database to store bfile:
SELECT bfile_directory_create('BFILE_DATA', '/tmp/bfiles');
-- Create the file bfile.data on the file system and add there the value of '0123456789':
SELECT bfile_write_direct(bfile_make('BFILE_DATA', 'bfile.data'), '0123456789');
-- Create the table bfile and add there one record referencing that file:
CREATE TABLE bfile_table(id int, bf bfile);
INSERT INTO bfile_table VALUES (1, bfile_make('BFILE_DATA', 'bfile.data'));
-- Create a user to run the script:
CREATE USER bf_test_user;
-- Grant bf_test_user with read-write access to BFILE_DATA:
SELECT bfile_grant_directory('BFILE_DATA', 'bf_test_user', 3);
-- Grant bf_test_user with rights on bfile_table:
GRANT ALL ON bfile_table TO bf_test_user;
-- Register as bf_test_user:
SET SESSION AUTHORIZATION bf_test_user;
DO $$
DECLARE
v_bfile bfile;
v_buffer bytea;
v_length bigint;
v_handler int;
BEGIN
-- Open the file for reading and writing:
SELECT bfile_open(bf, 3) INTO v_handler FROM bfile_table WHERE id = 1;
-- This character string will be written to end of file:
PERFORM bfile_write(v_handler, '_suffix');
-- This character string will be written from the position 0 and replace '0123456':
PERFORM bfile_write(v_handler, 'prefix_', 0);
-- Read the character string from file to a buffer and print out its length and contents:
v_buffer = bfile_read(v_handler);
RAISE NOTICE 'Buffer length: %', length(v_buffer);
RAISE NOTICE 'Buffer content: %', encode(v_buffer, 'escape');
-- Get and print out the file size:
v_length = bfile_length(v_handler);
RAISE NOTICE 'BFILE length: %', v_length;
-- Close bfile:
PERFORM bfile_close(v_handler);
END $$;
-- Check the contents of the table:
SELECT encode(b, 'escape'), length(b) from (SELECT bfile_read_direct(bf) b FROM bfile_table) x;
-- Delete the table, user and pgpro_bfile extension:
RESET SESSION AUTHORIZATION;
DROP TABLE bfile_table;
DROP USER bf_test_user;
DROP EXTENSION pgpro_bfile;
The script produces the following output:
CREATE EXTENSION
bfile_directory_create
------------------------
1
(1 row)
bfile_write_direct
--------------------
(1 row)
CREATE TABLE
INSERT 0 1
CREATE ROLE
bfile_grant_directory
-----------------------
(1 row)
GRANT
SET
NOTICE: Buffer length: 17
NOTICE: Buffer content: prefix_789_suffix
NOTICE: BFILE length: 17
DO
encode | length
-------------------+--------
prefix_789_suffix | 17
(1 row)
RESET
DROP TABLE
DROP ROLE
DROP EXTENSION