How Do You Parse an Array Column in Postgresql Using libpq on Linux

Question

For basic data types I have no problems parsing data using a format like:

  if (FALSE == PQgetisnull(res, rowNumber, columnIndex))
  {
     retVal.m_assignedSocket = atol(PQgetvalue(res, rowNumber, columnIndex));
  } else
  {
     retVal.m_assignedSocket = 0;
  }

I would like to have an array field in one of my tables that is an array of bigints. I believe I can insert the data like this:

  // array elements are separated by curly braces
  insertQuery += "{";

  for (size_t i = 0; i < settings.m_stepVector.size(); i++)
  {
     if (snprintf(buffer, sizeof(buffer), ", %llu", settings.m_stepVector[i]) > (int) sizeof(buffer))
     {
        LOG_SYSTEM_WARNING("Unexpected string truncation\n");
     }
     insertQuery += buffer;
  }

  insertQuery += "}";

The problem comes with parsing the row. I have not found any examples or documentation stating the format of the returned array column. My assumption is that it is a string with curly braces on each end. What is the format between the curly braces? It doesn't seem like this is something new but I cannot find an answer anywhere. Could be I don't know the right keywords to search under. I tried postgresql, array, PQgetvalue, and parsing.

I have seen two libraries posted online, libpqxx and libpqtypes, that may offer a solution. I am not familiar with either. The original code base was written in c but new code being added is c++. — mojosound
– mojosound, Commented Jan 10, 2020 at 19:22
Did some experiments with libqpxx. I see that I can cast the field as an array but that puts it into an array parser object. It looks like the array is iterated over using a get_next method. The value will be a string that will need to be converted to the expected numeric data type I would assume. Not sure if the as<ULONG64>() will work here or if I need to use atoll(). — mojosound
– mojosound, Commented Jan 11, 2020 at 0:47

mojosound · Accepted Answer · 2020-01-14 18:45:07Z

2

Final experiments show this to be working:

  retVal.m_rules.clear();
  pqxx::array_parser parser = result[columnIndex].as_array();
  arrayObject obj = parser.get_next();
  while (obj.first != pqxx::array_parser::done)
  {
     if (obj.first == pqxx::array_parser::string_value)
     {
        // arrays return a string value so atoll needed to convert to number
        retVal.m_rules.push_back(atoll(obj.second.c_str()));
     }
     obj = parser.get_next();
  }
  columnIndex++;

Result is defined as pqxx::result::iterator &result.

answered Jan 14, 2020 at 18:45

mojosound

917 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How Do You Parse an Array Column in Postgresql Using libpq on Linux

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related