Return Multiple Fields as a Record in Postgresql with Pl/Pgsql

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) /* , ... */ )
...

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 values from declared variables in postrgres function

try this - you can use RETURN TABLE CONCEPT

CREATE OR REPLACE FUNCTION public.select_multiple_values(id_ bigint)
RETURNS TABLE(id_child bigint, id_parent bigint, name_ character varying ) 
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE PARALLEL UNSAFE
    ROWS 1000
    
AS $BODY$
declare p_id_child bigint;
declare p_id_parent bigint;
declare p_name_ character varying;
begin

    select id  into p_id_child from public.req where id = id_ ;
    select reg_id   into p_id_parent from public.req where id = id_;
    select "name"  into p_name_ from public.reg where id = id_parent ;

    
    return query select p_id_child as id_child, p_id_parent as id_parent, p_name_ as name_ ;
    
END
$BODY$;

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)

How to return multiple values from one function postgres

To return value to a function that returns table, we have to include RETURN QUERY statement inside function.

CREATE OR REPLACE FUNCTION get_students(classId int) 
    RETURNS TABLE (
        students_cursor refcursor,
        students_list text
) 
AS $$
DECLARE
    students text DEFAULT '';
    student record;
    cursor_students CURSOR(classId integer)
        FOR SELECT firstName, surname
        FROM students
        WHERE class = classId;
BEGIN

    OPEN cursor_students(classId);

    LOOP
        FETCH cursor_students INTO student;
        EXIT WHEN NOT FOUND;

    students := students  '  '  student.firstName  ' '  student.surname;
    END LOOP;

    CLOSE cursor_students;

    RETURN QUERY
       SELECT cursor_students,students;

END; $$ 

LANGUAGE 'plpgsql';

How to return multiple rows from PL/pgSQL function?

I see more bugs:

first, a SET RETURNING FUNCTIONS call has following syntax

SELECT * FROM get_object_fields()

second - RETURN QUERY forwards query result to output directly. You cannot store this result to variable - it is not possible ever in PostgreSQL now.

BEGIN
  RETURN QUERY SELECT ....; -- result is forwarded to output directly
  RETURN;   -- there will not be any next result, finish execution
END;

third - these simple functions is better to implement in SQL languages

CREATE OR REPLACE FUNCTION get_object_fields()
RETURNS SETOF RECORD AS $$
SELECT department_id WHERE ...
$$ LANGUAGE sql STABLE;

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;

Return multiple rows from plpgsql function

You need to do

select * from function1();

---

Related Topics