Description

Computed fields are extra fields added to a table whose value is computed from other columns. It facilitates users to define an additional field to an existing table whose return value is computed via an SQL function.

How it is different from Postgres generated columns

Postgres, from version 12, is introducing Generated Columns.
Postgres' generated columns come with its 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.

Refer to docs here

Refer to API reference here

Affected components

• Server
• Docs
• Tests

Related Issues

close #1387

Solution and Design

This PR is a significant change which touches code base for metadata, GraphQL schema generation and resolving and SQL generation.

Metadata

• Added two new query types to manage computed fields. See module Hasura.RQL.DDL.ComputedField
  1. add_computed_field
  2. drop_computed_field
• Detect changes to functions associated with computed fields in run_sql query
• Add computed fields in create_select_permission

GraphQL

• Either is being used before to represent whether a field is a column or a relationship. Now, those types are replaced with proper sum types to represent computed fields too.
• Resolve the computed field

SQL generation

Modules:-

• Hasura.RQL.DML.Select.Internal
• Hasura.RQL.DML.Select.Types

Changes:-

• The BaseNode now contains table computed fields as sub-nodes.
• TableFrom changed to SelectFromG to include a selection from rows returned by a function execution
• While resolving a BaseNode to SQL Select keep track of parent node prefix to execute the computed field function with the parent table row identifier.

In module Hasura.SQL.DML add FunctionExp to SQLExp AST.

The following function is added to the author table as a computed field with name
computed_articles

CREATE FUNCTION fetch_computed_articles(author_arg author, search text)
RETURNS SETOF article AS $$
    SELECT *
    FROM article
    WHERE
      (title ilike ('%' || search || '%')
      OR content ilike ('%' || search || '%'))
      AND article.author_id = author_arg.id
$$ LANGUAGE sql STABLE;

Querying data from author table along with computed field computed_articles

query {
  author{
    id
    name
    computed_articles(args:{search:"search_text"}){
      id
      title
    }
  }
}

Generates the SQL below.

SELECT
  coalesce(json_agg("root"), '[]') AS "root"
FROM
  (
    SELECT
      row_to_json(
        (
          SELECT
            "_5_e"
          FROM
            (
              SELECT
                "_0_root.base"."id" AS "id",
                "_0_root.base"."name" AS "name",
                "_4_root.cf.root.computed_articles"."computed_articles" AS "computed_articles"
            ) AS "_5_e"
        )
      ) AS "root"
    FROM
      (
        SELECT
          *
        FROM
          "public"."author"
        WHERE
          ('true')
      ) AS "_0_root.base"
      LEFT OUTER JOIN LATERAL (
        SELECT
          coalesce(json_agg("computed_articles"), '[]') AS "computed_articles"
        FROM
          (
            SELECT
              row_to_json(
                (
                  SELECT
                    "_2_e"
                  FROM
                    (
                      SELECT
                        "_1_root.cf.root.computed_articles.base"."id" AS "id",
                        "_1_root.cf.root.computed_articles.base"."title" AS "title"
                    ) AS "_2_e"
                )
              ) AS "computed_articles"
            FROM
              (
                SELECT
                  *
                FROM
                  "public"."fetch_computed_articles"(author_arg => "_0_root.base", search => 'search_text')
                WHERE
                  (("_0_root.base"."id") = ("author_id"))
              ) AS "_1_root.cf.root.computed_articles.base"
          ) AS "_3_root.cf.root.computed_articles"
      ) AS "_4_root.cf.root.computed_articles" ON ('true')
  ) AS "_6_root"

Steps to test and verify

Follow any test case or example

Limitations, known bugs & workarounds

• Aggregations are not available on computed fields which returns setof table
• Scalar computed fields are not allowed in where clause (bool exps)
• The SQL selection for computed fields need to be optimised (merge sub-queries for same computed fields)