How to return an array of table records in PostgreSQL

Question

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?

array_agg(t)? But why return an array? Why not use returns setof table1? — user330315
– user330315, Commented Aug 21, 2020 at 14:35
No, I tested that: `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"`` — coterobarros
– coterobarros, Commented Aug 21, 2020 at 14:36

user330315 · Accepted Answer · 2020-08-21 14:38:07Z

1

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.

answered Aug 21, 2020 at 14:38

user330315

Sign up to request clarification or add additional context in comments.

1 Comment

coterobarros Over a year ago

Thanks, speed is important to me.

Collectives™ on Stack Overflow

How to return an array of table records in PostgreSQL

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related