Contourner la limitation de la fonction unnest sous Postgres

Vous vous souvenez quand j'ai parlé de la fonction unnest() pour Postgres (native à 8.4 et aussi implémentée pour les versions antérieures) ? Elle m'est souvent pratique dans mes projets mais comporte une limitation importante : elle ne traite pas les arrays à deux dimensions. Voici comment j'ai réussi à contourner le problème pour transformer un array Postgres en recordset par une requête SQL.

Disons que j'obtiens des données d'une table sous la forme d'un array qui compte à chaque indice un array associatif représentant un enregistrement :

$rs = array(
  array('id' => 10, 'name' => 'Godspeed you black emperor', 'date' => '2003-05-01'),
  array('id' => 20, 'name' => 'Arcade Fire', 'date' => '2010-09-04'),
  array('id' => 30, 'name' => 'Thee Silver Mount Zion', 'date' => '2011-01-12')
);

La fonction unnest() ne permet pas de la traiter correctement les arrays à plusieurs dimensions. J'ai donc écrit une fonction PHP qui permet de convertir un array tel que décrit plus haut en une chaîne de caractères correspondant à la notation d'un array PostgreSQL.

function convertToPgArray($arrayCollection){
   $records = array();
   foreach($arrayCollection as $array){
       foreach($array as $idx => $value){
           $array[$idx] = '"' . $value . '"';
       }
       $records[] = '{' . implode(', ', $array) . '}';
   }
   return '{ ' . implode(',', $records) . ' }';
}

L'appel à la fonction retournera ceci :

$pgArray = convertToPostgresArray($rs);
echo $pgArray;

/*
{
   {"10", "Godspeed you black emperor", "2003-05-01"},
   {"20", "Arcade  Fire", "2010-09-04"},
   {"30", "Thee Silver Mount Zion", "2011-01-12"}
}
*/

Ce qui nous permet par la suite d'utiliser cette valeur comme paramètre de la requête SQL ou d'une fonction Postgres. Le truc est que les éléments de l'array doivent être du même type (tous des integers, des varchars, des dates, etc). C'est exactement pour cette raison que la fonction PHP convertit toutes les valeurs en tant que varchar. Après le traitement, on utilisera un "cast" (avec l'opérateur ::) pour convertir chaque valeur dans son type original.

La structure spécifique de la requête SQL qui convertira la chaîne en enregistrements prend cette forme :

SELECT sub[idx][1]::integer as id, sub[idx][2]::varchar as name, sub[idx][3]::date as important_date
FROM (SELECT 'PLACEHOLDER'::varchar[] as sub) as tmp
CROSS JOIN generate_series(1, array_upper('PLACEHOLDER'::varchar[], 1) ) as idx

J'utilise la fonction generate_series() pour obtenir une liste des indices selon la première dimensions de l'array (autrement dit, combien d'enregistrements) à laquelle je crée une jointure. Dans les espaces réservés où j'ai inscrit "PLACEHOLDER", remplacez-les par la valeur de $pgArray pour construire une requête SQL valide :

$sql = "
SELECT sub[idx][1]::integer as id, sub[idx][2]::varchar as name, sub[idx][3]::date as important_date
FROM (SELECT 'PLACEHOLDER'::varchar[] as sub) as tmp
CROSS JOIN generate_series(1, array_upper('PLACEHOLDER'::varchar[], 1) ) as idx
";

$sqlToExecute = str_replace('PLACEHOLDER', $pgArray, $sql);

echo $sqlToExecute;

/*
SELECT sub[idx][1]::integer as id, sub[idx][2]::varchar as name, sub[idx][3]::date as important_date
FROM (SELECT '{ {"10", "Godspeed you black emperor",  "2003-05-01"},{"20", "Arcade Fire", "2010-09-04"},{"30", "Thee Silver  Mount Zion", "2011-01-12"} }'::varchar[] as sub) as tmp
CROSS JOIN generate_series(1, array_upper('{ {"10", "Godspeed you black  emperor", "2003-05-01"},{"20", "Arcade Fire", "2010-09-04"},{"30", "Thee  Silver Mount Zion", "2011-01-12"} }'::varchar[], 1) ) as idx
*/

Une fois la requête exécutée, vous obtiendrez un jeu d'enregistrements :