PostgreSQL – Convert A String Into A Table

Recently at work we had the need to convert a string parameter (passed into an iReport) into a table. For many people, the first thing that may come to mind is either the STRING_TO_ARRAY(...) function combined with the UNNEST(...) function. The biggest issue with using just those two functions is the fact that you don’t have any way of allowing whatever delimiters that will be used for cells and rows to remain. For this reason, I created the following PL/pgSQL function which uses the commas as cell delimiters and semicolons as row delimiters:

CREATE OR REPLACE FUNCTION public.uri_decode_2d_array(IN input TEXT)
  RETURNS SETOF TEXT[]
  LANGUAGE plpgsql
  STABLE
AS $function$
/*******************************************************************************
 * Function Name: uri_decode_2d_array
 * In-coming Params:
 *   input [TEXT] - The string to decoded and convert into a set of text arrays.
 * Returns:
 *      TEXT
 * Description:
 *   Takes in a string and converts it to a set of arrays.
 * Created On: 2012-06-14
 * Updated On: 2012-06-14
 * Author: Chris West
 ******************************************************************************/
BEGIN
  RETURN QUERY
    SELECT array_agg(t)
    FROM (
      SELECT
        REPLACE(REPLACE(REPLACE(UNNEST(a), '%2C', ','), '%3B', ';'), '%25', '%') AS t,
        ROW_NUMBER() OVER (ORDER BY 1) AS r
      FROM (
        SELECT STRING_TO_ARRAY(UNNEST(STRING_TO_ARRAY(input, ';')), ',') a
      ) t
    ) t
    GROUP BY r
    ORDER BY r;
END;
$function$

Now the question is, how do you convert your 2D array into a string that will be interpreted by the above SQL function? The following PHP function can do just that:

function uriEncode2DArray($inputArray) {
  $ret = "";
  foreach($inputArray as $kOuter => $vOuter) {
    if($kOuter) {
      $ret .= ";";
    }
    foreach($vOuter as $kInner => $vInner) {
      $ret .= ($kInner ? "," : "") . cryptThis($vInner);
    }
  }
  return $ret;
}

The following is an example of converting a 2D array into a string using the above function:

$arr = array(
  array("Jen Harring", "001238192", "January 1, 1987"),
  array("Tim Alekper", "902340340", "June 30, 1987", "Mister 99%; AKA Vampire")
);
echo uriEncode2DArray($arr);
# The above outputs the following:
#   Jen Harring,001238192,January 1%2C 1987;Tim Alekper,902340340,June 30%2C 1987,Mister 99%25%3B AKA Vampire

The following is an example of how you would use the generated string to generate a table:

SELECT a[1]::VARCHAR AS name,
  REGEXP_REPLACE(a[2]::VARCHAR, '(...)(..)(....)', '\\1-\\2-\\3') AS ssn,
  a[3]::DATE AS date_of_birth,
  age(a[3]::TIMESTAMP) AS age,
  a[4]::TEXT AS comments
FROM uri_decode_2d_array('Jen Harring,001238192,January 1%2C 1987;Tim Alekper,902340340,June 30%2C 1987,Mister 99%25%3B AKA Vampire') AS a;

The following is the generated table: