Home How to store intermediate results in plpgsql function?
Reply: 1

How to store intermediate results in plpgsql function?

Lesly O
1#
Lesly O Published in 2018-01-13 09:50:37Z

I have a table my_table and a corresponding function:

create function my_function() returns setof my_table as
$$
    select my_table.*
    from   my_table
    where  ...;
$$
language sql stable;

Now I want to use that function in another plpgsql function. I want to store the results of my_function() so I can continue with the data. How is this done in PostgreSQL? I tried the following:

create function my_other_function() returns setof my_table as
$$
    declare
        results setof my_table;
    begin
        results := (select * from my_function());

        ...
    end;
$$
language plpgsql stable;

This however gives the error that setof my_table is an invalid type in my_other_function(), which is kinda odd, as it is a valid return type in my_function()?

a_horse_with_no_name
2#
a_horse_with_no_name Reply to 2018-01-13 10:25:58Z

You can use an array for that:

create function my_other_function() 
  returns setof my_table 
as
$$
declare
  results my_table[];
begin

  select array_agg(t)
    into results
  from my_function() t;

  return query 
    select *
    from unnest(results);
end;
$$
language plpgsql stable;
You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.363585 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO