Using RETURNS TABLE vs. OUT parameters   

SQL and PLPGSQL function Return single record in RETURNS TABLE

--returning a single record using SQL function
CREATE OR REPLACE FUNCTION fn_sqltestout(param_subject text, pos integer) 
    RETURNS TABLE(subject_scramble text, subject_char text)
   AS
$$
    SELECT  substring($1, 1,CAST(random()*length($1) As integer)) , 
      substring($1, 1,1) As subject_char;
    $$
  LANGUAGE 'sql' VOLATILE;
-- example use
SELECT  (fn_sqltestout('This is a test subject')).subject_scramble;
SELECT subject_scramble, subject_char FROM fn_sqltestout('This is a test subject');

--Same function but written in plpgsql
--PLPGSQL example -- return one record
CREATE OR REPLACE FUNCTION fn_plpgsqltestout(param_subject text)
  RETURNS TABLE(subject_scramble text, subject_char text)
   AS
$$
BEGIN
    subject_scramble := substring($1, 1,CAST(random()*length($1) As integer));
    subject_char := substring($1, 1,1);
    RETURN NEXT;
END;
    $$
  LANGUAGE 'plpgsql' VOLATILE;

-- example use
SELECT  (fn_sqltestout('This is a test subject')).subject_scramble;
SELECT subject_scramble, subject_char FROM fn_sqltestout('This is a test subject');  

SQL and PLPGSQL function RETURNS TABLE - return multiple records

-- test data to use --
CREATE TABLE testtable(id integer PRIMARY KEY, test text);
INSERT INTO testtable(id,test)
VALUES (1, 'Potato'), (2, 'Potato'), (3, 'Cheese'), (4, 'Cheese Dog');

--SQL function returning multiple records
CREATE OR REPLACE FUNCTION fn_sqltestmulti(param_subject varchar) 
    RETURNS TABLE(test_id integer, test_stuff text)
   AS
$$
    SELECT id, test
        FROM testtable WHERE test LIKE $1;
$$
  LANGUAGE 'sql' VOLATILE;
  
 -- example use
SELECT (fn_sqltestmulti('Cheese%')).test_stuff;
SELECT test_stuff FROM fn_sqltestmulti('Cheese%');

--both return:
 test_stuff
------------
 Cheese
 Cheese Dog
 
-- plpgsql function returning multiple records
-- note RETURN QUERY was introduced in 8.3
-- variant 1
CREATE OR REPLACE FUNCTION fn_plpgsqltestmulti(param_subject varchar) 
    RETURNS TABLE(test_id integer, test_stuff text)
   AS
$$
BEGIN
    RETURN QUERY SELECT id, test
        FROM testtable WHERE test LIKE param_subject;
END;
$$
  LANGUAGE 'plpgsql' VOLATILE;
  
-- variant 2 use this if you need to do something additional
-- or conditionally return values or more dynamic stuff
-- RETURN QUERY is generally more succinct and faster
CREATE OR REPLACE FUNCTION fn_plpgsqltestmulti(param_subject varchar) 
    RETURNS TABLE(test_id integer, test_stuff text)
   AS
$$
DECLARE 
    var_r record;
BEGIN
     FOR var_r IN(SELECT id, test 
                FROM test WHERE test LIKE param_subject)  LOOP
            test_id := var_r.id ; test_stuff := var_r.test;
            RETURN NEXT;
     END LOOP;
END;
$$
  LANGUAGE 'plpgsql' VOLATILE;
-- example use
-- This is legal in PostgreSQL 8.4+ 
-- (prior versions plpgsql could not be called this way)
SELECT (fn_plpgsqltestmulti('Cheese%')).test_stuff;


SELECT test_stuff FROM fn_plpgsqltestmulti('Cheese%');

-- All return:
 test_stuff
------------
 Cheese
 Cheese Dog

Which is faster OUT or RETURNS TABLE

It's great that there are so many ways you can choose to hang yourself, but which way is the least painful and the quickest? I was wondering that myself so I did some tests and the tests seemed to suggest there is no difference in speed, however it suggested something else I really wasn't expecting (about 9.0) that what I thought was really bad form for speed (though shorter) becomes faster or a non-issue when you move to 9.0

CREATE OR REPLACE FUNCTION testspeed_table(it numeric(20))
 RETURNS TABLE(newit numeric(20), itprod numeric(20))
 AS
 $$
  SELECT j::numeric(20), $1*j::numeric(20) As itprod
    FROM generate_series(1,$1::bigint) As j;
 
 $$
 LANGUAGE 'sql' VOLATILE;
 
CREATE OR REPLACE FUNCTION testspeed_out(it numeric(20), 
  OUT newit numeric(20), OUT itprod numeric(20) )
 RETURNS setof record
 AS
 $$
  SELECT j::numeric(20), $1*j::numeric(20) As itprod
    FROM generate_series(1,$1::bigint) As j;
 
 $$
 LANGUAGE 'sql' VOLATILE;

The results suggest that what I thought was the case as described in Output parameters, custom data type gotchas may not always be the case or at least not for SQL written functions. More analysis is called for. Also notice how the speeds flips between 8.4 and 9.0 (or at least on Windows), where calling the ().* was worse in 8.4 and in 9.0 actually faster than the FROM syntax. This is something I'll need to investigate with plpgsql functions with real tests and on Linux platform as well to make sure its not abberations in my development environment or idiosyncracies of the test example. I would be interested in knowing the explanations of others regarding what we consider a bit surprising of results and under what conditions this manifests.

-- On a 9.0.2 Windows 32-bit
-- 1813 ms, 1750 ms
 SELECT (testspeed_table(300000)).*;

-- 2453 ms, 2234ms, 2031ms
  SELECT * FROM
  testspeed_table(300000);
  
 -- 1781ms, 1750ms
 SELECT (testspeed_out(300000)).*;

--  2766ms, 2015ms
  SELECT *
  FROM testspeed_out(300000);
  
-- On a 8.4 Windows 32-bit  
-- 3062 ms, 3047 ms
 SELECT (testspeed_table(300000)).*;

-- 2719ms, 2234ms, 2218ms
  SELECT * FROM
  testspeed_table(300000);
  
 -- 3078 ms, 3078 ms, 3063 ms
 SELECT (testspeed_out(300000)).*;

--  2922 ms, 2860 ms, 
  SELECT *
  FROM testspeed_out(300000);