Postgresql – Postgres Pass Subquery results to function that returns TABLE

functionspostgresqlsubquery

I have a function that accepts arrays as parameters and returns a table. I can get the arguments passed in correctly, but it is returning rows of type record. If I try to expand the record with FROM I get syntax errors

SELECT
  zdb.filters(
      sub.idx
    , sub.labels
    , variadic sub.filters
    ) 
    FROM (
      SELECT
        'conversations_zdx' as idx
        , ARRAY_AGG(id)::TEXT[] as labels
        , ARRAY_AGG(filters) as filters
        FROM conversation_views 
        group by organization_id
) as sub;

+------------------------------------------+
| filters                                  |
|------------------------------------------|
| (32a430c6-eb33-4ac1-8ce7-9fb64e5b465c,1) |
| (a9177e44-95bf-4b1b-8724-b48efc97e3cf,4) |
+------------------------------------------+

The function signature is as follows

FUNCTION zdb.filters(
    index regclass,
    labels text[],
    filters JSON[]) 
RETURNS TABLE (
    label text,
    doc_count bigint)

This will give me errors

SELECT
  label
, doc_count 
FROM  zdb.filters(
      sub.idx
    , sub.labels
    , variadic sub.filters
    ) 
    FROM (
      SELECT
        'conversations_zdx' as idx
        , ARRAY_AGG(id)::TEXT[] as labels
        , ARRAY_AGG(filters) as filters
        FROM conversation_views 
        group by organization_id
) as sub;

update 8/26/19

The hangup is that I want to join the result of the function with another table, but I can't seem to get it.

this is the closest I can get, but the result of the function call, is still a record that I can't expand

WITH cviews AS (
    select
        id
    , organization_id
    , created_by
    , created_at
    , modified_by
    , modified_at
    , name
    , description
    , cache
    from conversation_views
    where organization_id = '3b440f43-c5f1-4338-a904-601c4dc8fd15'
)
, aggs AS (
    select organization_id, array_agg(id::text) as labels, array_agg(cache) as filters
      from cviews
      group by organization_id
  )
, counts AS (
  select 
    '3b440f43-c5f1-4338-a904-601c4dc8fd15' as organization_id
    , zdb.filters(
        'conversations_zdx'
    , aggs.labels
    , aggs.filters
    )
  from aggs
)
select
cviews.*, counts.*
from cviews
inner join counts on counts.organization_id::UUID = cviews.organization_id::UUID

Best Answer

Write a PL/pgSQL function and iterate through the conversation_view results:

DECLARE
   sub record;
BEGIN
   FOR sub IN
      SELECT 'conversations_zdx' as idx,
             ARRAY_AGG(id)::TEXT[] as labels,
             ARRAY_AGG(filters) as filters
      FROM conversation_views 
      GROUP BY organization_id
   LOOP
      RETURN QUERY
         SELECT idx, labels, filters
         FROM zdb.filters(
                 sub.idx,
                 sub.labels,
                 sub.filters
              ) AS q(idx,labels,filters);
   END LOOP;
END;
Related Question