Postgresql 8.1: plperl code works with LATIN1, fails with UTF8 - Mailing list pgsql-general

Hi,

I've got plperl code that works just fine when the database is encoded using LATIN1, but fails as soon as I switch to
UTF8.

I've been testing PG 8.1.4 under Linux, and PG 8.1.6 under FreeBSD, both behave exactly the save.

I'm sorry I'm not able to strip down the code, and show you a small test, but if anyone need the full script, feel free
toask me per email. 

The code is made up of plperl routines, all structured in the same way, but only one of them fails in UTF8. It is:


#----------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION public.volets_fiche_fab_1 (
    IN    id_commande        int4,

    OUT pos            int4,
    OUT quant            int4,
    OUT nbre_vtx        int4,
    OUT nbre_vtx_total        int4,
    OUT larg_maconnerie        int4,
    OUT haut_maconnerie        int4,
    OUT larg_vtx            varchar(20),
    OUT haut_vtx        int4,
    OUT ouv                     int4,
    OUT couvre_joints           text,
    OUT coupe_verticale         text,
    OUT vide_interieur          varchar(20),
    OUT typ            varchar(20)
)
RETURNS SETOF record
AS

$$

    BEGIN { strict->import(); }

    #----------------------------------------------------------------------------
    #-- Lexical variables
    #----------------------------------------------------------------------------
    my @i;
    my @io;
    my @o;
    my $i;
    my $io;
    my $o;
    my %input;
    my %output;
    my $fab;
    my $fab_nrows;
    my $lignes_query;
    my $lignes;
    my $lignes_nrows;
    my $lignes_rn;
    my $c;
    my $j;
    my $key;
    my $value;
    my $ordre;
    my $vtxg;
    my $vtxd;

    #----------------------------------------------------------------------------
    #-- Helper functions
    #----------------------------------------------------------------------------
    my $init = sub
    {
    $c = 0;
        foreach $i (@i) {$input{$i} = @_[$c++]};
        foreach $io (@io) {$input{$io} = @_[$c]; $output{$io} = @_[$c++]};
        foreach $o (@o) {$output{$o} = @_[$c++]};
    };

    my $start_sub = sub
    {
        &$init(@_);
    };

    my $end_sub = sub
    {
        return undef;
    };

    my $ret = sub
    {
    while (($key, $value) = each %output) {if (!defined($value)) {elog(ERROR, 'Valeur indéfinie pour ' . $key)}};
    return_next \%output;
    &$init(@_);
    };

    #----------------------------------------------------------------------------
    #-- Configuration des paramètres de la fonction
    #----------------------------------------------------------------------------
    @i = ( 'id_commande'
         );

    @io = ();

    @o = ( 'pos',
       'quant',
           'nbre_vtx',
           'nbre_vtx_total',
           'larg_maconnerie',
           'haut_maconnerie',
           'larg_vtx',
           'haut_vtx',
           'ouv',
           'couvre_joints',
       'coupe_verticale',
           'vide_interieur',
       'typ'
         );

    #----------------------------------------------------------------------------
    #-- Préparation des paramètres de la fonction
    #----------------------------------------------------------------------------
    &$start_sub(@_);

    #----------------------------------------------------------------------------
    #-- Création de la fiche de fabrication
    #----------------------------------------------------------------------------
    $lignes_query = 'SELECT * FROM lignes WHERE id_commande = ' . $input{'id_commande'} . ' ORDER BY pos;';
    $lignes = spi_exec_query($lignes_query);
    $lignes_nrows = $lignes->{processed};
    foreach $lignes_rn (0 .. $lignes_nrows - 1)
    {
    # Fabrication de la ligne
    $fab = spi_exec_query('SELECT * FROM volets_fab(' . $lignes->{rows}[$lignes_rn]->{'id'} . ');');
    $fab_nrows = $fab->{processed};

    # Recherches des éventuels vantaux de gauche et droite
    for ($j = 0; ($fab->{rows}[$j]->{'article'} ne 'Largeur de vantail gauche') and ($j < $fab_nrows); $j = $j + 1) {};
    if ($j < $fab_nrows) { $vtxg = $fab->{rows}[$j]->{'larg'}; }
    for ($j = 0; ($fab->{rows}[$j]->{'article'} ne 'Largeur de vantail droite') and ($j < $fab_nrows); $j = $j + 1) {};
    if ($j < $fab_nrows) { $vtxd = $fab->{rows}[$j]->{'larg'}; }

    # Position
    $output{'pos'} = $lignes->{rows}[$lignes_rn]->{'pos'};

    # Quantité
        $output{'quant'} = $lignes->{rows}[$lignes_rn]->{'quant'};

    # Nombre de vantaux
    $output{'nbre_vtx'} = $lignes->{rows}[$lignes_rn]->{'nbre_vtx'};

    # Nombre de vantaux total
    $output{'nbre_vtx_total'} = $lignes->{rows}[$lignes_rn]->{'nbre_vtx'} * $lignes->{rows}[$lignes_rn]->{'quant'};

    # Largeur de maçonnerie
    for ($j = 0; ($fab->{rows}[$j]->{'article'} ne 'Largeur de maçonnerie') and ($j < $fab_nrows); $j = $j + 1) {};
    if ($j < $fab_nrows) { $output{'larg_maconnerie'} = $fab->{rows}[$j]->{'larg'}; }
    else { $output{'larg_maconnerie'} = ''; };

    # Hauteur de maçonnerie
        for ($j = 0; ($fab->{rows}[$j]->{'article'} ne 'Hauteur de maçonnerie') and ($j < $fab_nrows); $j = $j + 1) {};
    if ($j < $fab_nrows) { $output{'haut_maconnerie'} = $fab->{rows}[$j]->{'haut'}; }
    else { $output{'haut_maconnerie'} = ''; };

        # Largeur de vantail
    if (defined($vtxg) and defined($vtxd))
    {
        # Vantaux asymétriques
        $output{'larg_vtx'} = $vtxg . " / " . $vtxd;
    }
    else
    {
        # Vantaux symétriques
        for ($j = 0; ($fab->{rows}[$j]->{'article'} ne 'Largeur de vantail') and ($j < $fab_nrows); $j = $j + 1) {};
        if ($j < $fab_nrows) { $output{'larg_vtx'} = $fab->{rows}[$j]->{'larg'}; }
        else { $output{'larg_vtx'} = ''; };
    }

    # Hauteur de vantail
        for ($j = 0; ($fab->{rows}[$j]->{'article'} ne 'Hauteur de vantail') and ($j < $fab_nrows); $j = $j + 1) {};
    if ($j < $fab_nrows) { $output{'haut_vtx'} = $fab->{rows}[$j]->{'haut'}; }
    else { $output{'haut_vtx'} = ''; };

        # Type d'ouverture
    $output{'ouv'} = $lignes->{rows}[$lignes_rn]->{'ouv'};

    # Image des couvre-joints
    for ($j = 0; ($fab->{rows}[$j]->{'article'} ne 'Couvre-joints') and ($j < $fab_nrows); $j = $j + 1) {};
    if ($j < $fab_nrows) { $output{'couvre_joints'} = $fab->{rows}[$j]->{'image'}; }
    else { $output{'couvre_joints'} = ''; };

    # Image de la coupe verticape
    for ($j = 0; ($fab->{rows}[$j]->{'article'} ne 'Coupe verticale') and ($j < $fab_nrows); $j = $j + 1) {};
    if ($j < $fab_nrows) { $output{'coupe_verticale'} = $fab->{rows}[$j]->{'image'}; }
    else { $output{'coupe_verticale'} = ''; };

    # Vide intérieur
    if (defined($vtxg) and defined($vtxd))
    {
        # Vantaux asymétriques
        $output{'vide_interieur'} = ($vtxg - 106) . " / " . ($vtxd - 106);
    }
    else
    {
        # Vantaux symétriques
        for ($j = 0; ($fab->{rows}[$j]->{'article'} ne 'Largeur de vantail') and ($j < $fab_nrows); $j = $j + 1) {};
        if ($j < $fab_nrows) { $output{'vide_interieur'} = $fab->{rows}[$j]->{'larg'} - 106; }
        else { $output{'vide_interieur'} = ''; };
    }

    # Type de volet
    $output{'typ'} = $lignes->{rows}[$lignes_rn]->{'typ'};

    # Sortie
        &$ret(@_);
    }

    #----------------------------------------------------------------------------
    #-- Fin de la fonction
    #----------------------------------------------------------------------------
    &$end_sub(@_);

$$

LANGUAGE 'plperl' VOLATILE;
#----------------------------------------------------------------------------


When running:
-------------

  select * from volets_fiche_fab_1(1)

Database replies:
-----------------

  ERROR: error from Perl function: invalid input syntax for integer: "" at line 54.
  SQL state: XX000



Does anyone have a small idea where to search?

Thanks

Philippe Lang