Declaring a `RECORD[]` variable gets `variable "..." has pseudo-type record[]` error

Question

I tried to declare the local variable value with RECORD[] type in a DO statement as shown below:

DO $$
DECLARE
  value RECORD[];
BEGIN
END
$$;

But, I got the error below:

ERROR: variable "value" has pseudo-type record[]

I referred to these questions and answers but I could not solve the error:

• postgresql cannot create table with pseudo-type record

• Postgresql: How to work around pseudo-type record errors

Actually, I could declare value with RECORD or INT[] type without error as shown below:

DO $$
DECLARE
  value RECORD;
BEGIN
END
$$;

DO $$
DECLARE
  value INT[];
BEGIN
END
$$;

So, how can I declare value with RECORD[] type?

Answer 1

Like the error message says, record is a pseudo-type. Note how pseudo-types are absent from this list in the manual:

Arrays of any built-in or user-defined base type, enum type, composite type, range type, or domain can be created.

Some pseudo-types don't make sense as element type of an array to begin with. And record[] is just not implemented to be declared in PL/pgSQL. That might change in the future, but don't hold your breath. I don't think it's very useful. And neither does Tom Lane.

I have never had the need for something like record[]. Maybe you can use an array based on a registered row type. But, typically, there is a better way. You would have do disclose what you are trying to achieve.

Answer 2

Let me preface by saying I agree that you shouldn't need it. But can you have it? You sure can:

DO $do$
DECLARE
  var record:=(null,array[row(1,'a',false),
                          row(2,'b',true),
                          row(3,'c') ] );
  rec record;
BEGIN 
FOREACH rec IN ARRAY var.f2 LOOP
    RAISE NOTICE '%', rec.f2::text;
END LOOP; 
END $do$;

Your record[] is now inside the second field of that record in var.

---

If you think about it, a local variable holding an array of records sounds an awful lot like a temp table. You could just as well: (working demo)

DO $do$
DECLARE
  r record;
BEGIN
  create temp table my_records (c1 int, c2 text, c3 boolean);
  insert into my_records values (1,'a',false),
                                (2,'b',true);
  for r in select * from my_records loop
     create table if not exists somewhere as select r.c1, r.c2, r.c3 limit 0;
     insert into somewhere select r.c1, r.c2, r.c3;
  end loop;
END $do$;