En PostgreSql a diferencia de otros motores de bases de datos, no existe una distinción explicita entre una función y un procedimiento almacenado. En PostgreSql solo existen funciones, claro que estas pueden ser usadas a modo de una función o de un procedimiento almacenado. Además de esta diferencia con otros motores de bases de datos, es importante mencionar que PostgreSql nos ofrece más de un lenguaje para crear nuestros procedimientos almacenados, pudiendo elegir entre los siguientes lenguajes:

En lo personal siempre me he inclinado por Pl/PgSQL pues es un lenguaje procedural basado en SQL y que sigue el estándar ANSI SQL. Si deseas conocer más de Pl/PgSQL aquí puedes encontrar la documentación oficial.

En este artículo veremos cómo se crean funciones en PostgreSql que nos devuelven un recordset y para ello usaremos la misma lógica del procedimiento almacenado creado en Mysql para el artículo “Procedimientos almacenados de Mysql desde Excel Con VBA y ADO“. Y de igual forma al procedimiento en mysql, crearemos una función en PostgreSql que nos devuelva un recordset conteniendo una lista de países, esto claro, dependiendo del valor pasado a través de un parámetro de entrada.

 

Para crear la función, ejecutaremos pgAdmin III

 

Y una vez autenticados en nuestro servidor y seleccionada la base de datos en que trabajaremos ejecutaremos el editor de consultas SQL que se encuentra en la barra de herramientas:

 

Y se nos mostrará el editor de consultas, en el que podremos ejecutar tanto código PLSQL como SQL.

 

Una vez abierto nuestro editor el primer paso será crear la tabla lista_paises y para ello deberemos ejecutar el siguiente bloque SQL, modificando antes el userpostgre por el usuario que usas en tu servidor.

CREATE TABLE lista_paises
(
  id integer DEFAULT 0,
  opcion character varying(100) DEFAULT ''::character varying
)
WITH (
  OIDS=FALSE
);
ALTER TABLE lista_paises
  OWNER TO userpostgre;

Ya que tenemos la tabla comenzaremos a crear nuestra función. Para ello primero definimos el encabezado, y aquí es importante mencionar que en este caso, donde vamos a devolver un recordset, deberemos declarar tantos parámetros de salida como columnas tenga nuestra consulta:

CREATE OR REPLACE FUNCTION SP_DamePaises
   ( IN PV_OPCION VARCHAR(10) default 'DAMETODOS',
     OUT columna1 int, 
     OUT columna2 varchar(100)
   ) RETURNS setof record AS

Dado que la función devolverá un recordset conteniendo las columnas id y opción, hemos agregado ambos parámetros de salida a la función y hemos agregado el parámetro de entrada Pv_Opcion que usaremos a manera de menú de selección para decirle a la función que bloque deberá ejecutar. Al final del encabezado establecemos el tipo de dato que devolverá la función que en este caso será un recordset o setof  record.

Declarado el encabezado vamos a continuar con el cuerpo de la función que contendrá las operaciones a realizar:

$BODY$
begin
 IF PV_OPCION = 'DAMETODOS' THEN
   return query select id, opcion from lista_paises;
 END IF;  
 IF PV_OPCION = 'DAMECINCO' THEN
   return query select id, opcion from lista_paises limit 5;
 END IF;  
return;
end;
$BODY$
LANGUAGE 'plpgsql';

Como podemos ver, el código es muy parecido al usado en Mysql, con las variantes de que aquí debemos establecer con return query el recordset a devolver, además de tener que indicar el lenguaje a usar y el inicio y fin del cuerpo de la función.

Para  ejecutar el procedimiento almacenado ejecutaremos la sentencia que nos devolverá el listado:

Select * From SP_DamePaises('DAMETODOS');

Una vez que lo hayamos ejecutado obtendremos el siguiente resultado:

 

Y para terminar aquí está  el bloque completo de la función:

CREATE OR REPLACE FUNCTION SP_DamePaises
   ( IN PV_OPCION VARCHAR(10) default 'DAMETODOS',
     OUT columna1 int, 
     OUT columna2 varchar(100)
   ) RETURNS setof record AS
$BODY$
begin
 IF PV_OPCION = 'DAMETODOS' THEN
   return query select id, opcion from lista_paises;
 END IF;  
 IF PV_OPCION = 'DAMECINCO' THEN
   return query select id, opcion from lista_paises limit 5;
 END IF;  
return;
end;
$BODY$
LANGUAGE 'plpgsql';

 

Y el video que muestra cómo crear el procedimiento en postgresql:

 

 

Todo comentario o sugerencia será muy apreciado.

19 Thoughts on “Procedimientos almacenados en PostgreSql

  1. Pingback: » Ejecutar Procedimientos almacenados de PostgreSql con Delphi y ADO

  2. Pingback: » Ejecutar procedimientos almacenados de PosgreSql con Php

  3. Antony Stephen on December 4, 2012 at 4:36 am said:

    CREATE OR REPLACE FUNCTION ctotal
    (IN _campana integer,
    OUT columna1 character varying(20),
    OUT columna2 character varying(250),
    OUT columna3 bigint
    )
    RETURNS setof record AS
    $BODY$

    BEGIN
    return query select a.codcliente as codcliente,max(cl.nombrecompleto) as nombrecompleto,max(a.idasignaciones) as idasignaciones from asignaciones a left join cliente cl on a.codcliente = cl.codcliente left join cuentas c on a.codcliente = c.codcliente left join gestion g on a.codcliente = g.codcliente where a.idcampana = _campana group by a.codcliente;
    END;
    $BODY$
    LANGUAGE ‘plpgsql';

    Me muestra este error:

    Error de SQL:

    ERROR: structure of query does not match function result type
    DETAIL: Returned type text does not match expected type character varying in column 2.
    CONTEXT: PL/pgSQL function “ctotal” line 4 at RETURN QUERY

    En la declaración:
    select ctotal(’42’);

    por favor ayudame man soy nuevo es postgresql pero me interesa mucho aprender. gracias de antemano

    • Por el mensaje de error al parecer la manera en que declaraste los parámetros de salida no se corresponde con tus columnas. Prueba cambiando character varying(20), por Varchar(20)

      Por cierto. En http://www.delphiaccess.com puedes postear dudas :D allí entre yo y algunos amigos mios podemos ayudarte con todas las dudas que tengas.

      Saludox

  4. armando gomez on February 25, 2013 at 12:49 am said:

    Muchas gracias por la iniciación al tema de procedimientos almacenados. El resto debe ir por nuestra cuenta.
    El ejemplo claro, sencillo y funcional.

  5. Pingback: » DB2 – Procedimientos almacenados que devuelven recordsets

  6. quisiera saber si se puede, ¿como puedo llamar un procedimiento almacenado o funcion de postgreSQL con visual Studio C#?
    de antemano muchas gracias

  7. Pingback: SQL SERVER – Crear un procedimiento almacenado | Blog del Poli

  8. Hola, cree mi funcion correctamente, pero quisiera probarla, hay una mander de hacerlo directamente desde el manejador de base de datos?
    estoy usando STORMDB.
    Saludos!

    • Claro, sin importar qué uses para conectarte, al ejecutar la sentencia Select * from Funcion estarás ejecutando la función desde tu manejador.

      Saludos

  9. Pingback: Access – Ejecutar procedimientos almacenados de PostgreSql (Con vistas) | Blog del Poli

  10. Antonio Padron on May 8, 2014 at 5:53 pm said:

    Hola como estas excelente tu video y instructivo, pero necesito preguntarte, como puedo hacer un procedimiento de almacenado haciendo un cruce de 3 tablas e insertando en una 4°.

    • como no conozco a detalle lo que requieres, sugiero hacer algo como esto:


      insert
      into tabla4
      select columnas
      from tabla1
      left join tabla2
      on condicionesjoin
      left join tabla3
      on condicionesjoin

  11. Buenas, excelente explicación!
    Tengo una duda, copie y pegue todo el codigo y el resultado es el mismo, excepto que en vez de crear “columna 1″ y “columna 2″ me devuelve todo en una sola columna “record” y los datos dentro de parentesis separados por comas. Como puedo hacer para que salgan en columnas diferentes? GRACIAS!!

  12. name on June 14, 2014 at 6:40 am said:

    si tengo procedimientos almacenados ya en postgresql como los llamo desde php?

  13. EfrainMejiasC on November 11, 2014 at 8:06 pm said:

    Hola
    Como seria una funcion para conocer el valor devuelto de esta sentencia
    SELECT SUM (“CANTIDAD”) FROM “CLIENTE”

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Post Navigation