lunes, 26 de abril de 2010

Uso de funciones PL/SQL en postgreSQL

Una función (function) en PostgreSQL son sentencias SQL agrupadas y precompiladas para ejecutarse en bloque dentro del servidor, a diferencia de las consultas SQL donde cada consulta es procesada en tiempo de ejecución por el servidor , las funciones procedurales son compilados cuando son creados, ya que el servidor asume que serán ejecutados más de una vez, un función ofrece las siguientes ventajas:

  • No sobrecarga la comunicación cliente/servidor al evitar enviar una consulta tras otra, en su lugar procesa una consulta tras otra y envía únicamente el resultado.

  • Cuando y se ejecuta la primera vez se crea un plan preparado de ejecución, las siguientes ejecuciones reutilizan el plan preparado.

  • Agrega estructuras de control y capacidad de calculo al lenguaje SQL.

  • Las mismas consultas están disponibles para varias aplicaciones.

  • Seguridad los datos solo estan accesibles mediante las funciones y evita el uso de SQL injection.



De los lenguajes más utilizados para crear funciones en postgreSQL, se encuentra PL/pgSQL, el cual se distribuye como un módulo cargable junto con postgreSQL, para emplearlo en nuestra base de datos es necesario darlo de alta, de la siguiente manera.




Revisamos si ya lo tenemos disponible en nuestra base de datos para utilizarlo
La sintaxis de PL/pgSQL (similar al lenguaje PL/SQL de Oracle)



Utilizando PL/pgSQL con C#


En este ejemplo usaremos PL/pgSQL y C# para resolver un requerimiento practico como seria relacionar la columna city de nuestra tabla authors en nuestra base de datos con una tabla llamada cities donde se encontrará la información de la columna ciudad mas un identificador.




La relación deberá de quedar de la siguiente manera, donde la columna city se debe cambiar por la clave primaria de la tabla cities que tendrá como clave primaria la clave de la ciudad y una columna adicional llamada city que contendrá el nombre de la ciudad.



CREATE TABLE cities(
idcity varchar(5) PRIMARY KEY,
city varchar(20) NOT NULL UNIQUE
);

Ahora usamos la siguiente funciónfunction para tomar los valores de la columna city en la tabla authors, crear un identificador único para la llave primaria, insertar ese valor de clave primaria junto con el nombre de la ciudad y por último sustituir los valores en la columna city y reemplazarlos con el valor de la llave primaria en la tabla cities.

CREATE FUNCTION AddCities(varchar) RETURNS VARCHAR AS
'DECLARE
hay record;
nume varchar;
BEGIN
SELECT INTO hay count(city) FROM cities WHERE city = $1;
IF hay.count = 0
THEN
INSERT INTO cities(idcity,city)
VALUES(substring($1,1,3) || substring(random() * 1000,1,2),$1);
SELECT INTO nume idcity FROM cities WHERE city = $1;
UPDATE authors SET city = nume WHERE city = $1;
RETURN nume;
END IF;
IF hay.count > 0
THEN
SELECT INTO nume idcity FROM cities WHERE city = $1;
UPDATE authors SET city = nume WHERE city = $1;
RETURN nume;
END IF;
END;
'
LANGUAGE 'plpgsql';

Guardamos nuestra función en un archivo llamado store1.sql y lo ejecutamos para crearla dentro del servidor.



Una vez creada nuestra función mostraremos como emplearla desde C# con el siguiente:



using System;
using Gtk;
using System.Text;
using System.Data;
using Npgsql;
using NpgsqlTypes;

namespace PgForm {
class PgForm : Window {
Label lbMsg = new Label("DEBUG: ");
Entry txtNameFunction = new Entry();
Entry txtArg = new Entry();

public PgForm() : base("Ejecutar funciones"){
BorderWidth = 8;
SetDefaultSize(208,220);
this.DeleteEvent += new DeleteEventHandler(OnWindowDelete);
Frame frame = new Frame ("Ejecutar funciones");
Add (frame);
VBox MainPanel = new VBox (false, 8);
MainPanel.BorderWidth = 8;
frame.Add (MainPanel);
MainPanel.PackStart(new Label("Nombre de la funcion"),false,false,0);
MainPanel.PackStart(txtNameFunction,false,false,0);
MainPanel.PackStart(new Label("Si recibe un argumento de tipo varchar")
,false,false,0);
MainPanel.PackStart(txtArg,false,false,0);
Button btnSubmit = new Button("Ejecutar funcion");
btnSubmit.Clicked += new EventHandler(btnSubmitClicked);
MainPanel.PackStart(btnSubmit,false,false,0);
MainPanel.PackStart(lbMsg,false,false,0);
lbMsg.LineWrap = true;
ShowAll();
}

public void OnWindowDelete(object o, DeleteEventArgs args) {
Application.Quit();
}

void btnSubmitClicked(object o, EventArgs args){
string cStr = "Server=127.0.0.1;Port=5432;User Id=postgres;" +
"Password=postgres;Database=pubs;";
try{
using(NpgsqlConnection conn = new NpgsqlConnection(cStr))
{
conn.Open();
NpgsqlCommand cmd = new NpgsqlCommand(txtNameFunction.Text, conn);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
//en caso de tener un parametro como en nuestra funcion de ejemplo
if(txtArg.Text.Length > 0){
cmd.Parameters.Add(new NpgsqlParameter());
cmd.Parameters[0].NpgsqlDbType = NpgsqlDbType.Varchar;
cmd.Parameters[0].Value = txtArg.Text;
}
lbMsg.Text += cmd.ExecuteScalar().ToString();
}}catch(Exception e){
lbMsg.Text += e.Message;
}
}

static void Main(string[] args) {
Application.Init();
new PgForm();
Application.Run();
}}
}

Compilamos y ejecutamos.

Podemos probar nuestro programa invocando la funcion version() predeterminada de PostgreSQL.

Al ejecutar la función sin argumentos desde el formulario se vera el mismo resultado.

Aquí el driver de PostgreSQL para .NET ejecuta la función usando la clase

NpgsqlCommand
la cual recibe como argumento el nombre de la función y la conexión al servidor donde se encuentra.
NpgsqlCommand cmd = new NpgsqlCommand(“version”, conn);

Si la función recibe parametros, debemos de crear una instancia de la clase
NpgsqlParameter()
por cada uno de los parametros que reciba, es muy importante no olvidar indicarle a la clase
NpgsqlCommand
que el comando que ejecutaremos es un stored procedure o una función pl/sql, esto lo hacemos mediante la instrucción:
cmd.CommandType = System.Data.CommandType.StoredProcedure;

Para mayor referencia no olvidar leer la documentación del data provider para PostgreSQL.
Si todo es ejecutado correctamente, ya podemos probar la función AddCities con el argumento del nombre de la ciudad y debe devolvernos la clave primaria de la tabla cities.



 Descarga el código fuente