How to return an array of table records in PostgreSQL

I am getting a type mismatch error when trying to return an array of elements of type table1, the inherent type of the table1 I have declared.

Error occurred during SQL query execution

Razón:
 SQL Error [42P13]: ERROR: return type mismatch in function declared to return table1[]
  Detail: Actual return type is record[].
  Where: SQL function "arrayof_records"

This is an oversimplified code that reproduces my problem.

drop table if exists table1 cascade;

create table table1 (
  id        serial primary key,
  title     text,
  create_dt timestamp default now()
);

insert into table1 (title) values 
('one'),
('two'),
('three');

create or replace function arrayof_records ()
returns table1[]
stable language sql as $$
  select array_agg (t.*)
  from (
    select * from table1
    order by create_dt desc
  ) as t;
$$;

It is clear that the parser is expecting some other expression in the array_agg function. I have tried t, t.* and *. All of them fail.

I expect there is a syntax, as PostgreSQL 12 documentations states "array_agg(expression)|any non-array type".

Any idea?

Solution

You can use a slightly different way of creating the array:

create or replace function arrayof_records ()
  returns table1[]
  stable language sql 
as 
$$
  select array(
    select table1 
    from table1
    order by create_dt desc
  );
$$;

That's typically faster than array_agg() as well.