Request a Demo
F.47. pgpro_bfile — a composite type to access an external file
Prev UpAppendix F. Additional Supplied Modules and Extensions Shipped in postgrespro-ent-16-contribHome Next

F.47. pgpro_bfile — a composite type to access an external file #

F.47.1. Data Structures
F.47.2. Functions
F.47.3. Example

The pgpro_bfile extension introduces a composite type bfile that implements an Oracle-like technique to access an external file.

F.47.1. Data Structures #

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.

F.47.2. Functions #

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.31. Functions for Working with Directories

Function

Description

bfile_directory_create (a_dir_alias text, a_dir_path text) returns (int)

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.

bfile_directory_delete (a_dir_alias text) returns (void)

Deletes the directory with the given alias. Superuser privileges are required to call this function.

bfile_directory_rename (a_dir_alias text, a_new_dir_alias text) returns (void)

Changes the alias of the directory. Superuser privileges are required to call this function.

bfile_directory_set_path (a_dir_alias text, a_dir_path text) returns (void)

Changes the path to the directory with the given alias. Superuser privileges are required to call this function.

bfile_directory_get_path_by_alias (a_dir_alias text) returns (text)

Returns the path to the directory with the given alias.

bfile_directory_get_path_by_id (a_dir_id int) returns (text)

Returns the path to the directory with the given ID.

bfile_directory_get_alias_by_id (a_dir_id int) returns (text)

Returns the alias of the directory with the given ID.

bfile_directory_get_id_by_alias (a_dir_alias text) returns (int)

Returns the ID of the directory with the given alias.


Table F.32. Functions for Granting and Revoking Directory Access Rights

Function

Description

bfile_grant_directory (a_dir_alias text, a_role_name text, a_access_mask int) returns (void)

Grants access rights on the a_dir_alias directory to the a_role_name user/role. a_access_mask specifies the access to grant (1 — read, 2 — write, 3 — read and write). Superuser privileges are required to call this function.

bfile_revoke_directory (a_dir_alias text, a_role_name text, a_access_mask int) returns (void)

Revokes access rights on the a_dir_alias directory form the a_role_name user/role. a_access_mask specifies the access to revoke (1 — read, 2 — write, 3 — read and write). Superuser privileges are required to call this function.

bfile_cleanup_directory_roles () returns (void)

Removes information on directory access rights of deleted users/roles from the bfile_directory_roles table. Superuser privileges are required to call this function.


Table F.33. Functions for Working with bfile Values

Function

Description

bfile_make (a_dir_alias text, a_file_name text) returns (bfile)

Returns the bfile structure for the given directory alias and filename.

bfile_make_dir_id (a_dir_id int, a_file_name text) returns (bfile)

Returns the bfile structure for the given directory ID and filename.

bfile_open (a_bfile bfile[, a_access_mask int]) returns (int)

Opens the file for the given a_bfile and returns its descriptor. a_access_mask specifies the file access mode (1 — read, 2 — write, 3 — read and write), the default is 1.

bfile_close (a_handler int) returns (void)

Closes the file for the given descriptor returned by the bfile_open function.

bfile_close_all () returns (int)

Closes all the files that were opened earlier by the bfile_open function. Returns the number of closed files.

bfile_length (a_handler int) returns (bigint)

Returns the size of the open file with the given descriptor.

bfile_read (a_handler int[, a_offset bigint, a_length bigint]) returns (bytea)

From the open file with the descriptor a_handler, reads a_length bytes at the a_offset offset from the beginning of file and returns the bytes read. By default, a_length is -1, which specifies reading to the end of file, and a_offset is 0, which specifies reading from the beginning of file.

bfile_write (a_handler int, a_buffer bytea[, a_offset bigint]) returns (void)

Writes the given buffer a_buffer to the open file having the given descriptor a_handler at the given a_offset offset from the beginning of file. By default, a_offset is -1, which means writing from the end of file.

bfile_fileexists (a_bfile bfile) returns (boolean)

Returns true if the file corresponding to a_bfile can be used. The function checks that the file opens without errors.

bfile_length_direct (a_bfile bfile) returns (bigint)

Returns the file size for the given a_bfile.

bfile_read_direct (a_bfile bfile[, a_offset bigint, a_length bigint]) returns (bytea)

For the given a_bfile, reads a_length bytes at the a_offset offset from the beginning of file and returns the bytes read. By default, a_length is -1, which means reading to the end of file, and a_offset is 0, which means reading from the beginning of file.

bfile_write_direct (a_bfile bfile, a_buffer bytea) returns (void)

For the given a_bfile, writes the given buffer a_buffer to a new file. Write access to the a_bfile directory is required.

bfile_delete (a_bfile bfile) returns (void)

Deletes the file for the given a_bfile. Write access to the a_bfile directory is required.

bfile_compare (a_bfile_1 bfile, a_bfile_2 bfile[, a_amount bigint, a_offset_1 bigint, a_offset_2 bigint]) returns (int)

When a_amount, a_offset_1 and a_offset_2 are not specified, if the sizes of a_bfile_1 and a_bfile_2 files are different, returns -1 if the size of a_bfile_1 is less than the size of a_bfile_2 and 1 otherwise. If the files have equal sizes, the function performs byte-wise comparison of the file data and returns 0 if the comparison shows equal data, -1 if the data in a_bfile_1 is less than in a_bfile_2 and 1 if the data in a_bfile_1 is greater than in a_bfile_2. a_amount specifies the number of bytes in the given bfile values to compare, while a_offset_1 and a_offset_2 specify the offsets to start the comparison at.

bfile_md5 (a_bfile bfile) returns (text)

Calculates an MD5 hash for the specified bfile object and returns the result as a 16-byte value in text form.


F.47.3. Example #

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

Prev Up Next
F.46. pgpro_application_info — port applications using DBMS_APPLICATION_INFO package Home F.48. pg_proaudit — enables detailed logging of various security events
epub pdf