Array exceed allowed size in Postgres

Question

I have a function that accepts as parameter an array like:

CREATE OR REPLACE FUNCTION my_func(some_data custom_datum[]) RETURNS VOID AS $$
 BEGIN
 create table foo_table as (
 select
 coalesce(foo_ind, bar_ind, ter_ind) as foobarter,
 import_date,
 -- do some stuff here
 from unnest (some_data) as T
 group by grouping sets ((foo_ind, import_date), (bar_ind, import_date), (ter_ind, import_date))
 );
 END
$$ LANGUAGE plpgsql;

The input array is generated by another function foo. So I call everything in this way:

select my_func(array(select foo()));

where the function foo is:

CREATE OR REPLACE FUNCTION foo() RETURNS SETOF custom_datum

The problem is that for a big amount of data array(select foo()) returns:

ERROR: array size exceeds the maximum allowed (1073741823)

What I am trying to workaround is the lack of possibility to pass different functions, as at the moment, the input array gets generated by different functions:

select my_func(array(select foo()));
select my_func(array(select bar()));
select my_func(array(select ter()));
.... etc

How can I workaround this problem?

• Evan Carroll: Passing in an array like that is usually a horrible idea, paste the definition of my_func
• Randomize: the definition of my_func is there
• András Váczi: @Randomize I see the signature only.
• Randomize: I put the function. Yes actually I create and unnest an array which is quite useless but the problem is, how can I pass a set?

Answer 1

What your my_func is essentially doing is creating a MATERIALIZED VIEW -- a materialized view is a cached copy of a result set stored as a table. Drop the function and use the normal MATERIALIZED VIEW.

Skip generating an array -- waste of a time and space and may even be serializing the result set to disk twice. And, instead, just use something like this:

CREATE MATERIALIZED VIEW foo_view
AS
 SELECT whatever
 FROM wherever
 GROUP BY GROUPING SETS (
 (foo_ind, import_date),
 (bar_ind, import_date),
 (ter_ind, import_date)
 );

Now you can "refresh" this by doing REFRESH foo_view;

• Randomize: The problem is that I am workaround different kind of inputs. Please read my updated question.
• Evan Carroll: @Randomize how did your function my_func return the table name of the table it generated?
• Randomize: it is not doing it at the moment
• Evan Carroll: Just FYI @Randomize, A SQL array is a container type. It's not meant to hold a result set. And doing that is going to sting no way around it -- it's going to be worse in every way (slower, memory intensive, less terse, etc)
• Evan Carroll: SQL is a declarative language that has a declarative method that takes a query and generates an on disk cache of that query. You don't need to write a function that accepts an array, or accepts a text query, you just need CREATE MATERIALIZED VIEW that's sql-esque. That's the SQL way to do it.

Answer 2

You can possibly limit the output of foo():

SELECT foo() LIMIT 10000;

Or, most probably better, avoid using arrays altogether (pass the set, not an array made of it). For this, you will, of course, have to rewrite my_func(). Thinking about it, I can imagine that this will be beneficial from a performance point of view, too.