On 4th of January 2024, Tom Lane committed patch:

In plpgsql, allow %TYPE and %ROWTYPE to be followed by array decoration.
 
This provides the useful ability to declare a variable that is an array
of the type of some other variable or some table column.
 
Quan Zongliang, Pavel Stehule
 
Discussion: https://postgr.es/m/ec4523e1-9e7e-f3ef-f9ce-bafd680ad6f6@yeah.net

This is pretty cool, though, I guess, will not see wide use.

When writing PL/pgSQL functions/procedures/blocks one can use variables. Each variable has type. For example:

use_hash text;
all_hashes text[];

Defines variable named use_hash with datatype of text, and another variable, named all_hashes which is array of texts ([] means array).

On top of it, you can use pseudotypes %TYPE and %ROWTYPE, like this:

use_hash users.password%TYPE;
user_row users%ROWTYPE;

In here we again declare two variables, but their types are evaluated based on what exists in the db. Specifically use_hash will have the same type as column password in table users, and user_row will be typed to record with the same structure as users table.

The thing is that up until 4th of January, we couldn't easily define variables as arrays of %TYPE or %ROWTYPE. Now we can:

DO $$
DECLARE
    v_all_db_names pg_database.datname%TYPE[];
    v_all_dbs pg_database%ROWTYPE[];
BEGIN
    SELECT array_agg(datname) INTO v_all_db_names FROM pg_database;
    SELECT array_agg(d) INTO v_all_dbs FROM pg_database d;
    raise notice 'All db names = %', v_all_db_names;
    raise notice 'All dbs = %', v_all_dbs;
END;
$$;
NOTICE:  ALL db names = {postgres,template1,template0,pgdba,depesz}
NOTICE:  ALL dbs = {"(5,postgres,10,6,c,f,t,f,-1,728,1,1663,en_US.UTF-8,en_US.UTF-8,,,2.37,)","(1,template1,10,6,c,t,t,f,-1,728,1,1663,en_US.UTF-8,en_US.UTF-8,,,2.37,\"{=c/pgdba,pgdba=CTc/pgdba}\")","(4,template0,10,6,c,t,f,f,-1,728,1,1663,en_US.UTF-8,en_US.UTF-8,,,,\"{=c/pgdba,pgdba=CTc/pgdba}\")","(16462,pgdba,10,6,c,f,t,f,-1,728,1,1663,en_US.UTF-8,en_US.UTF-8,,,2.37,)","(16473,depesz,16384,6,c,f,t,f,-1,728,1,1663,en_US.UTF-8,en_US.UTF-8,,,2.37,)"}
DO

Pretty sweet. Allows for easy and nice storing recordsets in a variable, so that it can be later on used repeatedly (we could do that earlier, but in less nice way).

Thanks to all involved 🙂