How to pass table rows to a plpgsql function?

Question

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):

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;

which gives the expected output:

SELECT * FROM toy_function( 10, 20 );

 x  |  y  
----+-----
 31 | 200
 32 | 400
(2 rows)

But if I pass rows from a table to it, like this:

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;

I get a list of records, not the columns like I was getting before:

 toy_function 
--------------
 (4,2)
 (5,4)
 (7,8)
 (8,16)
 (10,18)
 (11,36)
(6 rows)

Wrapping the function call in ().* returns separate columns, but slows down the query a lot (in my original problem, it goes from 2 seconds to 6 seconds!).

I also tried passing the input data in a subquery, but it fails with an error I don't quite understand:

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));

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?

Answer 1

After digging for a couple more weeks, I found the answer: it's LATERAL JOIN. In my example, the query I need is:

WITH data AS (
    SELECT 1 * i AS a, 2 * i AS b
    FROM GENERATE_SERIES( 1, 3, 1 ) as i
)
SELECT
    f.*
FROM
    data, LATERAL toy_function( a, b ) f;

which gives the result I was looking for:

 x  | y
----+----
  4 |  2
  5 |  4
  7 |  8
  8 | 16
 10 | 18
 11 | 36
(6 rows)

(NOTE: the LATERAL keyword is optional for functions).

This new join was added to postgresql 9.3, docs here, where they explicitely mention this usage: "A common application is providing an argument value for a set-returning function". Also, the runtime of the query is now ok, it doesn't take 3x as much.

Related posts (for reference):

How can you expand a "condensed" PostgreSQL row into separate columns?

What is the difference between LATERAL and a subquery in PostgreSQL?

Call a set-returning function with an array argument multiple times

As for the reason of the increased runtime when wrapping the function call in ().* , it turns out it's because of a a bad macro expansion in the parser, which doesn't happen when you do a LATERAL join. See here for more details:

How to avoid multiple function evals with the (func()).* syntax in an SQL query?

Answer 2

Here's an example how to use a record of a table as parameter:

CREATE FUNCTION  your_function_name( paramater_name table_name ) RETURNS INTEGER
AS
$BODY$
   RAISE NOTICE 'You can use value from your table : field_x = % ', paramater_name.field_x;
   RETURN 0;
$BODY$
LANGUAGE 'plpgsql';