Postgresql - Actualizar tablas de forma dinámica la estructura es de tablas de relleno

-- Function: sdd_execute_idu_relleno(character varying, integer, character varying, character varying, integer, smallint)

-- DROP FUNCTION sdd_execute_idu_relleno(character varying, integer, character varying, character varying, integer, smallint);

CREATE OR REPLACE FUNCTION sdd_execute_idu_relleno(s_tabla_nom character varying, i_id integer, s_descripcion character varying, s_sintetico character varying, i_estado integer, i_oper smallint)
RETURNS integer AS
$BODY$
DECLARE s_error VARCHAR(255);
DECLARE s_dinamic_sql TEXT;
DECLARE i_error INTEGER;
DECLARE i_return SMALLINT;
DECLARE s_descp_exec VARCHAR(255);
DECLARE s_log VARCHAR(255);
DECLARE s_opera VARCHAR(20);
BEGIN
/*
Funcion:
Permite realizar la ejecucion dinamicas de las funciones
Parametros:
Los valores fijos de las tablas de rellenos son:
i_id--> El valor identificador de la tabla es un valor SERIAL
s_descripcion --> Contiene la descripcion de la tabla de relleno es un valor del tipo char
i_estado--> es el estado que contiene en la tabla 1/habilitado-0/inhabilitado
i_oper --> Contien el tipo de operacion a realizar 1/Delte-2/Insert-3/delete
*/


i_return := 0;
/*
Las tipos de operaciones de IDU se encuentran marcadas por i_oper
validamos que se ingrese un valor valido 0 o NULL son no validos
*/
IF (i_oper = NULL) OR (i_oper <= 0) THEN
s_error := 'No se ingreso ningún valor para realizar operacion i_oper';
RAISE WARNING '%',s_error;
i_error = 25000;
RETURN i_error;
END IF;

/*
1 - Operacion del tipo DELETE(1)
*/
IF (i_oper = 1) THEN
/*
Validamos el estado de la clave principal :-)
*/
IF (i_id = NULL) OR (i_id = 0) THEN
s_error := 'No se ingreso un identificador valido para borrar i_id';
RAISE WARNING '%',s_error;
i_error := 30000;
RETURN i_error; /*Id para delete*/
END IF;

/*
Ejecucion dinamica del select esto puede se aplicado al final
*/
s_dinamic_sql := 'DELETE FROM '||s_tabla_nom||' WHERE id = '||to_char(i_id,'999');
s_descp_exec := 'borro';
/*
Marcamos la etiqueta para que el log marque q estamos haciendo
*/
s_opera := 'DELETE =>';
END IF;


/*
Controlde Nulidad tanto para Insert Como Update es el mismo :-)
*/

If(i_oper = 1) OR (i_oper = 2) Then
/*
Validamos la existencia de los valores para la clave principal
*/

i_error := 0;
/*=========================================================================================*/
/*CONTROL DE NULIDAD DE VARIABLES NO NULAS APRA REALIZAR EL INSERT DE LOS DATOS */
/*=========================================================================================*/
IF (s_descripcion IS NULL) OR(s_sintetico IS NULL) OR (i_estado = NULL) THEN

IF (descripcion IS NULL) THEN
s_error := s_error||'descripcion';
END IF;
IF (sintetico IS NULL) THEN
s_error := s_error||'sintetico';
END IF;

IF (i_estado IS NULL) THEN
s_error := s_error||'estado';
END IF;

RAISE WARNING 'Parametros nulos (%)',s_error;
i_error := 24000;
RETURN i_error;
END IF;

End If;

/*
2 - Operacion del Tipo Insert(2)
*/

IF (i_oper = 2) THEN
s_dinamic_sql :='INSERT INTO '||s_tabla_nom||'
(descripcion,sintetico,estado)
VALUES('||quote_literal(s_descripcion)||','||
quote_literal(s_sintetico)||','||
to_char(i_estado,'999')||')';

s_opera := 'INSERT =>';
END IF;

/*
3 - Operacion de Update (3)
*/
IF (i_oper = 3) THEN
/*
SP dinamico de UPDATE
*/
s_dinamic_sql := 'UPDATE '||s_tabla_nom||' SET '||
'descripcion= '||quote_literal(s_descripcion)||','||
' sintetico= '||quote_literal(s_sintetico)||','||
' estado= '||to_char(i_estado,'999')||
' WHERE id= '||to_char(i_id,'999');
s_opera := 'UPDATE =>';
END IF;

/*
Ejecutamos el valor de forma dinamica esto es posible ya
que las operaciones de IDU siempre devuelve la cantidad
de filas afectadas
*/

EXECUTE s_dinamic_sql;

/*
Controlamos que se ejecute el proceso
*/
GET DIAGNOSTICS i_return = ROW_COUNT;
IF i_return >0 THEN
i_error :=0;
/*
Guardamo en el log el evento para control a posteridad
*/
s_log := s_opera||' Tabla de relleno '||s_tabla_nom||' i_id:'||to_char(i_id,'999');
SELECT * INTO i_error FROM ssg_iutsg_log_admin('4',s_log);
ELSE
i_error := 26000;
RAISE WARNING 'No se % ningun valor para la clave: (%)',s_descp_exec,s_dinamic_sql;
RETURN i_error; /*los errores de 600 son de UDI*/
END IF;


RETURN i_error;

END
$BODY$
LANGUAGE 'plpgsql' VOLATILE
COST 100;
ALTER FUNCTION sdd_execute_idu_relleno(character varying, integer, character varying, character varying, integer, smallint) OWNER TO <>;

Postgresql - Validar que la entrada sea solo números

-- Function: sgl_solo_numeros(character varying)

-- DROP FUNCTION sgl_solo_numeros(character varying);

CREATE OR REPLACE FUNCTION sgl_solo_numeros(s_value character varying)
RETURNS integer AS
$BODY$
DECLARE i_pasada INTEGER;
DECLARE i_length_char INTEGER;
DECLARE i_error INTEGER;
DECLARE s_error VARCHAR(255);
DECLARE s_subcadena VARCHAR(2);
BEGIN

i_error := 0;
IF (s_value IS NULL) OR (s_value = '') THEN
s_error := 'No se ingreso ningún valor para validar';
RAISE WARNING '%',s_error;
i_error := 22500;
RETURN i_error;
END IF;

/*
Determinamos si la cadena tiene un tamaño relativo
*/

i_length_char := char_length(s_value);

IF (i_length_char <= 0) Then
s_error := 'La cadena ingresada no posee valores';
RAISE WARNING '%',s_error;
i_error := 22600;
RETURN i_error;
End If;

i_pasada := 1;
WHILE i_pasada <= i_length_char LOOP
s_subcadena := substr(s_value,i_pasada,1);
IF (ascii(s_subcadena) < 48) OR (ascii(s_subcadena) > 57) THEN
RAISE WARNING '%',s_subcadena;
i_error = i_pasada; /*no posee solo numeros*/
RETURN i_error;
END IF;
i_pasada := i_pasada +1;
END LOOP;

RETURN i_error;
END
$BODY$
LANGUAGE 'plpgsql' VOLATILE
COST 100;
ALTER FUNCTION sgl_solo_numeros(character varying) OWNER TO <>;

Postgresl - Validar cadenas validas

-- Function: sgl_solo_caracteres(character varying)

-- DROP FUNCTION sgl_solo_caracteres(character varying);
/*Esta funcion controla que se ingresen solo caracteres*/
CREATE OR REPLACE FUNCTION sgl_solo_caracteres(s_value character varying)
RETURNS integer AS
$BODY$
DECLARE i_pasada INTEGER;
DECLARE i_length_char INTEGER;
DECLARE i_error INTEGER;
DECLARE s_error VARCHAR(255);
DECLARE s_subcadena VARCHAR(2);
BEGIN

i_error := 0;
IF (s_value IS NULL) OR (s_value = '') THEN
s_error := 'No se ingreso ningún valor para validar';
RAISE WARNING '%',s_error;
i_error := 22500;
RETURN i_error;
END IF;

/*
Determinamos si la cadena tiene un tamaño relativo
*/

i_length_char := char_length(s_value);

IF (i_length_char <= 0) Then
s_error := 'La cadena ingresada no posee valores';
RAISE WARNING '%',s_error;
i_error := 22600;
RETURN i_error;
End If;

i_pasada := 1;



WHILE i_pasada <= i_length_char LOOP
s_subcadena := substr(s_value,i_pasada,1);
IF (ascii(UPPER(s_subcadena)) < 65) OR (ascii(UPPER(s_subcadena)) > 90) THEN
RAISE WARNING '%',s_subcadena;
i_error = i_pasada; /*no posee solo numeros*/
RETURN i_error;
END IF;
i_pasada := i_pasada +1;
END LOOP;

RETURN i_error;
END
$BODY$
LANGUAGE 'plpgsql' VOLATILE
COST 100;
ALTER FUNCTION sgl_solo_caracteres(character varying) OWNER TO ;

Postgresql - Recalcular edad y actualizar edad con cursor

-- Function: sgl_recacl_edad()

-- DROP FUNCTION sgl_recacl_edad();

CREATE OR REPLACE FUNCTION sgl_recacl_edad()
RETURNS integer AS
$BODY$
DECLARE s_error VARCHAR(255);
DECLARE s_log VARCHAR(255);
DECLARE i_error INTEGER;
DECLARE s_id VARCHAR(20);
DECLARE cafiliados refcursor;
DECLARE c_edad smallint;
DECLARE c_fechanac timestamp without time zone;
DECLARE fechaactual timestamp without time zone;
DECLARE c_id integer;
DECLARE edad_act smallint;
DECLARE dia_nac SMALLINT;
DECLARE dia_actual SMALLINT;
DECLARE mes_actual SMALLINT;
DECLARE mes_nac SMALLINT;
BEGIN
/*
*Funcion: Permite modificar y recalcular la edad de un cliente
*/
fechaactual := current_timestamp;

/*Transaccion para los datos de los clientes*/
--START TRANSACTION;

OPEN cafiliados
FOR SELECT fechanac,edad,id FROM t_afiliados;

FETCH cafiliados INTO c_fechanac,c_edad,c_id;
/*si se encontraron filas*/
while found loop
/*calculamos la edad del cliente de nuevo*/
SELECT s_calcula_edad(c_fechanac) INTO edad_act;
dia_nac := EXTRACT(DAY FROM c_fechanac);
dia_actual := EXTRACT(DAY FROM fechaactual);
mes_nac := EXTRACT(MONTH FROM c_fechanac);
mes_actual := EXTRACT(MONTH FROM fechaactual);

IF (dia_nac = dia_actual ) AND (mes_nac = mes_actual) THEN
UPDATE t_afiliados
SET edad=edad_act
WHERE id = c_id;
/*Determinamos si se recuperaron datos desde la busqueda de info*/

IF FOUND THEN
i_error := 0;
ELSE
i_error := -1;/*no se actualizaron filas*/
END IF;
END IF;

/*sacamos los datos de la proxima fila*/
FETCH cafiliados INTO c_fechanac,c_edad,c_id;
end loop;

CLOSE cafiliados;


RETURN i_error;
END
$BODY$
LANGUAGE 'plpgsql' VOLATILE
COST 100;
ALTER FUNCTION sgl_recacl_edad() OWNER TO <>;
/*tambien se puede utilizar de la seguiente forma l actualizacion*/
UPDATE t_afiliados
SET edad=s_calcula_edad(current_timestamp,fechanac)

Postgrsql - Calcula edad con rango de fechas

Ejemplo de calculo de la edad con un rango de fechas desde hasta


-- Function: s_calcula_edad(timestamp without time zone)

-- DROP FUNCTION s_calcula_edad(timestamp without time zone);

CREATE OR REPLACE FUNCTION s_calcula_edad(fechanac timestamp without time zone)
RETURNS integer AS
$BODY$
DECLARE s_fecha VARCHAR(60);
DECLARE i_fecha INTEGER;
BEGIN

/*
Este procedimiento permite calcular la edad de una persona a partir de la fecha ingresada
*/

s_fecha := age(now(),fechanac);

If (s_fecha IS NULL) OR (s_fecha = '') Then
return -1; /*Error la fecha no es valida*/
End If;

/*
Recupero solamente la edad de la persona
*/
s_fecha := substring(s_fecha,1,strpos(s_fecha,' '));

i_fecha := rtrim(s_fecha);

RETURN i_fecha;
END
$BODY$
LANGUAGE 'plpgsql' VOLATILE
COST 100;
ALTER FUNCTION s_calcula_edad(timestamp without time zone) OWNER TO postgres;