BUG #3428: plperl spi_exec_prepared char length always unity
The following bug has been logged online:
Bug reference: 3428
Logged by: Matt Taylor
Email address: matt@lindenelevator.com
PostgreSQL version: 8.2.4
Operating system: Gentoo
Description: plperl spi_exec_prepared char length always unity
Details:
The spi_prepare and spi_exec_prepared doesn't seem to properly handle fixed
character types. Only the first character of a string is passed to the
database.
Is this behavior related to the following statement from the manual?
PostgreSQL 8.2.4 Documentation
Chapter 8. Data Types
8.3. Character Types
The type "char" (note the quotes) is different from char(1) in that it only
uses one byte of storage. It is internally used in the system catalogs as a
poor-man's enumeration type.
Matt Taylor
The following code should recreate the problem:
create table bug_demo_table ( xx char(2) );
insert into bug_demo_table ( xx ) values ( 'AB' );
CREATE FUNCTION bug_demo() RETURNS INTEGER AS $$
my $sql = 'insert into bug_demo_table ( xx ) values ( $1 );' ;
my $sth = spi_prepare( $sql, 'char' );
spi_exec_prepared( $sth, 'CD' ); # broken
my $sth = spi_prepare( $sql, 'text' );
spi_exec_prepared( $sth, 'EF' ); # works
my $sth = spi_prepare( $sql, 'varchar' );
spi_exec_prepared( $sth, 'GH' ); # works
return 1;
$$ LANGUAGE 'plperlu';
select bug_demo();
select * from bug_demo_table;
"Matt Taylor" <matt@lindenelevator.com> writes:
The spi_prepare and spi_exec_prepared doesn't seem to properly handle fixed
character types. Only the first character of a string is passed to the
database.
IIRC, "char" means "char(1)" by default, so I'm unconvinced that this
behavior is a bug.
regards, tom lane
On Thu, 2007-07-05 at 00:13 -0400, Tom Lane wrote:
"Matt Taylor" <matt@lindenelevator.com> writes:
The spi_prepare and spi_exec_prepared doesn't seem to properly handle fixed
character types. Only the first character of a string is passed to the
database.IIRC, "char" means "char(1)" by default, so I'm unconvinced that this
behavior is a bug.regards, tom lane
Since 'character' is not accepted as a valid type by spi_prepare, is
this bug a duplicate of BUG #2917? Maybe I am just doing this wrong,
the documentation is sparse.
Matt
Matt Taylor <matt@lindenelevator.com> writes:
On Thu, 2007-07-05 at 00:13 -0400, Tom Lane wrote:
IIRC, "char" means "char(1)" by default, so I'm unconvinced that this
behavior is a bug.
Since 'character' is not accepted as a valid type by spi_prepare, is
this bug a duplicate of BUG #2917? Maybe I am just doing this wrong,
the documentation is sparse.
Oh, duh, I should have thought harder. Yeah, in 8.2 you will get the
internal 1-byte-char datatype, not bpchar which is what spi_prepare
will interpret this as meaning in 8.3 and beyond.
I'd suggest using "bpchar", or maybe better "varchar" (do you actually
want fixed-width char type??) for the time being.
regards, tom lane