La asignación de un valor a una variable de PL/pgSQL se escribe como:

variable { := | = } expresión;

Como se explicó anteriormente, la expresión en dicha instrucción se evalúa mediante un comando SQL SELECT enviado al motor de base de datos principal. La expresión debe producir un único valor (posiblemente un valor de fila, si la variable es una variable de fila o de registro). La variable de destino puede ser una variable simple (opcionalmente calificada con un nombre de bloque), un campo de un destino de fila o registro, o un elemento o sección (slice) de un destino de array. Se puede usar el signo igual (=) en lugar de := compatible con PL/SQL.

Si el tipo de datos del resultado de la expresión no coincide con el tipo de datos de la variable, el valor se convertirá como si se tratara de una conversión de asignación (consulte la Section 10.4). Si no se conoce ninguna conversión de asignación para el par de tipos de datos involucrados, el intérprete de PL/pgSQL intentará convertir el valor del resultado textualmente, es decir, aplicando la función de salida del tipo de resultado seguida de la función de entrada del tipo de variable. Ten en cuenta que esto podría dar lugar a errores en tiempo de ejecución generados por la función de entrada si la forma de cadena del valor del resultado no es aceptable para la función de entrada.

Ejemplos:

tax := subtotal * 0.06;
my_record.user_id := 20;
my_array[j] := 20;
my_array[1:3] := array[1,2,3];
complex_array[n].realpart = 12.3;

En general, any SQL command that does not return rows can be executed within a PL/pgSQL function just by writing the command. Por ejemplo, podrías crear y llenar una tabla escribiendo:

CREATE TABLE mytable (id int primary key, data text);
INSERT INTO mytable VALUES (1,'one'), (2,'two');

If the command does return rows (for example SELECT, or INSERT/UPDATE/DELETE/MERGE with RETURNING), there are two ways to proceed. When the command will return at most one row, or you only care about the first row of output, write the command as usual but add an INTO clause to capture the output, as described in Section 41.5.3. To process all of the output rows, write the command as the data source for a FOR loop, as described in Section 41.6.6.

Usually it is not sufficient just to execute statically-defined SQL commands. Typically you'll want a command to use varying data values, or even to vary in more fundamental ways such as by using different table names at different times. Again, there are two ways to proceed depending on the situation.

PL/pgSQL variable values can be automatically inserted into optimizable SQL commands, which are SELECT, INSERT, UPDATE, DELETE, MERGE, and certain utility commands that incorporate one of these, such as EXPLAIN and CREATE TABLE ... AS SELECT. In these commands, any PL/pgSQL variable name appearing in the command text is replaced by a query parameter, and then the current value of the variable is provided as the parameter value at run time. This is exactly like the processing described earlier for expressions; for details see Section 41.11.1.

When executing an optimizable SQL command in this way, PL/pgSQL may cache and re-use the execution plan for the command, as discussed in Section 41.11.2.

Non-optimizable SQL commands (also called utility commands) are not capable of accepting query parameters. So automatic substitution of PL/pgSQL variables does not work in such commands. To include non-constant text in a utility command executed from PL/pgSQL, you must build the utility command as a string and then EXECUTE it, as discussed in Section 41.5.4.

EXECUTE must also be used if you want to modify the command in some other way than supplying a data value, for example by changing a table name.

Sometimes it is useful to evaluate an expression or SELECT query but discard the result, for example when calling a function that has side-effects but no useful result value. To do this in PL/pgSQL, use the PERFORM statement:

PERFORM query;

This executes query and discards the result. Write the query the same way you would write an SQL SELECT command, but replace the initial keyword SELECT with PERFORM. For WITH queries, use PERFORM and then place the query in parentheses. (In this case, the query can only return one row.) PL/pgSQL variables will be substituted into the query just as described above, and the plan is cached in the same way. Also, the special variable FOUND is set to true if the query produced at least one row, or false if it produced no rows (see Section 41.5.5).

Un ejemplo:

PERFORM create_mv('cs_session_page_requests_mv', my_query);

El resultado de un comando SQL que produce una sola fila (posiblemente de múltiples columnas) se puede asignar a una variable de registro, una variable de tipo de fila o una lista de variables escalares. Esto se hace escribiendo el comando SQL base y agregando una cláusula INTO. Por ejemplo,

SELECT select_expressions INTO [STRICT] destino FROM ...;
INSERT ... RETURNING expresiones INTO [STRICT] destino;
UPDATE ... RETURNING expresiones INTO [STRICT] destino;
DELETE ... RETURNING expresiones INTO [STRICT] destino;
MERGE ... RETURNING expresiones INTO [STRICT] destino;

donde el destino puede ser una variable de registro, una variable de fila o una lista de variables simples y campos de registro/fila separados por comas. Las variables de PL/pgSQL se sustituirán en el resto del comando (es decir, en todo menos en la cláusula INTO) tal como se describe anteriormente, y el plan se almacena en caché de la misma manera. Esto funciona para SELECT, INSERT/UPDATE/DELETE/MERGE con RETURNING y ciertos comandos de utilidad que devuelven conjuntos de filas, como EXPLAIN. A excepción de la cláusula INTO, el comando SQL es el mismo que se escribiría fuera de PL/pgSQL.

If a row variable or a variable list is used as target, the command's result columns must exactly match the structure of the target as to number and data types, or else a run-time error occurs. When a record variable is the target, it automatically configures itself to the row type of the command's result columns.

The INTO clause can appear almost anywhere in the SQL command. Customarily it is written either just before or just after the list of select_expressions in a SELECT command, or at the end of the command for other command types. It is recommended that you follow this convention in case the PL/pgSQL parser becomes stricter in future versions.

If STRICT is not specified in the INTO clause, then target will be set to the first row returned by the command, or to nulls if the command returned no rows. (Note that “the first row” is not well-defined unless you've used ORDER BY.) Any result rows after the first row are discarded. You can check the special FOUND variable (see Section 41.5.5) to determine whether a row was returned:

SELECT * INTO myrec FROM emp WHERE empname = myname;
IF NOT FOUND THEN
    RAISE EXCEPTION 'employee % not found', myname;
END IF;

If the STRICT option is specified, the command must return exactly one row or a run-time error will be reported, either NO_DATA_FOUND (no rows) or TOO_MANY_ROWS (more than one row). You can use an exception block if you wish to catch the error, for example:

BEGIN
    SELECT * INTO STRICT myrec FROM emp WHERE empname = myname;
    EXCEPTION
        WHEN NO_DATA_FOUND THEN
            RAISE EXCEPTION 'employee % not found', myname;
        WHEN TOO_MANY_ROWS THEN
            RAISE EXCEPTION 'employee % not unique', myname;
END;

La ejecución exitosa de un comando con STRICT siempre establece FOUND en true.

Para INSERT/UPDATE/DELETE/MERGE con RETURNING, PL/pgSQL reporta un error si se devuelve más de una fila, incluso cuando no se especifica STRICT. Esto se debe a que no existe una opción como ORDER BY con la cual determinar qué fila afectada debe devolverse.

Si print_strict_params está habilitado para la función, entonces, cuando se genera un error debido a que no se cumplen los requisitos de STRICT, la sección DETAIL del mensaje de error incluirá información sobre los parámetros pasados al comando. Puedes cambiar la configuración de print_strict_params para todas las funciones configurando plpgsql.print_strict_params, aunque solo se verán afectadas las compilaciones de funciones posteriores. También puedes habilitarlo función por función mediante una opción del compilador, por ejemplo:

CREATE FUNCTION get_userid(username text) RETURNS int
AS $$
#print_strict_params on
DECLARE
userid int;
BEGIN
    SELECT users.userid INTO STRICT userid
        FROM users WHERE users.username = get_userid.username;
    RETURN userid;
END;
$$ LANGUAGE plpgsql;

En caso de fallo, esta función podría producir un mensaje de error como:

ERROR:  query returned no rows
DETAIL:  parameters: username = 'nosuchuser'
CONTEXT:  PL/pgSQL function get_userid(text) line 6 at SQL statement

A menudo desearás generar comandos dinámicos dentro de tus funciones de PL/pgSQL, es decir, comandos que involucrarán diferentes tablas o diferentes tipos de datos cada vez que se ejecuten. Los intentos normales de PL/pgSQL de almacenar en caché los planes de los comandos (como se analiza en la Section 41.11.2) no funcionarán en tales escenarios. Para manejar este tipo de problemas, se proporciona la instrucción EXECUTE:

EXECUTE cadena_comando [ INTO [STRICT] destino ] [ USING expresión [, ... ] ];

donde cadena_comando es una expresión que produce una cadena (de tipo text) que contiene el comando a ejecutar. El destino opcional es una variable de registro, una variable de fila o una lista de variables simples y campos de registro/fila separados por comas, en los que se almacenarán los resultados del comando. Las expresiones opcionales USING suministran valores para ser insertados en el comando.

No se realiza ninguna sustitución de variables de PL/pgSQL en la cadena de comando calculada. Cualquier valor de variable requerido debe insertarse en la cadena de comando a medida que se construye; o puedes usar parámetros como se describe a continuación.

Además, no hay almacenamiento en caché de planes para los comandos ejecutados a través de EXECUTE. En su lugar, el comando siempre se planifica cada vez que se ejecuta la instrucción. De este modo, la cadena de comando se puede crear dinámicamente dentro de la función para realizar acciones en diferentes tablas y columnas.

La cláusula INTO especifica dónde se deben asignar los resultados de un comando SQL que devuelve filas. Si se proporciona una variable de fila o una lista de variables, debe coincidir exactamente con la estructura de los resultados del comando; si se proporciona una variable de registro, esta se configurará automáticamente para coincidir con la estructura del resultado. Si se devuelven múltiples filas, solo la primera se asignará a la(s) variable(s) INTO. Si no se devuelven filas, se asigna NULL a la(s) variable(s) INTO. Si no se especifica ninguna cláusula INTO, los resultados del comando se descartan.

Si se proporciona la opción STRICT, se reporta un error a menos que el comando produzca exactamente una fila.

La cadena de comando puede usar valores de parámetros, a los que se hace referencia en el comando como $1, $2, etc. Estos símbolos se refieren a los valores suministrados en la cláusula USING. Este método suele ser preferible a insertar los valores de los datos en la cadena de comando como texto: evita la sobrecarga en tiempo de ejecución de convertir los valores a texto y viceversa, y es mucho menos propenso a ataques de inyección SQL, ya que no es necesario entrecomillar ni escapar. Un ejemplo es:

EXECUTE 'SELECT count(*) FROM mytable WHERE inserted_by = $1 AND inserted <= $2'
   INTO c
   USING checked_user, checked_date;

Ten en cuenta que los símbolos de parámetros solo se pueden usar para valores de datos — si deseas usar nombres de tablas o columnas determinados dinámicamente, debes insertarlos textualmente en la cadena de comando. Por ejemplo, si la consulta anterior debiera realizarse contra una tabla seleccionada dinámicamente, podrías hacerlo así:

EXECUTE 'SELECT count(*) FROM '
    || quote_ident(tabname)
    || ' WHERE inserted_by = $1 AND inserted <= $2'
   INTO c
   USING checked_user, checked_date;

Un enfoque más limpio es usar la especificación %I de format() para insertar nombres de tablas o columnas con entrecomillado automático:

EXECUTE format('SELECT count(*) FROM %I '
   'WHERE inserted_by = $1 AND inserted <= $2', tabname)
   INTO c
   USING checked_user, checked_date;

(Este ejemplo se basa en la regla de SQL de que las cadenas literales separadas por una nueva línea se concatenan implícitamente).

Otra restricción de los símbolos de parámetros es que solo funcionan en comandos SQL optimizables (SELECT, INSERT, UPDATE, DELETE, MERGE y ciertos comandos que contienen uno de estos). En otros tipos de instrucciones (genéricamente llamadas instrucciones de utilidad), debes insertar los valores textualmente, incluso si son solo valores de datos.

Un comando EXECUTE con una cadena de comando constante simple y algunos parámetros USING, como en el primer ejemplo anterior, es funcionalmente equivalente a simplemente escribir el comando directamente en PL/pgSQL y permitir que la sustitución de variables de PL/pgSQL ocurrir automáticamente. La diferencia importante es que EXECUTE volverá a planificar el comando en cada ejecución, generando un plan que es específico para los valores de parámetros actuales; mientras que de otro modo, PL/pgSQL podría crear un plan genérico y almacenarlo en caché para su reutilización. En situaciones donde el mejor plan depende fuertemente de los valores de los parámetros, puede ser útil usar EXECUTE para asegurar positivamente que no se seleccione un plan genérico.

SELECT INTO no es compatible actualmente dentro de EXECUTE; en su lugar, ejecuta un comando SELECT simple y especifica INTO como parte del propio comando EXECUTE.

EXECUTE format('UPDATE tbl SET %I = $1 '
   'WHERE key = $2', colname) USING newvalue, keyvalue;

EXECUTE 'UPDATE tbl SET '
        || quote_ident(colname)
        || ' = '
        || quote_literal(newvalue)
        || ' WHERE key = '
        || quote_literal(keyvalue);

EXECUTE 'UPDATE tbl SET '
        || quote_ident(colname)
        || ' = '
        || quote_nullable(newvalue)
        || ' WHERE key = '
        || quote_nullable(keyvalue);

'WHERE key = ' || quote_nullable(keyvalue)

'WHERE key IS NOT DISTINCT FROM ' || quote_nullable(keyvalue)

EXECUTE 'UPDATE tbl SET '
        || quote_ident(colname)
        || ' = $$'
        || newvalue
        || '$$ WHERE key = '
        || quote_literal(keyvalue);

EXECUTE format('UPDATE tbl SET %I = %L '
   'WHERE key = %L', colname, newvalue, keyvalue);

EXECUTE format('UPDATE tbl SET %I = $1 WHERE key = $2', colname)
   USING newvalue, keyvalue;

Un ejemplo mucho mayor de un comando dinámico y EXECUTE se puede ver en la Example 41.10, que construye y ejecuta un comando CREATE FUNCTION para definir una nueva función.

Existen varias formas de determinar el efecto de un comando. El primer método es utilizar el comando GET DIAGNOSTICS, que tiene la siguiente forma:

GET [ CURRENT ] DIAGNOSTICS variable { = | := } elemento [ , ... ];

Este comando permite recuperar indicadores de estado del sistema. CURRENT es una palabra de adorno (ruido) (pero consulta también GET STACKED DIAGNOSTICS en la Section 41.6.8.1). Cada elemento es una palabra clave que identifica un valor de estado que se asignará a la variable especificada (que debería ser del tipo de datos adecuado para recibirlo). Los elementos de estado disponibles actualmente se muestran en la Table 41.1. Se puede utilizar la combinación de dos puntos e igual (:=) en lugar del token = estándar de SQL. Un ejemplo:

GET DIAGNOSTICS integer_var = ROW_COUNT;

El segundo método para determinar los efectos de un comando es verificar la variable especial llamada FOUND, que es de tipo boolean. FOUND comienza en false dentro de cada llamada a una función de PL/pgSQL. Se establece por cada uno de los siguientes tipos de instrucciones:

Otras instrucciones de PL/pgSQL no cambian el estado de FOUND. Ten en cuenta en particular que EXECUTE cambia la salida de GET DIAGNOSTICS, pero no cambia FOUND.

FOUND es una variable local dentro de cada función de PL/pgSQL; cualquier cambio en ella afecta únicamente a la función actual.

A veces resulta útil una instrucción de marcador de posición que no hace nada. Por ejemplo, puede indicar que una de las ramas de una cadena if/then/else está vacía deliberadamente. Para este propósito, utiliza la instrucción NULL:

NULL;

Por ejemplo, los siguientes dos fragmentos de código son equivalentes:

BEGIN
    y := x / 0;
EXCEPTION
    WHEN division_by_zero THEN
        NULL;  -- ignorar el error
END;

BEGIN
    y := x / 0;
EXCEPTION
    WHEN division_by_zero THEN  -- ignorar el error
END;

Cuál es preferible es una cuestión de gusto.