Re: [HACKERS] PL/Perl: spi_prepare() and RETURNING
On 8/27/06, Tom Lane <[EMAIL PROTECTED]> wrote: I've applied a patch along these lines. David's plperl example now does what (I think) he expected. Kewl. Thanks Tom. -- Jonah H. Harris, Software Architect | phone: 732.331.1300 EnterpriseDB Corporation| fax: 732.331.1301 33 Wood Ave S, 2nd Floor| [EMAIL PROTECTED] Iselin, New Jersey 08830| http://www.enterprisedb.com/ ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] PL/Perl: spi_prepare() and RETURNING
"Jonah H. Harris" <[EMAIL PROTECTED]> writes: > On 8/24/06, Tom Lane <[EMAIL PROTECTED]> wrote: >> This reminds me of a consideration I had been intending to bring up on >> the mailing lists: what exactly do we want to do with the SPI API for >> RETURNING queries? > I like adding RETURNING-specific return codes. >> Another issue I noted in that same area is that spi.c does not set >> SPI_processed for a utility statement, even if the utility statement >> returns tuples. Is this a bug, or should we leave it alone? > I think it's a bug. I've applied a patch along these lines. David's plperl example now does what (I think) he expected. Does anyone want to extend the plperl, pltcl, plpython regression tests to check behavior with INSERT RETURNING etc? regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] PL/Perl: spi_prepare() and RETURNING
On 8/24/06, Tom Lane <[EMAIL PROTECTED]> wrote: This reminds me of a consideration I had been intending to bring up on the mailing lists: what exactly do we want to do with the SPI API for RETURNING queries? The current behavior is that it still returns SPI_OK_INSERT and so on, but also creates a SPI_tuptable. Is this what we want? Perhaps we should invent additional return codes SPI_OK_INSERT_RETURNING etc. I like adding RETURNING-specific return codes. Another issue I noted in that same area is that spi.c does not set SPI_processed for a utility statement, even if the utility statement returns tuples. Is this a bug, or should we leave it alone? I think it's a bug. -- Jonah H. Harris, Software Architect | phone: 732.331.1300 EnterpriseDB Corporation| fax: 732.331.1301 33 Wood Ave S, 2nd Floor| [EMAIL PROTECTED] Iselin, New Jersey 08830| http://www.enterprisedb.com/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] PL/Perl: spi_prepare() and RETURNING
Tom Lane wrote: Another issue I noted in that same area is that spi.c does not set SPI_processed for a utility statement, even if the utility statement returns tuples. Is this a bug, or should we leave it alone? Bug or not, it's at least a limitation we could do without, I think. cheers andrew ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] PL/Perl: spi_prepare() and RETURNING
David Fetter <[EMAIL PROTECTED]> writes: >> I've found a little lacuna in the RETURNING feature, that being in >> PL/Perl's spi_prepare()/spi_execute_prepared() as illustrated in the >> attached file on CVS TIP. It looks like plperl_spi_execute_fetch_result only expects to find tuples returned if the status is SPI_OK_SELECT. pltcl and plpython have likely got similar issues. This reminds me of a consideration I had been intending to bring up on the mailing lists: what exactly do we want to do with the SPI API for RETURNING queries? The current behavior is that it still returns SPI_OK_INSERT and so on, but also creates a SPI_tuptable. Is this what we want? Perhaps we should invent additional return codes SPI_OK_INSERT_RETURNING etc. Another issue I noted in that same area is that spi.c does not set SPI_processed for a utility statement, even if the utility statement returns tuples. Is this a bug, or should we leave it alone? regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] PL/Perl: spi_prepare() and RETURNING
David Fetter wrote:
On Thu, Aug 24, 2006 at 11:31:02AM -0700, David Fetter wrote:
Folks,
I've found a little lacuna in the RETURNING feature, that being in
PL/Perl's spi_prepare()/spi_execute_prepared() as illustrated in the
attached file on CVS TIP.
I think that fixing this is a matter of post-RETURNING-patch cleanup.
What else might not know about RETURNING that needs to?
Cheers,
D
Oops. This time, with the actual file attached :P
my $foo_id = spi_exec_prepared(
$sth,
$_[0],
)->{rows}->[0]->{foo_id};
I am not sure that the 'rows' member is the right place for it, even if
it were returned. For one thing, it should always be an arrayref, but
overloading the first row like this is ugly.
Maybe 'returned_value' or some such would be better, e.g. $foo_id =
$result->{returned_value}->{foo_id}
(We can't just stash it in the result hash with the used name in case
somebody overrides the preexisiting members)
cheers
andrew
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
Re: [HACKERS] PL/Perl: spi_prepare() and RETURNING
On Thu, Aug 24, 2006 at 11:31:02AM -0700, David Fetter wrote:
> Folks,
>
> I've found a little lacuna in the RETURNING feature, that being in
> PL/Perl's spi_prepare()/spi_execute_prepared() as illustrated in the
> attached file on CVS TIP.
>
> I think that fixing this is a matter of post-RETURNING-patch cleanup.
> What else might not know about RETURNING that needs to?
>
> Cheers,
> D
Oops. This time, with the actual file attached :P
Cheers,
D
--
David Fetter <[EMAIL PROTECTED]> http://fetter.org/
phone: +1 415 235 3778AIM: dfetter666
Skype: davidfetter
Remember to vote!
DROP TABLE IF EXISTS foo;
CREATE TABLE foo (
foo_id SERIAL PRIMARY KEY,
foo_text TEXT NOT NULL
);
CREATE OR REPLACE FUNCTION foo_insert(
in_foo_text TEXT
)
RETURNS INTEGER
LANGUAGE plperlu
AS $$
use strict;
elog NOTICE, "in_foo_text is $_[0]";
my $sql = <<'SQL';
INSERT INTO foo (
foo_text
)
VALUES (
$1
)
RETURNING foo_id
SQL
elog NOTICE, $sql;
my $sth = spi_prepare($sql, 'TEXT');
my $foo_id = spi_exec_prepared(
$sth,
$_[0],
)->{rows}->[0]->{foo_id};
spi_freeplan($sth);
return $foo_id;
$$;
SELECT foo_insert('blargh'::TEXT);
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
