I have a problem I'm working on. The following 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 will notice that I am creating an array of several rows, and the array is returned on the same row as the other data. This array should contain information for both a and b , and store two values associated with each other. It would seem to be useful to use an anonymous string or record (I want to avoid creating a simple type).
All this works well, until I need to start pulling data. In the above instance, I need to access the last record in the array, which is easily done with array_upper , but then I need to access the value in column b , which I cannot figure out how to do this.
Essentially, the following query is being returned right now:
"data7";"(data5,6)"
And I need to come back
"data7";6
How can i do this?
NOTE. Although I use text and integers as types for my data in the above example, they are not actual final types, but rather are used to simplify the example.
NOTE. This is using PostgreSQL 9.2
EDIT: for clarification, something like SELECT 'data7', 6 not what I need. Imagine that the_table is actually pulling out the database tables, not the WITH statement, which I put for convenience, and I don’t understand 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 return this:
"data7";6
In fact, as soon as I put something in an anonymous record using the row () function, how do I return it? How to separate part of 'data5' and part 6 so that they do not return in the same column?
For another example:
SELECT ROW('data5', 6)
does 'data5' and 6 returns in one column. How to take this one column and break it into the original two?
I hope this clarifies