Description

Affected components

• Server
• Console
• Tests

Related Issues

close #1378

Design

Allow support for computed columns.
-> Add SQL functions through /v1/query API of type track_function.
-> Server will automatically determine the table to which the SQL function to be added as a computed column based on the first input parameter.
-> create_select_permission query type of /v1/query is updated to have an optional field computed_columns to permit SQL functions to be queried. An Example given in the next section.

Internal changes:-

• Updated hdb_function_agg view to have qualified Postgres types for input arguments of a SQL function
• Added hdb_computed_column to keep track of SQL function and table to which computed column is defined. Defined an array relationship from hdb_table with name computed_columns. This helps console to fetch list of computed columns to all tracked tables.
• Catalog version bumped to 13 with SQL and Metdata migration

Steps to test and verify

1. Create the following Postgres schema:

      CREATE TABLE person (
        id serial primary key,
        first_name text not null,
        last_name text not null
      );

      INSERT INTO person
      (first_name, last_name) VALUES
      ('taylor', 'chris'),
      ('clarke', 'michael');

      CREATE FUNCTION full_name(person person) RETURNS text AS $$
       SELECT  person.last_name || ' ' || person.first_name
      $$ LANGUAGE sql STABLE;

      CREATE FUNCTION full_name_prefix(person, prefix text) RETURNS text AS $$
       SELECT  prefix || ' ' || $1.last_name || ' ' || $1.first_name
      $$ LANGUAGE sql STABLE;

2. Track table and SQL functions
3. Make a graphql query as following

    query {
      person{
        id
        first_name
        last_name
        full_name
        full_name_prefix(args: {prefix: "Mr."})
      }
    }

reponse:-

{
  "data": {
    "person": [
      {
        "id": 1,
        "first_name": "taylor",
        "last_name": "chris",
        "full_name": "chris taylor",
        "full_name_prefix": "Mr. chris taylor"
      },
      {
        "id": 2,
        "first_name": "clarke",
        "last_name": "michael",
        "full_name": "michael clarke",
        "full_name_prefix": "Mr. michael clarke"
      }
    ]
  }
}

4. Define select permission on computed columns

POST /v1/query
{
  "type": "create_select_permission",
  "args": {
    "table": "person",
    "role": "user",
    "permission": {
      "columns": "*",
      "computed_columns": [
        "full_name_prefix"
      ],
      "filter": {
        "id": "X-Hasura-Person-Id"
      }
    }
  }
}

Limitations, known bugs & workarounds

• SQL function which returns BASIC type are allowed to be tracked as a computed column.
• If any select permission defined using computed columns, then untracking SQL functions associated is not possible straight away. Server raises the dependency error. Drop select permission before untracking those SQL functions. To fix this, we've to add cascade field to untrack_function query type which will be a breaking change.