Thread: 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