That's not an associative array, it's just an array of a composite type. The hstore type is probably the closest you'd get to an associate array in PostgreSQL, but the keys and values are always strings.
No, a table (possibly a temp table with the results of a particular query) with an appropriately defined primary key (or other unique index) would be the closest you can get to an associative array -- since a relation with n candidate keys is exactly identical to n different associative arrays with the same data but different key/value splits.
But an array of a composite type drawn in such a way that some set of columns is guaranteed unique is pretty much the same thing from a data perspective, even though you may need to do work on the client side to load it into a structure that supports associative array operations (e.g., efficient key/value lookups).
The "associative" in associative array implies association is a property of the data structure, not the way you happen to be using it.
CREATE OR REPLACE FUNCTION get_people()
RETURNS person[] LANGUAGE sql AS
person is a composite type, defined further up. The square brackets indicate an array of that type.
I.E. Things categorized by tags or groups where 1-to-n relationships are necessary. Of course, the extreme of this is EAV (Entity Attribute Value) which I would limit to meta/taxonomy data for performance reasons.
But for information where the labels (or quantity) aren't known before input are easier to deal with in normalized databases.
E.G. Getting metadata on a specific entry with no prior knowlege of said data except parent id:
Or if you're using Postgres, you can return the metadata as an associative array. E.G. http://stackoverflow.com/a/11942726The following is an excerpt from the actual schema I used on a very simple forum that ran on SQLite for years before switching to Postgres fairly recently. The normalization (varying forms) afforded a lot of flexibility. Maybe someone will find it useful.