The next planned major release of the Metadb software, version 1.4, will extract all the data fields from the data arrays into columns. Given this change, many of the derived tables the Reporting SIG currently maintains can be retired because they were designed to extract data from the data arrays. Some derived tables were created to achieve different goals, however, so they will be retained. The Reporting SIG is reviewing release 1.7.9 of derived tables for use with Metadb with the goal of reducing the number of tables overall. The focus will be on keeping tables that help report writers create queries that work well with the FOLIO software data model.
Reports that use derived tables can be rewritten to use CTEs (Common Table Expression), such as the CTE at the top of this user_permission_sets.sql query:
WITH permissions_users AS (
SELECT
jsonb_array_elements_text(jsonb->'permissions') AS user_permissions,
jsonb_extract_path_text(jsonb, 'userId')::uuid AS user_id
FROM
folio_permissions.permissions_users
)
See the Chapter on CTEs in the cookbook for more information:
The functionality of derived tables could also be replaced with FUNCTIONS.
1.7.9 Derived Table for Metadb | Keep or Retire? | Document in Wiki? | Notes |
|---|---|---|---|
Retire | Yes | custom properties will be extracted into columns | |
END |