title: How to pass table rows to a plpgsql function?
body: <p>I'm trying to create a function that can take rows from a table, one by one, and for each row produce 1 or more rows for a different table.  For example, let's take this toy function (NOTE: in this example, input and output have the same fields, but in my original problem the fields are different):</p>
  
  <pre><code>CREATE OR REPLACE FUNCTION toy_function( a integer, b integer )
  RETURNS TABLE( x integer, y integer ) AS $$
  BEGIN
      FOR i IN 1..2 LOOP
          x := a + b + i;
          y := a * b * i;
          RETURN NEXT;
      END LOOP;
  END;
  $$ LANGUAGE plpgsql;
  </code></pre>
  
  <p>which gives the expected output:</p>
  
  <pre><code>SELECT * FROM toy_function( 10, 20 );
  
   x  |  y  
  ----+-----
   31 | 200
   32 | 400
  (2 rows)
  </code></pre>
  
  <p>But if I pass rows from a table to it, like this:</p>
  
  <pre><code>WITH data AS (
      SELECT 1 * i AS a, 2 * i AS b
      FROM GENERATE_SERIES( 1, 3, 1 ) as i
  )
  SELECT
      toy_function( a, b )
  FROM
      data;
  </code></pre>
  
  <p>I get a list of records, not the columns like I was getting before:</p>
  
  <pre><code> toy_function 
  --------------
   (4,2)
   (5,4)
   (7,8)
   (8,16)
   (10,18)
   (11,36)
  (6 rows)
  </code></pre>
  
  <p>Wrapping the function call in <code>().*</code> returns separate columns, but slows down the query a lot (in my original problem, it goes from 2 seconds to 6 seconds!).</p>
  
  <p>I also tried passing the input data in a subquery, but it fails with an error I don't quite understand:</p>
  
  <pre><code>WITH data AS (
      SELECT 1 * i AS a, 2 * i AS b
      FROM GENERATE_SERIES( 1, 3, 1 ) as i
  )
  SELECT
      *
  FROM
      toy_function(( SELECT * FROM data));
  
  ERROR:  subquery must return only one column
  LINE 8: toy_function(( SELECT * FROM data));
  </code></pre>
  
  <p>Is there a way to do this? To pass the rows from "data" to the function one by one and get a table out of the function, with explicit columns?</p>