Saturday, July 28, 2012

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

Friday, July 13, 2012

Using Postgresql, treat columns as dictionary

Why is Postgresql so lovely?

CREATE EXTENSION hstore;

select (each(hstore(r))).* from (select * from pg_tables limit 1)  as r;

select (each(hstore(r))).key, (each(hstore(r))).value from (select * from pg_tables limit 1)  as r;

select z.key, z.value from ( select (each(hstore(r))).*  from (select * from pg_tables limit 1)  as r ) as z;

Nuff said

Related answer: http://stackoverflow.com/questions/11469059/postgresql-return-entire-row-as-array/11472794#11472794