El lenguaje procedural plpython permite escribir funciones python para la base de datos relacional PostgreSQL.
Como se puede acceder a todas las funciones de python, no debe usarse para usuarios no confiados, por eso se lo llama plpythonu (u=untrusted).
Para más información ver Sitio del Grupo de Usuarios de PostgreSQL Argentina, sección PlPython
Funciones ("Procedimientos almacenados")
El cuerpo de una funcion plpythonu es simplemente un script de Python. Cuando la función es llamada, sus argumentos son pasados como elementos de una lista args; los argumentos por nombre son pasados como variables ordinarias. El resultado es devuelto de la manera usual, con un return o un yield (en el caso que devuelvan un conjunto de resultados)
Los valores NULL de PostgreSQL equivalen a None en Python.
Está disponible el diccionario SD para almacenar datos entre cada llamada a función, y el diccionario globar GD para usar desde todas las funciones.
Nota: PostgreSQL 8.1 no soporta argumentos por nombre, recibir valores compuestos, devolver listas/tuplas o usar generadores.
Ejemplo simple
Calcular el valor máximo entre dos enteros, descartando valores nulos:
CREATE FUNCTION pymax (a integer, b integer) RETURNS integer AS $$ if (a is None) or (b is None): return None if a > b: return a return b $$ LANGUAGE plpythonu; -- invoco la función: SELECT pymax(2, 3); -- devuelve 3
Recibir tipos compuestos
Las funciones plpython pueden recibir tipos compuestos (ej.registros de tablas) como diccionarios:
CREATE TABLE empleado ( nombre TEXT, salario INTEGER, edad INTEGER ); CREATE FUNCTION sueldo_alto (e empleado) RETURNS boolean AS $$ if e["salario"] > 200000: return True if (e["edad"] < 30) and (e["salario"] > 100000): return True return False $$ LANGUAGE plpythonu;
Devolver tipos compuestos
Los tipos compuestos pueden ser devueltos como secuencias (tuplas o listas), diccionarios u objetos. En este ejemplo se devuelve un tipo compuesto representando una persona:
CREATE TYPE persona AS ( nombre TEXT, apellido TEXT ); CREATE FUNCTION crear_persona (nombre TEXT, apellido TEXT) RETURNS persona AS $$ return [ nombre, apellido ] # o como tupla: return ( nombre, apellido ) # o como diccionario: return { "nombre": nombre, "apellido": apellido } $$ LANGUAGE plpythonu; CREATE FUNCTION crear_persona (nombre TEXT, persona TEXT) RETURNS persona AS $$ class Persona: def __init__ (self, n, a): self.nombre = n self.apellido = a return Persona(nombre, apellido) $$ LANGUAGE plpythonu;
Devolver múltiples tipos escalares o compuestos (''set-of'')
Se puede devolver múltiples valores (usando listas/tuplas, iteradores o generadores). En este ejemplo se devuelven varios saludos:
CREATE TYPE saludo AS ( mensaje TEXT, -- hola a_quien TEXT -- mundo ); CREATE FUNCTION saludar (mensaje TEXT) RETURNS SETOF saludo AS $$ # devolver una tupla conteniendo lista de tipos compuestos # todas las otras combinaciones son posibles return ( [ mensaje, "Mundo" ], [ mensaje, "PostgreSQL" ], [ mensaje, "PL/Python" ] ) $$ LANGUAGE plpythonu; CREATE FUNCTION saludar_generador (mensaje TEXT) RETURNS SETOF saludo AS $$ for a_quien in [ "Mundo", "PostgreSQL", "PL/Python" ]: yield ( mensaje, a_quien ) $$ LANGUAGE plpythonu;
Disparadores (Triggers)
Cuando una función plpython es usada en un disparador, el diccionario TD contiene:
- TD["new"]: valores nuevos de la fila afectada (diccionario)
- TD["old"]: valores viejos de la fila afectada (diccionario)
- TD["event"]: tipo de evento "INSERT", "UPDATE", "DELETE", o "UNKNOWN"
- TD["when"]: momento en que se ejecutó: "BEFORE" (antes del commit), "AFTER" (despues del commit), o "UNKNOWN"
- TD["level"]: nivel al que se ejecutó: "ROW" (por fila), "STATEMENT" (por sentencia), o "UNKNOWN"
- TD["name"]: nombre del disparador
- TD["table_name"]: nombre de la tabla en que se disparó
- TD["table_schema"]: esquema en el que se disparó
- TD["relid"]: OID de la tabla que disparó
- Si el comando CREATE TRIGGER incluyó argumentos, estos estarán disponibles en la lista TD["args"]
Si TD["when"] es BEFORE, se puede devolver None or "OK" para indicar que la fila no se modificó, "SKIP" para abortar el evento, o "MODIFY" para indicar que hemos modificado la fila.
Acceso a la base de datos
Automaticamente se importa un módulo llamado plpy.
Generar mensajes y lanzar errores
Este módulo incluye funciones de plpy.debug(msg), plpy.log(msg), plpy.info(msg), plpy.notice(msg), plpy.warning(msg), plpy.error(msg), y plpy.fatal(msg)
plpy.error y plpy.fatal en realidad disparan una excepción python, si no se controla, se propaga y causa que la transacción se aborte. Equivalente a llamar raise plpy.ERROR(msg) y raise plpy.FATAL(msg), respectivamente
Las otras funciones solo generan mensajes en los distintos niveles de prioridad.
Preparar y ejecutar consultas
Adicionalmente, el módulo plpy provee dos funciones: execute y prepare.
Llamar a plpy.execute(query, limit) con una consulta (query: string) y un límite de registros opcional (limit), permite ejecutar la consulta y devuelve los resultados en un objeto que emula una lista de diccionarios, pudiendo acceder por número de fila y nombre de columna. Tiene tres métodos adicionales: nrows que devuelve el número de filas, y status.
Ejemplo:
rv = plpy.execute("SELECT * FROM mi_tabla", 5) for fila in rv: print fila['columna']
La función plpy.prepare(query,[parameter_types]), prepara el plan de ejecución para una consulta, se le pasa la consulta como string y la lista de tipos de parámetros:
plan = plpy.prepare("SELECT apellido FROM usuario WHERE nombre = $1 AND casado = $2 ", [ "text", "boolean" ])
text y boolean son los tipos de la variables que se pasara como parámetros ($1 y $2).
Despues de preparar la sentencia, usar la función plpy.execute para ejecutarla:
rv = plpy.execute(plan, [ "Mariano", True ], 5)
Se pasa el plan como primer argumento, los parámetros como segundo (en este caso, busca nombre="Mariano" y si esta casado). El límite (tercer argumento) es opcional.
Al preparar un plan, este se almacena para usarlo posteriormente. Para usarlo eficazmente entre llamada y llamada, se debe usar un diccionario de almacenamiento persistente (SD o GD) para guardarlo:
CREATE FUNCTION usar_plan_guardado() RETURNS trigger AS $$ if SD.has_key("plan"): plan = SD["plan"] # está el plan, lo reutilizo else: # no esta el plan, lo creo y almaceno en el diccionario persistente plan = plpy.prepare("SELECT 1") SD["plan"] = plan # continua la función... $$ LANGUAGE plpythonu;