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

CREATE OR REPLACE FUNCTION my_func(some_data custom_datum[]) RETURNS VOID AS $$
        create table foo_table as (

           coalesce(foo_ind, bar_ind, ter_ind) as foobarter,

           -- 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))
$$ 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:


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?

  • 1
    Passing in an array like that is usually a horrible idea, paste the definition of my_func Sep 1, 2017 at 15:41
  • the definition of my_func is there
    – Randomize
    Sep 1, 2017 at 15:42
  • 2
    @Randomize I see the signature only. Sep 1, 2017 at 15:43
  • 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?
    – Randomize
    Sep 1, 2017 at 15:50

2 Answers 2


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:

  SELECT whatever
  FROM wherever
    (foo_ind, import_date),
    (bar_ind, import_date),
    (ter_ind, import_date)

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

  • The problem is that I am workaround different kind of inputs. Please read my updated question.
    – Randomize
    Sep 1, 2017 at 16:06
  • @Randomize how did your function my_func return the table name of the table it generated? Sep 1, 2017 at 16:09
  • it is not doing it at the moment
    – Randomize
    Sep 1, 2017 at 16:14
  • 1
    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) Sep 1, 2017 at 16:22
  • 1
    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. Sep 1, 2017 at 17:24

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.