C Function returning a row.... - Mailing list pgsql-novice

Hi Again,

  I'm trying to return a "row" from the following function, I'm not sure what I'm missing or doing wrong but when I call it I get this error message:

ERROR:  array value must start with "{" or dimension information

  I've searched through the documentation and on Google but haven't turned anything up, any hints would be much appreciated!

Regards,
Brett

create type __BkpTime as (bt_Start varchar[32], bt_End varchar[32], bt_Elapsed integer, bt_KB bigint);
create function bm_test(CSTRING, CSTRING, CSTRING) returns __BkpTime  as '/home/brettm/BkpTime/bkptime.so', 'bm_test' LANGUAGE C STRICT;

Datum
bm_test(PG_FUNCTION_ARGS)
{
TupleDesc       tupdesc;
TupleTableSlot  *slot;
AttInMetadata   *attinmeta;
int             ret;
char            sSQL[8192];
BTstruct        BkpTime;

  char *szPolicy = PG_GETARG_CSTRING(0);
  char *szStart  = PG_GETARG_CSTRING(1);
  char *szEnd    = PG_GETARG_CSTRING(2);

#ifdef DEBUG
  elog(NOTICE, "szPolicy len [%d]'%s'", strlen(szPolicy), szPolicy);
  elog(NOTICE, "szStart      [%s]", szStart);
  elog(NOTICE, "szEnd        [%s]\n", szEnd);
#endif

  tupdesc = RelationNameGetTupleDesc("__BkpTime");
  attinmeta = TupleDescGetAttInMetadata(tupdesc);
  slot = TupleDescGetSlot(tupdesc);

  /*  Connect to the SPI Manager */
  if ((ret = SPI_connect()) < 0)
    /* internal error */
    elog(ERROR, "bm_test: SPI_connect returned %d", ret);

  snprintf(sSQL, sizeof(sSQL),
           "SELECT bs_PolicyName, bs_KiloBytes, " \
                  "bs_Start, EXTRACT(EPOCH FROM bs_Start) AS bs_StartEpoch, " \
                  "bs_End, EXTRACT(EPOCH FROM bs_End) AS bs_EndEpoch, " \
                  "EXTRACT(EPOCH FROM bs_Elapsed) AS bs_Elapsed " \
             "FROM Backup_Stats " \
            "WHERE bs_PolicyName = '%s' " \
              "AND bs_Start >= '%s' " \
              "AND bs_End <= '%s' " \
         "ORDER BY bs_Start, bs_End",
           szPolicy, szStart, szEnd);

#ifdef DEBUG
  elog(NOTICE, "\n%s\n", sSQL);
#endif

  /* Execute SELECT */
  if((ret = SPI_exec(sSQL, 0)) != SPI_OK_SELECT) {
    /* CASE Error results ?, external function ? */
    elog(ERROR, "SELECT Failed, SP_exec returned %d.", ret);
  }
  SPITupleTable *tuptable = SPI_tuptable;
  SPI_finish();

  BkpTime = BackupTime(tuptable);

#ifdef DEBUG
  elog (NOTICE, "Start [%s] Finish [%s], Seconds Elapsed %d, " \
                "Backed Up %d KB\n", \
        BkpTime.sStart, BkpTime.sEnd, BkpTime.iElapsed, BkpTime.lKB);
#endif

  char       **values;
  char         sTemp[32];
  HeapTuple    tuple;
  Datum        result;

/*
 * Prepare a values array for storage in our slot.
 * This should be an array of C strings which will
 * be processed later by the type input functions.
*/
  values = (char **) palloc(4 * sizeof(char *));
  values[0] = (char *) palloc(32 * sizeof(char));
  values[1] = (char *) palloc(32 * sizeof(char));
  values[2] = (char *) palloc(32 * sizeof(char));
  values[3] = (char *) palloc(32 * sizeof(char));

  snprintf(values[0], 32, "%s", BkpTime.sStart);
  snprintf(values[1], 32, "%s", BkpTime.sEnd);
  snprintf(sTemp, 32, "%d", BkpTime.iElapsed);
  snprintf(values[2], 32, "%s", sTemp);
  snprintf(sTemp, 32, "%d", BkpTime.lKB);
  snprintf(values[3], 32, "%s", sTemp);

  /* build a tuple */
  tuple = BuildTupleFromCStrings(attinmeta, values);

  /* make the tuple into a datum */
  result = TupleGetDatum(slot, tuple);

  /* clean up (this is not really necessary) */
  pfree(values[0]);
  pfree(values[1]);
  pfree(values[2]);
  pfree(values[3]);
  pfree(values);

  PG_RETURN_DATUM(result);
}

Brett Maton
Linux Consultant
RHCE #805007238628267

Anton House
Chantry Street
Andover
SP10 1DE

Mobile: 07765 240365
Tel: 01264 353211 ext 2338

brett.maton@simplyhealth.co.uk
GBSLinuxAdmin@simplyhealth.co.uk (to include Mark Bradley)