Search code examples
sqlpostgresqlset-returning-functions

How to return two columns with function

I want to return 2 values with my SQL function:

CREATE OR REPLACE FUNCTION get_avg_prices(...)
  RETURNS table(avg_sale_price decimal, avg_rent_price decimal) 
AS
$$

SELECT 
  building_prices.avg_sale_price, 
  building_prices.avg_rent_price
FROM 
  ...

$$
  LANGUAGE sql;

It works but returns the values in single column:

How to return the values in 2 separate columns?

Solution

  • Table functions (functions defined as returns table or returns setof) need to be used in the from clause like a table.

    So you need to use:

    select * 
from get_avg_prices(...);

    Only scalar functions (functions which return only a single value, e.g. a number) should be put into the select list.