result types and computed field

[avallete]

What I was referring to does work (see: https://docs.postgrest.org/en/latest/references/api/computed_fields.html#computed-fields), my question is about fixing the generated type as the typescript error message is preventing builds of my next.js app to succeed. I've been forced to add ts-ignore everywhere since 2 years and I was thinking that this PR sounds like it would fix this issue

Originally posted by @whollacsek in #558 (comment)

[avallete]

Just tried out with both a computed field and a generated column defined like so:

CREATE TYPE public.user_status AS ENUM ('ONLINE', 'OFFLINE');
CREATE TABLE public.users (
  username text primary key,
  data jsonb DEFAULT null,
  age_range int4range DEFAULT null,
  status user_status DEFAULT 'ONLINE'::public.user_status,
  catchphrase tsvector DEFAULT null,
  -- Use a generated column as defined in postgres12
  generated_col text GENERATED ALWAYS AS (username || ' ' || status) STORED
);
ALTER TABLE public.users REPLICA IDENTITY FULL; -- Send "previous data" to supabase
COMMENT ON COLUMN public.users.data IS 'For unstructured data and prototyping.';

-- Create a computed field
CREATE FUNCTION username_status(public.users)
RETURN text as $$
  SELECT $1.username || ' ' || $1.status;
$$ LANGUAGE SQL;

The resulting "Row" type after types gens seems correct:

...

      users: {
        Row: {
          age_range: unknown | null
          catchphrase: unknown | null
          data: Json | null
          generated_col: string | null
          status: Database["public"]["Enums"]["user_status"] | null
          username: string
          username_status: string | null
        }
...

@whollacsek Did you regenerated the types recently ? This should already work with the current query parser.

[whollacsek]

This is the generated type I got:

export type Database = {
  event_ticketing: {
    Functions: {
      sold_quantity: {
        Args: {
          inventory_item: unknown
        }
        Returns: number
      }
      ...

I think the current parser or type generator doesn't understand schemas other than public as you see the sold_quantity function's argument is unknown, maybe this is the reason sold_quantity wasn't included in the column list of the table

[avallete]

This is the generated type I got:

export type Database = {
  event_ticketing: {
    Functions: {
      sold_quantity: {
        Args: {
          inventory_item: unknown
        }
        Returns: number
      }
      ...

I think the current parser or type generator doesn't understand schemas other than public as you see the sold_quantity function's argument is unknown, maybe this is the reason sold_quantity wasn't included in the column list of the table

PostgREST can indeed cause issues with cross-schema relationships.

Additionally, computed fields need to be declared in the exposed schema to be used as expected:

"Computed fields must be created in the exposed schema or in a schema in the extra search path to be used in this way."

It could also be related to how the function is defined: supabase/postgres-meta#775.

A potential workaround would be to manually override the types and declare the column in the appropriate "Row", as shown in the second example here:

https://supabase.com/docs/guides/api/rest/generating-types#helper-types-for-tables-and-joins

Anyway if introspection gets confused, the parser will inevitably misinterpret it. The discussion belongs to postgres-meta which is in charge of the introspection.

[whollacsek]

I see thanks for explaining, in my case the function and the table are both in the same schema and this schema is exposed.

For now I'll implement the workaround you linked, hopefully this could be solved in the future.

[avallete]

I see thanks for explaining, in my case the function and the table are both in the same schema and this schema is exposed.

For now I'll implement the workaround you linked, hopefully this could be solved in the future.

Indeed, for what I understood your function arguments might come from another schema is that it ?

What could be really helpful is if you could provide a minimal SQL example of how we can reproduce that with a simple CREATE TABLE and CREATE FUNCTION statement. Because so far, I wasn't able to reproduce.

Something like:

CREATE TABLE event_ticketing ( ...<minimal set of columns>... )
CREATE FUNCTION sold_quantity(...)

With the exact function definition that could just return a simple dummy value. The actual content of the function shouldn't really impact the introspection. But I'm thinking maybe something special about the prototype definition ?

If we can get the full pipeline from SQL to the failing types, we should be able to reproduce and debug this much better.

[avallete]

Closing seems like duplicate of #474