39.2. Accès à la base de données depuis PL/Perl

spi_exec_query exécute une commande SQL et renvoie l'ensemble complet de la ligne comme une référence à un table de références hachées. Vous ne devez utiliser cette commande que lorsque vous savez que l'ensemble de résultat sera relativement petit. Voici un exemple d'une requête (commande SELECT) avec le nombre optionnel maximum de lignes :

$rv = spi_exec_query('SELECT * FROM ma_table', 5);

Ceci entrevoit cinq lignes au maximum de la table ma_table. Si ma_table a une colonne ma_colonne, vous obtenez la valeur de la ligne $i du résultat de cette façon :

$foo = $rv->{rows}[$i]->{ma_colonne};

Le nombre total des lignes renvoyées d'une requête SELECT peut être accédé de cette façon :

$nrows = $rv->{processed}

Voici un exemple en utilisant un type de commande différent :

$query = "INSERT INTO ma_table VALUES (1, 'test')";
$rv = spi_exec_query($query);

Ensuite, vous pouvez accéder au statut de la commande (c'est-à-dire, SPI_OK_INSERT) de cette façon :

$res = $rv->{status};

Pour obtenir le nombre de lignes affectées, exécutez :

$nrows = $rv->{processed};

Voici un exemple complet :

CREATE TABLE test (
    i int,
    v varchar
    );

INSERT INTO test (i, v) VALUES (1, 'première ligne');
INSERT INTO test (i, v) VALUES (2, 'deuxième ligne');
INSERT INTO test (i, v) VALUES (3, 'troisième ligne');
INSERT INTO test (i, v) VALUES (4, 'immortel');

CREATE OR REPLACE FUNCTION test_munge() RETURNS SETOF test AS $$
    my $rv = spi_exec_query('select i, v from test;');
    my $status = $rv->{status};
    my $nrows = $rv->{processed};
    foreach my $rn (0 .. $nrows - 1) {
        my $row = $rv->{rows}[$rn];
        $row->{i} += 200 if defined($row->{i});
        $row->{v} =~ tr/A-Za-z/a-zA-Z/ if (defined($row->{v}));
        return_next($row);
    }
    return undef;
$$ LANGUAGE plperl;

SELECT * FROM test_munge();
       

spi_query et spi_fetchrow fonctionnent ensemble comme une paire d'ensembles de lignes pouvant être assez importants ou pour les cas où vous souhaitez renvoyer les lignes dès qu'elles arrivent. spi_fetchrow fonctionne seulement avec spi_query. L'exemple suivant illustre comment vous les utilisez ensemble :

CREATE TYPE foo_type AS (the_num INTEGER, the_text TEXT);

CREATE OR REPLACE FUNCTION lotsa_md5 (INTEGER) RETURNS SETOF foo_type AS $$
    use Digest::MD5 qw(md5_hex);
    my $file = '/usr/share/dict/words';
    my $t = localtime;
    elog(NOTICE, "opening file $file at $t" );
    open my $fh, '<', $file # ooh, it's a file access!
        or elog(ERROR, "can't open $file for reading: $!");
    my @words = <$fh>;
    close $fh;
    $t = localtime;
    elog(NOTICE, "closed file $file at $t");
    chomp(@words);
    my $row;
    my $sth = spi_query("SELECT * FROM generate_series(1,$_[0]) AS b(a)");
    while (defined ($row = spi_fetchrow($sth))) {
        return_next({
            the_num => $row->{a},
            the_text => md5_hex($words[rand @words])
        });
    }
    return;
$$ LANGUAGE plperlu;

SELECT * from lotsa_md5(500);

spi_prepare, spi_query_prepared, spi_exec_prepared et spi_freeplan implémentent la même fonctionnalité mais pour les requêtes préparées. Une fois que le plan de requêtes est préparé par un appel à spi_prepare, le plan peut être utilisé à la place de la requête, soit dans spi_exec_prepared, où le résultat est identique à celui renvoyé par spi_exec_query, soit spi_query_prepared qui renvoie un curseur exactement comme le fait spi_query, qui pourra être passé par la suite à spi_fetchrow.

L'avantage des requêtes préparées est qu'il est possible d'utiliser un plan préparé pour plus d'une exécution de requête. Une fois le plan inutile, il pourra être libéré avec spi_freeplan :

CREATE OR REPLACE FUNCTION init() RETURNS INTEGER AS $$
        $_SHARED{my_plan} = spi_prepare( 'SELECT (now() + $1)::date AS now', 'INTERVAL');
$$ LANGUAGE plperl;

CREATE OR REPLACE FUNCTION add_time( INTERVAL ) RETURNS TEXT AS $$
        return spi_exec_prepared( 
                $_SHARED{my_plan},
                $_[0],
        )->{rows}->[0]->{now};
$$ LANGUAGE plperl;

CREATE OR REPLACE FUNCTION done() RETURNS INTEGER AS $$
        spi_freeplan( $_SHARED{my_plan});
        undef $_SHARED{my_plan};
$$ LANGUAGE plperl;

SELECT init();
SELECT add_time('1 day'), add_time('2 days'), add_time('3 days');
SELECT done();

  add_time  |  add_time  |  add_time  
------------+------------+------------
 2005-12-10 | 2005-12-11 | 2005-12-12
    

Notez que les indices des paramètres dans spi_prepare sont définis via $1, $2, $3, etc, pour éviter de déclarer des chaînes de requête entre guillemets doubles, ce qui pourrait amener à des bogues difficilement supprimables.

Habituellement, spi_fetchrow devra être répété jusqu'à ce qu'il renvoie undef, indiquant qu'il n'y a plus de lignes à lire. Le curseur est automatiquement libéré quand spi_fetchrow renvoie undef. Si vous ne souhaitez pas lire toutes les lignes, appelez à la place spi_cursor_close pour libérer le curseur. Un échec ici résultera en des pertes mémoire.

Produit un message de trace ou d'erreur. Les niveaux possibles sont DEBUG, LOG, INFO, NOTICE, WARNING et ERROR. ERROR lève une condition d'erreur ; si elle n'est pas récupérée par le code Perl l'entourant, l'erreur se propage à l'extérieur de la requête appelante, causant l'annulation de la transaction ou sous-transaction en cours. Ceci est en fait identique à la commande die de Perl. Les autres niveaux génèrent seulement des messages de niveaux de priorité différents. Le fait que les messages d'un niveau de priorité particulier soient rapportés au client, écrit dans les journaux du serveur, voire les deux, est contrôlé par les variables de configuration log_min_messages et client_min_messages. Voir le Chapitre 17, Configuration du serveur pour plus d'informations.