skip to Main Content

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?

2

Answers


  1. 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. 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