What is the simplest way to return multiple rows from table function in Postgres?

Question

There is a table my_table that I want to query through a table function.

create table my_table (
  col_1 text,
  col_2 numeric
  // 10 more columns
);

According to documentation and articles in the Internet, there are several ways to do that. But most of them involve defining a row type duplicating the structure of the resulting table like create function ... returns table( col_1 text, col_2 numeric, ...) in this case.

The simplest way I've found is as follows:

create or replace function my_func(p_x text)
   returns my_table
as
$$
  select * from my_table where col_1 = p_x;
$$
language sql;

--
select * from my_func('hello');

Unfortunately, it returns only the first row of the result, even though the query returns many. This is not what I expected.

If we change the function header to

create or replace function my_func(p_x text)
   returns table( col_1 text, col_2 numeric, /* and others */)

it works fine, but it would be great to avoid this duplication and reuse here the rowtype of my_table.

How to define a function with table result having exactly same columns as in my_table?

Answer 1

You need to define the function as returns setof my_table to indicate that it returns a set, rather than just one row:

create or replace function my_func(p_x text)
   returns SETOF my_table
as
$$
  select * from my_table where col_1 = p_x;
$$
language sql;