Función que cuenta días laborables

Saludos a todos, después un largo tiempo de para vuelvo a escribir un tip para poder obtener los días laborales en PostgreSQL 9.1, lo que os muestro es una modificación de una función existente.

Gracias a mi amigo Javier Altamirano por los datos y parte de la función.

Funcionalidades:

• Actual: Reconoce sábados y domingos.
• Modificación: Reconocer días no laborables decretados por el estado peruano.

Sintaxis:

             días = dbcont_diaslab(fechaInicio, fechaFin)

Uso:

            select dbcont_diaslab('2013-06-23','2013-06-29') 

Pueden usar CURRENT_DATE.
           
Código:

CREATE OR REPLACE FUNCTION dbcont_diaslab(date, date)
  RETURNS bigint AS
$BODY$
SELECT count(*) FROM
(SELECT extract('dow' FROM $1+x) AS dow
FROM generate_series(0,$2-$1) x WHERE $1+x NOT IN (SELECT fe_no_labo FROM dias_no_labo WHERE extract('dow' FROM fe_no_labo) NOT IN(0,6))) AS foo
WHERE dow BETWEEN 1 AND 5;
$BODY$
  LANGUAGE sql VOLATILE
  COST 100;
ALTER FUNCTION dbcont_diaslab(date, date) OWNER TO postgres;

Nota:

En la tabla dias_no_labo se cuenta con registros de feriados nacionales y del sector público de Perú, sábados y domingos del 2013.

Los sábados y domingos no los tengo para realizar el cálculo, por que ya lo estoy haciendo en la parte final de la función.

Descargar:

DIAS_NO_LABO.backup

Saludos.

Replica de base de datos en PostgreSQL

Saludos, en esta oportunidad veremos el tema de replicación de datos en PostgreSQL, para ello definamos algunos conceptos:

• Nodo.- Base de datos que se encuentra envuelta en el proceso de replicación entre los principales tenemos: nodo origen y nodo suscriptor.
• Replicación.- Proceso por el cual se desea mantener y copiar los datos de una base de datos de manera que estos datos son transportados y son almacenados.
• Replicación maestro.- Se encarga de transferir las modificaciones de forma asíncrona a los nodos suscriptores.

Software

El software que utilizaremos en esta oportunidad es Slony-I para windows, el cual permite la replicación basada en triggers de forma asíncrona. Utiliza un simple master para múltipes esclavos (nodos suscriptores).

Cada tabla o secuencia en el master podría ser replicada vía triggers remotos a los esclavos. Actulizaciones son realizadas en la base de datos y luegos son replicadas mediante eventos.

Una replicación basada en triggers usa on insert, on update, on delete en las tablas para mantener la replicación entre el nodo master y esclavo.

• Limitaciones
• Tablas deben de ser primarias o únicas.
• Solo tablas y secuencias son permitidas en replicación.
• Base de datos esclavas no pueden ser modificadas.
• Ventajas
• Usando Slony-I podemos actualizar postgres de una versión a otra sin tiempo muerto. 
• Desventajas
• No detecta fallo en la red.
• No se permiten usar cambios de DDL en la replicación de tablas mientras el servicio de Slony-I esta ejecutandose.

A continuación se muestran otras opciones de replicación a tener en cuenta:

Descargar slony desde: http://slony.info/ para windows, en linux tiene otro proceso que espero también ponerlo en otro post.

Importante es siempre tener en cuenta la documentación: http://slony.info/documentation/2.0/index.html

Topología de red (ejemplo)


Nodo maestro (192.168.1.34)
Nodo esclavo (192.168.1.63)

Instalación y configuración
1. Instalar Slony-I en el nodo maestro y esclavo, para esto lo podemos hacer desde el Stack Builder de PostgreSQL.
2. Configurar el fichero pg_hba.conf del nodo maestro y esclavo añadiendo la siguiente información:

#maestro
host    all         all         192.168.1.34/32          md5
#esclavo
host    all         all         192.168.1.63/32          md5

3. Seleccionar una base de datos de prueba yo utilizare la siguiente: HR.backup
4. Indicamos al nodo maestro y esclavo la ruta del software Slony, En PgAddmin Click en menú Archivo->Options, luego en la pestaña General, llenamos el campo Slony-I path con:

5. Configurar los puertos habilitados en el firewall de windows excepciones a los puertos de base de datos 5432.
6. En el nodo maestro crear un script que nos digan las tablas de la base de datos que queremos replicar:

#--
# define the namespace the replication system uses in our example it is
# slony_example
#--
cluster name = slony_example;

#--
# admin conninfo's are used by slonik to connect to the nodes one for each
# node on each side of the cluster, the syntax is that of PQconnectdb in
# the C-API - replace the parameters which are appropriate for your setup
#
# this section tells slonik how to connect to the various nodes when running
# this script.
# --

node 1 admin conninfo = 'dbname=hr host=192.168.1.34 user=postgres password= admin';
node 2 admin conninfo = 'dbname=hr host=192.168.1.63 user=postgres password= admin';

#--
# init the first node. Its id MUST be 1. This creates the schema
# _$CLUSTERNAME containing all replication system specific database
# objects.

#--

init cluster ( id=1, comment = 'Nodo Maestro');

#--
# this is an example of how to work around a table that has no primary key
# and is based on an example by Robert Treat. The history table referred to
# is one of the tables being replicated.
#
# Because the history table does not have a primary key or other unique
# constraint that could be used to identify a row, we need to add one.
# The following command adds a bigint column named
# _Slony-I_$CLUSTERNAME_rowID to the table. It will have a default value
# of nextval('_$CLUSTERNAME.s1_rowid_seq'), and have UNIQUE and NOT NULL
# constraints applied. All existing rows will be initialized with a
# number
#--
# Traducción: Esto solo es necesario usarlo si tienes una tabla que no tenga una llave primaria, procura hacer un buen diseño en tu base de datos.

#table add key (node id = 1, fully qualified name = 'public.mi_tabla_sin_llave_primaria');

#--
# Slony-I organizes tables into sets. The smallest unit a node can
# subscribe is a set. The following commands create one set containing
# all tables to be replicated. Tables not named here will not be replicated.
# The master or origin of the set is node 1. Note that the second id must be
# incremented for each table added.
#--

create set (id=1, origin=1, comment='aqui van todas mis tablas');

set add table (set id=1, origin=1, id=1, fully qualified name = 'public.employees',comment='mi tabla empleado');
set add table (set id=1, origin=1, id=2, fully qualified name = 'public.departments',comment='mi tabla departamento');

#--
# Create the second node (the slave) tell the 2 nodes how to connect to
# each other and how they should listen for events.
# we have to repeat the conninfo detils here, because these details are written into
# the slony database.
#--

store node (id=2, comment = 'Nodo Esclavo');
store path (server = 1, client = 2, conninfo = 'dbname=hr host=192.168.1.34 user=postgres password= admin');
store path (server = 2, client = 1, conninfo = 'dbname=hr host=192.168.1.63 user=postgres password= admin');


store listen (origin=1, provider = 1, receiver =2);
store listen (origin=2, provider = 2, receiver =1);

######################################################################################

7. Luego de creado copiar este scripts a c:\Program Files\PostgreSQL\8.3\bin con el nombre maestro.txt
8. Crear el el nodo esclavo el siguiente script para luego guardarlo en c:\Program Files\PostgreSQL\8.3\bin con el nombre esclavo.txt como sigue:

cluster name = slony_example;

node 1 admin conninfo = 'dbname=hr host=192.168.1.34 user=postgres password= admin';
node 2 admin conninfo = 'dbname=hr host=192.168.1.63 user=postgres password= admin';

subscribe set (id=1, provider=1,receiver=2,forward=yes);

9. En el nodo maestro y en línea de comando estando en el directorio c:\Program Files\PostgreSQL\8.3\bin ejecutar:

# slonik maestro.txt

Si todo sale bien no deberás ningún mensaje de error en consola de windows como resultado de ejecutar la sentencia anterior y poder ver configurado slony en el nodo maestro.

Ahora el comando también debe de haber creado el cluster en el nodo esclavo, para ello verificamos:

10. Desde el nodo subscriptor (esclavo) y en el directorio c:\Program Files\PostgreSQL\8.3\bin ejecutar:

# slonik esclavo.txt


Si hemos tenido éxito no deberemos tener ningún mensaje de error en pantalla.

11. En el nodo maestro y en línea de comando estando en el directorio c:\Program Files\PostgreSQL\8.3\bin ejecutar:

# slon slony_example "dbname=hr user=postgres password= admin"

Esta línea me permite iniciar la replicación en el maestro.

12. En el nodo esclavo y en línea de comando estando en el directorio c:\Program Files\PostgreSQL\8.3\bin ejecutar:

# slon slony_example "dbname=hr user=postgres password= admin" 

Esta línea me permite iniciar la replicación en el nodo esclavo.

Nota: las consolas abiertas tanto en el paso 11 y 12  no deberán cerrarse.

Pruebas

En el nodo maestro abrir la base de datos HR e ingresar un registro a la tabla employees como sigue:

Verificamos en el esclavo:

Terminamos, espero que les sirva.   ;)

ODBC PostgreSQL-Excel en Win7 64bits

Saludos el siguiente Post trata de como habilitar PostgreSQL para Exel, pero en un sistema windows de 64 bits, comencemos.

En primer lugar eh buscado odbc postgres para 64 bits y solo encontre la siguiente documentación mas importante http://code.google.com/p/visionmap/wiki/psqlODBC

Seguido probe el driver para conectarme a un PostgreSQL 8.3 de 32 bits, pero no funcionó.

Instalé el odbc para PostgresSQL 8.3 de 32 bits y cuando me dirigía a herramientas administrativas->Origenes de datos me daba con la sorpresa que no esta el driver instalado.

Así que encontre esto: Inicio->Ejecutar->C:\WINDOWS\SysWOW64\odbcad32.exe

Recién con este comando pude crear mi origen de datos como pueden ver:

Luego para seguir con el trabajo selecciono PostgreSQL Unicode con los siguientes datos:

Una vez creado mi conexión odbc paso a excel para hacer el vínculo de datos:

Selecciono DNS:

Luego mi origen de datos:

Luego selecciono la base de datos y la tabla, en este caso una vista de capitales del Perú:

Guardar el archivo de conexion de datos y finalizar.

Casi para terminar seleccionamos el modo de ver los datos en el libro, lo dejamos por defecto, para finalmente poder ver lo siguiente:

Nos vemos ;)

Servidor vinculado SQL Server 2005 y PostgreSQL

Pasos:
1. Descargarse e instalarse la version odbc mas actual para PostgreSQL de: http://www.postgresql.org/ftp/odbc/versions/msi/psqlodbc_08_04_0100.zip

2. Crear un oriden de datos de tipo “System DNS” en Panel de control->Herramientas administrativas->Origenes de datos ODBC, que conecte a su servidor PostgreSQL y nombrarlo por ejemplo “Pucallpa” (base de datos espacial con extensión PostGIS de ejemplo)

Figura 1: conexión ODBC a una base de datos PostgreSQL

3. Correr el siguiente código en el SQL Server Management Studio Express:

EXEC master.dbo.sp_addlinkedserver @server = N'Pucallpa', @srvproduct=N'Microsoft OLE DB Provider for ODBC Driver', @provider=N'MSDASQL', @datasrc='Pucallpa', @location='localhost', @catalog='pucallpa'

Este código es para añadir un servidor vinculado a SQL Server.

4. Correr el siguiente código en el SQL Server Management Studio Express: para asociar el login al servidor vinculado.

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'Pucallpa', @useself=N'False', @locallogin=NULL, @rmtuser='postgres', @rmtpassword='xxx'

5. Ejecutar el siguiente código SQL desde SQL Server 2005:

SELECT * FROM OpenQuery(Pucallpa, 'select cod_lote from lotes')

Algo que me pidieron hacer hace un tiempo como consulta fue: saber el numero de habitantes en los lotes que están a 500 metros de un mercado de la ciudad de Pucallpa (llamado Mercado Nro 02)

SELECT --a.codlote, a.radio, u.nro_habitantes

sum(nro_habitantes)

FROM OpenQuery(Pucallpa, 'select * from mercadolimit5001000') a

left join tb_unidad_catastral u on substring(u.ucnumero,7,8) = a.codlote

where a.radio = 500

Figura 2: Lotes a 500 mts del Mercado N° 2

Para saber cuantos lotes estan a una distancia de otro lote (en este caso el mercado n° 2) hago la siguiente consulta en PostgreSQL con extensión espacial PostGIS:

Figura 3: Consulta espacial en la base de datos “pucallpa” de PostgreSQL

select l.* from lotes l, lotes a where( st_distance(l.geometria, a.geometria)<=1000 and a.cod_lote in ('01040001', '01040002', '01040003', '01040004', '01040005'))

1. La tabla mercadolimit5001000 a sido creada con la finalidad de almacenar los datos de la consulta anterior, se ejecuto el sigueinte SQL en postgres:

insert into mercadolimit5001000 select l.cod_lote, 500 from lotes l, lotes a where( st_distance(l.geometria, a.geometria)<=500 and a.cod_lote in ('01040001','01040002','01040003','01040004','01040005'

Si desean ver un poco mas a modo de consulta y capas sobre la base de datos espacial que se utilizo en este post esta alojada en el Servidor de Mapas de la Municipalidad Provincial de Coronel Portillo en la que eh sido colaborador directo, ubicado en la siguiente direccion: http://201.230.96.134/pucallpa/mapa.phtml

Proximos Articulos:

- retomando PHP Zend Framework demo

- Google Maps con PHP aplicado a un Sistema de Posicionamiento Global

Triggers en PostgreSQL

Triggers en PostgreSQL

Publish at Scribd or explore others: Tutorial General spanish triggers

Postgis la extension espacial de postgres

Post Gis - Upload a Document to Scribd