Sabiduría para programar

Recientemente leí en el histórica de la lista de postgresql, en palabras de Alvaro Herrera, algo así como:

No le digas como lo va a hacer, dile qué es lo que tiene que hacer

. Palabras que hoy en día me sonaron, por mucho, llenas de sabiduría... ¿a qué se refería? era parte de una respuesta a una programación del lado de la Base de Datos... mejorar la programación, el rendimiento del motor, etc...

Haré el intento de conseguir las palabras exactas...

Insertar varios registros sí no existe

Bueno... es posible que esta idea este extraña, pero así es como lo tenemos.

Nuestro código político está más o menos así:
\d region
Tabla «public.region»
Columna | Tipo | Modificadores
---------+-----------------------+---------------
reg_001 | character varying(2) | not null
reg_002 | character varying(35) | not null
Índices:
«region_pkey» PRIMARY KEY, btree (reg_001)

\d estado
Tabla «public.estado»
Columna | Tipo | Modificadores
---------+-----------------------+---------------
reg_001 | character varying(2) | not null
est_001 | character varying(2) | not null
est_002 | character varying(35) | not null
Índices:
«estado_pkey» PRIMARY KEY, btree (est_001)
«estado_ukey» UNIQUE, btree (reg_001, est_001)
Restricciones de llave foránea:
«estado_fkey» FOREIGN KEY (reg_001) REFERENCES region(reg_001) ON UPDATE CASCADE ON DELETE RESTRICT

\d municipio
Tabla «public.municipio»
Columna | Tipo | Modificadores
---------+-----------------------+---------------
reg_001 | character varying(2) | not null
est_001 | character varying(2) | not null
mun_001 | character varying(2) | not null
mun_002 | character varying(35) | not null
Índices:
«municipio_ukey» UNIQUE, btree (reg_001, est_001, mun_001)
Restricciones de llave foránea:
«municipio_fkey» FOREIGN KEY (est_001) REFERENCES estado(est_001) ON UPDATE CASCADE ON DELETE RESTRICT

\d parroquia
Tabla «public.parroquia»
Columna | Tipo | Modificadores
---------+-----------------------+---------------
reg_001 | character varying(2) | not null
est_001 | character varying(2) | not null
mun_001 | character varying(2) | not null
par_001 | character varying(2) | not null
par_002 | character varying(35) | not null
Índices:
«parroquia_ukey» UNIQUE, btree (reg_001, est_001, mun_001, par_001)

\d centro_poblado
Tabla «public.centro_poblado»
Columna | Tipo | Modificadores
---------+-----------------------+-------------------------------------------------------------
reg_001 | character varying(2) | not null
est_001 | character varying(2) | not null
mun_001 | character varying(2) | not null
par_001 | character varying(2) | not null
cpo_001 | character varying(3) | not null
cpo_002 | character varying(50) | not null
id | integer | not null default nextval('centro_poblado_id_seq'::regclass)

Y para el registro de datos, existe la posibilidad de que el detalle no llegue sino hasta la parroquia, por ejemplo, por lo que habría que seleccionar un Centro Poblado del tipo 'No Aplica', pero, un 'No Aplica' por cada parroquia existente, son como... muchos! al menos para insertarlos manualmente y uno por uno, así que decidí estudiar un poquito y ver cómo lo hacía vía plpgsql, he aquí el resultado:
CREATE OR REPLACE FUNCTION leer(out a varchar) RETURNS SETOF varchar LANGUAGE plpgsql AS $$
DECLARE
r record;
p varchar := '01';
BEGIN
-- recorrer la tabla tantas veces como parroquias existan
FOR r IN SELECT par_001 FROM centro_poblado GROUP BY par_001 LOOP
-- recorrer la tabla tantas veces como parroquias existan y centros poblados con código igual a '000' existan
FOR r IN SELECT COUNT(*) AS cuenta FROM centro_poblado WHERE par_001 = p AND cpo_001 = '000' LOOP
-- inicializo 'a'
a = r.cuenta;
-- sí a leido como entero, es igual a cero, es porque no existe un centro poblado con código igual a '000'
IF (a::int = 0) THEN
-- insertar el registro
INSERT INTO centro_poblado (reg_001, est_001, mun_001, par_001, cpo_001, cpo_002) VALUES ('06', '14', '01', p, '000', 'No Aplica');
END IF;
-- incremento para pasar a la siguiente parroquia
p := '0'|| p::int + 1;
-- paso a la siguiente parroquia
RETURN next;
END LOOP;
END LOOP;
END;
$$;

Seguro, alguien consigue otra forma de hacerlo mejor, pero al menos sirve... También es posible ir lanzando algunas notificaciones para saber por dónde y cómo va, tal como RAISE NOTICE ' Parroquia tiene %',p; pero es a gusto de cada quien...

Listo! yo mismo lo hice de otra forma:

--
-- funcion para insertar no aplica para parroquias
--
CREATE OR REPLACE FUNCTION insertar_no_aplica_centro_poblado(e varchar, out a varchar) RETURNS SETOF varchar LANGUAGE plpgsql AS $$
DECLARE
l record;
r record;
p record;
BEGIN
create temp table audit_tmp (usuario integer, ip inet, ocurrencia timestamp default now());
-- listar todas las parroquias y municipios agrupadas
FOR l IN SELECT mun_001 FROM centro_poblado WHERE est_001 = e GROUP BY mun_001 ORDER BY mun_001 LOOP
FOR r IN SELECT par_001 FROM centro_poblado WHERE est_001 = e AND mun_001 = l.mun_001 GROUP BY par_001 ORDER BY par_001 LOOP
FOR p IN SELECT cpo_001 FROM centro_poblado WHERE est_001 = e AND mun_001 = l.mun_001 AND par_001 = r.par_001 GROUP BY cpo_001 ORDER BY cpo_001 LOOP
IF (p.cpo_001 = '000') THEN
RAISE NOTICE 'No Aplica para : %', r.par_001;
EXIT;
ELSE
insert into audit_tmp values (1, '127.0.0.1');
RAISE NOTICE 'Insertando para la parroquia : %', r.par_001;
INSERT INTO centro_poblado (reg_001, est_001, mun_001, par_001, cpo_001, cpo_002) VALUES ('06', e, l.mun_001, r.par_001, '000', 'No Aplica');
EXIT;
END IF;
END LOOP;
END LOOP;
END LOOP;
END;
$$;

Ya ven, es otra forma...

Eliminando registros duplicados, desde SQL

Zas! Por un error de programación, de repente aparecieron un montón de registros duplicados, y cuando digo un montón, son un montón!!!

y ahora? pues, a pensar con cabeza fría... cuáles son las opciones?

• borrar todos los duplicados a través del mismo sistema
• pensar en un script en cualquier lenguaje que haga todo de un sólo halón

Por razones de seguridad de los datos, me inclino por la segunda opción... lo primero que pensé es en cómo se podía hacer con SQL

La tabla:
\d centro_poblado
Tabla «public.centro_poblado»
Columna | Tipo | Modificadores
---------+-----------------------+-------------------------------------------------------------
reg_001 | character varying(2) | not null
est_001 | character varying(2) | not null
mun_001 | character varying(2) | not null
par_001 | character varying(2) | not null
cpo_001 | character varying(3) | not null
cpo_002 | character varying(50) | not null
id | integer | not null default nextval('centro_poblado_id_seq'::regclass)

Como la tabla tiene un id de tipo serial, eso ayuda.

Verficiar cuáles son los registros repetidos:
SELECT COUNT(*), reg_001, est_001, mun_001, par_001 FROM centro_poblado GROUP BY cpo_002, reg_001, est_001, mun_001, par_001 ORDER BY reg_001, est_001, mun_001, par_001;

Verificar los registros repetidos:
SELECT * FROM centro_poblado WHERE reg_001 = '06' AND est_001 = '14' AND mun_001 = '01' AND par_001 = '05';

Contar los registros repetidos, en detalle:
SELECT count(*) FROM centro_poblado WHERE reg_001 = '06' AND est_001 = '14' AND mun_001 = '01' AND par_001 = '05' AND cpo_001 = '018';

Verificar el id min y máx del detalle de los registros repetidos:
SELECT min(id), max(id) FROM centro_poblado WHERE reg_001 = '06' AND est_001 = '14' AND mun_001 = '01' AND par_001 = '05' AND cpo_001 = '018';

La verdad es que luego descubrí que se podía hacer así:
SELECT * FROM centro_poblado AS a
WHERE EXISTS(
SELECT cpo_001, COUNT(cpo_001)
FROM centro_poblado
WHERE a.cpo_001= cpo_001
GROUP BY cpo_001
HAVING COUNT(cpo_001)>1)
ORDER BY cpo_001

Eliminar los registros repetidos, menos 1:
DELETE FROM centro_poblado WHERE reg_001 = '06' AND est_001 = '14' AND mun_001 = '01' AND par_001 = '05' AND cpo_001 = '018' AND id > 1;

Esto también se puede resolver con una tabla temporal, y metiendo la consulta en una función y todo eso, pero aun no lo he probado...

Finalmente, y gracias a la lista de usuarios de postgres y su histórico, me encontré [1]:
DELETE FROM centro_poblado WHERE ctid NOT IN (SELECT min(ctid) FROM centro_poblado GROUP BY cpo_002, reg_001, est_001, mun_001, par_001);

[1]http://archives.postgresql.org/pgsql-es-ayuda/2007-01/msg00353.php