0

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
  • Can you show the failing query? It should work just fine if you put the function call into the FROM clause. – Laurenz Albe Aug 20 at 7:08
  • The last query above is the one that is failing – Eric Satterwhite Aug 26 at 19:55
  • I guess issue is that I want to get the columns expanded from the record returned from the function and join it to another table. But to get the columns, I have to specify the function as the from clause, which is the end of the line – Eric Satterwhite Aug 26 at 20:11
0

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;
  • Yeah, that is always an option. I was hoping it were possible with just a subquery, or CTE. but returning the table seems to always be the hangup. – Eric Satterwhite Aug 27 at 15:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.