plperl spi_exec_query patch - Mailing list pgsql-patches

The following patch applies a change I inadvertantly left out of the
previous patch, and makes spi_exec_query work correctly in the case of a
select query.
Test shows:
CREATE TABLE test (
        i int,
        v varchar
);
CREATE TABLE

INSERT INTO test (i, v) VALUES (1,'first line');
INSERT 25616 1
INSERT INTO test (i, v) VALUES (2,'second line');
INSERT 25617 1
INSERT INTO test (i, v) VALUES (3,'third line');
INSERT 25618 1
INSERT INTO test (i, v) VALUES (4,'immortal');
INSERT 25619 1
create function test_munge() returns setof test language plperl as $$

  my $res = [];
  my $rv = spi_exec_query("select i,v from plperltest.test;");
  my $status = $rv->{status};
  my $rows = @{$rv->{rows}};
  my $processed = $rv->{processed};
  foreach my $rn (0..$rows-1)
  {
    my $row = $rv->{rows}[$rn];
    $row->{i} += 200 if defined($row->{i});
    $row->{v} =~ tr/A-Za-z/a-zA-Z/ if (defined($row->{v}));
    push @$res,$row;
  }
  return $res;
$$;
CREATE FUNCTION
select * from test_munge();
  i  |      v
-----+-------------
 201 | FIRST LINE
 202 | SECOND LINE
 203 | THIRD LINE
 204 | IMMORTAL
(4 rows)

cheers

andrew
Index: spi_internal.c
===================================================================
RCS file: /projects/cvsroot/pgsql-server/src/pl/plperl/spi_internal.c,v
retrieving revision 1.1
diff -c -r1.1 spi_internal.c
*** spi_internal.c    1 Jul 2004 20:50:22 -0000    1.1
--- spi_internal.c    4 Jul 2004 17:48:41 -0000
***************
*** 82,123 ****
          * Get the attributes value
          ************************************************************/
          attdata = SPI_getvalue(tuple, tupdesc, i+1);
!         hv_store(array, attname, strlen(attname), newSVpv(attdata,0), 0);
      }
      return array;
  }

  static HV*
! plperl_spi_execute_fetch_result(SPITupleTable *tuptable, int rows, int status)
  {

      HV *result;
      int i;

      result = newHV();

      if (status == SPI_OK_UTILITY)
      {
          hv_store(result, "status", strlen("status"), newSVpv("SPI_OK_UTILITY",0), 0);
!         hv_store(result, "rows", strlen("rows"), newSViv(rows), 0);
      }
      else if (status != SPI_OK_SELECT)
      {
          hv_store(result, "status", strlen("status"), newSVpv((char*)plperl_spi_status_string(status),0), 0);
!         hv_store(result, "rows", strlen("rows"), newSViv(rows), 0);
      }
      else
      {
!         if (rows)
          {
-             char* key=palloc(sizeof(int));
              HV *row;
!             for (i = 0; i < rows; i++)
              {
                  row = plperl_hash_from_tuple(tuptable->vals[i], tuptable->tupdesc);
!                 sprintf(key, "%i", i);
!                 hv_store(result, key, strlen(key), newRV_noinc((SV*)row), 0);
              }
              SPI_freetuptable(tuptable);
          }
      }
--- 82,129 ----
          * Get the attributes value
          ************************************************************/
          attdata = SPI_getvalue(tuple, tupdesc, i+1);
!         if(attdata)
!             hv_store(array, attname, strlen(attname), newSVpv(attdata,0), 0);
!         else
!             hv_store(array, attname, strlen(attname), newSVpv("undef",0), 0);
      }
      return array;
  }

  static HV*
! plperl_spi_execute_fetch_result(SPITupleTable *tuptable, int processed, int status)
  {

      HV *result;
+      AV *rows;
      int i;

      result = newHV();
+     rows = newAV();

      if (status == SPI_OK_UTILITY)
      {
          hv_store(result, "status", strlen("status"), newSVpv("SPI_OK_UTILITY",0), 0);
!         hv_store(result, "processed", strlen("processed"), newSViv(processed), 0);
      }
      else if (status != SPI_OK_SELECT)
      {
          hv_store(result, "status", strlen("status"), newSVpv((char*)plperl_spi_status_string(status),0), 0);
!         hv_store(result, "processed", strlen("processed"), newSViv(processed), 0);
      }
      else
      {
!         hv_store(result, "status", strlen("status"), newSVpv((char*)plperl_spi_status_string(status),0), 0);
!         hv_store(result, "processed", strlen("processed"), newSViv(processed), 0);
!         if (processed)
          {
              HV *row;
!             for (i = 0; i < processed; i++)
              {
                  row = plperl_hash_from_tuple(tuptable->vals[i], tuptable->tupdesc);
!                  av_store(rows, i, newRV_noinc((SV*)row));
              }
+             hv_store(result, "rows", strlen("rows"), newRV_noinc((SV*)rows), 0);
              SPI_freetuptable(tuptable);
          }
      }