Question 337547 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Declaring a `RECORD[]` variable gets `variable "…" has pseudo-type record[]` error

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:

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?

Tags:

2

Answers


  1. 0

    Like the error message says, record is a pseudo-type. You cannot declare an array based on that. 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.

    I have never had the need for something like record[] (which isn’t implemented to begin with). 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.

    Login or Signup to reply.
  2. 0

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

    DO $do$
DECLARE
  value record:=(select (null,array[row(1,'a',false)]));
BEGIN
END
$do$;

    Your record[] is now inside that record, but it’s there. All I can wish you is good luck using that.

    If you think about it, a local variable holding an array of records sounds an awful lot like a temp table. If your aim was to do something like this (pseudo-code):

    DO $do$
DECLARE
  value record[]:=array[row(1,'a',false),
                        row(2,'b',true)];
  r record;
BEGIN
  foreach r in value loop
     insert into somewhere select r.*;
  end loop
END

    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$;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search