PostgreSQL v9.X have real "array of record"?

Question

This query works fine,

 WITH test AS (
   SELECT array_agg(t) as x FROM (
     SELECT 1111 as id, 'aaaaa' as cc  
   ) AS t
 ) SELECT x[1] FROM test;

but, can I access the recod elements? I try SELECT x[1].id; SELECT x[1][1]; ... nothing works.

PS: with Google we see only OLD solutions... The context here is v9.X, no news about "array of record"?

---

I try also

 select x[1] from (select array[row(1,2)] as x) as t;

no solution to access only item 1 or only item 2.

---

A clue that I was unable to follow: postgresql.1045698.n5.nabble.com solve the problem using a CREATE TYPE... Ok, but I need "all in a query" solution. Where the "dynamic typing" of PostgreSQL?? How to CAST or express the type without a CREATE TYPE clause?

Answer 1

At present there does not appear to be any syntax for accessing a record of anonymous type except via the function call syntax or via hstore. That's unfortunate, but not likely to get fixed in a hurry unless someone who really cares comes along to make it happen. There are other priorities.

You have three workaround options:

• CREATE TYPE
• hstore

CREATE TYPE

The issue is with records of anonymous type. So make it not anonymous. Unfortunately this is only possible before it becomes an anonymous record type; you can't currently cast from record to a usertype. So you'd need to do:

CREATE TYPE some_t AS (id integer, cc text);

WITH test AS (
   SELECT array_agg(t::some_t) as x FROM (
     SELECT 1111 as id, 'aaaaa' as cc  
   ) AS t
 ) SELECT x[1].id FROM test;

Note the cast of the subquery output to some_t before aggregation.

I can't say I understand why this cast can't be performed after indexing the array instead.

hstore

As usual, hstore rides to the mostly-rescue with difficult type problems.

regress=> WITH test AS (
       SELECT array_agg(t) as x FROM (
         SELECT 1111 as id,  'aaaaa' as cc  
       ) AS t
     ) SELECT hstore(x[1])->'id' FROM test;
 ?column? 
----------
 1111
(1 row)

You need the hstore extension, and I'm sure it's not efficient, but it works. This builds on the hstore support for creating a hstore from anonymous records that was added to support NEW and OLD in triggers, a past pain-point.

Wrapper function?

Turns out you can't get around it with a simple wrapper function to let you specify the type on the call-site:

regress=> CREATE OR REPLACE FUNCTION identity(record) RETURNS record AS $$
          SELECT $1; 
          $$ LANGUAGE sql IMMUTABLE;
ERROR:  SQL functions cannot have arguments of type record

so you'd have to use a higher-overhead procedural language, at which point you might as well use hstore instead, it'll be faster and easier.

Making it better?

So, this is all a bit ugly. It's not ever likely to be possible to directly index a field from an anonymous record, since it might not exist and its type cannot be deduced. But there's no reason we can't use the type system feature that allows us to return record from a function and specify its type on the caller-side to also do so in casts.

It should be possible to make PostgreSQL support something like:

WITH test AS (
   SELECT array_agg(t) as x FROM (
      SELECT 1111 as id,  'aaaaa' as cc  
   ) AS t
) SELECT (x[1] AS some_t(id integer, cc text)).id  FROM test;

it'd just involve appropriate parser-hacking, and a way to make sure that was never ambiguously parsed in conflict with a column-alias.

Really, even type-inference could be possible if someone was willing to put the work in and convince the team that the rather large amount of query-planner processor time required was worth it. (unlikely).

This is an irritating, but minor, corner in the type system. If you want it to change you will need to make noise on pgsql-general, and accompany that noise with the willingness to do real work to improve the problem. This may involve learning more than you ever wanted to know about the innards of PostgreSQL's type system, learning the fun of "backward compatibility", and having frustrating arguments around and around in circles. Welcome to open source!