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

Anabel

Why do you need to use arrays for what looks like a simple aggregation query? – Indulgent

This is a simplified example to make my question clearer. The full query uses a recursive with statement to gather data (including rows with arrays) and then I use a DISTINCT ON on one column with an ORDER BY on another column to eliminate unneeded data generated from the recursive query. It all works beautifully if, for example, I only need the data from column a, but I need both a and b, and I can't lose their association. If I were using pgsql 9.4 then I'd just have a and b in separate arrays, and then use the multi column unnest that it has to reassociate a and b later. But I'm on 9.2. – Anabel

A page that has a similar question with an answer that doesn't work in this case can be found here: postgresql.org/message-id/… – Anabel

@TrevorYoung: Can you post the real query and some real data? Like this the readers are groping in the dark. Your OP is about rows in an array, your comment above is about an array in a row. What you have posted so far has the rather trivial solution SELECT 'data7', 6; which is obviously not what you are looking for... – Alligator

I'll have to pull out the query next time I'm in work, but the point of the example is to keep it simple. My question quite simply is how do I return a column from inside the object that is created by the row() function? – Anabel

If I were using pgsql 9.4 then I'd just have a and b in separate arrays Why can't you do that in 9.2? – Pinnace

Multi argument unnest is a new feature as of 9.4. Reading on it here: michael.otacoo.com/postgresql-2/… – Anabel

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

Pinnace

This is basically exactly what I'm looking for except for the fact that you have to download an extension to make it work. I don't know, I just think it's kind of stupid that you can build a structure that you can't take apart unless you use an extension. In any case, I'll give you an upvote now, and if no one comes along with a more native solution (without extensions), then I'll accept your answer. – Anabel

Question for you. Do you know how the avals method puts two different data types into the same array? Most info on the internet says you can't have an array with differing types, yet avals seems to allow this. – Anabel

Wait, I found the answer to my own question there. It turns everything to text and uses a text array. It does work for my example with text and integers, but it could become more of a hassle to work with composite types, for example. I'd prefer to find something that maintains the types if at all possible. – Anabel

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;

Thordia