Return Multiple Columns and Rows from a Function Postgresql Instead of Record

Return multiple columns and rows from a function PostgreSQL instead of record

I was able to see it as expected with this query:

SELECT * FROM brand_hierarchy (id)

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

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;

How can I return multiple rows of records in PL/pgSQL

The function needs to return a SETOF RECORD instead of RECORD and have one RETURN NEXT per row instead of a single RETURN, as in:

CREATE FUNCTION test() RETURNS SETOF RECORD AS $$
DECLARE
 rec record;
BEGIN
  select 1,2 into rec;
  return next rec;

  select 3,4 into rec;
  return next rec;
END $$ language plpgsql;

Caller:

=> select * from test() as x(a int ,b int) ;
 a | b 
---+---
 1 | 2
 3 | 4
(2 rows)

Note that SQL being strongly and statically typed, the RECORD pseudo-type is hard to work with.

Often it's less cumbersome to use right from the start a composite type with a full definition of names and type for each column, either with the TABLE(...) syntax for an anonymous type or with CREATE TYPE for a persistent named type.

Return multiple fields as a record in PostgreSQL with PL/pgSQL

You need to define a new type and define your function to return that type.

CREATE TYPE my_type AS (f1 varchar(10), f2 varchar(10) /* , ... */ );

CREATE OR REPLACE FUNCTION get_object_fields(name text) 
RETURNS my_type 
AS 
$$

DECLARE
  result_record my_type;

BEGIN
  SELECT f1, f2, f3
  INTO result_record.f1, result_record.f2, result_record.f3
  FROM table1
  WHERE pk_col = 42;

  SELECT f3 
  INTO result_record.f3
  FROM table2
  WHERE pk_col = 24;

  RETURN result_record;

END
$$ LANGUAGE plpgsql; 

If you want to return more than one record you need to define the function as returns setof my_type

---

Update

Another option is to use RETURNS TABLE() instead of creating a TYPE which was introduced in Postgres 8.4

CREATE OR REPLACE FUNCTION get_object_fields(name text) 
  RETURNS TABLE (f1 varchar(10), f2 varchar(10) /* , ... */ )
...

PostgreSQL - Function to return multiple columns

you don't need the extra type definition. And to return multiple rows, use return query:

Something like this:

CREATE OR REPLACE FUNCTION Repeat(fromDate date,toDate date)
  returns table (label text, cnt bigint)
AS 
$$
BEGIN
    Return query 
       SELECT label, count(*) AS Cnt 
       from test 
       where date between fromDate and toDate
       group by label;
END;
$$
LANGUAGE plpgsql;

You don't even need a PL/pgSQL function, you can use a simple SQL function for this:

CREATE OR REPLACE FUNCTION Repeat(fromDate date, toDate date)
  returns table (label text, cnt bigint)
AS 
$$
   SELECT label, count(*) AS Cnt 
   from test 
   where date between fromDate and toDate
   group by label;
$$
LANGUAGE sql;

function returns multiple columns as a single column instead of multiple columns

you need to call the function like this:

select * from foo(6);

which will return something like this:

project_id | project_name | project_type | project_description | project_status
-----------|--------------|--------------|---------------------|----------------
         6 | test project |      inbound |        inbound test |     processing

it's a quirk of postgres that it can be called both ways and give you a result. you might want to check the docs on set returning functions some more, there are other ways to do this as well. Oh, there is a wiki page on it, written for plpgsql, but most applies to sql functions as well: http://wiki.postgresql.org/wiki/Return_more_than_one_row_of_data_from_PL/pgSQL_functions

Create a function that accepts a string and returns multiple rows

Something like this

CREATE OR REPLACE FUNCTION get_data(currency_type text) 
RETURNS TABLE  ( payment_id int, rental_id int, amount numeric(5,2) ) 
language plpgsql
as $$
begin 
   return query 
     SELECT b.payment_id, b.rental_id, 
    case 
        when currency_type = 'EUR' then b.amount * 1.16     
        when currency_type = 'RMB' then b.amount * 6.44 
        when currency_type = 'USD' then b.amount 
    end as amount 
    FROM payment b;
end;$$

It does return in the form of a table if you use

select * from get_data('EUR');

Here a demo

demo in db<>fiddle

Return table with columns

select * from foo4(4); should give you the result you are looking for.

---

Related Topics