Porting Oracle associative arrays to Postgres and wrapping the data access using npgsql

Question

It is great to get prompt replies on the npgsql queries. Thanks to its owner! I am trying to port a sproc that took in array valued parameters to postgres with similar abstraction/semantics. I am wondering how one would shape the pgsql sproc and use npgsql api to call that. I have another layer of application abstraction on top of our dal abstraction. We use data adapters to go to sql server, assoc arrays to oracle and trying to figure out what we could map this to for postgres using npgsql. We have some room in shaping the sproc but still keep the number of input params the same. we could certainly build this sproc much different but we still need it behind the same app api which supplies some set of typed arrays as shown below

public static void Flush2OraWithAssocArrayInsnetworkdatabatch(string dbKey  ,int?[] ENDPOINTID,DateTime?[] INSERTEDDATETIME,int?[] RECORDTYPEID,long?[] RECORDVALUE,int?[] PACKETSIZE)
    { 
         Database db = Helper.GetDatabase(dbKey);
          using (DbConnection con = db.CreateConnection()){
                con.Open();
                using (DbCommand cmd  = con.CreateCommand()){
                    cmd.CommandText =  "Insnetworkdatabatch";
                    Helper.InitializeCommand(cmd, 300, "Insnetworkdatabatch");
                    BuildInsnetworkdatabatchOracleAssocArrayCommandParameters(cmd ,ENDPOINTID,INSERTEDDATETIME,RECORDTYPEID,RECORDVALUE,PACKETSIZE);
                    try {
                        Helper.ExecuteNonQuery(cmd, cmd.CommandText);
                        con.Close();
                    } catch (DALException ) {
                        throw;
                    } 
             }
        }
    }

I have a oracle sproc written as follows

create or replace PROCEDURE InsNetworkDataBatch2
  (
    -- Add the parameters for the stored procedure here
    v_endPointID       IN arrays.t_number ,
    v_insertedDateTime IN arrays.t_date ,
    v_recordTypeID     IN arrays.t_number ,
    v_recordValue      IN arrays.t_number ,
    v_packetSize       IN arrays.t_number )
AS
BEGIN
  DECLARE 
  BEGIN
    FORALL i IN v_endpointID.FIRST..v_endpointID.LAST SAVE EXCEPTIONS
      INSERT
      INTO STGNETWORKSTATS
        (
          INSERTEDDATE,
          ENDPOINTID,
          RECORDTYPEID,
          RECORDVALUE,
          PACKETSIZE
        )
        VALUES
        (
          v_insertedDateTime(i),
          v_endPointID(i),
          v_recordTypeID(i),
          v_recordValue(i),
          v_packetSize(i)
        );

  END;
END;
-- END PL/SQL BLOCK (do not remove this line) ----------------------------------

Here is the assoc array package in oracle

  create or replace PACKAGE Arrays AS

 type t_number is table of number index by binary_integer;
 type t_date is table of date index by binary_integer; 
END Arrays; 

Here is how we build the oracle parm and wondering what its equivalency if at all possible in postgres and trying to see how npgsql will support it

   public override void CreateAssociativeArrayParameter(DbCommand cmd, string parameterName, object parameterValue, string dbType, ParameterDirection direction)
    {
        OracleDbType oracleDbType = dbSpecificTypesMap[dbType];
        OracleParameter param = new OracleParameter(parameterName, oracleDbType, direction);
        param.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
        param.Value = parameterValue;
        cmd.Parameters.Add(param);
    }

Answer 1

I don't know anything about Oracle arrays or associative arrays. However, PostgreSQL has a rich support for complex types. PostgreSQL arrays are a good way to store an array of values in a column, and PostgreSQL even provides indexing and database-side functions to work with arrays.

If you're looking for a dictionary type (associative array?), take a look at hstore or json.

EDITED: If your associative array has a fixed schema (i.e. the fields don't change), you can also consider PostgreSQL composite.

Answer 2

Here is an attempt with Postgres stored procedure. This is now working. I got around some casting issues thrown from inside the npgsql which was a result of my .net type not being compatible with the sproc parameter data type in postgres.

Here is how i am trying to add the param value

create or replace FUNCTION InsNetworkDataBatch
  (
    -- Add the parameters for the stored procedure here
    v_endPointID       IN int[] ,
    v_insertedDateTime IN timestamp[] ,
    v_recordTypeID     IN int[] ,
    v_recordValue      IN bigint[] ,
    v_packetSize       IN int[] ) RETURNS void
      LANGUAGE 'plpgsql'

AS $$
BEGIN
  DECLARE 
  BEGIN

 FOR i IN array_lower(v_endPointID, 1) .. array_upper(v_endPointID, 1)
    loop 
     INSERT INTO STGNETWORKSTATS
        (
          INSERTEDDATE,
          ENDPOINTID,
          RECORDTYPEID,
          RECORDVALUE,
          PACKETSIZE
        )
        VALUES
        (
          v_insertedDateTime[i],
          v_endPointID[i],
          v_recordTypeID[i],
          v_recordValue[i],
          v_packetSize[i]
        ); 
   end loop; 

  END; 
END;
$$

Here is how i am trying to bind the app to the command params

public override void CreateAssociativeArrayParameter(DbCommand cmd, string parameterName, object parameterValue, string dbType, ParameterDirection direction)
        {
            NpgsqlDbType npgsqlDbType;
            if (dbSpecificTypesMap.ContainsKey(dbType))
            {
                npgsqlDbType = dbSpecificTypesMap[dbType];
            }
            else
            {
                throw new ApplicationException($"The db type {dbType} could not be parsed into the target NpgsqlDbType. Please check the underlying type of the parameter");
            }
            NpgsqlParameter param = new NpgsqlParameter(parameterName.ToLower(),  NpgsqlDbType.Array | npgsqlDbType); 
            param.Value = parameterValue;
            cmd.Parameters.Add(param);
        }