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

Postgresql – How to initialize a rowtype array in pl/pgsql?

I need to declare an array of records that will be inserted into some table then. But i cannot find a way to initialize this array. Like, for table like this:

create table mytable
(
  id serial,
  value varchar
);

I need something like:

do
$$
    declare 
        rowsForTable mytable%rowtype[] := array [
           ( id=1, value='val 1' ),
           ( id=2, value='val 2' ),
           ( id=3 )
    ];

How could I achieve it?
Thank you!

Tags:

2

Answers


  1. 0

    Using the array and ROW constructors in PostgreSQL, you can initialise an array of composite types (records) for insertion into a table. While you cannot initialise fields directly, as in your example, you can initialise each field separately using the ROW constructor. This is how:

    DO $$ 
DECLARE
    rowsForTable mytable%rowtype[];
BEGIN
    rowsForTable := array[
        ROW(1, 'val 1'::varchar),
        ROW(2, 'val 2'::varchar),
        ROW(3, NULL::varchar)
    ];

    -- Loop through the array and insert values into the table
    FOREACH row IN ARRAY rowsForTable
    LOOP
        INSERT INTO mytable (id, value) VALUES (row.id, row.value);
    END LOOP;
END $$;

    This method utilises a loop to put values into the table after initialising each field using the ROW constructor. Due to the loop, it’s crucial to keep in mind that this approach works best for smaller datasets. For larger inserts, a more effective strategy like a single INSERT INTO line with a VALUES clause could be preferable.

    Login or Signup to reply.
  2. 0

    Use just mytable[] for rowsForTable datatype, not mytable%rowtype[]. Use positional row literals w/o names to initialize the array.

    -- setup
create table mytable
(
  id serial,
  value text
);

-- demo
do 
$$
declare 
 rowsForTable mytable[] := array[(1, 'val 1'), (2, 'val 2'), (3, null)];
begin
  insert into mytable select * from unnest (rowsForTable);
end;
$$;

    Please note the null in the third array element.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search