Get instant support on our discord server
Computed fields¶
Table of contents
What are computed fields?¶
Computed fields are virtual values or objects that are dynamically computed and can be queried along with a table’s columns. Computed fields are computed when requested for via custom SQL functions using other columns of the table and other custom inputs if needed.
Note
Computed fields are only exposed over the GraphQL API and the database schema is not modified on addition of a computed field.
Supported SQL functions¶
Only functions which satisfy the following constraints can be added as a computed field to a table. (terminology from Postgres docs):
- Function behaviour: ONLY
STABLEorIMMUTABLE - Argument modes: ONLY
IN - Table Argument: One input argument with a table row type
- Return type: Either
SETOF <table-name>orBASEtype
Note
Functions used as computed fields can also accept other arguments other than the mandatory table row argument. Values for these extra arguments can be passed as arguments to the computed field in the GraphQL API.
Computed field types¶
Based on the SQL function’s return type, we can define two types of computed fields:
1. Scalar computed fields¶
Computed fields whose associated SQL function returns a base type like Integer, Boolean, Geography etc. are scalar computed fields.
Example:
The author table has two text columns: first_name and last_name.
Define an SQL function called author_full_name:
CREATE FUNCTION author_full_name(author_row author)
RETURNS TEXT AS $$
SELECT author_row.first_name || ' ' || author_row.last_name
$$ LANGUAGE sql STABLE;
Add a computed field called full_name to the author table using the SQL function above.
Query data from the author table:
query {
author {
id
first_name
last_name
full_name
}
}
2. Table computed fields¶
Computed fields whose associated SQL function returns SETOF <table-name> are table computed fields.
The return table must be tracked to define such a computed field.
Example:
In a simple author <-> article schema, we can define a relationship on the author
table to fetch authors along with their articles.
We can make use of computed fields to fetch the author’s articles with a search parameter.
Define an SQL function called filter_author_articles:
CREATE FUNCTION filter_author_articles(author_row author, search text)
RETURNS SETOF article AS $$
SELECT *
FROM article
WHERE
( title ilike ('%' || search || '%')
OR content ilike ('%' || search || '%')
) AND author_id = author_row.id
$$ LANGUAGE sql STABLE;
Add a computed field called filtered_articles to the author table using the SQL function above.
Query data from the author table:
query {
author {
id
first_name
last_name
filtered_articles(args: {search: "Hasura"}){
id
title
content
}
}
}
Adding a computed field to a table¶
Head to the Modify tab of the table and click on the Add button in the Computed fields
section:
Supported from
Console support is available in v1.1.0 and above
Computed fields permissions¶
Access control to computed fields depends on the type of computed field.
- For scalar computed fields, permissions are managed similar to the columns permissions of the table.
- For table computed fields, the permissions set on the return table are respected.
Computed fields vs. Postgres generated columns¶
Postgres, from version 12, is introducing Generated Columns.
The value of generated columns is also computed from other columns of a table. Postgres’ generated columns
come with their own limitations. Hasura’s computed fields are defined via an SQL function, which allows users
to define any complex business logic in a function. Generated columns will go together with computed fields where
Hasura treats generated columns as normal Postgres columns.
