Hasura "Running Total" Computed Column

Question

I'm looking to create a computed column in Hasura which returns a set of another table and includes a running total column of the set. For example:

table_a

id           product_id
-----------  ----------  
1            "1"      
2            "2"      
2            "3"      


table_b

id           product_id  qty   created_at
-----------  ----------  ---   ----------
1            "1"         6     01/01/20
2            "2"         4     01/02/20
3            "3"         2     01/02/20
4            "3"         2     01/02/20 
5            "1"         4     01/03/20
6            "2"         6     01/03/20

Desired GQL Response:

{
  "data": {
    "table_a": [
      {
        "id": 1,
        "product_id": "1",
        "computed_table_b_rows": [
           {
              id: 1,
              product_id: "1",
              qty: 6,
              created_at: 01/01/20,
              running_total: 6,
           },
           {
              id: 5,
              product_id: "1",
              qty: 4,
              created_at: 01/03/20,
              running_total: 10,
           },
        ]
      }
    ]
  }
}

Here's what I have so far which does not work:

CREATE FUNCTION filter_table_b(table_a_row table_a)
RETURNS SETOF table_b AS $$
  SELECT *,
  SUM (qty) OVER (PARTITION BY product_id ORDER BY created_at) as running_total
  FROM table_b
  WHERE product_id = table_a_row.product_id
$$ LANGUAGE sql STABLE;

avimoondra · Accepted Answer · 2020-03-20T14:45:21.563

It seems like SETOF is supposed to be a subset of the table, and cannot have new columns. E.g. I tried:

CREATE FUNCTION getfoo(int) RETURNS SETOF table_a AS $$
    SELECT *, 'asdf' as extra FROM table_a WHERE id = $1;
$$ LANGUAGE SQL;

resulting in the Hasura-reported error:

SQL Execution Failed
postgres-error: return type mismatch in function declared to return table_a

{
    "path": "$.args[0].args",
    "error": "query execution failed",
    "internal": {
        "arguments": [],
        "error": {
            "status_code": "42P13",
            "exec_status": "FatalError",
            "message": "return type mismatch in function declared to return table_a",
            "description": "Final statement returns too many columns.",
            "hint": ""
        },
        "prepared": false,
        "statement": "CREATE FUNCTION getfoo(int) RETURNS SETOF table_a AS $$\n    SELECT *, 'asdf' as extra FROM table_a WHERE id = $1;\n$$ LANGUAGE SQL;"
    },
    "code": "postgres-error"
}

There are a few other options, however:

Add the "running total" column to table_b itself (by changing the function definition. Detailed below.
If this is not performant enough, another option is to create a view over table_b that has the running_total column using sum and partition. And then create a relationship from table_a to table_b_view.
Create a trigger on table_b, that when a new row is added it computes the running_total column and stores it, as that data seems to be static as it's based on historical data.

Option 1 is detailed below as it most closely resembles original implementation (use of a function):

CREATE OR REPLACE FUNCTION public.table_b_running_total(table_b_row table_b)
 RETURNS bigint
 LANGUAGE sql
 STABLE
AS $function$
  SELECT SUM (qty)
  FROM table_b
  WHERE product_id = table_b_row.product_id
  AND created_at <= table_b_row.created_at
  LIMIT 1
$function$

With that, I was able to get the desired result -- tho the desired graphql response does not exactly match.

query:

query MyQuery {
  table_a(where:{ id: { _eq: 1 }}) {
    id
    product_id
    table_bs {
      id
      product_id
      qty
      created_at
      table_b_running_total
    }
  }
}

response:

{
  "data": {
    "table_a": [
      {
        "id": 1,
        "product_id": "1",
        "table_bs": [
          {
            "id": 1,
            "product_id": "1",
            "qty": 6,
            "created_at": "2020-01-01T00:00:00+00:00",
            "table_b_running_total": 6
          },
          {
            "id": 5,
            "product_id": "1",
            "qty": 4,
            "created_at": "2020-01-03T00:00:00+00:00",
            "table_b_running_total": 10
          }
        ]
      }
    ]
  }
}

Hasura "Running Total" Computed Column

1 Answers1