Re: function returning a row - Mailing list pgsql-novice

On Wednesday 15 December 2004 18:05, Michael Fuhr wrote:
> On Wed, Dec 15, 2004 at 07:16:20AM -0800, Joe Conway wrote:
> See also "Returning Sets from C-Language Functions" in the "C-Language
> Functions" section of the "Extending SQL" chapter in the documentation.

Hi again.

I know it is kind of an old thread, but i have some problems with getting this
function to work. When i run it in the database, the database crashes. The
last thing it prints out before it stops is "return next". I have added my
code here, sorry that there is that much, but i didn't find anything that i
could leave out. I don't know if i need to have the tables in the structure,
since it is allocated with multi_call_memory, but i have tried with and
without it in the structure, and it didn't make any difference. Hope someone
can help.

thanks
- Kjetil

typedef struct {
  bool finished;
  int x_value;
  int y_value;
  int min_value;
  int max_x;
  int max_y;
  int **dynamic;
  int **backtracking;
  bool **before;
}set_data;

Datum smith_waterman_set(PG_FUNCTION_ARGS) {
  FuncCallContext *funcctx;
  MemoryContext oldcontext;
  int call_cntr;

  set_data *set;
  int x_value, y_value, max_x, max_y;
  int min_value;
  int **dynamic;
  int **backtracking;
  bool **before;
  char *in;
  char *sml;
  char g[2]="-";

  if(SRF_IS_FIRSTCALL()) {
    funcctx = SRF_FIRSTCALL_INIT();
    oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx);

    set = (set_data *) palloc(sizeof(*set));

    text *intext = PG_GETARG_TEXT_P(0);
    text *smltext = PG_GETARG_TEXT_P(1);
    in = (char*)palloc(VARSIZE(intext)-VARHDRSZ+1);
    sml = (char*)palloc(VARSIZE(smltext)-VARHDRSZ+1);
    snprintf (in, VARSIZE(intext) - VARHDRSZ + 1, VARDATA(intext));
    snprintf (sml, VARSIZE(smltext) - VARHDRSZ + 1, VARDATA(smltext));

    dynamic = (int **) palloc((1+strlen(in))* sizeof(int));
    backtracking = (int **) palloc((1+strlen(in)) *sizeof(int));
    before = (bool **) palloc((1+strlen(in))*sizeof(bool));

    if(dynamic == NULL) {
      printf("\n failed to allocate memory for dynamic");
      ereport(ERROR, (errcode(ERRCODE_SYNTAX_ERROR),
              errmsg("failed to allocate memory for dynamic")));
    }

    int i, j, diag, up, left, max, s, maxi, maxj;
    int maxvalue = -1;
    for(i=0; i<strlen(in)+1; i++) {
      dynamic[i] = palloc((1+strlen(sml))* sizeof(int));
      backtracking[i] = palloc((1+strlen(sml))*sizeof(int));
      before[i] = palloc((1+strlen(sml))*sizeof(bool));
      if(dynamic[i] == NULL) {
    printf("\nfailed to allocate for dynamic[%d]", i);
    ereport(ERROR,
        (errcode(ERRCODE_SYNTAX_ERROR),
         errmsg("failed to allocate memory for dynamic[%d]", i)));
      }
      if(backtracking[i] == NULL) {
    printf("\nfailed to allocate for backtracking[%d]", i);
    ereport(ERROR,
        (errcode(ERRCODE_SYNTAX_ERROR),
         errmsg("failed to allocate memory for backtracking[%d]", i)));
      }
    }

    //sets up the dynamic programing table,
    //all values are zero since it is local similarity
    for(i=0; i<strlen(in)+1; i++) {
      for(j=0; j<strlen(sml)+1; j++) {
    dynamic[i][j] = 0;
    backtracking[i][j] = 0;
      }
    }

    for(i=0; i<strlen(in); i++) {
      for(j=0; j<strlen(sml); j++) {
    s = score(in+i, sml+j);

    diag = dynamic[i][j] + s;
    up = dynamic[i][j+1] + s - gap;
    left = dynamic[i+1][j] + s - gap;

    max = MAX3(diag, up, left);

    if(max <= 0) {
      dynamic[i+1][j+1] = 0;
      backtracking[i+1][j+1] = 0;
    }
    else if(max == diag) {
      dynamic[i+1][j+1] = diag;
      backtracking[i+1][j+1] = 1;
    }
    else if(max == up) {
      dynamic[i+1][j+1] = up;
      backtracking[i+1][j+1] = 2;
    }
    else if(max == left) {
      dynamic[i+1][j+1] = left;
      backtracking[i+1][j+1] = 3;
    }

    if(dynamic[i+1][j+1] > maxvalue) {
      maxvalue = dynamic[i+1][j+1];
      maxi = i+1;
      maxj = j+1;
    }
      }
    }
    set->finished = false;
    set->x_value = strlen(in);
    set->y_value = strlen(sml);
    set->min_value = PG_GETARG_INT32(2);
    set->max_x = strlen(in);
    set->max_y = strlen(sml);
    set->dynamic = dynamic;
    set->backtracking = backtracking;
    set->before = before;

    funcctx->user_fctx = set;
    MemoryContextSwitchTo(oldcontext);
  }

  //stuff done on every call of the function
  funcctx = SRF_PERCALL_SETUP();

  call_cntr = funcctx->call_cntr;
  set = funcctx->user_fctx;
  x_value = set->x_value;
  y_value = set->y_value;
  min_value = set->min_value;
  max_x = set->max_x;
  max_y = set->max_y;
  dynamic = set->dynamic;
  backtracking = set->backtracking;
  before = set->before;

  if(!(set->finished)) {
    alignres *result;

    char *res1 = (char *) palloc((strlen(in)+strlen(sml))*sizeof(char));
    char *res2 = (char *) palloc((strlen(in)+strlen(sml))*sizeof(char));

    //finds the alignment
    int k=0, i=0, j=0, temp_i=0, temp_j=0;
    int lengde=0;
    bool more = false;
    bool first = true;
    bool found = false;
    for(i=x_value; i>0; i--) {
    elog(NOTICE, "found = %d", found);
    if(!found) {
      for(j=max_y; j>0; j--) {
        if(first) {
          j=y_value;
          first = false;
        }
        elog(NOTICE, "dynamic[%d][%d]=%d", i, j, dynamic[i][j]);
        if(dynamic[i][j] >= min_value && before[i][j] == false) {
          before[i][j] = true;
          elog(NOTICE, "more");
          more = true;
          if(j>0) {
        set->x_value = i;
        set->y_value = j-1;
          }
          else if(i>0){
        set->x_value = i-1;
        set->y_value = max_y;
          }
          else
        set->finished = true;
          found = true;
          temp_i = i;
          temp_j = j;
          break;
       }
     }
      }
     else
    break;
    }
    i = temp_i;
    j = temp_j;

    while(more)
      lengde++;
      if(backtracking[i][j]==1) {
    res1[k]=in[i-1];
    res2[k]=sml[j-1];
    i=i-1;
    j=j-1;
    before[i][j] = true;
      }
      else if(backtracking[i][j]==2) {
    res1[k]=in[i-1];
    res2[k]=g[0];
    i=i-1;
    before[i][j] = true;
      }
      else if(backtracking[i][j]==3) {
    res1[k]=g[0];
    res2[k]=sml[j-1];
    j=j-1;
    before[i][j] = true;
      }
      else {
    more = false;
    lengde--;
      }
      k++;
    }
    if(backtracking[i][j] > 0) {
      if(i != 0)
    res1[k]=in[i-1];
      else
    res1[k]=g[0];
      if(j != 0)
    res2[k]=sml[j-1];
      else
    res2[k]=g[0];
      lengde++;
    }

    smst_in = (char*) palloc(1+lengde*sizeof(char));
    smst_sml = (char*) palloc(1+lengde*sizeof(char));

    i = lengde-1;
    j = 0;

    while (*(smst_in+j) = *(res1+i)) {
      j++;
      i--;
    }
    smst_in[lengde] = '\0';

    i = lengde-1;
    j = 0;

    while (*(smst_sml+j) = *(res2+i)) {
      j++;
      i--;
    }
    smst_sml[lengde] = '\0';
    elog(NOTICE, "smst_in=%s, smst_sml=%s", smst_in, smst_sml);

    result = (alignres *)
palloc(sizeof(*result)+strlen(smst_in)+strlen(smst_sml));
    bool first2 = true;
    set->finished = true;
    for(i=x_value; i>0; i--) {
      for(j=max_y; j>0; j--) {
    if(first2) {
      j=y_value;
      first2 = false;
    }
    if(dynamic[i][j] >= min_value && before[i][j] == false) {
      elog(NOTICE, "verdi = %d, setter finished til false", dynamic[i][j]);
      set->x_value = i;
      set->y_value = j;
      set->finished = false;
      break;
    }
      }
    }
    funcctx->user_fctx = set;
    elog(NOTICE, "return next");
    SRF_RETURN_NEXT(funcctx, PointerGetDatum(result));
  }
  else {
    elog(NOTICE, "return done");
    SRF_RETURN_DONE(funcctx);
  }
}