title: Can I have a postgres plpgsql function return variable-column records?
body: <p>I want to create a postgres function that builds the set of columns it
  returns on-the-fly; in short, it should take in a list of keys, build
  one column per-key, and return a record consisting of whatever that set
  of columns was.  Briefly, here's the code:</p>
  
  <pre><code>CREATE OR REPLACE FUNCTION reports.get_activities_for_report() RETURNS int[] AS $F$
  BEGIN
      RETURN ARRAY(SELECT activity_id FROM public.activity WHERE activity_id NOT IN (1, 2));
  END;
  $F$
  LANGUAGE plpgsql
  STABLE;
  
  CREATE OR REPLACE FUNCTION reports.get_amount_of_time_query(format TEXT, _activity_id INTEGER) RETURNS TEXT AS $F$
  DECLARE
      _label TEXT;
  BEGIN
      SELECT label INTO _label FROM public.activity WHERE activity_id = _activity_id;
      IF _label IS NOT NULL THEN
          IF lower(format) = 'percentage' THEN
              RETURN $$TO_CHAR(100.0 *$$ ||
              $$ (SUM(CASE WHEN activity_id = $$ || _activity_id || $$ THEN EXTRACT(EPOCH FROM ended - started) END) /$$ ||
              $$ SUM(EXTRACT(EPOCH FROM ended - started))),$$ ||
              $$ '990.99 %') AS $$ || quote_ident(_label);
          ELSE
              RETURN $$SUM(CASE WHEN activity_id = $$ || _activity_id || $$ THEN ended - started END)$$ ||
              $$ AS $$ || quote_ident(_label);
          END IF;
      END IF;
  END;
  $F$
  LANGUAGE plpgsql
  STABLE;
  
  CREATE OR REPLACE FUNCTION reports.build_activity_query(format TEXT, activities int[]) RETURNS TEXT AS $F$
  DECLARE
      _activity_id INT;
      query TEXT;
      _activity_count INT;
  BEGIN
      _activity_count := array_upper(activities, 1);
      query := $$SELECT agent_id, portal_user_id, SUM(ended - started) AS total$$;
      FOR i IN 1.._activity_count LOOP
          _activity_id := activities[i];
  
          query := query || ', ' || reports.get_amount_of_time_query(format, _activity_id);
      END LOOP;
      query := query || $$ FROM public.activity_log_final$$ ||
      $$ LEFT JOIN agent USING (agent_id)$$ ||
      $$ WHERE started::DATE BETWEEN actual_start_date AND actual_end_date$$ ||
      $$ GROUP BY agent_id, portal_user_id$$ ||
      $$ ORDER BY agent_id$$;
      RETURN query;
  END;
  $F$
  LANGUAGE plpgsql
  STABLE;
  
  CREATE OR REPLACE FUNCTION reports.get_agent_activity_breakdown(format TEXT, start_date DATE, end_date DATE) RETURNS SETOF RECORD AS $F$
  DECLARE
      actual_end_date DATE;
      actual_start_date DATE;
      query TEXT;
      _rec RECORD;
  BEGIN
      actual_start_date := COALESCE(start_date, '1970-01-01'::DATE);
      actual_end_date := COALESCE(end_date, now()::DATE);
      query := reports.build_activity_query(format, reports.get_activities_for_report());
  
      FOR _rec IN EXECUTE query LOOP
          RETURN NEXT _rec;
      END LOOP;
  END
  $F$
  LANGUAGE plpgsql;
  </code></pre>
  
  <p>This builds queries that look (roughly) like this:</p>
  
  <pre><code>SELECT agent_id, 
      portal_user_id, 
      SUM(ended - started) AS total, 
      SUM(CASE WHEN activity_id = 3 THEN ended - started END) AS "Label 1"
      SUM(CASE WHEN activity_id = 4 THEN ended - started END) AS "Label 2"
  FROM public.activity_log_final 
      LEFT JOIN agent USING (agent_id) 
  WHERE started::DATE BETWEEN actual_start_date AND actual_end_date 
  GROUP BY agent_id, portal_user_id 
  ORDER BY agent_id
  </code></pre>
  
  <p>When I try to call the <code>get_agent_activity_breakdown()</code> function, I get this error:</p>
  
  <pre><code>psql:2009-10-22_agent_activity_report_test.sql:179: ERROR:  a column definition list is required for functions returning "record"
  CONTEXT:  SQL statement "SELECT * FROM reports.get_agent_activity_breakdown('percentage', NULL, NULL)"
  PL/pgSQL function "test_agent_activity" line 92 at SQL statement
  </code></pre>
  
  <p>The trick is, of course, that the columns labeled 'Label 1' and 'Label
  2' are dependent on the set of activities defined in the contents of the
  activity table, which I cannot predict when calling the function.  How
  can I create a function to access this information?</p>