Ejemplos

Para ilustrar una función SQL sencilla, considere lo siguiente, que se podría usar para cargar en una cuenta bancaria:

create function TP1 (int4, float8) returns int4
    as 'update BANK set balance = BANK.balance - $2
        where BANK.acctountno = $1
        select(x = 1)'
    language 'sql';
     

select (x = TP1( 17,100.0));
     

El más interesante ejemplo siguiente toma una argumento sencillo de tipo EMP, y devuelve resultados múltiples:

select function hobbies (EMP) returns set of HOBBIES
    as 'select (HOBBIES.all) from HOBBIES
        where $1.name = HOBBIES.person'
    language 'sql';
     

Funciones SQL sobre Tipos Base

La función SQL más simple posible no tiene argumentos y sencillamente devuelve un tipo base, tal como int4:

    CREATE FUNCTION one() RETURNS int4
     AS 'SELECT 1 as RESULT' LANGUAGE 'sql';

    SELECT one() AS answer;

         +-------+
         |answer |
         +-------+
         |1      |
         +-------+
     

Notar que definimos una lista objetivo para la función (con el nombre RESULT), pero la lista objetivo de la consulta que llamó a la función sobreescribió la lista objetivo de la función. Por esto, el resultado se etiqueta answer en vez de one.

Es casi tan fácil definir funciones SQL que tomen tipos base como argumentos. En el ejemplo de abajo, note cómo nos referimos a los argumentos dentro de la función como $1 y $2:

    CREATE FUNCTION add_em(int4, int4) RETURNS int4
     AS 'SELECT $1 + $2;' LANGUAGE 'sql';

    SELECT add_em(1, 2) AS answer;

         +-------+
         |answer |
         +-------+
         |3      |
         +-------+
     

Funciones SQL sobre Tipos Compuestos

Al especificar funciones con argumentos de tipos compuestos (tales como EMP), debemos no solo especificar qué argumento queremos (como hicimos más arriba con $1 y $2) sino también los atributos de ese argumento. Por ejemplo, observe la función double_salary que procesa cual sería su salario si se doblase:

    CREATE FUNCTION double_salary(EMP) RETURNS int4
     AS 'SELECT $1.salary * 2 AS salary;' LANGUAGE 'sql';

    SELECT name, double_salary(EMP) AS dream
     FROM EMP
     WHERE EMP.cubicle ~= '(2,1)'::point;
     

         +-----+-------+
         |name | dream |
         +-----+-------+
         |Sam  | 2400  |
         +-----+-------+
     

Note el uso de la sintaxis $1.salary. Antes de adentrarnos en el tema de las funciones que devuelven tipos compuestos, debemos presentar primero la notación de la función para proyectar atributos. La forma sencilla de explicar esto es que podemos normalmente usar la notación atributo(clase) y clase.atributo indistintamente:

    --
    -- esto es lo mismo que:
    --  SELECT EMP.name AS youngster FROM EMP WHERE EMP.age < 30
    --
    SELECT name(EMP) AS youngster
     FROM EMP
     WHERE age(EMP) < 30;

         +----------+
         |youngster |
         +----------+
         |Sam       |
         +----------+
     

Como veremos, sin embargo, no siempre es este el caso. Esta notación de función es importante cuando queremos usar una función que devuelva una única instancia. Hacemos esto embebiendo la instancia completa dentro de la función, atributo por atributo. Esto es un ejemplo de una función que devuelve una única instancia EMP:

    CREATE FUNCTION new_emp() RETURNS EMP
     AS 'SELECT \'None\'::text AS name,
      1000 AS salary,
      25 AS age,
       \'(2,2)\'::point AS cubicle'
      LANGUAGE 'sql';
     

En este caso hemos especificado cada uno de los atributos con un valor constante, pero cualquier computación o expresión se podría haber sustituido por estas constantes. Definir una función como esta puede ser delicado. Algunos de las deficiencias más importantes son los siguientes:

• La orden de la lista objetivo debe ser exactamente la misma que aquella en la que los atributos aparezcan en la orden CREATE TABLE (o cuando ejecute una consulta .*).

• Se debe encasillar las expresiones (usando ::) muy cuidadosamente o verá el siguiente error:

  WARN::function declared to return type EMP does not retrieve (EMP.*)

• Al llamar a una función que devuelva una instancia, no podemos obtener la instancia completa. Debemos o bien proyectar un atributo fuera de la instancia o bien pasar la instancia completa a otra función.

  SELECT name(new_emp()) AS nobody; +-------+ |nobody | +-------+ |None | +-------+

• La razón por la que, en general, debemos usar la sintaxis de función para proyectar los atributos de los valores de retorno de la función es que el parser no comprende la otra sintaxis (dot) para la proyección cuando se combina con llamadas a funciones.

  SELECT new_emp().name AS nobody; WARN:parser: syntax error at or near "."

Cualquier colección de ordenes en el lenguaje de consulta SQL se pueden empaquetar juntas y se pueden definir como una función. Las ordenes pueden incluir updates (es decir, consultas INSERT, UPDATE, y DELETE) así como SELECT. Sin embargo, la orden final debe ser un SELECT que devuelva lo que se especifique como el tipo de retorno de la función.

    CREATE FUNCTION clean_EMP () RETURNS int4
     AS 'DELETE FROM EMP WHERE EMP.salary <= 0;
    SELECT 1 AS ignore_this'
     LANGUAGE 'sql';

    SELECT clean_EMP();

         +--+
         |x |
         +--+
         |1 |
         +--+