Graphql-engine: support computed columns in queries

[0x777]

Like Postgraphile does: https://www.graphile.org/postgraphile/computed-columns/

[maxpain]

Yes, this is a very useful feature.

[coco98]

Adding another note from @GeertJohan on email:

I have a lot of calculated fields. Example:

CREATE OR REPLACE FUNCTION public.available_quantity("$1" stock.items) RETURNS integer
LANGUAGE sql STABLE
AS $_$
SELECT
    CASE
        WHEN $1.kind = 'print'::stock.itemkind THEN $1.quantity - ceil($1.physical_quantity * 0.1)::integer
        ELSE $1.quantity
    END
$_$;

This can be used as a field "available_quantiy" in a query on the stock.items table.

SELECT
    name,
    items.available_quantity,
    some,
    other,
    fields
FROM stock.items;

Note the items. qualifier in front of available_quantity. Postgres finds a matching function in the search path (public) to execute for matching rows.

It would be great if Hasura could detect these fields, public.<identifier>(table_type) maps to a table_type.identifier field.

[chunsli]

this will be a very useful feature

[dsandip]

FYI @rakeshkky has started working on this and will be part of a mid-Aprilish release.

[chunsli]

@rakeshkky really looking forward to this feature, I have looked at the design and it seems a good fit to what we need. wondering if it is still planned to be released in mid-April (or within April at all)?

[trickymast3r]

Waiting this integration !

[thangtq-intelads]

Waiting this integration !

me too

[barbalex]

Mee too.

But in the meantime I use this rather usable workaround:

1. create a view containing the table id and all computed fields
2. create a relationship from the table to the view, name it computed

Example:

create the view:

create or replace view herkunft_computed as
select
  herkunft.id,
  case
    when is_valid_coordinates(geom_point) then ST_Y(geom_point)
    else null
  end as wgs84_lat,
  case
    when is_valid_coordinates(geom_point) then ST_X(geom_point)
    else null
  end as wgs84_long,
  case
    when is_valid_coordinates(geom_point) then round(ST_X(ST_Transform(geom_point, 2056)))
    else null
  end as lv95_x,
  case
    when is_valid_coordinates(geom_point) then round(ST_Y(ST_Transform(geom_point, 2056)))
    else null
  end as lv95_y
from herkunft;

query the table including computed fields:

{
  herkunft {
    id
    geom_point
    computed {
      lv95_x
      lv95_y
      wgs84_lat
      wgs84_long
    }
  }
}

[alak]

any news on this @rakeshkky ?

[JordyMiggelbrink]

Also waiting for this!

[bs1180]

Postgres 12 offers generated columns which would cover several of the use cases raised here.