76 lines
2.4 KiB
ReStructuredText
76 lines
2.4 KiB
ReStructuredText
.. _computed_cols:
|
|
|
|
Computed Fields
|
|
###############
|
|
|
|
Computed fields are virtual columns that are not stored in a table. PostgreSQL makes it possible to implement them using functions on table types.
|
|
|
|
.. code-block:: postgres
|
|
|
|
CREATE TABLE people (
|
|
first_name text
|
|
, last_name text
|
|
, job text
|
|
);
|
|
|
|
-- a computed field that combines data from two columns
|
|
CREATE FUNCTION full_name(people)
|
|
RETURNS text AS $$
|
|
SELECT $1.first_name || ' ' || $1.last_name;
|
|
$$ LANGUAGE SQL;
|
|
|
|
Horizontal Filtering on Computed Fields
|
|
=======================================
|
|
|
|
:ref:`h_filter` can be applied to computed fields. For example, we can do a :ref:`fts` on :code:`full_name`:
|
|
|
|
.. code-block:: postgres
|
|
|
|
-- (optional) you can add an index on the computed field to speed up the query
|
|
CREATE INDEX people_full_name_idx ON people
|
|
USING GIN (to_tsvector('english', full_name(people)));
|
|
|
|
.. code-block:: bash
|
|
|
|
curl "http://localhost:3000/people?full_name=fts.Beckett"
|
|
|
|
.. code-block:: json
|
|
|
|
[
|
|
{"first_name": "Samuel", "last_name": "Beckett", "job": "novelist"}
|
|
]
|
|
|
|
Vertical Filtering on Computed Fields
|
|
=====================================
|
|
|
|
Computed fields won't appear on the response by default but you can use :ref:`v_filter` to include them:
|
|
|
|
.. code-block:: bash
|
|
|
|
curl "http://localhost:3000/people?select=full_name,job"
|
|
|
|
.. code-block:: json
|
|
|
|
[
|
|
{"full_name": "Samuel Beckett", "job": "novelist"}
|
|
]
|
|
|
|
Ordering on Computed Fields
|
|
===========================
|
|
|
|
:ref:`ordering` on computed fields is also possible:
|
|
|
|
.. code-block:: bash
|
|
|
|
curl "http://localhost:3000/people?order=full_name.desc"
|
|
|
|
.. important::
|
|
|
|
Computed fields must be created in the :ref:`exposed schema <db-schemas>` or in a schema in the :ref:`extra search path <db-extra-search-path>` to be used in this way. When placing the computed field in the :ref:`exposed schema <db-schemas>` you can use an **unnamed** parameter, as in the example above, to prevent it from being exposed as an :ref:`RPC <functions>` under ``/rpc``.
|
|
|
|
.. note::
|
|
|
|
- PostgreSQL 12 introduced `generated columns <https://www.postgresql.org/docs/12/ddl-generated-columns.html>`_, which can also compute a value based on other columns. However they're stored, not virtual.
|
|
- "computed fields" are documented on https://www.postgresql.org/docs/current/rowtypes.html#ROWTYPES-USAGE (search for "computed fields")
|
|
- On previous PostgREST versions this feature was documented with the name of "computed columns".
|