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.

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

Horizontal Filtering can be applied to computed fields. For example, we can do a Full-Text Search on full_name:

-- (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)));

curl "http://localhost:3000/people?full_name=fts.Beckett"

[
  {"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 Vertical Filtering to include them:

curl "http://localhost:3000/people?select=full_name,job"

[
  {"full_name": "Samuel Beckett", "job": "novelist"}
]

Ordering on Computed Fields

Ordering on computed fields is also possible:

curl "http://localhost:3000/people?order=full_name.desc"

Important

Computed fields must be created in the exposed schema or in a schema in the extra search path to be used in this way. When placing the computed field in the exposed schema you can use an unnamed parameter, as in the example above, to prevent it from being exposed as an RPC under /rpc.

Note