I know it can be done pretty easily with Postgresql

Upon seeing this question in stackoverflow:

I want to make a SELECT and bring the info from a column and his values. I know that I need to access *information_schema* then I need to make another SELECT inside with the *column_name* in the specific row to acess that value.
SELECT column_name,
DATA_TYPE,
CHARACTER_MAXIMUM_LENGTH,
(? ... SELECT COLUMN_NAME FROM MYTABLE ... ?)
FROM information_schema.columns
WHERE table_name = 'MYTABLE'
ORDER BY ordinal_position

How to change the COLUMN_NAME with the column in the row to get the result?

I quickly discern that it can done pretty easily with Postgres, and sans the brittle string manipulation to boot.


To cut to the chase, here's the code:

create table beatles(firstname text,middlename text,lastname text, age int);

insert into beatles(firstname,middlename,lastname,age)
values('John','Winston','Lennon',40);



select c.column_name, x.arr[c.ordinal_position]
from information_schema.columns c
cross join ( select avals (hstore(b)) as arr from (select * from beatles) as b  ) x
where c.table_schema = 'public' and c.table_name = 'beatles'
order by c.ordinal_position

Output:

| COLUMN_NAME |     ARR |
-------------------------
|   firstname |    John |
|  middlename | Winston |
|    lastname |  Lennon |
|         age |      40 |    

Live demo: http://www.sqlfiddle.com/#!1/dea17/1