Обсуждение: prepared queries in plperl

Hello,

I've posted before a patch that enables prepared queries in plperl, and
now, after a while, as it seems working as expected, I'd like to ask if it
would be a reasonable thing to commit in into -devel? The patch against
the latest cvs is at http://www.karasik.eu.org/misc/plperl.diff .

The idea behind the patch is to make it possible to pass non-interpolated
parameters to the query:

CREATE OR REPLACE FUNCTION plus_one(INTEGER) RETURNS INTEGER AS $$ my $x = spi_prepare('SELECT $1 AS datum', 'INT4');
my$rv = spi_exec_prepared($x, $q + 1); spi_freeplan( $x); return $rv->{rows}[0]->{datum};
 
$$ LANGUAGE plperl;

-- 
Thanks,Dmitry Karasik

Dmitry Karasik said:
>
> Hello,
>
> I've posted before a patch that enables prepared queries in plperl, and
> now, after a while, as it seems working as expected, I'd like to ask if
> it would be a reasonable thing to commit in into -devel? The patch
> against the latest cvs is at http://www.karasik.eu.org/misc/plperl.diff
> .
>
> The idea behind the patch is to make it possible to pass
> non-interpolated parameters to the query:
>
> CREATE OR REPLACE FUNCTION plus_one(INTEGER) RETURNS INTEGER AS $$
>  my $x = spi_prepare('SELECT $1 AS datum', 'INT4');
>  my $rv = spi_exec_prepared($x, $q + 1);
>  spi_freeplan( $x);
>  return $rv->{rows}[0]->{datum};
> $$ LANGUAGE plperl;
>


Develeopment branch is currently in feature freeze pending release of
version 8.1.

Meanwhile, I will observe that this very desirable feature needs an
interface with spi_fetchrow() - fetching large gobs of data all at once into
perl memory is just not nice.

We need to examine the names being used on our spi functions - I'm not sure
there is sufficient consistency about them.

cheers

andrew

Andrew> We need to examine the names being used on our spi functions - I'mAndrew> not sure there is sufficient
consistencyabout them.
 

Yes, but I'd rather wish the patch committed before that, because
otherwise I would be forced to rework the code again, after the
namespace is finally stabilized.
Andrew> Meanwhile, I will observe that this very desirable feature needsAndrew> an interface with spi_fetchrow() -
fetchinglarge gobs of data allAndrew> at once into perl memory is just not nice.
 

Agreed, I think as there is pair of spi_exec_query/spi_query, there should be
corresponding spi_exec_prepared/spi_whatever/spi_fetchrow_prepared. If I
have time, I'll look into implementing these, but again, I'd rather wait
until the patch is committed. When 8.1 is scheduled for release? 

-- 
Sincerely,Dmitry Karasik

    Hi Dmitry!

On 27 Sep 05 at 16:16, "Dmitry" (Dmitry Karasik) wrote to "Andrew Dunstan":
Andrew> Meanwhile, I will observe that this very desirable feature needsAndrew> an interface with spi_fetchrow()

I re-worked the patch ( http://www.karasik.eu.org/misc/plperl.diff ) and
now there's also spi_query_prepared(), that returns a cursor for a
previously prepared query that is to be used with subsequent spi_fetchrow()
calls.

I also modified spi_fetchrow() itself to avoid a couple of memory leaks,
and added spi_cursor_close() for cases where not all rows are fetched.

-- 
Sincerely,Dmitry Karasik