pg_query result contains strings instead of integer, numeric

When I get data from a postgresql database with pg_query,pg_fetch_assoc all numeric fields get returned as strings. Is there a way around this or do I have to typecast every single field the way I need it to be?

Solution 1:

That's what PHP does. From the manual:

Each value in the array is represented as a string.

Solution 2:

Maybe a bit off-topic -- let me know and i will drop my answer -- but still, this is one of the reasons Pomm project went out: because by default, booleans are returned as string 't' and 'f' (true and false).

This is very inconvenient and it keeps away developers from using complex and useful structures like arrays, ranges, geometric types and so on.

Pomm's Foundation package uses the pg_field_type function to trigger a converter on results and parameters:

select
    array[1, 2, 3]::int4[] as some_ints,
    now() as a_date,
    point(1,2) as a_point,
    true as a_bool
    ;

Doing this query using either PDO or native PHP's Postgres library does return an array of string like the following:

some_ints │            a_date             │ a_point │ a_bool
{1,2,3}   │ 2015-07-31 07:35:36.143182+00 │ (1,2)   │ t

Doing this query using Pomm's Foundation does return an iterator with a single array result:

[
    "some_ints" => [
        1,
        2,
        3
    ],
    "a_date"    => <DateTime> {
        date: "2015-07-31 07:45:21.438447",
        timezone_type: 1,
        timezone: "+00:00"
    },
    "a_point"   => <PommProject\Foundation\Converter\Type\Point> {
        x: 1.0,
        y: 2.0
    },
    "a_bool"    => true
]

The converter system also works for query parameters:

$sql = <<<SQL
select
    2 = ANY($*::int4[]) as contain_two,
    now() > $*::timestamptz as is_past,
    circle(point(0,0), 5) @> $*::point as is_in_circle
    ;
SQL;
$res = $pomm
   ->getDefaultSession()
   ->getQueryManager()
   ->query($sql, [
       [1,2,3],
       new \DateTime('yesterday'),
       new \PommProject\Foundation\Converter\Type\Point('(3,3)')
   ])
   ->current()
   ;

This returns:

[
    "contain_two"  => true,
    "is_past"      => true,
    "is_in_circle" => true
]

It is even possible to create custom converters and declare how composite (row) types may be converted. A more detailed article about it here.