Thread: plperl spi_exec_query patch
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);
}
}
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