category_path function throws error in a migrated database

[cmeessen]

The category_path function throws an error when trying to view a software page that uses categories. This only occurs when using a migrated database but not in a newly set up database. This is the database log:

2024-09-11 10:32:54.201 UTC [294] ERROR:  return type mismatch in function declared to return category
2024-09-11 10:32:54.201 UTC [294] DETAIL:  Final statement returns character varying instead of jsonb at column 5.
2024-09-11 10:32:54.201 UTC [294] CONTEXT:  SQL function "category_path" during startup
    SQL function "category_path_expanded" statement 1
    SQL function "category_paths_by_software_expanded" statement 1
2024-09-11 10:32:54.201 UTC [294] STATEMENT:  WITH pgrst_source AS (SELECT pgrst_call.pgrst_scalar FROM (SELECT $1 AS json_data) pgrst_payload, LATERAL (SELECT "software_id" FROM json_to_record(pgrst_payload.json_data) AS _("software_id" uuid) LIMIT 1) pgrst_body , LATERAL (SELECT "public"."category_paths_by_software_expanded"("software_id" := pgrst_body."software_id") pgrst_scalar) pgrst_call) SELECT null::bigint AS total_result_set, 1 AS page_total, coalesce(json_agg(_postgrest_t.pgrst_scalar)->0, 'null') AS body, nullif(current_setting('response.headers', true), '') AS response_headers, nullif(current_setting('response.status', true), '') AS response_status, '' AS response_inserted FROM (SELECT "category_paths_by_software_expanded".* FROM "pgrst_source" AS "category_paths_by_software_expanded"     ) _postgrest_t

category_path uses LIKE to specify its return type:

CREATE FUNCTION category_path(category_id UUID)
RETURNS TABLE (LIKE category)

The column order in category is different in a migrated database, because the community column is added to the end of the category table.