[pgpool-general: 7662] How to cache a SQL that is using customized function?

[chen dean]

Hi, pgpool team:
  I'm using pgpool as my cache for postgresql, my question is, why I can not cache this SQL:

              /* sql case1 tile */
              SELECT
              'cluster' AS type,
              'case1 tile' AS log,
              NULL AS zoom_to,
              region_id id,
              centroid estimated_geometric_location,
              St_asgeojson(centroid) latlon,
              type_id as region_type,
              count(tree_region.id) count,
              CASE WHEN count(tree_region.id) > 1000
              THEN  (count(tree_region.id) / 1000) || 'K'
              ELSE count(tree_region.id) || ''
              END AS count_text
              FROM active_tree_region tree_region
              WHERE zoom_level = 8
                AND tree_region.tree_id IN(
                  select distinct * from (
                    SELECT trees.id as id from trees
                      INNER JOIN (
                        SELECT id FROM planter
                        JOIN (
                          SELECT entity_id FROM getEntityRelationshipChildren(
                            (SELECT id FROM entity WHERE map_name = 'TheHaitiTreeProject')
                          )
                        ) org ON planter.organization_id = org.entity_id
                      ) planter_ids
                      ON trees.planter_id = planter_ids.id
                  union all
                    SELECT trees.id as id from trees
                      INNER JOIN (
                        SELECT id FROM planter
                        JOIN (
                          SELECT entity_id FROM getEntityRelationshipChildren(
                            (SELECT id FROM entity WHERE map_name = 'TheHaitiTreeProject')
                          )
                        ) org ON planter.organization_id = org.entity_id
                      ) planter_ids
                      ON trees.planter_id = planter_ids.id
                  ) t1
                )
              GROUP BY region_id, centroid, type_idtreetracker

In the SQL, I guess that's because the function `getEntityRelationshipChildren` make pgpool ignore the result of this SQL? This is a function I created by myself, it would select some other table and return the result, basically, I hope it get cached.
According to the document, there is a setting called `cache_safe_memqcache_table_list.` but the document says it just support view and table, so, how could I make this SQL cached?

Thanks! team.

Dadior
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.pgpool.net/pipermail/pgpool-general/attachments/20210808/449adb00/attachment.htm>