Unable to create computed field with text[]

[deathemperor]

Version Information

Server Version: v2.41.0

Environment

OSS

What is the current behaviour?

Error when creating computed field

{
  "error": "Inconsistent object: in table \"mp.medical_providers\": in computed field \"names\": the computed field \"names\" cannot be added to table \"mp.medical_providers\" because the function \"mp.getarraynames\" returning type _text is not a BASE type",
  "path": "$.args[0].args",
  "code": "invalid-configuration",
  "internal": [
    {
      "definition": {
        "comment": null,
        "definition": {
          "function": {
            "name": "getarraynames",
            "schema": "mp"
          }
        },
        "name": "names",
        "source": "apple",
        "table": {
          "name": "medical_providers",
          "schema": "mp"
        }
      },
      "name": "computed_field names in table mp.medical_providers in source apple",
      "reason": "Inconsistent object: in table \"mp.medical_providers\": in computed field \"names\": the computed field \"names\" cannot be added to table \"mp.medical_providers\" because the function \"mp.getarraynames\" returning type _text is not a BASE type",
      "type": "computed_field"
    }
  ]
}

What is the expected behaviour?

Computed field successfully created

How to reproduce the issue?

SQL function for the computed field:

CREATE OR REPLACE FUNCTION mp.getArrayNames(mp_row mp.medical_providers)
 RETURNS text[]
 LANGUAGE sql
 STABLE
AS $function$
    SELECT array[name::text] FROM (
        SELECT name FROM mp.medical_provider_names WHERE medical_provider_id = mp_row.medical_provider_id AND deleted_at IS NULL
        UNION ALL (SELECT name FROM mp.medical_providers WHERE medical_provider_id = mp_row.medical_provider_id)
        UNION ALL (SELECT en_name as name FROM mp.medical_providers WHERE medical_provider_id = mp_row.medical_provider_id)
    )
$function$;

Please provide any traces or logs that could help here.

{
  "error": "Inconsistent object: in table \"mp.medical_providers\": in computed field \"names\": the computed field \"names\" cannot be added to table \"mp.medical_providers\" because the function \"mp.getarraynames\" returning type _text is not a BASE type",
  "path": "$.args[0].args",
  "code": "invalid-configuration",
  "internal": [
    {
      "definition": {
        "comment": null,
        "definition": {
          "function": {
            "name": "getarraynames",
            "schema": "mp"
          }
        },
        "name": "names",
        "source": "apple",
        "table": {
          "name": "medical_providers",
          "schema": "mp"
        }
      },
      "name": "computed_field names in table mp.medical_providers in source apple",
      "reason": "Inconsistent object: in table \"mp.medical_providers\": in computed field \"names\": the computed field \"names\" cannot be added to table \"mp.medical_providers\" because the function \"mp.getarraynames\" returning type _text is not a BASE type",
      "type": "computed_field"
    }
  ]
}

Any possible solutions/workarounds you're aware of?

No

Keywords

array type _text computed field

[deathemperor]

current workaround: use generated column

Function:

CREATE OR REPLACE FUNCTION mp.getArrayNamesById(mp_id uuid)
 RETURNS text[]
 LANGUAGE sql
 IMMUTABLE
AS $function$
    SELECT array_agg(name) FROM (
        SELECT name FROM mp.medical_provider_names WHERE medical_provider_id = mp_id AND deleted_at IS NULL
        UNION ALL (SELECT name FROM mp.medical_providers WHERE medical_provider_id = mp_id)
        UNION ALL (SELECT en_name as name FROM mp.medical_providers WHERE medical_provider_id = mp_id)
    )
$function$;

column:

ALTER TABLE mp.medical_providers add COLUMN names text[] generated always AS (mp.getArrayNamesById(medical_provider_id)) stored;

The solution has a draw back: names will be cached (due to immutability?). Unless main record is updated, same value is returned even new record is inserted into medical_provider_names

[rakeshkky]

Hi @deathemperor, return type of computed field function must be either SETOF <table> or BASE type (scalar type). The text[] is not a base type. It is a container type. Please refer to docs for more details. I'm closing this issue for now.