I'm using the pageinspect extension to inspect old row versions in Postgres. Here is the query I'm using:
SELECT ctid, tuple_data_split('t0'::regclass, t_data, t_infomask, t_infomask2, t_bits) AS raw_row_data
FROM heap_page_items(get_raw_page('t0',0));
For example, let's say I have a table with two integer columns and I insert (1, 1). I then update this to become (2, 2). The above query will return the following:
ctid | raw_row_data
--------+-------------------------------
(0,1) | {"\\x01000000","\\x01000000"}
(0,2) | {"\\x02000000","\\x02000000"}
I would like to modify the query so that I can get the data in typed format. For example, I would like the query to return:
ctid | typed_row_data
--------+----------------
(0,1) | {1,1}
(0,2) | {2,2}
In this specific case, I could just add a function to convert the bytea to int, however I would like this to work for any given table that may contain data types not known when writing the query. I know that it is possible to manually retrieve the table schema, so it should be possible to use this to select an appropriate conversion function depending on the data type. Is there any existing functionality that can do this? Or alternatively, is there a better way of doing this?