Is there anyway to tell the datatype in a postgres array?

Question

I am using a Postgres 9.4 database and have PHP as my front end.

A general query I may run would look like this:

PHP :

$query = "select * from some_table";
pg_prepare($connection,"some_query",$query);
$result = pg_execute($connection,"some_query",array());

while ($row = pg_fetch_array($result,null,PGSQL_ASSOC)) {
    echo $row['some_field'];
    echo $row['some_field_1'];
    echo $row['some_field_2'];
}

I am running into a front-end that requires to know the datatype of the column that spits out - specifically I need to know when the echo'd database field is a timestamp column.

Obviously I can tell integers and string, however timestamp is a bit of a different thing.

I suppose I could see if strtotime() returns false, however that seems a little dirty to me.

So my question is:

Is there a PHP built-in function that can return a multi-dimensional array of the database row with not only $key=>$value pair but also the datatype?

Any help on this would be appreciated - thank you!

Answer 1

You can query from information_schema.columns and fetch just like any other query:

SELECT column_name, data_type
FROM information_schema.columns
WHERE table_name='some_table'

Or after your query use pg_field_type():

$type = pg_field_type($result, 0);

But you need to know the position of the column in the result so you should (best practice anyway) list the columns. For the above case using 0 would give the type of col1 in the query below::

SELECT col1, col2, col3 FROM some_table