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