RETURN cannot have a parameter in function returning set; use RETURN NEXT at or near "QUERY" Postgres

Question

When I try to compile this function:

CREATE OR REPLACE FUNCTION test_proc4(sr_num bigint) 
    RETURNS TABLE(sr_number bigint, product_serial_number varchar(35))
AS $$
    BEGIN
        RETURN QUERY SELECT select sr_number,product_serial_number from temp_table where sr_number=sr_num
    END;
$$
LANGUAGE 'plpgsql' VOLATILE;

Why do I get this error?

RETURN cannot have a parameter in function returning set; use RETURN NEXT at or near "QUERY"

I am using postgres version 8.4.

Answer 1

Aside from a typo (you duplicated select, and you didn't terminate the RETURN statement with a semicolon), I think you were quite close - just needed to disambiguate the table columns within the query by qualifying them with the table name. Try this (reformatted hopefully to improve readability):

CREATE OR REPLACE FUNCTION test_proc4(sr_num bigint)
    RETURNS TABLE(sr_number bigint, product_serial_number varchar(35)) AS $$
  BEGIN
    RETURN QUERY
      SELECT
        temp_table.sr_number, temp_table.product_serial_number
      from temp_table
      where temp_table.sr_number=sr_num;
  END;
$$ LANGUAGE 'plpgsql' VOLATILE;

Caveat: I only tested this in PG 9.4, so haven't tested it in your version yet (which is no longer supported, I might add). In case there are issues regarding PLPGSQL implementation between your version and 9.4, you can try this form as an alternative:

CREATE OR REPLACE FUNCTION test_proc4(sr_num bigint)
    RETURNS TABLE(sr_number bigint, product_serial_number varchar(35)) AS $$
  BEGIN
    FOR sr_number, product_serial_number IN
      SELECT
        temp_table.sr_number, temp_table.product_serial_number
      from temp_table
      where temp_table.sr_number=sr_num
    LOOP
      RETURN NEXT;
    END LOOP;
    RETURN;
  END;
$$ LANGUAGE 'plpgsql' VOLATILE;

A small sanity check using a table I populated with dummy data:

postgres=# select * from temp_table;
 sr_number | product_serial_number
-----------+-----------------------
         1 | product 1
         2 | product 2
         2 | another product 2
(3 rows)

postgres=# select * from test_proc4(1);
 sr_number | product_serial_number
-----------+-----------------------
         1 | product 1
(1 row)

postgres=# select * from test_proc4(2);
 sr_number | product_serial_number
-----------+-----------------------
         2 | product 2
         2 | another product 2
(2 rows)