Pipelines PGFS reference
========================

This reference documentation for EDB Postgres AI - AI Accelerator
Pipelines PGFS includes information on the functions and views available
in the :ref:`Pipelines PGFS reference <Pipelines PGFS reference>`  extension. These functions give AIDB access to
S3-compatible file systems, Google Cloud Storage buckets, and local file
systems.

pgfs
----

Functions
^^^^^^^^^

- :ref:`pgfs.create_foreign_table <pgfs.create_foreign_table>` 

- :ref:`pgfs.create_storage_location <pgfs.create_storage_location>` 

- :ref:`pgfs.create_storage_location_with_foreign_table <pgfs.create_storage_location_with_foreign_table>` 

- :ref:`pgfs.delete_storage_location <pgfs.delete_storage_location>` 

- :ref:`pgfs.list_storage_locations <pgfs.list_storage_locations>` 

- :ref:`pgfs.get_storage_location <pgfs.get_storage_location>` 

- :ref:`pgfs.update_storage_location <pgfs.update_storage_location>` 

.. _functions-1:

Functions
---------

pgfs.create_foreign_table
^^^^^^^^^^^^^^^^^^^^^^^^^

Creates a foreign table in the database.

Parameters
^^^^^^^^^^

.. csv-table::
  :header: Parameter,Type,Default,Description
  :widths: 15,12,12,20
  :align: left
  :class: longtable

  `table_name`,text,"",""
  `server_name`,text,"",""

Example
^^^^^^^

.. code:: sql

   SELECT pgfs.create_foreign_table(my_table, my_server);

pgfs.create_storage_location
^^^^^^^^^^^^^^^^^^^^^^^^^^^^

Creates a storage location in the database.

.. _parameters-1:

Parameters
^^^^^^^^^^

.. csv-table::
  :header: Parameter,Type,Default,Description
  :widths: 15,12,12,20
  :align: left
  :class: longtable

  `name`,text,"",Name for storage location
  `url`,text,"","URL for this storage location (prefix `s3:`, `gs:`, or `file:`)"
  `options`,json,"",Options for the storage location  (optional)
  `credentials`,json,"",Credentials for the storage location (optional)

**OR**

.. csv-table::
  :header: Parameter,Type,Default,Description
  :widths: 15,12,12,20
  :align: left
  :class: longtable

  `name`,text,"",Name for storage location
  `url`,text,"","URL for this storage location (prefix `s3:`, `gs:`, or `file:`)"
  `msl_id`,uuid,"",Unused
  `options`,json,"",Options for the storage location  (optional)
  `credentials`,json,"",Credentials for the storage location (optional)

.. _example-1:

Example
^^^^^^^

.. code:: sql

   SELECT pgfs.create_storage_location(my_storage, 
                                       s3://my_bucket, 123e4567-e89b-12d3-a456-426614174000, 
                                       {}, 
                                       {});

pgfs.create_storage_location_with_foreign_table
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

Creates a storage location in the database and associates it with a
foreign table.

.. _parameters-2:

Parameters
^^^^^^^^^^

.. csv-table::
  :header: Parameter,Type,Default,Description
  :widths: 15,12,12,20
  :align: left
  :class: longtable

  `storage_location_name`,text,"",Name for storage location
  `url`,text,"","URL for this storage location (prefix `s3:`, `gs:` or `file:`)"
  `options`,json,"",Options for the storage location  (optional)
  `credentials`,json,"",Credentials for the storage location (optional)

**OR**

.. csv-table::
  :header: Parameter,Type,Default,Description
  :widths: 15,12,12,20
  :align: left
  :class: longtable

  `storage_location_name`,text,"",Name for storage location
  `url`,text,"","URL for this storage location (prefix `s3:`, `gs:` or `file:`)"
  `msl_id`,uuid,"",Unused
  `options`,json,"",Options for the storage location  (optional)
  `credentials`,json,"",Credentials for the storage location (optional)

.. _example-2:

Example
^^^^^^^

.. code:: sql

   SELECT pgfs.create_storage_location_with_foreign_table(my_storage, 
                                                          s3://my_bucket, 123e4567-e89b-12d3-a456-426614174000, 
                                                          {}, 
                                                          {});

pgfs.delete_storage_location
^^^^^^^^^^^^^^^^^^^^^^^^^^^^

Deletes a storage location from the database.

.. _parameters-3:

Parameters
^^^^^^^^^^

.. csv-table::
  :header: Parameter,Type,Default,Description
  :widths: 15,12,12,20
  :align: left
  :class: longtable

  `storage_location_name`,text,"",Name of storage location

.. _example-3:

Example
^^^^^^^

.. code:: sql

   SELECT pgfs.delete_storage_location(my_storage);

pgfs.get_default_storage_location
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

Returns the default storage location.

Returns
^^^^^^^

.. csv-table::
  :header: Column,Type,Description
  :widths: 12,15,25
  :align: left
  :class: longtable

  `default_storage_location`,text,""

.. _example-4:

Example
^^^^^^^

.. code:: sql

   SELECT * FROM pgfs.get_default_storage_location();

pgfs.get_storage_location
^^^^^^^^^^^^^^^^^^^^^^^^^

Returns information about a storage location.

.. _parameters-4:

Parameters
^^^^^^^^^^

.. csv-table::
  :header: Parameter,Type,Default,Description
  :widths: 15,12,12,20
  :align: left
  :class: longtable

  `storage_location_name`,text,"",Name of storage location

.. _returns-1:

Returns
^^^^^^^

.. csv-table::
  :header: Column,Type,Description
  :widths: 12,15,25
  :align: left
  :class: longtable

  `name`,text,""
  `url`,text,""
  `msl_id`,uuid,""
  `options`,json,""
  `credentials`,json,""

.. _example-5:

Example
^^^^^^^

.. code:: sql

   SELECT * FROM pgfs.get_storage_location(my_storage);

pgfs.list_storage_locations
^^^^^^^^^^^^^^^^^^^^^^^^^^^

Lists all storage locations in the database.

.. _returns-2:

Returns
^^^^^^^

.. csv-table::
  :header: Column,Type,Description
  :widths: 12,15,25
  :align: left
  :class: longtable

  `name`,text,""
  `url`,text,""
  `msl_id`,uuid,""
  `options`,json,""
  `credentials`,json,""

.. _example-6:

Example
^^^^^^^

.. code:: sql

   SELECT * FROM pgfs.list_storage_locations();

pgfs.set_default_storage_location
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

Sets the default storage location.

.. _parameters-5:

Parameters
^^^^^^^^^^

.. csv-table::
  :header: Parameter,Type,Default,Description
  :widths: 15,12,12,20
  :align: left
  :class: longtable

  `storage_location_name`,text,"",""

.. _example-7:

Example
^^^^^^^

.. code:: sql

   SELECT pgfs.set_default_storage_location(my_storage);

pgfs.update_storage_location
^^^^^^^^^^^^^^^^^^^^^^^^^^^^

Updates a storage location in the database.

.. _parameters-6:

Parameters
^^^^^^^^^^

.. csv-table::
  :header: Parameter,Type,Default,Description
  :widths: 15,12,12,20
  :align: left
  :class: longtable

  `name`,text,"",""
  `url`,text,"",""
  `msl_id`,uuid,"",""
  `options`,json,"",""
  `credentials`,json,"",""

.. _example-8:

Example
^^^^^^^

.. code:: sql

   SELECT pgfs.update_storage_location(my_storage, s3://my_bucket, 123e4567-e89b-12d3-a456-426614174000, {}, {});

Views
-----

There are no user-facing views in the ``pgfs`` schema.

Tables
------

There are no user-facing tables in the ``pgfs`` schema.