Array Not Being Returned By Select Query Inside Plpgsql Test Case

SELECT query I am using: SELECT ARRAY[table_name,pg_size_pretty(table_size)] FROM ( SELECT table_name, pg_table_size(table_name) AS table_size, pg_index

Solution 1:

You are not the first to run into this. Currently, array_agg() (or the array constructor ARRAY(SELECT ...) only accept scalar values as input, not array types. so you cannot build multi-dimensional arrays with it.

The fix is simple, though. Create a custom aggregate function:

CREATE AGGREGATE array_agg_mult (anyarray)  (
    SFUNC     = array_cat
   ,STYPE     = anyarray
   ,INITCOND  = '{}');

After simplifying a couple of other things, a basic version of your function becomes:

CREATEFUNCTION example2()
  RETURNS text[] AS
$func$
SELECT array_agg_mult(ARRAY[ARRAY[tbl, pg_size_pretty(pg_table_size(tbl))]]
                      ORDERBY pg_total_relation_size(tbl) DESC) AS have
FROM (
   SELECT format('%I.%I', table_schema, table_name) AS tbl
   FROM   information_schema.tables
   WHERE  table_schema NOTLIKE'pg_%'AND    table_schema <>'information_schema'
   ) AS all_tables
$func$  LANGUAGEsql;

Related (with an outlook to Postgres 9.5):

• Selecting data into a Postgres array