A common development practice in applications written for Oracle is to use a collection data structure in server-side code called an associative array. These are simple key/value structures where the key is either a number or string and the value can be any type including complex types. They are a convenient way to pass around a set of data to different parts of your business logic without needing to query your tables repeatedly.
When considering a migration from Oracle to PostgreSQL, if the associative array is keyed by an integer, converting to a native PostgreSQL array works well. PostgreSQL arrays can fixed or variable sized, can be sparsely populated and can have a value of any valid PostgreSQL type including complex types. The example below shows an array being loaded with the contents of the `users` table and then accessing individual elements within that array.
blog=#
DO
$$
DECLARE
r users%ROWTYPE;
u users[];
BEGIN
FOR r IN SELECT * FROM users
LOOP
u[r.id] = r;
END LOOP;
RAISE NOTICE 'id: %', u[42].id;
RAISE NOTICE 'user_name: %', u[42].user_name;
RAISE NOTICE 'email: %', u[42].email;
END
$$;
NOTICE: id: 42
NOTICE: user_name: mgiovanardi15
NOTICE: email: mgiovanardi15@bizjournals.com
DO
Converting an associative array that is keyed by a string has been more challenging. There are a number of blogs that give various workarounds like using json or hstore, but those are cumbersome and slow. The new pgcollection extension is designed to address this specific case. It adds a new collection data type that is a key value structure with the key being a string. The example below follows the same pattern as the array example above, but using the collection type.
blog=#
DO
$$
DECLARE
r users%ROWTYPE;
c collection('users');
BEGIN
FOR r IN SELECT * FROM users
LOOP
c[r.user_name] = r;
END LOOP;
RAISE NOTICE 'id: %', c['mgiovanardi15'].id;
RAISE NOTICE 'user_name: %', c['mgiovanardi15'].user_name;
RAISE NOTICE 'email: %', c['mgiovanardi15'].email;
END
$$;
NOTICE: id: 42
NOTICE: user_name: mgiovanardi15
NOTICE: email: mgiovanardi15@bizjournals.com
DO
The pgcollection extension is still in beta, so don't run it in production without extensive testing. While testing, if you come across any issues, please open up something on Github.
