Re: plperl spi_exec_query patch - Mailing list pgsql-patches

Patch withdrawn by author.

---------------------------------------------------------------------------

Andrew Dunstan wrote:
>
> 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
>
>
> !DSPAM:40e84605260381013413849!

[ text/x-patch is unsupported, treating like TEXT/PLAIN ]

> 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);
>           }
>       }
>
>
> !DSPAM:40e84605260381013413849!

>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>
>
> !DSPAM:40e84605260381013413849!

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073