CODEWITHSUNDEEP
postgresqlplpgsql
Asma Zubair
Asma Zubair

Reputation: 428

How to return multiple rows from PL/pgSQL function?

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"

Upvotes: 14

Views: 45997

Answers (4)

Ajay
Ajay

Reputation: 1

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()

Upvotes: 0

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656616

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.

Related:

Upvotes: 21

superlogical
superlogical

Reputation: 14950

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();

Upvotes: 0

Pavel Stehule
Pavel Stehule

Reputation: 45795

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;

Upvotes: 7

Related Questions