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?

If an answer resolved your issue then please accept it. Accepting an answer helps future questioners having the same issue. Do not just leave an answered question open. — Belayer
– Belayer, Commented Jul 14, 2021 at 16:48

user330315 · Accepted Answer · 2021-07-14 06:16:11Z

4

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;

answered Jul 14, 2021 at 6:16

user330315

Sign up to request clarification or add additional context in comments.

1 Comment

diziaq Over a year ago

Thank you for your brilliancy and blazing fast reaction.

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related