PostgreSQL: How to access column on anonymous record

Question

I have a problem that I'm working on. Below is a simplified query to show the problem:

WITH the_table AS (
    SELECT a, b 
    FROM (VALUES('data1', 2), ('data3', 4), ('data5', 6)) x (a, b)
), my_data AS (
    SELECT 'data7' AS c, array_agg(ROW(a, b)) AS d
    FROM the_table
)
SELECT c, d[array_upper(d, 1)]
FROM my_data

In the my data section, you'll notice that I'm creating an array from multiple rows, and the array is returned in one row with other data. This array needs to contain the information for both a and b, and keep two values linked together. What would seem to make sense would be to use an anonymous row or record (I want to avoid actually creating a composite type).

This all works well until I need to start pulling data back out. In the above instance, I need to access the last entry in the array, which is done easily by using array_upper, but then I need to access the value in what used to be the b column, which I cannot figure out how to do.

Essentially, right now the above query is returning:

"data7";"(data5,6)"

And I need to return

"data7";6

How can I do this?

NOTE: While in the above example I'm using text and integers as the types for my data, they are not the actual final types, but are rather used to simplify the example.

NOTE: This is using PostgreSQL 9.2

EDIT: For clarification, Something like SELECT 'data7', 6 is not what I'm after. Imagine that the_table is actually pulling from database tables and not the WITH statement the I put in for convenience, and I don't readily know what data is in the table.

In other words, I want to be able to do something like this:

SELECT c, (d[array_upper(d, 1)]).b
FROM my_data

And get this back:

"data7";6

Essentially, once I've put something into an anonymous record by using the row() function, how do I get it back out? How do I split up the 'data5' part and the 6 part so that they don't both return in one column?

For another example:

SELECT ROW('data5', 6)

makes 'data5' and 6 return in one column. How do I take that one column and break it back into the original two?

I hope that clarifies

Answer 1

If you can install the hstore extension:

with the_table as (
    select a, b
    from (values('data1', 2), ('data3', 4), ('data5', 6)) x (a, b)
), my_data as (
    select 'data7' as c, array_agg(row(a, b)) as d
    from the_table
)
select c, (avals(hstore(d[array_upper(d, 1)])))[2]
from my_data
;
   c   | avals 
-------+-------
 data7 | 6

Answer 2

This is just a very quick throw together around a similarish problem - not an answer to your question. This appears to be one direction towards identifying columns.

with x as (select 1 a, 2  b union all values (1,2),(1,2),(1,2))
select a from x;