spi_exec_query ejecuta un comando SQL y devuelve todo el conjunto de filas como una referencia a un array de referencias a hashes. Si se especifica limit y es mayor que cero, entonces spi_exec_query recupera como máximo limit filas, de la misma manera que si la consulta incluyera una cláusula LIMIT. Omitir limit o especificarlo como cero da como resultado que no haya límite de filas.

Solo debes usar este comando cuando sepas que el conjunto de resultados será relativamente pequeño. Aquí hay un ejemplo de una consulta (comando SELECT) con el número máximo opcional de filas:

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

Esto devuelve hasta 5 filas de la tabla my_table. Si my_table tiene una columna my_column, puedes obtener ese valor de la fila $i del resultado de esta manera:

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

Se puede acceder al número total de filas devueltas por una consulta SELECT de esta manera:

$nrows = $rv->{processed}

Aquí hay un ejemplo que utiliza un tipo de comando diferente:

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

Luego puedes acceder al estado del comando (por ejemplo, SPI_OK_INSERT) de esta manera:

$res = $rv->{status};

Para obtener el número de filas afectadas, haz lo siguiente:

$nrows = $rv->{processed};

Aquí hay un ejemplo completo:

CREATE TABLE test (
    i int,
    v varchar
);

INSERT INTO test (i, v) VALUES (1, 'first line');
INSERT INTO test (i, v) VALUES (2, 'second line');
INSERT INTO test (i, v) VALUES (3, 'third line');
INSERT INTO test (i, v) VALUES (4, 'immortal');

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 y spi_fetchrow trabajan juntos como una pareja para conjuntos de filas que podrían ser grandes, o para casos en los que deseas devolver filas a medida que llegan. spi_fetchrow funciona solo con spi_query. El siguiente ejemplo ilustra cómo los usas juntos:

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 # ¡vaya, es un acceso a un archivo!
        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);

Normalmente, spi_fetchrow debe repetirse hasta que devuelva undef, lo que indica que no hay más filas que leer. El cursor devuelto por spi_query se libera automáticamente cuando spi_fetchrow devuelve undef. Si no deseas leer todas las filas, llama en su lugar a spi_cursor_close para liberar el cursor. El no hacerlo resultará en fugas de memoria (memory leaks).

spi_prepare, spi_query_prepared, spi_exec_prepared, y spi_freeplan implementan la misma funcionalidad pero para consultas preparadas. spi_prepare acepta una cadena de consulta con marcadores de posición de argumentos numerados ($1, $2, etc.) y una lista de cadenas de tipos de argumentos:

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

Una vez que un plan de consulta se prepara mediante una llamada a spi_prepare, el plan se puede utilizar en lugar de la consulta de cadena, ya sea en spi_exec_prepared, donde el resultado es el mismo que el devuelto por spi_exec_query, o en spi_query_prepared que devuelve un cursor exactamente como lo hace spi_query, que luego se puede pasar a spi_fetchrow. El segundo parámetro opcional para spi_exec_prepared es una referencia a un hash de atributos; el único atributo actualmente admitido es limit, que establece el número máximo de filas devueltas por la consulta. Omitir limit o especificarlo como cero da como resultado que no haya límite de filas.

La ventaja de las consultas preparadas es que es posible utilizar un plan preparado para más de una ejecución de consulta. Después de que el plan ya no sea necesario, se puede liberar con 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

Ten en cuenta que el subíndice del parámetro en spi_prepare se define a través de $1, $2, $3, etc., así que evita declarar cadenas de consulta en comillas dobles que puedan fácilmente conducir a errores difíciles de detectar.

Otro ejemplo ilustra el uso de un parámetro opcional en 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)

Confirma (commit) o revierte (rollback) la transacción actual. Esto solo se puede llamar en un procedimiento o bloque de código anónimo (comando DO) llamado desde el nivel superior. (Ten en cuenta que no es posible ejecutar los comandos SQL COMMIT o ROLLBACK a través de spi_exec_query o similar. Se tiene que hacer utilizando estas funciones). Después de que finaliza una transacción, se inicia automáticamente una nueva transacción, por lo que no hay una función separada para eso.

Aquí hay un ejemplo:

CREATE PROCEDURE transaction_test1()
LANGUAGE plperl
AS $$
foreach my $i (0..9) {
    spi_exec_query("INSERT INTO test1 (a) VALUES ($i)");
    if ($i % 2 == 0) {
        spi_commit();
    } else {
        spi_rollback();
    }
}
$$;

CALL transaction_test1();

Emite un mensaje de registro o error. Los niveles posibles son DEBUG, LOG, INFO, NOTICE, WARNING, y ERROR. ERROR eleva una condición de error; si esta no es capturada por el código Perl circundante, el error se propaga a la consulta de llamada, causando que la transacción o subtransacción actual sea abortada. Esto es efectivamente lo mismo que el comando Perl die. Los otros niveles solo generan mensajes de diferentes niveles de prioridad. Si los mensajes de una prioridad particular se reportan al cliente, se escriben en el registro del servidor o ambos, se controla mediante las variables de configuración log_min_messages y client_min_messages. Consulta la Chapter 19 para obtener más información.

Devuelve la cadena dada adecuadamente entrecomillada para ser utilizada como un literal de cadena en una cadena de sentencia SQL. Las comillas simples y barras invertidas incrustadas se duplican correctamente. Ten en cuenta que quote_literal devuelve undef en una entrada undef; si el argumento puede ser undef, quote_nullable suele ser más adecuado.

Devuelve la cadena dada adecuadamente entrecomillada para ser utilizada como un literal de cadena en una cadena de sentencia SQL; o, si el argumento es undef, devuelve la cadena sin comillas "NULL". Las comillas simples y barras invertidas incrustadas se duplican correctamente.

Devuelve la cadena dada adecuadamente entrecomillada para ser utilizada como un identificador en una cadena de sentencia SQL. Las comillas se añaden solo si es necesario (es decir, si la cadena contiene caracteres que no son de identificador o se convertirían a minúsculas). Las comillas incrustadas se duplican correctamente.

Devuelve los datos binarios sin escapar representados por el contenido de la cadena dada, la cual debe estar codificada en bytea.

Devuelve la forma codificada en bytea del contenido de datos binarios de la cadena dada.

Devuelve el contenido del array referenciado como una cadena en formato de literal de array (consulta la Section 8.15.2). Devuelve el valor del argumento inalterado si no es una referencia a un array. El delimitador utilizado entre los elementos del literal de array por defecto es ", " si no se especifica un delimitador o es undef.

Convierte una variable Perl al valor del tipo de datos pasado como segundo argumento y devuelve una representación de cadena de este valor. Maneja correctamente arrays anizados y valores de tipos compuestos.

Devuelve el contenido del array referenciado como una cadena en formato de constructor de array (consulta la Section 4.2.12). Los valores individuales se entrecomillan utilizando quote_nullable. Devuelve el valor del argumento, entrecomillado con quote_nullable, si no es una referencia a un array.

Devuelve un valor verdadero si el contenido de la cadena dada parece un número, según Perl, de lo contrario devuelve falso. Devuelve undef si el argumento es undef. El espacio inicial y final se ignora. Inf e Infinity se consideran números.

Devuelve un valor verdadero si el argumento dado puede tratarse como una referencia a un array, es decir, si ref del argumento es ARRAY o PostgreSQL::InServer::ARRAY. Devuelve falso de lo contrario.