Convert postgresql hstore to php array

Question

Is there a good php code snippet to convert a postgresql hstore to a php array, that will correctly translate an unquoted NULL within the hstore to a php NULL?

EG: Suppose we have the following hstore string:

"k1"=>"v1", "k2"=>NULL, "k3"=>"NULL", "k4"=>"\"v4"
(aka SELECT '"k1"=>"v1","k2"=>NULL,"k3"=>"NULL","k4"=>"\\"v4"'::hstore;)

How can we convert this into the following php array?

array('k1' => 'v1', 'k2' => NULL, 'k3' => 'NULL', 'k4' => '\"v4');

I following the following converter but it does not seem to handle the unquoted NULL: https://github.com/chanmix51/Pomm/blob/master/Pomm/Converter/PgHStore.php

Answer 1

I believe the syntax would be something like this:

$pdo = new PDO( /*connection string*/ );
// h is the hstore column.
$stmt = $pdo->query( "SELECT (each(h)).key, (each(h)).value FROM <table name>" );
$output = array();
foreach( $stmt->fetchAll( PDO::FETCH_NUM ) as $row )
{
   // $row[ 0 ] is the key, $row[ 1 ] is the value.
   $output[ $row[ 0 ] ] = $row[ 1 ];
}

Answer 2

$hstore = '"A"=>"AAA", "B"=>"BBB"';
print_r(json_decode('{' . str_replace('"=>"', '":"', $hstore) . '}', true));

Answer 3

I attempted to make use of Pomm's PgHStore method, however it broke under a half dozen or so different circumstances. I don't remember them all, but here are a few I do recall:

• Lack of native PHP Null support
• Lack of properly escaping double quotes
• Didn't properly escape values for safe PostgreSQL insertion

I ultimately ended up deriving my own solution, coined PHPG. Supports automatic conversion of arrays of any data-type, Hstores, Geometric data-types, Dates / Timestamps, etc: https://github.com/JDBurnZ/PHPG

If you wish to tackle this problem without the use of an external class or library, this is going to be very difficult and will most likely be reliable, and here's why:

One might suggest you could simply explode on "," to create a list of key/value pairs, from which you could explode each element in the list on "=>". This works if all values are strings, but PostgreSQL Hstores support NULL values. NULL values are not encapsulated in double quotes, so the above solution will not work appropriately in these scenarios.

Answer 4

Just in case somebody is still interested in this: since 9.2 there is a function called hstore_to_json that can be used in select clauses to convert the hstore content to JSON. This will look like this:

SELECT id, hstore_to_json(my_hstore_field) AS myField FROM mytable WHERE ...

Then in PHP just use

json_decode($row['myField'])

to decode it to a php array...