41.3. Fonction incluses

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 renvoi 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.

Retourne la chaîne donnée convenablement placé entre simple guillemets pour être utilisée comme une chaîne littérale au sein d'une chaîne représentant un ordre SQL. Les simples guillemets et antislashes de la chaîne sont correctement doublés Notez que quote_literal retourne undef avec une entrée undef ; si l'argument peut être undef, quote_nullable est souvent plus approprié.

Retourne la chaîne donnée convenablement placé entre simple guillemets pour être utilisée comme une chaîne littérale au sein d'une chaîne représentant un ordre SQL. Si l'argument d'entrée est undef, retourne la chaîne "NULL" sans simple guillemet. Les simples guillemets et antislashes de la chaîne sont correctement doublés

Retourne la chaîne donnée convenablement placé entre guillemets pour être utilisée comme un identifiant au sein d'une chaîne représentant un ordre SQL. Les guillemets sont ajoutées seulement si cela est nécessaire (i.e. si la chaîne contient des caractères non-identifiant ou est en majuscule). Les guillemets de la chaîne seront convenablement doublés.

Retourne les données binaires non échappé représentées par le contenu de la chaîne donnée, qui doit être encodé au format bytea.

Retourne sous la forme d'un bytea le contenu binaire dans la chaîne passé en argument.

Retourne le contenu de tableau passé par référence sous forme d'une chaîne littérale. (voir Section 8.15.2, « Saisie de valeurs de type tableau »). Retourne la valeur de l'argument non altérée si ce n'est pas une référence à un tableau. Le délimiteur utilisé entre les éléments du tableau sous forme littérale sera par défaut ", " si aucun délimiteur n'est spécifié ou s'il est undef.

Convertit une variable Perl en une valeur du type de données passé en second argument et renvoie une représentation de type chaîne pour cette valeur. Gère correctement les tableaux imbriqués et les valeurs de types composites.

Retourne le contenu de tableau passé par référence sous forme d'une chaîne permettant de construire un tableau en SQL. (voir Section 4.2.12, « Constructeurs de tableaux »). Chaque élément est entouré de simple guillemets par quote_nullable. Retourne la valeur de l'argument, entouré de simple guillemets par quote_nullable, si ce n'est pas une référence à un tableau.

Retourne une valeur vraie si le contenu de la chaîne passée ressemble à un nombre, selon l'interprétation de Perl, et faux dans le cas contraire. Retourne undef si undef est passé en argument. Tout espace en début et fin de chaîne sont ignorés. Inf et Infinity sont vu comme des nombres.

Renvoie une valeur true si l'argument donné peut être traité comme une référence de tableau, c'est-à-dire si la référence de l'argument est ARRAY ou PostgreSQL::InServer::ARRAY. Renvoie false sinon.