How to return multiple rows from PL/pgSQL function?

Question

I have spent good amount of time trying to figure it out and I haven't been able to resolve it. So, I need your help please.

I am trying to write a PL/pgSQL function that returns multiple rows. The function I wrote is shown below. But it is not working.

CREATE OR REPLACE FUNCTION get_object_fields()
RETURNS SETOF RECORD
AS 
$$
DECLARE result_record keyMetrics;
BEGIN
    return QUERY SELECT department_id into result_record.visits 
    from fact_department_daily 
    where report_date='2013-06-07';
    --return result_record;
END

$$ LANGUAGE plpgsql; 

SELECT * FROM get_object_fields;

It is returning this error:

ERROR: RETURN cannot have a parameter in function returning set;
use RETURN NEXT at or near "QUERY"

As always, your version of Postgres is an essential detail.

Erwin Brandstetter
– Erwin Brandstetter

2013-06-24 17:34:07 +00:00
Commented Jun 24, 2013 at 17:34 — Erwin Brandstetter
– Erwin Brandstetter, Commented Jun 24, 2013 at 17:34
This question is answered on stackexchange

Eugen Konkov
– Eugen Konkov

2018-09-08 14:07:09 +00:00
Commented Sep 8, 2018 at 14:07 — Eugen Konkov
– Eugen Konkov, Commented Sep 8, 2018 at 14:07

Erwin Brandstetter · Accepted Answer · 2020-07-23 21:15:26Z

21

After fixing the bugs @Pavel pointed out, also define your return type properly, or you have to provide a column definition list with every call.

This call:

SELECT * FROM get_object_fields()

... assumes that Postgres knows how to expand *. Since you are returning anonymous records, you get an exception:

ERROR:  a column definition list is required for functions returning "record"

One way (of several) to fix this is with RETURNS TABLE (Postgres 8.4+):

CREATE OR REPLACE FUNCTION get_object_fields()
  RETURNS TABLE (department_id int) AS 
$func$
BEGIN
   RETURN QUERY
   SELECT department_id
   FROM   fact_department_daily 
   WHERE  report_date = '2013-06-07';
END
$func$ LANGUAGE plpgsql;

Works for SQL functions just the same.

PostgreSQL: ERROR: 42601: a column definition list is required for functions returning "record"

edited Jul 23, 2020 at 21:15

answered Jun 24, 2013 at 17:30

Erwin Brandstetter

668k159 gold badges1.2k silver badges1.3k bronze badges

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

Comments

Pavel Stehule · Accepted Answer · 2013-06-24 12:24:22Z

7

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;

answered Jun 24, 2013 at 12:24

Pavel Stehule

46.6k6 gold badges103 silver badges102 bronze badges

Comments

superlogical · Accepted Answer · 2016-07-12 00:24:54Z

Here's one way

drop function if exists get_test_type();

drop type if exists test_comp;
drop type if exists test_type;
drop type if exists test_person;

create type test_type as (
  foo int, 
  bar int
);

create type test_person as (
  first_name text, 
  last_name text
);

create type test_comp as 
(
  prop_a test_type[], 
  prop_b test_person[]
);


create or replace function get_test_type()
returns test_comp
as $$
declare
  a test_type[];
  b test_person[];
  x test_comp;
begin

  a := array(
    select row (m.message_id, m.message_id) 
    from message m
  );

  -- alternative 'strongly typed'
  b := array[
    row('Bob', 'Jones')::test_person,
    row('Mike', 'Reid')::test_person
  ]::test_person[];

  -- alternative 'loosely typed'
  b := array[
    row('Bob', 'Jones'),
    row('Mike', 'Reid')
  ];

  -- using a select
  b := array (
    select row ('Jake', 'Scott')
    union all 
    select row ('Suraksha', 'Setty')
  );  

  x := row(a, b);

  return x;  
end;
$$
language 'plpgsql' stable;


select * from get_test_type();

RonJohn · Accepted Answer · 2023-09-25 21:28:02Z

0

CREATE OR REPLACE FUNCTION get_object_fields()
RETURNS table (department_id integer)
AS 
$$
DECLARE result_record keyMetrics;
BEGIN
    return QUERY 
SELECT department_id 
    from fact_department_daily 
    where report_date='2013-06-07';
    --return result_record;
END;

$$ LANGUAGE plpgsql; 

SELECT * FROM get_object_fields()

edited Sep 25, 2023 at 21:28

RonJohn

4533 gold badges9 silver badges24 bronze badges

answered May 26, 2022 at 5:40

Ajay

1

1 Comment

Community Over a year ago

As it’s currently written, your answer is unclear. Please edit to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers in the help center.

Collectives™ on Stack Overflow

How to return multiple rows from PL/pgSQL function?

4 Answers 4

Comments

Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related