Set returning functions (SRFs) can be used to return a set of rows. The row type can either be a base type such as integer, composite, table type, pseudo type, or domain type. To return a set from a function, the keyword SETOF is used to mark the function as an SRF, as follows:

-- In SQL 
CREATE OR REPLACE FUNCTION car_portal_app.car_model(model_name TEXT) RETURNS SETOF car_portal_app.car_model AS $$
  SELECT car_model_id, make, model FROM car_portal_app.car_model WHERE model = model_name;
$$ LANGUAGE SQL;

-- In plpgSQL
CREATE OR REPLACE FUNCTION car_portal_app.car_model1(model_name TEXT) RETURNS SETOF car_portal_app.car_model AS $$
BEGIN
  RETURN QUERY SELECT car_model_id, make, model FROM car_portal_app.car_model WHERE model = model_name;
END;
$$ LANGUAGE plpgsql;

To test the previous functions, let's run them. Note the caching effect of plpgsql on performance:

car_portal=> 	iming 
Timing is on.
car_portal=> SELECT * FROM car_portal_app.car_model('A1');
 car_model_id | make | model 
--------------+------+-------
            1 | Audi | A1
(1 row)

Time: 1,026 ms
car_portal=> SELECT * FROM car_portal_app.car_model1('A1');
 car_model_id | make | model 
--------------+------+-------
            1 | Audi | A1
(1 row)

Time: 0,546 ms

If the return type is not defined, one could:

• Define a new data type and use it
• Use a return table
• Use output parameters and record the datatype

Let's assume that we would like to return only the car_model_id and the make, as in this case, we do not have a data type defined. Thus, the preceding function can be written as:

-- SQL 
CREATE OR REPLACE FUNCTION car_portal_app.car_model2(model_name TEXT) RETURNS TABLE (car_model_id INT , make TEXT) AS $$
  SELECT car_model_id, make FROM car_portal_app.car_model WHERE model = model_name;
$$ LANGUAGE SQL;

-- plpgSQL
CREATE OR REPLACE FUNCTION car_portal_app.car_model3(model_name TEXT) RETURNS TABLE (car_model_id INT , make TEXT) AS $$
BEGIN
  RETURN QUERY SELECT car_model_id, make FROM car_portal_app.car_model WHERE model = model_name;
END;
$$ LANGUAGE plpgsql;

To test the functions:

car_portal=> SELECT * FROM car_portal_app.car_model2('A1');
 car_model_id | make 
--------------+------
            1 | Audi
(1 row)

Time: 0,797 ms
car_portal=> SELECT * FROM car_portal_app.car_model3('A1');
ERROR: column reference "car_model_id" is ambiguous
LINE 1: SELECT car_model_id, make FROM car_portal_app.car_model WHE...
               ^
DETAIL: It could refer to either a PL/pgSQL variable or a table column.
QUERY: SELECT car_model_id, make FROM car_portal_app.car_model WHERE model = model_name
CONTEXT: PL/pgSQL function car_model3(text) line 3 at RETURN QUERY
Time: 0,926 ms

Note that, in the preceding function, an error is raised because plpgsql was confusing the column name with the table definition. The reason behind this is that the return table is a shorthand for writing OUTPUT parameters. To fix this, we need to rename the attribute names, as follows:

CREATE OR REPLACE FUNCTION car_portal_app.car_model3(model_name TEXT) RETURNS TABLE (car_model_id INT , make TEXT) AS $$
BEGIN
  RETURN QUERY SELECT a.car_model_id, a.make FROM car_portal_app.car_model a WHERE model = model_name;
END;
$$ LANGUAGE plpgsql; 
car_portal=> SELECT * FROM car_portal_app.car_model3('A1');
 car_model_id | make 
--------------+------
            1 | Audi
(1 row)

The preceding function can also be written using the OUT variables; actually, the return table is implemented internally, as indicated by the error using the OUT variables, as follows:

CREATE OR REPLACE FUNCTION car_portal_app.car_model4(model_name TEXT, OUT car_model_id INT, OUT make TEXT ) RETURNS SETOF RECORD AS $$
BEGIN
  RETURN QUERY SELECT a.car_model_id, a.make FROM car_portal_app.car_model a WHERE model = model_name;
END;
$$ LANGUAGE plpgsql;
car_portal=> SELECT * FROM car_portal_app.car_model4('A1'::text);
 car_model_id | make 
--------------+------
            1 | Audi
(1 row)