40.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, "cannot 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);

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 renvoyé par spi_query 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.

spi_prepare, spi_query_prepared, spi_exec_prepared et spi_freeplan implémentent la même fonctionnalité, mais pour des requêtes préparées. spi_prepare accepte une chaîne pour la requête avec des arguments numérotés ($1, $2, etc) et une liste de chaînes indiquant le type des arguments :

$plan = spi_prepare('SELECT * FROM test WHERE id > $1 AND name = $2', 'INTEGER', 'TEXT');

Une fois qu'un plan est préparé suite à 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 dans spi_query_prepared qui renvoit un curseur exactement comme le fait spi_query, qui peut ensuite être passé à spi_fetchrow. Le deuxième paramètre, optionnel, de spi_exec_prepared est une référence hachée des attributs ; le seul attribut actuellement supporté est limit, qui configure le nombre maximum de lignes renvoyées par une requête.

L'avantage des requêtes préparées est que cela rend possible l'utilisation d'un plan préparé par plusieurs exécutions de la requête. Une fois que le plan n'est plus utile, il peut être libéré avec spi_freeplan :

CREATE OR REPLACE FUNCTION init() RETURNS VOID 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 VOID 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 l'indice du paramètre dans spi_prepare est défini via $1, $2, $3, etc, donc évitez de déclarer des chaînes de requêtes qui pourraient aisément amener des bogues difficiles à trouver et corriger.

Cet autre exemple illustre l'utilisation d'un paramètre optionnel avec spi_exec_prepared :

CREATE TABLE hosts AS SELECT id, ('192.168.1.'||id)::inet AS address FROM generate_series(1,3) AS id;

CREATE OR REPLACE FUNCTION init_hosts_query() RETURNS VOID AS $$
        $_SHARED{plan} = spi_prepare('SELECT * FROM hosts WHERE address << $1', 'inet');
$$ LANGUAGE plperl;

CREATE OR REPLACE FUNCTION query_hosts(inet) RETURNS SETOF hosts AS $$
        return spi_exec_prepared(
                $_SHARED{plan},
                {limit => 2},
                $_[0]
        )->{rows};
$$ LANGUAGE plperl;

CREATE OR REPLACE FUNCTION release_hosts_query() RETURNS VOID AS $$
        spi_freeplan($_SHARED{plan});
        undef $_SHARED{plan};
$$ LANGUAGE plperl;

SELECT init_hosts_query();
SELECT query_hosts('192.168.1.0/30');
SELECT release_hosts_query();

    query_hosts    
-----------------
 (1,192.168.1.1)
 (2,192.168.1.2)
(2 rows)
    

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 18, Configuration du serveur pour plus d'informations.