Pl/Python: Python dentro de PostgreSQL

El lenguaje procedural plpython permite escribir funciones python para la base de datos relacional PostgreSQL.

Python es un lenguaje simple, moderno y flexible, fácil de aprender y usar, que posibilita el diseño rápido de todo tipo de aplicaciones multiplataforma, ya sea en sistemas de gestión comercial como juegos o aplicaciones científicas

Para información sobre como instalarlo, ver PreguntasFrecuentes

Como se puede acceder a todas las funciones de python, no debe usarse para usuarios no confiados, por ello la u en plpythonu (u=untrusted).

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;

Ejemplos Prácticos

Analizador de direcciones

En este ejemplo completo, se utiliza una función para analizar y desglozar una dirección ingresada como un texto (string), en tres campos: calle1 (principal), calle2 (transversal) y altura (nº en la calle principal).

Para ello creamos el tipo direccion para contener estos campos desglozados que devuelve nuestra función:

CREATE TYPE direccion AS (
  calle1 TEXT,
  calle2 TEXT,
  altura  INTEGER
);

Luego creamos la funcion analizar_dir con el código python propiamente dicho:

CREATE OR REPLACE FUNCTION analizar_dir(dir text)
  RETURNS direccion AS
$BODY$
    def is_numeric(x):
        return not [y for y in x if not '0'<=x<='9']
    nombres = ["","",""]
    altura = ""
    ss = dir.strip() + " "
    calle = 0 # comienzo a procesar por la primer calle;
    n = p = ""
    for c in ss:
        if c in (' ', '.', ',', '/', '-'):
            # procesar separador:
            if n:
                if nombres[calle].strip().lower() in ('ruta', 'r.p.', 'rp', 'r.n.', 'rn'):
                    # tomar el numero de ruta (nacional o provincial)
                    nombres[calle] += n + ' '
                    n = ''
                else:
                    # tomar el numero como altura
                    altura = n
                    n = ""
                    calle+=1
            elif p.lower() in ('y', 'e', 'entre', 'u') or c in ('/', '-') or p[0:3]=='esq':
                # la palabra es separador de calles
                if nombres[calle]: # pasar a la siguiente calle
                    calle+=1
            elif p.lower() not in ('calle', 'avenida','al','n?','nro') and c not in ('.',) and (p or is_numeric(p)):
                # agregar la palabra como parte de esta calle
                nombres[calle] += p + " "
            p = ""
        elif '0' <= c <= '9':
            if calle==0 and nombres[calle]:
                # agregar el caracter como parte del numero
                n += c
            else:
                # agregar el caracter como parte de la palabra actual
                p += c
        else:
            # agrego el caracter a la palabra actual
            cc = c.lower()
            if ('a'<=cc<='z' or '0'<=cc<='9'):
                p += c;
            else:
                p += "?" # comodin de una letra por las dudas (si no es un caracter numerico o alfa)
        if calle>2:
            break

    # devolver las dos primeras calles (si hay) y la altura
    return nombres[0], nombres[1], altura and int(altura[-6:]) or None
$BODY$
  LANGUAGE 'plpythonu' IMMUTABLE;

Para ejecutar la función, podemos simplemente llamarla desde la clausula from ya que devuelve un tipo de datos compuesto:

select calle1, calle2, altura from analizar_dir('balcarce 50 esquina rivadavia')

Lo que nos devolverá:

calle1 textcalle2 textaltura integer
balcarcerivadavia50

También se la puede llamar desde una consulta analizando datos de una tabla (ej. campo lugar de novedades):

SELECT (d.dir).calle1, (d.dir).calle2, (d.dir).altura 
FROM (SELECT analizar_dir(lugar) AS dir FROM novedades) d

Temas varios

Error con generadores y plpy.execute

Si se usa un generador (yield) para devolver filas (setof) y al mismo tiempo se usa plpy.execute:

CREATE OR REPLACE FUNCTION mi_funcion2() RETURNS SETOF mi_tabla AS $BODY$
plpy.execute("select 1")
yield [None, None, None]
$BODY$ LANGUAGE 'plpythonu' VOLATILE;
SELECT * FROM mi_funcion2()

Se producirá un error:

********** Error **********

ERROR: error fetching next item from iterator
SQL state: 22000

Utilizar return en lugar de yield

Ejemplo de Agregados

CREATE OR REPLACE FUNCTION py_disp()
  RETURNS trigger AS
$$
plpy.notice("Campo 1 = %s" % TD["new"]["campo1"])
if TD["new"]["campo1"] == "":
   raise RuntimeError("El campo no puede ser vacio")
$$ language plpythonu;

create table tabla_prueba (campo1 text)

CREATE TRIGGER disp_prueba
  BEFORE INSERT OR UPDATE OR DELETE
  ON tabla_prueba
  FOR EACH ROW
  EXECUTE PROCEDURE py_disp();

INSERT INTO tabla_prueba VALUES ('')



CREATE OR REPLACE FUNCTION first_agg(text, text) RETURNS text AS
$BODY$ 
if not args[0]: 
  return args[1] 
else: 
  return args[0] $BODY$
LANGUAGE 'plpythonu' VOLATILE COST 100;

CREATE AGGREGATE first (text) ( sfunc = first_agg, stype = text );
select * from part;
select price, first(pname) , sum(stock), count(pno) from part GROUP BY PRICE;
UPDATE part set price=15 where pno in (2, 4)

Ejemplo de Disparador

CREATE OR REPLACE FUNCTION py_disp()
  RETURNS trigger AS
$$
plpy.notice("Campo 1 = %s" % TD["new"]["campo1"])
if TD["new"]["campo1"] == "":
   raise RuntimeError("El campo no puede ser vacio")
$$ language plpythonu;

create table tabla_prueba (campo1 text)

CREATE TRIGGER disp_prueba
  BEFORE INSERT OR UPDATE OR DELETE
  ON tabla_prueba
  FOR EACH ROW
  EXECUTE PROCEDURE py_disp();

INSERT INTO tabla_prueba VALUES ('hola');

INSERT INTO tabla_prueba VALUES ('');