Strange error in the select of the array of records-CodePudding

As I understand Postgres supports selecting of arrays of complex types - records (tables). I found this answer so I try in my PLPGSQL function a code like this:

...
DECLARE
  deleting my_table[];
BEGIN
  deleting := ARRAY(SELECT mt.* FROM table1 t1
    INNER JOIN table2 t2 ON t2.id=t1.table1
    INNER JOIN my_table mt ON mt.id=t2.my_table
    WHERE t1.x=10);
...
END;
$$;

just simple example to illustrate the error. The error is:

SQL Error [42601]: ERROR: subquery must return only one column
where: PL/pgSQL function xxx(...) line 6 at assignment

The error is in the assignment deleting := ARRAY(SELECT mt.* ...) (I verified it with a debug RAISE directly after this line). But what is wrong with it? I want to select ALL columns sure, and my array type is the type of the mt table. It looks similar to the mentioned StackOverflow's answer IMHO. TBH I know only one alternative to achieve the same without querying of an records array: multiple records - one per column which looks dummy.

Would somebody hint with a right syntax? Any help will be very useful.

PS. I use Postgres 12.

CodePudding user response：

You can use one trick (it is equal to @a_horse_with_no_name notice - here is just more detailed description). Any Postgres's table has some invisible columns. One, that has same name like table name, holds all columns of table in composite format. I have not an idea, when this feature was introduced - it's pretty old (maybe from pre SQL age):

postgres=# select * from foo;
┌────┬────┐
│ a  │ b  │
╞════╪════╡
│ 10 │ 20 │
│ 30 │ 40 │
└────┴────┘
(2 rows)

postgres=# select foo from foo ;
┌─────────┐
│   foo   │
╞═════════╡
│ (10,20) │
│ (30,40) │
└─────────┘
(2 rows)

Sometimes this feature can be pretty useful:

postgres=# do $$             
declare a foo[];
begin
  a := array(select foo from foo);
  raise notice '%', a;
end;
$$;
NOTICE:  {"(10,20)","(30,40)"}
DO

If you don't know this trick, or if you prefer exact column specification, you can use row constructor:

postgres=# do $$
declare a foo[];
begin
  a := array(select row(x.a,x.b) from foo x);
  raise notice '%', a;
end;
$$;
NOTICE:  {"(10,20)","(30,40)"}
DO

In both cases the result is an array of composite values. Attention - the content is hold only in memory, and you can exhaust lot of memory (although the size is limited to 1GB). On second hand, it is very practical replacement of temporary tables or table's variables. Creating and dropping temporary variable is relatively slow and expensive, and when the content is not too big, and when you don't need full functionality of tables (indexes, statistics, session related life-cycle), then composite arrays can be very good replacement (perfect) of temporary tables.