PostgreSQL array data type

[pgdude]

What is the official status on arrays. How are they going to be implemented?
Code sample would help I guess. Just curios If it is worth waiting for version 1.3.

[Moderator]

We have finished implementation of the multi-dimensional arrays in the Provider, version 1.3.

Since OLEDB does not support arrays, we have implemented them as VARIANT wrappers around SAFEARRAYs. We decided that an array lower index will be 1 (not 0).

The array elements accessed differently depending on the programming language. For example, let's PostgreSQL array declared as:

Code: Select all

CREATE TABLE test_arrays
(
  "array2D" smallint[][],
  ...
)

// Populate "array2D" as 5x7 matrix
INSERT INTO test_arrays("array2D",...)
VALUES('{{3,16,9,22,15,0,100},{20,8,21,14,2,0,100},{7,25,13,1,19,0,100},{24,12,5,18,6,0,100},{11,4,17,10,23,0,100}}',...)


The following C# sample demonstrates access to the 2D array:

Code: Select all

string sConnectionStr = "Provider=PGNP.1;...";
OleDbConnection conn = new OleDbConnection(sConnectionStr);
conn.Open();

OleDbCommand dbcmd = new OleDbCommand("SELECT \"array2D\" FROM test_arrays", conn);
OleDbDataReader dr = dbcmd.ExecuteReader();

while (dr.Read())
{
    object o = dr.GetValue(0);
    if (o is DBNull)
        Console.WriteLine("[NULL]");
    else
    {
        Array array = o as Array;
        switch (lang_req.Rank)
        {
        case 1:
              for (int i = lang_req.GetLowerBound(0); i < lang_req.GetLowerBound(0) + lang_req.GetLength(0); i++)
              {
                   object x = lang_req.GetValue(i);
                   Console.WriteLine(String.Format("{0}>{1}", i, x));
              }
              break;

        case 2:
            for (int j = lang_req.GetLowerBound(1); j < lang_req.GetLowerBound(1) + lang_req.GetLength(1); j++)
            {
                for (int i = lang_req.GetLowerBound(0); i < lang_req.GetLowerBound(0) + lang_req.GetLength(0); i++)
                {
                   object x = lang_req.GetValue(i, j);
                   Console.WriteLine(String.Format("({0},{1}) > {2}", i, j, x));
                }
            }
            break;

        case 3:
            for (int k = lang_req.GetLowerBound(2); k < lang_req.GetLowerBound(2) + lang_req.GetLength(2); k++)
            {
                 for (int j = lang_req.GetLowerBound(1); j < lang_req.GetLowerBound(1) + lang_req.GetLength(1); j++)
                 {
                     for (int i = lang_req.GetLowerBound(0); i < lang_req.GetLowerBound(0) + lang_req.GetLength(0); i++)
                     {
                         object x = lang_req.GetValue(i, j, k);
                         Console.WriteLine(String.Format("({0},{1},{2}) > {3}", i, j, k, x));
                     }
                 }
             }
             break;
        }
    }
}

If you compile and execute the C# sample you will notice that indexes are:

Code: Select all

(1,1) > 3
(2,1) > 16
(3,1) > 9
(4,1) > 22
(5,1) > 15
(6,1) > 0
(7,1) > 100
(1,2) > 20
(2,2) > 8
(3,2) > 21
(4,2) > 14
(5,2) > 2
(6,2) > 0
(7,2) > 100
(1,3) > 7
(2,3) > 25
(3,3) > 13
(4,3) > 1
(5,3) > 19
(6,3) > 0
(7,3) > 100
(1,4) > 24
(2,4) > 12
(3,4) > 5
(4,4) > 18
(5,4) > 6
(6,4) > 0
(7,4) > 100
(1,5) > 11
(2,5) > 4
(3,5) > 17
(4,5) > 10
(5,5) > 23
(6,5) > 0
(7,5) > 100

That corresponds to C# array:

Code: Select all

short array2D[7,5];      // it can be confusing because PostgreSQL array is 5x7!

However, C++ allows to access the array elements similarly to PostgreSQL declaration.

[pgdude]

Thank you for the C# sample. Would you have a C++ code handy?

[Moderator]

C++ sample demonstrating multidimentional arrays access via PGNP provider

Code: Select all

#include <stdio.h>
#include <tchar.h>
#include <iostream>
#include <string>
using namespace std;

#import "msado15.dll" no_namespace rename("EOF", "adoEOF")

void MyArrayPrint(const _variant_t& v);

int _tmain(int argc, _TCHAR* argv[])
{
   CoInitialize (NULL);

   _ConnectionPtr pConn;

   try   
   {
      HRESULT hr = pConn.CreateInstance (__uuidof (Connection));
      if (FAILED (hr))
      {
         cout << "Can't create Connection instance" << endl;
         return 1;
      }
      hr = pConn->Open (_bstr_t ("Provider=PGNP.1;Password=12345;User ID=postgres;Initial Catalog=postgres;Data Source=localhost;Extended Properties=\"\""), _bstr_t (""), _bstr_t (""), adModeUnknown);
      if (FAILED(hr))
      {
         cout << "Can't open datasource" << endl;
         return 2;
      }

      _CommandPtr pCommand;

      hr = pCommand.CreateInstance (__uuidof (Command));
      if (FAILED (hr))
      {
         cout << "Can't create an instance of Command" << endl;
         return 3;
      }

      pCommand->ActiveConnection = pConn;
      pCommand->CommandText = "select \"array2D\"from test_arrays";


      _RecordsetPtr pRecordset;

      hr = pRecordset.CreateInstance (__uuidof (Recordset));
      if (FAILED (hr))
      {
         cout << "Can't create an instance of Recordset" << endl;
         return 4;
      }

      pRecordset->CursorLocation = adUseClient;
      pRecordset->Open ((IDispatch *) pCommand, vtMissing, adOpenForwardOnly, adLockUnspecified, adCmdUnknown);

      if (!pRecordset->GetadoEOF ())
      {
         _variant_t v = pRecordset->Fields->GetItem("array2D")->GetValue();

         MyArrayPrint(v);
      }

      pRecordset->Close ();
   }
   catch ( _com_error &e )
   {
      cout << e.Description() << endl;
   }
   catch (...)
   {
      cout << "*** Unhandled Exception ***" << endl;
   }

   if ((pConn->State & adStateOpen) == adStateOpen)
      pConn->Close();

   CoUninitialize();

   return 0;
}

void PrintArrayElement(VARTYPE vt, LPVOID pvData, LONG i)
{
   switch (vt)
   {
   default: cout << "?unknown vartype"; break;

   case VT_I2: case VT_UI2:   cout << ((short*)pvData)[i]; break;

   case VT_I4: case VT_UI4:   cout << ((int*)pvData)[i]; break;

   case VT_BSTR: { _bstr_t bstrTemp(((BSTR*)pvData)[i], true); cout << LPCSTR(bstrTemp); } break;
   }
}

void MyArrayPrint(const _variant_t& v)
{
   if (0 == (v.vt & VT_ARRAY))
   {
      cout << "Not array, vt=" << v.vt;
      return;
   }

   VARTYPE vt = v.vt & ~VT_ARRAY;   // element type

   switch (v.parray->cDims)
   {
      case 1:
         for (LONG i = v.parray->rgsabound[0].lLbound; i < v.parray->rgsabound[0].lLbound + v.parray->rgsabound[0].cElements; i++)
         {
            cout << i << ">";
            PrintArrayElement(vt, v.parray->pvData, i-v.parray->rgsabound[0].lLbound);   // the implementation requires zero-based indexes
            cout << endl;
         }
         break;

      case 2:
         for (LONG i = v.parray->rgsabound[0].lLbound; i < v.parray->rgsabound[0].lLbound + v.parray->rgsabound[0].cElements; i++)
         {
            for (LONG j = v.parray->rgsabound[1].lLbound; j < v.parray->rgsabound[1].lLbound + v.parray->rgsabound[1].cElements; j++)
            {
               cout << "(" << i << "," << j << ")>";
               PrintArrayElement(vt, v.parray->pvData,
                  (i-v.parray->rgsabound[0].lLbound) * v.parray->rgsabound[1].cElements +
                  (j-v.parray->rgsabound[1].lLbound));   // the implementation requires zero-based indexes
               cout << endl;
            }
         }
         break;

      case 3:
         for (LONG i = v.parray->rgsabound[0].lLbound; i < v.parray->rgsabound[0].lLbound + v.parray->rgsabound[0].cElements; i++)
         {
            for (LONG j = v.parray->rgsabound[1].lLbound; j < v.parray->rgsabound[1].lLbound + v.parray->rgsabound[1].cElements; j++)
            {
               for (LONG k = v.parray->rgsabound[2].lLbound; k < v.parray->rgsabound[2].lLbound + v.parray->rgsabound[2].cElements; k++)
               {
                  cout << "(" << i << "," << j << "," << k << ")>";
                  PrintArrayElement(vt, v.parray->pvData,
                     ((i-v.parray->rgsabound[0].lLbound) * v.parray->rgsabound[1].cElements +
                      (j-v.parray->rgsabound[1].lLbound)) * v.parray->rgsabound[2].cElements +
                     (k-v.parray->rgsabound[2].lLbound));   // the implementation requires zero-based indexes
                  cout << endl;
               }
            }
         }
         break;
   }
}