Postgres (buenas practicas)

 

CREATE OR REPLACE FUNCTION esquema.funcion(usuario INTEGER)
RETURNS TABLE(data JSON)
LANGUAGE plpgsql
AS $function$
DECLARE 
    funcionEjecutada VARCHAR(300);
    parametrosSeguimiento VARCHAR(3000);
    ultimoRegistro INTEGER;
    tiempoInicio TIMESTAMP := CLOCK_TIMESTAMP();
BEGIN
    -- Definir el nombre del procedimiento almacenado y los parámetros
    funcionEjecutada := 'funcion';
    parametrosSeguimiento := 'Usuario: ' || usuario::VARCHAR;

    -- Insertar en el log
    INSERT INTO "log".log(funcionEjecucion, parametrosSeguimiento, datoEjecucion, usuario, creacion)
    VALUES (funcionEjecutada, parametrosSeguimiento, tiempoInicio, usuario, CURRENT_TIMESTAMP)
    RETURNING log_id INTO ultimoRegistro;

    -- Crear una tabla temporal para almacenar los resultados
    CREATE TEMP TABLE table_tmp (
        dato INT,
        indicacion VARCHAR(250)
    ) ON COMMIT DROP;

    -- Insertar los datos en la tabla temporal
    INSERT INTO table_tmp (dato, indicacion)
    SELECT tipo_reporte_id, indicacion
    FROM esquema.tablaorigenDato
    WHERE active = TRUE;

    -- Retornar los datos en formato JSON
    RETURN QUERY	
    SELECT json_build_object(
        'msg', 'Consulta Exitosa',
        'success', 1,
        'data', COALESCE(
            (SELECT json_agg(row_to_json(c)) FROM (
                SELECT dato AS id, indicacion FROM table_tmp ORDER BY dato
            ) c), '[]'::JSON
        )
    )::JSON;

    -- Actualizar el log con el tiempo de ejecución
    UPDATE "log".log
    SET  
        execution_end_date = CLOCK_TIMESTAMP(),
        updated_at = CURRENT_TIMESTAMP,
        execution_time = ROUND(EXTRACT(EPOCH FROM (CLOCK_TIMESTAMP() - tiempoInicio)) * 1000) -- en milisegundos
    WHERE log_id = ultimoRegistro;
END;
$function$;

Puntos clave de la corrección:

Se inicializa correctamente tiempoInicio con CLOCK_TIMESTAMP().
Se usa parametrosSeguimiento con la ortografía correcta.
Se asegura que table_tmp y tablaorigenDato coincidan en nombres de columnas.
Se usa dato AS id en la salida JSON para que coincida con la estructura esperada

Explicación de la función PL/pgSQL en PostgreSQL

La función esquema.funcion(usuario INTEGER) realiza las siguientes acciones:

1. Registra la ejecución en un log
2. Crea una tabla temporal para almacenar resultados
3. Inserta datos desde una tabla origen (esquema.tablaorigenDato)
4. Retorna los datos en formato JSON
5. Actualiza el log con el tiempo de ejecución

1. Definición de la función

CREATE OR REPLACE FUNCTION esquema.funcion(usuario INTEGER)
RETURNS TABLE(data JSON)
LANGUAGE plpgsql

• Se define una función almacenada en PostgreSQL dentro del esquema esquema.
• Acepta un parámetro entero usuario INTEGER.
• Retorna una tabla con una columna data de tipo JSON.
• Usa PL/pgSQL como lenguaje.

2. Declaración de variables

DECLARE 
    funcionEjecutada VARCHAR(300);
    parametrosSeguimiento VARCHAR(3000);
    ultimoRegistro INTEGER;
    tiempoInicio TIMESTAMP := CLOCK_TIMESTAMP();

• funcionEjecutada: Guarda el nombre de la función en ejecución.
• parametrosSeguimiento: Almacena información del usuario que llamó la función.
• ultimoRegistro: Guarda el log_id generado en el log.
• tiempoInicio: Captura el tiempo de inicio de la función con CLOCK_TIMESTAMP().

3. Inserción en la tabla de logs

INSERT INTO "log".log(funcionEjecucion, parametrosSeguimiento, datoEjecucion, usuario, creacion)
VALUES (funcionEjecutada, parametrosSeguimiento, tiempoInicio, usuario, CURRENT_TIMESTAMP)
RETURNING log_id INTO ultimoRegistro;

• Inserta un registro en la tabla "log".log, guardando:
  • funcionEjecutada: Nombre de la función.
  • parametrosSeguimiento: Información del usuario.
  • datoEjecucion: Hora de inicio (tiempoInicio).
  • usuario: ID del usuario.
  • creacion: Fecha/hora actual (CURRENT_TIMESTAMP).
• RETURNING log_id INTO ultimoRegistro: Obtiene el ID del log insertado.

4. Creación de la tabla temporal

CREATE TEMP TABLE table_tmp (
    dato INT,
    indicacion VARCHAR(250)
) ON COMMIT DROP;

• Se crea una tabla temporal table_tmp con dos columnas:
  • dato: Un entero.
  • indicacion: Un texto de hasta 250 caracteres.
• ON COMMIT DROP: La tabla se elimina automáticamente al finalizar la transacción.

5. Inserción de datos en la tabla temporal

INSERT INTO table_tmp (dato, indicacion)
SELECT tipo_reporte_id, indicacion
FROM esquema.tablaorigenDato
WHERE active = TRUE;

• Se copian datos desde esquema.tablaorigenDato a table_tmp, filtrando solo los registros activos (active = TRUE).

6. Retorno de los datos en formato JSON

RETURN QUERY	
SELECT json_build_object(
    'msg', 'Consulta Exitosa',
    'success', 1,
    'data', COALESCE(
        (SELECT json_agg(row_to_json(c)) FROM (
            SELECT dato AS id, indicacion FROM table_tmp ORDER BY dato
        ) c), '[]'::JSON
    )
)::JSON;

• Se construye un JSON con json_build_object:
  • 'msg': Mensaje "Consulta Exitosa".
  • 'success': Valor 1 (indicando éxito).
  • 'data': Se obtiene con json_agg(row_to_json(c)), transformando los resultados en un array de objetos JSON.
  • COALESCE(..., '[]'::JSON): Si no hay datos, devuelve un array vacío [].

Ejemplo de salida JSON:

{
    "msg": "Consulta Exitosa",
    "success": 1,
    "data": [
        {"id": 1, "indicacion": "Alerta"},
        {"id": 2, "indicacion": "Normal"}
    ]
}

7. Actualización del log con el tiempo de ejecución

UPDATE "log".log
SET  
    execution_end_date = CLOCK_TIMESTAMP(),
    updated_at = CURRENT_TIMESTAMP,
    execution_time = ROUND(EXTRACT(EPOCH FROM (CLOCK_TIMESTAMP() - tiempoInicio)) * 1000) -- en milisegundos
WHERE log_id = ultimoRegistro;

• Se actualiza el registro del log con:
  • execution_end_date: Hora final de ejecución.
  • updated_at: Última actualización del registro.
  • execution_time: Tiempo de ejecución en milisegundos.

Resumen del flujo de la función

1. Registra la ejecución en el log con la información del usuario.
2. Crea una tabla temporal para almacenar datos.
3. Obtiene datos desde esquema.tablaorigenDato y los guarda en table_tmp.
4. Convierte los datos en JSON y los retorna como resultado de la función.
5. Actualiza el log con el tiempo total de ejecución.

Es una función útil para registrar consultas y mejorar el seguimiento de la ejecución en PostgreSQL.