Parte de definir un tipo nuevo es la definici�n de funciones que describen su comportamiento. Como consecuencia, mientras que es posible definir una nueva funci�n sin definir un tipo nuevo, lo contrario no es cierto. Por ello describimos como a�adir nuevas funciones para Postgres antes de describir c�mo a�adir nuevos tipos.
Postgres SQL proporciona tres tipos de funciones:
funciones de lenguaje de consultas (funciones escritas en SQL)
funciones de lenguaje procedural (funciones escritas en, por ejemplo, PLTCL o PLSQL)
funciones de lenguaje de programaci�n (funciones escritas en un lenguaje de programaci�n compilado tales como C)
Las funciones SQL ejecutan una lista arbitraria de consultas SQL, devolviendo los resultados de la �ltima consulta de la lista. Las funciones SQL en general devuelven conjuntos. Si su tipo de retorno no se especifica como un setof, entonces un elemento arbitrario del resultado de la �ltima consulta ser� devuelto.
El cuerpo de una funci�n SQL que sigue a AS deber�a ser una lista de consultas separadas por caracteres espacio en blanco y entre par�ntesis dentro de comillas simples. Notar que las comillas simples usadas en las consultas se deben escribir como s�mbolos de escape, precedi�ndolas con dos barras invertidas.
Los argumentos de la funci�n SQL se pueden referenciar en las consultas usando una sintaxis $n: $1 se refiere al primer argumento, $2 al segundo, y as� sucesivamente. Si un argumento es complejo, entonces una notaci�n dot (por ejemplo "$1.emp") se puede usar para acceder a las propiedades o atributos del argumento o para llamar a funciones.
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'; |
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 | +-------+ |
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 | +--+ |